Ruby has a worksheet that she needs to print out in a couple of different ways, for different users. Part of preparing her data for printing involves hiding or displaying some rows and some columns, as appropriate. Ruby wondered if there was a way to hide the contents of individual cells, as well.
If, by "hide," you want to have the cell disappear and information under it move up (like when you hide a row) or move left (like when you hide a column), then there is no way to do this in Excel. Actual hiding in this manner can only be done on a row or column basis.
There are ways that you can hide the information in the cell, however, so that it doesn't show up on the printout. One easy way, for instance, is to format the cell so its contents are white. This means that, when you print, you'll end up with "white on white," which is invisible. Test this solution, though—some printers, depending on their capabilities, will still print the contents.
If this approach works for you, you could expand on it just a bit to make your data preparation tasks just a bit easier. Follow these general steps:
Figure 1. The New Formatting Rule dialog box.
Figure 2. The Font tab of the Format Cells dialog box.
Another solution is to use a custom format for the cells whose content you want to hide. Follow these steps:
Figure 3. The Number tab of the Format Cells dialog box.
Now the information in the cell is not visible, nor will it print. You can, however, see the information in the Formula Bar, and it can be overwritten if you enter anything else in the cell.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6866) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Hiding Individual Cells.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Excel provides a number of different ways you can apply formatting to a cell based upon various dynamic conditions. One ...
Discover MoreWant a quick way to replace background colors in cells? It's easy to do using Find and Replace, or you can simply use the ...
Discover MoreWant to repeat cell contents over and over again within a single cell? Excel provides two ways you can duplicate the content.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-08-02 03:37:21
Michael (Micky) Avidan
@To whom it may concern,
If you intend to "hide on the printout" more than a single cell - you should use this: =$J$1="p" to determine which Cells to Format,
rather than: =J1="p"
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL
2017-08-01 13:06:20
Dennis Costello
I've used the "white-on-white" Conditional Format technique for error messages: the normal format is white text on a white background, but if the situation in the other cells constitutes the error, the conditional format is white text on red background. Easy and effective.
2016-11-21 11:56:21
DPark
I have used this technique many times to include instructions to other users that are not obtrusive and that do not print. In these situations, I want the information to appear in the formula bar but not on the printed page. I find this to be easier to use than comments attached to individual cells.
2016-11-21 09:33:00
Jennifer Thomas
Agree that ;;; is the best method, but since you can still see the cell contents in the formula bar, you might want to hide that bar in the workbook via an 'on open' macro for the workbook (to turn it off), and an 'on close' macro to turn it back on again for the user (otherwise the formula bar won't show for that user in other workbooks).
I wouldn't presume to share my 'works-but-is-probably-inefficient' macro code for this, but if someone who is actually good at excel macros wants to share I'd appreciate it!
2016-11-19 16:18:49
mandora
Formating cells with custom format ;;; works like a charm. Setting text white only worked with printing set to color, since white is a color. I had other colors on the sheet I did not want to print.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Comments