Last used cell in Excel can slow you down

Many Excel users complain that Excel calculations are very slow. Sometimes they are slow because they are so many of them. Hundreds of thousands of vlookup functions that search thru tables with tens of thousands of rows will definitely take a long time to calculate. You can find many articles that give you advice how to use some functions over others or use Power Query and Power Pivot instead of merging your data using formulas.

I want to point to a cause of slowness that is not about the formulas themselves but when Excel takes too much time to calculate because it "thinks" that some sheets are much larger than they really are.

Many people write formulas referring to entire columns like "SUMIFS(G:G,I:I,A3,J:J,B3)."

The number of rows in the Excel grid is 1024*1024 or 2**20 or 1048576. Providing Excel with an entire columns is actually asking it to search thru over a million rows and do multiple comparisons on each row. If this was Excel's behavior it would be very , very , very slow indeed.

Excel is smarter than that. It maintains for each sheet a property of what is the last cell used and it knows not to search beyond this row because there is nothing there.

So what's the problem? In quite a few cases this setting of the last cell is not where it should be and many times it is actually pointing to somewhere in row 1048576. This can happen because of formatting or dragging values all the way down.

As long as the last used cell points to the last row, it will slow down any calculations searching data in this sheet.

It could be 100 times slower if your real data is only a hundred rows and Excel is searching a million.

Solution:

If you suspect that something like that is hapennig in one of your Excel files. You can go the suspicous sheets and click Ctrl+end, this will take you to the last used column in the last used row. If this location is way beyond where it should be, you can delete all the extra rows, save the file and this will (almost always) reset the last used cell. In some cases your file will also become much smaller.