5 Aspects to consider before ordering or creating a spreadsheet

Introduction

When I get people asking me to make a spreadsheet, most have already tried to make one, and have now come to me to make a better one. There are often a few elements that they have not considered, so I wanted to highlight these things, so that you would be more aware of them. If you make your own spreadsheets, then these are useful points to consider. If you’d rather I made your spreadsheets, then these are questions that will likely come up so that I can make the best solution possible. So, even if you don’t have answers to all these aspects to consider, even just thinking about the elements you do know, will help you to be more prepared for a new spreadsheet to be created. If you’re thinking of a new spreadsheet, have a look at these aspects to consider, and think about the spreadsheet you need. If I’m making your spreadsheet, I will help you with them, so see this as an opportunity to have more time to think about these options.

The process before and after the spreadsheet

This is something important, which very few actually consider, not just the available data and where it’s coming from which I’ll get to in the next point, but also the build up to the spreadsheet. For example, think about what part of the process is done before the spreadsheet’s part, and then what needs to happen afterwards. So, when the spreadsheet is done, and it produces the results of the report, what needs to be done with those results. If it’s for the directors, then we would include different data compared to if it is for a client or investor. If the data needs to be used elsewhere, or analysed further, then we would consider exporting it into tables to be converted to CSV. Sometimes I even do multiple export options, for different intended targets. All of this needs to be considered before setting out to make the spreadsheet.

The available data

This is key. What data do you have to ‘fuel’ the spreadsheet. Creating a spreadsheet for CSV data is VERY different to creating a spreadsheet for manually entered data. Sometimes you need a combination of the two. Importing CSV data (even if a manual copy/paste) would mean creating a space to do so, and then checking and re-arranging that data so that the spreadsheet understands it. It might need to be cleaned or recalculated. Allowing for manual entry means formatted cells, drop-down lists, data validation, etc. The other thing you need to consider is what data is available when. You don’t want to create 10 data entry columns, and then not put them in the natural order that you would complete them. So, if one element of data is known first, put it first. Then, if one element is only known a week later, put it afterwards, and have an alert popping up a week later to remind you to enter it. All of this attention to detail around the data, can make your spreadsheet more efficient, easier to use, and therefore more accurate.

Your objectives for the spreadsheet

The most important question to ask is, “What do you want the spreadsheet to achieve?”. That is the whole reason you are making it (or getting it made) in the first place. This needs to be clear. You’ve thought about the process before and after the spreadsheet, so you know where the spreadsheet fits into the process. You know what data you have available and what you need to collect, and now you consider the desired outcomes of the spreadsheet, taking into consideration the process that follows on from the spreadsheet. What do you want to know? So often, people are quick to tell me all about the spreadsheet they have made, but when I ask them what they want the spreadsheet to achieve for them, they have not considered that at all. They know they need a solution, but they haven’t really thought about what they are trying to achieve. If you know what results you want, you can make sure the spreadsheet is not only adequately equipped, but efficient in achieving those results.

The permitted variables and options

This applies to any set of data, whether captured manually or collected from elsewhere. The more checks and limitations you set on your data, the more you’ll eliminate issues. So, for example, you should have all cells that require a value, checking that it is a value entered. If a text is entered into a cell looking for a value, it should flag up. Why? Because many software programmes export values as text. It looks like a value to the naked eye, but Excel won’t include it in formulas. Imagine if your spreadsheet included some values and not others when calculating the reports. You’ll be basing decisions on incorrect reporting. The other reason to do this is for dates. Dates in Excel should be seen as a value, but many enter them in weird ways, which then is seen as text. A simple check could immediately show you that those dates are invalid. Drop-down lists are also essential. If you want to report on which salesperson is doing the most sales, you need a list of salespeople to choose from. If you manually enter Richard once, and then Rihcard by mistake the second time, Excel will not recognise the two as the same. Having a drop-down list of the options, means that you can only select valid names. If you then have an added check, even if you paste data in it will turn red if invalid.

The ongoing usage of the spreadsheet

The final thing to consider is the day-to-day usage of the spreadsheet. Imagine someone is using the spreadsheet 2 hours a day. What if that spreadsheet was more efficient, and the same results could have been achieved in 1.5 hours a day? That’s roughly 120 hours a year saving. Not only that, but the more efficient it is, the more accurate it usually is. All the elements I’ve spoken about will reduce errors and make the spreadsheet more accurate. This means that you will get better results and use less time. So, think about how the spreadsheet is used, think about what could save time and effort with each use. Even simple changes. One I do is in a date field, if I know that 99% of the time I want to enter the current day’s date. I then put today’s date in the drop-down list, with the option to over-ride it if need be. That way, if it is today’s date, I simply select the date. Yes, it only saves a second or two, but it also means that I don’t have to break my concentration to think of what today’s date is. It all adds up.

Conclusion

So, there you have it. 5 things to consider when making a spreadsheet, or having one made for you. Some of these aspects are obvious, like checking for number values, etc. In fact, many of these are included as standard in any spreadsheet I make. However, other elements like where the data is coming from or what you wish to achieve is unique for each spreadsheet, so if I am making your spreadsheet these are questions, I will ask you. However you choose to acquire your new spreadsheet, these are all things to consider, and they should all help you to get the best solution should you consider and accommodate them. I hope this has given you food for thought, and if you would like a spreadsheet from me, please get in touch.