Week 4 Excel

Determining Effective Data Display with Charts

Chapter 3

Chapter Introduction

• Creating effective charts that show quantitative information clearly, precisely, and efficiently

• Basics of creating and modifying line and column charts

• Influence of chart type on the viewer’s perception

Succeeding in Business with Microsoft Excel 2013: Chapter 3 2

Chapter Introduction (continued)

• How charts can be used in specific situations

• Building a management dashboard by combining different chart types within the same chart

To go to Level 1, click here

To go to Level 2, click here

To go to Level 3, click here

Succeeding in Business with Microsoft Excel 2013: Chapter 3 3

Chart Types Covered in This Chapter

• Area • Bubble • Column • Combo • Doughnut • Line • Pie • Radar • Stock • X Y (Scatter)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 4

Level 1 Objectives: Visualizing Data

• Understand the principles of effective data display

• Analyze various Excel chart types

• Determine appropriate uses for different chart types

• Modify the chart type and the chart source data

• Specify chart options, including chart and axis titles, legends, and data labels

Succeeding in Business with Microsoft Excel 2013: Chapter 3 5

Visualizing Data

Succeeding in Business with Microsoft Excel 2013: Chapter 3 6Level 1 home

Visualizing Data (continued)

• Data graphics principles (Tufte)

– Above all else, show the data

– Maximize the data-ink ratio, within reason

– Erase non-data-ink, within reason

– Erase redundant data-ink, within reason

– Revise and edit

Succeeding in Business with Microsoft Excel 2013: Chapter 3 7Level 1 home

Visualizing Data (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 8Level 1 home

Effective Data Display

• “Above all else show the data”

– Reminder not to clutter a chart by adding unnecessary illustration or decoration

• “Maximize the data-ink ratio”

– Refers to the portion of ink that is devoted to displaying the data versus the portion of the graphic that can be removed without losing the data

Succeeding in Business with Microsoft Excel 2013: Chapter 3 9Level 1 home

Effective Data Display (continued)

• “Erase non-data-ink”

– Non-data-ink is a part of the chart that decorates more than informs

• “Erase redundant data ink”

– Redundant data ink is ink that repeats information

• “Revise and edit”

– Revise and edit charts like you would a piece of writing

Succeeding in Business with Microsoft Excel 2013: Chapter 3 10Level 1 home

Effective Data Display (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 11Level 1 home

Effective Data Display (continued)

• Sparklines

– Small word-sized charts or graphics that are embedded within the words, numbers, and images they represent

– Make multiple comparisons of trends and patterns much easier

Succeeding in Business with Microsoft Excel 2013: Chapter 3 12Level 1 home

Effective Data Display (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 13Level 1 home

Effective Charting in Excel

• Creating a chart in Excel:

– Select the data to display

– Click the INSERT tab

– Click a button in the Charts group or the Dialog Box Launcher

Succeeding in Business with Microsoft Excel 2013: Chapter 3 14Level 1 home

Effective Charting in Excel (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 15Level 1 home

Effective Charting in Excel (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 16Level 1 home

Effective Charting in Excel (continued)

• Line chart: displays trends over time or by category

• Column chart: compares values across categories in a vertical orientation

Succeeding in Business with Microsoft Excel 2013: Chapter 3 17Level 1 home

Effective Charting in Excel (continued)

• Comparing line and X Y (Scatter) charts:

– X Y (Scatter) charts plot numeric values on both the x- and y-axes based on the value of the data

– Line charts plot numeric values on one axis and category labels equidistantly on the other axis

Succeeding in Business with Microsoft Excel 2013: Chapter 3 18Level 1 home

Effective Charting in Excel (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 19Level 1 home

Effective Charting in Excel (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 20Level 1 home

Effective Charting in Excel (continued)

• Specifying chart layout options:

– Click the Chart Elements button

Succeeding in Business with Microsoft Excel 2013: Chapter 3 21Level 1 home

Effective Charting in Excel (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 22Level 1 home

Effective Charting in Excel (continued)

• Understanding area and pie charts:

– Area chart: combines the features of a line chart with a bar or column chart by filling in the area below the line, and displaying the trend values over time or categories

– Pie chart: displays the percentage contribution that each category makes to a whole or 100%

Succeeding in Business with Microsoft Excel 2013: Chapter 3 23Level 1 home

Effective Charting in Excel (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 24Level 1 home

Effective Charting in Excel (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 25Level 1 home

Level 1 Summary

• Using charts to illustrate quantitative information adds visual analysis to problem solving

• Tufte’s guiding principles on the creation of graphics

• How the choice of chart type can influence the viewer’s perception of information presented

• Differences between main chart types

• Different interpretation of data can result from using a different chart type

Succeeding in Business with Microsoft Excel 2013: Chapter 3 26

Level 2 Objectives: Evaluating Chart Sub-Types

• Examine the effectiveness of different chart sub-types

• Evaluate the stacked and 100% stacked sub-types

• Explore the Pie of Pie and Bar of Pie sub-types

• Create various stock charts to display financial data

• Clarify data with trendlines and moving averages

Succeeding in Business with Microsoft Excel 2013: Chapter 3 27

Examining Sub-Types for Various Chart Types

Stacked charts • Illustrate cumulative effects of data in categories

• Available for line, bar, column, and area charts

Summing to 100%

(100% stacked

sub-type)

• Illustrate cumulative (rather than individual) contribution for each category as a

percentage

• Available for line, bar, column, and area charts • Combine features of a pie chart with features

of line, column, or area charts

• Similar to a pie chart except pieces are in a column instead of a circle

Succeeding in Business with Microsoft Excel 2013: Chapter 3 28Level 2 home

Examining Sub-Types for Various Chart Types (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 29Level 2 home

Examining Sub-Types for Various Chart Types (continued)

• Summing to 100%: alternatives to pie charts

– Showing the cumulative contribution for each category as a percentage can reduce confusion over whether the line on the chart represents the individual or cumulative contribution to the whole

Succeeding in Business with Microsoft Excel 2013: Chapter 3 30Level 2 home

Examining Sub-Types for Various Chart Types (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 31Level 2 home

An excessive number of pie slices makes the chart cluttered and confusing.

Examining Sub-Types for Various Chart Types (continued)

• Pie of Pie and Bar of Pie chart sub-types

– Decrease the number of pie segments to improve the visual display of data

– Use the Format Data Series task pane to select options for splitting: data series

• Position

• Value

• Percent Value

• Custom

Succeeding in Business with Microsoft Excel 2013: Chapter 3 32Level 2 home

Examining Sub-Types for Various Chart Types (continued)

• Doughnut charts

– Show individual percentages contained in a pie chart for more than one series

Succeeding in Business with Microsoft Excel 2013: Chapter 3 33Level 2 home

Examining Sub-Types for Various Chart Types (continued)

• Monitoring a business with stock charts

– Excel stock reporting charts are somewhat based on the candlestick plot format

– Stock chart sub-types:

• High-Low-Close

• Open-High-Low-Close

• Volume-High-Low-Close

• Volume-Open-High-Low-Close

Succeeding in Business with Microsoft Excel 2013: Chapter 3 34Level 2 home

Examining Sub-Types for Various Chart Types (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 35Level 2 home

Examining Sub-Types for Various Chart Types (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 36Level 2 home

Examining Sub-Types for Various Chart Types (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 37Level 2 home

• Using a combo chart

Examining Sub-Types for Various Chart Types (continued)

• Trendlines

– Graphically illustrate trends in data using a statistical technique known as regression

• Moving average trend line

– Used to smooth out the data, making it easier to spot trends

Succeeding in Business with Microsoft Excel 2013: Chapter 3 38Level 2 home

Examining Sub-Types for Various Chart Types (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 39Level 2 home

• Adding trendlines and moving averages

Level 2 Summary

• Chart sub-types for line, column, and area charts (stacked and 100% stacked)

• Pie of Pie and Bar of Pie chart sub-types

• Sub-types of stock charts

• Clarifying data in stock charts using trendlines and moving averages

Succeeding in Business with Microsoft Excel 2013: Chapter 3 40

Level 3 Objectives: Exploring More Advanced Chart Types

• Understand and evaluate radar, bubble, and dashboard charts

• Compare a bubble chart with a 3-D column chart

• Explore and customize a dashboard chart

• Create and customize a doughnut chart

Succeeding in Business with Microsoft Excel 2013: Chapter 3 41

Evaluating the Effectiveness of Radar, Bubble, and Dashboard Charts

Radar charts Powerful method of displaying and

comparing data across categories

Bubble charts Allow three-dimensional data to be

plotted in 2-D on two axes

Dashboard charts Feature a set of charts that

summarize several sets of data

graphically

Succeeding in Business with Microsoft Excel 2013: Chapter 3 42Level 3 home

Evaluating the Effectiveness of Radar, Bubble, and Dashboard Charts (continued)

• Understanding radar charts

Succeeding in Business with Microsoft Excel 2013: Chapter 3 43Level 3 home

The straight lines that radiate out from the center represent categories.

Evaluating the Effectiveness of Radar, Bubble, and Dashboard Charts (continued)

• Plotting 3-D data in two axes: bubble charts versus 3-D column charts

Succeeding in Business with Microsoft Excel 2013: Chapter 3 44Level 3 home

Evaluating the Effectiveness of Radar, Bubble, and Dashboard Charts (continued)

• Sample bubble chart

Succeeding in Business with Microsoft Excel 2013: Chapter 3 45Level 3 home

The market share of each shoe style is represented by the size of the bubbles.

Evaluating the Effectiveness of Radar, Bubble, and Dashboard Charts (continued)

• Creating a management dashboard:

– Build a dashboard chart

– Define the normal operating range

– Create the value indicator

– Create the doughnut chart

– Add digital values to the chart

Succeeding in Business with Microsoft Excel 2013: Chapter 3 46Level 3 home

Evaluating the Effectiveness of Radar, Bubble, and Dashboard Charts (continued)

• Build a dashboard chart

Succeeding in Business with Microsoft Excel 2013: Chapter 3 47Level 3 home

Evaluating the Effectiveness of Radar, Bubble, and Dashboard Charts (continued)

• Define the normal operating range

Succeeding in Business with Microsoft Excel 2013: Chapter 3 48Level 3 home

Level 3 Summary

• More advanced chart types

– Radar charts

– Bubble charts

• How to combine chart types within the same chart to build a management dashboard

Succeeding in Business with Microsoft Excel 2013: Chapter 3 49

Chapter Summary

• Charts offer the opportunity to add visual analysis to problem solving

• Chart types: – Line

– Column

– Bar

– Area

– Pie

– X Y (Scatter)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 50

Chapter Summary (continued)

• Chart sub-types further summarize the data being presented

• Advanced chart types such as radar and bubble charts

Succeeding in Business with Microsoft Excel 2013: Chapter 3 51

Chapter Summary (continued)

Succeeding in Business with Microsoft Excel 2013: Chapter 3 52