Analyzing Data with Tables and Charts in Microsoft Excel 2013

Turning data into charts

The process of building a chart in Excel is literally a two-step operation. Step one: select some data. Step two: choose a chart type. Everything beyond that is a matter of refinement, making the resulting chart more visually appealing and understandable, with titles and labels and color gradients and other tweaks.

In this whirlwind tour of the charting features in Excel, we focus on the nuts and bolts of actually building a chart. If you’re looking for detailed explanations of the concepts behind turning information into graphics, we highly recommend starting with Edward Tufte’s seminal work on the subject (see www.edwardtufte.com). And because space is limited in this chapter, we can only dive just below the surface in showing you the many options available when you create and customize Excel charts. For a much more complete picture, we recommend Microsoft Excel 2013 Inside Out, by our colleagues Mark Dodge and Craig Stinson (Microsoft Press, 2013).

With that disclaimer out of the way, we ask you to look at the simple line chart shown in Figure 13-15, which illustrates the most common chart elements.

With a little more data and a few more clicks, we could make this chart much more complicated, although that would defeat its purpose. The following list describes the main chart elements available to you in Excel:

• Data series and plot area Each series of data appears within the plot area, represented as a line, column, bar, or pie slice, depending on the chart type. You can use a unique color in the plot area to make it stand out from the chart background. Three-dimensional charts have a wall, a floor, and rotation options as well.

• Axes and gridlines Column, bar, and line charts typically plot data along two axes. Figure 13-15, for example, shows time along the horizontal axis and rainfall (measured in inches) along the vertical axis. A depth axis is available for 3-D charts. Gridlines help you compare the values in a data series to the values on an axis.

• Titles If you choose to use a chart title, you can overlay the title on the chart itself or allow the title to sit above the chart. In addition, you can add a title to any axis to help explain the data plotted along that axis. Figure 13-15, for example, might be easier to understand at a glance if the vertical axis had “Monthly rainfall (inches)” as a title.

• Legend This optional element functions as a key when a chart contains multiple data series; it typically provides labels next to the color or shape used for the corresponding data series.

• Labels You can add labels to axes to indicate what each step along the axis represents. On the horizontal axis shown in Figure 13-15, we removed the labels for the 12 data points (January through December) within each year and instead used labels to identify entire years. You can also add data labels to a data series in the plot area itself to indicate the actual values represented by plot points.

All of the tools you need to create a chart are in the Charts section of the Insert tab, shown here.

There’s nothing subtle about the way this group of commands pushes a signature feature of Excel 2013. Click the oversize Recommended Charts button on the left and you get exactly what you would expect: one or more suggestions on how to turn the current selection or region into a chart. (If you know the exact type of chart you want to use, you can click its icon from the set of eight options in the middle of the command group.)

Figure 13-16 shows one set of recommendations. The choices you see on the dialog box’s Recommended Charts tab use your real data and display an accurate representation of what you’ll get if you click OK. The exact number and type of suggestions on this tab depend on the type of data you select and how it’s arranged.

If none of the recommendations suits your fancy, click the All Charts tab to choose from the full range of chart types, as shown here.

Choose a chart type from the column on the left and then one of the subtypes from the thumbnails along the top of the preview pane on the right. The resulting thumbnails use your live data, and you can get a closer look at any chart by letting your mouse hover over the thumbnail until it zooms.

Choosing the right chart type

How do you know which type of chart is right for your data? Recommendations go a long way, and Excel’s algorithms can be spookily accurate. But, as mentioned earlier, building a chart involves equal parts art and science, and there’s nothing like your own eye (and perhaps feedback from colleagues) to help you determine whether the chart you’ve chosen is the right one for the story you’re trying to tell.

In this section, we look at the main chart types with an eye to helping you choose the right one.

Column charts

Column charts are tailor-made for side-by-side comparisons, especially over time. Available subtypes include clustered columns, stacked columns, and 100% stacked columns, with and without 3-D effects. Figure 13-17, for example, shows a clustered column chart that offers a simple comparison of revenues in four regions over four years. It’s easy to see at a glance that revenues in the North and East regions have been flat or down slightly over time, while the South and West regions have grown impressively. Note that the recommended chart includes a placeholder for a title, which we need to replace with a meaningful description.

The other main variation in this chart type is the stacked column chart, which combines data from different series into a single column that emphasizes the relationship of individual items to the total. If your data series includes several similar totals and you want to emphasize a percentage change in one or more individual components, use a 100% stacked chart.

For column charts, most of the 3-D subtypes apply visual effects only. The exception is the 3-D Column subtype, which uses the horizontal axis and the depth axis to compare series and categories equally, with values on the vertical axis.

Line charts

Use a line chart when you want to plot data over time (or along ordered categories) to show trends on a continuous scale: revenues by quarter, economic growth by year or decade, rainfall or high and low temperature by month, and so on. The horizontal axis should be divided into equal units, with no gaps. Figure 13-18, for example, shows a month’s worth of daily high and low temperatures plotted on a line graph.

Each of the 2-D subtypes in the Line Chart category includes options to show lines with or without data markers. If the number of data points on the category axis is relatively small, let Excel automatically add markers to show the exact location of each point. That makes it easier to find the value associated with each entry on the horizontal axis. You can customize the color and shape of each marker, as we explain later in this chapter.

Pie charts

If the data you want to plot is in a single row or column, it just might fit in a pie chart. Each pie chart consists of a single data series. Each data point is a slice proportional in size to the other items in the series, adding up to 100 percent. Pie charts work best when you have a small number of data points, six or seven at most, and no slice is too close to 0. (Negative numbers aren’t allowed in a pie chart.)

You can emphasize one or more slices of a pie chart by “exploding” it from the rest of the chart, as we’ve done here. Select a slice and drag it away from the pie. (This option is especially dramatic if you’ve chosen a 3-D chart type.) If you select the entire series—in other words, every slice of the pie—and drag out, you’ll end up with an exploded pie chart. This option is most relevant when you want to talk about each data point separately in order of size.

Two of the advanced pie chart subtypes are surprisingly useful when you want to tell a story within a story. It’s also a good way to create a readable chart when you have a dozen or more data points. The Pie Of Pie and Bar Of Pie subtypes let you combine two or more slices into a single slice called Other, with those data points plotted in a second pie or bar chart. Figure 13-19 shows the Bar Of Pie chart subtype.

The options for the second chart are well hidden. Right-click the bar portion of the chart and then choose Format Data Series. In the Format Data Series pane on the right, use the Split Series By options to define which pieces of the original pie are broken out into the second chart. Use the sliders under the Gap Width and Second Plot Size headings to adjust the distance between the two charts and change their size relative to each other.

Doughnut charts are similar to pie charts but can contain multiple data series, with one series inside the “doughnut hole” of the next. Excel’s Help system notes that doughnut charts are “not easy to read” and suggests stacked column or stacked bar charts as alternatives.

Bar charts

A bar chart is, in its simplest form, a column chart turned on its side, with the values on the horizontal axis and categories on the vertical axis. Bar charts are ideal for differentiating winners and losers—or at least those who are in the lead for now. Bar charts work equally well for presenting results of speed tests and for pointing out who’s in front in a fundraising competition. In Figure 13-20, for example, we could have just as easily plotted this data as a column chart, but the long school names would have been awkward to position along the horizontal axis and look more natural and readable here.

Area charts

Area charts show the magnitude of change in a data set over time and thus offer a good way to show changes in the relative contributions of different parts of a group. In their plainest form, 2-D and 3-D area charts are like line charts, except that the value between the data point and the next lowest point on another series (or the lowest point on the axis) is filled in with color.

A stacked area chart like the one shown in Figure 13-21 adds all the values together so that the highest point on the chart for each point on the horizontal axis represents the total for that point.

Scatter (X, Y) charts

Scatter charts (also known as X, Y charts) are fascinating, versatile, and often misunderstood. A scatter chart is made up of two numeric data series, plotted in pairs on the horizontal and vertical axes (which are also known respectively as the x-axis and y-axis, thus explaining the origin of the name). You can use a scatter chart in place of a line chart when data points on the horizontal axis aren’t linear; the visual result is similar, but without the distortion caused by irregular spacing of data points. One common use of a scatter chart is to identify clusters of similar data in a nonlinear set. In Figure 13-22, for example, we’ve created this chart type by plotting survey data for 15 companies, with customer satisfaction ratings on the vertical axis and price on the horizontal axis.

You’ll notice in this scatter chart that we deliberately hid the values on both axes. The numbers themselves can be on any scale you create. It’s the position of the data in this chart that matters most. The data point for Wingtip Toys represents the best value, while the one for Wide World Importers is the worst.

Bubble charts resemble scatter (X, Y) charts with an extra dimension that turns plot points into bubbles of varying sizes. As in a scatter chart, the values in the x and y series plot the location of each data point. The third value determines the bubble size.

Other chart types

The last two tiny icons in the Charts group on the Insert tab cover a hodgepodge of chart types that are useful for specialized purposes. One icon covers Stock, Surface, and Radar charts; the other lets you create a Combo chart.

Analyzing trends in the markets for stocks, bonds, and other securities was once one of the most popular uses for Excel charts. It’s less important today, with the widespread availability of online services that can create these charts for you. But if you have a set of data that includes historical stock prices, this chart type is made for you. The four available layout options enable you to plot the movement of stock prices on a daily basis, using a single line to indicate high, low, and closing prices. Opening prices and trading volume are optional data series.

Building a stock chart requires that you arrange your data in a specific order. If you try to create a stock chart using an incorrect arrangement of data, you’re greeted with a helpful message like the one shown here. (The exact content of the message changes based on the chart subtype you’ve selected.)

Surface charts are made up of two data series containing numeric data and resemble a topographic map. If you can envision a rubber sheet stretched over a 3-D column chart, you have a pretty good idea of what a 3-D surface chart looks like.

Radar charts plot data in a circular arrangement, where one set of numeric values starts at the center of the chart and a second set of ordered values (typically time) is plotted around the outside of the circle.

Changing a chart’s layout or design

After you create a chart, you can change its fundamental organization, layout, and location at any time. You can also tweak the style and appearance of individual chart elements. In previous versions of Excel, these tasks required visits to myriad dialog boxes. In Excel 2013, selecting a chart exposes two custom tabs that appear under the Chart Tools heading.

On the left side of the Design tab is an Add Chart Element menu, which displays options that are specifically designed for the type of chart you’ve selected. You might want to add labels showing the exact numbers associated with each bar, for example. To do so, click Data Labels. As Figure 13-23 shows, allowing the mouse pointer to hover over an individual option previews the effect of that option on the live chart. Click to apply the change.

Other items on the Design tab allow you to quickly change the layout of a chart, keeping its basic organization but showing, hiding, and rearranging elements such as chart titles and axis titles. The Change Colors menu lets you choose from a selection of ready-made color palettes, some bold and others muted. You can also adjust colors and fonts automatically using thumbnails in the Chart Styles group. All of these options allow you to point and preview their effects in the live chart. If you don’t like the effect, move the pointer away to preserve your existing chart choices.

For alternative access to the same controls, click anywhere within the chart to reveal three buttons at the right. Clicking any button reveals additional options, such as the Chart Elements menu shown here.

Selecting a check box adds an element to the chart; clearing a check box removes it from the chart. Allow the mouse pointer to hover over the name of an element to preview its appearance. Click the arrow to the right of any item to see a submenu with additional choices. Note that this menu is identical in function, if not appearance, to the Add Chart Element menu on the Design tab.

Likewise, the choices available when you click the second button mirror the options you can choose from the Chart Styles group and the Change Colors menu, respectively, on the Design tab.

The third and final button has the same effect as the Select Data button on the Design tab, allowing you to adjust the values and names of data series that appear in the chart.

These options work well for most charts, but there are times when you might want to fine-tune the appearance or position of a particular chart element. When that type of situation arises, you have four choices:

• Click the Add Chart Element menu on the Design tab, select an element, and then click the More element type Options menu choice below the ready-made choices.

• Click the Chart Elements button (the first one to the right of the chart, identified by a plus sign), click the arrow to the right of the element you want to adjust, and then click More Options at the bottom of the submenu.

• Click the Format tab and choose an element from the list at the top of the Current Selection group; then click Format Selection.

• If the element is visible in the chart, double-click it.

Regardless of which method you select, the tools you need appear in task-specific panes on the right side of the worksheet.

There are, quite literally, thousands of individual options available to you when you begin poking around at a granular level with individual chart elements. The pane containing formatting options is typically divided into tabs. The Format Legend pane, for example, includes the Legend Options tab (shown here) and a Text Options tab; both are in turn divided into three subgroups of settings.

Many of the commands in the formatting panes lead to collapsible menus that present common formatting options. The Format Axis Title pane, for example, shows three options you’ll find repeated in the corresponding formatting panes for other elements. From left to right, these are Fill (shown in Figure 13-24), Effects, and Size & Properties.

A surprising range of options is available if you dig deep enough. For example, a graph whose values are expressed in large numbers—thousands or millions—might benefit from having the values shown in a truncated form on the axis, so that 244,778,845 appears between the tick marks for 200 and 250. The Format Axis pane, shown next, lets you do exactly that, setting a value-based axis to show numbers in hundreds, thousands, millions, billions, trillions, or even a logarithmic scale.

The process of building a chart doesn’t have to be linear. After you get a few basic design decisions out of the way, you can revisit and refine the chart’s layout, formatting, and style options as needed, in any order, trying out alternatives until you’re satisfied.

If the iterative process turns out a collection of settings you’re especially pleased with, or if you want to share your handiwork with other people, you can save the current settings as a chart template for reuse. Right-click the chart area, choose Save As Template from the shortcut menu, and give the template a descriptive name. To apply all those settings to a new chart with one pass, click Change Chart Type on the Design tab, choose the Templates category from the All Charts tab, and pick the thumbnail for your saved template.

One final option allows you to change the location of a chart. Click Move Chart (the rightmost command on the Design tab) to see the dialog box shown here. You can position the chart on its own sheet or as an object that floats on a worksheet—typically the same one containing your source data.

Linking worksheet data to chart elements

The elements that make up a chart are, in most cases, linked directly to data within a worksheet. Series names typically come from the label attached to the column or row that provides the data series values. Axis labels and legends are also derived from source data. If you change any of the data points in the source data, the corresponding chart element is updated immediately.

You can view and edit the source data for a chart by clicking anywhere in the chart and then clicking Select Data, in the Data group on the Design tab. Figure 13-25 shows this dialog box for a chart whose data source consists of four rows (each treated as a separate data series) and four columns (each treated as a separate category).

In this example, the source data (as identified in the Chart Data Range box) is a single contiguous range. If your chart consists of selected rows or columns from a larger range or table, you’ll see each range listed separately, with commas separating the multiple ranges.

The labels above the two main boxes in the Select Data Source dialog box do not change with the chart type, which can lead to some confusing results. For example, in a pie chart, the values in the Horizontal (Category) Axis Labels box define each slice of the pie and are used for the legend, while the values in the Legend Entries (Series) box contain the values that are plotted in the chart and are not in the legend.

When you open the Select Data Source dialog box, it positions itself so that the upper-left corner of the chart’s data range is visible.

To edit an individual data series, select it in the Legend Entries (Series) list and then click the Edit button. The Edit Series dialog box, shown here, shows you which cell is being picked up as the series name and which range defines the data series. In both cases, you can see the current values to the right of the Collapse Dialog button and the equal sign.

The Add button opens the same Edit Series dialog box, with no data source selected. Click to fill in the Series Name and Series Values boxes with valid ranges and then click OK to add the new series to your chart. The Remove button completely removes the selected data series from the chart. Use the Move Up and Move Down arrows (to the right of the Remove button) to change the order of the selected data series in the list. (You cannot change the order of categories here—do that by using the Axis Options tab in the Format Axis task pane.)

With a chart that is embedded on the same sheet as the source data, you can edit chart data directly, using color-coded handles that surround the corresponding source data. If you click to select the entire chart, selection handles appear around all values listed in the Chart Data Range box. If you click to select a data series in the chart, the handles appear around the source cells and ranges associated with that series. Figure 13-26, for example, shows the result when we select the fourth and final series in a clustered column chart.

The red box indicates the data series names, the blue box indicates the series values (points to be plotted in the chart), and the purple box identifies category axis labels.

If you use a table as the source data for your chart, adding a new row or column automatically extends the corresponding series in the linked chart. If your data source is a simple range, you have to add new data manually. To do so, enter your data first, including the column or row heading, and then click the chart to expose the color-coded handles. Drag the corner of the range containing the series values so that it includes your new row or column, and then drag the series name or category axis label, as needed, to include the newly added cell.

If you find it easier to use the Clipboard, you can add a new row or column to your data source (or select an existing range that isn’t currently part of the chart), copy it to the Clipboard, and then click to select the chart and paste the Clipboard contents. Be sure to include the cell that includes the series name or category axis label, if appropriate.

You can also use the sizing handles to reduce the number of series or data points. For example, if you have a column chart that includes 12 months’ worth of results but you want to show only the last three months, drag the corresponding selections in the data source to make them smaller, using just the data you want to include.