What are the main budgeting mistakes in Excel?
1. Parallel data entry and analytics on the same worksheet – this leads to massive spreadsheets with “400” columns where you can barely enter anything without fear of breaking formulas, not to mention the risks of adding rows/columns.
2. Using old volatile functions such as OFFSET / INDIRECT for reporting purposes – if your Excel takes a few seconds to respond with every click, you’re a prime candidate for this mistake.
3. Creating “helper” worksheets for intermediate calculations – that’s why Excel is 10, 20, and we’ve seen budgeting files as large as 85 mb. The bigger the Excel file, the slower everything gets: opening, saving, entering data, calculations. Ideally, an Excel file should not exceed 10 mb.
Here are some numbers from our latest project for a valued client:
Previous state:
- Excel file size 25 mb
- File opening time 45 sec
- 49 worksheets, of which 21 were “helper” sheets
- Every click (filtering, sorting, grouping, input) – 3 to 4 sec response time
- Adding a new budgeting line – 3 hours of formula updates and checks
- Preparing and updating for a new month – 4 hours of updates
- ~ 800,000 cells with formulas
Excel was a nightmare to use, so we completely rebuilt the budgeting process. Every part of the old system remained at least equally functional, and a large part of reporting was significantly improved.
New state:
- Excel file size 6 mb
- File opening time 5 sec
- 24 worksheets, only 4 “helper” sheets
- No response delay in Excel
- Adding a new budgeting line – simple insert row (a few minutes if product descriptions need to be added)
- Preparing a new month/year – 15 to 30 min including all checks
- ~ 150,000 cells with formulas – still too high in our view, due to numerous analytical worksheets that will be migrated to Power BI
- Use of PowerPivot Tables and Charts
How did we achieve this?
- Shifted all raw data processing and preparation to Power Query – saving at least 8 hours per month for updating actuals and preparing the file for a new month.
- Clearly separated data entry from analytics – fewer input errors and easier maintenance.
- Implemented PowerPivot data models for analytics – providing flexibility in reporting and structural stability.
- For static analytical views (fixed structures required by senior management), we replaced old Excel formulas (OFFSET / INDIRECT / INDEX, etc.) with modern Office 365 array functions. The most commonly used were:
- BYROW / BYCOL / FILTER – speeding up calculations, since unlike old cell-by-cell functions, modern array functions can return an entire range of results in one call – hundreds of times faster than traditional formulas.
- LAMBDA – uses the arrow function concept (like each in PowerQuery) to optimize calculation speed.
- LET – modern Excel variables that speed up formulas and make them easier to read.
What are the next steps?
- Migrating all analytics to Power BI (Excel will keep only operational analytics) – goal: reduce Excel file size to under 3 mb.
- Why Power BI? Because it solves Excel’s main analytics issues – report sharing and preventing users from “tampering” with formulas/views.
- Why keep operational input in Excel? Because budgeting requires flexibility that is very hard to achieve through standardized budgeting tools.
In Excel, the golden rule is less is more. So, if someone boasts about having a million formulas in their file and 100 worksheets, think twice before hiring them.
