If you use Excel on a daily basis, then you’ve probably come across situations where you needed to hide something on an Excel sheet. You may have additional data sheets that are referenced but do not need to be viewed. Or maybe you have multiple rows of data at the bottom of the worksheet that need to be hidden.
There are many different parts in an Excel spreadsheet, and each part can be hidden in different ways. In this article, I will walk you through the different content that can be hidden in Excel and how to view the hidden data later.
How to hide tabs / worksheets
To hide a sheet or tab in Excel, right-click the tab and select Hide. It was pretty straightforward.
Once hidden, you can right-click the visible sheet and select Show. All hidden sheets will be shown in the list and you can select the one you want to show.
How to hide cells
In Excel, there is no way to hide cells in the traditional sense, where they just disappear until you show them, like in the above example with sheets. He can only clear the cell so that it looks like there is nothing in it, but he cannot really “hide†the cell, because if the cell is hidden, what would you replace that cell with?
You can hide entire rows and columns in Excel, which I will explain below, but you can only clear individual cells. Right-click a cell or multiple selected cells, and then click Format Cells.
On the Number tab, select Custom at the bottom and enter three semicolons (;;;) without brackets in the Type field.
Click OK and now the data in these cells is hidden. You can click a cell and you will see the cell remains blank, but the data in the cell appears in the formula bar.
To display the cells, follow the same procedure as above, but this time select the original cell format, not Custom. Please note that if you enter anything in these cells, it will be automatically hidden after pressing the Enter key. Also, any original value that was in a hidden cell will be replaced when entered into a hidden cell.
Hide grid lines
A common task in Excel is to hide gridlines to make your data presentation cleaner. When you hide gridlines, you can either hide all gridlines on the entire sheet, or hide gridlines for a specific part of the sheet. I’ll explain both options below.
To hide all gridlines, you can click the View tab and then uncheck the Gridlines checkbox.
You can also go to the Page Layout tab and uncheck the View checkbox under Gridlines.
How to hide rows and columns
If you want to hide an entire row or column, right-click the row or column header and select Hide. To hide a line or multiple lines, you need to right-click the line number in the far left corner. To hide a column or multiple columns, you need to right-click the column letter at the very top.
You can easily detect that there are hidden rows and columns in Excel because numbers or letters are skipped and two visible lines are displayed to indicate hidden columns or rows.
To display a row or column, you need to select the row / column before and the row / column after the hidden row / column. For example, if Column B is hidden, you need to select Column A and Column C and then right-click and select Show to show it.
How to hide formulas
Hiding formulas is a little more difficult than hiding rows, columns, and tabs. If you want to hide the formula, you need to do TWO things: set the cells to Hidden and then protect the sheet.
So, for example, I have a spreadsheet with some proprietary formulas that I don’t want anyone to see!
First, I’ll select the cells in column F, right-click and select Format Cells. Now go to the “Protection” tab and check the “Hidden” box.
As you can see from the post, hiding formulas won’t take effect until you protect the worksheet. You can do this by clicking the Browse tab and then clicking Protect Sheet.
You can enter a password if you want people not to be able to hide the formulas. You will now notice that if you try to view formulas by pressing CTRL + ~ or by clicking Show Formulas in the Formulas tab, they will not be visible, but the results of that formula will remain visible.
Hide comments
By default, when you add a comment to an Excel cell, a small red arrow appears in the upper right corner to indicate that there is a comment there. When you hover over or select a cell, the comment automatically appears in a pop-up window.
You can change this behavior so that the arrow and comment are not displayed on hover or cell selection. The comment will remain and can be viewed simply by going to the Browse tab and clicking Show All Comments. To hide comments, click File and then Options.
Tap Advanced and then scroll down to the Display section. There you will see an option No comments or indicators under the heading For cells with comments show:
Hide redundant text
In Excel, if you enter a lot of text in a cell, it just overflows the adjacent cells. In the example below, the text only exists in cell A1, but it flows into other cells so that you can see everything.
If I were to enter something into cell B1, it would disable the overflow and show the contents of B1. If you want this behavior not to be introduced into a neighboring cell, you can right-click the cell, select Format Cells, and then select Fill from the Horizontal Text Alignment drop-down list.
This will hide the overflow text for that cell, even if there is nothing in the adjacent cell. Note that this is a hack of sorts, but it works in most cases.
You can also select Format Cells and then select the Wrap Text check box in the Text section of the Alignment tab, but this will increase the line height. To work around this, you can simply right-click the line number and then click “Line Height” to revert the height back to its original value. Either of these two methods will work for hiding the overflow text.
Hide the workbook
I don’t know why you want or need this, but you can also go to the View tab and click the Hide button in the Split section. This will hide the entire workbook in Excel! There is nothing you can do except click the Show button to return the book.
So now you’ve learned how to hide workbooks, sheets, rows, columns, gridlines, comments, cells, and formulas in Excel! If you have any questions, please leave a comment. Enjoy!
–