How to Unhide and show Hidden Columns in Excel

Microsoft Excel is a versatile spreadsheet software tool that empowers users to not only maintain and organize data but also wield its powerful array of predefined functions for in-depth analysis and insights. Excel’s capabilities extend far beyond mere data storage, it excels in ensuring precision and streamlining complex calculations. With its ability to sort and filter data as per user requirements, Excel has become a cornerstone of corporate operations, unleashing creativity and efficiency in the business world. Among the multitude of features it offers, one particularly crucial function is the art of hiding and unhiding columns. This feature grants users unprecedented control over their visual data, elevating readability and enhancing the quality of results. It enables users to spotlight vital information while safeguarding sensitive data from prying eyes. In this article, we will delve into the intricacies of hiding and unhiding columns in Excel, unlocking the potential to harness data effectively and maximize productivity.

How to Unhide All Columns In Excel

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 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

Step 1: Open the Excel File with Hidden Columns

Open the excel file with hiddens 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: Navigate to the “Home” tab, and Click on Format and Select “Hide and Unhide” 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 Unhide Columns

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

Step 1: Access the excel sheet

Step 2: Slide Your Cursor Left to Expand the Hidden Column

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

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.

Keyboard Shortcut to Unhide All Columns in Excel

Step 1: Open Microsoft Spreadsheet

Step 2: Select All Cells

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 the First Column in Excel

Step 1: Open the Excel Sheet with the First Column Hidden

Step 2: Move the cursor to the left and drag to increase the width of the hidden column.

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 Show All Hidden Columns Using Go to Special

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

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 Unhide all Columns Automatically with VBA

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, then Click on Visual Basic

Step 2: Go to Insert and Click Module

Step 3: Write the below code in Module Editor Window

Step 4: Click on Run Module

Step 5: Preview all the Hidden Columns

How to Unhide Columns in Excel for Mac

To unhide columns in Excel for Mac, follow these steps:

Step 1: Select Adjacent Columns

Click on the letter of the column to the left and the one to the right of the hidden columns. For example, if you want to unhide column C, click on columns B and D.

Step 2: Access the Context Menu

Right-click on the selected columns or Control-click if you’re using a Mac’s trackpad.

Step 3: Choose “Unhide”

In the menu that pops up, select the option that says “Unhide.”

Conclusion

Microsoft Excel’s prowess extends well beyond being a mere spreadsheet software; it’s a dynamic tool that empowers individuals and businesses alike to manage, analyze, and make sense of data efficiently. The ability to sort, filter, and, importantly, hide and unhide columns is a testament to its versatility. By mastering this feature, users can enhance data visibility, improve the quality of their reports, and safeguard sensitive information. Excel continues to be a linchpin in the corporate world, fostering creativity, precision, and productivity. As we wrap up our exploration of this essential Excel function, remember that in the realm of data, control over what you see and how you see it can be the key to unlocking insights and making informed decisions, and Excel provides the tools to do just that. So, go forth with newfound Excel expertise and use it to your advantage in harnessing the full potential of your data.

FAQs

How do you unhide columns Quickly?

To unhide rows, use the shortcut “Ctrl+Shift+9” by pressing all keys together. For columns, select the entire worksheet and press “Alt H O U L.”

How do I hide and unhide certain columns in Excel?

Step 1: Choose the two adjacent columns or rows you want to work with.

Step 2: Head to the Home tab, located in the Cells group.

Step 3: Click on “Format,” then “Hide and Unhide,” and choose either “Unhide Rows” or “Unhide Columns.”

To unveil all columns or rows in your sheet, select everything by pressing Control + A (Command + A on Mac), right-click, and select “Unhide.”

What should I do if I’ve accidentally hidden columns and can’t find them?

In this case you can use the ‘Go to special’ approach and click on the Visible cells only to unhide the columns. You can make use of the other options given in this article.

Can I unhide columns using keyboard shortcuts?

You can use ctrl + shift+ 0 shortcut to unhide selected columns.

Can I unhide multiple hidden columns at once?

Yes, you can unhide multiple hidden columns, even all columns with the help of the first approach discussed in this article.

Is this feature available in other devices as well?

Yes, you can hide/unhide columns in excel on mobile phones as well as tablets.