Clear or Delete? A hassle-free way to clear everything in a cell

Do you have a habit of right-click a cell and Delete it rather than pressing the “Delete” button on keyboard?


If you do have this habit, I guess you possibly are frustrated by the following experience before:

Excel Tips - Clear or Delete 2

Basically, pressing “Delete” key simply means “Clear Contents”.  Comments, Data Validation rule, Formats remain in the cell.  This may be undesirable.

Nevertheless, if you opt for right-clicking the cell –> Delete…

You are actually “Deleting” the cell from the spreadsheet.  That’s why you could remove everything instantly by this action.

However, Excel would not allow “Hole” in your spreadsheet.  Excel will fill the “Hole” by shifting the cells next (on the right and from below) to it.  That’s why you will be prompted the following:


So what’s wrong with deleting a cell?  It leads to two potential problems:

1. Mesh up the structure of your surrounding tables

Excel Tips - Clear or Delete 4

ooops… per your request, all cells underneath go up.  So ugly now.  Remind you that sometime you do not notice the impact right away if you are working on a large spreadsheet.  The table may be sitting well below that is out of the screen.  It will be too late when you realize the “destruction” made by yourself.

2. Mesh up the formula (if any) somewhere in the workbook

Oh! That’s right.  I learn from mistake.  So now when I delete a cell, I select “Shift cells left” as I have already checked that there is nothing on the right: Shifting cells to left should impose NO impact on the tables.

Excel Tips - Clear or Delete 5

Yes! You are correct partially.  Although there is no impact on the structure of the table, some figures turn to #REF.  Why? Because those cells contain formula with cell reference to “Hourly Rate” that you had just deleted.

Excel Tips - Clear or Delete 6

OMG.  How come this is so troublesome to delete a cell?

Am I suggesting we need to check

  1. if there is anything surrounding the “to be deleted” cell
  2. if the “to be deleted” cell is a dependent of some other cells

before deleting a cell?

My answer is YES if you insist on “Deleting” a cell.

But why you need to “Delete” a cell when you have a better alternative?

Here we come the main point of this post🙂

Did you know… You may delete every thing (formats, contents, data validation, etc.) of a cell in few clicks:  HOME tab –> Edit Group –> Clear –> All

Excel Tips - Clear or Delete 7 (Tips: You may put it on QAT to make it even more convenient!)

Is this way much more direct and efficient?  Please leave your comment.

Excel Tips - Clear or Delete 8

This entry was posted in Excel Basic, Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s