Many people worry that creating a PivotTable is time consuming, but if you have a well-organised data source, you can create a PivotTable in a snap.
Tables and PivotTables are a great match! When you use a table as source data for a PivotTable, Excel will automatically expand and shrink the table as you add or remove data, so your PivotTable will always stay in sync with your data.
You might think you have to be working with numbers to use a PivotTable but, by default, a PivotTable will count any text field.
When you've created a PivotTable from data in the same worksheet, you can remove the data if you like and the PivotTable will continue to function. Each PivotTable has a pivot cache that contains an exact duplicate of the data used to create the PivotTable.
Although PivotTables can automatically group data in many ways, you can also group items manually.
One of the most powerful features of PivotTables is the ability to group data by numbers. You can use this feature to group by age range, price range, or any numerical range that makes sense in your data.
When you add fields to a PivotTables, the PivotTable will display the name that appears in the source data. Value field names appear with "Sum of" or "Count of" at the start.
It may seem counterintuative, but you can add the same field to a PivotTable more than once. In fact, there are situations where you'll want to do just that.
Anytime you add a value field to a PivotTable, make sure you set the number format on the field itself.
Anytime you see a subtotal or grand total in a PivotTable, you can easily get to the exact data that makes up that value using the "drill down" feature built in to any PivotTable.
Ian Littlejohn worked as a Management Consultant for 15 years and is passionate about teaching business people to analyze data and understand business data.