Key takeaways
- Use tables and structured references so formulas follow new rows.
- Separate lookup, aggregation, and presentation formulas.
- Make period logic explicit rather than hiding it in a date filter.
- Build reconciliation checks into the report, not into a separate review.
On this page
Start with a stable structure
Formulas are only as reliable as the table beneath them. Convert source ranges to Excel Tables, keep one row per record, and avoid merged cells or blank rows. Structured references then expand with the data and make formulas easier to read. A single header row, consistent data types, and no totals inside the data range prevent most broken formulas.
Decide where each calculation belongs. Source data should stay untouched, calculations should sit on a working sheet, and the report should present results. A formula embedded in a formatted report is difficult to test and easy to overwrite. Name the tables and key ranges so a formula reads like a sentence rather than a set of coordinates.
Lookups that fail visibly
XLOOKUP is the most direct lookup in current Excel: it searches a range or array, returns a matching value, and can supply a fallback when no match exists. INDEX and MATCH remain useful when the lookup direction varies or the workbook must support older versions. Use the fallback argument to return a clear message when a value is missing, rather than a zero that looks like real data.
Do not hide every error with IFERROR. A missing customer or an unrecognised product code is information. Use IFNA for expected lookup misses and let genuine errors surface, so a broken reference is not disguised as a zero. When a lookup fails, check for trailing spaces, text stored as numbers, and duplicate keys before changing the formula.
=XLOOKUP(
[@CustomerID],
Customers[CustomerID],
Customers[CustomerName],
"Customer not found",
0
)Aggregation and conditional logic
SUMIFS, COUNTIFS, and AVERAGEIFS answer most reporting questions: revenue by month and region, open tickets by owner, average order value by channel. They are easier to audit than a chain of nested IFs and they work well with structured references.
SUMPRODUCT handles weighted totals and conditions that SUMIFS cannot express cleanly, such as comparisons across arrays. Use it deliberately; it is powerful but harder for the next analyst to read.
- SUMIFS: additive totals with several conditions.
- COUNTIFS: volume and exception counts.
- SUMPRODUCT: weighted averages or array conditions.
=SUMIFS(
Sales[Revenue],
Sales[Month], [@Month],
Sales[Region], [@Region]
)Dates and periods
Reporting periods need explicit rules. EOMONTH returns the last day of a month, EDATE shifts a date by whole months, and YEARFRAC supports part-period calculations. A month key built with TEXT and a year-month format makes grouping and sorting predictable.
Avoid relying on the current date inside a report unless the report is meant to be relative. A fixed period end is easier to reconcile and archive. Where a rolling view is needed, keep the period control in one cell and reference it everywhere. A fiscal calendar needs its own mapping table when the year does not start in January.
=EOMONTH([@TransactionDate], 0)Dynamic arrays and readable formulas
Dynamic arrays changed reporting work in Excel. FILTER returns the rows that meet a condition, UNIQUE lists distinct values, SORT orders the result, and the output spills into adjacent cells. A report can refresh its exception list without a manual copy.
LET names intermediate calculations inside a formula, which makes a long expression easier to follow and cheaper to calculate. Use it to separate the source, the filter, and the result rather than nesting everything in one line. Name the spill range when other formulas need to reference it, and leave the adjacent cells empty.
=LET(
selectedMonth, $B$2,
exceptionRows,
FILTER(
Sales,
(Sales[Month] = selectedMonth) *
(Sales[Margin] < Sales[MarginTarget]),
"No exceptions"
),
SORT(exceptionRows, Sales[Margin], 1)
)Checks and presentation
Every report needs a reconciliation block: source total, report total, difference, and a status. Keep it visible on the working sheet or at the top of the report. A report that cannot prove its totals will not be trusted. Set a tolerance for the difference and explain anything above it.
Use ROUND at the point where a number is presented, not throughout the calculation. Use number formats for display and keep the underlying value precise. TEXT is for labels, not for numbers that still need to be summed.
Frequently asked questions
Which lookup function should a reporting analyst use?+
XLOOKUP when the workbook supports it, because it handles exact matches and fallbacks cleanly. Use INDEX and MATCH where compatibility or flexible direction is required.
Are dynamic arrays safe in a shared workbook?+
They are reliable when the spill range is clear and nobody types over it. Leave space for the output and protect the area if several people use the file.
Should formulas be replaced with Power Query?+
Use Power Query for import and transformation, and formulas for reporting logic that users need to see or adjust. The two work well together.
How should an inherited report be audited?+
Map the inputs, trace one total from source to output, check the period logic, and list every hard-coded value. Rebuild the reconciliation before changing the presentation.
Need help applying this?
Improve your bi dashboards & reporting.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
Variance analysis: price, volume, and mix
Price, volume, and mix explain most revenue and margin gaps. A worked example shows how to split the variance and reconcile it to the total.
Reporting calendar: keeping packs on schedule
A reporting calendar is a dependency map. Build it backwards from the review date, set cut-offs, and measure every miss to keep packs on schedule.