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.
