2

In Excel, it is possible to create a pivot table where data comes from several worksheets. It's an old feature, but still useful. The process starts by pressing Alt, D, P. In one of steps there is one thing which I don't understand, page fields:

enter image description here

I tried testing this feature - inserting different number of them, less than the number of referenced sheets, more than that, I tried putting page fields to different parts of pivot table, but I can't understand what is the use of this feature? Could someone knowledgeable please explain it?

  • This article from MS might be useful. The rest of the search may have something good too. I quit at the first link. – Byron Wall May 26 '15 at 16:38
  • @Byron - I need a practical user's comment. If there are features additional to that if I did not use page fields. It seems you're not a user if you use google search to find an answer. I did that too, believe me. I saw and read that linked page. I found it not useful, as you can consolidate separate sheets into one pivot both, using page fields and not. – ZygD May 26 '15 at 18:53
  • See the answer for a practical comment on this feature. To be blunt, your question comes off as "I didn't read the manual" since you posted the dialog and asked how it might be useful. And FWIW, the 7th link on that search goes to a Contexture blog which describes most of what I posted below. A little searching and experimenting goes a long way for a "user". – Byron Wall May 26 '15 at 19:56
6

I assume you are using Multiple Consolidation Ranges on the menu 2 screens before the one you've shown since I believe that is the only way to get the page selection screen.

When you do this, you are making a Pivot Table, but it is sort of a combination of the Pivot Table feature and the Data->Consolidate feature.

The page fields serve as semi-arbitrary tags on the data ranges that were used. Be aware that you have to enter a label for each page field for each range of data. For 3 ranges of data with 2 fields, you will need to enter 6 values. These values can be combined in the result table to provide some of the grouping and filtering features that would normally be available for "free" with a normal Pivot Table.

An example of when this might be useful is if you have a workbook that has different worksheets for each month of data. Ideally, you would put all of these into one table and add a month column to the single table. If this is not an option, you can use this multi-range consolidate and a lot of typing to achieve the same result.

I will show the 3 month version of this instead of the 12 month because I am lazy.

Set up and data

Let's assume we have the following (completely made up and useless) data. It describes sales for a couple of salesmen split into tables that indicate the month the data was collected. Since the data contains different columns and people and has multiple months, I would really like to get it into a more suitable format for analysis. To that end, I will create one of these Pivot Tables out of it so that I can do some analysis. Let's also assume that the column headers are names of products (B,C,E,G).

data to use

I would like to do the analysis by MONTH and QTR. (If I was using a normal Pivot Table, this would be easy with a date and the Group Field option.)

Configure that page field setup

In order to do the analysis within those groups, I need to define the page fields to accommodate it.

For each of the ranges selected, I have typed in in the relevant categorizing data. In this case, it's the MONTH and the QTR. I did this for each range and field. Here is the dialog with this options in place. Excel helps a little with the drop downs. This process is quite slow and manual. Image shows the selection for the 4th item in the list of ranges. I have already typed in values for the other 3 ranges.

dialog box

Resulting Pivot Tables, and how it might be useful

The resulting Pivot Table is not much more useful than just doing Data->Consolidate on the ranges. It contains summaries of the data by row and column. We can see though that the Page Fields have shown up in the Report Filter section. This is slightly useful, it allows me to filter to only get data for a given month or quarter.

pivot table from consolidation

Where they are really useful is when you are trying to replicate the Grouping features that you can get in a normal Pivot Table. This is done by moving the Page Fields out of the Filters section and down into the Rows or Columns section. If you do that in the order of Page2, Page1, then you will get a nice hierarchical grouping.

pivot table with fields down in the filters

The final note (emphasized above) is that you can do all of this much more quickly and without manual typing with a normal Pivot Table if it is set up properly. If you are limited by the way that data is arranged, then this might be a useful way of dealing with it in some circumstances.

  • How do you get QTR1 (consisting of FEB and MAR) and QTR2 (APR). If I create 2 page fields (QTR2 and APR) and move them into columns and rows section just like you did, I get QTR2 (APR) and Blank(Blank). – ZygD May 26 '15 at 21:10
  • You have to manually type the months and quarters for every range in the main list box. I showed the dialog for the 4th item in the list (see selection) after I already typed in values for the other 3. Select the item and add the text to dropdowns in the bottom. This constant typing is why this method is far inferior to a single table for most data sets. – Byron Wall May 26 '15 at 21:14
  • 1
    Now I got it - we need to define page fields anew for every new range that we include in order the final pivot table not to have those blanks. I know you said that already, but I only now actually understood it. Real help! Thanks! – ZygD May 26 '15 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.