Bottom line: Learn how to add total rows and columns to the bottom of the spill ranges created by dynamic array formulas.
Skill level: Advanced
Download the Excel File
You can download the example file I use in the video below to follow along.
Floating Totals for Spill Ranges?
Thomas, a member of our Elevate Excel Training Program, asked a great question in the Community Forum about adding total rows or summary rows to spill ranges.
This is NOT directly possible in Excel yet. However, I recently stumbled upon an interesting range reference that allows us to create a workaround solution.
The solution just uses a few Dynamic Array formulas and Conditional Formatting. I can't say it's easy, but I think the technique will be very useful in many different scenarios.
The Problem: Spill Ranges Change Size
Dynamic array formulas are an awesome way to create interactive summary reports, and can be used as an alternative to pivot tables. The spill ranges will typically change size (more or less rows or columns) as input values change.
One common issue with dynamic arrays is that we can't append data to the bottom of the spill ranges yet.
In the example above I'm using the FILTER function to create a report by customer. The number of rows in the spill range changes when a new selection is made from the Customer drop-down list.
Therefore, it's difficult to put a total row below the spill range because the last row of the spill range is constantly changing.
If you're not familiar with dynamic arrays yet, then checkout my post & video on Dynamic Array Formulas and Spill Ranges. You will also want to make sure you are on a version of Excel that has dynamic arrays.
The Solution: The Overflowing Spill Range?
So, we need a way to append total rows or columns to the bottom of the spill range, and/or include them inside the spill range.
One possible solution uses this weird range reference:
It allows us to use the spill range operator and create a range reference that includes rows and columns ABOVE and to the LEFT of the spill range.
I'm not sure if there is a term for this yet, but I'm calling it an overflowing spill range. 🙂
This means we can put our total rows/columns above or to the left of a spill range, then use the SORTBY function to move them to the bottom/right.
If that sounds crazy, well, buckle up…
The Explanation: Total Rows for Spill Ranges
There are two major ingredients for this technique: dynamic array formulas and conditional formatting. The solution does NOT use VBA or macros.
Let's take a look at each component.
Step 1: Write the Formulas
There are four major formula components that go into the solution.
The first three components can be created in a staging/calculation area on a separate worksheet. The fourth component will be the formula that spills the final output with the total rows/columns.
#1 – The Original Spill Range
The first thing we need is a spill range for the summary report. You can use ANY dynamic array formula that produces a spill range.
In the example above I used a FILTER function. Here is another example that uses the SUMIFS function to produce a spill range.
#2 – The Total Row
Next we need to create the total row(s) directly above the original spill range.
Type the word Total in the left most cell.
Note: You do NOT have to use the word Total. This can be any phrase. It just needs to be different from any values in the left column of the spill range to differentiate it for the conditional formatting.
Next you'll create the formulas for the total row. You can use any formula to calculate various metrics: sum, average, count, etc.
In the first example I use the SUM function to sum individual columns in the original spill range. That formula looks like the following.
It uses the INDEX function to return a range reference to a single column in the spill range (G6#). See the video above for further explanation.
You can also use a SUMIFS formula here to create a sum from the source data range.
#3 – The Index Column
Next we need a way to put the total row at the bottom. We're going to create an “index column” that contains row numbers. This can be to the left or right of the original spill range, and does not need to be directly next to it.
There are two formulas for the index column:
- The SEQUENCE function is used to create row numbers for each row of the spill range. Put the following formula in the same row as the first row of the original spill range and reference it with the spill range operator.
This creates a list of numbers from 1 to the number of rows in the original spill range.
- The row number for the total row is 1 greater than the number of rows in the spill range. Put the following formula next to the cell in the total row.
This formula counts the number of rows in the spill range and adds 1, ensuring that the total row will always be at the bottom when it's sorted.
#4 – The Sorted Output Range
Finally, we are going to sort the data in a new output range using the SORTBY function. This formula can be on a separate sheet where you are creating your summary report or dashboard.
This formula uses the overflowing spill range reference that I mentioned earlier, and looks like the following:
The first argument is the array or range we want to sort (G6#:G5). For this we only include the original spill range using the spill range operator (G6#) to the row above that contains the total row (G5).
Interestingly, this reference includes all of the cells from G5 to the bottom right corner of the spill range.
Note: If you ever type a reference with a cell above or to the left last in the range (B100:A1), Excel will usually fix it automatically (A1:B100). However, it doesn't fix it in the case of named ranges and spill ranges. Awesome!
The second argument is the by_array or range we want to sort by. For this we reference our index column with the same range reference (F6#:F5). This includes all of the cells in the sequence function (F6#) and the cell above that contains the row number of the total row (F5).
The third argument is the sort_order and we specify 1 for ascending order.
This SORTBY formula allows us to create a spill range with total row(s) at the bottom of a spill range. Now we need to polish it up with some conditional formatting.
Step 2: Apply Conditional Formatting
With all this hard work to create the total row, we now need it to stand out!
Since the position of the total row will likely change, we can't just apply formatting to a single row on the sheet. Conditional formatting to the rescue.
Here are the steps to apply the conditional formatting. See the video above for more details.
- Select a range from the top left corner of the output range (SORTBY formula) to the right-most row and go down the maximum number of rows the range can spill to, plus one for the total row. We can't make the conditional formatting range dynamic yet, so just go down more rows than you will likely need.
- Create a new conditional formatting rule by going to the Home tab > Conditional Formatting > New Rule…
- In the New Formatting Rule window, select Use a formula to determine which cells to format.
- Add a formula that uses a mixed reference for the top left cell in the selection and is equal to the phrase you used for the left cell in the total row.
Notice that $ symbol in front of the L to make the column an absolute reference and the row number is relative. This applied the formatting to the entire total row where the value in column L equals Total (when the formula evaluates to TRUE).
- Click the Format… button to create the formatting for the total row.
- Number tab: See the video on how to hide the zeros in columns that don't contain a calculation. You will use a custom number format and leave the format for the zero blank. The Accounting style custom format looks like the following: ($* #,##0);($* (#,##0);;(@_)
- Font tab: Change the font to Bold, or whatever your preference is. You can also change the font color.
- Border tab: Add a top border and a bottom border if you like. You can also change the color of the border before adding it.
- Fill tab: Select the fill color to make the total row stand out.
- Press OK a few times to apply the formatting to the total row.
If you have multiple total rows you will just repeat this process for the other names used in the left cells of those rows.
The formatting will automatically be applied to the total row(s) as the spill range changes size. If you've made it this far then pause and do the happy dance. It's fun to see it all work! 😀
There are a lot of uses for this technique and it really all stems from the ability to create a range reference that includes the entire spill range and cells above and to the left of it (the overflowing spill range).
We can add multiple total rows to the bottom by creating total rows with row index numbers that are sequentially greater than the total number of rows in the spill range.
We can also add total columns to the right of the spill range and even have the total column move right/left if the number of columns in the spill range is changing.
See the video above and example file for more on those techniques.
We can also use this technique to add subtotal rows within our spill range. I'll leave that for another post, but it's essentially using multiple total rows and getting the row order correct in the index column.
Another solution is to put the total row way below the spill range and then use the FILTER function to filter out the blank rows between. This solution is a bit less flexible and has the potential for more #SPILL! errors.
You will also need to filter by a column that will NEVER contain blank cells in the spill range. Or you can put a column to the left of the spill range that identifies complete blank rows with the COUNTA function.
You will still use the same technique to conditionally format the total rows in the output range.
If you've been screaming, “why not use a pivot table?!?” as you read this, well you have a point. Pivot tables are a good alternative solution to dynamic arrays. There are pros & cons to each, and I'll leave that as a topic for a separate post.
I can't say this is the easiest solution, but I do believe the overflowing spill range has a lot of potential uses that are yet to be discovered.
So please leave a comment below with any questions, suggestions, or ideas on how we can use this technique to solve other problems.
Thank you! 🙂
Free Training Webinar on the Power Tools
Interested in learning more about the new tools and features of Excel?
Right now I'm running a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It's called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
The webinar is running at multiple days and times. Please click the link below to get registered and save your seat.