Add column with auto increment number in existing PIVOT table

[AlexBN]

I am uploading simple sample with my issue. I have created PIVOT and visually formatted it as I want. Now, I need to add column before first pivot column with auto increment number. I can add it and auto fill it, but it is not part of PIVOT, and also it is not colored as pivot. I am asking is it possible to add it to be part of pivot, also when I add new data in Base table, that column should be auto filled ad long as there is new rows.

[p45cal]

Attached has a table at cell H3 of the Pivot sheet which you can refresh like a Pivot table but it's not a Pivot table. I can't guarantee that the index will stay constant for a given Ime - you'd need a separate table for that (or have it in you pivot's source data).

[AlexBN]

Attached has a table at cell H3 of the Pivot sheet which you can refresh like a Pivot table but it's not a Pivot table. I can't guarantee that the index will stay constant for a given Ime - you'd need a separate table for that (or have it in you pivot's source data).

Thank you. p45cal. Your job is working. But problem is that I don't know what you did. If you can explain how does it work. Thank you and best regards.

[p45cal]

It's Power Query (aka Get & Transform Data). See the Data tab of the ribbon, sections Get & transform Data and Queries & Connections.
You can right-click the table, choose Table, then Edit Query…

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[AlexBN]

It's Power Query (aka Get & Transform Data). See the Data tab of the ribbon, sections Get & transform Data and Queries & Connections.
You can right-click the table, choose Table, then Edit Query…

Thank you. This exceeds my knowledge of the Excel. I will add it manually out of pivot table. Best regards.

[p45cal]

I will add it manually out of pivot table

Love it.

Put the string Index in cell E1 of your Base sheet
Put a 1 (one) in cell E2
In E3 put:

=IFERROR(VLOOKUP($A3,$A$2:$E2,5,0),MAX($E$2:$E2)+1)

copy down.
Extend your pivot table's source range to include column E
Add the Index field as the first item in the Rows area of the pivot table
In the Design tab choose Show in tabular form in the Report Layout dropdown of the Layout section.
Hide the +/- buttons.

See your pivot table tweaked at cell B3 of the Pivot sheet in the attached.

[AlexBN]

p45cal, thank you for helping. Unfortunately this solution doesn't help. Maybe I wasn't clear. This Pivot table is intended to show order of best player in some game. After making Pivot it should show rank list. But when I change values of average in Base table (some player get better score) this Index column behaves as ID of that player. In Pivot table I don't want that. I want to have order in Rank list. And if Slobodan is for example worst player it must be on bottom and number before him is Ranking 5 not his index which is 1

[AlexBN]

I think I found solution. I added column Sum of Average one more time and called it Rank. Than right-click on any values in that column and the rank value is created setting "Show values as" to "Rank largest to smallest", with Ime set as the Base field. Than I Sort value in column with clicking Auto sort by Sum of Average. And I got what i lookin for.
P45cal thank you for helping.