Hide Error Cells When Printing

October 3, 2023 - by Bill Jelen

Hide Error Cells When Printing

Problem: I have a formula that does division. Occasionally, the divisor cell is zero, so I have a couple of #DIV/0! value errors. I need to print this sheet without the errors to get the report to a staff meeting. I don’t have time to rewrite all the formulas to test whether the divisor is zero. What can I do?

Several cities are shown. Revenue is in B. Units is in C. A formula in D calculates the average as =B2/C2.  However, one city has 0 in B and C, so the formula in D shows as #DIV/0 meaning a division by zero error.
Figure 1345. A few nagging error cells.

Strategy: From the Page Layout tab, you can select the dialog launcher at the bottom right corner of the Page Setup group. In the Page Setup dialog, you go to the Sheet tab, select the dropdown for (Print) Cell Errors As, and select <blank>.

continue reading »

More Reading

Group Columns Instead of Hiding Them

Group Columns Instead of Hiding Them »

October 2, 2023 - by Bill Jelen

I have a report with months and quarters. My manager sometimes wants the reports printed with months hidden and other times with the months showing. It is a pain to hide/unhide the four groups of monthly columns.

Hide and Unhide Data

Hide and Unhide Data »

September 29, 2023 - by Bill Jelen

I need to hide data in a worksheet, but I don’t want to delete it. Is there a way to do this besides using the previous two techniques?

Hide Values Using a Number Format

Hide Values Using a Number Format »

September 28, 2023 - by Bill Jelen

Why did you use the white font to hide those cells? Isn’t there a custom number format to hide values?

read more articles »

Featured Products

Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

April 2022

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won't find anywhere else, and create automated reports that are amazingly powerful.

MrExcel 2021 - Unmasking Excel

February 2021

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

Programming PowerPoint with VBA

February 2022

This book assumes you already use PowerPoint and want to automate or enhance your presentations using Visual Basic for Applications (VBA). This book includes VBA samples for working with layouts, themes & masters, creating tables, drawing objects, charting, animation effects and event programming.

Financial Modelling in Power BI

September 2022

The only book to produce all three financial statements in Power BI.

see more products »