Tuesday, 22 October 2013

Using pivot charts in Excel 2013

Charts are a method for visual storytelling. You may not judge a story by its cover, but a great cover (or chart) will absolutely draw in more readers to engage with the rest of your content.

Themes and Chart Colors are great choices for quick-and-easy customization: just a couple clicks and you're done.

Getting started with a combo chart

Consider I want to have a chart from a flight that was delayed, so the story I'd like to tell is about the best flight choices from Detroit to Seattle, based on average delays, maximum delays, and historical on-time performance.
To do that, I created the combo chart below. I edited the Chart Title by triple-clicking on it.
  • I added Axis Titles to all axes by clicking the Chart Elements button (the + symbol) and checking the Axis Titles box.
As it stands, this is a perfectly workable chart. It's clean and readable, and it conveys a good amount of information. But it's not quite the chart I'm looking for.

Using Chart Colors

Specifically, that orange is a bit bright and I want to change it
  • Select the chart by clicking on it.
  • On the Ribbon, click the Chart Tools Design tab, then click the Change Colors button.
  • Click a set of Chart Colors.
With just a few clicks, I've toned it down a bit. Note that this button only affected the chart I selected; the colors in the rest of my workbook are just as they were before.

Using Theme Colors

Now, if none of the colors in the Change Colors button catches your eye, changing your Excel workbook's theme will give you even more color choices. To do so:
  • Select the chart by clicking on it.
  • On the Ribbon, click the Page Layout tab, then click the Colors button.
  • Click a set of Theme Colors.
Do keep in mind that changing the Theme Colors affects your entire workbook (including other charts and theme-dependent objects like SmartArt).

Using Chart Styles

Chart Styles are a great way to go a little beyond just changing the colors of your chart - they affect more of your chart than the Chart Colors button does, but they're still one-click easy. One of them is the dark style, especially if you're going to use your chart in a PowerPoint presentation with a dark background. To apply a Chart Style:
  • Select the chart by clicking on it.
  • On the Ribbon, click the Chart Tools Design tab, then click a Chart Style from the gallery.
Something to remember: Chart Colors and Theme Colors both impact Chart Styles - each Chart Style will change based on your selected Theme and Color set.

Using the Task Pane to customize your chart

It's entirely possible never to use the Task Pane. For some charts, Chart Colors, Theme Colors, and Chart Styles will take care of the stylistic changes needed. But for charts that need more customization, the Task Pane is the place to look.
You might have noticed that the dark chart in the previous section had several changes: the lines became thicker, they acquired shadows, and the background became a radial black/grey gradient. Those changes are just some of the customizations that you can make in the Task Pane. Anything from the pattern of your lines and bars (e.g., dashed or shaded) to the content of your data labels can be changed in the Task Pane.
To open the Task Pane, just double-click on any chart element such as a series or the title. In this case, I double-clicked on the blue Max Delay line.
You also can choose an element and open the Task Pane from the Ribbon. To do so:
  • Select the chart by clicking on it.
  • On the Ribbon, click the Chart Tools Format tab, then use the dropdown menu to select an element.
  • Click Format Selection.

Changing individual colors

To start out, I'm going to change the color of my max delay series. Visually, this adds contrast and highlights the series. In addition, changing the color adds a semantic meaning - delays in the US are generally shown in red--that helps chart tell its story. To do that, I can:
  • Click the Fill & Line tab (the paint bucket).
  • Click the Line sub-heading.
  • Click the Color paint bucket to select a red color for the line.

Adding and styling data labels

Next, I'd like to call out that high point on the Max Delay series, because the potential for almost 500 minutes of delay is something I'd like to avoid! To do so
  • Select the Max Delay series by clicking on it.
  • Add Data Labels by clicking the Chart Elements button (the + symbol), checking the Data Labels box, and using the flyout menu to switch to Data Callouts.
There's a bit more information in the callouts than I need. I'd like to remove the flight number, because that information is already clear from the horizontal axis; all I want is the length of the delay. To achieve this, I can:
  • Select the data labels by clicking on one.
  • In the Task Pane, click the Label Options tab (the bar chart icon).
  • Click the Label Options subheading to expand it.
  • Uncheck the Category Name box.
Then, to delete some individual data labels until only the high and low point labels are left, I can:
  • Select all data labels by clicking on one.
  • Select an individual label by clicking on it after all data labels are already selected.
  • Delete the selected label by pressing the Delete key or right-clicking and choosing Delete.

Additional effects

Chart is almost telling the story I want it to tell. For some last effects, I'd like to thin the columns and thicken the Max Delay series line. To thin the columns, I can:
  • Select the On-time % series by clicking on one of the bars.
  • In the Task Pane, click the Series Options tab (the bar chart icon).
  • Change the Gap Width to 410% by typing or using the slider.
To thicken the Max Delay series line, I can:
  • Select the Max Delay series by clicking on it.
  • In the Task Pane, click the Fill & Line tab (the paint bucket icon).
  • Select a Width of 4 pt by typing or using the arrows.
  • Select a Join type of Miter (which gives each bend the more angular appearance) by using the dropdown menu.

Manual element positioning

For one final change, I'd like to move the data label callouts so they don't overlap my series. To achieve this, I can:
  • Select all data labels by clicking on one.
  • Select an individual label by clicking on it after all data labels are already selected.
  • Click-and-drag the labels to a better position.

The final chart

Now that all the changes have been made, here's my chart. Compared to my original chart, it tells a better, more visually appealing story; and, it helps me make a decision: Flight 5, while it's frequently on time, has higher-than-average delays and a poor max delay record, so Flight 2 would probably be a better choice.
For more details on the Excel charting features mentioned, check out these related topics:

3 comments:

  1. There is noticeably much to experience this. I believe you made several well points in Features as well. http://www.lowhosting.com/justhost-review/

    ReplyDelete
  2. It is amazing and wonderful to visit your site. I've learn many things from your site.

    Format your Pivot Chart

    ReplyDelete
    Replies
    1. My pleasure. Hope you have a great year ahead!

      Delete