Sometimes you want your Excel spreadsheet to display data differently in file than on paper. Effectively what you can do is add custom views to print empty cells.
To see a use case, let’s take a look at this question from Jack:
I have one excel sheet, in that sheet I had marked as A and P as content of the cell, now i want this excel sheet to be printed, but I do not want to print A and P as normal, I want to print this content of cell (A and P) in such a manner that it cannot be seen with naked, actually i want to overwrite it with ball pen, how to do this, I am also attaching image of excel file, please have a look, and give me your expert advice or trick to do my task.
* Edited to remove extra spacing.
I can see English is not their first language, but that’s ok. We’ll both get on the same page.
Thanks for your question Jack. So you want to have cells with tabular data and hide or at least obfuscate the cells with A and P in them when you print.
Table of Contents
3 Ways to Print Empty Cells by Formatting
1. Adjust Font Colors
The easiest way to do this would be to adjust the colors of the font within those cells. To have the data blend in entirely, make the colors the same as the background, which is white by default:
1. Select the cells with A and P in them.
2. On the Home Tab under Font > Click Font Colors.
3. Change to white.
This way the data is still there and you can see it by highlighting all cells or by changing the font color again.
If you wish to change it to a lighter shade than black:
1. While under the Font Colors window, select More Colors.
2. Select a level of gray that’s acceptable to you.
This way you can still see the cell content, although lightly, and will be able to trace the contents with your ballpoint pen.
2. Hide the Rows
1. Highlight cells with A and P by clicking your mouse in one corner and dragging to the other corner so you select all cells in that block.
2. On the Home Tab under Cells > Click Format > Hide & Unhide > Then click Hide Rows.
2a. If you like shortcuts, the hide row shortcut is CTRL + 9.
Whenever you want to show the hidden rows, do the following while under the Format menu:
1. Under Hide & Unhide > Click Unhide Rows.
1a. If you like shortcuts, the unhide row shortcut is CTRL + SHIFT + 9.
3. Format Cell to Hide
1. Right-click selection of cells and select Format Cells.
2. Click on Number Tab if it already isn’t on there.
3. Click on the Custom Category at the bottom left part of the window.
4. Type in 3 semicolons ;;; in the Type box.
5. After you click OK the value of the cell will be hidden in the sheet view but you’ll see the content in the Formula Bar.
This will hide the value of the cell regardless if the content is a number or a letter.
Update 1: We got a little more clarity from Jack.
Thanks or such a detail reply and showing me different ways to do, but what I want is not exactly solved by any of the ways you mentioned, I want to see this A by any means when this sheet is printed, but at the same time I want to other peoples who see the printed sheet not to see this A. It would be great if can give me solution for this.
* Edited to remove extra spacing.
Filters and Custom Views
Give this a whirl, it’s a combination of what I recommended earlier and custom views:
1. Start off by doubling your rows so you have one half data, the other half blank. Do this by selecting the data.
2. Then go to the Home tab > Under the Font group select Borders > All Borders.
3. Click inside those cells or re-highlight the selection again and go to the Data tab > Under the Sort & Filter group > Filter.
4. Click a drop-down arrow and only select the data (non-empty cells) or deselect (Blanks), whatever is easiest for you.
5. Now you will see only the data.
6. Go to the View tab > Under the Workbook Views group > Custom Views.
7. Click Add. In my screenshot, you’ll see I already created 2 views.
8. Put in your view name in the Name field. I named mine Test but you can name it something more descriptive.
9. Go back to that same drop-down arrow you used to select the data and deselect the data and select (Blanks).
10. Now your little table will be blank.
11. Click on the Custom Views button once again (Step 6). Click on the Add button once again.
12. Put in your view name in the Name field. I named mine Test2 but you can name it something more descriptive.
13. From here you can quickly switch between views to essentially swap the data out.
This process is quick to do and seamless but it’s not really swapping, it’s showing and hiding the sections of your mini-table. You can swap the views from the Custom Views window and click Show or just double-click the View selector in that window.
The first view will be your view where you can see your data (A and P) and the second view will be your team’s view (or whoever will receive the print out of empty cells) when you’re ready to print and distribute your sheet.
Print Empty Cells Conclusion
Let me know what you think. Outside of these 4 ways, I wouldn’t spend too much time on this since there’s a manual element to it already. Thanks!
Update 2: I heard back once again. The custom views are what helped here.
Thanks for such a detail and helpful reply, Thanks a lot!
Perfect!
