Excel: Hide and Unhide Data

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. Hide the column.

    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.

  2. Initially, the active cell is in the hidden column.

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

    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.

For more resources for Microsoft Excel