Clear Contents vs Delete in Excel: What Each One Removes

Clear contents vs delete comes down to one question: should the cells stay or go? Clear Contents empties cells and leaves them in place, so the layout doesn't move. Delete removes the cells, and everything below or to the right shifts in. This guide is for beginners who have pressed Delete and been surprised, and for anyone who has seen #REF! appear after tidying a sheet.

Clear Contents vs Delete at a glance#

The confusing part is the name: the Delete key on your keyboard does Clear Contents, not Delete.

What Clear Contents does#

Clear Contents removes what is typed in the cells, whether a value or a formula, and nothing else. To use it:

1. Select the cells.
2. Press the Delete key (on a Mac, delete). You can also right-click and choose Clear Contents, or go to Home > Clear > Clear Contents.

The cells stay where they are. Their formatting stays too, so a cell formatted as a date will show the next number you type as a date. That's why the guide to clearing formatting recommends Clear All when you want a truly empty cell.

The Clear menu has several options:

• Clear All removes contents, formatting and comments or notes.
• Clear Formats removes formatting only.
• Clear Contents removes values and formulas only.
• Clear Comments and Notes removes only those.

Formulas that refer to a cleared cell keep working. They treat it as empty: =B3*2 returns 0, and AVERAGE skips it. The guide to editing cells and undo covers the Delete key along with F2, Esc and Ctrl+Z.

What Delete does#

Delete removes cells from the grid, and Excel closes the gap by moving other cells. To use it:

1. Select the cells, or whole rows or columns.
2. Press Ctrl+- (Mac: ⌘-), or right-click and choose Delete, or go to Home > Delete.
3. If you selected cells rather than whole rows, Excel asks how to close the gap: Shift cells left, Shift cells up, Entire row or Entire column.

If you select whole rows first (click the row numbers), Excel deletes them straight away. The guide to inserting and deleting rows and columns covers every shortcut.

When cells move, Excel updates formulas to follow the data. =SUM(B2:B8) becomes =SUM(B2:B6) if you delete two rows inside it. But a formula that pointed directly at a removed cell has nothing to follow, so it shows #REF!. The guide to the #REF! error explains how to fix it.

The same sheet, cleared vs deleted#

Here is a small list of test scores, with three summary formulas underneath:

At the start, the average is 77.14 (540 ÷ 7), the count is 7, and =B3 shows Ben's 80.

Option 1: Clear Contents on B3 and B6#

Select B3, hold Ctrl (Mac: ⌘), click B6, and press Delete. Ben and Eli keep their rows, but their scores are gone.

• =AVERAGE(B2:B8) returns 77 (385 ÷ 5). AVERAGE ignores empty cells.
• =COUNT(B2:B8) returns 5.
• =B3 returns 0, because B3 is now empty.
• =COUNTBLANK(B2:B8) returns 2: the two cleared cells.

Option 2: Delete rows 3 and 6#

Select row 3, hold Ctrl (Mac: ⌘), click row 6, and press Ctrl+- (Mac: ⌘-). Ben and Eli are removed completely, and the rows below move up, including the three summary rows (now rows 8 to 10).

• The average formula becomes =AVERAGE(B2:B6) and still returns 77.
• The count formula becomes =COUNT(B2:B6) and returns 5.
• Ben's score formula becomes =#REF!, because the cell it pointed at no longer exists.

Same averages, very different sheets. Clearing kept the names and left gaps. Deleting removed the people and broke the one formula that pointed straight at a removed cell.

When to use Clear Contents#

Use Clear Contents when the structure should stay and only the data should go:

• Reusing a template. Clear last month's numbers and keep the headers, formatting and formulas around them.
• Removing a wrong value. A typo in one cell doesn't mean the row should disappear.
• Keeping other formulas safe. Nothing moves, so references elsewhere still point at the right cells.
• Leaving room for later. A blank cell can be filled in when the data arrives.

To clear typed values but keep formulas, select the range, press F5 (or Ctrl+G; Mac: Control+G), click Special, choose Constants and press Delete.

When to use Delete#

Use Delete when the cells themselves shouldn't be there:

• Removing records. A cancelled order or a duplicate customer should leave the list, not leave an empty row.
• Removing blank rows that break sorting, filters and pivot tables. The guide on deleting blank rows safely shows a filter-based method.
• Removing a column you no longer need, such as an old helper column.
• Tidying a list for a pivot table or for sharing, where gaps would look like missing data.

Before you delete, check whether any formula points straight at those cells. If one does, it will show #REF!.

Common mistakes#

These slips cause most of the surprises:

1. Expecting the Delete key to remove rows. It only clears them. The empty row stays and can break a sort or a pivot table.
2. Deleting cells instead of whole rows. Choosing Shift cells up on one column moves that column's data out of line with the others, so names and scores no longer match. When in doubt, choose Entire row.
3. Deleting cells that formulas use directly. The result is #REF!. Undo straight away with Ctrl+Z (Mac: ⌘Z).
4. Clearing contents and leaving the format. A cleared cell may still be formatted as currency or a date, so new entries look wrong. Use Clear All for a fresh cell.
5. Clearing formulas by accident. The Delete key removes formulas as well as values. Use Go To Special > Constants if you only want to clear typed numbers.

Our recommendation#

Use Clear Contents (the Delete key) as your everyday way to remove data, because it never moves anything and never creates #REF! errors. Use Delete (Ctrl+- or ⌘-) on purpose, when a row, column or cell genuinely shouldn't exist, and select whole rows or columns so your data stays lined up.

In short: clear to empty, delete to remove. And if either one goes wrong, Ctrl+Z (⌘Z) brings it back.

Practise it#

Try the COUNTBLANK exercise on this page to see how cleared cells stay in the range. Then practise editing, clearing and undoing in a live spreadsheet: you'll clear cells, fix mistakes and undo them, with each step checked instantly. If you're new to the grid, start with the cells, rows and columns lesson.