How to Unhide Columns in Excel All at Once (4 Quick Ways)

Take a look below for an example of a dataset where a number of columns are hidden. If you want to unhide columns simultaneously, you can use three quick and easy methods in Excel, or you can use the fourth more programming-intensive but versatile way.

The examples will use the Microsoft 365 version of Excel but should work on any platform and device. If any of the methods won’t work in your version, leave a comment below.

How to Unhide Columns in Excel All at Once

Read More: Hide Columns with No Data in Excel


1. Select Entire Worksheet and Use Unhide Command

You can unhide all hidden columns at once by selecting all cells and selecting the Unhide option. Follow these steps to do this:

Steps:

  • Click on the green arrow in the left-top corner of the worksheet. This will select all the rows and columns in the current sheet.

How to Unhide Columns in Excel All at Once

  • Move the mouse cursor over the column header and right-click.
  • Select the Unhide option from the context menu.

How to Unhide Columns in Excel All at Once

  • As a result, all hidden columns will appear. In the dataset, Columns D and F were hidden but have appeared now.

Unhide Columns All at Once

Read More: How to Hide Columns with Button in Excel


2. Use the Keyboard Shortcut to Unhide Columns

There is a keyboard shortcut in Excel you can use to unhide all hidden columns very easily. Follow these steps to use it:

Steps:

  • First, click on any cell of the worksheet to select it.
  • Then, use an Excel keyboard shortcut Ctrl + A to select all the cells of the active worksheet.
  • Now, hold the ALT key and press the H, O, U, and L keys one by one.
  • In some cases, you can just press the ALT key, release it, then continue with H, O, U, and L. If you go slow enough, you can see that each key will open a menu.
  • You will see all hidden columns have appeared.

Read More: How to Hide Multiple Columns in Excel


3. Apply a Different Width to All Columns

Hiding a column means making the column have zero width. So, you can change the width of all columns to unhide them. To do this, follow the steps below:

Steps:

  • Press Ctrl + A in an active sheet to select all the cells.
  • Go to the Home tab >> Format option >> Column Width. (Keyboard shortcut ALT H O W)

Imposing Column Width

  • A window named Column Width will appear. Insert a suitable value in the box and press OK.

Imposing Column Width

  • All columns will adopt the new width, including the hidden ones. Entering “0” hides all columns, so beware.

Imposing Column Width

Read More: How to Hide Columns Without Right Click in Excel


4. Use a VBA Macro to Unhide Columns

Alternatively, you can use a VBA macro code to unhide all the hidden columns at once. Here’s the sample VBA code you can use and how to apply it.

Steps:

  • Go to the top bar and click on Developer, then select the Visual Basic option from the menu. You can also use ALT + F11 if you don’t have the Developer tab added.

How to Unhide Columns in Excel All at Once

  • A window named Microsoft Visual Basic for Applications will appear. In its top menu bar, go to Insert and a menu will appear. From them select the Module option.

Using VBA Macro

  • Now, a new Module window will appear. Copy and paste the following code into the box:
Sub UnhideAllColumnsatOnce()
Cells.EntireColumn.Hidden = False
End Sub

Using VBA Macro

  • To run the code, go to the top menu, press on the Run option, and select the Run Sub/UserForm or press F5 to run the code.

Using VBA Macro

  • As a result, you will see the hidden columns have appeared in the worksheet.

hide Columns All at Once

Read More: How to Hide and Unhide Columns in Excel


Download Practice Workbook

You can download the practice workbook from here:


 


Related Articles


<< Go Back to Hide Columns | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

2 Comments
  1. I have read this whole article. I don’t see where it helps my situation.

    I have columns A & B hidden.

    How do I unhide them since I can’t select any column before column C.

    I have selected the whole spreadsheet and then held down the ALt button then the H O U L keys sequentially.

    I get no results.

    Thanks for your help with this problem!

    Take care,
    Tony

    • Dear Anthony Maviglia,
      Thank you for your comment. Sometimes keyboard shortcuts may not work in Excel for several reasons. You can check an alternative solution below.

      • If you want to select column A and B and then unhide them, first press Ctrl + G >> type A:B in the Reference box >> click on OK. This will select your hidden columns.

      • Then, go to the Home tab >> click on Format >> click on Hide & Unhide >> select Unhide Columns option.

      I hope this will help you to solve your problem. Please let us know if you have other queries.
      Regards,
      Arin Islam
      ExcelDemy.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo