Cat On A Spreadsheet

From Formula Prototype to Reliable Reporting Workbook

Throughout this series, we have explored how dynamic arrays can transform Excel from a collection of cell-based calculations into a capable reporting environment. We have filtered and reshaped data, built management summaries, introduced time intelligence, created reusable functions with LAMBDA and generated reporting matrices with MAKEARRAY. In the previous post, we examined how to test those calculations. We established expected results, reconciled totals, tested boundary conditions and considered how changes to a report might introduce errors elsewhere.

 

That brings us to an important distinction. A formula that works correctly is not necessarily a reporting solution that works reliably. A reporting workbook has to deal with more than calculations. Source data changes, users make unexpected selections, reporting periods move forward, business rules evolve and the output eventually needs to be reviewed or distributed. Even an elegant dynamic-array formula can become a maintenance problem if these surrounding responsibilities are not considered. So let's take the reporting engine we developed earlier and look at what would be required to make it suitable for regular use.

 

The workbook needs a structure, not just a formula

 

Our reporting engine uses a transaction table called tblTransactions, containing Date, Department, Region, Category, Status and Value. The user can select a reporting period, choose whether the rows represent departments or regions, choose months or categories for the columns, and select between total value, transaction count and average transaction value. The engine then constructs the appropriate matrix.

 

As a calculation, this is a useful result. But imagine handing the workbook to a finance manager who has never seen its formulas. Where should new transactions be placed? Which cells can be changed? How can the user tell whether the source data is current? What should happen if the report displays an error? How do we know that the output is safe to use? Those questions cannot all be answered by adding another expression to LET; they require a sensible workbook structure.

 

I would begin by separating the workbook into a few clearly defined areas: one worksheet contains the source data, another contains the user controls and the report itself, a separate area holds validation and reconciliation checks and, finally, a short documentation worksheet explains the purpose of the workbook, its data requirements and the correct way to update it.

 

For a larger system, some of these responsibilities might deserve separate worksheets. For a smaller workbook, combining related areas can be perfectly reasonable. The objective is not to maximise the number of worksheets, but to make the different responsibilities visible. The source data should not be mixed with presentation calculations; users should not have to navigate around formula-generated output to find the controls; important validation checks should not be buried inside a hidden calculation that nobody knows exists.

 

This separation also makes future maintenance easier. If the transaction extract changes, we know where to investigate. If management wants a new report layout, we can work on the presentation without redesigning the source-data process. That is a practical advantage long before the workbook becomes particularly complex.

 

Treat the source table as a controlled interface

 

One of the most common weaknesses in Excel reporting systems is an unclear boundary between the original data and the calculations that use it. Perhaps somebody pastes a new extract over the previous one; perhaps a column is renamed, or perhaps the source system begins exporting dates as text or introduces a new status value. The report may continue calculating, but its results are no longer necessarily correct.

 

Our dynamic reporting engine expects a particular data structure. The field names are important, and the values must have consistent meanings. We should treat that structure as an interface between the source system and the report. For example, Date must contain genuine Excel date values, potentially including times, Value must contain numeric transaction values, Status must use the agreed values, including "Posted" for transactions eligible for reporting, Department, Region and Category must also be populated according to the rules of the reporting model. The report can tolerate changes in the number of rows - that is one of the main benefits of using an Excel Table - but it should not be expected to tolerate arbitrary changes to the meaning of its columns.

 

This is where Power Query can be particularly useful. Instead of repeatedly pasting source data into the workbook, we can connect to the original file or database, select the required columns, assign their data types and apply basic transformations before loading the result into tblTransactions.

 

Power Query is not mandatory. A manually maintained Table may be entirely appropriate for a small reporting process. But the same principle applies in either case: the reporting calculation should receive data in a predictable structure.

 

I would also avoid modifying the imported records simply to make a particular report work. If we need a calculated reporting field, it should generally be added in a clearly identified transformation stage rather than overwriting the original information. For example, a transaction date should remain a date. The reporting month can be derived from it. Likewise, if a department code needs to be translated into a descriptive name, the mapping should be controlled rather than manually replacing values throughout the source table. The closer we can keep the source data to a consistent, verifiable representation, the easier it becomes to investigate discrepancies later.

 

Distinguish refreshing data from recalculating a report

 

This is a small but important operational distinction: refreshing the source data and recalculating the report are not necessarily the same operation. If tblTransactions is populated by Power Query, the query must retrieve and load the latest source records before the reporting formulas can calculate against them. Once the Table has been updated, the dynamic-array formulas can recalculate using the new data.

 

But a correctly calculated report does not prove that the source was refreshed successfully. Imagine opening the workbook on Monday morning. The reporting matrix calculates without errors, the totals reconcile and the report looks sensible - but it may still be using Friday's transaction extract. Neither SUMIFS nor MAKEARRAY can determine whether the underlying database contains newer records that have not been loaded.

 

This is why the workbook should expose some information about its source. A useful reporting interface might display the latest successful data-refresh timestamp, the source period covered and the number of records currently loaded. The refresh timestamp should come from the actual refresh process or be recorded as part of a controlled manual update. Using NOW() on the report would merely show the current calculation time, not when the underlying data was retrieved. Similarly, MAX(tblTransactions[Date]) can tell us the latest transaction date represented in the table, but it cannot prove that the extract is complete. Those are different pieces of information.

 

A report can be arithmetically correct and operationally outdated at the same time, and that is why source freshness belongs alongside calculation accuracy when we assess whether a report is ready for use.

 

Designing controls that cannot easily be misused

 

Our reporting engine currently expects five inputs, as the user selects a start month, an end month, a row dimension, a column dimension and a measure.

 

The first improvement is straightforward: restrict the available choices wherever possible. Data Validation dropdowns are a natural choice for the dimension and measure selectors. They prevent ordinary users from entering unexpected spellings or unsupported options. The date cells can also use Data Validation to encourage valid date entries, although the reporting formula should still validate them independently.

 

Why do both? Because Data Validation improves the user experience, while formula-based validation protects the calculation. Data Validation can be bypassed by certain paste operations, copied values or programmatic changes. It should not be treated as a guarantee that the inputs are valid.

 

We can therefore create a small status calculation that checks the reporting controls before the main report is used. For example, if our controls are in H2:H6, the following formula validates the supported selections:

=LET(
     StartDate,H2,
     EndDate,H3,
     RowChoice,H4,
     ColumnChoice,H5,
     MeasureChoice,H6,
     IF(OR(NOT(ISNUMBER(StartDate)),NOT(ISNUMBER(EndDate))),"CHECK: Reporting dates",
         IF(EOMONTH(StartDate,-1)>EOMONTH(EndDate,-1),"CHECK: Date order",
              IF(ISNA(XMATCH(RowChoice,{"Department","Region"})),"CHECK: Row dimension",
                 IF(ISNA(XMATCH(ColumnChoice,{"Month","Category"})),"CHECK: Column dimension",
                    IF(ISNA(XMATCH(MeasureChoice,{"Total Value","Transactions","Average Value"})),"CHECK: Measure",
                       "READY"
                    )
                  )
              )
         )
     )
)

 

This is not a complete validation system, but it establishes a useful principle. For that reason, I would dedicate a clearly defined area of the worksheet to the reporting matrix and avoid placing unrelated information immediately below or to its right, and we should also avoid merged cells inside the potential spill range.

 

The formula itself should sit outside an Excel Table. Although the source data benefits from being stored in a structured Table, the spilled output needs an ordinary worksheet range where it can expand. A report beginning in J10, for example, can be referenced elsewhere using J10#. The spill operator identifies the complete current output of the formula. That makes it possible to build other calculations that follow the changing dimensions of the report.

 

But the worksheet layout needs to respect that dynamic behaviour - a report that works with three departments should not fail merely because a fourth department appears. We should also be cautious about manually editing individual cells inside the output. A dynamic-array result is generated by its anchor formula. Users cannot independently edit the spilled values as though they were ordinary formulas copied into separate cells. If a result is wrong, the correct response is to investigate the source data, controls or calculation—not to overwrite one reported figure manually. That distinction should be clear to anyone using the workbook.

 

Presentation should follow the measure

 

Our reporting engine supports total values, transaction counts and average transaction values. These measures should not necessarily be displayed in the same way. A total value might require euro formatting with two decimal places. A transaction count should normally be shown as a whole number. An average transaction value might also use currency formatting, but its interpretation differs from a total. The source calculation can return all three measures correctly while the report still presents them poorly. For example, displaying a transaction count as €150.00 would be confusing, even if the underlying number were correct. Likewise, showing an average as a whole number may conceal differences that matter to management.

 

This is where we need to recognise a limitation of formula-generated reports: a formula returns values, not a complete presentation specification. We can use worksheet formatting, conditional formatting and carefully arranged report areas to make the output readable, but a single static number format may not suit every possible measure.

 

For a small workbook, a reasonable compromise may be to format the report consistently and provide a clear indication of the selected measure. If more polished presentation is required, VBA can respond to control changes and apply appropriate formatting to the output range. Alternatively, separate presentation layouts may be used for different measure types.

 

Neither approach is inherently superior. The choice depends on how interactive the workbook needs to be and how much additional complexity is justified.

 

The important point is that the calculation and presentation have different responsibilities. Our dynamic-array formula should produce the correct numerical result, while the workbook interface should make that result understandable. A report title should therefore reflect the selected dimensions and measure. The reporting period should be visible, and the current source-data status should not be hidden from the user.

 

A table labelled merely "Report" is not particularly informative once it has been copied into a management presentation or attached to an email; however, a title such as "Total Transaction Value by Department and Month — January to March 2026" provides far more useful context. That context becomes especially important when the report is exported or distributed separately from its original controls.

 

Reconciliation should remain visible in production

 

In the previous post, we built controlled test scenarios and examined several ways to verify reporting calculations. Those tests remain valuable during development, but I would also retain some simpler reconciliation controls in the operational workbook.

 

The purpose is slightly different: while a development test verifies that the formula behaves correctly for known inputs, a production reconciliation checks whether the current output remains consistent with the current source data.

 

For our reporting engine, a useful control is to compare the sum of the displayed matrix with an independently calculated total from the transactions. This works when the selected measure is Total Value or Transactions, provided the chosen dimensions form complete, non-overlapping groups, but it does not work by simply summing the cells when Average Value is selected, because averages are not additive.

 

Suppose the report begins in J10, with the first row containing column headings and the first column containing row headings. We can use DROP(J10#,1,1) to retrieve the numerical body of the spilled report. The following formula then reconciles additive measures against tblTransactions:

=LET(
     StartDate,EOMONTH(H2,-1)+1,
     EndExclusive,EOMONTH(H3,0)+1,
     ExpectedCount,COUNTIFS(tblTransactions[Status],"Posted",tblTransactions[Date],">="&StartDate,tblTransactions[Date],"<"&EndExclusive),
     IF(H6="Average Value","CHECK WEIGHTED AVERAGE",
         IF(ExpectedCount=0,"NO DATA",
              IFERROR(
                 LET(
                     Expected,IF(H6="Transactions",ExpectedCount,SUMIFS(tblTransactions[Value],tblTransactions[Status],"Posted",tblTransactions[Date],">="&StartDate,tblTransactions[Date],"<"&EndExclusive)),
                   Actual,SUM(DROP(J10#,1,1)),
                     IF(ABS(Expected-Actual)<0.005,"PASS","FAIL")
                 )
              "CHECK REPORT OUTPUT")
         )
     )
)

 

The logic is deliberately restricted to the measures for which the comparison is meaningful. If Average Value is selected, the formula indicates that a different reconciliation method is required. If the selected period contains no posted transactions, it reports that condition rather than pretending that a normal reconciliation has passed. For an additive measure, it calculates the expected result directly from the source table and compares it with the matrix total.

 

The tolerance of €0.005 accommodates ordinary two-decimal-place monetary reporting. Transaction counts are integers, so the same tolerance also requires an exact count match. If the report cannot be read as the expected spilled matrix, the calculation returns a specific message.

 

This is an operational control, not a replacement for the more comprehensive tests developed in the previous article. A passing grand-total reconciliation cannot prove that every department or category is correct. Values could be allocated to the wrong groups while the overall total remains unchanged. Similarly, an incomplete source extract could produce a report that reconciles perfectly against the incomplete data.

 

That is why reconciliation should sit alongside input validation, source-data checks and the more detailed testing process. It is one component of reliability, not a complete guarantee.

 

A successful calculation is not the same as a successful reporting cycle

 

Let's consider how the workbook would actually be used each month. First, the source data is refreshed or replaced according to a defined process. Then the user checks that the update completed successfully and that the extract covers the required reporting period. Next, the workbook validates its required fields and controls. The reporting engine then calculates using the updated source. After that, the user examines the resulting report and verifies that the reconciliation checks have passed. Only after those stages should the report be treated as ready for review or distribution.

 

This sequence matters. Suppose the source file is missing one branch's transactions. The calculation might still work, the grand total might still reconcile against the incomplete source, and the workbook might display "READY" because all control cells are valid, yet the report is not complete.

 

This is why I would avoid using a single status label to represent every aspect of reliability. There is a difference between valid user controls, a successfully refreshed source, acceptable data quality and a passed financial reconciliation. A production workbook should preserve those distinctions, even if it ultimately combines them into one overall readiness indicator.

 

For example, the report might be considered ready for management review only when the latest source update has been confirmed, required data-quality checks have passed, the selected criteria are valid and applicable reconciliations are successful. If one condition fails, the workbook should identify which one. That is more useful than a generic error message, and much safer than allowing a visually polished report to imply that every underlying process has succeeded.

 

The status should also distinguish a period with genuinely no transactions from a period for which data has not been loaded.

 

Those conditions may look identical inside the transaction table, but they have very different business meanings.

 

Preparing reports for distribution

 

There is another transition to consider: an interactive workbook and a distributed management report serve different purposes. While the workbook allows the user to change reporting periods, dimensions and measures, the distributed report usually needs to represent a fixed reporting position. If we export the report to PDF, for example, the selected period and measure should be unambiguous. The document should ideally identify when it was generated and what source information it represents.

 

The same applies to a PowerPoint management pack. For financial or management reporting, I would generally produce the initial output as a draft for review rather than treating an automatically generated file as final. The workbook can perform the calculations, populate the reporting tables and prepare the draft output. A finance or management reviewer can then confirm that the figures and interpretation are appropriate before distribution.

 

This is particularly important when a report contains commentary or recommendations that cannot be validated through arithmetic alone. An automatically calculated variance may be correct, but an explanation of why the variance occurred requires business knowledge, and the output process should preserve that distinction. A report can be generated automatically without being approved automatically.

 

If distribution becomes frequent or repetitive, VBA or Power Automate may be useful for creating files, applying naming conventions and saving copies in the appropriate location, but those additional tools should not become prerequisites unless they provide a practical benefit. For a workbook used by one person once a month, manually selecting the required report and exporting it may be simpler and more reliable than introducing an elaborate automation process. Automation is useful when it removes meaningful repetitive work, not merely because it is possible.

 

Documenting a workbook without overengineering it

 

Documentation is another area where spreadsheet solutions often fall into one of two extremes. Some workbooks contain almost no explanation of how they work, while others have so much documentation and internal structure that maintaining the documentation becomes a project in its own right. For a reporting solution like ours, a short but precise explanation is usually sufficient.

 

I would want the workbook to state where its source data comes from, what fields are required, how the data is updated, which controls are available and how each measure is defined. It should also identify the main reporting formula or named function, explain the validation checks and describe what the user should do when a check fails. That is not a particularly large amount of information, but it makes an enormous difference when the workbook is handed to somebody else or revisited after several months.

 

Measure definitions deserve particular attention. For example, Average Value in our engine means total qualifying transaction value divided by the number of qualifying transaction records at the selected intersection. That definition may sound obvious, but it is not necessarily the same as average customer value, average invoice value or average value of positive transactions. If the workbook uses one definition, the documentation should state it clearly.

 

The same applies to the treatment of cancelled and pending transactions, negative values and missing categories. Many reporting disagreements are not caused by arithmetic mistakes. They arise because different people are working with different interpretations of what the report is supposed to measure. Documentation helps make those decisions explicit.

 

I would also maintain a simple version history for material changes. If the reporting engine is modified to introduce a new measure or alter the definition of a valid transaction, that change should be identifiable. For a small workbook, this could be a short change log. For a more substantial development project, version control and documented test results may be appropriate.

 

The degree of formality should reflect the importance and complexity of the solution.

 

Maintaining the workbook as requirements evolve

 

No reporting workbook remains unchanged indefinitely. Management will request new measures. The source system may add fields. Departments may be reorganised. Reporting periods may change. The question is not whether change will occur, but how expensive and risky those changes will be.

 

Our current architecture offers several advantages: because source data is maintained separately, we can change the presentation without modifying the transaction extract; because the reporting dimensions and measures are defined explicitly, we know where to introduce new options; because the main calculation is centralised, we do not need to hunt through dozens of copied formulas when a business rule changes; and because we have established test scenarios and reconciliation checks, we have a way to verify that modifications have not damaged existing behaviour.

 

These advantages only remain useful if we preserve the separation of responsibilities. If every new requirement leads to another exception embedded somewhere inside an increasingly complicated LET formula, the reporting engine can eventually become difficult to understand. At that point, the correct response may not be to add another nested IF. It may be to reconsider the structure.

 

Perhaps a frequently repeated calculation should become a named LAMBDA, perhaps a source transformation would be more appropriate in Power Query, perhaps the volume and complexity of the measures now justify using the Data Model or perhaps the workbook has grown beyond the point where a formula-only reporting engine is the most sensible design. There is no requirement to preserve the original architecture indefinitely. A maintainable solution is one that can evolve without becoming unnecessarily fragile. That sometimes means improving the formulas. At other times, it means moving part of the work to a more suitable tool.

 

Knowing when the workbook is finished

 

One temptation in Excel development is to keep adding features. Dynamic arrays make this particularly attractive because another reporting option may require only a relatively small change to the existing formula. We can add another measure, another dimension, another control and another output. Eventually, however, additional flexibility can begin to make the workbook less usable rather than more useful.

 

A report that allows users to configure almost everything may require considerable knowledge to operate correctly. Management often values clarity and consistency more than unlimited customisation. For that reason, I would define a clear scope for the reporting solution.

 

Our example supports two row dimensions, two column dimensions and three measures. It uses one consistent population of posted transactions within the selected reporting period. That is already enough to support several useful management views. If those views satisfy the reporting requirement, there is no need to turn the workbook into a general-purpose business intelligence platform. The calculation should be flexible where flexibility provides value; elsewhere, simplicity is an advantage.

 

The same applies to the user interface, validation controls and automation. A reliable workbook is not necessarily one with the most features. It is one that performs its intended tasks correctly, makes its assumptions clear and can be operated and maintained without unnecessary effort. That is a useful standard against which to evaluate every new requirement.

 

Bringing the pieces together

 

We began this series with individual dynamic-array techniques, examining how they could replace some of the repetitive structures commonly found in traditional Excel workbooks. As the examples progressed, we developed something more substantial.

 

We used LET to make transformations readable and avoid repeating intermediate calculations. We used lookup functions to enrich records, aggregation techniques to change reporting grain and time-intelligence calculations to construct reporting periods independently of source transactions. We introduced LAMBDA to make business rules reusable, explored functions such as MAP, BYROW, REDUCE and SCAN, and used MAKEARRAY to construct reporting matrices whose dimensions could change dynamically and, finally, we built a configurable reporting engine and considered how to test it.

 

The remaining step was to place that engine inside a workbook that people could actually use. That requires some discipline around the source data, controls, output structure, validation, reconciliation and distribution process. None of those considerations is particularly glamorous, but they are often what distinguishes an impressive spreadsheet demonstration from a dependable reporting solution.

 

A formula can be technically sophisticated while the workbook around it remains fragile. Conversely, a relatively simple calculation can form the basis of a very reliable reporting process if its inputs, business rules and outputs are properly controlled. The objective is not to maximise formula complexity, it is to produce trustworthy information with an appropriate amount of effort, and that is where I believe dynamic arrays are most valuable. They allow us to consolidate calculations that previously required large numbers of helper cells, copied formulas and manually maintained report structures. Used carefully, they can make a reporting workbook smaller, more consistent and easier to adapt, but they work best as part of a considered design, not as a substitute for one.

 

And that brings this stage of the series to a natural conclusion. We now have a functional reporting engine, a method for validating its results and a practical structure for maintaining and distributing its output.

 

There is one further direction worth exploring. So far, the entire reporting process has been centred on Excel's formula engine, yet many real reporting solutions also involve importing data, preparing draft reports, producing PDF or PowerPoint outputs and coordinating several applications. Dynamic arrays can handle the calculations, but they do not need to handle the complete workflow.

 

In the next post, we will examine how to combine Power Query, dynamic arrays and VBA into a reporting pipeline, allowing each technology to perform the tasks it is best suited to while keeping the overall solution understandable and maintainable.

16 November 2026

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet

Cat On A Spreadsheet