We get sent many spreadsheets to rebuild, and we see the same issues over and over again. Many of these issues mean that people work themselves into a corner that they can’t get out of. We build spreadsheets differently, so that they are more robust, more user-friendly, and more efficient and adaptable. Here are ways that our spreadsheets differ to those sent to us in the first place.
What we see done
What we do
I ask questions before I make a spreadsheet. I see what you would like to achieve with it, as well as what can also be achieved with the available data. Only once that is all considered, do I start making the spreadsheet. This ensures that the spreadsheet is built the right way.
What we see done
So many people build spreadsheets for what they need, rather than what they may need. For example, they make it for for salespeople, and build it in such a way that more can’t easily be added. Then when they get a fifth person, they then have to manually adapt the spreadsheet.
What we do
I always ask how many you think you will need, and then add some more. I build the spreadsheet so that adding a new salesperson is as simple as adding a name to an available cell, rather than having to create all new tabs for the new addition. I also consider growth beyond your expectation.
Keeping the user in mind
What we see done
What we do
We believe that if a spreadsheet is easy to use, then people will make fewer errors when filling it in. So, I design all spreadsheets to be easy for the person entering data, to ensure that there are as few hurdles as possible for them. This usually leads to a more efficient spreadsheet.
What we see done
What we do
What we see done
I see spreadsheets with loads of columns, and zero indication of what is required. So there is a mess of columns with endless data, and you only need to fill in 3 columns and see another 2. Honestly, all these helper columns simply doing calculations that are irrelevant, can and should be hidden. Also, you don’t need to work from left to right on the spreadsheet.
What we do
We separate data entry, with relevant calculated data. So, we put the data entry columns to the left with headers in a colour identifying them as such. We then put the relevant formulated columns to the right of that with a different coloured header, so that you know they don’t need to be filled in. All irrelevant calculation columns that don’t need to be seen, are done to the right of that and hidden away. In fact, the parts you see of one of our spreadsheets, is usually only about a third of all the work.
What we see done
So many people don’t even use drop-down lists, and it means that the data is never as accurate as it could be. Imagine miss-spelling a name for example, or using a different name by mistake. I see it so often where someone will type in William, and then Will, and tell me that it is the same person. However, then the spreadsheet reporting will be incorrect, as it sees them as two different people, where as they should have both been the same person.
What we do
We use drop-down lists whenever we can. This ensures that each entry is always spelled the same, so that the correct data is always included in each report. Also, this means that we can create a check for the cell, to ensure that only valid data gets entered. Even if pasted in as a bulk entry, it will still flag up if it isn’t one of the drop-down options. This also creates an atmosphere where people are often more inclined to fill information in if they can simply choose the correct option rather than having to think what might be needed.
Reducing unusable data
What we see done
People tend to have too many free text columns, which means that the data can’t be analysed. One I see regularly, is a ‘why was our quote rejected’ column. The staff then write all sorts of stuff in there, and it can’t be easily analysed. That means that all of that data is now useless unless someone goes through and checks it manually.
What we do
Imagine if you had a column called ‘Reason for rejection’ with 10 or 15 options in a drop-down list, and then accompanying notes as an optional extra. Then you could analyse your data to see why you lose the quotes you do. You can see now that the number one reason is price, and you don’t have to read that Mrs Smith said you were £10 over price unless you specifically want to look in detail.
Avoid hard-coded formulas
What we see done
Hard coded formulas are where values, that could in theory change, are entered directly into formulas. I see this done all the time, and it is really a bad practice. I see it done with VAT or commission for example. Folk will type “*0.20” directly into a formula to multiply for 20% VAT, and then if or when the VAT rate changes, they need to go through an entire spreadsheet finding all the locations with the VAT included, and manually change everything to the new percentage.
What we do
We don’t hardcode anything. So, there will be a settings tab with the VAT percentage in (for example) so that you can change that in one place, in order to change all formulas using that reference. In fact, we often go one step further, to accommodate previous entries being one rate, and then later entries being a different rate, to allow for mid-year rate changes. This means that all such values can be easily changed, without having to open up and edit formulas.
Spreadsheet layouts
What we see done
So often I get sent spreadsheets that are just a mass of numbers. Data entry, formulated cells, helper cells, calculations, and even other columns and cells all in one big grid. In fact, I’ve often seen reports on the same tab as the data entry in one big mess. No one knows what is going on, sometimes not even the person who built it.
What we do
We have a far more logical layout to our spreadsheets, almost like what you’d expect from software. A tab for each function and data set, and everything clearly marked as to what needs to be entered and what is calculated. We separate reports, and have them in layouts suitable for screen viewing or PDF conversion. Everything has its place, and it all makes sense.
Reducing manual data for calculations
What we see done
I often see people having to enter unnecessary data into spreadsheets. For example, if the start and and dates are entered, you don’t need to ask how many days as that can be calculated. But there are more problematic scenarios. For example, if you select an end date for a project and then ask the status of the project when needs to be set to complete. I see data sets where the end date has been entered, but the person forgot to change it to complete. So, all the reporting is out.
What we do
I prefer using calculations over manual entry whenever possible. So, if the status is dependant on other entered data, I would rather formulate that status from the other data entered. That way, it serves two purposes. Firstly, you won’t have mismatched information where the user forgot to change something. Secondly, all the supporting data will need to be filled in to change the status. That means that the status will match the information, and the only way to change it will be to enter all the required information.
Keeping data sets together
What we see done
I usually see people break data into categories, like branch, staff, month, etc. So, they might have a tab for Colin for January, and then another for February, and them more tabs for Mary for each month, etc. They end up with loads of tabs of bits of data. They then want to calculate the total for various staff over a quarter, which means a whole bunch of unique formulas. People often do this because they need the value per person per month, and they don’t know how to calculate it without splitting the data.
What we do
I keep all the data together. One tab, with a column to select the salesperson, as well as the date, to calculate the month. This way, all the data is in one place, you can easily add more salespeople without adding new tabs, and I can easily calculate just about whatever information you wish to analyse from one single, and efficient data source. Filtering our one person’s sales from a date range, and even by product type (if listed) is easier than it would be over multiple data sets.
Limiting data to accommodate formulas
What we see done
So often I see spreadsheets that have ‘outgrown their formulas’. Let me explain. If you create a data set down to row 1000, which have formulas attached, then the formulas stop at row 1000. If you then type data into row 1001, the formulas may not expand with the selection. That means that new data won’t be included in reports. Even when people have used tables, they have entered data below the table, not realising it wasn’t included. The way some overcome this is to include the entire column in formulas, but that makes the spreadsheet way too bloated. Either of these options are dangerous, and so few spreadsheets have a clear indication where the end of the data set should be.
What we do
I limit data sets. If you say you need 1000 rows, I leave 2000, if you need 2000, I leave 5000. The idea is to leave plenty of space, but to limit it for the sake of accurate formulas. Then I draw a border around that data, make a space around that, and then hide all further columns and rows. That way, it is clear where the data set ends, and you can’t add more data outside of that. So, what happens if/when you run out of space? I’ll get to ‘rolling data or set periods’ to explain how I overcome that issue.
Using colour effectively, and only where needed
What we see done
So many spreadsheets are an ‘assault of colour’ and not in a good way. I see spreadsheets with no colour in the headers to differentiate data entry from formulated cells, but each row of data is a bright colour. People colour in the whole row in the brightest colour they can find, to show which jobs have been won and which have been lost (for example). It’s often so overwhelming. I did one spreadsheet once where the user could be triggered into a fit with too much colour, so I had to make the whole spreadsheet in grey shades, that was interesting. Now, this grey spreadsheet may not be the norm, but colour certainly should be used more effectively.
What we do
I use your company corporate colours to determine the data entry columns from the formulated columns, by colouring in the headers. And while I agree in changing colours based on the status or outcome of each row, I only change one cell, and use less intrusive colours. Instead of bright red and green, more pastel colours seems to be easier on the eyes. There are various ways of tackling this, depending on the layout and design, but the idea is to use colour where needed, and to not have it so overwhelming that it takes your attention away from what you need to see. I do use the bright red and yellow on the dashboard, but only when it is alerting you to real issues, and absolutely has to be seen and actioned.
Rolling data or set periods
What we see done
People often create spreadsheets with no life span in mind. So, they simply start collecting data, and the spreadsheet keeps growing, and then eventually explodes after years of collecting data. I often get sent spreadsheets that have become unmanageable, because they have collected years of now unnecessary data.
What we do
I limit most spreadsheets to a 12 month set period, or a 12 month rolling period. If a rolling period, then I make the spreadsheet show you what data you can safely remove or archive, which is no longer required for reporting. Sometimes I do it for longer periods, depending on the demand, or collect some key data as you go for reporting, while removing unnecessary data. There are many ways of doing this, but the idea is to keep the spreadsheet data to a minimum, so that you always have space, and you don’t blow the spreadsheet up. When doing set 12 month periods, you can simply archive old spreadsheets and start again with a new one each year.
Prioritising structure over fancy formulas
What we see done
I often get people asking me how to get them out of a hole with regards to formulas. They struggle to do what they need to do with regards to reporting, but when I look at it, I can see why they are struggling. The data sets are not well laid out, they are split up, and the method of reporting is not efficient. So, they get themselves into a tricky position, which becomes almost impossible to do effective reporting. This means that they need to come up with some near impossible formulas to cope with the demand. This is why they come to me at this point. Sometimes I can help, sometimes a re-build is required.
What we do
I will say this. The structure (layout and design) of the spreadsheet, can make or break it before you even begin to formulate data. This is why I ask questions about what you need, what you might need, planned growth, etc, before building anything. I need to have a clear picture of the whole plan, so that I can make sure that the spreadsheet is as efficient as possible, so that I don’t end up in a dead end. This is arguably the most important aspect to making new spreadsheets, and almost no spreadsheets I get sent even consider this.
Pivot tables
What we see done
Pivot tables are seen as the answer to everything. They are relatively quick and easy to do. Most businesses list pivot tables as a required skill for for new staff in job adverts, but they really are not great. Yes, if you need a quick report with temporary data, then they are useful. However, if you need a robust report to re-use, then they are almost dangerous at times.
What we do
I have never used a pivot table in a spreadsheet sent to a client, and I never plan to do so. Whatever a pivot table can do, the right formulas can do better. And the formula method will be safer, more flexible (in making) and more robust. Let me know what reporting you’d like, and I will use formulas and other features to do what a pivot table can do, only better.
Macros
What we see done
Now, I don’t want a fight here, as there are legitimate uses for macros, and they are absolutely required for certain applications. I am not knocking them. I will say that they are often used instead of other features, purely because people don’t know how to do the same thing using formulas. Often those with a programming background resort to macros over formulas, because they are more comfortable with programming. I often get sent macro-heavy spreadsheets, and get asked to remake them without macros.
What we do
My background is in Excel and not programming, so I don’t use macros unless I absolutely have to, which I try to avoid. Macro-free spreadsheets can be used in Excel Online, as well as other spreadsheet platforms, and they tend to be less glitchy when things change. They also don’t trigger the security alerts that macros often do. I try and stay away from macros for these reasons, and because I’m not that good at them, so it will be very rare that I would use a macro.
Spreadsheet Security
What we see done
If you ask me the most common and dangerous issue I see with spreadsheets I get sent, one things springs to mind. Almost none of them are locked, and almost all have at least one formula over-written. Formulas should calculate what is needed, and should not need to be over-written in certain cases. I see formulas over-written (intentionally or unintentionally) and then the original data is changed, and now the results are incorrect. All spreadsheets should be locked (with the editable cells unlocked). Even if it’s only you using it, because mistakes can happen.
What we do
Every single spreadsheet is locked to protect the integrity of the formulas and layout. Even the ones that only I use. This means that no formulas can be over-written. Any ‘in certain cases, do something else’ scenarios are written into the formula, so that it doesn’t need to be edited or over-written. Does this mean that your new spreadsheets will be unbreakable? No, but I have a website page showing you how to keep your spreadsheets working. This does protect you from intentionally or unintentionally over-writing formulas.
Build to a structure over building to demand
What we see done
People have an immediate need, so that excitedly start building their spreadsheet to solve that need. The issue here, is that they build it to solve one need, and then later realise that they have another need, and the spreadsheet is not suitable to solve them both. I’ve spoken in other sections about the importance of the initial layout of the spreadsheet, and not forward planning could run you into issues later on. So often people go straight from their raw data to their reporting, and in a way that limits the spreadsheet’s capabilities for further development.
What we do
I build spreadsheets in three parts. The first being the data entry, and the third being the resulting reporting. The second part (which you usually don’t even see) is where all the data is collected for reporting. I do this to create a structure as opposed to an immediate result. This means that I’m not just solving the issues at hand, but actually creating a way of solving issues that may occur in the future. Now, you can’t always build onto a spreadsheet if the demands change too drastically, but I want to give us the best chance of being able to do so, which is why I put more thought and effort into the structure of the spreadsheet.
Dynamic reporting over fixed reporting
What we see done
Imagine you have 12 salespeople over 4 different branches. The it comes to reporting. Now, you want to know what each salesperson did per month, as well as what each branch did per month, as well as the totals. This means at least 17 graphs. And don’t say that no one would do this, because I’ve seen it way too often. This results in a massively complex looking report, all for essentially the same graph, just representing different data.
What we do
I use dynamic reporting. Take the same scenario, but this time I only make one graph. I then let you filter what information fuels that graph. This way, you could possibly even combine branches or salespeople (depending on numbers) to see almost any report you wish. You could also combine filters. So, if a salesperson sells at two branches, you can view their sales from only one specific branch. This means that you can have a far more compact report, but with almost any variation possible. This dynamic reporting is a game-changer.
There you have it, how our spreadsheets differ from those you usually see in action. If these sound like the kind of features you would like your spreadsheets to have, please get in touch so that we can discuss what you need.