Cat On A Spreadsheet

One of the most familiar formats in management reporting is the matrix. Departments appear down the left-hand side, months appear across the top, and each intersection contains a measure such as revenue, expenditure, transaction count or outstanding workload. It is a useful format because it makes comparisons easy. Management can follow a department's performance across several months, compare departments within the same month and identify changes that would be less obvious in a transaction-level report. Excel has supported this style of reporting for a long time. PivotTables are particularly good at producing it, and traditional formulas such as SUMIFS can also populate a predefined reporting grid.

The difficulty arises when the grid itself needs to be dynamic. Perhaps the report must accommodate new departments automatically. Perhaps the user can select any reporting period, from three months to two years. Perhaps the measure is calculated according to a business rule that cannot be handled conveniently by a conventional PivotTable.

We could construct the report using a collection of formulas and helper ranges, but modern Excel offers another possibility. MAKEARRAY allows us to generate an entire rectangular array by defining the calculation that should be performed at each row and column intersection. Combined with LET, LAMBDA and the other dynamic-array functions we have explored throughout this series, it provides a particularly interesting way to construct management reports.

Thinking in terms of intersections

Suppose we have an Excel Table called tblTransactions, containing Date, Department, Status and Value. The table contains transactions from several years. Departments can appear many times, and new departments may be added over time. We want a report with departments down the rows and months across the columns. Each intersection should contain the total value of transactions for that department during that month, excluding cancelled transactions. The user controls the reporting period through two cells. H2 contains any date in the first reporting month, and H3 contains any date in the final reporting month.

Our expected result might look something like this:

The figures are illustrative, but the structure is important. Every value in the body of the report is determined by two coordinates: a department and a month. The value in the Operations row under February represents the total for Operations during February. The value in the Sales row under March represents the total for Sales during March.

This is the central idea behind our solution. Rather than constructing a formula for every possible intersection, we will construct the row and column dimensions and define one calculation that Excel can apply at each intersection.

Understanding what MAKEARRAY actually does

The syntax is:

=MAKEARRAY(rows,columns,LAMBDA(row,column,calculation))

The first two arguments define the size of the resulting array. The third defines how each value should be calculated.

Consider this small example:

=MAKEARRAY(3,4,LAMBDA(RowNumber,ColumnNumber,RowNumber*10+ColumnNumber))

Excel generates a three-row, four-column array. The values are 11, 12, 13 and 14 in the first row, 21, 22, 23 and 24 in the second, and 31, 32, 33 and 34 in the third. The LAMBDA parameters have not been defined anywhere outside the formula, and they do not need to be. MAKEARRAY supplies their values automatically. When Excel calculates the first cell, it supplies 1 for RowNumber and 1 for ColumnNumber. For the cell immediately to its right, it supplies 1 and 2. When it reaches the second row, the first parameter becomes 2. These are positions within the generated array, beginning at 1. They are not worksheet row numbers, and they are not the actual contents of any source data.

The parameter names are chosen by us. We could use shorter names, but RowNumber and ColumnNumber make the calculation easier to follow. This is similar to the behaviour we examined with SCAN in the previous post, where the surrounding function supplied the accumulator and current movement to its LAMBDA. Here, the surrounding function supplies the coordinates of the cell currently being generated.

There is an important difference, however. SCAN passes an evolving value from one step to the next. MAKEARRAY simply generates each intersection according to its row and column positions. The calculation for one cell does not inherently depend on the result of another. The LAMBDA must also return a single value for each intersection. It cannot return a separate spilled table inside one cell of the generated matrix. That requirement suits our reporting problem perfectly. Each department-month intersection needs exactly one number.

Constructing the reporting dimensions

Before generating the matrix, we need to determine which departments and months belong in it. These are two separate analytical dimensions. The departments can be derived from the transaction table. We can use FILTER to exclude blank department names and cancelled transactions, then UNIQUE to identify the distinct departments and SORT to arrange them alphabetically. The resulting expression is:

SORT(UNIQUE(FILTER(tblTransactions[Department],(tblTransactions[Department]<>"")*(tblTransactions[Status]<>"Cancelled"))))

This gives us a vertical array containing one entry per qualifying department.

There is an interesting design decision here. We are identifying departments from the complete transaction history rather than from the selected reporting months. That means a department with historical activity but no activity during the currently selected period will still appear, with zero values. For many management reports, that is desirable. A department does not cease to exist merely because it had no transactions during a particular month. In a production reporting system, a separate department master table would often be an even better source for the row dimension. It could include active departments that have not yet generated any transactions and exclude obsolete departments according to explicit business rules. For our example, deriving departments from the transaction table is sufficient.

The month dimension requires a different approach. We do not want to extract only the months that happen to appear in the transactions. As we discussed in the time-intelligence post, doing so would cause months with no activity to disappear. Instead, we generate the entire requested calendar period. First, EOMONTH(H2,-1)+1 gives us the first day of the starting month. The same calculation applied to H3 gives us the first day of the ending month. We can calculate the number of months between those dates using their years and month numbers. SEQUENCE then provides the offsets, and EDATE converts them into real monthly dates.

For a horizontal array, the essential expression is EDATE(FirstMonth,SEQUENCE(1,MonthCount,0)). The result might contain 1 January 2026, 1 February 2026 and 1 March 2026. These are genuine Excel dates, which we can later display using the number format mmm yyyy.

At this point, we have two arrays: a vertical list of departments and a horizontal list of months. We know exactly how large our reporting matrix needs to be. Its number of rows is ROWS(Departments), and its number of columns is COLUMNS(Months). That is everything MAKEARRAY needs to establish the matrix dimensions.

Calculating the value at each intersection

The remaining question is what the LAMBDA should calculate. For each matrix position, we need to retrieve the corresponding department and month. If RowNumber represents the current matrix row, INDEX(Departments,RowNumber) returns the department belonging to that row. If ColumnNumber represents the current matrix column, INDEX(Months,1,ColumnNumber) returns the month belonging to that column.

Notice the distinction between the coordinates and the values they identify. RowNumber might be 2, but the department is "Operations". ColumnNumber might be 3, but the reporting month is March 2026. The coordinates tell us where we are in the matrix. INDEX translates them into meaningful reporting criteria. Once we have those criteria, the aggregation is straightforward.

We can use SUMIFS to total transaction values where the department matches the selected department, the transaction date belongs to the selected month, and the status is not "Cancelled". For the month condition, I prefer to use the first day of the current month as the inclusive lower boundary and the first day of the following month as the exclusive upper boundary. In other words, a transaction qualifies when its date is on or after the start of the month and before the start of the next month. This is slightly more robust than comparing against the last day of the month, because transaction dates may contain time components. A transaction recorded at 15:30 on the final day should not be accidentally excluded.

The resulting SUMIFS calculation returns a single number, and that is the calculation we give to MAKEARRAY. For each department-month intersection, Excel retrieves the appropriate criteria, performs the aggregation and places the result into the generated matrix. We have now defined the entire numerical body of the report without writing a separate formula for any individual department or month.

Putting the report together

Let's combine the dimensions and the calculation into one complete formula. We'll also include row totals, column totals and a grand total. For simplicity, the example assumes that H2 and H3 contain valid dates in chronological order, that the transaction values are numeric and that the table contains at least one non-cancelled transaction with a populated department.

Although the formula is fairly substantial, its structure follows the report's construction process: we begin by defining the reporting period and generating the months, we identify the departments that should appear as rows, MAKEARRAY generates the numerical body by evaluating the same SUMIFS logic at each intersection, BYROW calculates the total for each department, while BYCOL calculates the total for each month and then finally, HSTACK and VSTACK assemble those components into the finished report. The result is one dynamic formula producing a complete rectangular reporting structure.

If another department is added to the underlying data, the department array grows and the matrix gains another row. If the reporting period changes from three months to twelve, the month array grows and the matrix gains the necessary columns. We do not need to insert or copy additional formulas.

One practical detail is that the header contains actual Excel date values. After entering the formula, we should format the month-header cells as mmm yyyy so they display as January, February and so on rather than as date serial numbers. We should also apply appropriate numeric formatting to the values. As with other spilled reports, the formula generates the data, while worksheet formatting controls its appearance. When the report may change size, formatting a sufficiently large output area or using a suitable automated formatting process can help keep the presentation consistent.

Why generate the matrix instead of using SUMIFS directly?

It would be perfectly possible to build a department-by-month matrix using SUMIFS formulas copied across a worksheet. We could place departments in one column, months in a header row, and write a SUMIFS formula in the first intersection using references to the corresponding department and month. For many workbooks, that would be an entirely reasonable solution. The advantage of MAKEARRAY is not that SUMIFS suddenly becomes more powerful. We are still using exactly the same aggregation function. What changes is the relationship between the calculation and the output structure.

With copied formulas, the worksheet grid determines how many intersections exist. The formulas occupy the intersections that somebody has prepared. With MAKEARRAY, the reporting dimensions determine the size of the grid, and Excel generates the required intersections automatically. That becomes useful when the output dimensions are not known in advance. A report that always contains twelve months and five departments does not gain very much from dynamic matrix generation; however, a report whose departments and periods change according to source data and user selections is a much better candidate.

There is also a maintainability benefit. We have one definition of the measure, rather than dozens or hundreds of physically separate formulas. If the business changes how valid transactions are defined, we can update the aggregation logic in one place.

The trade-off is that the complete formula is more technically demanding. A user who understands ordinary worksheet SUMIFS may be less comfortable maintaining a formula containing MAKEARRAY, LAMBDA, BYROW and BYCOL. That makes clarity and documentation particularly important.

Calculating something more interesting than a simple sum

Our example uses SUMIFS, but nothing about MAKEARRAY requires that choice. Suppose management wants the number of transactions for each department and month rather than their total value. We could replace the aggregation inside the LAMBDA with COUNTIFS using the same department, date and status criteria. The matrix dimensions would remain unchanged. Now suppose management wants the average transaction value for each department and month. We could calculate the qualifying total and transaction count, then divide one by the other, handling months with no transactions according to the reporting requirements. Again, the matrix construction would not change.

That separation is one of the main advantages of this design. The row and column dimensions tell us which intersections need to exist. The LAMBDA tells us what measure belongs at each intersection. The same reporting framework can therefore support different measures without fundamentally changing its structure.

There is an important caution here, however. The totals along the edges of the matrix do not always make sense as sums of the displayed values. For revenue or transaction count, summing the monthly values across a department gives a meaningful departmental total. For average transaction value, summing the monthly averages would be meaningless. Even averaging those averages might be wrong, because months can contain different numbers of transactions. A correct total average would normally need to be calculated from the underlying total value and transaction count for the full period. The same issue arises with percentages, rates and many other business measures.

Our example's row and column totals work because total transaction value is an additive measure. If we change the matrix to display a different measure, we must reconsider the totals rather than merely reusing the same BYROW and BYCOL expressions.

This is another case where the mathematical structure of a formula must follow the business meaning of the measure.

Handling missing activity and reporting completeness

The matrix also introduces an interesting approach to missing data. Because we generate the months independently of the transaction history, every requested month appears even when no transactions occurred. Likewise, because our department list comes from the broader valid transaction history, a department with no activity in the selected reporting period can still appear. In those cases, SUMIFS returns zero. That is generally preferable to the department or month disappearing silently from the report.

However, zero activity and missing information are not always the same thing. If a department has genuinely recorded no transactions, zero is appropriate. If the source extract is incomplete, or transactions have not yet been loaded for a month, showing zero may give a misleading impression of performance. In a production reporting system, it can therefore be useful to track data completeness separately from the measures themselves.

For example, a report might display the latest transaction date available, the extract date or an indicator showing which periods have been finalised. MAKEARRAY can generate the reporting structure, but it cannot independently determine whether missing source records represent genuine inactivity or incomplete data. That is a data-governance question rather than a formula problem.

There is also a distinction between a department with no activity during the selected period and a department that has never appeared in the transaction history. Our current formula includes the former but cannot include the latter because the department list is derived from transactions. If a complete organisational structure is required, a master department table is a better source for the row dimension.

This is another reason to think of row and column definitions as separate parts of the reporting model. They do not necessarily need to come from the same dataset as the measures.

Adding business rules and exception indicators

Once we have a matrix of calculated values, we can begin to treat it as a dataset in its own right. Suppose the report contains monthly expenditure for each department and management wants to identify particularly high values. We could compare every element of the matrix with a threshold using ordinary array arithmetic or an IF expression. If the threshold is stored in H4, IF(Matrix>H4,"Review","") returns an array of exception indicators with the same dimensions as the original matrix. Those indicators could be displayed in a separate report or used to calculate exception counts. Alternatively, a conditional-formatting rule could highlight the corresponding cells in the original report without replacing the numeric values.

For a report intended for management, the latter is often more useful. The figures remain visible, while the formatting draws attention to values that require investigation. But suppose the threshold is not the same for every department. Some departments may operate at much larger volumes than others. A €20,000 monthly value could be exceptional for one department and entirely normal for another.

If we have a department master containing an appropriate threshold for each department, XLOOKUP can retrieve that threshold and apply it to the relevant row. We could incorporate the threshold directly into the calculation used by MAKEARRAY, or generate a separate threshold matrix that aligns with the value matrix. Either approach works, but I would generally favour keeping the numerical measure and its interpretation separate unless the report specifically requires a combined result. The measure answers, “What happened?” while the threshold determines, “Does that require attention?” Those are different questions, and different parts of a reporting system may need to use the same measure with different interpretations. The ability to generate aligned arrays makes it easier to keep those concerns separate.

Performance and practical limitations

There is an obvious consideration with our approach. MAKEARRAY executes the supplied LAMBDA for every intersection in the output. If the report contains ten departments and twelve months, that means 120 evaluations of the aggregation logic. If it contains 200 departments and 36 months, that becomes 7,200 evaluations. In our example, each evaluation runs a SUMIFS calculation against the transaction table. Excel's calculation engine is highly optimised for many common operations, but repeatedly evaluating criteria against a large source can still become expensive. The formula may be compact in terms of worksheet cells without being computationally inexpensive.

This is where it helps to distinguish the size of the output from the amount of work required to produce it. A matrix containing a few thousand cells may represent many millions of source-record comparisons, depending on the structure of the underlying calculation. For moderate datasets, the approach can be perfectly practical. For much larger reporting systems, preprocessing the data with Power Query, constructing a PivotTable or using the Data Model may be considerably more efficient.

We could also reduce the amount of data being processed before building the matrix, perhaps by filtering out invalid transactions or limiting the source to the relevant period. But even that optimisation requires care. Some aggregation functions work most naturally with actual worksheet ranges rather than arbitrary calculated arrays. SUMIFS, for example, expects compatible range arguments, so replacing its source ranges with dynamically calculated arrays is not always straightforward.

An alternative for an already-filtered in-memory dataset is to use FILTER and SUM inside the MAKEARRAY calculation, but doing that for every intersection can also become expensive.

There is no universal winner. The best approach depends on data volume, calculation complexity, update frequency and the requirements of the report. For a workbook containing a moderate transaction history and several interactive management reports, MAKEARRAY may offer a very attractive balance between flexibility and maintainability. For a large operational reporting platform, the same logical matrix might be better produced by a dedicated aggregation engine.

The important thing is to recognise that MAKEARRAY is a matrix-construction tool, not a replacement for every other reporting technology.

There are also input-validation and empty-data cases to consider. Our main formula assumes a valid chronological reporting period and at least one eligible department. If the ending month precedes the starting month, the calculated month count is invalid. If the source contains no qualifying departments, FILTER cannot produce the department dimension we expect. A production version should validate the control cells and provide a meaningful empty-state message. These checks belong around the report-building calculation rather than being hidden inside every individual matrix intersection.

The reporting matrix as a reusable pattern

There is a broader idea emerging from this example. Many management reports can be described in terms of three components: a row dimension, a column dimension and a measure. The row dimension might contain departments, customers, products, regions or employees. The column dimension might contain months, quarters, performance categories, scenarios or status groups. The measure might be revenue, cost, transaction count, average processing time, outstanding workload or another business calculation. Once we separate those three components, a large class of reporting problems begins to look structurally similar.

Our department-by-month report uses departments as rows, months as columns and total transaction value as the measure. A customer-by-quarter report could use the same general approach with different dimensions and a different aggregation. A region-by-product-category report could do the same. Even a budget-versus-actual matrix becomes possible, although the logic inside the LAMBDA would need to retrieve the appropriate value for each scenario.

This is not a reason to build an elaborate generic reporting framework immediately. A formula that attempts to accommodate every possible dimension and measure can quickly become more complicated than the reports it is supposed to simplify; but recognising the shared structure is useful. It encourages us to design the reporting dimensions deliberately rather than relying on whatever arrangement happens to exist in the source data.

That is consistent with the direction we have taken throughout this series. We began by filtering and reshaping datasets. We then enriched them with reference information, grouped records, calculated measures and constructed reports. We introduced time intelligence by generating reporting periods independently of the source data. We made filtering logic reusable with LAMBDA, and then examined functions that apply calculations across arrays and accumulated sequences. MAKEARRAY brings several of those ideas together. Instead of merely transforming an existing array, we can construct a new one with the exact dimensions required by the report. The row and column coordinates become the inputs to our calculation, and the calculation determines what belongs at every intersection.

That is an important extension to the way we can think about Excel formulas. The worksheet grid is no longer necessarily something we must prepare in advance. It can itself be the output of a calculation. And that raises an interesting next question.

Our matrix is dynamic, but it still represents one particular report: departments by month, with a fixed measure and a fixed interpretation of the source data. What if we wanted to take these ideas further and build a reusable reporting engine in which the user could select the reporting dimensions and measures, while Excel constructed the corresponding output automatically? That would require us to think beyond individual formulas and consider the architecture of the reporting workbook itself: its source data, calculation layer, reusable functions, user controls and presentation layer. It would also bring us closer to the boundary between an advanced spreadsheet and a small reporting application.

That is where we will go next.

Full Service Consulting

Reporting

Automation

Cat On A Spreadsheet