How to spill formulas in Excel
Q. How do I spill formulas in Excel?
A. Spilling is a feature available in Excel 365 and later versions. With spilling, you can create a formula in one cell, and that formula will then spill over into other adjacent cells when a set of values is calculated. This capability was added to Excel as part of the Dynamic Arrays upgrade introduced a couple of years ago.
You can download a workbook with an example of how to spill data. You can access an accompanying video at the end of this article.
Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
Let’s go through an example. We want Excel to calculate the down payment and amount to be financed for several loans by multiplying the selling price by the percentage required for the down payment and subtracting the down payment from the selling price, respectively. These are simple calculations. We will create just one formula for the down payment calculation that will populate the down payment for all loans. We will do the same for the amount to be financed, as shown in the screenshot below.
The formula for the down payment is created in cell F2. Because I want this one formula to calculate the down payment for all my loans, I will multiply the selling price of all loans by the down payment percentage required for each loan. Therefore, the formula in cell F2 is =B2:B10*E2:E10, as shown in the screenshot below.
Note that the formula was entered only into cell F2 and can only be edited in that cell. Although the formula is visible when you click in the other cells with the calculated values, nothing was entered in those cells, and nothing can be changed there. Make sure that nothing is entered in the adjacent cells you want to be filled or a spill error will occur.
Now, let’s create a formula in cell G2 that will populate all amounts to be financed by selecting all selling prices and subtracting all down payments. The formula is =B2:B10-F2:E10 or =B2:B10F2#. Excel uses the pound sign (#) to reference a spilled range, and that’s what will appear if you build the formula by selecting the cells F2:F10 , as shown in the screenshots below.
For Excel 365, formulas that return a set of values (or an array) are able to spill. Therefore, you can have your formulas spill when using simple calculations, as we did here, and also when using more complicated functions. However, spilling will not occur within an Excel table. Therefore, you could place the formula(s) outside of the table, or you could convert the Excel table to a range of data by clicking anywhere in the table, right-clicking, and selecting Table, then Convert to Range.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org.We regret being unable to individually answer all submitted questions.
