How to use the spill range in Excel

Excel’s spill range is a product of using dynamic array functions. Learn how the spill range fulfills these functions.


Image: PixieMe/Shutterstock

Over the last few months, I’ve written several articles about Excel’s newish dynamic array functions. In many cases, they can replace older, more complex expressions. The new functions do all that calculating internally and return a range of result values. This range of resulting values is called the spill range. When using these dynamic array functions, you’ll need to know all about the spill range, so read on.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. These new dynamic array functions are available only in Microsoft 365, Excel for the Web, and Excel for Android tablets and phones. There’s no demonstration file; you won’t need one.

Let’s start with a good definition of spill range

When a dynamic array function or expression produces more than one result value, those values spill over into the adjacent cell(s). In other words, the spill range is the array of values returned by a dynamic array expression or function. It’s important to note that they are merely resulting values and the only cell that contains an expression or function is the cell where you entered it. The top-left cell contains the expression or function and the remaining cells in the spill range display only the resulting values. This means that you have only one cell to alter when modifying the expression or function; however, all the resulting values will update accordingly. Fortunately, it’s not as complicated as it first sounds.

See also  How to configure the ONLYOFFICE CRM for your business needs

SEE: Windows 11: Tips on installation, security and more (free PDF) (TechRepublic)

When you click any cell within the spill range, Excel displays a blue border around the entire spill range. Everything within that blue boundary is a result value. Figure A shows a small spill range for the UNIQUE() dynamic function in H5. This dynamic function returns a list of unique values from the Region column:


In the screenshot, it’s difficult to tell that the boundary is blue because it’s thin. Currently, the spill range is H5:H7. In addition, the formula bar shows the explicit range instead of using the structured Table referencing. I did so to show both in use; they will both work. 

Figure A


  Use the UNIQUE() dynamic function to return a unique list when the source data contains repeating values.

Because the source data is a Table object, the dynamic function in H5 will update as you update the source data in the Table. For instance, if you add a new record that includes a new region, the list beginning at H5 will update accordingly, as shown in Figure B. If you’re not using a Table to store the source data and you enter a value outside the original range (in this case the Region column), the UNIQUE() function spill range will not update. If you delete the dynamic function in H5, the spill range disappears completely.

Figure B


  Because the source data is a Table, the dynamic function updates with the Table’s data.

So far, there’s been plenty of room for the result values. What happens when there isn’t?

What is the #SPILL! error?

Although the previous section has been easy—no errors, no problems—that won’t always be the case. Figure C shows a spill range error, #SPILL!. The anchor cell, E5, hasn’t changed; the function is exactly the same. However, instead of seeing the result values we saw earlier, we now see an error.

See also  Understanding the Basics of SQL Data Recovery Procedures

Figure C


  Now the dynamic function returns an error.

If you guessed that the value in H7, test, is in the way, you’re right! When you see this error, the problem is almost always an inadequate number of cells in which to fulfill the original calculation. If the dynamic function can’t return all of the result values, it returns the #SPILL! error.

The solution is easy. You can delete the offending data in the spill range, or you can move the original dynamic function to a cell where there’s plenty of room to fulfill all of the calculated result values. 

When the problem is obvious, it’s easy to solve. When the problem isn’t as obvious as the example in Figure C, don’t despair. Instead, check the error tag for more information. The error tag is the exclamation point shown when you select the original expression or function (Figure C). Simply click the smart tag to see the error list shown in Figure D.

Figure D


   The error tag shows the possible errors.

Let’s review the possible errors:

  • Spill Range Isn’t Blank is probably the most common error. This is our problem in Figure C. It’s obvious in our example, but what if the problem cell is off screen? When you can’t find the offending value, choose Select Obstructing Cells. Doing so will take you to the cell that’s blocking the result values. At this point, you can decide to delete the value or move the dynamic function.
  • Help On This Error opens the Help files with a list of what these error messages mean.
  • Select Obstructing Cells will select the cell within the spill range that is blocking the resulting values. It might also indicate merged cells because the spill range can’t accommodate merged cells.
  • Show Calculation Steps shows the Evaluate Formula dialog where you can step through the calculations, similar to the way you might step through VBA code in the Visual Basic Editor.
  • Ignore Error lets you clear the smart tag and continue working despite the error. I don’t recommend using this one unless you have a specific reason for doing so.
  • Edit in Formula Bar gives focus to the actual expression or function in the Formula Bar so you can edit.
  • Error Checking Options opens the Options dialog so you can review and possibly change these options.
See also  Digital marketing and SEO: This free training course will teach you the basics

In our case, which is probably the most common error, the first item, Spill Range Isn’t Blank is direct and informative. If you can’t find the offending cell, choose Select Obstructing Cells. You’ll not often run into the other errors.

In addition, the Table object doesn’t support dynamic array expressions or functions. Don’t enter these expressions and functions into a Table object. The source data can, and should, be in a Table, but don’t enter the actual function into a Table.

Excel’s built-in dynamic array functions are easy to use and easy to maintain as long as you know what the spill range is and how to accommodate its needs.

Read the following articles to learn more about these new dynamic array functions:

Also see

Source link

© 2022, Admin. All rights reserved.

istanbul escort aksaray escort arnavutköy escort ataköy escort avcılar escort avcılar türbanlı escort avrupa yakası escort bağcılar escort bahçelievler escort bahçeşehir escort bakırköy escort başakşehir escort bayrampaşa escort beşiktaş escort beykent escort beylikdüzü escort beylikdüzü türbanlı escort beyoğlu escort büyükçekmece escort cevizlibağ escort çapa escort çatalca escort esenler escort esenyurt escort esenyurt türbanlı escort etiler escort eyüp escort fatih escort fındıkzade escort florya escort gaziosmanpaşa escort güneşli escort güngören escort halkalı escort ikitelli escort istanbul escort kağıthane escort kayaşehir escort küçükçekmece escort mecidiyeköy escort merter escort nişantaşı escort sarıyer escort sefaköy escort silivri escort sultangazi escort suriyeli escort şirinevler escort şişli escort taksim escort topkapı escort yenibosna escort zeytinburnu escort porno 1080p porno izle 4k porno izle 720p porno izle abella danger alman alman porno alman porno izle aloha tube porno amatör amatör porno amatör porno izle anal anal porno anal porno izle arap porno asa akira porno asyalı porno bangbros porno bangbros porno izle banyoda sikis başörtülü porno beeg porno izle beyaz tenli porno izle biseksuel porno izle bisexsuel porno brandi love porno brazzers brazzers porno izle canli porno canli porno izle çinli porno çinli porno izle ensest porno ensest porno izle ensest seks erotik porno erotik porno izle esmer porno esmer porno izle etek altı fake agent fake taxi fake taxi porno fantazi pornoları fantezi porno izle fetiş porno fetiş porno izle fetish fransız porno fransız porno izle full hd hg porno izle gangbang porno genç kız porno izle genç kız sikişi genç teen porno izle gizli çekim porno gizli çekim pornosu grup pornosu grup porno grup porno izle hd pornolar hd porno hd porno izle hemşire porno hemşire pornosu hizmetçi porno hizmetçi porno izle ingiliz porno japon pornoları japon porno kızlık bozma kızlık bozma porno izle konulu porno konulu porno izle koreli porno köylü pornoları kumral porno kumral porno izle latin pornoları latin porno latin porno izle lezbiyen pornoları lezbiyen porno lezbiyen porno izle lisa ann porno liseli pornoları liseli porno liseli porno izle manken porno manken porno izle masaj porno izle masturbasyon porno izle masturbasyon pornoları mature porno mia khalifa porno mia malkova porno milf porno izle mobil porno mobil porno izle öğrenci porno izle öğretmen porno izle okul porno izle olgun kadın pornosu olgun porno oral porno oral porno izle oral seks porna izle pornhub pornhub porno izle porno film izle porno indir porno izle porno resimler porno star porntube porno izle redtube redtube pornoları riley reid porno rokettube rus pornoları rus porno rus porno izle sakso blowjob porno izle sarışın pornoları sarışın porno sarışın porno izle sarışın pornoları sekreter porno shemale sikiş sikiş sikiş izle şişman porno siyahi pornoları suriyeli pornoları swinger porno tecavüz porno teen porn türbanlı pornoları türbanlı porno türk pornoları türk porno türk porno izle türkçe altyazılı porno türkçe altyazılı porno izle xhamster pornoları xhamster porno xhamster porno izle xnxx xnxx porno xnxx porno izle xvideos xvideos porno izle yaşlı porno yeşilçam porno izle youjizz youporn youporn porno izle zenci porno güvenilir bahis siteleri bahis siteleri casino deneme bonusu casino siteleri deneme bonusu para yatırma bonusu bahis siteleri casino siteleribahis sitesi para yatırma