Five reasons (and solutions for) why your spreadsheet is not working

Simon Selkrig, Strategize Financial Modelling, simon@strategizefm.com, Montreal, QC

1. Perhaps the ‘Workbook Calculation’ parameter is set to Manual instead of Automatic, which would cause formulas and other functionality in Excel not to update whilst you work with your spreadsheet.
   
2. Are you unable to edit, let alone select certain cells? Perhaps someone else who also uses the spreadsheet has protected the worksheet and you need to get the password from them.
   
3. There are reference errors littered across your spreadsheet. This can be a major headache and can jeopardise the financial model’s overall existence.  As I mentioned in my article, 10 ways to improve your financial spreadsheets, try to restrict all external cell referencing to only a few import worksheets.
4. Information is sourced from static PivotTables. This stimulates a lot of discussion; given PivotTables can be a great resource to undertake analysis. However, as I discussed in The Dangers of PivotTables, array formulae and other non-seamless Excel feature, most users don’t realise that a PivotTable cannot update seamlessly with changes in data inputs – the user must always manually refresh the PivotTable. An advanced solution would be to write VBA code to automate this however that is beyond the scope of most Excel users.
5. Some complex formulae need to be realised via an array formulae, which is a static calculation that requires manually updating if financial inputs change. Most users are unaware of this. An array formula is created by pressing Ctrl+Shift+Enter to turbocharge the formulas in a spreadsheet to great effect.