Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible to prevent the contents of specific cells (or entire row or column) from printing in Excel? I need to maintain the cell size. I've been setting the text color to white before printing, but it seems there should be an automagic way of doing this.

share|improve this question

10 Answers 10

up vote 3 down vote accepted

You can apply normal (not conditional) formatting to achieve this. Select the cell, row, column in question and go to Cell Formatting (Windows shortcut it Ctrl + 1).

For number format select Custom and for Type set the format to be:

"";"";"";""

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

share|improve this answer
    
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
4  
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

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

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 tohide columns (this example hide column 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:

share|improve this answer
    
This is a very poor solution and links do not work any longer. –  Vojtěch Dohnal May 20 at 12:39

Not sure if you've received an answer to this or not. 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 else is shown when viewing on screen.

see here for example: http://i.stack.imgur.com/5JMRY.jpg

share|improve this answer
    
How strange - this is the only correct and valid answer. –  Vojtěch Dohnal May 20 at 12:34

You can "hide" entire rows or columns by right clicking the row/column and selecting hide. This will prevent the row/column to be printed. If you have multiple rows/columns you can highlight them by "click drag" if the are next to each other or by "crtl 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 maybe able to automate this with conditional formatting.

share|improve this answer
    
But the OP does not seek hiding cells but to prevent them from printing. –  Vojtěch Dohnal May 20 at 12:40

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.

share|improve this answer

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.

share|improve this answer
    
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 at 12:47

You could try hiding the row by right clicking on it and pressing hide.

http://spreadsheets.about.com/od/excelformatting/qt/070814_hide.htm

share|improve this answer
1  
You answer is good but has already been provided by @RSMoser. The link you kindly provided is useful but may have been better offered as a comment or suggested edit to that answer. –  pnuts Sep 28 '12 at 11:23
    
Okay, I wasn't aware of that. Thank you for letting me know. –  Jake Elsley Sep 28 '12 at 11:32

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

share|improve this answer

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

share|improve this answer

I've done this before. I wish I could provide code for you but it was long ago and I don't have that code any longer. My data was set up with rows as records and columns as fields. I had specific rows to print at certain times, so I added a column to my sheet that I would put a letter into as a label for when to print. i.e. S for Sales dept, A for Accounting etc.. Then I wrote a vba routine for each letter that grabbed all the records with that letter, copied them to another sheet, printed them, then deleted the sheet. This way neither my sheet nor my original data were altered in any way. I suggest looking into doing it a similar way, if you feel you're vba skills would allow. Happy coding...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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