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
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 on the right-side border of this double-line band between two columns to start unhiding hidden columns in Excel.
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.
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.
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.
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.
Now, hover the cursor over the highlighted border in the column letter menu. The arrow cursor will become the Resize or Column Resize icon.
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
The Format tool inside the Cells block of the Home tab in Excel allows you to manually unhide columns.
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.
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.
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.
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.
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.
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.
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.
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.
Then, you can right-click on the highlighted column border and choose Unhide from the context menu.
All of the three hidden columns shall unhide instantly.
Excel Unhiding Columns Using the 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.
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.
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.
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.
You can click the plus sign (+) or the last outline number, like 2 in this example to unhide all hidden columns.
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:
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 wsEnd SubIf you wish to unhide all columns in the active worksheet, use the following VBA script instead:
Sub UnhideAllColumnsInWorksheet() ' Unhide all columns in the active worksheet ActiveSheet.Columns.Hidden = FalseEnd SubSuppose, you want to unhide only a few columns in the worksheet selectively. In that case, you can use the following VBA script:
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 SubIf 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
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.
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.
Then, you’ll see another prompt where you must enter the column letters you want to unhide.
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.
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.
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:
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.
