Unhide Excel Column

What Is Unhide Column In Excel?

The Unhide Column feature helps the user unhide hidden columns in an Excel spreadsheet. It enables those working with Excel workbooks containing hidden columns to unhide them and make all the required data visible.

For example, we have a table that provides a company’s asset details. And the table has a few columns hidden, as depicted below.

Suppose we want to view the hidden columns. Then, we can execute the Unhide command in Excel and reveal the concealed columns, in this case, columns D and E.

How To Unhide All Columns In Excel?

You can apply the unhide Excel column function in seven different ways. Let us understand each method using an example.

Consider the table shown below. It shows the list of the world’s top finance companies and their details.

Suppose a user hides columns C:E. So, you will see the spreadsheet as depicted below.

Here is how you can unhide the hidden columns.

 1. Home Tab Of Excel Ribbon

The steps to execute unhide Excel column option from the Home tab are as follows:

Step 1: Select the columns on either side of the hidden columns.

Step 2: Go to Home > Format > Hide & Unhide > Unhide Columns.

Once you click on Unhide Columns, the hidden columns C:E will become visible.

Please Note: The hidden columns are contiguous in the above illustration, so we selected columns B:F to unhide the three columns.

But, if the spreadsheet has nonadjacent hidden columns, press the Ctrl key while selecting the columns on either side of the hidden columns. You can then perform step 2 to unhide all of them.

2. Shortcut Key

The procedure to use the unhide Excel column shortcut is:

1. Select the visible columns on either side of the hidden columns. If the hidden columns are non-contiguous, press the Ctrl key while selecting the specific columns.
2. Press the keyboard shortcuts – Alt key and keys H, O, U, and L, one at a time. It will unhide all the concealed columns in your spreadsheet.

The other unhide Excel column shortcut available to unhide columns is Ctrl + Shift + 0. However, it works for a non-numeric keyboard.

3. Context Menu

Here are the steps to apply the Unhide function from the context menu.

Step 1: Select the column headers of the visible columns on either side of the hidden columns.

Please Note: Only if you select the column headers will you get the Unhide option in the context menu.

Step 2: Right-click anywhere on the selected region to open the context menu.

Step 3: Choose the Unhide option.

The hidden columns will show up.

4. Column Width

You can also execute the unhide Excel column command from the context menu using the Column Width option.

Step 1: Select the column headers of the visible columns on either side of the hidden columns.

Please Note: If you do not click on the column header, you will be unable to unhide Excel column as you will not get the Column Width option in the context menu.

Step 2: Right-click anywhere on the selected columns to open the context menu.

Step 3: Choose the option Column Width.

Step 4: A dialog box will open wherein you have to enter the required column width, say, 11.91.

The hidden columns will show up.

Step 5: Adjust the column widths according to your requirement and unselect the columns to get the table provided at the beginning of this section.

Please Note: The two methods mentioned above will work for unhiding one or multiple adjacent columns. But suppose you want to unhide nonadjacent columns by using the unhide Excel column option from the context menu. In that case, you have to press the Ctrl key while selecting the visible column headers on either side of the concealed columns. And then, you have to right-click on any of the chosen column headers to select and execute the Unhide and Column Width functions in the context menu.

5. Go to Command

Here is another method to implement the unhide Excel column function from the Home tab. And the steps are:

Step 1: Go to Home > Find & Select > Go To.

Alternatively, you can use the shortcut key F5 to open the Go To window.

Step 2: Enter the cell reference of the hidden column range and click OK. In our example, the cell reference is C:E.

Step 3: The thick bar at the column C header gets highlighted.

You can now use any previously explained methods to unhide columns C:E.

Please Note: If the above table has non-contiguous columns, say, B and E hidden, you can follow the earlier steps. But the cell reference will be B:B,E:E.

The thick bars at the column headers of the referenced columns will get highlighted.

6. Ctrl + F (Find) Command

Suppose you do not know the cell reference. You may think you will be unable to unhide Excel column.

However, the Ctrl + F command works when you do not have the excel cell reference but remember at least one of the hidden cell’s data.

For example, you know that one of the cells in hidden columns C:E contains the data Conglomerate. Here is how you can unhide the required columns using the Find function.

Step 1: Open the Find and Replace window using the shortcut keys Ctrl + F.

Step 2: Provide the hidden cell data in the Find What box and click on Options >>.

Step 3: Choose Match entire cell contents, and click on Find Next.

You will notice the hidden cell reference with the value displayed in the Name Box and Formula Bar. Also, the thick bar appears near the cell reference in the hidden column.

Step 4: Close the dialog box, and you can apply any previously discussed methods to unhide the required columns.

7. Unhide Columns Automatically With VBA

Sometimes you will work with spreadsheets with multiple columns hidden. And implementing any of the methods mentioned above can put you in a situation of unhide Excel columns not working, thus complicating your task further.

Instead, you can use a VBA macro to automatically unhide the hidden columns without using the Go To or Find function.

Step 1: Using the shortcut keys Alt + F11, open the Visual Basic Editor.

Step 2: Go to Insert > Module.

Step 3: The Module 1 window opens. Enter the VBA code to unhide all columns and click on Run Sub, as shown below:

Code:

Sub Unhide_All_Columns()

        Cells.EntireColumn.Hidden = False

End Sub

The function name is Unhide_All_Columns, and the keywords are EntireColumn and Hidden.

Once you click on the Run Sub button, all hidden columns in your worksheet become visible in the display.

Please Note: VBA macro also works when you need to unhide specific columns. For example, if columns C and E are hidden in our example table, the code below will unhide them.

Macros ensure you do not face the issue of unhide Excel columns not working, as the codes are straightforward. Also, it is a one-time effort and does not require you to choose multiple options to search and locate the hidden columns in your spreadsheet.

How To Unhide First Column In Excel

If you have a spreadsheet with multiple hidden columns, including the left-most column, it becomes challenging to unhide column A. The reason is that you do not have an option to select the particular column to unhide it.

Here are a few ways you can unhide column A in your worksheet.

Assume you have to work with the below-depicted spreadsheet. It shows a supermarket’s stock details.

But you receive the sheet with column A and a few other columns (D and E) hidden, as depicted below.

1. Unhide Column A Using the Go To Option

You can execute the unhide Excel column command to make column A visible using the Go To option.

Step 1: Go to Home > Find & Select > Go To.

Step 2: A dialog box will open. Enter the reference as A1.

Step 3: Click on OK. Though column A remains hidden, cell A1 gets selected.

Step 4: Go to Home > Format > Hide & Unhide > Unhide Columns.

Column A will become visible now, as depicted below, while columns D:E remain hidden.

2. Unhide the First Column by Expanding It

Step 1: Choose column header B to select the entire column.

Step 2: Move the cursor towards the left of column header B till you see a double-sided arrow.

Step 3: Now, pull the mouse cursor towards the right side. It will expand the concealed column A.

Adjust the column A width to view it properly.

Please Note: You can use this technique to unhide Excel columns to the left of a visible column anywhere in your spreadsheet. However, you will have to move the cursor towards the left on the specific column header to get the double-sided arrow and drag it to expand the hidden column.

Likewise, you can also unhide the concealed columns on the right of a visible column, except that you need to get the double-sided arrow on the right side of the column header.

3. Unhide Column A by Selecting It

Applying this unhide Excel column option to make column A visible is simpler. The steps are:

Step 1: Choose column header B to select the entire column.

Step 2: Pull the cursor towards the left, and you will observe the column B header selection and color changes. It indicates the column A selection.

Step 3: You can release the cursor and navigate to the Home tab. Select Format > Hide & Unhide > Unhide Columns to unhide column A.

You can use this method to unhide Excel columns to the left and right of visible columns. It is equivalent to how you select columns on either side of a concealed column to unhide it.

Check A Workbook For Hidden Columns

Sometimes, your workbook can have multiple hidden columns. You can count them before locating them and use the unhide Excel column function in every worksheet. It will give you better clarity while unhiding columns across the workbook.

For example, you have a workbook Unhide Excel Column_Workbook with four worksheets. And all contain hidden columns. You can check for hidden columns in the workbook by following the below steps:

Step 1: Go to the File option. Choose Check for Issues > Inspect Document.

Step 2: The Document Inspector window will open. Select Hidden Rows and Columns.

Step 3: Click on Inspect. The result will be as depicted below.

Thus, the result shows that the Unhide Excel Column_Workbook has 8 hidden columns. You can then execute the unhide Excel column command in the workbook.

Please Note: If you select the Remove All option depicted in the above image, it will delete the hidden data in the workbook.

Disable The Unhide Column Option

You can have critical data in a few columns of your spreadsheet which you do not wish to share with others. However, even though you hide those columns, other users can still unhide those columns. Therefore, you should disable the unhide column option and share the Excel with stakeholders.

Below is a table image that shows students’ marks in various subjects, aggregates, and overall grades.

Here, we want to hide columns C:J and display student details and their aggregate, percentage, and grade.

We can ensure no other user executes the unhide Excel column command in this table using the following steps:

Step 1: Select the entire sheet by clicking at the top-left corner, where the row and column intersect.

Step 2: Right-click anywhere in the select region and choose the option Format Cells from the context menu.

Step 3: Go to the Protection tab in the Format Cells window. Uncheck the Locked option and click on OK.

Step 4: Now, select the column headers to ensure no one can unhide them. Go to the Format Cells window as explained in the previous steps.

Please Note: If you wish to protect non-contiguous columns, press the Ctrl key while selecting columns in this step.

Step 5: In the Protection tab of the Format Cells window, choose the Locked option and click on OK.

Step 6: Now, hide the selected columns. Right-click anywhere on the selected region and choose the Hide function from the context menu.

Please Note: If you want to hide nonadjacent columns, choose the column headers and right-click on any selected headers to access the context menu.

Step 7: Go to Home > Format > Protect Sheet.

Step 8: The Protect Sheet dialog box will open. By default, users can select locked and unlocked cells. Enter the password and click OK. Another window will open where you have to confirm your password. Click OK.

Now, when someone tries unhiding the columns you want to conceal, they will find the Unhide function disabled.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Unhide Excel Column, using various methods and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Unhide Excel Column. Here we learn to unhide first & A columns, disable the unhide option with examples & a downloadable template. You can learn more from the following articles –

• Data Table in Excel
• LEN Excel Function
• Hide Column in Excel