Weekly Computer Tip # 399
19 July 2013

Last updated 3 April 2024 - added warning how (not) to send a PivotTable report to a third party

See the data behind a specific value in a PivotTable report (Excel 2003-2013)

If you use Excel as a database (or rather: 'list' as it is referred to in Excel) I'm pretty sure you love pivot tables, as it can help you to quickly retrieve and manipulate your worksheet data in numerous ways. But what I often see is that people use PivotTable reports as a simple, static summary, yet it can be used to interactively analyse the underlying data. For example, you can quickly display the data 'behind' any figure in your report. Obviously, you can use the standard filter functionalities in the source data. But all you need is two clicks.

Here's how:

1. Double-click any of the values in the PivotTable report.

A new worksheet is created, displaying the related records from the source data. If you are already using version 2013 the new Quick Analysis button will appear next to the extracted information, allowing you to quickly and easily view your data with conditional formatting (colours and icons), sparklines, or charts.

NOTE: When you create a PivotTable you simply hide the data. It even comes along when you copy and paste the report in a separate workbook. Worse ... when you send it to someone else, the recipient can also use the double-click trick to reveal the underlying data. Even if you did not send the file! See tip 740 for two safe ways to share a PivotTable report to a third party.