When you saved an Excel file with its formulas broken

1. If it is still open — Undo

Even after saving, Ctrl+Z (⌘Z on a Mac) works as long as you have not closed the workbook. Undo until the formulas are back, then use Save As. If you closed it, the undo history is gone.

2. First find where it broke

• Show Formulas: press Ctrl+` (the key to the left of 1; ⌃` on a Mac) and every cell shows its formula instead of its value. A cell that should hold a formula but shows only a number is one that "hardened into a value".
• Go To Special: F5 (⌃G on a Mac) › Special › Constants selects every cell that holds only a value. If a selection appears in the middle of a formula column, that is where a paste overwrote it.
• Error Checking: Formulas › Error Checking walks you through the #REF! and #VALUE! cells in turn. #REF! means the row or column it referred to was deleted, so fixing the formula does not bring back the cell it used to point at.

3. Excel’s AutoRecover

4. Where an old version might be

• Previous Versions on Windows: right-click the file › Properties › Previous Versions. Only there if File History was turned on.
• Time Machine on a Mac: if it was turned on, Browse Time Machine Backups from the folder.
• OneDrive, Google Drive, Dropbox: the file’s Version history (Manage versions on Google).
• Sent mail: if you sent the budget last week, the formulas in that attachment are intact.

5. Merging the old formulas with today’s data

Once you have the old version, open the two side by side, copy the formula columns from the old file and put them into the same place in the current file with Paste Special › Formulas. The other way round (today’s data into the old file) can break references to other sheets. After pasting, go over it once more with Show Formulas.

How not to be here next time

If none of the above brought it back, that version is gone. A file keeps only its latest state, which is why people make one more copy before they press Save. That copy is the "budget_final.xlsx" and "budget_final_v2.xlsx" in your folder.

Octorium is a desktop app for Mac and Windows that does that job for you. Hand it the one folder you work in, save the way you always have, and each time the folder goes quiet for a moment (2 minutes by default) it records that moment. The entries are grouped under Today, Yesterday and the days of the week, each with its time, and from any line you can return the whole folder with Go back to this moment or a single file with Go back for this file only. Going back is recorded too, so going back can itself be undone.

• Nothing appears in your folder. The history is kept separately.
• Your files never leave this computer. Recording and going back both happen on this computer, and they work without the internet.
• 30 days free, no card, counted from the moment you press start in the app.

Install it from /en/download; going back is explained in the guide (/en/guide#restore).

For Excel there is one more thing. Octorium’s description says the data changed and the formulas changed in different sentences — "Changed the formulas in the Budget sheet", for example. So instead of opening saves one by one to find when the formulas broke, you use Go back for this file only to the save just before the one with that sentence.