Excel guide · 4 min read
How to clear contents in Excel without deleting formulas
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Category | Budget | Week 1 | Week 2 | Week 3 | Week 4 | Total | Left | Avg per week |
| 2 | Travel | 1200 | 310 | 0 | 455 | 120 | 885 | 315 | 221.25 |
| 3 | Meals | 400 | 62.5 | 88 | 41 | 97.2 | 288.7 | 111.3 | 72.175 |
| 4 | Software | 650 | 649 | 649 | 1 | 649 | |||
| 5 | Office supplies | 150 | 23.99 | 12.5 | 38 | 74.49 | 75.51 | 24.83 | |
| 6 | Training | 500 | 450 | 450 | 50 | 450 | |||
| 7 | Phone | 90 | 0 | 90 | |||||
| 8 | Total | 2990 | 1045.49 | 550.5 | 496 | 255.2 | 2347.19 | 642.81 |
Download this sheet as an Excel practice file (.xlsx)
Go To Special > Constants
This weekly expense template has categories in A, budgets in B, typed amounts for weeks 1 to 4 in C:F, and formulas in G (total), H (budget left) and I (average per week), plus a totals row 8. Each month you want the weekly amounts gone and everything else kept.
Constants are cells you typed: numbers, text, TRUE/FALSE and error values. Go To Special selects them and skips every formula cell. Tick Numbers only, so the category names and headers (text) stay. A typed 0, like Travel's week 2, is a number too and gets cleared with the rest.
- Select C2:F7, the weekly input area only.
- Go to Home > Find & Select > Go To Special (or press F5 and click Special).
- Choose Constants, untick Text, Logicals and Errors so only Numbers is ticked, and click OK.
- Press Delete.
Select the input area first, or you lose the budgets
If only one cell is selected when you open Go To Special, Excel searches the whole sheet. Here that would also select the budget column, because budgets are typed numbers too, and so would years typed as headers (2026) or typed dates, which Excel stores as numbers. Select just the area you want to wipe first; Go To Special then searches only inside the selection.
If you reset the same template every month, name the input range once: select C2:F7, type Inputs in the Name Box left of the formula bar and press Enter. Next month, pick Inputs from the Name Box drop-down and run Go To Special on that selection.
Result: 13
13: the typed amounts in the input area now. Run it again after the wipe; it should be 0.
Delete vs Clear All vs Backspace
Delete is the same as Home > Clear > Clear Contents: it removes values and keeps formatting, number formats, data validation drop-downs and notes. Clear All also strips formats, so your currency format and borders go too. Clear Formats does the opposite, keeping the values.
Backspace on a multi-cell selection clears only the active cell and leaves you editing it, so the rest of the selection keeps its numbers. Use Delete. To strip colour from cells you don't need any more, see the clear formatting shortcut and remove conditional formatting.
Check the formulas survived, and fix the #DIV/0! ones
After clearing, every total should fall to 0 rather than turning into an error or disappearing. Row 7 (Phone) has no amounts yet, so it shows what every row looks like after the wipe: its total is =SUM(C7:F7), and SUM over empty cells is 0.
Formulas that divide by a count of the inputs break when the inputs go. An average per week written as =G7/COUNT(C7:F7) divides by 0. Column I avoids it with =IF(COUNT(C2:F2)=0,"",G2/COUNT(C2:F2)), which shows a blank until a number is entered. Fix the #DIV/0! error covers the other ways.
To see which cells are formulas before or after, press Ctrl+` (the show formulas shortcut), or use Go To Special > Formulas and give them a fill colour.
Result: #DIV/0!
#DIV/0!: the Phone row has no amounts, so the count is 0. The IF version in column I shows a blank instead.
Does an empty row really total to zero?
Yes. SUM ignores empty cells, so the total of an empty input row is 0, not an error.
Result: 0
0: the Phone row's total with no amounts entered.
Protect the formula cells so a wipe can't break them
Go To Special keeps formulas safe when you use it carefully. Protection keeps them safe from everyone else, including whoever selects the whole sheet and presses Delete. Every cell starts out Locked, but Locked only does something once the sheet is protected.
After this, Excel refuses to change locked cells, so the formulas, budgets and labels can't be cleared or overtyped, and Tab moves between the input cells. Lock cells with formulas has the full walkthrough. If the template is an Excel table, select a column's data by clicking in it and pressing Ctrl+Space, then Delete: calculated columns keep their formula.
- Select the input cells C2:F7.
- Press Ctrl+1, open the Protection tab, untick Locked and click OK.
- Go to Review > Protect Sheet, add a password if you want one, and click OK.
Common mistakes
Running Go To Special with one cell selected
Select the input area first. With one cell selected, Excel searches the whole sheet and picks up budgets, years and dates too.
Leaving Text ticked under Constants
Untick Text, or your labels and headers are cleared along with the numbers.
Using Clear All
Use Delete (Clear Contents) so formats and drop-downs stay.
Pressing Backspace on a selection
Backspace clears only the active cell. Press Delete to clear every selected cell.
FAQ
How do I delete numbers but keep formulas in Excel?
Select the range, open Home > Find & Select > Go To Special, choose Constants with only Numbers ticked, click OK and press Delete. Formula cells aren't selected, so they stay.
What is the shortcut for Go To Special?
Press F5 or Ctrl+G to open Go To, then click Special. Go To Special has no single built-in shortcut of its own.
Does Clear Contents remove formatting?
No. Clear Contents, the same as pressing Delete, removes values and formulas but keeps formats, number formats and data validation. Clear All removes formatting as well.
Why did my totals show #DIV/0! after clearing?
Formulas that divide by a count or a total of the inputs now divide by zero. Wrap them in IF, for example =IF(COUNT(range)=0,"",total/COUNT(range)).
Functions used
Related guides
- How to lock cells with formulas in Excel
- Excel showing the formula instead of the result (formula shows as text)
- How to remove conditional formatting in Excel
- #DIV/0! error in Excel: why it happens and how to fix it
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate a project budget burn rate in Excel
Updated