Tame your computer - look under the table!

In her regular series for Cambridge Network members, software training expert Karen Roem offers handy tips to help you 'Tame your computer'. This week she describes how to see the data behind a specific figure 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 they 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 they 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 figures 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.

With special thanks to my accountant – who was asking me for some additional information – for the inspiration for this week’s tip!


PS I'm committed to sharing the best of what I know with others so please don't keep these tips a secret. If you enjoyed today's tip, please forward it to anyone you feel may benefit or reprint it (with full copyright and subscription information) in your newsletters and message boards. Got a tip to share? Submit it by emailing karen@roem.co.uk - if your tip is selected, I'll publish it along with your name; the bragging rights will last a lifetime.


22 July 2013

Karen Roem offers software training and support through her company Roem Ltd.  Contact her by email (Karen@roem.co.uk) or visit her website at http://www.roem.co.uk

______________________________________________
 

 






Looking for something specific?