Master Station Log

techno-rants, musings, misc geek writing, and occasional caffeine binges

Author: jason

Date: 2010-02-14 12:17:07

Category: Technical

Excel 2007 has made improvements to the Pivot Tables, over Excel 2003. Another nice thing about the 2007 version you get 1,048,576 rows, 2003 only had 65,536. There are quite a few more rows and columns in 2007, which is good when working with large data sets.

Let’s start with a simple set of data to see the new Pivot Tables. A good way to summarize large blocks of information is to create a Pivot Table from them. A Pivot Table lets us Sum, Average, Count, etc the on different fields in our data set. In the next article, we’ll show building a Chart from the Pivot Table. Here we have an example of candy sales. We have the Date, the Product, Initial Cost, and the Resale Cost.

To help see what is happening on each sale, we’ll add 2 additional columns: Profit Percentage and Profit. In the formula bar for the Profit Percentage first cell type: "=C2/D2" without the quotes. This tells Excel to divide the Initial Cost into the Resale Cost. Then you need to right-click the cell, select Format Cells and in the Number window/tab select the Percentage option. Now we have our Profit Percentage for the first product. For the Profit cell enter in the formula bar: "=D2-C2" without the quotes. Format that cell to Currency. Now we have the dollar amount of Profit for the sale of that product, the Resale Cost minus the Initial Cost.

To put these formulas on all the product rows highlight both cells then with the mouse grab the right corner and drag down. Or double-click the right corner. Excel automatically knows what you want to do, apply the formula to the below cells. Excel is smart enough to know to increment each cell reference so we have the correct value for each product row, i.e. on row 9 column F the formula is correct in subtracting C9 from D9, and not referencing our original value of D2 minus C2.

Now that we have that little but of prep work done we can insert a Pivot Table. I would recommend going to Sheet 2, and starting there. While you can insert a Pivot Table or Chart into the same sheet as your data, I would recommend keeping them separate; inserting a Pivot table with often "overwrite" adjacent columns in rows. We don’t want to overwrite our data set!

On Sheet 2 go up to Insert, at the top, click Pivot Table.

In the Create PivotTable Wizard, click in the Table/Range box.

Then go back to Sheet 1 and highlight Columns A to F, then click OK

You should be rewarded with the following screen.

Click on the name Product in the PivotTable Field List, and drag it down to the Row Labels section. Grab Profit Percentage and drag it down to the Values section. Do the same with Profit. We can see that be default Excel has chosen to Count the Profit Percentage and Profit columns. Looks like the 12 piece chocolates were sold the most number of times, but did they make us the most profit?

Click the Count of Profit text down in the Values section, choose Value Field Settings.

Select Sum, click OK.

Then click on Column C to highlight all the cells in that column, right click and choose Format Cells. In the Number window/tab select Currency. Now we have a pretty good table with the number of sales and the profit sums. Looks like the 12 piece chocolate was the highest profit followed by the Reese’s. Interesting that the Reese’s was not the second highest number candy selling, that was the Stride gum. The Stride gum didn’t make much profit though.

If your data in Sheet 1 is updated and there are new rows, make sure the formulas are applied to the new rows, and on Sheet 2 right-click the Pivot Table and select Refresh. Now your Pivot Table will be updated with the newest information. The next article will be showing how to insert a Chart based on the Pivot Table.