MrExcel.com - Your One Stop for Excel Tips & Solutions

Hide and Unhide Data

September 29, 2023 - by

Hide and Unhide Data

Problem: I need to hide data in a worksheet, but I don’t want to delete it. Is there a way to do this besides using the previous two techniques?

Strategy: Another method for hiding data to simplify a worksheet is to physically hide a row or column.

Say that you want to hide column C. To do this, you select a cell in column C. Then you select Home, Format dropdown, Hide & Unhide, Hide Columns. Alternatively, use Alt+O+C+H. Alternatively, right-click the column C heading and choose Hide.

Column C will be hidden.

It is interesting to note that the cell pointer has essentially disappeared. You can see from the Name box that C3 is the active cell. You can also see in the formula bar that the value of C3 is 152. Even though the column is hidden, the active cell is still C3.

Simply press the Left Arrow key or Right Arrow key to move to a visible column to get the cell pointer back.

Home, Format, Hide & Unhide, Hide Columns.
Figure 1341. Hide the column.
The column letters go from A, B, to D, E, F, and G. It seems that you have hidden column C. But if someone uses F5 to Go To C3, you can still see the value in the formula bar.
Figure 1342. Initially, the active cell is in the hidden column.

Additional Details: Immediately after you hid column C, the active cell was still in column C, so you used an arrow key to move out of the hidden column. Once you’ve arrowed out of the hidden column, you cannot arrow back into it. However, if you type C3 in the Name box—the area that contains the active cell address to the left of the formula bar—and press Enter, Excel will once again select a cell in the hidden column. This can be a handy trick for seeing a value in a hidden cell. You can also use the up and down arrow keys to move through column C, seeing each value one at a time.

To unhide column C, you click the B heading and drag to the right to select the entire range B:D. Select Format, Hide & Unhide, Unhide Columns.

What happens if you need to unhide column A? You can’t really select something to the left of A to use the trick just described, but you can follow these steps:

  • 1. Click the column letter B.

  • 2. Drag up and to the left so that the mouse is above row 1. The difference is subtle, but you have now selected columns B and A. Select Format, Column, Unhide.

This article is an excerpt from Power Excel With MrExcel

Title photo by Mikhail Vasilyev on Unsplash