Excel Report Diagnostic Display

How to make an Excel Report Diagnostic Display, to check key pivot tables, and make sure that all the filters are set correctly, before you print the workbook.

Thanks to AlexJ, who shared this technique for setting up a report monitoring sheet. It could help you save time, and prevent incorrect information from being distributed.

Excel Report Diagnostic Display

If you have several pivot table reports in a Microsoft Excel workbook, you can create a Diagnostics sheet, to make sure all the filters are set correctly, before you print the workbook.

At a glance, you can see if there are any reports to fix, and how many reports are ready.

This technique uses:

• 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

Multiple Reports and Slicers

AlexJ 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

In this example, there is a simpler report, with 3 reports and a diagnostics sheet, based on fictional data.

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

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

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

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.

Record the Setpoints

To create 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.

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 copy on the Diagnostics sheet, all the fields have been removed, except the Report Filters.

All that is left is a very small pivot table, that looks similar to a data validation drop down list.

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.

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.

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.

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

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

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)

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.

Get the Sample Files

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

Related Links

Excel Named Table

Pivot Table Slicers

Create a Pivot Table

Excel Printing Tips & Fixes