FREE Excel Tips Newsletter from
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010,
2007, 2003
November 10, 2016

Greetings from The Excel Addict

Francis Hayes (The Excel Addict)

Hi fellow Excel Addict,

I'm a little behind on my newsletters this week. This 'Excel in Minutes' newsletter was supposed to be ready yesterday but due some out-of-town commitments I had earlier this week I'm playing catch-up.

And being a day late, I am sending this newsletter on Remembrance Day, a memorial day observed on November 11th here in Canada and in other Commonwealth Nations since the end of the First World War to honour the members the armed forces who died in the line of duty.

This is picture I took this morning at the Monument of Honour in my hometown of Conception Bay South.

2016-11-11 Monument Of Honour Conception Bay South, Newfoundland, Canada

In today's 'Excel in Minutes' tip I'm going to show you how to 'Find (and/or Replace) All Blanks Resulting From Formulas'.
I think you're going to like this one.

f you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you how to 'Prevent Auto Incrementing of Numbers'. Although it is a pretty basic tip, as I suspected, I received a lot of feedback from people who were unaware of this trick that will now save them so much frustration. You can read that tip here.

I hope you have a great week and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]


If you missed my last newsletter, you can click here to view it online. Quote of the Day

"The only thing necessary for the triumph of evil
is for good people to do nothing."

-- Edmund Burke --
If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

Find (and/or Replace) All Blanks Resulting From Formulas

When is a blank cell not  blank cell? Answer. When a blank is the result of a formula.

Formulas such as =IFERROR(S10/$D10,""), may result in blank cells that are not actually blank. 
A blank returned by a formula such as this is actually a zero-length string (""), which is different than an genuine empty cell. 

These cells can sometimes be difficult to deal with because Excel doesn't treat them as 'blank' cells. This can cause issues for you when you have a worksheet with formulas that return zero-length strings. Some issues that may result from these 'non-blank' blank cells are...

• Using CTRL arrow key to jump to next blank cell in a row or column may not work because Excel will not recognize these 'blank' cells and go right on past them;

• Using F5, Goto, Special, Blanks does not recognize these cells as blanks.

Cannot find blank cells in Excel?
• Autofilter DOES treat these cells as blanks, so for a single column it's easy to find and select these blanks. However, if the blanks are scattered all over your worksheet, using Autofilter on each column will be very cumbersome.

Find blank cells with autofilter not working excel 2007 2010 2013
• Using  Find (CTRL+F) and Replace (CTRL+H) doesn't ALWAYS find these blanks.

Find and replace blank cells not working in Excel 2007 2010 2013 (zero-length string, null)
I said that "Find and Replace doesn't ALWAYS find these blanks". Here's the trick you need to know to find (and replace, if you want) these zero-length blank cells.

To limit the search area, select that range first, otherwise select a single cell to search the entire worksheet.

1) Open the Find and Replace  dialog (CTRL+F if you just want to Find or CTRL+H if you want to find and replace );

2) Clear anything in the 'Find what' field;

3) In the 'Look in' field, select Values;

4) Click the Find All button. All 'found' cells will be listed at the bottom of the dialog with only one item selected;

5) Press CTRL+A to select all items in the 'found' list. All items will be highlighted in the dialog and all cells on the worksheet will be selected;

6)  At this point, if you want to replace these cells with something (e.g. zeros) type the value in the 'Replace with' field and click Replace All;

7) When you click the Close button on the dialog, you will be back to the worksheet with all of the cells still selected. If you want, you can add a background fill color to highlight theses cells to make them easier to identify .

Find and replace blank cells in Excel 2007 2010 2013 (zero-length string, null)
I struggled with locating these 'blank' cells many times before I discovered that the trick was to search in 'Values' not 'Formulas'.

FREE Excel Tips Newsletter from
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630