Analyse and understand the data
1. Drill down to see (or extract) the data behind any total
Whenever you see data displayed in a pivot table, you can easily see and extract the data that makes up the total by “drilling down”. To do this simple double-click on any cell in the pivot table.
- For example, assume you are looking at a pivot table that shows employee count by department. You can see that there are 50 employees in the Engineering department, but you want to see the actual names.
- To see the 50 people that make up this number, double-click directly on the number 50 and Excel will add a new sheet to your workbook that contains the exact data used to calculate 50 engineers.
- You can use this same approach to see and extract data behind totals wherever you see them in a pivot table.
Each time you drill down Excel will deposit the data into a new worksheet with a standard name e.g. ‘Sheet 7’. If you do this several times each time you use the spreadsheet you can end up with many extra tabs. Therefore I would recommend deleting these tabs as soon as you have finished with them. After all, should you accidently delete a drill down tab you find you subsequently need then you can easily recreate it by double clicking the original cell again!
2. Use slicers for easy filtering of data
Slicers are a visual way of filtering your data. They can be easily added to your pivot table. For many people slicers are more intuitive to use than the traditional report filters at the top of the pivot table – probably because they are a visual list.
To add slicers to your pivot table:
- From the Pivot-Options ribbon click the slicer button
- Check all field you wish to use as filters
Once the slicers are created and selected then there are options for formatting the slicers (a contextual ribbon will appear). These options are very similar to those for shapes tables and pictures. I think that the most useful thing that can be done are:
- Change the colouring
- Alignment – align tops and space evenly are the most useful
- Change the size – so that all the slicers you are using are the same height
3. Group numeric data into ranges
One of the most interesting and powerful features that every pivot table has is the ability to group numeric data into ranges or buckets. For example, assume you have a list of voting results that includes voter age, and you want to summarize the results by age group:
- Create your pivot table normally
- Add Age as a Row Label, Vote as a Column Label, and Name as a Value
- Right-click any value in the Age field and choose Group
- Enter 10 as the interval in the “By:” input area
- When you click OK, you’ll see the voting data grouped by age into 10-year buckets
4. Use a pivot table to count things
By default, a Pivot Table will count any text field. This can be a really handy feature in a lot of general business situations. For example, suppose you have a list of employees and want to get a count by department? To get a breakdown by department, follow these steps:
- Create a pivot table normally
- Add the Department as a Row Label
- Add the employee Name field as a Value
- The pivot table will display a count of employee by Department
5. Use a pivot table to build a list of unique values
Because pivot tables summarize data, they can be used to find unique values in a field. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies. For example, suppose you have sales data and you want to see a list of every product that was sold. To create a product list:
- Create a pivot table normally
- Add the Product as a Row Label
- Add any other text field (category, customer, etc) as a Value
- The pivot table will show a list of all products that appear in the sales data
Excel Monkey 