10 Features that would improve your spreadsheets

Introduction

I get sent many spreadsheets, some of which are vital to the running of a business, and most of what I am sent is well below par. People try to make spreadsheets with minimal experience, and while that is a noble effort, it means that the spreadsheets are not nearly as efficient as possible. I will show you 10 features which will improve just about any spreadsheet.

Drop-down lists

These are really under-used. Any cell which requires one selection from a few options, should be in a drop-down list. I see the lack of a drop-down list cause a few issues. Firstly, for consistency. For example, if you have a list of clients, and you select Spreadsheet Solutions, then you can select the same name again, which makes your reporting accurate. If you manually type in the name, and then next time you type Spreadsheet Solution, it won’t count the two as the same business. Drop-down lists help to eliminate errors which could cause problems down the line. Not only text, but values too. If you have 5 different rate options, then have those 5 in a drop-down list. Then people can’t make up other rates, or leave off a zero, or make another mistake. Drop-down lists make data entry easier, and the data cleaner, and too few people use them.

Data validation

I’ve seen spreadsheets where people had zero checks for incorrect data. They came to me to ask me why their figures were not adding up, and when I checked it was because some of the values entered were entered in a text, and it wasn’t calculating. It looked the same to the naked eye but was not recognised by Excel. A simple formula to check if it wasn’t a value, and some conditional formatting to turn the cell red, lit the spreadsheet up like a Christmas tree. The other time this is important is to check if data entered is available in the respective drop-down list. You may think that you can limit drop-down selections to what is in the list, but that doesn’t solve pasting in data. Nor does it help if that option is later removed from the list. All my spreadsheets now have a standard check for obviously incorrect data, which can be edited to be more specific.

Checks for missing data

Missing data. It can cause many problems and is completely avoidable. If you’re doing functions that rely on the data, like SUMIF of COUNTIF for example, then leaving out the data will give you incorrect figures. I see it all the time. People asking why their reports look inaccurate, while there is a load of missing data that is causing it. I now make sure that all data entry cells have a check for not only incorrect data, but also missing data. So, when you start another row of data, as you complete it, the spreadsheet shows you what is still required by turning yellow until you fill it in. Kind of like compulsory fields in a website form. The reason they have those is to make sure the information is all entered, and they also have checks (as far as possible) to make sure what is entered is correct. That is exactly what I do with spreadsheets, so that you have the cleanest, most accurate data possible with which to report.

Automated reports

If we’re going to be collecting all this clean and useful data, it makes sense to put it to good use. Many people have spreadsheets where they enter in the details themselves (usually ones they have made) and then at the year end or some other time, they then bring all that data to find someone to analyse it. What I prefer to do is make the spreadsheet with the built in analysis, so as you enter data, the report tab updates accordingly. This could be for a month, project, year, etc. If you know what you want to see in the reports, it makes sense to build them into the spreadsheet. This way you can get live reports whenever you want. Simply click on the report tab and see the current report for all the entered data on the other tabs.

A dashboard

Reports are great, but you may not need to see all the bells and whistles every day. I know in my business, that I look at a few key figures and graphs each day, and so those are on the dashboard. The idea behind the dashboard is to see high level information, that is crucial to be seen regularly, all on one screen. Information could even be duplicated on the report and dashboard, as the report is usually a few pages, and can be saved neatly as a PDF. The dashboard is for quick, live, viewing. Many people make over-complicated dashboards, and others do inconclusive reports, so having one of each means that you can tailor each of them to be more effective.

Adjustable settings

So many people hard code their formulas. This means that they enter what should be adjustable data into formulas that shouldn’t be edited. So, for example, you want to calculate how much money you made for 5 hours of work, and you know that you charge £40 an hour. You then do the formula as [No. hours] x 40. So, when you type in 8 hours, it works out 8 x 40. However, then when you wish to change the hourly rate, you’re frantically searching for all the affected formulas to change. If you had am [Hourly Rate] cell somewhere, you could simply change that. Then if you’ve used [No. Hours} x [Hourly Rate] then they would all simply update. This also goes for lookup tables, thresholds for colours, period limitations or alerts, etc. I usually put all of these editable cells on one tab called the Settings tab, so that they are easy to find and all in the same place.

Dynamic CSV data import

There are so many websites, software packages, CRMs, etc that collect useful data. This means that people want to export the data, and then report on it. Excel is great for this, however there could be one spanner in the works. The raw data often changes format. So, one day there are 9 columns of data, and the next day there are 10, and all the columns have moved up 1. So, if you have a spreadsheet designed to read that data, it is all out of alignment, and it doesn’t work. What I do is create a tab where you can enter the data, but then YOU have control of what headers to look for, to look up the correct data. If the layout changes the spreadsheet either corrects itself or alerts you to the changes, so that you can adjust the settings, to get your spreadsheet back on track. Yes, this can make the spreadsheet much larger, so sometimes I create a ‘data sorter’ spreadsheet which does the heavy lifting, and then that clean data can be pasted into another spreadsheet, which is free to analyse it.

Data filters and sorters

Many people do use the data filters functionality in Excel, the problem then is that often data is hidden and missed. Also, if they need to filter one batch of data by criteria from another batch, filters are useless. I often actually create a data filter tab, which can analyse various sets of data, and where you can make selections to filter the data. When doing so, it shows a list of all valid entries, and not even the whole entry, but just the required data. So, for example, if you had a list of projects on the go. You could ask for all projects, at stage 4, which are due next week. It will then give you the key information for all the relevant projects. When I show people an example of this kind of tab, they usually want it, because it is extremely useful.

Locked and secure

This is something that I see regularly, and I just don’t understand why people do it. Unlocked spreadsheets being used by various staff. It is SO dangerous. I have spreadsheets that I built, and only I use, and I STILL lock them. Just to prevent me from accidentally over-writing something. If you have a spreadsheet and it is not locked, you need to rectify it. Select all the cells that should be unlocked, right click and format, and then change them to unlocked (under protection). Then go to Review and click protect sheet. Enter a password (don’t forget it). That means that people can only enter data into the cells you unlocked, and not the others. The unlocked cells should be data entry cells, and the formulated cells should be locked. That should be a minimum requirement but is very seldom used.

Neat, tidy, and easy to use

People are so pre-occupied with learning fancy formulas, or doing pivot tables, that they miss out on the basics. Lay the data out nicely, and you’ll have a far better working experience. Data entry cells in one place, and formulated cells separate, not all mixed so people don’t know what to enter. Different coloured headings to show what is what. Nice, neat white borders so the spreadsheet is clear. Branded in your corporate colours with your logo. Limit the rows so that your data is manageable, and the formulas are as small as possible. Use all the other features that I have spoken of, with a nice, neat spreadsheet, and you’re onto a winner. Yes, people are often impressed with what my spreadsheet can do, but the biggest gasp I get is when I show them the new spreadsheet. The initial view of a neat and tidy spreadsheet already sets it on the right track.

Conclusion

So, there you have it. 10 features that could improve most spreadsheets. I see loads of spreadsheets and many of these points are lacking in most spreadsheets I get sent. I’m sure you’ve realised by now that I use all of these in my spreadsheets, and were you to get me to make your spreadsheets, I would include any applicable features in your new spreadsheet.
If your spreadsheets fall short with regard to any of these features, maybe it’s time we had a chat.