Thursday, August 6, 2009

The key to MS Excel Pivot Tables--

The key to MS Excel Pivot Tables--I finally figured out.
The data you start with must be in a relational type format.
NOT a typical spreadsheet format. Each row should have all the "qualifying" parameters for the data, e.g.
(Table 1)
Region
Period
Store Num
Product
Sales (units)
West
1Q08
127
Dora
27
West1Q08127Diego
14
West1Q08127Phineas
18
West1Q08127Ferb
7
West
1Q08
138
Dora
33
. . .





Select an area of data like this, select Pivot Tables, and it will work naturally.
It does NOT work well on typical hand-created spreadsheet data or human formatted reports like this:

(Table 2)
Region Period



West 1Q08




Store Num Product




Dora Diego Phineas Ferb

127 27 14 18 7

138 33 17 19 9

At least, I've never been able to get it to work.
Actually what Pivot Tables does is create a table like (2) from data like (1), but with automatic summation or averaging, and ability to summarize or drill down by clicking.

No comments: