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.
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.
If 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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
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.
• 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.
• Using Find (CTRL+F) and Replace (CTRL+H) doesn't ALWAYS find these blanks.
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 .
I struggled with locating these 'blank' cells many times before I discovered that the trick was to search in 'Values' not 'Formulas'.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630