| FREE
Excel Tips Newsletter from TheExcelAddict.com Subscribe here to get more tips like this every week |
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)
Email: fhayes[AT]TheExcelAddict.com
If you missed my last newsletter, you can click here to view it online.
| TheExcelAddict.com 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.
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.
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 .
| FREE
Excel Tips Newsletter from TheExcelAddict.com Subscribe here to get more tips like this every week |
| 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. |
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630