Solved

# Excel - Show Top 5 Pivot for Current Year Not Total

Posted on 2016-08-05
28 Views
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
0
Question by:kwarden13
• 7
• 3
• 2

LVL 14

Expert Comment

ID: 41744608
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
0

Author Comment

ID: 41744686
Sorry I meant show top 3.

The top 3 by 2016 is different then the total
0

LVL 14

Expert Comment

ID: 41744753
Can you give me an example of how it should look?
0

LVL 14

Expert Comment

ID: 41744760
I created a table and sorted....  See attached and Table Tab.
EES-of-example_2.xlsx
0

LVL 28

Accepted Solution

Subodh Tiwari (Neeraj) earned 250 total points
ID: 41745131
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
0

Author Comment

ID: 41747417
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?
0

LVL 14

Expert Comment

ID: 41747454
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
0

LVL 14

Assisted Solution

Edward Pamias earned 250 total points
ID: 41747467
I did one pivot with years side by side.
EE-of-example_3.xlsx
0

Author Comment

ID: 41747492
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.
0

LVL 14

Expert Comment

ID: 41747493
What do you mean you were trying to get in one pivot? Once you open the file makes sure you select enable editing.
0

LVL 14

Expert Comment

ID: 41747498
You said you wanted to see side by side examples? So which one did you like better the previous file or the last sample?
0

LVL 28

Expert Comment

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

## Join & Write a Comment Already a member? Login.

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tabâ€¦
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦

#### 747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!