Excel Pivot table

[trianna]

Hello everyone.

I am working to extend my Excel Pivot skills and have recently purchased the Excel 2016 Bible.

An example within the book focused on grouping provides the screenshot attached example. As the example data file comes with the book I'm not sure if I can legally post the workbook example. Do let me know if this is allowed.

My question is how is Location2 automatically loaded as an available field from the table. Not sure if it would be done this way but I find no macro/vba within the work book.

I have attempted to replicate this example and similar with other data with no success. I’ve googled the question in every way I can think to phrase it with no success.

I’m almost obsessed with wanting to understand how this is done.

Any feedback provided would be appreciated.

 

[NARAYANK991]

Hi ,

Are you sure the Location2 field is not a calculated field ?

Narayan

 

[trianna]

Narayan,

Thank you for your response.


I wondered that as well and should have mentioned that I looked for the possibility by viewing the List Formulas. I found none. Please see the attached.

Trianna

 

[NARAYANK991]

Hi ,

What happens when you double-click on any cell in the Location2 field ?

Is the resulting data from one table or more than one table ?

Narayan

 

[trianna]

Narayan,

The data is from one table only. It is shown on the left of the screen shot.

As I shared the working example was focused on Grouping. Where the Location2 field is to generate the Regions groups shown in my first example. I re-created the pivot without altering the names. Please see the attached.

I have found the same in a couple of other examples within this section of the book but am mystified on how it was accomplished.

Trianna

 

[NARAYANK991]

Hi ,

The Location2 field is automatically created by Excel when you do a grouping on the Row Labels.

Suppose all the Row Labels are listed in the first column of the pivot table ; now if you select Arizona , California and Washington , and do a grouping , Excel automatically creates a duplicate field and labels it with suffixes such as 2 , 3 ,...

Narayan