Category Archives: 11532

Keeping It Clear

It is not my intention for EDDD to be another ‘Excel Tips & Tricks’ blog, there are plenty of good examples of these already, but I saw someone struggling in a demonstration recently so I am thinking that this technique is not as well know as I had presumed, nor as it should be. Therefore I shall share it with you all.

Firstly, I hope we all agree that the Pivot table Field List in Excel 2007 is a big improvement over earlier versions. However, sometimes the cost of a nice facility is that comes at the cost of a lot of real estate, the field list is quite big. And it is rooted to the right of the Excel window. Or is it?

Actually, you can tear it off, move it and resize it.

Move the cursor towards the top of the field list until the cursor changes to  a cross with arrows at all points



At this point, you can tear off the field list using the mouse left button, and drag it off to wherever you want it on the screen, and resize it to whatever size is good for you.

This is so much easier than scrolling the data and pivots around so that the field list does not obscure the point being made.

To restore it to its original position, just double-click in the title bar.

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.