How to Unhide and Show Hidden Columns in Excel: Step by Step Guide

There are lots of times when you need to hide certain columns on a temporary basis so you can focus on the specific data. But knowing how to unhide the hidden columns is also important if you want to work on the hidden data again. This complete guide on how to unhide hidden columns in Excel will walk you through step-by-step methods to unhide columns in Excel, helping you efficiently manage and analyze your data.

How to Unhide All Columns In Excel Basic Method (Windows and Mac)

Unhiding all columns in Excel on both Windows and Mac is quick and straightforward. Follow these steps to reveal all hidden columns in your workbook.

Step 1: Open the Excel File

Open the Excel file, for which you want to unhide all columns. The Excel file you are applying these to, should have certain hidden columns for you to understand the steps better. Try to Use these steps on Excel sheets with hidden columns.

Step 2: Select the Whole Sheet

Use Ctrl + A (For Windows) or Command + A (For Mac) or click on the triangle on the upper left corner of the sheet. Through this step you have access to the entire spreadsheet, thus allowing you to unhide, hide every column.

Step 3: Right-Click on the Sheet and Select Unhide

Right click on this selection, and from the search menu bar search "Unhide”, and choose from the dropdown.

This feature will give you the whole data in return with no specific properties of customization. It is used for the purpose of dealing with the whole data and analyzing it or making predictions based on it. The access to the data provides you with an opportunity to simplify or maintain the integrity of it.

The consistency of the data is maintained, giving the user a standard layout. Now this data can be customized according to the needs of the data and the demands of the further project. This approach is mainly used to resolve and debug the inconsistencies of the selective and customized data.

How to Unhide All Columns in Excel Using the Format Tool

You can Unhide all columns in Excel using the format tab. Follow the below steps to unhide all columns in Excel:

Step 1: Open the Excel File with Hidden Columns

Open the excel file with hidden column. Here in the given excel sheet, A is the hidden column.

Step 2: Select the Columns Adjacent to the Hidden Column

Choose the columns on the left and the right of the hidden column. Like in the given case, B should be chosen as A is the missing column.

Step 3: Go to the Home Tab, Click on Format Tab and Choose "Unhide Columns"

Go to the Home tab, towards the cells group, in the Format button, click "Hide and Unhide" options, and unhide columns.

Step 4: Preview the unhidden columns for clarity

Now You can see the Unhidden Column.

By using this approach, you have access to the data which is required for your current task or data analysis, thus helping you have a focused data management. It also provides you with an option to hide certain data so that you can still keep your confidential information confidential. Thus, this approach is better if you wish to customize the visibility of the data.

How to Unhide All Columns by Setting the Column Width

Adjusting column width is one of the simplest ways to unhide columns in Excel. Follow the below steps to Unhide all Columns in Excel at once

Step 1: Open the Excel Sheet

Open the Existing MS Excel Sheet

Step 2: Slide over the column edges and drag to increase the width.

Move the cursor to the left and drag to increase the width of the hidden column

Step 3: Hidden columns will appear

Now you can see the Hidden Column providing access to critical data.

First Columns always carry unique values, which acts as differentiators, in many spreadsheets. Thus to avoid misinterpretation, we can make use of the approach given above and unhide the first column. This is also one of the most straightforward approaches to unhide the columns and is not limited to just the first column in practicality.

How to Unhide Columns in Excel Using Keyboard Shortcut

Use the Keyboard shortcut key to Unhide columns in Excel or Follow the steps to Unhide columns in Excel without mouse:

Step 1: Open Microsoft Spreadsheet

Open existing MS Excel Sheet

Step 2: Select All Cells

Select all Cells using Ctrl + A (For Windows) or Command + A (For Mac) or You can Click on the Small Triangle icon to select all cells in the sheet.

Step 3: Press Alt + H + O + U + L to unhide all Columns

To unhide the columns using Alt+H+O+U+L. Although this is trickier than any other method we have discussed, it’s also a method which you can try.

How to Unhide all Columns Automatically with VBA Macro

Use VBA to automate the process of unhiding all hidden columns in Excel.

In the below sheet, Columns A,C,E,G are hidden.

Follow the below steps to unhide the columns using VBA:

Step 1: Go to the Developer Tab and Click on Visual Basic

Go to the Developer tab and Click on Visual Basic option

Step 2: Go to Insert and Click Module

Go to Insert and Select Module, This will open the VBA Editor

Step 3: Write the below code in Module Editor Window

Sub Unhide_Columns_in_Excel()

Columns.EntireColumn.Hidden = False

End Sub

Step 4: Click on Run Module

To Run the module, Click on Run Module Option

Step 5: Preview all the Hidden Columns

You can now see all the Hidden Columns in the sheet.

How to Show All Hidden Columns in Excel

We can show all the hidden columns in Excel using Go To Special feature.

Step 1: Open the Excel Sheet with Hidden Columns in it and Click on Go to Special

Open the excel sheet and access the Home tab. In the Find & select section of Home tab, click on the ‘Go to special’ .

Step 2: In the go to special dialog box, choose visible cells only

The dialog box can help you select cells with certain properties, like comments, constants, formulae, blanks or objects. For the purpose of this article, we’ll stick to visible cells only.

Step 3: Preview the Hidden Columns

All hidden columns are now visible.

This approach is useful if the data is huge and requires error reduction. It is capable enough to deal with specific customization in your data. The ‘Go to special’ feature contains many important tools to modify the spreadsheets, thus should be used to the best of its capacity to clean up data, analyze it, validate the data or even format it. It can also help with locating cells and filtering it. The efficiency and the precision offered by this tool can be used to our advantage for unhiding columns with specific properties, customizing it for the users.

How to Check the Number of Hidden Columns in a Workbook

To check for hidden columns in an entire workbook, use the Document Inspector instead of the Go To Special option. Follow the below steps to check how many hidden columns are there:

Step 1: Open excel file with Hidden Columns

Open your existing Excel File with hidden columns in it.

Step 2: Go to File Tab and Select Inspect Workbook

Click on the File menu in the top left corner and Select Inspect Workbook

Step 3: Make sure the Hidden Rows and Columns option is checked in the Dialog Box

In the Document Inspector window, ensure the Hidden Rows and Columns option is selected, then click Inspect.

Step 4: View Results

Once the inspection is complete, you’ll see the results showing hidden rows and columns.

Alternative Method to check the Hidden Column in Excel

Using this methods, you can quickly identify and count all hidden columns in your workbook for better data management.

Step 1: Look at the column letters

Hidden columns will appear as skipped letters (e.g., Only B and D columns are visible that means A and C are hidden).

Also Read:

• How to Hide Columns in Excel
• How to Split Cells in Excel: In 5 Easy Steps

Conclusion

Understanding how to unhide and show hidden columns in Excel is an essential skill for anyone working with spreadsheets. From using quick shortcuts to VBA macros, these techniques help you access hidden data and maintain an organized sheet. By mastering these methods, you can ensure no vital information is left unseen, streamlining your workflow and enhancing productivity.