• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to apply Rank in Pivots

D

DJ

Member
Hi,


How we can apply rank in the pivots eg. if I want to arrange salesmen according to the increasing order of the sales made by them? I am using Excel 2007.


Thanks
 
Hi DJ,


Have you tried this option of placing your selection in of the cells that has sales data (sum of sales column)and then just do a sort by right clicking mouse.


Regards,

Prasad DN
 
I am doing the same, however, can a saperate calculated field can be inserted ? Or something else can be done?
 
Hi DJ,


Sorry for not understanding your question properly.


Well, surprisingly, this even i didn't knew until now, but the best part is I got the solution for you.


You were almost in right direction.


Step 1.

You have to insert a calculated field .. lets call it as Field1 = Sales and add this field to your values section.


Now you PT will show you two identical column one is your sales column and the other is your new field called Field1, both shows total sales.


Step 2:

keep your selection in one of the cells of column Field1 and goto "Calculation Tab" under Pivot table option, click "show values as" it will by defualt be showing "no calculations", select "Rank by Largest to Smallest..."


step 3:

Click ok to pop with base field as sales agent.


Step 4:

Smile to see the result :)


Regards,

Prasad DN
 
Hi Prasaddn,


Thank you so much for your efforts and the solution for my problem. However, I am unable to follow step-2 as unable to see Calculation Tab under Pivot Table Option (Tried to find Calculation Tab by right clicking on the pivot table and in the Pivot Table ribbon both).


Again to inform that I am using Office 2007. I hope you are using 2010.


Please suggest if I am something wrong.


Thanks again for your help.

DJ
 
yes, I use office 2010 and office 2007 should be same.


When you single click on PT, you will get Pivottable Tools and Options and Design tab highlighted, in options tab you will see refresh and change data source under Data frame and clear, select, and move pt under Actions frame, like wise next to it you have calculations frame.


It is just above "Field, items and sets" where you click for adding field.


Unfortunately, I dont know what the correct names for these tabs/frames/toolbar/ribbons, and I dont have option to give you screenshot.


I am sorry if I am confusing, the silver line is your problem has solution.


Regards,

Prasad DN
 
You must log in or register to reply here.
Back
Top