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:
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
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.
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.
OMG. How come this is so troublesome to delete a cell?
Am I suggesting we need to check
- if there is anything surrounding the “to be deleted” cell
- 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
Is this way much more direct and efficient? Please leave your comment.