What is the cleanest way to write an array formula to facilitate the addition of more rows?

Question

I have a spreadsheet with several columns that use array formulas, specifically referencing other columns of data. I started off using the A$1:A$100 syntax (if 100 was the last row of data in the spreadsheet), but found that this was rather time consuming if I ended up adding more rows of data since I have several columns like this. I have also used the column A:A syntax, but it seems to be cumbersome (i.e. it takes a while to calculate), since it's referencing the entire column of data.

Is this the cleanest approach to avoid having to change the formulas whenever I add new rows of data?

Answer 1

I'm sure this is a debatable topic but I'll throw out a couple suggestions.

First one is you could change you data into a table (select the range then insert>table) you can then refer to the range as Table1[tax] or whatever name you give it.

The other solution would be to use dynamic named ranges. If you use to many of these it could also slow down your sheet though.

You could define your named range as something like

mytable =OFFSET($A$1,0,0,COUNT($A:$A),3)

which gives a table with as many rows as column A contains cells and 3 columns wide). For other ways to define this see Ozgrid

Then you can reference the second column using =INDEX(mytable,0,2). By using a single named range for the entire table it reduces the number of dynamic names it has to evaluate and should be quicker.

Overall the first method is probably the cleanest way to go, the excel tables will extend as rows are added.