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.

Unhide Column in Excel Intro Example

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.

Unhide Column in Excel Intro
Key Takeaways
  • The unhide Excel column function unhides hidden columns in a spreadsheet, making them visible and the data available for use.
  • You can unhide columns using:
    • Unhide Columns option from the Home tab
    • Shortcut keys by pressing the Alt key and H, O, U, and L, one at a time.
    • Unhide function from the context menu.
    • Manually setting the Column Width.
    • Go To command
    • Find command
    • VBA code
  • Unhiding the hidden column A can be challenging. But you can unhide it by using the Go To command, expanding it, or selecting it.
  • While determining the hidden columns count in a workbook, you can disable the Unhide feature using a password.

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.

How To Unhide All Columns In Excel

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

How To Unhide All Columns In Excel Example

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.

Excel Using Ribbon1

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

Unhide Column in Excel Using Ribbon2

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

Unhide Column in Excel Using Ribbon3

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 shortcut – 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.

Unhide Column in Excel Using Context1

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.

Using Context Menu 2

Step 3: Choose the Unhide option.

Excel Using Context Menu 3

The hidden columns will show up.

Unhide Column in Excel Using Ribbon3

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.

Unhide Column in Excel Using Context1

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.

Using Context Menu 2

Step 3: Choose the option Column Width.

Excel Using Column Width

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

Excel Using Column Width1

The hidden columns will show up.

Excel Using Column Width2

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.

Unhide Column in Excel Using Goto Command 1

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.

Unhide Column in Excel Using Go To Command 2

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

Excel Using Go To Command3

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.

Using Go To Command4

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

Unhide Column in Excel Using Go To Command5

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.

Unhide Column in Excel Using Ctrl F Command 1

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

Excel Using Ctrl F Command 2

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

Excel Using Ctrl F Command 3

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.

Unhide Column in Excel Using VBA 1

Step 2: Go to Insert > Module.

Excel Using VBA 2

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

Excel Using VBA 3

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.

Unhide Column in Excel Using VBA 4

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.

How To Unhide First Column In Excel

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

How To Unhide First Column In Excel Example

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.

Excel Example 1

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

Excel Example 1.2

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

Unhide First Column In Excel Example 1.3

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

Unhide First Column In Excel Example 1.4

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

Unhide First Column In Excel Example 1.5

2. Unhide the First Column by Expanding It

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

Excel Example 2

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

Unhide First Column In Excel Example 2.2

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

Excel Example 2.3

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.

Unhide First Column In Excel Example 3.1

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.

Unhide First Column In Excel Example 3.2

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

Excel Example 3.3

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.

Excel Check Workbook 1

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

Unhide Column in Excel Check Workbook 2

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

Excel Check Workbook 3

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.

Disable Unhide Column Option.1

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.

Disable Unhide Column Option

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

Disable Unhide Column Option.2

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

Disable Unhide Column Option.3

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.

Disable Unhide Column Option.4

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.

Disable Unhide Column Option.5

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

Disable Unhide Column Option.6

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.

Disable Unhide Column Option.7

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.

Disable Unhide Column Option.8

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

Disable Unhide Column Option.9

Frequently Asked Questions (FAQs)

How do you unhide Excel columns?


There are multiple ways to unhide columns in Excel. First, one needs to select the columns to the right and left side of the hidden columns. But if the hidden columns are non-contiguous, press Ctrl and select the column headers on the left and right of each hidden column.

Then you can unhide the required columns using:

1. Unhide Columns option from the Home tab
2. Shortcut keys by pressing the Alt key and H, O, U, and L, one at a time.
3. Unhide function from the context menu.
4. Manually setting the Column Width.
5. Go To command
6. Find command
7. VBA code

How do I unhide multiple columns at once?


First, ensure you select the visible columns on either side of each column you want to unhide. If the hidden columns are nonadjacent, you will have to press Ctrl and make the selection from the column headers.

Next, right-click and choose the Unhide function from the context menu. And if the hidden columns are nonadjacent, right-click on one of the selected column headers to select the Unhide function.

What happens when you hide a column?


Hiding a column in Excel makes the column disappear from the display and hides the data from the user. On the other hand, a user finds the data easier to work with as all relevant columns get placed next to each other.

What is the shortcut key for unhide columns in Excel?


The shortcut key is pressing Alt and H, O, U, and L, one at a time.

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published.