by Joel Ericsson
Everyone has dealt with spreadsheets that just didn’t behave. No matter how many times you tried to make it do what you wanted it to, it seemed to have a mind of its own. It did its own thing. Tasks that you knew you’d somehow done easily before now took hours to go back and manually correct. Excel just didn’t like you. We’ve all been there.
The people that make Excel look easy don’t always know about hidden buttons. Their secret is that they know what Excel wants. They know that Excel has certain expectations, and if you can give Excel the data in the way that it wishes to see it, Excel will like you. They know that when Excel is in a good mood, things just work. Their secret is that they know how to make Excel happy.
Every spreadsheet is different, but there are basic guidelines that can turn Excel into your friend:
- The leftmost column and/or topmost row are labels.
- The bottom most row and/or rightmost column are totals.
- Everything else is numerical data.
- No spaces. There are no breaks or empty rows or columns between related data.
When Excel has to make guess about where information is, it looks for these things. If you use AutoSum to create a function to total a column from the bottommost row, Excel will look for numbers that are above and adjacent to that cell, and then use all the numbers it can find until it runs into text, which it assumes to be the label at the top. If it finds a break in those numbers, it assumes that is the end of the related information.
Obviously, not every sheet can be formatted like this, but the closer you can get, the happier Excel will be. Tools like AutoSum rely on this assumption, and this structure is the difference between finishing with a single click, or manually correcting each formula. Some of the Excel tools will let you manually correct to what Excel assumes, but others simply will not work if you don’t take the time to make Excel happy first.
If Excel isn’t behaving how you expect it to, instead of fighting it can be helpful to take a step back and see if you’re behaving the way that it expects you to.