Problem: Excel Formulas Not Updating Automatically
We run complex models, like really complex. It's not unusual for us to run a model that references back to 20+ other Excel files. I put together a pretty simple spreadsheet where a single cell will reference one other file, very simple. When I closed out of my work so my boss could review, he told me the model did not update. The cell references were all correct, but for whatever reason they would not update.
Solution: What to Check and Where to Get Help
The first thing to do is to run a series of preliminary checks, including:
- Verify cell references are correct
- Confirm that "Formulas -> Calculation Options -> Automatic" is checked
- Click Calculate Now
- Click Calculate Sheet
- Alt+E+K to refresh links
- F9 to recalculate formulas
- Verify that your links reference the absolute path of the linked spreadsheet
- Check if sheet updates on a coworker's computer -> If it doesn't, it could be a server issue and you'll likely need to get your IT team involved
If these tips don't help, it could be an issue with using different versions of Excel.
What to do When You Change Excel Versions?
Hopefully your IT team has come up with a plan to update spreadsheets when they upgrade versions of MS Office. If not, they should be prepared to handle a lot of help desk calls. Here are a few tips from MSFT Office on using excel with earlier versions:
- Open, edit and save file in Compatibility Mode
- Convert files using a file converter download
In OP's case, it was an issue between Excel 03 and 07. The sheet was created in Excel 03, but when it was opened in Excel 07 a "Security Warning" disabled the automatic update of links. To fix this issue he had to click on the Security Warning and enable the links to update automatically.
Excel Help Forum
If these steps don't work, there are countless Excel help forums you can reach out to for assistance. With more than 500,000 Wall Street-minded community members, when you post to WSO you're likely to find someone who has encountered a similar issue. There are other help forums out there, such as Reddit, Excel Forum, Mr. Excel or Chandoo Forums - but these are not specific to finance.
The Wall Street Prep Financial Modeling Course is also a great way to strengthen those Excel skills.