Excel 2007 and Pretty Charts
Date: 2010-02-14 16:32:27
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.
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.
Go up to Insert and choose Column Chart. Click the first chart, Clustered 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.
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.
I personally do not like the legend on the right side. Right-click the legend, and choose Format Legend.
Choose Bottom, then click Close.
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.
Then right-click a blue column and choose Add Data Labels. Both columns now have the summary numbers.
If you donít like the 2-D Clustered chart you can easily change it. Right-click the chart and select Change Chart Type.
Select 3-D Cylinder from the choices presented, click OK.
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.