Back
Excel

More Charting Enhancements in Excel 2010

I’m back from my vacation (boy, Seattle is really gorgeous during the summer!).  Thanks to Sam Radakovitz (Sam Rad) for filling in for me while I was out.  And thanks to Ben Rampson and Robin Wakefield for putting together this final post on charting improvements in Excel 2010.

In this post I’ll walk through some more of the new charting features available in Excel 2010. These include:

  • PivotChart Interactivity
  • Formatting Enhancements
  • Parity Improvements
  • Limit Increases

PivotChart Interactivity

In Excel 2010, you can quickly display different data views directly on the PivotChart to visually analyze your numbers. In Excel 2007, we provided a modeless dialog which allowed you to pivot the chart but the inline buttons are a more intuitive way to do this which is why we moved back to this model.

For example, let’s look at the sales data for a bookstore chain. Below you can see a PivotChart showing the sales amount for each book genre at individual store locations. When looking at this data you can immediately see that Bellevue, which is represented by the blue column, is selling more than other store locations. Is this a normal trend? You can begin to investigate that directly on the chart by using the buttons available on the chart to filter the data down to each individual week. Just click on any button with a drop down icon to choose a filter and click OK.

All Time Periods

image

Now you can see a chart of the sales data for the week of 10/13 – 10/19.

10/13/2008- 10/19/2008

image

After filtering the data down to each individual week you can determine if this holds true across all time periods or not.

After your analysis is complete you can remove these buttons in order to create a cleaner looking report, just select a PivotChart and go to PivotChart Tools Analyze tab. You can use the Show/Hide Field Buttons command to hide specific buttons on the chart or you have the option to hide all.

image

These options as well as the ability to change where the field appears on the PivotChart are also available in the right click menu of the buttons themselves.

image

Try them out and let us know what you think!

Formatting Enhancements

In Excel 2010 we have made enhancements to the formatting options in order to improve productivity and bring back some highly asked for features.

  • Chart formatting options are now a double click away. You no longer need to use the ribbon or right click and select Format, all you need to do is double click the chart element and the format dialog will appear.
  • Selecting chart elements is now easier. We have added a chart element selector to the right click mini-bar so you can select the element right off the chart itself.
    image 
  • Pattern fills are back. You can set your fill to a variety of pattern and color options to increase differentiation between series when printing without color.
    image 
  • Macro recording is now available when formatting charts. Do you ever find yourself making the same formatting and layout changes to the majority of charts you make? Now you can use the macro recorder to record these changes and play them back. No need to perform repetitive tasks to modify the chart to your preferred look.

Parity Improvements

When upgrading the charting engine in Office 2007, there were some places where the 2007 application did not behave exactly the same as previous versions of Office charting. These subtle differences had the ability to alter the results of existing customer solutions. Much work was completed in Office 2007 SP2 to address common parity issues. In Office 2010 this work continued in the following area:

  • Axis Scaling
  • Layout
  • Visual Parity
  • Object Model Parity

Limit Increases

In Excel 2010 many of the data size limits on charts have been removed or increased to allow for larger data sets. As PCs become more powerful, we have found that users want to plot increasingly larger number of data points on their charts. Many of these requests come from scientists and analysts looking to spot outliers in huge amounts of data.

  • The previous limit of 32,000 points per data series on 2D charts has been removed. The current limit is bounded only by the available memory on the machine.
  • Charts are no longer limited to a maximum of 256,000 data points. Again, this limit is now bound by available memory and machine capacity.