How to get the best out of pivot tables

I believe Pivot Tables are one of the best features in Excel.  For me they are the single most powerful tool in Excel and easy to use. Pivot tables come with some fantastic features such as slicers and the ability to link to Excel tables.  

Pivots tables are an easy way to analyse and summarise large amounts of data. There are easy to refresh as your data changes and come with some built in ways to visualise the data such as table formats and charts.  As with many other aspects of excel you can customise formats and many aspects of pivot tables to suit your purposes.  There are also several ways to interact with your pivot table such as real time updating of the table as you drag a field from a column to a row or a filter.  Slicers are also another excellent way of filtering the data presented.

This ExcelMonkey masterclass is a list of useful features that come with pivot tables rather than a step by step guide.  It highlights some ways to get the best out of your pivot tables by looking at the key features and ways to use pivot tables. I’ve grouped these tips into basics; Formatting; Analysis; Advanced and other features. 

This is one of a series of Excel Masterclasses that I’ll be posting on the ExcelMonkey.org website. 

This masterclass is a list of useful features that come with pivot tables rather than a step by step guide and will cover

1. Basics.
   • You can build a pivot table in about one minute.
   • Perfect your source data
   • Plan before you build.
2. Formatting.
   • Auto formatting
   • Drag and drop fields to new positions in your pivot table
   • Rename fields for better readability
   • Add a space to field names when Excel complains
   • Format empty cells
   • Tabular format is a neater way of  displaying data with multiple descriptive columns
   • Add a little white space around your pivot tables
   • Turn off AutoFit when necessary
   • Get rid of useless headings
   • Get rid of row and column grand totals
3. Analyse and understand the data
   • Drill down to see (or extract) the data behind any total
   • Use slicers for easy filtering of data
   • Group numeric data into ranges
   • Use a pivot table to count things
   • Use a pivot table to build a list of unique values
4. Advanced features.
   • Use calculated fields instead of adding columns to source data.
   • Use a table for your data to create a “dynamic range”.
   • Create a self-contained pivot table.
   • Clone your pivot tables when you need another view.
   • Unclone a pivot table to refresh independently.
5. Other features.
   • Automatically format all value fields.
   • Add a field more than once to a pivot table.
   • Count the data first.
   • Group a pivot table manually.
   • Show totals as a percentage.
   • Using a pivot table to link two files.