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.
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:
AlexJ sends out several monthly reports that are pivot tables, all based on the same source data.
In his monthly report Excel workbook:
In this example, there is a simpler report, with 3 reports and a diagnostics sheet, based on fictional data.
In the screen shot below you can see Report A from the sample file.
In the screen shot below you can see Report B from the sample file.
In the screen shot below you can see Report C from the sample file.
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.
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.
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.
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:
Those items are the same, so the result is TRUE.
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.
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:
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:
The formula counts, based on 2 criteria
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
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:
The formula in cell F7 counts the FALSE results.
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.
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.
Last updated: January 31, 2022 3:07 PM