Excel does not have any formula for hiding or unhiding rows and columns. By putting our data in a table and using some simple steps, we can work ourselves through several ways to accomplish this.
Figure 1 – How to unhide columns
How to Unhide All Columns
When we have one or a group of columns in a table, the Unhide option makes it easy to show all columns in our table at once. To use this function:
- We will click on the small triangle found at the upper-left corner of our table adjacent to Column A. This will instantly select our worksheet.
Figure 2 – Select the triangle located at the upper-left segment of the table.
- We will right-click on the selection and choose the Unhide option
- Once selected, all columns hidden in our worksheet will be displayed.
Figure 3 – How to Use the Unhide Function
How to Show Specific Hidden Columns
When we have multiple columns but wish to be specific, we can show certain hidden columns. To do this, we will follow the steps:
- We will unhide Column B (Product) and C (Rep)
Figure 4 – Column B and C hidden
- We will click the home tab, select “Cells group” and then click format. We will select, “hide or unhide” and lastly “unhide columns”.
Figure 5 – Select Unhide Columns
Figure 6 – Column B and C unhidden
How to use the Go-To Option to Unhide the First Column
We can unhide the first column of our worksheet using the Go-To option even as there is nothing before the first column that can be selected.
- We select “Find & Select” in the Home tab and click on “Go to”
Figure 7 – Click on Find and Select
Figure 8 – Select the Go To option
- Once the “Go-To” dialog box opens, we enter A3 in the Reference field and select “OK”.
- We will select Cells group in the Home tab, click Format, select “Hide & Unhide” and lastly select “unhide” as in Figure 5
Figure 9 – Column A Un-hidden
How to Unhide the First Column by Expanding the Second Column to the Right
We can unhide the first column by using the second column, which in our example is Column B.
- We will highlight column B by clicking on the header
Figure 10 – Column A hidden
- We will place the cursor to the left until we see the double-sided arrow
- We will drag the mouse pointer to the right. As we move the pointer to the right, Column A will become visible.
Figure 11- Unhide option for expanding column A
How to Unhide Column A by Selecting it
- Again, we will highlight column B using the header.
- Now, we will drag the mouse pointer towards the left until we see the border lose its colors (becomes white). This means Column A is now selected, but we can’t see it yet
- Let go of the mouse cursor, go to the “Format” button on the Home tab.
- Select “Hide & unhide” and lastly, “unhide columns”
- We can now see column A.
How to Show All Hidden Columns Using Go To Special
To find all hidden columns in a worksheet with several hidden columns, we can do the following:
- We will go to the Home tab in our worksheet. We will select “Find & Select”
- We will click “Go To special”
Figure 12 – Using the Go To Special option
- In the Go To Special dialog box, we will select “Visible cells only” and select “OK”
- All visible areas will become visible but we can see some evidence of the hidden columns anywhere we have whitish lines.
Figure 13 – Go To shows all hidden columns
How to Count All Hidden Columns in a Workbook Using Inspect Document
Before searching for the locations of hidden columns, we can check for it using the Document Inspector.
- Click the “File” tab, select “Check for Issues” and select “Inspect Document”
Figure 14 – Select Inspect Document option
- We will see the Document Inspector window with a lot of options
- Select the “Hidden Rows and Columns” option and click “Inspect”
Figure 15- Select Hidden Rows and Columns
The Document Inspector will begin searching for the number of hidden columns. Once, completed, the number of hidden columns will be displayed. If we want to delete hidden data, we can do so by clicking “Remove all”
Figure 16 – Using the Document Inspector
How to Disable Unhidden Columns
When we want to hide sensitive data in excel, we can follow these steps:
- Click the “Select all” icon found at the intersection between columns letters and row numbers to highlight the entire table
- Right-click on selected list.
- Select “Format Cells”
- Open the “Protection Tab” and unselect the “locked” checkbox.
- Click “OK” to save changes
Figure 17 – Select the Protection tab to unselect the Locked box
- Now, we will select the columns we want to keep hidden.
- In one of the highlighted columns, we will select “Format cells” option again
- In the dialog box, we will go to the “Protection tab” and mark the “locked” checkbox. Select “OK”
- To hide columns, we will select Format cells, Protect Sheet option
- Next, we will check boxes for “Select Locked cells” and “Select unlocked cells” and enter a password to protect the columns.
Figure 18 – Using password protection for locked cells
Note: Protected columns can still be visible if there is an unhidden column that has a formula whose results are in the protected columns.