Skip to main content

Quickly unhide columns in Excel – A step by step guide

Author: Xiaoyang Last Modified: 2023-06-27

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

Unhide all columns in Excel

Unhide the first column in Excel

Locate the hidden columns in Excel

Delete hidden columns in Excel

Disable Unhide column option in Excel

Unhide columns not working (Issues & Solutions)


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 with 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:

Tips: You also can use the Unhide Columns feature from the Format menu, please click Home > Format > Hide & Unhide > Unhide Columns.

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 column 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:

Tips:

● For applying this feature, please download and install Kutools for Excel first, then click Kutools >
Navigation > Column List to open the Column List pane.

● In the Column List pane, you can also perform the following operations:

  • Click button to show the hidden columns, and click it again to hide them once more.
  • Click icon to hide the related column, and click icon to show the related column.
  • Click these buttons or drag the item in the pane to move the selected column to any other place.

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:

Tips:

● For applying this feature, please download and install Kutools for Excel first, then click Kutools >
Navigation > Column List to open the Column List pane.

● In the Column List pane, you can also perform the following operations:

  • Click icon to hide the related column, and click icon to show the related column.
  • Click these buttons or drag the item in the pane to move the selected column to any other place.

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 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 with 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.

Note: This feature will remove all hidden rows and columns in the whole workbook.

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.

NoteTo apply this Delete Hidden (Visible) Rows & Columns feature, please download and install Kutools for Excel first.

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

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 button. Now that the worksheet is unprotected, you can use any of the methods mentioned above to unhide the columns.

Related Articles:

  • Hide columns in Excel
  • As an Excel user, you may need to hide columns for various reasons.For example, you want to temporarily hide some columns to simplify the worksheet and make it easier to read and work with, hide columns with sensitive data, or hide some unwanted columns when printing a worksheet.
  • Checkbox to hide/unhide rows or columns
  • Supposing you need to use a checkbox to hide or unhide certain rows or columns. For example, when the Active X Control checkbox is checked, the certain rows or columns are displayed, otherwise, they will be hidden. This article shows you the method of how to use checkbox to hide/unhide rows or columns in Excel with details.
  • Hide columns based on date
  • Supposing, I have a range of data in a worksheet, now, I want to hide the columns based on a specific date automatically. For example, when I enter the date 5/16/2016 in a cell, I want to hide the columns which date is less than this specific date immediately as following screenshot shown. This article will introduce some tricks for solving it.
  • Hide columns in real-time based on user input
  • It may be a common task for most of us to hide columns in Excel, but, have you ever tried to hide columns automatically based on some specific input values? For example, when I enter the text “AA” in a cell, the column A is hidden; when I enter “BB”, the column B and C are hidden; when entering “CC”, the column D and E are hidden; when entering “DD”, the column F is hidden as below demo shown. This article will create a VBA code for solving it in Excel.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations