Quickly Hide Excel Rows and Columns
In some of my Excel workbooks there are calculation rows or columns that are required for producing the end result, but users don’t need to see them.
I can manually hide these rows and columns, then unhide them if I need to check a calculation, or adjust a formula.
To make it easy to hide things, I mark the rows and columns with an X. In the screen shot below,
- the only entries in Row 1 are the X marks on the columns that should be hidden
- the only entries in Column A are the X marks on the rows that should be hidden.
Select the Marked Columns
First, follow these steps to select the marked columns.
- Click the row button for row 1, to select the entire row.
- On the Ribbon, click the Home button
- In the Editing group, click Find & Select, then click Go To Special
- In the Go To Special dialog box, click Constants
- Uncheck all the boxes except Text, then click OK
Now, only the cells that contain constant values (the “X” cells) are selected.
Hide the Marked Columns
Next, with the marked columns selected, follow these cells to hide the columns:
- Click the Format command on the Ribbon’s Home tab
- Click Hide & Unhide, then click Hide Columns.
Hide the Marked Rows
Use similar steps to hide the marked rows. Select column A and go to the Constants that are text, then use the Hide Rows command.
Automate the Steps
If you frequently hide and unhide the columns and rows, record a macro as your perform the steps. Then, run that macro to automatically hide all the marked rows and columns.