Often in formulas in Excel, we use double quotes to indicate a blank. But in Excel, double quotes do not generate true blank cells. How can we get true blank cells without formulas in Excel? This tutorial will take you through the steps to accomplish this, plus show you a few other valuable tricks like converting text numbers to values, a quick way to convert formulas to values only, and how to delete rows with blanks.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a list of products that all begin with RD and contain a two digit number:
What I want to have as an end result is just a list of those products which have a number greater than 40, and enter “Order” in column “H” like this:
So, the first thing I will do is enter the following formula:
It looks correct, since the number on the part in the first row is greater than 40. But when I copy it down, I get this:
The reason is that the data in column G is text, and so Excel can’t determine if the last two digits are greater than 40 since they are not numbers, but text.
We need to convert those text digits to actual values. There are a few ways to do it, but the one I like to use is entering a double negative in front of the RIGHT formula, which will convert the results to actual values. After doing so, and copying down the formula, you’ll see the results are correct:
So now all I have to do is delete the rows that have blanks in column H, and I’ll have my result. To do this I will highlight column H, use the keyboard shortcut Ctrl + G to bring up the Go To dialog box, select Special, then Blanks and click on OK. This should highlight only the blank cells in column H, but look what happens:
Excel can’t find any blanks because the cells that appear to be blank have a formula in them. So, the next thing I’ll do is convert those formulas to values. The trick I like to use to do this is the highlight the area, right-click on the right edge of the area, and while holding down my right mouse key, dragging the area off then right back on and releasing my mouse key. A menu pops up and I select “Copy here as values only”:
However, when I go through the same process as above to select the blank cells, I get the same error from Excel:
The reason is that these cells are truly not blank. There is still an indicator in that cell from when we entered the double quotes. So how do we get these cells to be truly blank?
The first step is the enter something other than double quotes into our IF statement. I will use “No” and enter this formula:
Next, after I convert the formulas to values only, I’ll use the keyboard shortcut Ctrl + F to open the Find dialog box and search for the word “No”, and click on Find All:
Excel will give me a list of all the cells that contain “No”. These are all hyperlinks that I can click on to go to any individual cell, but if I hold down my Shift key and select the last one on the list, it will highlight all of them and all the cells containing “No” will be highlighted. Once they are highlighted, I can close the Find dialog box:
Now I can hit my “Delete” key and have true blanks in those cells. Then I can go back to the Go To dialog box, choose my blanks, the using the keyboard shortcut Ctrl + – (minus sign), bring up the Delete dialog box and delete those rows with blanks and I have the result I wanted:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!