Chapter 23
Graphing Data Using Excel Charts

Between Excel 2016 and Excel 2019, two new charts were added to Excel: the Funnel chart and the Filled Map chart. There was an announcement at a Microsoft conference in May 2018 that Excel would soon start supporting the Power BI Custom Visuals. This functionality is exclusive to Office 365. Take a look at three examples at the end of this chapter.

Before getting to the new charts, the chapter covers general techniques for creating graphs.

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 box. 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 Troubleshooting 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.

   

   Troubleshooting

   Excel plots the chart in the middle of the visible screen. By selecting your data, the visible portion of the window is often at the bottom of the worksheet instead of the top.

   Suppose you have data in A1:C500 that should appear on a chart. You would probably start in A1, press Ctrl+Shift+Down Arrow, and then press Ctrl+Shift+Right Arrow. You would end up with rows 490 through 520 visible on your screen. However, if you insert a chart, it would appear at the bottom of your data instead of at the top of your data. Instead, press Ctrl+Backspace before creating the chart, which brings the active cell back into view.

Using the Paintbrush Icon for Styles

Click the paintbrush icon to reveal a flyout 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 2019 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 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 flyout 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.

Creating a Frequency Distribution with a Histogram Chart

Creating a frequency distribution using the FREQUENCY array function is difficult and confusing. The Histogram chart in Excel 2019 makes it easy. In Figure 23.6, 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.6, 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.7).

In Figure 23.7, 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 percent of the points fall in the first two columns.

Showing Financial Data with a Waterfall Chart

Figure 23.8 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.8, 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.

Changing the colors in the chart is particularly hidden in the Ivy charts. To change the color, follow these steps:

1. Click once on the legend to select the whole legend.

2. Click a second time on one individual legend entry to select one type of data point.

3. Right-click the legend entry and a Fill drop-down list appears, as shown in Figure 23.9.

   

Saving Time with Charting Tricks

Here are 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.

Inside OUT

Building X Y Scatter charts with two series is particularly difficult. You can create the chart using a Paste Special.

In the first image below, a scatter chart has been created from B1:C21. Follow these steps to add the second population:

1. Select the X and Y values with headings from G1:G7.

2. Copy that data to the Clipboard.

3. Click the chart to select it.

4. From the ribbon, select Home and open the Paste Options drop-down menu by clicking the bottom of the Paste icon. Do not choose the icon for Paste; instead, click Paste Special.

5. The Paste Special dialog offers to Add Cells As New Series. The Values (Y) In option is already set to Columns, and Series Names In First Row is already checked. However, you will need to choose Categories (X Values) in First Column. Leave Replace Existing Categories unchecked.

6. Click OK to add the second series to the chart.

7. Use the plus icon at the top right of the chart to add Legend and Axis Titles.

   

Two series appear on a scatter chart. One shows tenure earnings versus earnings for hourly workers. A second series shows the tenure earnings versus earnings for salaried workers.

Note that if you are willing to reorient your data so you have adjacent pairs of columns, you can create the chart by using the All Charts tab of the Insert Chart dialog box. Choose X Y (Scatter) category from the left navigation bar and then choose the top-right tile in the preview area. The tile does not have a name, but it will show two series of dots instead of three series.

The Insert Charts dialog box offers three preview tiles for X Y Scatter charts. Choose the top-right tile to treat each pair of columns as a series.

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 columns 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 box appears.

3. In the Change Chart Type dialog box, select the last option for pie charts: Bar Of Pie.

4. Click OK to close the dialog box.

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 percent should end up in the bar portion of the chart. The result is shown in Figure 23.10.

   

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-down lists and then select All Chart Types. The Create Chart dialog box appears.

3. In the Create Chart dialog box, 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 box appears.

3. In the Create Chart dialog box, select the Templates category.

4. Select the desired template.

5. In the lower-left corner of the Change Chart Type dialog box, 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.

Office 365 Will Soon Support the Custom Visuals from Power BI

The Power BI product from Microsoft offered support for open-source visuals submitted by developers. These custom visuals have become popular in Power BI. As of September 2018, Microsoft is encouraging developers to start preparing for these visualizations to be available in Excel.

Think of each visualization as an add-in. You will use the Excel Store on the Insert tab to find and download custom visuals to your computer.

Once the visualizations are downloaded, you can access them using the plus icon in the Insert Charts area, as shown in Figure 23.11. First, select your data for the visualization, and click the plus icon. Choose a visualization from the Custom Visuals tab on the Insert Chart dialog box.

One example of a custom visualization is a Word Cloud. In Figure 23.12, the words from the table of contents for this book are arranged in a visualization.

A Charts pane appears on the right side of the screen when the visualization is selected. The formatting tools will be specific to each visualization. For example, the Word Cloud visualization in Figure 23.12 changed the default settings to specify a minimum angle of -30 degrees, maximum angle of +30 degrees, and no more than seven orientations.

A Sankey chart shows the flow of material from various cities to another (see Figure 23.13). This type of chart, with two sets of labels, would be very hard to build into the current Excel chart model. However, by allowing custom visualizations, it is possible.

One inherent complication with these custom visualizations is that you need the add-in installed on your computer to display the chart. If you send the workbook to others, they might not have the same add-in. If you click the top-right of the visualization as shown in Figure 23.14, you can access a menu to show the visualization as an image.