SpreadsheetBased Decision Support Systems Chapter 5 Charts Sparklines

Spreadsheet-Based Decision Support Systems Chapter 5: Charts & Sparklines Prof. Name Position University Name name@email. com (123) 456 -7890

Overview v v v 5. 1 Introduction 5. 2 Creating Charts 5. 3 Working with Chart Tools 5. 4 Creating Sparklines 5. 5 Creating Dynamic Charts and Sparklines 5. 6 Summary 2

Introduction v Creating a chart and a sparkline v Customizing charts and sparklines using various chart options v Creating dynamic charts and sparklines 3

Creating Charts v Charts are used to take data from a table and transform it into a graphical illustration v They are useful for displaying data patterns or results v To create a chart, we will use Charts group of Excel Ribbon. – Insert tab > Charts group on the Ribbon Dialog Launcher 4

Creating a Chart v There are three main steps in creating a chart. – Step 1: Select Data Range – Step 2: Select Chart Type – Step 3: Select Chart Subtype v Chart Type = bar graph, scattered graph, pie chart, etc. v Chart Subtype = clustered column, stacked column, 100% stacked column, etc. 5

Figure 5. 2 v Table of data = Number Sold and Revenue Generated from various Car Models v We create two charts: column charts of Number Sold and pie chart of Revenue Generated 6

Step 1: Select Data Range v Highlight the data to be graphed. v In Figure 5. 2, – Hold down the CTRL key. – Highlight the cells B 4: B 10 and E 4: E 10. v Column titles are highlighted to label data automatically. 7

Steps 2&3: Select Chart Type and Subtype v Each chart type has a set of chart sub-types. - v First we will select our chart type. We can then choose a sub-type. Example: Insert a 2 D column Chart Using Commands on the Ribbon: - - Select: Insert > Charts > Column command Click on: 2 -D Column Subtype 8

Steps 2&3: Select Chart Type and Subtype Using Dialog Launcher: - Select a chart type from the left side of the dialog box. Choose an appropriate chart subtype. 9

Figure 5. 5 v From the column graph we see that the Aco 3500 sold the most. 10

Figure 5. 6 v Follow the same steps to create the pie chart of Revenue Generated. v We see that the Cam 3200 generated the highest revenues. 11

Working with Chart Tools v After creating a chart, we customize its Design, Layout and Format using the contextual Chart Tools tabs on the Ribbon. v Design tools modify – Chart type , Data source, Layout, Style, etc. v Layout tools modify – Chart labels, Axes, Gridlines, Background, etc. v Format tools modify – Chart size, Shape style, Word art style, etc. 12

Working with Chart Tools (cont’d) Change Chart Type: v Select the column chart in Figure 5. 5. v Click on Design > Type > Change Chart Type command on the Ribbon. v Select Bar Option. 13

Figure 5. 8 v The resulting bar chart of Car Models Sold 14

Working with Chart Tools (cont’d) Change Data Source: v Change data source of the chart to present Mileage rather than Number Sold. v Select bar chart in Figure 5. 8 and click on Design > Data > Select Data command. v Change Chart Date Range – From: 'New Example’!$B$4: $B$10, ‘New Example’!$E$4: $E$10 – To: ‘New Example’!$B$4: $B$10, ‘New Example’!$D$4: $D$10 15

Working with Chart Tools (cont’d) Edit Data Series: v Series are various sets of data. v Use dialog box in Figure 5. 9. Series window is on the left of the dialog box. v Number Sold is the only series listed. v Select Add button. v On the Edit Series dialog box: – Add a Name to the new series. – Select the range of the new data. 16

Figure 5. 11 v The resulting bar charts the Revenues Generated and Mileage per car model. 17

Working with Chart Tools (cont’d) Add Chart and Axes Titles: v Select Layout > Labels > Axis Titles. v Select Primary Horizontal Axis Title from the drop-down list. v Select Title Below Axis option from the fly-out menu. v Type “Revenue/Mileage” 18

Figure 5. 13 Add Chart and Axes Titles (cont’d): v Follow similar steps to add the title “Car Models” to vertical axis. v Select Layout > Labels > Chart Title to add a title to the chart. v Figure 5. 13 shows the resulting changes to the bar chart. 19

Working with Chart Tools (cont’d) Format Chart Area: v Double click on the chart. Use the Format Chart Area box that appears to modify: – – Chart fill color Chart transparency Border color Border style, etc. v Double click on elements of the chart to change their format. v Right click on chart area – Short-cut menu – Mini toolbar 20

Working with Chart Tools (cont’d) Format Axes: v Double click on the axis (x, y, or z). Use the Format Axis dialog box to modify: – – Max and min values on the axis Number type Fill type Line color and style, etc. Format Gridlines: v Double click on any of the gridlines. v Select Layout > Axes > Gridlines to determine which gridlines to modify. 21

Figure 5. 16 v Data table shows plant growth based on minerals added or removed from the ground 22

Figures 5. 17 and 5. 18 v Create the chart 23

Figures 5. 19, 5. 20 (a) and (b) v Switch x and y values for better data analysis: – Remove the gridlines and change the order of the series: 24

Figure 5. 21 v Updated chart to reflect the new x and y values. 25

Creating Sparklines v Sparklines are small charts displayed in a single cell. Similar to charts, sparklines spot trends and variations in the data. v Example: Figure 5. 22 provides production and sales data. v ― Create a Column Sparkline: § Click on: Insert > Sparklines > Column command. § Select cell C 13 to be the Location Range for the “Production” sparkline. 26

Figure 5. 24 Formatting Sparklines: v Change type: – Sparkline Design > Type > Line command. v Highlight the highest value: – Sparkline Design > Show > High Point checkbox. v Change line color: – Sparkline Design > Style > Sparkline Color window. 27

Figure 5. 25 Conditional Formatting: v Three conditional formatting options are listed in Home > Styles > Conditional Formatting drop-down menu: – Data Bars – Color Scales – Icon Sets 28

Figure 5. 26 Conditional Formatting Example: v Conditional format cells: – C 4: C 11 in Figure 5. 22 using Data Bars > Gradient Fill – D 4: D 11 using Icon Sets > Four Bar Rating 29

Creating Dynamic Charts and Sparklines v A chart is linked directly to the Data Range specified when creating the chart. v If any points in this range of data are modified, the chart is automatically updated to reflect a new corresponding data point. v There will be three main Excel concepts used to create a dynamic chart and a dynamic sparkline: – Defining names – OFFSET function – COUNT function 30

Creating Dynamic Charts and Sparklines (cont’d) v Create some range names using the OFFSET and COUNT functions and set the Series of the chart to these dynamic ranges. – =OFFSET(initial_data_location, 0, 0, COUNT(entire_column), 1) v The rows_to_move and columns_to_move parameters are set to 0 because we are only interested in the column in which our reference_cell ( = initial_data_location) is located. v The width is again set to 1, since we are interested only in one column. v The height parameter is found using the COUNT function. – The COUNT function will review the entire column of the relative data and count how many cells have numeric values. – Thus the height of our range becomes dynamic as the amount of numeric values in the column increases. 31

Figure 5. 27 v Months and Units Sold may be dynamic values 32

Figures 5. 28 (a) and (b) v The dynamic ranges are created for each column using the OFFSET and COUNT functions 33

Figures 5. 29 and 5. 30 v Use Edit Series dialog box to define dynamic range names for a chart. v Use Edit Sparklines dialog box to define dynamic range names for a sparkline. – Select: Sparkline Design > Sparkline > Edit Data command. – Type the name of the dynamic range in the data range window. 34

Figure 5. 31 v The chart and the sparkline are now dynamic. 35

Summary v v Excel Charts and Sparklines allow you to illustrate your data in order to perform better analysis. There are three basic steps to create a chart: – Step 1: Select the data range. – Step 2: Select a chart type from the list in Insert > Charts group of the Ribbon. – Step 3: Determine the Chart Sub-type from the corresponding drop-down list. v v A chart can be modified after it is created by using the commands listed on the charts contextual tabs; and by using the dialog box that appears when double-clicking on the chart or different parts of the chart. You can change basic settings as well as formatting. These are the basic steps to create a Line Sparkline: – Step 1: Select the data range. – Step 2: Select Insert > Sparklines > Line. – Step 3: Type the address of the cell where the sparkline will be located. v A dynamic chart and sparkline can be created using the OFFSET and COUNT functions to create dynamic ranges used as Series in the Source Data. 36

Additional Links v (place links here) 37