Right-click a value, such as the Grand Total for the Arts & Photography genre, point to Sort, click Sort Largest to Smallest, and the Genres are sorted from the largest to smallest Grand Total Sales amounts.

On the Quick Access Toolbar, click Undo, to undo the sort.

You can also filter your PivotTable. It helps you focus on the data you want to analyze.

Click the down-arrow next to Row Labels. Since we clicked the down-arrow for a label, point to Label Filters.

Click an option, such as Begins With. Type your criteria, such as the letter c, and click OK. And only genres of books that start with "c" are displayed.

To remove the filter, click the down arrow again, it now looks like a funnel because a filter is applied. And click Clear Filter from "Genre".

Slicers are one of the best ways to filter your PivotTable data.

For information about using slicers, see the 4th video in this course-- Use slicers, timelines, and PivotCharts to analyze your PivotTable data.

To view only the items in your PivotTable that you want, you can select the cells that contain the items.

These can be text or dates under Row Labels. You can't use numbers.

Right-click them (Genres in this example), point to Filter, click Keep Only Selected Items, and only the selected Genres are displayed.

To show only the three genres with the highest Grand Totals, right-click a Genre, point to Filter, click Top 10-- I know, clicking Top 10 to see the Top 3 doesn't seem to make sense, but look.

Change 10 to 3, click OK, and the top three genres are displayed.

Until now, the values in the PivotTable have been displayed as the sum of the Sales Amount field, but you can use other functions.

For example, right-click a cell in the Grand Total column, point to Show Values As (there are a lot of options, such as % of Row Total and % of Column Total). Pick an option (such as % of Grand Total), and you can see that Arts & Photography represents a little over 10% of Sales and how each store contributes to that figure.

For this chart I am trying to see the top 10 "count" of a certain column. So I choose the Value filter Top 10. Great, works as you would expect.

However, on a lot of the rows, there is "N/A" as an entry, and these make up the number 1 spot on my chart as there are far more N/A than other entries. Is there a way I can display the top 10 but no count the N/A, so the 10 ten actually starts from the second most popular entry and goes to the 11th?

I have tried creating the top 10 value filter, and then filtering out N/A, but this just seems to replace the top 10 filter and shows everything apart from N/A

Hope this makes sense

Excel Facts

How can you turn a range sideways?

Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

awoohaw

Well-known Member

Joined Mar 23, 2022 Messages 4,543 Office Version

1. 365 Platform
2. Windows
3. Web

• 2

Can you post a mini workbook with your data source and pivot table with the xl2bb add in, please? It would help the forum in testing out solutions to your scenario.

grabrail

Board Regular

Joined Sep 6, 2010 Messages 128 Office Version

1. 365 Platform
2. Windows

• 3

This is a snippet of the table I am creating the pivot table/chart from

Database.xlsmABCDEF1Gatecheck IDDPU Report No.Check No.Defect FoundServiceableOCRS Score211N/ADriver seat is completely worn and needs rectificationDP25311N/ASpray Suppression is clogged in excess of 25%DP25422N/ASpray Suppression clogged in excess of 25%DP25521N/ANo Additional Defects FoundN/A0623N/ANo Additional Defects FoundN/A0724N/AOS mirror mounting bracket is structurally deterioratedDP25834N/ANS mirror mounting bracket is structurally deterioratedDP25932N/ANo Additional Defects FoundN/A01031N/ASpray suppression is clogged in excess of 25%DP251133N/ANo Additional Defects FoundN/A01235N/ANo Additional Defects FoundN/A01341N/ANo Additional Defects FoundN/A01442N/ANo Additional Defects FoundN/A01551N/ANo Additional Defects FoundN/A01652N/ANo Additional Defects FoundN/A01753N/ANo Additional Defects FoundN/A01862N/ANo Additional Defects FoundN/A01963N/ANo Additional Defects FoundN/A02061N/ASpray suppression partially blockedS02164N/ANo Additional Defects FoundN/A02265N/ANo Additional Defects FoundN/A02371N/ASpray Suppression Partially blockedS02472N/ANo Additional Defects FoundN/A02573N/ASpray Suppression partially blocked - S02674N/ANo Additional Defects FoundN/A02775N/ANo Additional Defects FoundN/A02876N/ANo Additional Defects FoundN/A02977N/ANo Additional Defects FoundN/A03078N/ANo Additional Defects FoundN/A03179N/ANo Additional Defects FoundN/A0

GateCheckDefects

and this is the pivot table created

Database.xlsmAB3Row LabelsCount of Gatecheck ID4No fleet insurance certificate in vehicle25Spray Suppression is clogged in excess of 25%26ADR paperwork out of date27N/S air leak28VTG 6 Not present new vehicle29Spray Suppression partially blocked -210More than 25% of Spray suppression is clogged with dirt311Spray Suppression over 25% Blocked412Spray suppression partially blocked713No Additional Defects Found33714Grand Total363

Sheet4

The entry I want to filter out of the top 10 on this list is "No Additional Defect Found"

grabrail

Board Regular

Joined Sep 6, 2010 Messages 128 Office Version

1. 365 Platform
2. Windows

• 4

I found what I needed, you need to go into the Pivot Table Options and allow "multiple filters" setting

How do I Unfilter a PivotTable?

To remove all report filters, labels, values, and formatting from a PivotTable, and to start designing the layout all over again, use the Clear All command. Click the PivotTable or PivotChart. On the Analyze tab, in the Actions group, click Clear, and then click Clear All.

How do you Unfilter in Excel?

If you want to completely remove filters, go to the Data tab and click the Filter button, or use the keyboard shortcut Alt+D+F+F.

How do I filter top 10 values in Excel?

Filter for the top or bottom set of values.

Click a cell in the range or table that you want to filter..

On the Data tab, select Filter..

Select the arrow. in the column that contains the content that you want to filter..

Under Filter, select Choose One, and then enter your filter criteria..

How do you unlock filters in Excel?

Click the Filter menu. Click Lock. A lock icon appears to indicate that the filter is locked. To unlock the filter, click on the menu, and select Unlock.