The Glory of Pivots

Surely we all know how useful Pivot Tables are in Excel by now? They are great for knocking up a filterable, drillable list of the data, but they can be much more.

For instance, it is realtively simple to create a Top 10 (or top n) list from your data in a pivot table. To my mind, this sort of table should form the basis of all dashboards, far better than those pointless guages, pie charts and stacked bar charts (but we digress!).

Anyway, as I said, a top 10 list is simple to do. Here are the instructions for excel 2003.

We will start with a simple table of data like so



which we create a pivot table from, the result looking like this.



Then we double click on the item we want to rank (or we wcould right-click and select Field Settings), Company in this case, which presents the following dialog



From here we select the Advanced button, and get the following dialog



 


Here it is a simply a matter of choosing whether the data is ascending or descending, clicking the On option of Top 10 AUtoshow, and spinning through the number to be ranked for our choice.

The results of the top 10 would then look like this




Simple, quick and effective, what more can we ask? Of course, you need to refresh the pivot whenever the data changes, but that can be automated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>