Spill is a feature that was implemented in 2019.
This is a very beneficial feature for books with a large amount of formulas.
Spill is an updated version of Array formula, and its shortcomings have been eliminated.
Comparison of use cases with conventional methods.
As an example, we will show you a sample that calculates the amount based on price, quantity and tax rate.
Normal Formula.
The most common method is to create a formula in one cell and copy it to another cell.
Advantages
The advantage is that it is an easy and commonly known method.
There is nothing wrong with small sheets.
Disadvantages
When large numbers of the same formulas exist, there is a danger of
- Forgetting to copy formulas when they are changed.
- Destruction of formulas.
That mistake is fatal because it is hard to notice.
Also, if there are fixed cell references, such as tax rates or commission rates, there are a good number of people who are not good at them, and absolute cell reference are necessary, which are often forgotten.
Array formula.
Array formula eliminate the disadvantages of regular formulas.
First, select a range of cells. This is the cell range that was copied in the normal formula.
Then enter the formula, specify the price and quantity part in the cell range, and press Ctrl+Shift+Enter to confirm the entry.
In the example below, select G5 to G7, enter =C5:C7*D5:D7*(1+C1) in the G5 cell, and press Ctrl+Shift+Enter to confirm.
The formula will then be applied to the entire cell range.
Array formula are displayed enclosed in {}.
Advantages
Changing any one formula in this G5 to G7 will change the formulas in all cells, so there is no need to forget to copy.
Also, this method does not require absolute cell references.
Disadvantages
Ctrl+Shift+Enter for input confirmation is also required for formula changes.
Even if you have not changed the formula, Enter alone will generate an error message.
An even bigger problem is that it is impossible to perform any column or row addition operation that would change the range of the array formula.
In the sample case, it is impossible to add or delete rows 5 through 7 as long as there is an array formula.
This disadvantage of array formula is eliminated in Spill.
Microsoft also plans to move from array formula to Spill.
Spill. Dynamic array formulas.
Spill is an improved version of the array formula.
Enter =C5:C7*D5:D7(1+C1) in one cell only.
Confirm with Enter only, and the formula will be automatically copied to I5 to I7.
This copy is called a ghost and is displayed in gray text and cannot be edited except in the original cell.
The ghosted area is where the formulas do not exist.
ISFORMULA and FORMULATEXT functions can only be used on the original cell.
Also, if you select any cell in a spill range, the range of the spill that belongs to the original cell will be highlighted in a lighter color.
If any value is entered into a ghost cell, the original cell will result in a #SPILL error.
Advantages
It takes over the advantages of array formula while eliminating their disadvantages.
- It inherits the advantages of array formula while eliminating their disadvantages.
- Only the Enter key is required to confirm input.
Addition and deletion of rows and columns are not prevented, and changed formulas are automatically reflected in other cells.
Also, if you add # to a cell that has a formula for a Spill, it becomes the entire range of the Spill and can be incorporated into the function.
Disadvantages
Cannot be nullified.
I know many of you feel you don't need the spiel.
Once it is applied in an update, it cannot be stopped in the options settings.
Spill functions.
| 関数 | 機能 |
|---|---|
| FILTER | Get the range of cells that match the criteria. |
| RANDARRAY | Random numbers in multiple cells. |
| UNIQUE | Delete duplicates in a cell range. |
| SEQUENCE | Create sequential numbers in a cell range. |
| SORT | Sort a range of cells. |
| SORTBY | Sort cell ranges based on multiple columns. |
---