How to hide cells before printing in Excel

In this article, we will learn How to hide cells before printing in Excel.

How to hide cells in Excel

Problem occurs when a user needs to get the print of the page. Let's say The page has error values or many zero values and can be interpreted as blank cells in the printed page. For example hiding the Employee code column before printing the page. For situations like these, we hide cells containing values/text before printing, use one of the following techniques: Note: These techniques only change the font color so that the cell appears invisible, it doesn't edit the data value.

Format Selected cells in Excel

Select all cells, Then switch the font color the same as the background color of the cell. You can achieve this by many ways.

  • If there are less numbers of cells to hide. Select cells > Home > Font Color (Switch to white or same as background color).
  • Select cells > Open Format dialog box (Ctrl + 1) > Font (Switch to white or same as background color).

Alternate way using Conditional Formatting

If there are more cells and those cells come under some criteria like contain zero values or contain errors. They use this condition.

Select the whole table > Home > Conditional Formatting > New Rule > Use the formula (apply criteria as formula) > Custom Format > Font > Color (Switch to white or same as background color).

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have a sample data to print. But it doesn't look good to print. So we need to hide some data which is unnecessary on the table. So we just select the cells as shown in the below table and follow further steps.

Go to Home > Click the arrow option next to A > Click on White Background 1.

Or you can Go to the Format cells dialog box. OPen format dialog box using three ways.

  • Go to Home > Numbers > More number formats.
  • Right click selected cells > Format cells…
  • Keyboard shortcut Ctrl + 1.

Then Go to Font > Color > drop down color list > Select white.

Alternate way using Conditional Formatting

We use this if you have a large amount of cells to hide in data satisfying some criteria. To make the above method interesting. We can use conditional formatting. Make a new rule in conditional formatting. Use a formula to determine cells to color and now use the font format as transparent or white. Now we learn how we will do it step by step for an example Hiding error values and zero values

Select cells > Home > Conditional Formatting > New Rule > Use a formula to determine

Use the formula

=OR(ISERROR(A1), A1=0)

OR operator returns True or False and edits only the cells where the formula returns True. Here OR operator returns TRUE only where the cell has error value or contains zero value.

As you can see the data is neat and in a manner of print. So now check the page and layout and click Print. You can use any function or make any formula in a conditional formatting formula box that returns TRUE or FALSE.

Here are all the observational notes using the formula in Excel
Notes :

  1. These techniques only change the font color so that the cell appears invisible, it doesn't edit the data value.
  2. Conditional formatting allows you to apply the formatting basis on the cell values such as colours, icons and data bars.
  3. The formula works for text and numbers both.

Hope this article about How to hide cells before printing in Excel is explanatory. Find more articles on calculating values and related Excel formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to Select Entire Column and Row Using Keyboard Shortcuts in Excel : Use Ctrl + Space to select whole column and  Shift + Space to select whole row using keyboard shortcut in Excel

Conditional Formatting for Pivot Table : Conditional formatting in pivot tables is the same as the conditional formatting on normal data. But you need to be careful while conditional formatting pivot tables as the data changes dynamically.

Find the partial match number from data in Excel : find the substring matching cell values using the formula in Excel

How to Highlight cells that contain specific text in Excel : Highlight cells based on the formula to find the specific text value within the cell in Excel.

Conditional formatting based on another cell value in Excel : format cells in Excel based on the condition of another cell using some criteria.

IF function and Conditional formatting in Excel : How to use IF condition in conditional formatting with formula in excel.

Perform Conditional Formatting with formula 2016 : Learn all default features of Conditional formatting in Excel

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.