23. Graphing Data Using Excel Charts
Excel 2016 introduces six new chart types, which are discussed at the end of this chapter. Before getting to the new charts, the chapter covers general techniques for creating graphs.
Starting in Excel 2013, the Recommended Charts feature analyzes your data and suggests charts that might look good. The rules behind the chart recommendations are surprisingly good. In most cases, you will create good-looking charts by going with one of the recommendations. Excel never suggests a 3D chart because those types of charts misrepresent the data.
After you have the chart, a paintbrush icon offers 10 to 15 ways to style the chart. Styles range from minimalist to intense. If you are a disciple of Professor Edward R. Tufte and you believe every bit of ink on the chart has to have a meaning, you will prefer minimalist styles.
Choosing from Recommended Charts
The data in Figure 23.1 is a simple trend of monthly sales. Headings for the months appear in B1:M1. Sales appear in B2:M2. A label of “Sales” appears in A2. The label in A1 is optional. To create a chart, follow these steps:
1. Select A1:M2.
2. On the Insert tab, choose Recommended Charts. Excel displays the Insert Chart dialog. The Recommended Charts tab shows four thumbnails on the left side. Click each thumbnail to see a larger chart on the right side. A description below the chart explains why this chart is appropriate.
3. Click OK to insert the chart in the center of the visible window. Grab the border of the chart and drag to the appropriate place. (See the following tip if you have to move the chart more than a screenful of data away.)
4. Notice the three icons to the right of the chart and the two contextual ribbon tabs that appear while the chart is selected. The following sections show you how to use those tools to polish the chart.
If your chart appears in row 500 and you want it to appear in row 5, you should consider changing how you select a data set. Suppose that you have data in A1:C500 that should appear on a chart. You would probably start in A1, press Ctrl+Shift+Down Arrow and Ctrl+Shift+Right Arrow. You end up with rows 490 through 520 visible on your screen. When you insert a chart, it appears at the bottom of your data instead of at the top of your data. Instead, press Ctrl+Backspace before creating the chart. This brings the active cell back into view.
Using the Paintbrush Icon for Styles
Click the paintbrush icon to reveal a fly-out menu with 12 to 15 professionally designed chart styles for the selected chart (see Figure 23.2).
The chart styles in this menu look new and stylish. Consider the style shown in Figure 23.2. You have fading drop lines extending from a data label down toward the baseline of the chart. The chart area and plot area are dark blue and all chart elements are white. The marker is really the data label. The actual line appears only between October and December because of the jump in sales. I went back to Excel 2010 to see whether it’s possible to create the identical chart. You can. It takes 26 separate steps, most of which require at least two mouse clicks. And that is 26 steps when you are trying to mimic an existing chart. If you were to try to come up with those steps without having a pattern to follow, it would be very time consuming. Excel 2016 makes it available with just a few clicks.
Deleting Extraneous Data Using the Funnel
The data in Figure 23.3 includes quarterly totals and an annual total. Accidentally including this data in the chart is a common mistake. The size of the subtotals and total columns makes all the other columns indistinguishable from each other.
Open the Funnel icon. You can unselect the Quarterly and Annual Totals.
Changing Chart Options Using the Plus Icon
All of the chart settings that used to be on the Excel 2010 Layout tab in the ribbon have moved to a plus icon to the right of a selected chart. As shown in Figure 23.4, click the plus icon. Hover over Legend to have a fly-out appear to the right with choices of Right, Top, Left, Bottom, and More Options. If you decide you don’t need a legend, simply uncheck the option from the initial menu.
Easy Combo Charts
These charts are great when you have two different orders of magnitude in the same chart. Combo charts appeared in the Excel 2003 Chart Wizard. They were hidden on a back tab in the wizard, but at least they were there. In Excel 2007–2010, they weren’t in the wizard, but charting gurus could figure out how to create them. Now, you have an incredibly flexible interface for creating combo charts.
Figure 23.5 shows a perfect example of data in need of a combo chart. Row 2 shows monthly sales. Row 3 shows the YTD number and accumulates all the monthly sales. The problem, again, is that the height of the December YTD number forces the monthly sales line to be too small for you to actually notice any variability. Choose Insert, Recommended Chart, All Types, Combo. The new interface for combo charts enables you to identify which series should be plotted on the secondary axis.
Using the New Hierarchy Charts
Figure 23.6 shows sales and profit for a three-level hierarchy. Column A shows the meal, column B shows the category, and column C shows the actual food item. Select A2:C12 and the profit in H2:H12 and create a treemap.
The chart shows higher profit by increasing the area of the rectangle associated with the items at the lowest level of the hierarchy. Although the labels from column B are not shown in the chart, notice that all the lunch foods are grouped together on the left of the chart, followed by the lunch beverages. Food appears before beverage because it had a larger profit than the beverage sales.
Figure 23.7 shows a sunburst chart. This is good for a hierarchy chart where you have many categories. Also note that items in the hierarchy have three levels, but others, such as Beverage, are not broken out.
Creating a Frequency Distribution with a Histogram Chart
Creating a frequency distribution using the FREQUENCY array function is difficult and confusing. The new Histogram chart in Excel 2016 makes it easy. In Figure 23.8, you want to summarize thousands of points. Select the points. Choose Insert, Recommended Chart and choose Histogram.
Excel creates bins along the bottom of the chart. Because the chart doesn’t use round numbers when establishing groups, the automatic bins will be chaotic. Double-click the bins along the bottom to display the Format Axis task pane. Choose Axis Options, then the Chart icon, and specify a round number for the Bin Width. In Figure 23.8, the bin width is 10. Control the starting bin by using the Underflow Bin.
The Pareto chart rearranges the bins from the Histogram so the bins with the most values appear on the left side of the chart. The Pareto line is tied to the secondary axis and shows what percentage of the population is to the left of the bin (see Figure 23.9).
In Figure 23.9, it is difficult to get an exact reading of the Pareto line. What percentage of the data points falls within the first two columns? You must mentally draw a vertical line up the center of the second column. When it intersects the Pareto line, extend that point to the right axis. About 39% of the points fall in the first two columns.
Describe the Statistics of a Data Set with a Box and Whisker Chart
Box and Whisker charts were invented by John Tukey in 1977. In statistics, the theory is that data points are grouped around a central mean. The blue box in the chart encompasses 50% of the data points. It runs from the first quartile of your data to the third quartile of the data. The whiskers extend out 1.5 times the height of the blue box in both directions. By default, any points inside the whiskers are not shown. Only the outliers are drawn as points.
If you edit the series, you can force Excel to show the inner points as well. Figure 23.10 shows two versions of the chart. The top chart shows only the outlier points. The bottom chart shows the inner points as well.
Showing Financial Data with a Waterfall Chart
Figure 23.11 shows a profit waterfall chart that analyzes a pricing proposal. The total list price of the deal is $10 million. The sales team is offering two discounts to get the total revenue down to $6 million. Various cost components take the deal down to $1.2 million of gross profit. Sales commissions take the net profit to $1.1 million.
In Figure 23.11, four columns are marked as totals and will touch the zero axis. The other markers are either increases or decreases from the previous value. Set up your data so that the increases are positive and the decreases are negative.
The chart will always initially look wrong because Excel cannot tell which columns should touch the zero axis.
After creating the chart, double-click the first column that should touch the axis. Check the Set as Total box. From there, single-click the other total columns and choose the same box.
Saving Time with Charting Tricks
The rest of this chapter details some charting techniques that have been in Excel for several versions.
Adding New Data to a Chart by Pasting
Even though this next trick has existed in Excel since 1997, not many people know about it—you can add new data to a chart by pasting. Suppose you have a chart showing data for several months. You have nicely formatted and customized the chart. You now have new data available. Instead of re-creating the chart, you can paste the new data to the existing chart.
Follow these steps to expand the chart by pasting new data on it:
1. Make sure the new data has a heading consistent with the old data. Note that if you accidentally enter the heading as Text instead of Date, or vice versa, the trick has unexpected results.
2. Select the new data, including the heading.
3. Press Ctrl+C to copy the new data.
4. Select the chart.
5. Press Ctrl+V to paste the new data on the chart.
Dealing with Small Pie Slices
In many data series, a few pie slices take up 80% of the pie, and many tiny slices account for the rest of the pie. Typically, the last pie slices end up at the back of the pie, where it is impossible to fit the labels, so no one can make out what they are.
When you have several small data points at the end of a pie chart series, and you need to see all the smaller segments, you can change the chart type to a special type called bar of pie. In this type, the smallest few categories are exploded out and shown as a bar chart next to the pie.
To change an existing pie chart to a bar of pie chart, follow these steps:
1. Select the chart.
2. From the Type group of the Design tab, select Change Chart Type. The Change Chart Type dialog appears.
3. In the Change Chart Type dialog, select the last option for pie charts: Bar of Pie.
4. Click OK to close the dialog.
5. Double-click the bar chart. The Format Data Series task pane appears. Choose the chart icon.
6. In the Format Data Series task pane, you have control over the number of values in the bar chart. You can indicate to Split Series by Percentage Value and specify that any items less than 10% should end up in the bar portion of the chart. The result is shown in Figure 23.12.
Saving a Favorite Chart Style As a Template
Although Microsoft has provided great-looking built-in charts, you will likely design some great-looking charts of your own. After you have designed a chart, you can save it as a template. When you build new charts based on that template, all the settings for colors, fonts, effects, and chart elements are applied to the new data.
For all the power and glitz of Excel’s built-in chart styles, the chart templates feature can save you massive amounts of time, such as if you routinely customize your charts to meet company standards.
Follow these steps to create a template:
1. Build a chart and customize it as necessary.
2. Right-click the chart. Choose Save as Template. Give the chart template a name. Excel saves the template with a .crtx file extension.
To create a chart by using your template, follow these steps:
1. Select the data you want to chart.
2. From the Insert tab, choose any of the Chart drop-downs and then select All Chart Types. The Create Chart dialog appears.
3. In the Create Chart dialog, select the Templates category.
4. Click the desired template if there is more than one.
5. Click OK. Excel creates the chart with all the custom formatting from the saved template.
If you like your template so much that you want all future charts to be based on the template, follow these steps to make the template your default style:
1. Select a chart based on the desired template.
2. From the Design tab, select Change Chart Type. The Create Chart dialog appears.
3. In the Create Chart dialog, select the Templates category.
4. Select the desired template.
5. In the lower-left corner of the Change Chart Type dialog, select Set as Default Chart.
In the future, you can create a chart that uses this template by following these steps:
1. Select the data you want to chart.
2. Press Alt+F1 to apply your default template.