My reply is only regarding pivot tables/charts and not useful for the normal users of Excel spreadsheets. Simple reply would be that a pivot table is a way to report on raw data in a summary fashion that meets your needs.

I guess I am like many of you and have advanced from beginning, intermediate, to advanced. I have created more pivot tables than I care to count. The use has been for presentation reporting purposes. A typical pivot table spreadsheet would have number of different parts. Today these parts have been replaced by the numerous reporting tools out there, especially in large organizations. Pivot charts are also possible using the same methods.

1) Get data. This would be a read against a database ( Access, Oracle, MySql, etc). The output would be placed in tab labeled "data" and might be 10-100 fields wide and 1000-40,000+ rows. This tab is generally the last tab of the spreadsheet and can be hidden. Database extracts within Excel can be easy/not possible depending on your security access and sql knowledge.

2) Macro. The get data function is generally activated by a macro button on the first tab of the spreadsheet. You can also create macros for other purposes such as menu, formatting fields, creating extracts, etc. Creating macros are generally easy if you use the Excel macro recording functions.

3) Pivot tables. Generally pivot tables enable the user of the spreadsheet to slice/dice/filter the data. Each table uses the same data to create presentation reports that have different page, column and row variables that you want to report on. There are data variables which may be sum, min, max, calculations from other fields. I generally had 10+ pivot tables in single spreadsheet, each on a different tab. They automatically refreshed every time the "data tab" was refreshed. The users of these spreadsheets, generally management, would then use them to spot trends ( positive/negative) and filter data by region, acquisition method, etc, etc. It would be the basis of "what if" analysis. Additional variables not part of the data tab can be incorporated such as cost of goods, retention rates, etc.

Sorry for the long response.