Hello and welcome back to the Excel Tip of the Week! This week, we have a Basic User post in which we are looking at deleting and clearing cells, and the difference between them.
Clearing a cell removes the contents or other elements of that cell. There's a menu for it on the Home tab:
These options allow various version of clearing out the cell - whether that be the content, the formats, the comments, or the hyperlinks. However, the cell itself remains and no other cells are removed.
There's also a 'Clear contents' option on the right-click menu. Confusing, pressing the Delete key actually does a Clear contents, not a deletion.
Again, there's a menu on the Home tab:
This is also accessible from the right-click menu.
Deleting cells removes those cells from the spreadsheet entirely - not only destroying their content, formatting, and so on, but actually leaving a gap in the spreadsheet. When you delete one or more cells, you will get this menu:
Depending on your choice, the other rows and columns of the spreadsheet will be moved into the gap left by the deleted cells. New cells will be created at the edge of the spreadsheet, so the whole will remain the same size.
The keyboard shortcut for deletion is Ctrl and -.
Previous post - Count items in commonTOTW index Next post - CONVERT
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.
Just wanted to share an example of a big time and space saving I found using the "clear all" function.
I was asked to review the formulas in an inherited daily P&L reporting spreadsheet as the symptom was taking an age to open, save and performing calculations. Whilst there were some terrible formula (in no way following the 20 principles) this wasn't the whole reason.
Throughout the time the spreadsheet had evolved, someone had copied a formula from the top of the spreadsheet, around line 20 when the daily transaction listing started, down to the bottom of the sheet - line 1,048,576. Given there were around 500 transactions each month this was about 1.05m too many calculations. when I realised this sort of thing was repeated multiple times in multiple tabs, removing these led to a 15mb file reduction and a massive speed increase. I was able to just use the delete button for clearing these formulas.
The point to this rather rambling post is that someone who had tried to make this spreadsheet look 'pretty' by colouring all the blank cells in white - using the fill color button, rather than the remove gridlines ( on the ribbon - view / show / gridlines) . Given they had done this to all cells across the sheet and down the sheet but only about 50 columns and 500 lines were used, they had formatted 99.99985% of the sheets for no reason. Given that formatting colour still uses memory, using the "clear all" function on all of these unused cells saved another 10mb of file space and another big chunk of time saving.
This terrible practice was repeated in 10 different regional office P&L spreadsheet reports each day, so by clearing this out had saved about 300mb a day (happy IT department) and about 1 FTE of time saving due to delays in opening and processing the files (happy controllers)
Using the "clear all" function has been so revolutionary for me, the shortcut has been added to my quick access toolbar!