Pivot tables

Definition

A pivottable is one of the best functions to use in excel. With pivot tables you can manipulate data almost any way you want to. A Pivottable takes data and summarized the data for you. It’s difficult to explain Pivottables with a single definition. We will explain with examples.

Pivottable Example

Ok, so we have the following data from 30 different branches. We also have the four provinces in which the branches all fall.

We want is the profit per province. A pivot table will easily do this for you in about 5 seconds and the end report will look like follows:

You can even take a few branches out of some provinces if you want to see less information.

Pivot tables are actually magic. It saves a lot of time. You can save lots of time and do work that took you a week, in half an hour.

The makeup of Pivottables

So let’s show you how it works

The first thing to remember when doing pivot tables is that you must always have column headings in your data range. If even one column heading is missing the table will never work. The other nice thing about these tables is that you can stand on any cell you want to in your data range with headings, and when you go to the pivot table function it will automatically select the range, for which excel can find headings. It is better to make the range as long as possible, and then when you want to use the same pivot table next time, it will not affect the result if the data is longer.

If you accepted the automatic range which excel chose, you will be bound to these range. If you even add one line onto your data, it will not be included in the pivot table, except if you change the range.

Examples

Take the abovementioned example

Step 1: Currently we have selected F7. If we go to INSERT > PIVOTTABLE
Step 2: No range will be selected because we are currently standing on F7 and we would have to select the range ourselves. The following screen will pop up

Step 3: Our range is currently empty, because we were not standing on our range when we went into the Pivottable function. If we were standing on a cell in our data, the range would have been filled in automatically, like follows:

We were standing on C9 (which is a cell in our range), so because we had headings, the range was automatically filled in for all columns with headings.

The selected range will be $A$1:$D$31. Remember we said that to be safe, always make it bigger. So rather select the whole column A to D, all has headings, so that if you put more lines on, it will be included. So just change the range to $A:$D.

Step 4: Press OK after the range was put in. The following sheet will be opened:

Step 5: Now you must built the pivottable.

Can you see that all your headings are shown on the right? This screen will determine how your table will look. Let’s first explain what all the fields do in here

Report Filter:   If you drag something in the report filter, you will be able to select any one thing and only that information will be shown. For instance, if you put the province in there, only the province which you select will be shown. On our previous pivot table it would have looked like follows:

If you select only one province, for instance Gauteng, it will only show you Gauteng

Row and Column Fields: The row and column field would determine if your data will be horizontal or vertical. If we dragged the provinces into the columns, they would have been shown horizontally like follow:

Value Field: The value field is the values which you would like to calculate, for instance the profit. After you drag it, you can click on the name and select value field settings and select to sum, get an average or count it. It is important to note that sometimes excel counts the data automatically. It will thus look like this:

If you need to go back to the layout, click on the pivottable bar at the top or options toollbar. You need to then select the field list to see the layout again.

You can change the data and the Pivot table will get the new data. After changing the data, right click on the pivot table and click “Refresh” to get the new data in the pivot.

Just remember that if you extent the data, and the Pivottable range is not extended; the pivot table will not include this new data. If our range was only until row 31 and we added something on row 32, the new data will not be included.

To extent the range, you need to click on the pivottable icon as explained above and click on “Change data source” and extent the range.

See, this is why we said rather to select the whole column. Then when new data is added, it will always include this in the Pivottable when refreshed.

Ok, so basically that is how you generate a pivot table. Pivot tables are nice to summaries big quantities of data.

Dragging different fields in a Pivottable

When you have generated the pivot table, you stand on any field and drag it to the page, column or row fields. Try and see what the effects are of this. If we take the table below and stand on the word “province” which is currently in the row field and drag it down to the column field. Do it and see. It will look like the last table on the following page.

Pivottables may seem difficult at first, and it may be difficult to correct at first, but think of the power of this tool. You don’t even have to do reports with numbers in; we sometimes use Pivottables to just extract data. If someone gives you a list with thousands of lines, you can get the variables for the columns.

Troubleshooting

The following common problems are experienced with pivottables:

• Remember that all columns must have headings, even if it’s just a comma. It just can’t be an empty cell
• Excel will first try to count the figures when you put them in the data field, remember to change it to sum, otherwise it will not be correct
• Keep track of what you are hiding; this will stay hidden until you unhide them again.
• Make the range as big and long as possible to pick up data, when you add the next time you want to use the table

Things you can do with pivottables

• Combine multiple pivottables into one...contact us for more info
• Do your monthly reporting in minutes...contact us for more info
• Set up reports do run every month for different users with their needs...contact us for more info
• Quickly get information out of data, for example all the products we have...contact us for more info

<-----Back to index

Next topic----->

Articles

• Simple Pivot Table Trick
• Setting Up and Using Pivot Tables in Microsoft Excel
• The Fundamentals of Excel Pivot Tables
• Obtaining a Solid Grasp on Pivot Tables