Quickly unhide columns in Excel – A step by step guide

Hiding columns is a useful way for eliminating unnecessary clutter in your worksheet. By doing so, you can focus on the relevant data and create a clean and readable sheet. However, there may come a time when you need to unhide the hidden columns to view and work with specific information. In this tutorial, we will provide some tricks for unhiding all columns, specific columns, first column, and more.

Unhide specific columns in Excel

• By using built-in features | With a simple tool - Kutools for Excel |By dragging the mouse

Unhide all columns in Excel

• With Right-click menu | With one click by Kutools for Excel | With Format menu

Unhide the first column in Excel

• By dragging the mouse | With Right-click menu | By using built-in features

Locate the hidden columns in Excel

Delete hidden columns in Excel

• By using Inspect Document feature | With a handy tool – Kutools for Excel

Disable Unhide column option in Excel

Unhide columns not working (Issues & Solutions)

• Column width is very small | Worksheet is protected

Video: Quickly unhide columns in Excel

Unhide specific columns in Excel

If there are multiple hidden columns in your worksheet, and you just want to show only specific hidden columns instead of all of them. This section will introduce some tricks for solving it.

Unhide specific columns by using built-in features

Step 1: Select the adjacent columns for the hidden columns

Select the columns adjacent to the hidden columns. For example, to unhide Column B and Column C, I will select the columns from Column A to Column D, see screenshot:

Step 2: Apply the Unhide option

Right click the selection and choose "Unhide" from the context menu, see screenshot:

Result:

Now, the specific hidden columns (Column B and C) are displayed at once, see screenshot:

Unhide specific columns with a simple tool - Kutools for Excel

If you have Kutools for Excel installed in your Excel, with its Column List pane, all columns in the used range are listed into the pane, and the hidden columns are displayed in grey color, allowing you to identify them at a glance. To show specific hidden columns, you just need to click the icon to unhide the columns as below demo shown:

Unhide specific columns by dragging the mouse

Another quick and easy way to unhide specific columns is to use the mouse, please do as this:

Step 1: Select the columns that contain the hidden columns

Select the columns that contain the hidden columns in between. For example, to unhide Column B and Column C, I will select  the columns from Column A to Column D, see screenshot:

Step 2: Hover the cursor over the hidden columns and drag it

Put your mouse in between the column headers where the hidden columns are located. Here, I will hover the mouse between Column A and D, you will see a double-sided arrow. Then, hold the left key of the mouse and drag it to the right to expand the hidden columns. See the demo below:

Unhide all columns in Excel

If there are multiple hidden columns in your worksheet, to display all of them at once, please apply the following methods.

Unhide all columns with Right-click menu

Step 1: Select the whole worksheet

Click the small triangle in the upper left corner of the worksheet to select the entire worksheet (Or you can press Ctrl + A to select the whole sheet). See screenshot:

Step 2: Apply the Unhide option

Then, right click any one of the column headings and choose "Unhide" option from the context menu. See screenshot:

Result:

Now, all the hidden columns in the worksheet are shown at once.

One click to toggle the visibility of hidden columns

Sometimes, you may need to temporarily show the hidden columns. After viewing or editing the information, you want to hide them again for a clean look. In this case, the Kutools for Excel’s "Column list" feature also can help you toggle all hidden columns to be visible or invisible in used range, allowing you to easily show and hide them by one click. See the demo below:

Unhide all columns with Format menu

In Excel, the Format feature in the menu also can help you to unhide the hidden columns, please do with the following steps:

Step 1: Select the whole worksheet

Click the small triangle in the upper left corner of the worksheet to select the entire worksheet (Or you can press Ctrl + A to select the whole sheet).

Step 2: Go to enable the Unhide Columns feature

Select "Format" > "Hide & Unhide" > "Unhide Columns" under the "Home" tab, see screenshot:

Result:

Now, all hidden columns in the current worksheet are shown immediately.

Unhide the first column in Excel

If the first column (Column A) is not displayed in the worksheet, unhiding it can be a bit tricky because there is no straightforward way to select the hidden column. This section will talk about three tricks to solve this issue.

Unhide the first column by dragging the mouse

Even when the first column is hidden, Excel allows you to drag it to make it visible.

Step 1: Put the cursor on the left edge of Column B

Hover the cursor on the left edge of column B, and the cursor will change into a double-sided arrow, see screenshot:

Step 2: Drag the cursor to the right

Then, hold the left key of the mouse and drag it to the right to expand the hidden Column A. See the demo below:

Unhide the first column with right-click menu

You also can use the Unhide option in the right-click menu, please do as this:

Step 1: Select Column B and drag the mouse to left

1. Click on the header of Column B to select it.

2. And then drag the mouse to the left until you see the border change its color. That indicates Column A is
selected though you don't see it.

Step 2: Apply the Unhide option

Then, right click the selection and choose "Unhide" option from the context menu. See screenshot:

Result:

Now, Column A is visible while the other hidden columns remain hidden.

Unhide the first column by using built-in features

To unhide the first column of a worksheet, you can use the Name box to select the first cell (A1), and then apply the Unhide Column feature to show it.

Step 1: Enter A1 into the Name box to select it

In the Name box next to the formula bar, type A1, and then press Enter key.

Step 2: Go to enable the Unhide Columns feature

Select "Format" > "Hide & Unhide" > "Unhide Columns" under the "Home" tab, see screenshot:

Result:

Now, only Column A becomes visible while the other hidden columns remain hidden.

Locate the hidden columns in Excel

Finding hidden columns in Excel can be quite challenging, especially if you have a large number of hidden columns. In Excel, there is a simple shortcut to locate the hidden columns.

Press the Alt + ; keys simultaneously, you will see all visible cells are selected.Additionally, the column borders adjacent to the hidden columns will appear with a whitish color, making them stand out from the rest of the columns. See screenshot:

Delete hidden columns in Excel

If you need to delete the unwanted hidden columns, it could be a pain to find and delete them. Here, I’ll show you some quick ways to easily delete hidden columns in Excel.

Delete hidden columns with Inspect Document feature

In Excel, the Inspect Document feature can help you to check all hidden columns and rows in a workbook, allowing you to delete them at once.

Step 1: Go to open the Inspect Document feature

1. Open the workbook that you want to delete the hidden columns and rows, then, click "File" > "Info" > "Check for Issues" > "Inspect Document", see screenshot:
   

Step 2: Click the Inspect button to check for the hidden rows and columns

1. In the opened Document Inspector dialog, all available properties of this workbook are listed here. Make sure the "Hidden Rows and Columns" option is checked. Then, click "Inspect" button. See screenshot:
   
2. Once the search is complete, the inspection results will be displayed. Now, you can click "Remove All" button to remove all hidden rows and columns in the whole workbook.
   

Delete hidden columns with a handy tool- Kutools for Excel

With above method, all hidden rows and columns are deleted from the entire workbook, if you just want to remove hidden columns or rows from a range, active worksheet or selected sheets, the "Delete Hidden (Visible) Rows & Columns" feature of Kutools for Excel will be highly recommended to you. In addition to removing hidden rows/columns, this powerful feature also helps to remove blank rows/columns, remove visible rows/columns.

Click "Kutools" > "Delete" > "Delete Hidden (Visible) Rows & Columns" to enable the "Delete Hidden (Visible) Rows & Columns" feature.

Disable Unhide column option in Excel

Supposing, you have hidden columns with important data, such as formulas or confidential information. However, before sharing the workbook with other users, you want to ensure that no one will unhide those columns accidentally. How could you prevent others from unhiding the hidden columns in Excel?

For detailed step-by-step instructions, please view this tutorial: Protect / lock hidden columns in Excel.

Unhide columns not working (Issues & Solutions)

Occasionally, you might come across some hidden columns that cannot be unhidden using the usual methods, such as the Unhide option from the right-click menu or the Unhide Columns feature in the Format menu. There are generally two reasons why the columns won’t unhide:

• Column width is very small
• Worksheet is protected

Column width is very small

Sometimes, you may see a column hidden as shown in the below screenshot. But in reality, the column width is set to very small,which makes it appear as if the columns are hidden. When you attempt to unhide the columns using the "Unhide" option from right-click menu or "Unhide Columns" feature from the menu, the column remains hidden.

To solve this issue, please do as this:

Select the columns that contain the columns in between that you want to display. Then, put your mouse in between the column headers that have the hidden columns. Here, I will hover the mouse between Column C and E, and you will see a double-sided arrow. Then, hold the left key of the mouse and drag it to the right to expand the hidden column. See the demo below:

 Worksheet is protected

Another possible reason why you are unable to unhide columns is that the worksheet you are working on is protected. This can occur if someone has applied protection to the worksheet before sharing it with you. As a result, when you want to apply the Unhide option from the right-click menu or the Unhide Columns feature from the Format menu, these options become grey and unable to use.

In this case, to solve this issue follow the below steps:

1. Go to the "Review" tab, and click "Unprotect Sheet", see screenshot:
   
2. In the popped out "Unprotect Sheet" dialog, please enter the password if you know, see screenshot:
   
3. Then click OK to close this dialog. Now that the worksheet is unprotected, you can use any of the methods mentioned above to unhide the columns.

Related Articles: