Does anyone know of a way to limit the number of rows in Excel?
I much prefer using an entire column range in my formulas for SUMIFS VLOOKUP etc, but this isn't a great idea on some of the larger workbooks.
As the ranges can vary I don't really want to define a range or name to simplify things for other users and wondering if there is another way.
SUMIFS and VLOOKUP on full sheets are efficient unless you have some unrelated data somewhere deep down in the sheet. If you don't then the engine will not take into account all the blank rows at the bottom with no data. However, array formulas will take those into account even if they are empty. But it's not common for people to use array formulas.
To answer your question you may want to explore tables in Excel. I mean actual table objects. Let's start with this data:
Insert -> Table
You will see a table created with a name ("Table1") given automatically.
Now if you use the following formula it will only take into account cells which belong to this object.
=SUMIFS(Table1[col2],Table1[col1],"b")
As can be seen, the value un B8 does not affect the formula result. We have limited the rows to lookup, but we provided the whole column from the table object.
A nice thing about tables is that if you write something just below the table, the table will automatically expand one row down. Also, you can manually drag the bottom corner of the table. In such cases our SUMIFS formula outside of the table does not need to be changed. Also, if you have the same formula in the whole column, the formula will get automatically copied down after you insert new values.
One bad thing about tables - you cannot use array formulas in them. This may be a limitation to some, but definitely not everyone, because that is an advanced thing people usually don't use.
