15

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.

  • 2
    You should change the accepted answer to Terence's, as it is the one preventing cell from printing, without preventing them from showing up on screen (which looks like an implicit requirement for this question). – Ad N Aug 2 '15 at 10:36

12 Answers 12

7

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.

  • Wow, that's an awesome poweruser tip. I can't wait to try it in the morning. – Michael Itzoe Oct 20 '10 at 23:31
  • 10
    This also prevents cell contents from displaying, not only from printing. I would like to show the value on the screen, but hide when printing. – utapyngo Mar 10 '14 at 9:03
12

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:

Screenshot
(Click image to enlarge)

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

    

  • 1
    How strange - this is the only correct and valid answer. – Vojtěch Dohnal May 20 '15 at 12:34
7

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”.)
  • A non-printing shape is very useful when you only need to include user instructions. (Which is the reason I was reading this). – Theodore Aug 9 '17 at 16:40
2

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:

  • This is a very poor solution and links do not work any longer. – Vojtěch Dohnal May 20 '15 at 12:39
2

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.

1

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.

1

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.

  • 1
    But the OP does not seek hiding cells but to prevent them from printing. – Vojtěch Dohnal May 20 '15 at 12:40
  • @VojtěchDohnal hiding a cell will also hide it from printing. Presumably, once printed, you can then unhide. – iheanyi Dec 8 '16 at 23:15
0

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.

0

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

enter image description here

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

  • I do not understand, how this answer could have been upvoted... If you do not want cells to be printed just delete them... – Vojtěch Dohnal May 20 '15 at 12:47
  • The cells may contain values (for example profit percentage) which are to be used in formula calculations i.e. variables. If it is deleted then the calculations do not! – Recycled Steel Nov 5 at 14:12
0

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

0

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

0

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.

  • How does this stop the PDF printing in white? Aren't the cells you're setting to white via Conditional Formatting printed just as white on the PDF as they were before? – Andi Mohr Jul 14 '16 at 8:47
  • Yes they are but this solves the original question of how to switch between non printable cells (white font) and viewing them in the sheet by using cell A1 content (0 or 1) to switch the white font cells on and off. – Darren Jul 14 '16 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.