Home > Pivot > Filters > Troubleshoot Pivot Table Filter Dashboard Use this Excel troubleshooting tool to check pivot table filter settings before you print a large workbook. If the dashboard shows any problems, you can quickly find and fix them.

1) Avoid Pivot Printing Problems

Have you ever printed or emailed a complex pivot table report, then noticed a problem, 2 minutes later?

Fortunately, AlexJ created pivot table filter dashboard, to help you spot pivot table filter problems, and fix them before you print.

How The Problem Checker Works

In the sections below, there are details on how to set up your own diagnostic worksheet. Here are the main components for this technique:

• Pivot Table Slicers that are connected to multiple pivot tables
• Formulas to compare the pivot table Report Filter settings to their setpoints
• NO macros are used in this technique

▶

Tip: If you don't need to read the details, head straight to the Download Section, and get a copy of the Excel file, to try it for yourself.

1.1) Multiple Reports and Slicers

Thanks to AlexJ, who shared this troubleshooting tool! AlexJ created this summary sheet, because he sends out several monthly reports that are pivot tables, all based on the same source data.

In his monthly report Excel workbook:

• Each pivot table is on a separate sheet
• Each pivot table has a unique layout and filters

To keep things simple, this example only has 3 reports, and a diagnostics sheet, based on fictional data.

a) Report A

In the screen shot below you can see Report A from the sample file.

• This pivot table has one Report filter - Animal
• It also has one Slicer, for the Animal field
• There are 2 fields in the pivot table body - Breed and Count

b) Report B

In the screen shot below you can see Report B from the sample file.

• The pivot table for Report B has two Report Filters – Animal and Source
• It ahs two Slicers - one for each Report Filter field
• There are 2 fields in the pivot table body - Breed and Count

c) Report C

In the screen shot below you can see Report C from the sample file.

• This pivot table has one Report filter - Gender
• It also has one Slicer, for the Gender field
• There are 2 fields in the pivot table body - Animal and Count

2) Video: Add Slicers to Pivot Table

In this video you can see the steps for adding a slicer to a pivot table, and then using slicers to filter the data. The written instructions are on the Pivot Table Slicers page.

3) Record the Setpoints

To print a standard set of reports each month, specific items must be selected in the Slicers for each report.

In the report workbook, there is a master list of Slicer settings, stored on the Diagnostics sheet. These settings are named Setpoints.

4) Show Current Selections

To avoid going to each sheet every month, and checking its pivot table Slicers, the Report workbook has a copy of each pivot table on the Diagnostics sheet.

• In the pivot table copy on the Diagnostics sheet, all the fields have been removed, except the Report Filters.
• All that's left is a very tiny version of each pivot table (the blue cells)
• They look like data validation drop down lists!

4.1) Slicer Connections

The pivot tables on the Diagnostics sheet are connected to the same Slicers that are on the Report sheets.

Because of that connection, these pivot table filters show the same selections as the original pivot tables.

5) Find Mismatched Filters

With the current selections and the Setpoints showing on the Diagnostic sheet, it's easy to identify any reports where the incorrect filters are selected.

For example, in the screen shot below, a simple formula, in cell G18, compares the selection and Setpoint in Report A: 

• =F18=E18

Those items are the same, so the result is TRUE.

5.1) Mismatched Filter

However, in Report B, there is a mismatch in the Animal field' filter, so the formula result is FALSE.

Note: Conditional formatting highlights the cells that contain FALSE, so they stand out on the worksheet.

6) Show Diagnostic Summary

To show a quick summary of the report status, there is a Reports list at the top of the Diagnostics sheet, in cells D9:G12.

The Reports list is formatted as an Excel table, with the name, tblPrint. In that table:

• Report names are listed in column D, with the heading Rpt Name
• Sheet names are listed in column E, with the heading Sheet
• File names for the reports are in column F, with the heading File
• Formulas in column G check if all reports are ready, with the heading Ready

6.1) Ready Check Formula

The formula in column G uses a COUNTIFS function, to check the number of FALSE matches, for each report name in column D.

Here is the formula from cell G10:

• =COUNTIFS(C:C,D10,G:G,FALSE)=0

The formula counts, based on 2 criteria

• checks the entire column C, and looks for the report name in cell D10
• checks the entire column G, and looks for the value, FALSE

The formula result is the count of any rows where those 2 criteria are met.

Note: That formula is copied down to cells G11 and G12, to check report names from those rows

7) Count Ready Reports

Finally, there are two formulas at the top of the sheet, in cells F5 and F7

Those formulas count the number of TRUE and FALSE results in the Ready column of the summary Reports List.

The formula in cell F5 counts the TRUE results, and uses a structured reference to the Ready column:

• =COUNTIF(tblPrint[Ready],TRUE)

The formula in cell F7 counts the FALSE results.

• =COUNTIF(tblPrint[Ready],FALSE)

8) Fix Report Before Printing

Before printing the reports, you can use the pivot tables on the Diagnostics sheet, to correct any mismatched selections.

For example, in Report B, select canine from the Animal field, to match its Setpoint.

Then, after all the reports have been fixed, you can go ahead with the printing.

9) Get the Sample Files

• Pivot Table Filter Dashboard -- To see the completed workbook from this tutorial, download the Pivot Table Filter Dashboard sample file. The zipped Excel file is in xlsx format, and does not contain any macros.

Related Links

Pivot Table Printing Tips

Excel Named Table

Pivot Table Slicers

Create a Pivot Table

Excel Printing Tips & Fixes