Can Your Spreadsheets be Improved?

Many businesses use spreadsheets, but can they be improved? According to a poll I took, yes, they can. In fact, all but one of the people who said that their spreadsheets were ‘awesome’, were clients of mine. Here are the results from that poll. As you can see, I got a low answer rate here, I suspect many were too embarrassed to admit the sate of their spreadsheets.

If most business are using sub-par spreadsheets, how do you know if yours need to be upgraded or re-made? Here are 10 things to look out for.

1. Do you find that you are using certain columns, not using others, and then requiring some that you don’t have? This could be because the spreadsheet does not fit your processes and needs to be updated.

2. Does it give you the reports that you need? If you have a good spreadsheet, it should be set up to produce the reports that you need, you shouldn’t have to fiddle with pivot tables to get this info. If your spreadsheet doesn’t do that for you, it may be time for a new one.

3. Are there corruptions with the spreadsheet? If you have #REF errors especially, that means that cells have been moved and the spreadsheet is corrupt. You need to get that fixed or replaced.

4. Have you over-written any formulas? If your spreadsheet is locked and secured, you shouldn’t be able to over-write formulas, so if you have, it is probably not reliable.

5. Can you select the options that you need? I usually create dynamic drop-down lists, so you can edit the options, however some spreadsheets don’t give you that option. If you can’t input the correct data, you need to address that.

6. Is your spreadsheet neat and tidy? This may sound like a luxury, but entering data, training staff to use a spreadsheet, keeping errors to a minimum, and being able to understand a spreadsheet, can all come down to how organised it is. If you open a spreadsheet and it just looks like a mass of data, it is not simple enough. Mine are nicely laid out, with colour co-ordinated headers, so you know what data goes where.

7. Do your spreadsheets fit your process? If you get a batch of data, and have to retype it somewhere else, then the answer is no. Your spreadsheets should be fit for purpose, so if you get data from a source, your spreadsheet should be able to use that data as is. Many of my conversations with clients start with, “My spreadsheets take too long to use..”.

8. Are your spreadsheets too bulky? If you use the same spreadsheet for years, you may collect too much data. Many of mine are made for 12-month cycles, so you use a new one each year. This makes reporting easier, and the old ones can be archived. It can also be a rolling 12 months with the option to archive old data. This can also go for huge spreadsheets that could be done as a smaller spreadsheet. I once changed a 200 Meg spreadsheet, to less than 10 Meg when made properly.

9. Where and when did you get the spreadsheet? If you’ve inherited it from someone, or built it over time, it’s quite likely that it is not 100% fit for purpose. Yes, we could use it as a starting point to decide on what is needed, but I can leave out what you don’t need, and add in what is missing.

10. Do the pros of having the spreadsheet outweigh the cons? This is the best way to see that you need a new spreadsheet. If you open it and feel a sense of dread that you have to use this thing, then it is not great. If you find that the effort you have to put in to use it, is outweighed by the amazing reports and output that it provides, then you have a winner. If you’re begrudgingly sit faffing with it, and it provides very little in return, then you need a new one. I endeavour to make mine give as much out as it can, with as little input as possible. Yes, you need to add data to make it work, but the spreadsheet should do the heavy lifting and the lion’s share of the work.

I hope that has helped you. If you feel that you would like to discuss getting a new spreadsheet after reading this, I am happy to have a chat to see what can be done.