20 Features for better spreadsheets

I see spreadsheets that are used by businesses all the time, and I see many issues repeated over and over again. Issues that can be relatively easily fixed but cause huge problems for people. When I make spreadsheets, they will use all of these suggestions and more, but if you implement these in your spreadsheets, they can only improve. Here are 20 features you can use to improve your spreadsheets.

 

Colour co-ordinated headers

This is a simple one, which very few people do. Pick 2 colours for your spreadsheet, you can even use corporate colours to brand it. Use one colour for headers for formulated cells and use the other colour for data entry cells. Now you and your staff know which ones to fill in, and which ones to ignore.

 

Separate entered and calculated data

Keep the data entry cells and the formulated cells separate. So many times, people follow the logical progression, mixing formulated cells with data entry cells. If you keep them separate, not only is it easier to know which ones to enter (along with the colour co-ordinated headers) but you can also lock the formulated cells while still having a data filter on the data entry cells.

 

Flag up required cells

People not filling required data in, is a big problem. It means that calculations might be incorrect, if not all cells are filled in. Like in a web form, when it won’t let you proceed if you haven’t completed everything, you need something similar in Excel. If you use some clever formulas, you can use conditional formatting to turn cells yellow when required and clear them when completed. This is a good way of showing people what data is required and ensuring that it is all completed.

 

Flag up obviously incorrect data

This can be fine-tuned if needed but should at very least show obviously incorrect data. One very good example here is when it comes to dates. Dates in Excel are formatted as numbers but show as dates. However, many dates get exported as text from software programmes. Also, US and UK dates often get mixed up. So, by simply doing a check if the data entered is a value, it will flag up either of the issues mentioned if these criteria are not met. If seen it many times when transferring people’s data into a spreadsheet I made, and it lit up like a Christmas tree! The issue there, is that any calculations done off those dates in the old spreadsheet, would have been wrong! Checking for obviously incorrect data is key.

 

Drop-down lists where possible

Having a definitive list for people to choose from is better than letting people simply type in text. Not only is it easier to show people the options, but it keeps data more accurate. For example, if you have a list of cities, people can select Manchester. Which means all for Manchester are in as Manchester, you can’t type in ‘Manchster’ by mistake as it won’t be allowed. This means that any formulas using that data will be accurate.

 

Editable drop-down lists with warnings

I’ve said to use drop-down lists, but these lists need to be editable. So, put these drop-down list options on another page, and refer to them for these lists. That way, you can edit them if needed. Not only does this allow for ‘futureproofing’ but if you flag up incorrect data, you can then see all the instances where you have used now removed options from the drop-down list. So, if you remove Manchester from the list of possible cities, then all the occurrences where you’ve used Manchester will turn red. That makes it easier to adjust your data.

 

Limited data ranges

I often see spreadsheets where they simple start the columns and leave them open ended (height-wise). This means that they are relying on Excel to auto-complete formulas as they enter more data. It also means that they need to use the whole column reference in some formulas. Set an end point. Mark it off. Allow for growth. So, instead of just using column B, use range B11:B510 for example, for 500 rows (or whatever is needed). Mark it off with borders so it is clear. That will allow for more effective formulas, and no auto completion, which is essentially Excel guessing. This is not required if you use table, which many advise. There are reasons I don’t use tables, but some prefer it.

 

Logical flow of data entry

Think about how you enter data before deciding the order of the columns. For example, if you have 5 sets of data at the start, and 2 sets a week later, and 1 set a week after that, put them in that order. Put the first 5 columns first, then the next 2, then the last 1. Possibly even border them off so show the breaks. So often I see people filling in the first 3 columns, then leaving the 4th because they don’t know that yet, then filling in the next 2. It is disjointed, it’s messy, and its error prone. By keeping them in order, it makes it easier to complete and makes it easier to see what still needs to be completed.

 

Fewer data sets

So often I see people with unnecessary data sets. Sales for January, then sales for February, etc. All in different tables. Then loads of disjointed formulas trying to analyse data from them all. The reason people often do this is because they can’t extract monthly data unless it is separate. By putting all the sales data in one section, and simply using the date to determine what month it is in, it makes it so much easier to enter, analyse, and re-use the following year. There are instances where you need to have separate data sets, and I will address those, but in many cases, keeping data together in one ‘pool’ makes it far easier to use going forward.

 

Calculated data where possible

You might not believe this, but I’ve seen many a spreadsheet requiring double entry. For example, one recently where they entered the unit price, the number of units, and then also the total line price. Unit price x units. However, they worked I out on a calculator, then manually typed it in! Now, you may not go this far, but you might do similar things. Like assigning a status to a row, which could be calculated from the rest of the data. Doing things like this means that your reporting is not as accurate as possible, because people will forget to change data. Use as little raw data as possible, and then do as much with that as you can.

 

Colour alerts for met criteria

This can be as simple as flagging up values above or below a target, or cross referencing a few data sets to determine an outcome. However complicated, this should be a staple feature. If you have a list of sales, seeing at a glance which ones hit the desired margin (for example) is key. Set colours for those so that they stand out. Make sure they are accurate, the last thing you want is for some sales to turn red, but only some of those to genuinely be an issue, then you end up ignoring them all.

 

Comparable data sets

I’ve said before that you need to try and consolidate data into one data set. However, sometimes you need two. For example, you have a list of clients, one row per client, but each client could have a few jobs. So, you have a client list for the unique clients, and then you have a job list for all the jobs. Make sure that the client list feeds the client column in the job list. Make sure it’s on a drop-down list to keep it accurate. That means that you can use the unique client name to cross-compare both sets of data.

 

Editable cells rather than hard-coded formulas

Hard coding means to type a number directly into a formula. For example, VAT on £100. At the time of writing this, VAT is at 20%. So, 100*20% would give you the VAT on £100. However, it may not stay at 20%. So, instead of then having to go through your entire spreadsheet trying to find all the VAT references, make a VAT Rate cell (on another tab even). Then reference all formulas to that cell. Then if it changes, simply change one cell and all the references to that cell update.

 

Dynamic drop-down options or calculation over-ride

This is tricky to explain but I will try. Let’s say you have a value column, which is 99% always the same value (or calculated value) but in the odd occasion it might need to be changed. If you make it a data entry cell, you’re always entering it, even when it should be automated. If you make it a formulated cell, you can’t change it when you need to. There are 2 options here. Either use a drop-down list to show you the calculated value, with the option to over-ride, so you simply select the correct value as default. Or, have an ‘Over-ride’ column, which you only complete if it differs to the norm. Then you can calculate the norm as default, unless an over-ride is entered.

 

Hidden calculations

I often see spreadsheets with 32 columns, when only 5 of them are data entry, and 5 of them are useful to know. The other 22 columns are simply helper columns for workings. No harm in that whatsoever. The issue comes in when they are all in one messy lump. I’ve spoken about separating the data entry and formulated cells, but then keep the workings separate and hide them. Showing those only confuses the user. These columns don’t need to be in a logical order to follow the flow of the formula, but rather a logical order for the data entry and viewing.

 

Adjustable 12-month periods

I so often see spreadsheets created for a 12-month period, which makes complete sense. But then come the end of that period, and people are trying to make a new template for the new year, adjusting all the headers and dates. At very least you should be able to open a new template of the spreadsheet, change the old year to the new year in ONE place, and have the rest update. If you really want to be adventurous, why not have a rolling 12-month period, so that you always have 12 months of data to view. Both are possible, and I usually use whichever is best for each case.

 

Locked worksheets and workbook

If you are using a spreadsheet, rather than making a spreadsheet, it should be locked. Unlocking data entry cells, and the protecting the workbook, is a basic requirement. Yes, this means you can’t over-write formulas, but that is the point. I even lock the spreadsheets I make which only I use. It is so easy to over-write something you shouldn’t, so lock your spreadsheets.

 

Freeze panes

Some don’t even know you can do this. Freeze pains, so that you can scroll without data disappearing. So, you have important data n the first 3 columns, and then loads of columns after that. Freeze the first 3 columns, so the rest go behind those 3, so you can always see them as you scroll to the right. This is another reason why data layout is key. Also, don’t start in the first row. Start further down, so you can put headers at the top. This way, you can scroll right and down, without losing sight of the headers, totals, or important columns.

 

Dynamic reporting

Dynamic reporting means that you use filter data before it reaches the report. Think about one element here as an example. You have 10 staff, and there are 12 months in the year. So, if you made a graph per staff per month, that’s 120 reports (130 if you include all the staff each month). I’ve seen reports with this much data. However, if you have a filter to select the desired month (from a drop-down) and the desired staff member (from a drop-down), then you can have one graph that could show your desired data. If you leave the staff selection blank, it could include all staff together. This allows for much more compact, but far more versatile reports.

 

Separate dashboard

What’s the difference between a report and a dashboard? No, this isn’t a joke. A report is a detailed dive into the data. A dashboard is a high-level view of KPIs that lead you to know what to address or to report on. Many just morph these two into a weird combination of a mess. Keep the two separate and keep them doing what they should be. A dashboard should be to look at the spot key issues, and a report should be to analyse what has taken place and to help make key decisions. If you combine these two, it could render them both ineffective.

 

There you have it. 20 improvements to make to almost any spreadsheet to improve them. I consider all of these and more when making spreadsheets for people. So, if you’ve seen any of these that you wish to implement, but don’t have the skills to do so, please get in touch. Let’s get you the right spreadsheet, which is most efficient and accurate.