Excel spreadsheet design fails for one reason more than any other: the workbook asks every user to think like the person who built it. Formulas sit in the open, inputs and calculations share the same cells and one wrong paste breaks the file. Good design separates what people type from what the workbook works out, then hides the rest. This guide covers the rules that hold and the point where better design stops paying.
Table of Contents
Why do spreadsheets fail in business?
Spreadsheets fail because the design asks every user to behave like the builder. The cause is almost never the formulas. It is that inputs, calculations and outputs share the same cells, so ordinary daily use damages the file. Add a record-keeping obligation on top and a fragile workbook stops being an inconvenience.
Most advice about failing spreadsheets is written by companies selling the replacement, which is why it always arrives at the same conclusion. A well built spreadsheet is often the correct answer. The useful question is whether this one was designed or whether it accumulated.
There is a second failure most design guides skip. If a workbook holds quotes, invoices or anything else supporting a tax position, it is a business record. The ATO requires that the relevant information in your records must not be changed and must be stored in a way that protects the information from being changed or the record from being damaged. That is an anti-tampering rule rather than a ban on editing a working file, but our view is that a workbook where anyone can type over a calculated cell sits awkwardly against it, and the gap only surfaces when someone asks for the file.
Where a spreadsheet should sit against the rest of your systems is a wider question, covered in our guide to Excel for business.
What are the rules of good Excel spreadsheet design?
Good Excel spreadsheet design splits a workbook into three layers: the data people type, the calculations the workbook performs and the outputs people read. Keep each on its own sheet. Name the ranges so formulas read in plain English. Build the data layer first and the interface last. Nearly every design problem traces back to those layers being mixed.
In the workbooks we are asked to rescue, the layer boundary has almost always been crossed in the same place. Someone needed a rate visible beside the calculation that uses it, so they typed it there, and from that point the file had no safe cells. The table below sets out what belongs in each layer and what stops working when the boundary is crossed.
| Layer | What belongs here | What breaks when the boundary is crossed |
|---|---|---|
| Data | Raw entries, rates and reference lists. One row per record, one column per field, no merged cells and no blank spacer rows | Merged cells and spacer rows break Excel tables and make sorting and lookups behave unpredictably rather than fail visibly |
| Calculations | Every formula, lookup and intermediate step, on sheets the user never opens | Formulas sitting beside inputs get overwritten by ordinary typing and pasting |
| Output | What the reader sees. The quote, the summary, the report | Reformatting an output to suit a reader can change a calculation without anyone noticing |
This is the build-time form of the layer model in our Excel data analysis guide, which separates raw data, transformation, analysis and output for analytical work. For a tool people use daily, three layers is enough, because the person typing needs to know only two things: where they type and where they read.
The order you build in matters as much as the structure, because building the interface first locks in decisions the data cannot support. This is a build order for designing one, not a rescue plan for a workbook that has already gone wrong.
- Decide what the person using it will type and what they will only read. That split is the design.
- Lay the data out flat, one row per record, before a single formula exists.
- Convert every range to a named Excel table so formulas reference names rather than cell addresses.
- Put calculations on their own sheets, working only from the named tables.
- Build the output last, referencing calculations only and never raw data.
- Lock every sheet, then clear the locked property on only the cells a person is meant to fill.
- Test the design by watching someone who did not build it use it once, without helping.
Step seven is the one people skip, and it is the only step that tells you whether the design works. Named tables also keep lookup formulas readable, which matters more than most people expect once a workbook holds a few hundred of them. Our guide to VLOOKUP and its replacements covers the formula side.
How do you make a spreadsheet non-experts will actually use?
Non-experts use a spreadsheet when it asks them to make choices rather than write formulas. Replace free typing with drop-down lists, replace manual filtering with slicers and put a short instruction at the top of every sheet a person types into. If someone must understand the calculation to use the tool, the design has failed.
Three patterns do most of the work. Drop-down lists take away the decision about how to phrase an entry, which in our experience is where most bad data starts. Slicers on a pivot table give people a set of buttons instead of a filter menu, so filtering never touches a formula. A short instruction block at the top of each input sheet, written in the language of the job rather than the language of Excel, answers the question people are too polite to ask.
Australian small businesses also get less outside help than their neighbours. CPA Australia’s 2025-26 small business survey of more than 4,100 businesses found 17 per cent of Australian small businesses sought advice from IT consultants or specialists, against a 27 per cent average across the Asia-Pacific. Our read is that more of these tools are therefore being built without help, which makes design discipline the only thing standing between a team and a broken file.
Why won’t your team use the tool you built?
The most common reason a team ignores a workbook built for them is not that it is difficult. It is that using it correctly requires knowing which cells are safe, and nobody wants to be the person who broke the quoting tool. So people quietly keep doing it the old way, or they take a copy. In our experience removing that risk does more for adoption than any amount of training, because the barrier was never skill. It was exposure.
How do you stop people breaking your spreadsheet?
Lock every part of the workbook that is not an input. Protect the calculation and output sheets, leave the input cells editable and use data validation so only valid entries land in the first place. Protection is not about trust. It removes the possibility of a mistake nobody notices until a number is already out the door.
Protection and validation solve different halves of the same problem. Protection stops people changing what they should not touch. Validation stops the wrong value entering a cell they are supposed to type in. A workbook with one and not the other is still open at one end.
The sequence catches people out. Every cell in Excel is locked by default, but locking does nothing until the sheet itself is protected. So clear the locked property on the input cells first, then protect the sheet. The most common protection mistake we see is a sheet protected with every cell still locked, so the person who has to use it cannot type anything, and someone turns protection off entirely within a week.
Validation rewards going deeper than most people do, particularly around which rules survive contact with a busy team. Our guide to data validation in Excel covers that in detail.
This is also where the record-keeping obligation lands. If the workbook is a business record, our view is that the ATO rule that records must be stored in a way that protects the information in them from being changed is not met by asking people to be careful with it.
What this looks like on a real build
A Melbourne professional services firm came to us with a fee estimating workbook its own staff had stopped trusting. Rates and hours were typed directly over the formulas that used them, and the file had grown slow and fragile. The rebuild changed the structure rather than the maths, and estimating went from about 45 minutes to roughly 10.
The original had grown the way most of them do. It began as a single sheet with rates at the top and a total at the bottom, and every new service line was added by copying a column. By the time we saw it the rate table and the calculations occupied the same cells, so updating a rate meant typing over a formula. Staff had noticed and had started keeping personal copies rather than risk the shared file.
We did not rewrite the pricing logic. It was correct. What changed was the structure. Rates and service lines moved to a data sheet as named tables. Every calculation moved to sheets the estimator never opens. The estimating screen became drop-downs and a summary, with every cell locked except the ones a person is meant to fill.
The measurable outcome was time. A fee estimate that had taken about 45 minutes, most of it spent checking that nothing had broken, now takes roughly 10. The change that mattered more is harder to count. The personal copies stopped, because there was no longer a reason to keep one. Rebuilding workbooks like this one is the substance of our spreadsheet solutions work.
When does better spreadsheet design stop being the answer?
Better spreadsheet design stops paying when the design controls are exhausted. You have protected the sheets, added validation and written the instructions, and people still work around it. At that point the workbook is not badly designed. It is being asked to do a job a workbook cannot do, and more design effort buys nothing.
Whether your business as a whole has outgrown Excel is a separate question, and our guide to when a business outgrows Excel answers it. This section is narrower. It is about the point where one particular workbook has outgrown its own design, which you can observe without knowing anything about the rest of the business. The table sets out what each trigger means and why more design will not clear it.
| What you observe | What it means | Why more design will not fix it |
|---|---|---|
| Every sheet is protected and people still find ways around it | The design controls are exhausted | There is nothing left to lock. The next control has to sit outside the file |
| The instruction sheet is longer than the input sheet | The design is asking more of the user than it can explain | Instructions are a symptom. A design that needs a manual has already failed the test of being handed to someone who did not build it |
| Two people need different views of the same screen at once | An interface can only be one thing at a time | Layout can serve one job well. It cannot serve two opposed ones |
| Co-authoring stops working and nobody can say why | Microsoft lists unsupported objects, including charts, SmartArt graphics, OLE objects and ink, among the things that can block co-authoring | The objects that make a workbook readable are often the ones blocking it. In our experience the quickest confirmation is to open a copy with them removed |
When a workbook reaches one of these, the next control sits outside the file: a proper app for the process, a database for the records or a reporting model for the numbers. Which one depends on what the workbook is actually doing, and both the three upgrade paths from Excel to Power Apps and our comparison of Access and Excel work through that choice.
Frequently asked questions
Why is my spreadsheet slow when it only has 20,000 rows?
Because the cause is almost never size. Microsoft’s published Excel limits put the worksheet ceiling above a million rows, so 20,000 is nowhere near it. Slowness at that scale comes from structure: volatile functions recalculating on every keystroke, lookups reaching across sheets, whole-column references and conditional formatting applied to entire columns. Fixing the structure usually restores the speed without removing a single row.
Why did co-authoring stop working on our shared workbook?
Multiple people can edit the same workbook at once, which Microsoft’s co-authoring guidance says needs xlsx, xlsm or xlsb format on OneDrive or SharePoint Online. When that stops working, it is usually because something was added that co-authoring does not support. Microsoft’s troubleshooting guidance lists OLE objects, SmartArt graphics, charts and ink objects among the things that can block it. Those objects are what make a workbook readable, so the fix is a trade-off between how the file looks and how many people can be in it.
Should the person who uses a spreadsheet be the one who builds it?
Often not, and this is where most internal tools go wrong. Whoever builds it knows which cells are safe, so they never design for someone who does not. The better arrangement is that the person who uses it defines what they need to type and what they need to read, and someone else builds to that split. Where the builder and the user are the same person, test the result on a third.
What happens when the person who built the spreadsheet leaves?
Usually nothing, until something needs to change. The workbook keeps running, then a rate changes or a new line is added and nobody can make the edit safely. In our experience it is the least discussed spreadsheet risk and often the most expensive. Naming ranges, separating calculations onto their own sheets and writing a short instruction sheet all reduce it, but a workbook only one person understands is a structural risk rather than a documentation gap.
Is a business spreadsheet an audit or compliance risk?
It can be. If a workbook supports a tax position it is a business record, and the ATO requires most records to be kept for five years and requires the relevant information in them to be stored in a way that protects it from being changed or the record from being damaged. Our view is that a shared workbook where any user can type over a calculated cell is difficult to reconcile with that, which argues for protecting sheets rather than relying on care.
Do you need macros or VBA to make a spreadsheet easy to use?
Rarely, and reaching for one early is usually a symptom that the structure is wrong rather than a solution to it. Tables, named ranges, data validation, drop-downs, slicers and dynamic array functions cover most of what people reach for macros to do, and none of them break when the file moves or when someone opens it in a browser. If a design needs code to be usable, the design is worth revisiting first.
What to do with the workbook you have
A spreadsheet people want to use is not a prettier one. It is one where the structure makes the wrong action difficult and the right action obvious, and where nobody has to know how it works to use it. Most workbooks have not hit a technical limit. They have a design that was never decided, only accumulated.
Spreadsheets sit alongside dashboards and data pipelines in most businesses, and getting the boundary right between them is the substance of our reporting and analytics work.
If one workbook in your business causes more arguments than the rest, book a spreadsheet review and we will tell you whether its design can still be fixed.
