Excel 2007 and Pretty Charts
Author: jason
Date: 2010-02-14 16:32:27
Category: Technical

Letís start with a simple set of data to see a new Chart. Here we have an example of candy sales from our last article. We have the Date, the Product, Initial Cost, Resale Cost, Profit Percentage and Profit in Sheet 1. The last two columns are Formulas based on cells in columns C and D.

excel 2007 chart



In Sheet 2 we have our Pivot Table, highlight from the cell A2 (Row Labels) down to cell C8, the bottom right of the Snickers row. This is entirely necessary, but if you are inserting a Chart on a non-Pivot Table the chart should automatically pick up on the row and column labels by highlighting them and the values. Excel is smart enough if we just click somewhere in the table for inserting a chart based on a Pivot Table. But highlighting is an okay habit.

excel 2007 chart



Go up to Insert and choose Column Chart. Click the first chart, Clustered Chart.

excel 2007 chart



Here we can see the chart pops up over the top of the Pivot Table. You can click on the chart and drag it off the Pivot Table, if you like. Each Product is represented by 2 columns. The first column (in blue) is the Count, and the second column (in maroon) is the Sum. I like these charts already. In Excel 2003 the charts had a default background of gray. These new charts have a default that is white, which is much nicer.

excel 2007 chart



If you notice you have a space for a column at the right which is titled (blank). If you look in your Pivot Table you will notice there is a (blank ) labeled row there too. To remove this row go back up to the Pivot Table, click the down arrow next to the Row Label section and deselect the (blank) row checkbox. Click OK, the blank row will go away and the chart will update too.

excel 2007 chart



I personally do not like the legend on the right side. Right-click the legend, and choose Format Legend.

excel 2007 chart



Choose Bottom, then click Close.

excel 2007 chart



Now you can add the Data Labels to the columns. This will let you see the summary values for each column. Right-click one of the maroon columns and choose Add Data Labels.

excel 2007 chart



Then right-click a blue column and choose Add Data Labels. Both columns now have the summary numbers.

excel 2007 chartexcel 2007 chart



If you donít like the 2-D Clustered chart you can easily change it. Right-click the chart and select Change Chart Type.

excel 2007 chart



Select 3-D Cylinder from the choices presented, click OK.

excel 2007 chartexcel 2007 chart



This article was a short example of a simple chart which visually shows how each product has done, for volume of sales and profit summary.



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009