How to prevent cells from printing in Excel

Question

How can I prevent the contents of specific cells (or entire row or column) from printing in Excel, while keeping them visible on the screen when I'm editing the file? Ideally I should be able to do this to cells in the interior of the spreadsheet (i.e., not just the rightmost column(s) or the bottom row(s)). Hiding row(s) or column(s) is not acceptable because I need to maintain the cell size, to maintain the overall appearance of the sheet.

I've looked for a way to define a conditional format that's conditional on whether Excel is currently printing, but there doesn't seem to be any such thing. I've been manually setting the text color to white before printing (and manually reverting it afterwards), but it seems there should be an automagic way of doing this.

Answer 1

You can apply normal (not conditional) formatting to achieve this. Select the cell, row, and/or column in question and go to "Format Cells", which is accessible through the ribbon ("Home" → "Cells" → "Format") or the keyboard shortcut Ctrl+1.

On the "Number" tab, select Category = "Custom" and for "Type" enter:

"";"";"";""

or simply

;;;

This tells Excel to display an empty string if the cell contains a positive number, negative number, zero or (non-numeric) text. So any value that is not an error will be hidden on the screen and when printed.

Answer 2

If you're using Office 2010, under "File -> Print -> Page Setup -> Sheet (tab)", the setting for "Print area" should be what you are looking for. In my example, only columns A:J will be printed; however, everything is shown when viewing on screen.

This is what the dialog looks like:

^{(Click image to enlarge)}

This may also be accessible as "Page Layout" → "Page Setup" → (corner button) → "Page Setup" → "Sheet":

    

Answer 3

This solution doesn’t require you to take special action before and after each time you need to print the file.  It builds on the idea of using a “Print Area”, as suggested by Terence and Don Livezey.  Put the cell(s) that you don’t want to print outside the print area.  If that’s good enough for you, you’re done.

But you might want the excluded cell(s) to appear inside the print area when you’re editing the worksheet.  In that case, for each cell that that you want to display but not print:

• Insert a “shape”.  (A rectangle works best.)  Position it over the cell where you want the data to appear.  Format it visually as you want.  It may be best to have no border line.  Set the fill color to whatever you want for the background color, or no fill to let the underlying cell’s fill color show through (and make the shape itself invisible).
• Configure the shape to display the content of the desired cell, by selecting the shape and then typing a reference (e.g., =D15) into the formula bar at the top.  Do not just type into the shape; if you do that, the formula (=D15) will display literally.
• Right click on the shape and select “Format Shape”.  Under Properties, you can select if the shape is printed or not (by clearing the “Print object” checkbox).  (In Excel 2013, this is under “Shape Options” → “Size & Properties” → “Properties”.)

Answer 4

When I needed to do what you're saying, what I would do is:

Use the function in the code editor (VBA):

Private Sub Workbook_BeforePrint(Cancel As Boolean)

to hide the columns or rows, do the printout, and then unhide them.

Example:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.Name = "Sheet1" Then
        Cancel = True
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        With ActiveSheet
            .Rows("10:15").EntireRow.Hidden = True
            .PrintOut
            .Rows("10:15").EntireRow.Hidden = False
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Or change the respective part to hide columns (this example hides columns B and D):

        With ActiveSheet
            .Range("B1,D1").EntireColumn.Hidden = True
            .PrintOut
            .Range("B1,D1").EntireColumn.Hidden = False
        End With

Or hide all rows with a blank cell in column A:

        With ActiveSheet
            On Error Resume Next
            .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
            .PrintOut
            .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
            On Error GoTo 0
        End With

Links:

• Hide rows, columns or cells when you print a sheet
• Prevent Printing of Specific Cells
• How do I add VBA in MS Office?

Answer 5

I solved the same issue by creating a separate sheet and copying a link to the information in there. To do this, highlight the whole of the original sheet and copy it (Ctrl+C). Go to the new sheet and right click the first cell. Select the link icon in the Paste Options. Everything pasted will be unformatted, so you'll have to correct this. Empty cells will contain zeros. This can be rectified by going to "File" → "Options" → "Advanced" and unchecking the "Show a zero in cells that have a zero value" box.

You can now delete any cells, rows or columns that you wouldn't like to appear on print outs. The Print sheet will update as you update the original sheet.

If, like me, you have a base sheet that you copy for different periods of time or purposes, you can create this print sheet for the base sheet. When you need a new pair, simply copy both sheets and the new print sheet will link to the new sheet.

Answer 6

I don't have enough reputation points to comment on other posts, so I'll add an "answer" with some observations. I had the same or similar need, and there are pros and cons to various approaches.

• Setting print area: This can work depending on the layout of the sheet, but it doesn't handle a non-contiguous print area selection too well. Or, perhaps it handles it very well, but the selected approach doesn't work in my case. For example, if I have a spreadsheet with columns A-E, and I want to hide columns B and D on printing, then it puts each printed column {A, C, E} on a separate page. Not really what I wanted.

• Setting text to white on white: Could work in some cases, but BEWARE it does not prevent the hidden data from leaking. If you're trying to hide sensitive information, know that if you print to PDF, the data will still be present in the PDF file. Even though it is white-on-white, it is still there, and can be copied/pasted out of the PDF file.

I prefer the approach that uses macros to hide the selected columns upon printing, although I realize that may not be applicable or the best choice in all situations.

Answer 7

You can "hide" entire rows or columns by right clicking the row/column and selecting "Hide". This will prevent the row/column from being printed (or displayed). If you have multiple rows/columns, you can highlight them by "click drag" if they are next to each other or by Ctrl+click if they are not next to each other. When rows/columns are hidden, their number/letter is not shown. To unhide them just highlight the rows/columns on either side and "right click" and select "Unhide".

I am not sure if you can prevent individual cells from printing other than changing the font color. You may be able to automate this with conditional formatting.

Answer 8

Besides hiding columns or rows as is suggested above. You might look at Custom Views as a simple way to apply and un-apply what you don't want to show.

For instance if you print one column for a salesperson, another for a warehouse, and another with everything, you may create a custom view for each and easily switch to either.

Answer 9

Another simple way is to temporarily clear the contents of the cell by highlighting the selected cells - right click and select "Clear Contents".

Now you can print and then click the Undo button and all is back to normal.

Answer 10

I've gone down the "clear contents" road and can say that on a busy day, it's not hard to forget to undo something just for the sake of printing.

My process since consists of having a "working" (source) tab, with a "do not touch" message to any colleagues working in my file, as well as a "print" (destination) tab that draws directly from the working tab without having to worry about clearing, hiding, or remembering to put back.

When I update the source, the destination gets all the info it needs, and I don't lose hours of work I should have been better about backing up.

Also, back up your files, hehe.

Cheers

Answer 11

To prevent row or colum from printing you can hide it from view:

1. Select row(s)/column(s)
2. Right click on it
3. Choose "Hide" in context menu

The altenative way is to create group and then collapse it:

1. Select row(s)/colum(s)
2. Choose Data tab (in ribbon) -> Group button in Outline group

Now you can quickly collapse/expand row(s)/colum(s) for editing/printing.

Also take a look at the video describing multiple ways to hide cells (including described above).

Answer 12

Bearing in mind the previous advice that white font may be visible on PDF printing, I use the following technique:

Use a cell outside the print area with a "1" or "0" entry.

I then refer to this field in the conditional formatting of all cells required not to print.

If A1=0 then set format to white font.

If A1=1 then set format to black or any colour font you wish.

The second line may not be required but also allows you to highlight areas using different numbers for a range or single cells to the backgrounds, borders and fonts you choose by selecting a range of numbers in cell A1.