Pivot tables allow the user to quickly "pivot" from one table set-up to another and are an excellent way to quickly summarize/group and organize large amounts of data.
Excel & Pivot
It is highly recommended that your Pivot Table be based off of an Excel Table, as this makes the source data a dynamic range. This means that if columns or rows are added to the source data (the Excel Table), the corresponding Pivot Table needs only to be refreshed, in order for those fields or rows to be available in the Pivot Table and Pivot Table Fields.
To format the source data as an Excel Table, choose to either:
a) HOME >> Styles group >> Format as Table
b) INSERT >> Tables group >> Table
The next step is to take this source Table and create a Pivot Table from it. This can be done by either:
a) TABLE TOOLS DESIGN >> Tools group >> Summarize with Pivot Table
b) INSERT >> Tables group >> Pivot Table
Pivot Table Basics
1) Change the layout of the Pivot Table to "Classic" in order to view it in a more intuitive fashion
- PIVOTTABLES TOOLS ANALYZE >> Pivot Table group >> Options >> "Display" tab >> Classic Pivot Table Layout
2) Use the drag and drop method to create the Pivot Table based off the following parameters:
- Rows: Used to select categories of data to be listed vertically
- Columns: Used to select categories of data to be listed horizontally
- Values: Used to quantify the row/column item value
- Filter: Used to sort/filter the row data
3) One Industry Standard is to that a Pivot Table must have a value in every cell; including a zero value in any empty cells
- PIVOTTABLES TOOLS ANALYZE >> Pivot Table group >> Options >> "Layout and Format" tab >> For empty cells show "0"
4) A second Industry Standard is to enable Banded Rows to all Pivot Tables, to ensure the data can easily be read
- PIVOTTABLES TOOLS DESIGN >> PivotTable Style Options group >> check "Banded Rows"
5) To format the values in the Pivot Table:
- PIVOTTABLES TOOLS ANALYZE >> Active Field group >> Field Settings >> Number Format
Learn More About Excel
Master Excel through our Microsoft Office courses and Excel training in NYC. Sign up and start creating smart and efficient spreadsheets today!