Excel Report Diagnostic Display

Did you ever print and distribute pivot table reports in Excel, only to discover – too late – that they weren’t showing the correct information? To help save you from wasted time and paper, and possible embarrassment, AlexJ is sharing his Report Diagnostic Display technique today.

You might remember some of his other handy tips, such as

Before you read the rest of this article, remember that the 20% discount ends today, Oct. 16th, for Mynda Treacy’s Excel Dashboard course. To get a bonus of one of my products, enter DebraD in the coupon code box.

Multiple Reports and Slicers

AlexJ sends out several monthly reports that are pivot tables, all based on the same source data. Each pivot table is on a separate sheet, and each one has a unique layout and filters.

We’ll use a simpler version for this example, with 3 reports and a diagnostics sheet. In the screen shot below you can see Report A, and its Slicer for the Animal field.

alexj_reportdiag02

Report B is slightly different and has 2 Slicers – Animal and Source.

alexj_reportdiag03

The final report – Report C – has one Slicer, and a different layout.

alexj_reportdiag04

Record the Setpoints

To create a standard set of reports each month, AlexJ needs to have specific items selected in the Slicers for each report. He created a master list of these settings, and typed them onto the Diagnostics sheet in the workbook, as Setpoints.

alexj_reportdiag05

Show the Current Selections

To avoid going to each sheet every month, and checking its pivot table Slicers, AlexJ put a copy of each pivot table onto the Diagnostics sheet. Then, he removed all the fields, except the Report Filters. All that’s left is a tiny pivot table, that almost looks like a regular data validation drop down list.

alexj_reportdiag06

The pivot tables on the Diagnostics sheet are connected to the same Slicers that are on the Report sheets, so their filters show the same selections.

alexj_reportdiag07

Find the Mismatched Filters

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

For example, the formula in cell G18 compares the selection and Setpoint in Report A:  =F18=E18

Those items are the same, so the result is TRUE. However, in Report B, there is a mismatch in the Animal field, so the result is FALSE. Conditional formatting highlights the cells that contain FALSE.

alexj_reportdiag08

Show a Diagnostic Summary

To show a quick summary of the report status, there is a Reports list at the top of the Diagnostics sheet. It uses a COUNTIFS formula, to check the number of FALSE matches for each report. The formula in cell G10 is:

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

alexj_reportdiag09

Count the Ready Reports

Finally, there are two formulas at the top of the sheet, to count the number of TRUE and FALSE results in the Reports List.

The formula in cell F5 counts the TRUE results, and F7 counts the FALSE results.

=COUNTIF(tblPrint[Ready],TRUE)

alexj_reportdiag10

Fix the 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.

alexj_reportdiag11

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

Download the Sample File

To download the file, go to AlexJ’s Sample Files page on my Contextures website, and in the Pivot Tables section, look for PT0005 – Pivot Table Report Diagnostics

The zipped file is in xlsx format, and does not contain macros.

_____________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.