Excel 2013 includes a healthy assortment of new charting features that will save you a lot of time and help you produce better results.
Excel 2013 charts can be completed in a few minutes. Charts that used to require specialized knowledge and a lot of time and experimentation have been reduced to a few clicks. The emphasis is on generating quick and appropriate charts, from the get-go. Once you have figured out the best chart type, Excel 2013 offers new interactive options for tweaking your chart until it's perfect.
We'll step through 10 of Excel 2013's new charting features, updating a chart as we go. In the end, there's no guarantee you'll like the results. But what I can promise is that you won't spend much time creating new charts once you're familiar with these new features. You'll generate charts much quicker and spend less time regretting choices — it's just too easy to undo them and start over!
1: Recommended Charts
Choosing the appropriate chart type to represent your data's story and applying that choice requires knowledge (sometimes experience) that many users don't have. Recommended Charts takes a bit of the pain out of this process. To use this new intuitive feature, click inside the data range you want to chart, click the Insert tab, and click Recommended Charts. Excel will display a thumbnail collection of suggested chart types, as shown in Figure A. You can click each one to preview your data presented in that chart type. Figure B shows the data as a stacked column chart — the second choice for charting the data. Going this route helps you quickly zero in on the right chart type for your data. Double-click your choice to embed it.
Use Recommended Charts for a quick start on generating effective charts.
Choosing the right chart is as simple as a double-click.
This new feature is so smart that you'll want to check it out even if you're a charting expert. At the very least, it will save you time by starting with the most appropriate charts, which you can then tweak as necessary.
2: Better organization
Earlier versions of Excel offered three contextual tabs for working with charts. Figure C shows the two new contextual charting Ribbon tabs in Excel 2013: Design and Format. The Layout tab from the earlier Ribbon versions is missing. That seems like a bad thing, but it isn't—the options are better organized so finding them is easier.
Many charting options are on these two tabs.
3: Chart Elements
The new Chart Elements icon, shown in Figure D, replaces the Layout tab from earlier versions (#2). This new interface tool is probably the most versatile — the one you'll turn to the most often. To use this icon, just click it. Then, check or uncheck the chart element you want to add or delete, respectively. Figure E shows the result of removing the gridlines and adding an axis title.
Use these icons for quick access to charting options that were layers deep in earlier versions.
Quickly add and remove chart elements to customize your chart. 4: Chart Styles
The Chart Styles icon will help you change the way a chart looks by applying predefined styles. They've always been available, but you had to manually combine the different formats to get the final product. Now, they're available with a quick click. Figure F shows the new Chart Styles list and the result of applying one of the styles — that's a lot of change for a simple click. (Okay, so it isn't perfect; but that gives an opportunity to explore quick fixes!)
Apply a predefined style to reduce your formatting chores.
5: Chart Filters
The last icon, Chart Filters, lets you quickly add and remove values. In this case, it makes sense to remove the totaling rows and columns. To do so, click the Chart Filters icon and start unchecking items from the list shown in Figure G. Figure H shows the resulting chart after removing the Totals and Commissions values.
Use the filters list to quickly remove (or add) a series.
Removing the totaling values took just a few clicks.
6: Flexible data labels
Data labels are more manageable now because Excel 2013 offers more formatting and shape options. One clever option lets you easily connect them using a leader line. To see all the new possibilities, select a data label and click Chart Elements. Then, click the drop-down to the right of Data Labels and choose More Options, as shown in Figure I. (Applying a predefined style in #4 automatically added data labels to the example chart. To manually add data labels, click the Chart Elements icon and check Data Labels.) Figure J shows the options pane where I changed the format used to display the data labels' values. Specifically, I reduced the number of values and added the M character to represent millions. You can even use freeform text and anchor a label to a specific cell now.
Quick access and new options makes managing data labels easier than ever.
With just a few clicks you can reformat the values.
To learn more about the custom format used in this example, read Use a custom format in Excel to display easier to read millions.
7: Formatting task pane
For quick and easy access to an element's properties, double-click it; Excel will display that element's properties in the Formatting task pane. Figure K shows options for the chart's legend. Use the element selector (circled) to gain quick access to other elements, especially those small, hard-to-select elements.
Every element's formatting options are just a double-click away.
8: Combo charts
Combo charts require specialized knowledge in older versions. Now, they're a simple choice. After selecting your data and choosing Insert Chart, click the All Charts tab and select Combo.
We can work with our example chart, but first it needs a series that warrants a combo chart. Figure L shows a modified data range. I added a column of MAX() functions to return the largest value in each region. To add the new values to the chart, do the following:
- Select F2:F6 and press [Ctrl]+C to copy the new values to the Clipboard.
- Select the chart and press [Ctrl]+V to add the values to the chart as a new series. (You've always been able to add a new series this way.)
Add a new series to the chart.
Now, you can create a combo chart using the new values in column F, as follows:
- Select the chart, if necessary, click the contextual Design tab, and click Change Chart Type in the Type group.
- Click the All Charts tab and choose Combo.
- In the bottom pane, you'll probably have to reset the chart type for each series from clustered column to stacked column using the Chart Type drop-down for each series.
- Using the Chart Type drop-down for the Max series, choose Line (if necessary), as shown in Figure M.
- Click OK to see the combo chart in Figure N.
Select the appropriate type for each series.
This chart displays the totals as a line.
9: More dynamic data labels
The example looks a bit cluttered with all those data labels, so it's time to rethink the chart's purpose. (Aren't you glad that you didn't spend a lot of time adding them?) Let's suppose that you want to emphasize the $10,000 loss for Michaels in the West region. To do so, delete all the data labels except for that one, as shown in Figure O, as follows:
- Your first step is to move the data label you want to keep. Select it — not using a quick double-click, but two slow clicks. Doing so will select only that data label.
- Drag the single data label to the top of the plot area, out of the way.
- Select each series of data labels and press [Del]. When you come to the series that contains the -$10,000 value, you'll have to select each individually (using two slow clicks) to delete them without deleting the data label for -$10,000.
Retain only one data label for emphasis.
At this point, you can't read the label, so reformat it by double-clicking it and changing the font color to black as follows:
- Click the Fill & Line icon (top of the Format task pane).
- Change the label's font color. Perhaps the quickest way is to simply select it as you would any text and use the Font Color tool on the Home tab (in the Font group).
To truly emphasize your point, add some meaningful text. Position the cursor inside the data label and start typing. Figure P shows the addition of a simple but effective message. This technique isn't exactly new and renders your label static. If you change the value in cell D6, the label won't update accordingly.
Make a specific point using text.
There's a fix for that now. Position the cursor in the data label where you want the value to appear, right-click, and do the following:
- Choose Insert Data Label Field, as shown in Figure Q.
- In the resulting dialog, select [Cell] Choose Cell, as shown in Figure R.
- Identify the cell that contains the value (D6) and click OK.
- At this point, you'll have the previously formatted value and the formatted cell value. Delete the previously formatted value (in the data label) and keep the new one, because that field is now linked to the cell D6. After linking the data field to the cell, you can update the source value and the data label will also update – automatically. The data label isn't dynamic; the data field inside the data label is dynamic (Figure S).
Right-click the data label to make the value dynamic.
Link to the source cell.
The data field in the lone data label is dynamic.
At this point, the chart looks pretty good. It isn't finished — you might want to check out a few more predefined styles and add a chart title (we never did that, but that's nothing new). The stacked bars for the West region present an interesting problem, as there's little distinction between the personnel. But depending on your point of view, that might be exactly the look you need. Producing this chart took no specialized knowledge other than knowing how to find options via the new interface features.
10: Quick Analysis charts
Quick Analysis offers a second easy route to quick charts that are appropriate for the data. Simply select the data and click the Quick Analysis icon at the bottom-right, as shown in Figure T. Click the Charts tab to view the most appropriate chart types for your data. If you don't see the Quick Analysis icon, check your settings as follows:
- Click the File tab and choose Options from the left pane.
- Choose General in the left pane (the default).
- In the User Interface Options section, the Show Quick Analysis Options On Selection option should be checked. Enabled is the default, so you should see this icon unless someone has changed this setting.
- Click OK.