Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

November 1, 2016

Hi fellow Excel Addict,

Francis Hayes (The Excel Addict)On a cold (5C/41F) and wet November day, here is one bright spot in my garden — a lonely red strawberry. Usually strawberries ripen in mid to late summer but this one has managed to stick around until November and somehow coax enough energy out of the limited sunshine we get at this time of year to turn a nice shade of red.

That red strawberry is looking a little greedy to me by the looks of those smaller strawberries. Maybe those white strawberries are giving a hint of the weather to come.


In
today's 'Excel in Seconds' tip, I show you 'Printing Without Color'.

If you missed last Thursday's 'Excel in Minutes' tip, 'How to Monitor Changes in Key Cells Throughout a Workbook', you can read it here now.

Please feel free to share my newsletter with your friends — they will thank you for it.

H
ope you have a great week and keep on Excelling,
Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com



TheExcelAddict.com Quote of the Day

"Life is like a coin.
You can spend it any way you wish,
but you only spend it once"

-- Lillian Dickson --




Excel in Seconds with TheExcelAddict.com

Printing Without Color

Do you have worksheets that are formatted using various colors, such as with Conditionally Formatted cells, that serve to highlight specific information for the user of the workbook?

But when it comes to printing the workbook, these colors may not be necessary.

Printing Worksheet Colors in Microsoft Excel 2007 2010 2013 2016 365

If you want to be able to print worksheets without these colors (and also save on ink/toner), there is a setting that allows you to do this.

1) From the Page Layout tab, click that tiny arrow in the bottom right corner of the Sheet Options group.

2) On the Sheet tab of the Page Setup dialog, selecting the Black & white option in the Print section allows you to print a worksheet only in black and white. Colors (and patterns) will not print.


Print Worksheet Without Colors in Microsoft Excel 2007 2010 2013 2016 365
"Spreadsheets Tips From An Excel Addict" is a twice weekly publication of TheExcelAddict.com.
'Excel in Seconds' on Tuesday & 'Excel in Minutes' on Wednesday

You can subscribe to my FREE Excel Tips Newsletter at TheExcelAddict.com
and get more tips like this every week

Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630


    

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.

I
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]TheExcelAddict.com




 
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.

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'.

"Spreadsheets Tips From An Excel Addict" is a twice weekly publication of TheExcelAddict.com.
'Excel in Seconds' on Tuesday & 'Excel in Minutes' on Wednesday

You can subscribe to my FREE Excel Tips Newsletter at TheExcelAddict.com
and get more tips like this every week

Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630