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)
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)
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.
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 |
| West | 1Q08 | 127 | Diego | 14 |
| West | 1Q08 | 127 | Phineas | 18 |
| West | 1Q08 | 127 | Ferb | 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.
Comments