**Question description:**

*This user has given permission to use the problem statement for this blog.*

I want to add ranking column in my pivot table. I used rank formulas in calculated field but it just #N/A. How to calculate ranking based on the value in pivot table?

Solved by E. W. in 44 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
30/06/2018 - 04:09

Greetings!

Excelchat Expert
30/06/2018 - 04:10

Thank you for choosing Got It Pro.

Excelchat Expert
30/06/2018 - 04:10

You need help on Pivot Table rankings, correct?

User
30/06/2018 - 04:10

yes

Excelchat Expert
30/06/2018 - 04:10

Alright. Do you have an example data that we could work on?

User
30/06/2018 - 04:10

I use spreadsheet and I don't know how to add ranking formulas

User
30/06/2018 - 04:10

yes I have

User
30/06/2018 - 04:11

I'll send you this link

Excelchat Expert
30/06/2018 - 04:11

Alright. Can you paste the data here in this open workbook we have?

Excelchat Expert
30/06/2018 - 04:11

Or just attach the file. You can do that by clicking the "Clip" icon beside the chat window

User
30/06/2018 - 04:11

I do it on spreadsheet

User
30/06/2018 - 04:11

Can I send you the link?

Excelchat Expert
30/06/2018 - 04:12

Alright, that should be fine.

User
30/06/2018 - 04:12

https://docs.google.com/spreadsheets/d/1WjD_IE-Rure2Qia8-ZpDXuh1FzwFJayaaYnDw77xvVo/edit?usp=sharing

User
30/06/2018 - 04:13

I wanna add ranking column in Sum of Revenue by each items - 5 months (All Restaurants) table

Excelchat Expert
30/06/2018 - 04:13

Okay, give me a minute.

Excelchat Expert
30/06/2018 - 04:13

I don't have access to it.

Excelchat Expert
30/06/2018 - 04:13

Can you please just save the file and upload it here?

User
30/06/2018 - 04:13

I have just gave you an access

User
30/06/2018 - 04:14

because all of my sheet link together

Excelchat Expert
30/06/2018 - 04:14

Alright

User
30/06/2018 - 04:14

Can you check again? Sorry for this

Excelchat Expert
30/06/2018 - 04:14

You want to add a ranking for each month?

User
30/06/2018 - 04:14

only for May

Excelchat Expert
30/06/2018 - 04:15

Okay, hold on.

User
30/06/2018 - 04:15

Thanks a lot

Excelchat Expert
30/06/2018 - 04:19

Alright, first, your sheet is kind of complicated. I need you to do me a favor.

Excelchat Expert
30/06/2018 - 04:19

Can you duplicate the data of the Sum of May values?

User
30/06/2018 - 04:20

behind May column?, give me 1 minutes

Excelchat Expert
30/06/2018 - 04:20

Alright.

User
30/06/2018 - 04:21

I did it

Excelchat Expert
30/06/2018 - 04:21

Okay, hold on.

User
30/06/2018 - 04:22

yes

User
30/06/2018 - 04:30

Ranking Dimension is just Rank column I did in Sheet All - R

User
30/06/2018 - 04:30

By using formulas (if you need more in formation ^^)

Excelchat Expert
30/06/2018 - 04:30

That is the ranking values we are expecting?

User
30/06/2018 - 04:31

yes

User
30/06/2018 - 04:31

But I can't display it in pivot table

User
30/06/2018 - 04:31

it's so wrong

Excelchat Expert
30/06/2018 - 04:31

I see, hold on.

User
30/06/2018 - 04:32

I wanna add ranking for 27 key category first, from 1-27

User
30/06/2018 - 04:32

and when you expand this pivot table, we can add ranking based on your cell you expand

User
30/06/2018 - 04:33

anyway, I need to rank 27 key category from 1-27 first. All - R sheet is focus on the detail only

Excelchat Expert
30/06/2018 - 04:33

Understood.

Excelchat Expert
30/06/2018 - 04:34

We're getting close. It is just the difference between Google Sheets and Microsoft Excel that I have to figure out.

User
30/06/2018 - 04:38

yup I hope so. I tried a lot for 2 days but I can't find any way to do. No filter setting, no value setting, just using formulas. It can't understand cell I indicate :(

Excelchat Expert
30/06/2018 - 04:39

Exactly, there's also a limitation on the options on how to show the values as. And also sorting...

Excelchat Expert
30/06/2018 - 04:39

But we're getting closer, hold on.

User
30/06/2018 - 04:40

Yes, thanks a lot

Excelchat Expert
30/06/2018 - 04:44

Is it okay if we sort them by it's ranking?

Excelchat Expert
30/06/2018 - 04:44

Or does it have to stay the same way?

User
30/06/2018 - 04:45

it's okay if it don't change my category group

Excelchat Expert
30/06/2018 - 04:46

You mean column A?

User
30/06/2018 - 04:46

yup

Excelchat Expert
30/06/2018 - 04:46

Oh okay

User
30/06/2018 - 04:46

the detail in each category we can change

User
30/06/2018 - 04:46

the number of this is ok too

User
30/06/2018 - 04:46

don't worry

User
30/06/2018 - 04:46

just don't change these name and item details in this

Excelchat Expert
30/06/2018 - 04:47

Got it

User
30/06/2018 - 04:47

But I still prefer to add one more column. I hope so.

Excelchat Expert
30/06/2018 - 04:48

Okay, it seems like we are really limited in the pivot table functionality of google sheets. It's unfortunate but we can't add the ranking within the pivot table.

Excelchat Expert
30/06/2018 - 04:49

What we can do is add another separate column.

User
30/06/2018 - 04:49

I got it. Just do all of things you can

User
30/06/2018 - 04:50

if we add separate column, we can use formulas like excel, right

Excelchat Expert
30/06/2018 - 04:50

Yes.

User
30/06/2018 - 04:51

I understand. And I know how it works. Thanks a lot for spending your time with me

User
30/06/2018 - 04:51

Thanks a lot

Excelchat Expert
30/06/2018 - 04:51

Alright. You could also try and change the Column Header for Column J that would make sense.

Excelchat Expert
30/06/2018 - 04:52

I'm happy to be of service. Please try and leave a feedback after you end our chat session. Please choose Got It Pro once again! Thank you!

User
30/06/2018 - 04:52

Yes. I'll do that. Hope you have a nice day

Excelchat Expert
30/06/2018 - 04:52

You too!

User
30/06/2018 - 04:53

Thanks ^^

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*