Is there a way to have an array formula "spill over" in earlier Excel versions? I have heard that the new Excel will bring dynamic arrays, but need this functionality on an older version of Excel.

I have an existing column of names in column A (ex: Jane, Sam, Joe) and I want column B to automatically adjust to changes made to column A. So if I add or delete a row from column A, B will also add/delete that row and be identical to column A, but through using a formula of some sort.

I would imagine it would look something like {=A:A} in the B column.

  • 1
    When you say "spill over", are you asking about a way to accomplish a similar result using native functionality in an older version that doesn't have the new feature, or a way to add the new feature to the old version, like copying a code module or something? BTW, this recent thread may be interesting for you: @ in the beginning of a formula – fixer1234 Aug 14 at 22:52
  • @fixer1234 I am asking about a way to accomplish what is being done in the newer version using the older versions (and it is ok to use VBA if needed). The answer given in the link you provided is related to what I am asking. – Jack Moody Aug 15 at 13:10

An array formula would indeed do the job. If your original table is in column A of "SheetA", create an new sheet, select the whole A column and type =SheetA!A:A and then (important) hit CTRL+SHIFT+ENTER.

Array formulas are very powerful but I confess they are a bit tricky to master. The incoming dynamic array functions seem much easier to use.

Edit: not sure I got you there. If the 2 columns are adjacent, and you remove or insert an entire row, both columns are affected.

Edit2: as discussed below, if you want no zeros in your column, you may use =IF(SheetA!A:A="","",SheetA!A:A) instead enter image description here

  • This works, but it seems to slow down the workbook considerably. Is there a way to avoid all of the unnecessary #N/A errors that pop up for rows where there isn't a corresponding value in the other sheet? – Jack Moody Jun 13 at 18:57
  • A way of adressing the slowing is to select part of the A colmun (ie the 500 first cells if you're sure that data in Sheet A will never be more than 500 long). Regarding your #N/As, I have none here, but I get zeros. A way of fixing that is to type =IF(SheetA!A:A="","",SheetA!A:A) instead of only =SheetA!A:A. – mll Jun 13 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.