Hiding columns is useful for many reasons, but when someone else has hidden columns in a spreadsheet, it can be a pain to unhide them.
Fortunately, there are many methods to unhide columns, but know that some methods mentioned may not work for certain situations. Therefore, you should read through them all, to know what will work for you.
This article will cover 10 methods of unhiding columns in Microsoft Excel.
10 methods of unhiding hidden columns in Excel:
- Unhiding all the hidden columns
- Unhiding specific columns using the Format icon
- Unhiding specific columns using the context menu
- Unhiding columns by changing the column width
- Unhiding the first column
- Unhiding columns using shortcuts
- Unhiding columns using the Find option
- Unhiding columns using the Go To method
- Unhiding columns by inspecting documents
- Unhiding columns using VBA Macros
Let us begin.
Method #1: Unhiding All the Hidden Columns
This method works in all situations. It doesn’t matter if you have a single hidden column or multiple hidden columns. Follow the steps below to unhide all columns in three clicks.
Step #1: Select the entire spreadsheet
Click on the small triangle in the top-left corner of the spreadsheet. This action will highlight and select the entire spreadsheet.
Step #2: Unhide the hidden column
Right-click on any cell in the spreadsheet.
This action will give you a small menu.
Select Unhide from this menu.
This action will reveal all the hidden columns in the spreadsheet.
In our example, column B now shows.
Method #2: Unhiding a Specific Column Using the Format Icon
This method works best when you want to unhide only a single column or two.
Step #1: Select the flanking columns
Click and hold the header of any column flanking the hidden column.
Next, drag the mouse over the other column header flanking the hidden column.
In our example, that will be columns A and C, as column B is hidden.
Step #2: Unhide the hidden column
Go to the Home tab and click on Format.
This action will give you a dropdown menu.
Select Hide & Unhide from this menu.
A new submenu will appear.
Finally, select Unhide Columns.
This action will show the hidden column.
Method #3: Unhiding a Specific Column Using the Context Menu
This method is quick and easy to unhide a specific column.
Step #1: Select the flanking columns
Click and hold the header of any column flanking the hidden column.
Next, drag the mouse over the other column header flanking the hidden column.
In our example, that will be columns A and C, as column B is hidden.
Step #2: Unhide the hidden column
Right-click on the selected area.
You will get a pop-up menu.
Select Unhide from the pop-up menu.
Or
Press the Unhide shortcut Ctrl + Shift + 0.
Both actions will show you the hidden column between the selected columns.
In our example, that is column B.
Method #4: Unhiding Columns by Changing the Column Width
This is a quick trick that does two things – unhide hidden columns, and resize your columns uniformly at the same time.
Step #1: Select the flanking columns
Click and hold the header of any column flanking the hidden column.
Next, drag the mouse over the other column header flanking the hidden column.
In our example, that will be columns A and C, as column B is hidden.
Step #2: Select column width
Right-click on the selected area.
You will see a pop-up menu appear.
In the pop-up menu, select Column Width.
This will open a new dialog box.
Step #3: Enter the column width
Enter any number as the new column width and click on the OK button.
This action will make the hidden column become visible.
In our example, that is column B.
Method #5: Unhiding the First Column
Unhiding the first column can be difficult when you wish to apply some of the methods already mentioned.
This is because the first column is only flanked by a single column. To unhide the first column alone, follow the steps given below.
Step #1: Type A1 in the name box
Type A1 in the name box to select the first cell in the first column.
Next, press Enter on the keyboard.
Step #2: Unhide the hidden column
Go to the Home tab in the top menu bar.
Click on the Format icon.
This action will give you a pop-up menu.
Select Hide & Unhide from the pop-up menu.
Finally, select Unhide Columns.
This action will display the hidden first column.
Method #6: Unhiding Columns Using Shortcuts
Shortcuts can make you work faster when you know how to use them. Following the steps below will let you unhide hidden columns using shortcuts, in a flash.
Step #1: Select the flanking columns
Click and hold the header of any column flanking the hidden column.
Next, drag the mouse over the other column header flanking the hidden column.
In our example, that will be columns A and C, as column B is hidden.
Step #2: Use the shortcut
Press the following keys on your keyboard, one key at a time:
ALT H O U L
This action will make all the hidden columns become visible.
In our example, that is column B.
Method #7: Unhiding Columns Using the Find Option
This method works when you know the content of a cell but can’t find the column that contains it. Follow the steps below to find and unhide such columns.
Step #1: Select the ‘Find’ icon
Click on the Home tab in the top menu bar.
Select the Find & Select icon.
Alternatively, press Ctrl+F.
This will open up the Find dialog box.
Step #2: Enter the search data
Enter the data of one cell in the hidden columns in the ‘Find what’ text box.
Next, click on the Options < < button.
Finally, check the box labeled ‘Match entire cell contents.’
Step #3: Find the hidden column
Click the Find Next button which will close the Find and Replace dialog box.
This action will locate and highlight the hidden column.
Step #4: Unhide the hidden column
Go to the Home tab and click on Format.
This action will give you a dropdown menu.
Select Hide & Unhide from this menu.
A new submenu will appear.
Finally, select Unhide Columns.
This action will show you the hidden columns.
In our example, that is column A.
Method #8: Unhiding Columns Using the ‘Go To…’ Method
This method is useful if you know the range of the cells that have been hidden. For example, if you know Column A has been hidden, use A1 as your hidden cell reference.
Step #1: Open the ‘Go To’ dialog box
Click on the Home tab in the top menu bar.
Select the Find & Select icon.
Select ‘Go To…’ from the drop-down menu.
Alternatively, press Ctrl+G.
This will open up the ‘Go to:’ dialog box.
Step #2: Enter the hidden cell reference
Enter any cell reference from the hidden column in the ‘Go To:’ dialog box.
Click on the OK button.
Step #3: Unhide the hidden columns
Go to the Home tab and click on Format.
This action will give you a dropdown menu.
Select Hide & Unhide from this menu.
A new submenu will appear.
Finally, select Unhide Columns.
This action will show you the hidden columns.
In our example, that is column A.
Method #9: Unhiding Columns by Inspecting Documents
This method serves two purposes. It allows you to know the number of hidden columns in the worksheet. You can also use it to delete all the hidden columns. Follow the steps below to use it.
Step #1: Click on ‘File’
Click on the File tab in the top menu bar to change the ribbon.
Step #2: Check the workbook using ‘Inspect Document’
A new window will open.
Click on Info.
Next, select the ‘Check for Issues’ icon.
Finally, select ‘Inspect Document’.
Step #3: Inspect the document
The new window will close and return you to the spreadsheet page.
You will see the Document Inspector pop-up window.
Click on Inspect.
This action will show you the number of hidden columns within the document.
Note: Clicking on Remove All will permanently delete the hidden columns.
Your spreadsheet will have no hidden columns.
Method #10: Unhiding Columns Using VBA Macros
This method is a little complex. However, it can be a great hack if you receive a lot of Excel spreadsheets with hidden columns. You just have to run the Macro each time to unhide all the hidden columns. Follow the steps below to write and run the Macro.
Step #1: Go to the ‘Developer’ tab
Click on the Developer tab in the top menu bar to change the ribbon.
Select Visual Basic.
A new window will open.
Step #2: Open the Macro editor
In the window that opens, you will see the name of your spreadsheet.
Right-click on the name of your worksheet.
A drop-down menu will appear.
From the drop-down menu, click Insert and select Module.
You will get a new pop-up window.
Step #3: Label the Macro
The properties window that opened allows you to label the Macro.
You will see the word Name in brackets.
Delete everything written after Name and type in the label.
In this case, we are labeling the Macro as ‘UnhideColumns.’
Step #4: Write the syntax
Type the syntax into the pop-up window.
Type the following:
Sub UnhideColumns ()
Columns.EntireColumn.Hidden = False
End Sub
Close the window when you are done with the typing.
Closing the window will return you to the Microsoft Excel spreadsheet.
Step #5: Run the Macro
Click on the Developer tab in the top menu bar to change the ribbon.
Select the ‘Macros’ icon.
This will give you a pop-up window.
Select the UnhideColumns Macro – the name you gave the Macro.
Finally, click on the Run button.
With this action, all your hidden columns will appear.
In our example, column B now shows.
Conclusion
There are at least 10 methods of unhiding hidden columns in Microsoft Excel.
Trying to remember all of them can be difficult. Stick to learning a few of them that works for you, and it will help you become efficient at finding hidden columns in any spreadsheet.