5 Advanced PivotTable Techniques

PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.

This blog post explores 5 advanced PivotTable techniques.

  1. Grouping fields by month and year
  2. Calculating data as a percentage of the total
  3. Using Slicers
  4. Applying Conditional Formatting to PivotTable data
  5. Creating calculated fields

Continue reading

Count Unique Values in a PivotTable

When using a PivotTable to summarise a large list of data, you may need to count unique occurrences in a particular field.

For example, you may have a list of data like the one below and want to count the number of different customers in each country, or the number of different products that were sold in each country.

List of data to count uniques

If you added the Customer Name field to the Values area of a PivotTable and summarised it with a Count you would get the result below.

Counting values in a PivotTable

However this is counting all of the transactions in each country. We need to count the unique customers making those transactions in each country.

Continue reading

Ranking Fields of a PivotTable in Excel 2010

In Excel 2010 a feature was introduced to easily rank the fields of your PivotTables.

Yes, you can sort the fields of a PivotTable to view items in order from largest to smallest, or smallest to largest depending on what you are trying to achieve. But you may wish to keep your list of products, customers, salespersons or whatever the field is your are ranking in alphabetical order.

Take the table below for instance. This PivotTable displays the number of sales and conversion rate for the members of a sales team.

Sales team pivottable

We would like to add a rank field to the PivotTable for both fields.

Continue reading

Show PivotTable Report Filter on Different Sheets

The report filter is fantastic for analysing Excel PivotTable data. It allows you to filter the PivotTable using any field from your data source.

An awesome feature called Show Report Filter Pages will display each report filter result on a different sheet of your workbook. This works great when there are only a few filter criteria.

This feature always goes down well in training sessions. It allows you to quickly and easily create multiple reports on different sheets of a workbook, based on the criteria you want to use. Continue reading