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. Show
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:
Let us begin. Method #1: Unhiding All the Hidden ColumnsThis 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 spreadsheetClick 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 columnRight-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 IconThis method works best when you want to unhide only a single column or two. Step #1: Select the flanking columnsClick 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 columnGo 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 MenuThis method is quick and easy to unhide a specific column. Step #1: Select the flanking columnsClick 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 columnRight-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. Step #3: Enter the column widthEnter 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 ColumnUnhiding 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 boxType 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 columnGo 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 ShortcutsShortcuts 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 columnsClick 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 shortcutPress 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 OptionThis 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’ iconClick 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 dataEnter 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 columnClick 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 columnGo 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…’ MethodThis 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 boxClick 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 referenceEnter any cell reference from the hidden column in the ‘Go To:’ dialog box. Click on the OK button. Step #3: Unhide the hidden columnsGo 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 DocumentsThis 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 documentThe 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 MacrosThis 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’ tabClick 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 editorIn 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 MacroThe 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 syntaxType 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 MacroClick 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. ConclusionThere 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. |