A report you can trust next month, too

Start with the question, not the chart

Before opening a new dashboard sheet, write down the question the report needs to answer. For an illustrative training attendance report, that might be: which programmes ran this month, how many registrations were recorded, and which records still need a status update?

Each question implies a definition. Decide whether a person registered for two programmes counts as two registrations or one unique participant. Decide how cancellations and missing statuses should be treated. A formula can be technically correct and still answer the wrong question.

Keep an unchanged source

Retain the original export or approved input file so that the report can be checked later. Avoid manually replacing source values simply to make a chart look right. Corrections should have a clear reason and an identifiable place in the process.

For a record table, use one row for one agreed type of record and one column for one field. Give columns meaningful names. Keep titles, subtotals and explanatory notes outside the data range used for analysis. Consistent structure makes the next refresh easier to handle.

Make preparation a visible step

Dates, numbers and category names often arrive in inconsistent forms. Review those issues before writing the final calculation. In the attendance example, “Completed”, “complete” and a blank status should not silently become three unexplained categories.

Keep a small set of preparation rules: how dates are interpreted, how missing values are handled and which records qualify for the report. Where the same preparation happens repeatedly, a suitable Power Query workflow can make the transformation steps easier to repeat. The method still needs to be checked against the source.

• Inspect missing identifiers and required fields.
• Check whether number and date columns contain text.
• Review possible duplicates against the definition of a record.
• Document category changes rather than making unexplained replacements.

Build checks alongside calculations

A total becomes more credible when someone can explain where it came from. Keep a reconciliation between source records and the records included in the report. If exclusions are intentional, make them visible.

Test a few cases that can reveal mistakes: a missing value, a new category, a duplicate identifier and a record outside the selected period. For a lookup, decide what the workbook should show when no match exists. For a percentage, make the denominator clear.

Design the summary around the reader

Use a PivotTable, a simple table or a chart according to the question. A table may be the best way to show exact values. A chart can help show a trend or comparison. Neither becomes more useful by adding more colours, axes or effects.

State the reporting period, the meaning of important measures and the date of the latest data. Keep number formats consistent and make the filter context visible. A report should be understandable without the person who built it standing beside the screen.

Write the refresh instructions

The workflow is not complete until it can be repeated. Note where the next source file goes, which preparation steps run, what must be refreshed and which checks must pass before the report is circulated.

Ask a colleague to follow those instructions using a safe sample file. Any point where they need to guess is an opportunity to improve the workbook. This is often more valuable than adding another formula that only the original author understands.