Excel - how to add column "id" to pivot table

Hello experts,

to generated pivot table I want to add first column which will contain {1,2,3...}. How to do that?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I'm not sure about what you are questions is..If you are looking forward for how to make a pivot table in excel then you can check following links which can help you to do that..



This can start you going for sure about how to create pivot table..

Fajer39Author Commented:

ok maybe I should ask more clearly so  I will try to explain it more.

1. You create a pivot table with all your conditions e.g you will have TOP 20 clients based on something
2. columns will be - id, client, state, purchase ...and so on
3. I want to add first column (before column ID) which will count from 1 to 20 (that's why top 20). So in first look, you'll know that 13th biggest client is Mr. X which can have client's id e.g. 235...
Saurabh Singh TeotiaCommented:
So what i understand correctly from the items you want to show top-20?? Also it will be helpful if you can post sample file to look...
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Fajer39Author Commented:
Please see the imagesample.png
Saurabh Singh TeotiaCommented:

From your screenshot i can make out much..I understand you have confidential data..just change that data to something random..like some random company names and so on..and i can look into it and see what you are trying to do ,..if you update the sample file as you can just simply apply filter i mean check or uncheck your company to see this...
Fajer39Author Commented:
Isn't that obvious what I'm trying to do? Excel file won't help you at all, you will see the pivot table as shown in the picture but without the drawing...I just need to add a column "ID" inside my existing pivot table which I don't know how to do...

It's just for an eye (better orientation) because when you have TOP 50 of something, how would you know if something is 20th e.g. without the id column...I know i can add a column manualy, but i want to have it dynamical, so that it will updates when I change the filter of pivot table for example.
Saurabh Singh TeotiaCommented:
Their you go i added a extra column id on normal dataset..You can apply this formula..in pivot as well which will give you desired results..I applied this rank on %...

Again the reason i was asking for your data-set because nothing is obvious in excel or what you are trying to do...and basically id is nothing but rank column which i added in the formula..

Fajer39Author Commented:
Ok, you can use rank() function which is good to know, but I don't need that...I just need to number the records from 1 to records.length which were created by pivot table ...I don't need to rank it, I will do that automatically when I choose the right filters+formulas inside the pivot table....but I still don't know how to do that...

I'm sorry but I don't know how differently should I explain it to you.
Saurabh Singh TeotiaCommented:
Since your data starts at row number-5 use this formula...


This will give you id as row() will give you the row where the formula is applied...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fajer39Author Commented:
Ok I can do it by "add a collumn" before the actual pivot table and apply a formula manually to all rows which was created, but is there a way how to have that column inside the pivot table?

I mean i don't understand why to do that manualy all the time, it's such a common thing...it's logical that when you will be pasting that tables to presentation or report, you need to have row numbers next to it (in case of TOPXX analysis), because it's useless without it.
Saurabh Singh TeotiaCommented:
You can't do this in pivot table and you need to do the separate column for the same...
Fajer39Author Commented:
Well, it's not problem for couple tables, but when you have a lot of them like I do, it's a pain to do that manually, especially when you're changing the lenght of pivot table a lot...

But thank you for your help, so there's no way how to add custom column to the pivot table which is not created from the source columns of pivot table? Is that correct?
Saurabh Singh TeotiaCommented:
You can't do that in pivot table unless you add additional fields in the raw-data to do the same..
Fajer39Author Commented:
Ok that's what I wanted to know whole the time. Thank You.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.