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.
|Region||Period||Store Num||Product||Sales (units)|
|. . .|
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:
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.