 |
|
| TheExcelAddict.com | |
| December 12, 2018 | |
| Hi fellow Excel Addict, | |
Welcome
to winter. Yes, the forecast was correct and we had
25cm (10in) of snow last Thursday night and maybe
another 20cm (8 in)since then. Unlike our typical
weather, we haven't had a warm-up yet to melt this
snow, so we have a lot of snow on the ground right
now. It's supposed to rain on the weekend so we may be looking at the green grass again by next week — not a welcome sight for anyone 'dreaming of a white Christmas'. I hope you enjoy today's 'Excel in Seconds' tip 'Prevent Formula Errors From Displaying And Printing'. If you do, please share is with your colleagues and any other Excel users you know. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
 |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
| Prevent Formula
Errors From Displaying And Printing |
|
It's not uncommon to see formulas resulting in errors such as #DIV/0 or #N/A. Not such a big deal as long as you know your formulas are correct. But sometimes these error may raise questions in the minds of your worksheet readers. You have a couple of options: You can prevent the errors from displaying in the worksheet. You can allow errors to display on your worksheet but prevent them from appearing on your printed report. Prevent Formula Errors From Displaying In Worksheet It is not unusual for Excel users to use formulas in a column that refer to empty cells in other columns. This often causes the formulas referring to empty cells to display errors. 
In the example above, the formula =ROUND(B7/C7,2) results in a #DIV/0! error because cell C7 is blank. And you cannot divide by a blank cell or zero. The IFERROR function was added to Excel back in 2007. IFERROR(formula, value_to_display_if_error)
The IFERROR function allows you to specify a value to show in the cell if the result of the formula is an error. To prevent formula errors from appearing in your worksheet, wrap the formula with the IFERROR function and use two double quotes (i.e. Excel reads "" as 'blank') as the value_to_display_if_error. If the formula results in an error, Excel will display a blank cell. Otherwise, the result of the formula will be shown. IFERROR(your_formula,"")
 Prevent Formula Errors From Printing If you're OK with these errors in your worksheet but don't want them to show up on your printed report, instead of hiding these errors, there is an easy way to prevent them from printing. 1) From the Page Layout tab, click Print Titles. This will open the Page Setup dialog; 2) On the Sheet tab you will see a 'Cell errors as' dropdown. Here you can choose whether to print these cell errors as: displayed on the worksheet, blanks, two dashes or #N/A; 3) Click OK.  Now, even though the errors are still shown in the worksheet, when you print your report they will be hidden.  |
|
|
|
|
To share this tip with your friends and
|
|
|
|
|
 |
|
|
|
|
 |
|
| 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
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |