How does one ensure only relevant data points are included in a pivot chart in Excel?

Question

I'm currently working with pivot charts in Excel for the first time and I'm having a bit of trouble with it. The following image provides an example of what is not going entirely according to plan:

This is a graph of revenues of different pharmaceutical companies throughout the years. I think it's too cluttered, especially towards the bottom of the page. I therefore wish to include only the top 10 of companies in the graph, where the position of the company in the list is determined by the total revenue of the respective company throughout all of the years.

I wonder how I can automatically depict those companies in the chart that are in this top 10, and that only the legend items for these companies are shown on the side. Is there a way to do this?

Note I: the graph is a pivot chart, which is based on a pivot table with the years as the row labels and the companies as the column labels. The data entries are the yearly revenues of the corresponding companies.

Note II: when I right click on the pivot chart and click “Select Data...”, I can't remove any legend entries (series), which in this case are the companies (of which I'd like to remove some).

Answer 1

Slightly awkward way to do this, but it works. You basically want to define a process for knowing if the company is in the top 10 so that you can filter on it within your pivot table. My data has the following:

• Columns A, B and C as the year, company and revenue.

• Column G is the de-duped company.

• Column H is the total revenue for that company. Formula for H2, copy it down:

   =SUMIF(B:B,G2,C:C)
  

• Column I is the revenue rank for that company. Formula for I2, copy it down:

  =RANK.AVG(H2,H:H,0)
  

• Column D is a vlookup to column I. Formula to D2, copy it down:

   =VLOOKUP(B2,G:I,3,FALSE)
  

• Column E is an if statement where you define your top-X (I'm using top 3, but you should use top 10):

   =IF(D2<4,"YES","NO")
  

Lastly, create your pivot table on columns A through E. Filter on your Top X to "YES".