Excel - Show Top 5 Pivot for Current Year Not Total

kwarden13
kwarden13 used Ask the Experts™
on
Hi-

I have the attached workbook and would like to show top 5 for the current year not total. For example, in current workbook the top 5 for 2016 is company 4 , company 3, company 5.

However, when doing the top 5 the following are shown: company 2, company 5, company 1.

Can this be done in the pivot?
example_2.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Commented:
Do you want it to look like this??

Client      (All)      
            
Count of Sales      Column Labels      
Row Labels      2016      2015
Company 5      3      3
Company 4      3      
Company 1      3      4
Company 3      3      3
Company 2      2      4

Author

Commented:
Sorry I meant show top 3.

The top 3 by 2016 is different then the total
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Commented:
Can you give me an example of how it should look?
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Commented:
I created a table and sorted....  See attached and Table Tab.
EES-of-example_2.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
I have inserted another pivot table along with a slicer to pick a year. See if this is what you are trying to achieve.
example_2.xlsx

Author

Commented:
Hi Subodh-

This is what I want except now I want to be able to see how they did year over year. So its fine to select the year but how do I compare to 2015?
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Commented:
Check out sheet 2. I added the top 3 for 2016 and 2015 and a name range so when you add more data just refresh the pivot and it will up date the top 3.
EE-of-example_2.xlsx
Edward PamiasTeam Lead RRS Desk
Top Expert 2016
Commented:
I did one pivot with years side by side.
EE-of-example_3.xlsx

Author

Commented:
I was trying to get it in one pivot. I am guessing that is not doable. In the last example, it shows top 3 by total spend (for years 2015 and 2016). Not just the latest year.

It's odd you can't select which year.
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Commented:
What do you mean you were trying to get in one pivot? Once you open the file makes sure you select enable editing.
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Commented:
You said you wanted to see side by side examples? So which one did you like better the previous file or the last sample?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Considering the ask was not doable, the possible alternatives were shown in the Post ID: 41745131 and Post ID: 41747467.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial