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.
Open the Excel FileStep 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.
Select the Whole SheetStep 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.
Right-Click on the Sheet and Select UnhideThis 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.
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.
Open the Excel FileStep 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.
Select the Columns Adjacent to the Hidden ColumnGo to the Home tab, towards the cells group, in the Format button, click "Hide and Unhide" options, and unhide columns.
Unhide ColumnsStep 4: Preview the unhidden columns for clarity
Now You can see the Unhidden Column.
Preview Unhide ColumnsBy 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
Access the excel sheetStep 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.
Slide Your Cursor Left to Expand the Hidden ColumnFirst 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
Open Microsoft SpreadsheetStep 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.
Select All CellsStep 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.
Press Alt + H + O + U + LHow 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.
.webp)
Hidden ColumnFollow 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
.webp)
Go to the Developer TabStep 2: Go to Insert and Click Module
Go to Insert and Select Module, This will open the VBA Editor
.webp)
Go to Insert and Click ModuleStep 3: Write the below code in Module Editor Window
Sub Unhide_Columns_in_Excel()
Columns.EntireColumn.Hidden = False
End Sub
.webp)
Write the above CodeStep 4: Click on Run Module
To Run the module, Click on Run Module Option
.webp)
Click on Run ModuleStep 5: Preview all the Hidden Columns
You can now see all the Hidden Columns in the sheet.
.webp)
Preview all the Hidden ColumnsHow 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’ .
Open ExcelStep 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.
Choose visible cells onlyStep 3: Preview the Hidden Columns
All hidden columns are now visible.
Preview the Hidden ColumnsThis 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.
Open the Excel FileStep 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.
Make sure the Hidden Rows and Columns option is checked in the Dialog BoxStep 4: View Results
Once the inspection is complete, you’ll see the results showing hidden rows and columns.
View ResultsAlternative 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).
Check the Hidden Columns
Also Read:
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.
Similar Reads
How to Add a Column in Excel: Step-by-Step Guide Need to organize your data better in Excel but unsure how to add a column without disrupting your existing setup? Whether you’re working with a simple list or a complex table, inserting columns is a fundamental skill that increases productivity and keeps your data structured.This guide covers 4 easy
6 min read
How to Hide and Unhide Columns in Excel 2024: Easy Steps and Tips Knowing how to hide and unhide columns in Excel can increase your workflow, especially when working with large datasets. Instead of deleting columns, hiding them allows you to focus on essential information. In this how-to Excel article, you will learn effective methods for hide and unhide columns i
7 min read
How to Unfreeze Panes in Excel: A Step-by-Step Guide The freeze panes feature in Excel is invaluable for keeping key rows or columns visible while scrolling through large datasets. However, there are times when frozen panes can hinder flexibility, such as when you need to view your entire spreadsheet without locked rows or columns. Knowing how to unfr
4 min read
How to Freeze and Unfreeze Columns in Excel (With Step-by-Step Examples) Freezing and unfreezing columns in Excel is the perfect solution to keep key information like headers or labels visible, regardless of how far you scroll. Whether you're analyzing trends, preparing reports, or managing large datasets, the Freeze Panes feature in Excel can significantly enhance your
6 min read
How to Protect a Sheet in Excel: Step by Step Guide How to Protect a Sheet in Excel- Quick Steps Right Click on the Worksheet Tab Select Protect Sheet >>Enter Password Select the Actions you want to allow the UsersClick OkWhen working with sensitive or shared data in Excel, ensuring the security of specific sheets is essential. Excel sheet prot
9 min read