Tips – Pivot Tables part two

More on Pivot Tables - Source Image: FreeImages.com / Fabio Cabrera 12/12/06
More on Pivot Tables

I have shown you how to create a simple Pivot Table in my last tip. Now I would like to show you how to expand on that.

This is the result of our pivot table in my last tip.basic pivot

Once you have this result you may wish to analyse the amounts.

Double click on one of the resulting figures in your Pivot Table. I’m going to choose Bristol Memory Board of 345.

This has resulted in the creation of a new sheet (giving it a name is is a good idea) “Bristol Memory Board”. Excel has extracted all the data that made up the result automatically.

double click result

Using Filters

Go back to the Pivot Table and now we will Use the filters already placed in the Pivot Table in the same way as we did in my tip on How to use filters in Excel effectively, you can filter out just the information you require.

Placing a field in the fourth box named Filters allows you more flexibility.

Here is an example of using the filters inside the Pivot Table. I have chosen to only see the Bristol results.

Filters

Here is the result of placing the Vendor field in the Filter area so that you can see only the results for the specified vendor across the whole.

Field area

Select the vendor of your choice. I have selected Diamond.

dimond

This is the result.

Filter result

Adding more fields

Let us now add another field to the Pivot Table. By selecting Part number this field is automatically placed into the Values area as it is a number which can be calculated. Having not changed any settings in the Pivot table this is the result.

second value

As you can see this makes no sense what so ever.

Move the Part Number field into the Rows or Columns areas. You might like to try both to see which suits your needs better.

I have settled on placing the Part Number in the Rows area as it goes with the product better than the shop.

second Row field result

Because the Part Number field is below the Product field in the Rows area the result is understandable. If you were to move the Part Number above the Product field, then this would be the result.

poor placement

Once you have filtered the information you require you can of course sort should you need to through the filter arrow.

sorting

From this you have learnt that it is very easy to change your mind as to what goes where in the filed areas, what you filter out and which order you want to see things, thus results can be very different. You need to take your time in deciding what is needed to get the results you want. You need to know your data and enjoy ‘playing’ with the pivot table.

Tips – Pivot Tables part two was last modified: by

Post Comment