One of these formulas is better than the other

If you've got Excel open click in the top left hand corner in cell A1.?Type "=Sum(" and then click on column B.?A formula like "=Sum(B:B" will start to appear.

Press "Enter" and you can feel quite proud of yourself. You've created a formula "=Sum(B:B)" that will total all the data in column B, no matter what the length of the data in column B.

Imagine you were creating management accounts from information that continuously changed. You might be thinking that a formula like the one above (totals all the data, no matter how much of it you've got) could be really handy.

Not everything Excel lets us do is good

Here's a much better version of the same formula that takes slightly more thought when you create it: "=Sum(B1:B200)".

The problem with the first formula is that it forces Excel to work right to the bottom of the spreadsheet. If you want to satisfy your curiosity, press "Ctrl" and your keyboard down arrow at the same time. You'll bounce straight to the bottom of your spreadsheet and see that there are more than a million rows there. The formula "=Sum(B:B)" forces Excel to work through all those rows. By creating a formula like that, you're forcing Excel to do more work than it really needs to, multiplying up file size and slowing down your computer. A better version of the same formula would have you working out a maximum size of the data list (e.g. it could never be greater than 100, 200, 1,000 or even 10,000 rows) and tailoring a formula accordingly. Any of those options would be better than the formula that forces Excel to work throught the million plus rows.

How to inflate file size and slow down Excel

Some of the formulas that Excel provides us with suck more processing power than others. On the occasions I've been called to look at exceptionally large spreadsheets that users are wrestling with, one of the first things I've looked at is the formulas being used. For instance, those in the know appreciate that an Index function (for?picking data out of a big table) absorbs less processing power than the more-traditional Vlookup, minimising file size and speeding up your spreadsheet. Similarly, avoiding the habit of creating formulas that make Excel look through a whole row or column is one of the things that will help keep your file size down. That way you stand the best possible chance of running spreadsheets that hum rather than hang.

Happy modelling!