Operations Spreadsheets That Survive Growth
By CodexierPublished 5 min read
Spreadsheets run a surprising share of Swedish small businesses: order lists, staff schedules, supplier price lists, project trackers. They are quick to start and everyone knows them. They also decay fast when several people use them without rules. This guide sets out five habits that keep an operations spreadsheet reliable as the team grows, and the signs that it is time to move to a real system.
One table per purpose
Most broken spreadsheets are broken in structure, not in formulas. Data is laid out like a printed report, with merged cells, subtotals in the middle and blank rows for spacing. Several things are tracked on one tab, say orders and customers, with customer details retyped on every order. The fix is to think in tables: one tab for customers, one for orders, one for products, each with a header row and one row per item. Link them with an ID instead of copying details, and build reports on separate tabs that read from the tables.
| Rule | Why |
|---|---|
| One header row, no merged cells | Sorting, filtering and import work |
| One fact per column | A column with both date and comment cannot be filtered |
| A unique ID per row | Other tabs can refer to the row safely |
| No blank rows or subtotals in the data | Formulas and pivot tables do not miss rows |
| Dates, amounts and numbers stored as values | Text that looks like a number breaks totals |
Validation and dropdowns
Free text is where errors enter. If the status column can say Done, done, Klar or finished, no filter or count will be right. Put the allowed values on a separate lists tab and use data validation to offer them as a dropdown. Do the same for owners, categories and locations. Add validation to formats as well: dates must be dates, amounts must be numbers, organisation numbers must have the right length.
- Status, owner, category: dropdown from a lists tab.
- Dates: date validation, with a sensible range.
- Amounts and quantities: number validation, no negative values where they make no sense.
- Required columns: conditional formatting that highlights empty cells in red.
Protecting formulas
A formula overwritten by a typed value is the most common silent error in shared sheets. Keep input columns and calculated columns clearly apart, colour input cells lightly so people know where to type, and protect the calculated ranges and the report tabs so only the owner can edit them. Use whole-column or table references so formulas include new rows automatically, and avoid formulas that refer to other files, which break when files move.
Change logs and versions
Store the file in one shared place, SharePoint, OneDrive or Google Drive, and never email copies. Both Microsoft 365 and Google Sheets keep version history, so you can see who changed what and restore an earlier state. Add a small change log tab for structural changes: new columns, changed formulas, renamed statuses, with date, person and reason. It takes a minute and saves an afternoon of detective work later. Write the purpose of the file and the owner's name at the top of the first tab.
Owner
One named person decides on structure, formulas and access, with a deputy for holidays.
Access
Edit for those who enter data, view for everyone else. No anyone-with-the-link sharing for files with personal data.
Routine
A monthly check for duplicates, empty required fields and rows stuck in a status.
Signs it is time for a system
- Several people edit the same rows daily and work gets lost or duplicated.
- Customers, suppliers or money depend on the file being right in real time.
- You retype data from the sheet into Fortnox, a CRM or a webshop.
- You need different people to see different rows, for example for GDPR reasons.
- The file has become slow or needs a specialist to change.
Our guide on when your business has outgrown Excel covers the options when you see these signs. When you do not need us: the habits above are free and a capable colleague can apply them in a day. When the clean-up itself is the job, such as restructuring years of messy data before a move, our data entry and back-office service does the work. See the pricing page or book a call.
Frequently asked questions
Excel or Google Sheets for operations?
Either works with the habits above. Choose the one your company already uses for email and files, so access and sign-in are handled in one place. Both support validation, protection and version history.
Should we use Excel tables or plain ranges?
Tables, where available. An Excel table grows automatically with new rows, keeps formatting and lets formulas refer to column names, which removes a whole class of errors with ranges that miss new data.
Is it acceptable to keep customer data in a spreadsheet under GDPR?
Yes, if access is limited to people who need it, the data is kept only as long as necessary and you can find and delete a person's data on request. Shared links open to anyone are the usual weak point.
How do we stop people creating their own copies?
Make the shared file the easiest place to work: fast, clear, with a filter view per person. Then agree that copies are for analysis only and never the source of truth.
Get your key spreadsheet into shape
Book a short call and show us the spreadsheet your operations depend on. We suggest the structure changes, or tell you it is time for a system.
Book a free 15-minute call