Fundamentals of Excel Pivot Tables

While the idea of pivot tables and pivot charts may seem confusing at first, once mastered, their power in summarizing and displaying data is amazing. By being persistent, and trying different ways of organizing spreadsheet data, one can learn enough to be considered a sophisticated user of Excel.

The reason why this is important, especially in crunching data of all varieties, is that by seeing the information in a novel way, via the pivoting action, undiscovered meaning or new understanding can be found. Also it can make the compilation of large amounts of material from varied sources a lot more convenient.

When a pivot table is created using the Excel “pivot table & chart wizard,” then a pivot chart can also be created.

Online tutorials and the Excel help function are invaluable at explaining the steps for creating pivot tables and charts, and good references are found at the end of this article. To briefly explain these steps, one must prepare the data to be summarized, removing automatic calculations, and create named ranges for places to add data later on.

Before using the wizard, which is reached by clicking on “Data” and then “PivotTable & PivotChart Report”, one needs to open up the workbook where the report is being created, and click on a cell in the list used for the report. In the first step of this wizard, click on either PivotTable or PivotChart, then follow the instructions in the 2^nd step. For creating the PivotTable, this consists of entering the range of target cells for the data. Finally in the 3^rd step of the wizard, click on “Finish.”

For retrieving external data for the PivotTable report, more information can be obtained by typing “ways to retrieve data from an external database” in the Office Assistant, and clicking Search.

The creative portion of the endeavor is found in doing the lay out of the PivotTable report. Before completing the final step of the wizard, click on Layout, or use the PivotTable toolbar. In this template, the various column and/or row headings are available to be dragged into specific areas of a PivotTable diagram. To rearrange the fields they can be dragged from one area to another. A field from the source information to be assigned to a row-orientation in the new report is called a “row-field.” One for a column-orientation is called a “column-field.” What’s required is the “data-field”, containing the numbers to be summarized in different ways. There are also “page-fields” and “data areas” to consider.

Further information can be found in the Office Assistant by typing “elements of PivotTable report” into the search area.

Sources:
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
and
http://www.ozgrid.com/Excel/PivotTables/