Find me on LinkedIn

Mind the gap!

When you're designing your spreadsheets, it can be nice to lay things out with blank rows and columns to separate the data.  Although this can make it look pretty, there are some things you need to be aware of so you don't end up with incorrect results!

Sorting

If you choose the titles row on the worksheet and click on the Sort option, Excel will only automatically include the rows and columns to the next blank row.  So, in the example shown below, Excel would select rows 2 and 3 and columns A to C only so the remaining data would NOT be sorted.   

 

If you proceeded with this sort, the prices would no longer match your original intentions!

Autosum

Excel's autosum function would also ignore blank rows giving you can incorrect total.  So if, for example, you selected cell F10 and clicked the autosum button, only rows F8 to F9 would be included.

To include ALL the figures in column F, you would need to manually adjust the range to include these.

...and there are other potential problems

Excel tries to be helpful when you create charts, pivot tables, tables or formulas so will often select data areas automatically for you, but Excel assumes you ONLY want the data immediately adjacent to the cell you selected so will select just that.  Although you can of course manually extend the range, this makes this more difficult and prone to error.

My advice would be never to leave blank rows and columns in your data; there are other formatting techniques you can use (or even conditional formatting) which will allow you to present your data in a neat and easy to read format without the risks to accuracy that gaps represent.

Contact me today for more information

If you'd like to learn more about the power of Excel for your business, get in touch to discuss my development or bespoke training options

Leave a Comment