9 Ways to Unhide Columns in Microsoft Excel

If you’re searching the internet to learn how to unhide columns in Excel, you’ve come to the right resource!

Microsoft Excel offers many features that allow you to organize and visualize your datasets in many ways. One such feature is hiding dataset columns in Excel. You or a collaborator of the workbook might be interested in hiding helper or raw data columns so that the audience can only see clutter-free data visualizations.

However, you might also need to unhide those columns for workbook maintenance or raw data updating. Here you’ll find the skill to unhide one, select few, multiple, or all columns in Excel truly useful. Read this Excel tutorial until the end to explore the techniques you can use. Also, don’t forget to follow along with the methods as you read the article.

Excel Unhiding Columns Using a Double-Click

Double-line band for hidden columns
Double-line band for hidden columns

Usually, the border between two column header letters is a single line. However, if there’s a hidden column between two columns, you shall see a double line instead of a single-line border.

Double-click the right border of double-line
Double-click the right border of double-line

Double-click on the right-side border of this double-line band between two columns to start unhiding hidden columns in Excel.

Unhide columns in Excel using double-click
Unhide columns in Excel using double-click

As you double-click, Excel shall unhide the last column of the hidden columns group. For example, if B, C, and D columns are hidden, column D shall show up first, then column C, and finally column B.

Also, when you use this method, Excel shall autofit the column according to the lengthiest text or number of the column.

Unhide Columns by Dragging

Double-clicking the double-line band’s right border autofits column width, potentially lengthening the worksheet excessively when numerous hidden columns require unhiding. To overcome this challenge, you can use the column border dragging feature.

Locate the double-line band between two visible columns in your Excel worksheet. This is where you shall find the hidden columns of the spreadsheet.

The border you must drag
The border you must drag

Now, left-click on the right side border of the double-line band and drag the line to the right until enough portion of the column becomes visible.

Unhide columns by dragging
Unhide columns by dragging

You should’ve unhidden the last column of the hidden columns group.

If you need to unhide the next column in the line, repeat the column drag action again.

This method doesn’t autofit column width. Also, you can manually drag the column border to get the exact cell width you need, thereby saving worksheet space.

Excel Unhiding Columns Using the Find Tool

Suppose, you’re finding it challenging to locate and click on the double-line band to unhide columns by following the methods mentioned so far. No worries! You can make Excel select the hidden column or column groups for you. For this purpose, you’ll be using the Find and Replace tool of Excel.

Find and Replace
Find and Replace

Press Ctrl + F to bring up the Find and Replace tool.

In the Find what field, type the content that’s only available in the hidden column.

Click the Find All button.

The Find and Replace tool shall expand below. You shall find a single-cell reference. Click on that to select the hidden column in the column letter ribbon of the worksheet.

Resize icon

Now, hover the cursor over the highlighted border in the column letter menu. The arrow cursor will become the Resize or Column Resize icon.

Unhidden a column using Find what
Unhidden a column using Find what

Simply drag or double-click to make the hidden column visible.

If there’s more than one hidden column, you must keep unhiding until you find the column you’ve searched for in the Find and Replace tool.

Excel Unhiding Columns Using the Format Menu

Unhide Columns option
Unhide Columns option

The Format tool inside the Cells block of the Home tab in Excel allows you to manually unhide columns.

Gap in column letters
Gap in column letters

Firstly, you must locate the hidden column. You can easily do that by looking for gaps between two column letters in the column. For example, in the above worksheet, column C is hidden because there is a gap between columns B and D.

Hide and unhide
Hide and unhide

So, to unhide column C, I’d select columns B through D by clicking and dragging on the column letter ribbon.

Now, I shall press Alt + H + O + U + L to unhide column C instantly.

Unhidden column C
Unhidden column C

This method is valid for both single and multiple hidden columns within two-column letters.

For example, using the technique for columns B and D shall unhide column C.

However, performing the same action for columns B and G shall unhide C, D, E, and F.

Using Column Width Tool to Unhide Columns

When you hide a column in Excel, you’re reducing the column width to zero. So, you can use the Column Width tool to increase the hidden column’s width to make it visible again.

Highlight cells in continuation
Highlight cells in continuation

To use this technique, highlight the hidden column on your worksheet. You can do so by selecting two columns on either side of the hidden column. For example, if column C is hidden, you must select columns B and D in continuation as shown in the above screenshot.

Column width box
Column width box

Now press Alt + H + O + W to bring up the Column Width dialog box.

In the Column width field, enter a value, like 10 to accommodate 10 characters in the cells of the column.

Unhide columns using Column Width
Unhide columns using Column Width

Hit Enter to make the hidden column visible instantly.

Excel Unhiding Columns With a Right-Click

Suppose, you’re working on an Excel worksheet with hidden raw data or function columns. You want to quickly unhide a column and re-hide it after reviewing its content. In this case, you can use the right-click method.

Unhide from right click menu
Unhide from right-click menu

Highlight the hidden column indicator lines on the column letter menu on Excel and right-click.

You shall see the Unhide option in the context menu that shows.

Click Unhide to make the hidden column or columns visible.

After reviewing the contents of the column, use the Excel undo feature by pressing Ctrl + Z to rehide the column.

Excel Unhiding Columns Using Go To

You can also use the Go To tool to quickly highlight the hidden columns in your worksheet without looking for those manually in the column letter menu.

Go to dialog box
Go to dialog box

Press Ctrl + G to bring up the Go To dialog box on your screen.

In the Reference field, enter the column reference you want to unhide. For example, if you want to unhide a single column, the reference should be A:A, B:B, C:C, etc.

Alternatively, if there are multiple hidden columns, you can enter the reference for the entire group, like B:D, as used in the current example.

Hit OK to highlight the hidden column automatically.

Right click on hidden columns
Right-click on hidden columns

Then, you can right-click on the highlighted column border and choose Unhide from the context menu.

Unhide columns using Go To
Unhide columns using Go To

All of the three hidden columns shall unhide instantly.

Excel Unhiding Columns Using the Name Box

This is Name Box
This is Name Box

Another quick way to easily highlight one or more hidden columns in Excel is by using the Name Box.

Open your worksheet and navigate to the Name Box. It’s to the left of the formula bar in Excel.

Enter hidden column reference
Enter hidden column reference

Click on the Name Box field and enter the column address you want to unhide. For example, B:D.

To quickly select the hidden column group hit Enter.

Unhide columns using Name Box
Unhide columns using Name Box

You can now use any of the column unhiding methods mentioned so far to make the hidden columns visible.

Excel Unhiding Columns Using Group Control

Besides the Hide command, you can also use the Group command to hide columns in Excel. If you’ve used Group to hide columns, you can unhide those columns either using the Unhide command or using the Group control buttons.

Group controls in Excel
Group controls in Excel

The above screenshot shows you how a worksheet looks if some columns have been hidden using the Group command.

If you see any similar control buttons above the column letter menu, it indicates that someone has hidden columns by applying the Group command.

Click Plus sign to unhide columns
Click plus sign to unhide columns

You can click the plus sign (+) or the last outline number, like 2 in this example to unhide all hidden columns.

Unhide using Group control
Unhide using Group control

The above screenshot shows how a worksheet looks when you’ve made all grouped columns visible.

Excel Unhiding Columns Using Excel VBA

If you don’t want to remember all the command buttons, shortcuts, and related steps to unhide columns in Excel, you can create a VBA macro using Excel VBA. Then, you can run the script whenever you want to make hidden columns visible.

Find below a VBA script that automatically unhides all the columns in the active workbook:

VBA script unhide all columns
VBA script unhide all columns
Sub UnhideAllColumns()
    Dim ws As Worksheet

    ' Loop through each worksheet in the active workbook
    For Each ws In ActiveWorkbook.Worksheets
        ' Unhide all columns in the current worksheet
        ws.Columns.Hidden = False
    Next ws
End Sub

If you wish to unhide all columns in the active worksheet, use the following VBA script instead:

Unhide all columns in the worksheet
Unhide all columns in the worksheet
Sub UnhideAllColumnsInWorksheet()
    ' Unhide all columns in the active worksheet
    ActiveSheet.Columns.Hidden = False
End Sub

Suppose, you want to unhide only a few columns in the worksheet selectively. In that case, you can use the following VBA script:

VBA script to unhide columns selectively
VBA script to unhide columns selectively
Sub UnhideColumns()
    Dim ws As Worksheet
    Dim col As Range
    Dim sheetName As String
    Dim colLetters As String
    Dim letter As Variant
    Dim arr() As String

    ' Prompt the user to enter the sheet name
    sheetName = InputBox("Enter the name of the sheet where you want to unhide columns:")

    ' Check if the sheet exists
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "Sheet " & sheetName & " does not exist. Please check the sheet name and try again."
        Exit Sub
    End If

    ' Prompt the user to enter the column letters
    colLetters = InputBox("Enter the column letters to unhide, separated by commas (e.g., A, B, C):")

    ' Split the column letters into an array
    arr = Split(colLetters, ",")

    ' Unhide the columns
    Application.ScreenUpdating = False
    For Each letter In arr
        Set col = ws.Columns(Trim(letter))
        col.EntireColumn.Hidden = False
    Next letter
    Application.ScreenUpdating = True

    MsgBox "Columns " & colLetters & " have been unhidden in sheet " & sheetName & "."
End Sub

If you don’t know how to create a VBA macro using any of the above scripts, go through the following Excel VBA tutorial:

📒 Read More: How To Use The VBA Code You Find Online

Running a macro
Running a macro

Once you’ve created the VBA macro, press Alt + F8 to bring up the Macro dialog box. There, select the macro you want to execute and click the Run button.

Follow the onscreen instructions created by Excel.

Prompt for worksheet selection
Prompt for worksheet selection

For example, if you run the third VBA macro, Excel shall ask you to enter the name of the worksheet in a dedicated input box.

Prompt for column selection
Prompt for column selection

Then, you’ll see another prompt where you must enter the column letters you want to unhide.

Unhide columns using VBA
Unhide columns using VBA

Finally, Excel unhides the selective columns according to your input values.

⚠️ Warning: Create a backup of the workbook before running any Excel VBA macro or Office Scripts on your worksheets. You can use the Excel undo shortcut key to go back to the previous version.

Excel Unhiding Columns Using Office Scripts

If you want to automate the column unhiding process in Excel for the web, you can use Office Scripts. This feature is also available for Excel for Microsoft 365 desktop app.

Creating an Office Script
Creating an Office Script

On your Excel web or desktop app, click the Automate tab. Now, click on the New Script button inside the Scripting Tools commands block to open the Office Scripts Code Editor console.

Inside the Code Editor on the right, copy and paste the following Office Script:

function main(workbook: ExcelScript.Workbook) {
    // Get all worksheets in the workbook
    let worksheets: ExcelScript.Worksheet[] = workbook.getWorksheets();

    // Loop through each worksheet
    for (let worksheet of worksheets) {
        // Get the used range in the worksheet
        let usedRange: ExcelScript.Range = worksheet.getUsedRange();

        // Check if there is a used range in the worksheet
        if (usedRange) {
            // Get the total number of columns in the used range
            let columnCount: number = usedRange.getColumnCount();

            // Loop through each column in the used range
            for (let i = 0; i < columnCount; i++) {
                // Get the current column
                let column: ExcelScript.Range = usedRange.getColumn(i);

                // Check if the column is hidden
                if (column.getFormat().getColumnWidth() === 0) {
                    // Unhide the column by setting a non-zero width
                    column.getFormat().setColumnWidth(10);
                }
            }
        }
    }
}

Click the Save script button to save the Office Script for future use.

Unhide columns using Office Scripts
Unhide columns using Office Scripts

Now, hit the Run button to execute the script.

The Office Script shall instruct Excel to unhide all the columns in the active workbook.

Find below another Office Script that only unhides all columns in the active worksheet:

Unhide all columns in active worksheet
Unhide all columns in the active worksheet
function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet in the workbook
    let worksheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();

    // Get the used range in the worksheet
    let usedRange: ExcelScript.Range = worksheet.getUsedRange();

    // Check if there is a used range in the worksheet
    if (usedRange) {
        // Get the total number of columns in the used range
        let columnCount: number = usedRange.getColumnCount();

        // Loop through each column in the used range
        for (let i = 0; i < columnCount; i++) {
            // Get the current column
            let column: ExcelScript.Range = usedRange.getColumn(i);

            // Check if the column is hidden
            if (column.getFormat().getColumnWidth() === 0) {
                // Unhide the column by setting a non-zero width
                column.getFormat().setColumnWidth(10);
            }
        }
    }
}

📝 Note: Office Scripts is only available if you’re using a Microsoft 365 Business Standard or a better subscription plan. Also, the Microsoft domain administrator must enable the functionality for your account if you’re a part of an organization.

Conclusions

You must know how to unhide columns in Excel if you aim to become a master of this dominant data analysis and visualization tool from Microsoft 365.

I’ve shown above 9 different techniques so that you can find the right method for a specific use case.

If the article helped or you’ve got a method to share, comment below.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃