- Columns can be hidden in Excel to streamline the spreadsheet's appearance and protect sensitive information, keeping the data intact but out of sight.
- Look for gaps or double lines between column headers or check for missing column letters to spot hidden columns.
- Excel offers several methods to unhide columns, including using the context menu, keyboard shortcuts, the ribbon, increasing column width, and using Excel VBA. For the first column, use the Name Box and Format tab.
Among the plethora of features you get with Microsoft Excel, the ability to hide and unhide columns can often go unnoticed. This unique feature allows spreadsheet creators to make viewers focus on certain key figures and safeguard sensitive information from everyone.
With this in mind, if you are working with Excel files with hidden columns, it’s important to learn how to unhide columns in Excel to extract maximum data. So get your spreadsheets ready and let’s jump in!
Table of Contents
- How Does Hiding Columns Work in Excel?
- How to Identify Hidden Columns
- 5 Ways to Unhide Columns in Excel
- How to Unhide Column A in Excel (First Column)
- Bonus: How to Unhide Rows in Excel
- Bonus: How to Unhide Row 1 in Excel (First Row)
- Unhide All Columns and Rows in Excel
- Disable the Option to Unhide Columns in Excel
- Find Any Hidden Rows and Columns in the Workbook
- Alternative to Hiding and Unhiding Columns — Grouping
How Does Hiding Columns Work in Excel?
Including data points for calculations and summaries is common when working on Excel. While all that is important, you don’t want your viewers to see that mess. That is where hidden columns come in handy.
This feature allows you to hide information such as formulas from your view while keeping it in the spreadsheet. Hiding columns makes your spreadsheet easy to understand and enhances its overall aesthetics.
How to Identify Hidden Columns
One of the easiest ways to identify hidden columns is by looking for a double line or a gap between the top column headers. Another way to identify hidden columns is by checking for missing column letters. This gives users a visual cue that some of the columns are hidden.
5 Ways to Unhide Columns in Excel
When bringing back hidden columns to your spreadsheet, you have 5 different ways to help you get the job done. Let’s break all of them down individually:
1. Unhide Columns in Excel Using the Context Menu
This method allows you to reveal hidden columns directly from the context menu. Here’s a quick rundown to unhide columns using the context menu:
- First, identify the hidden columns you want to reveal.
- Once that’s done, select the two adjacent columns near your hidden columns. For instance, if Column G is hidden, select Columns F and H.
- Next, right-click on one of the column headers. This will open up the Context Menu. Here, select the Unhide option.
- The hidden column should appear in between the two selected columns.
2. Unhide Columns in Excel Using Keyboard Shortcuts
Another quick way to unhide columns in Excel is by using keyboard shortcuts allowing you to save the time and effort of manually going through different tabs and sections. When using shortcuts, you have two options that offer the same purpose:
- ALT + H + O + U + L
- ALT + O + C + U
Apart from a few different keys, both these combinations serve the same purpose and will reveal all hidden columns from your spreadsheet.
3. Unhide Columns in Excel Using the Ribbon
The second method to unhide columns in Excel is by using the Ribbon menu which also refers to the tools at the top of your page that help manage your data. Here’s how that’s done:
- Select the adjacent columns next to your hidden columns.
- From here, head over to the Home tab and click on the Format option in the Cells section.
- From the drop-down menu, head to the Visibility section and hover over to the Hide & Unhide tab.
- Next, click on the Unhide Columns to reveal hidden columns on your sheet.
4. Unhide Columns in Excel Using Width Increase
This method isn’t one of the most conventional ways to unhide columns and is often used by beginners as it takes up a lot of time when manually doing it. However, it’s good to have it in your mind when working with someone who doesn’t know Excel adequately.
- Identify the hidden column from the gap lines.
- Next, hover over to the rightmost line and drag it while pressing and holding the mouse button.
- As and when you do this, the hidden column should slowly start appearing. Release the mouse button when it’s properly visible.
5. Unhide Columns Using Excel VBA
Excel VBA (Virtual Basic for Applications) is a great way to automate tasks and customize certain operations. However, to use this method properly, you will need to have a basic grip on applying VBA codes. Here’s how to unhide columns using Excel VBA:
- Head over to the Developer tab from the ribbon and click on the Visual Basic option.
- Once the application opens up, click on the Insert tab and select the Module option.
- Here, you’ll have the option to type in the following commands:
If you want to unhide a specific column, type in this command:
Columns("B:B").Hidden = False
If you want to unhide a range of columns, type in this command:
Columns("B:D").Hidden = False
And that’s pretty much it. While using VBS is good for automating even the most complex tasks, we don’t suggest it for unhiding columns considering you have easier alternatives at hand.
How to Unhide Column A in Excel (First Column)
When working on spreadsheets with a hidden first column, it can be tricky to unhide it as there’s no manual way to select the A column as you can with others. To work around that, here’s how to unhide column A in Excel:
- Click on the Name Box in the top left corner of your document.
- Next, type in “A1” or any other cell name from the A column. Excel will select your cell, but you won’t be able to see the usual green box around it.
- Next, head over to the Format tab and click on the Unhide Columns button under the Visibility section.
Additionally, you can also try increasing the width of the first column by dragging the line to unhide it and bring it to your view.
Bonus: How to Unhide Rows in Excel
Just like hidden columns, hidden rows can also be identified by a double-line marker between rows. Additionally, you will also see row number labels skip the usual numerical order and appear randomly. Once you have your hidden rows identified, follow these steps:
- Select the top and bottom rows that are adjacent to your hidden row.
- Next, right-click anywhere in the selection and select Unhide from the Context menu.
While this is the most basic and simplest way to do it, you can also try out the above-mentioned steps to unhide columns and they will work the same way for hiding rows.
READ MORE: How to Calculate Standard Error in Excel? ➜
Bonus: How to Unhide Row 1 in Excel (First Row)
Similar to the A column, you can also unhide the first row to reveal important information regarding column headers. Here’s how to unhide the first row in Excel:
- Click on the Name Box at the top left corner of your document.
- Here, type in the cell name such as A1.
- Finally, head over to the Format tab and click on the Unhide Rows option from the Visibility tab.
Unhide All Columns and Rows in Excel
If your spreadsheet has several hidden columns and rows, manually unhiding them through different methods can be a hassle. However, you can make things convenient by selecting the entire spreadsheet to unhide all columns and rows.
To carry it out all you need to do is first select the entire spreadsheet by clicking on the small grey downward-facing triangle at the top left corner of your screen. Once it turns green, right-click anywhere on the column header and select the Unhide option from the context menu.
Disable the Option to Unhide Columns in Excel
If you don’t want users to unhide hidden columns, Excel also gives you the option to protect your sheet from others accessing sensitive data. With this setting enabled, anyone (including you) will not be able to unhide columns and you’ll have to enter a password to make these options accessible again.
Here’s how to disable the option to unhide columns in Excel:
- Select the entire sheet by clicking on the grey triangle icon.
- Next, right-click anywhere on the column headers to open up the context menu. Here, select the Format Cells option.
- From here, head over to the Protection tab > uncheck the Locked option and hit OK.
- Now, select the columns you want to hide and once again head to the Format Cells option but this time check the Locked box.
- Next, head over to the Review tab and click on the Protect Sheet option.
- From the menu, make sure the Select locked cells and Selected unlocked cells are checked. Now type in a password of your choice and hit OK after confirming it.
- And that’s it. The Unhide option should now be disabled for everyone.
Find Any Hidden Rows and Columns in the Workbook
Another interesting feature that comes with Excel is the built-in Workbook tool that allows users to inspect a document for all hidden rows and columns.
Here’s how to find any hidden rows and columns in the workbook:
- Click on the File option in the top left corner.
- From here, select the Info option from the sidebar and click on the Inspect Workbook option.
- This will open a small drop-down menu. Here, click on the Inspect Document option to open up the Document Inspector.
- Here, you’ll have the option to check any specific detail. Scroll down and select the Hidden Rows and Columns tab.
- As and when you click on the Inspect button, Excel will prompt you with the number of all hidden rows and columns along with the option to Remove All.
Alternative to Hiding and Unhiding Columns — Grouping
In Microsoft Excel, Hiding columns and Grouping them are two different features with different purposes. Hiding columns allows you to make certain information invisible without deleting it. On the other hand, Grouping organizes your data into collapsible sections making them useful for creating a structured and navigable layout for sheets with large datasets.
Additionally, hidden columns are hard to spot when quickly scrolling through a spreadsheet, whereas Grouping is much more distinct with a bold line above the grouped section that can be expanded or collapsed with just a click.
To use the grouping feature all you have to do is select your columns and either use the shortcut keys “ALT + SHIFT + Right Arrow” or head to the Data Ribbon and use the Group and Ungroup options. After grouping columns in Excel, you can expand or collapse the grouped section by clicking the “+” or “–” icon.
To unhide columns in Excel, you have several different ways to get the job done. Once you master this feature you can not only improve the quality of your sheets but also protect data. Additionally, if you want to make enhance your content’s visibility, you can also try using the Grouping alternative to add collapsible sections to your project.
Yes, hiding columns works similarly in both Excel and Google Sheets, with minor differences in how you access the option. The basic idea is the same in both applications.
No, hiding columns in Excel doesn’t delete your data. It just makes the columns temporarily invisible. Your data remains intact and can be easily unhidden when needed.
In Excel, there are multiple ways to select columns. If you want to select multiple non-adjacent columns, you’ll need to press and hold the CTRL key and click on the columns you want to select. Alternatively, press and hold the Shift key to select a range between two columns.
Yes, the ability to unhide columns is available in Excel on various devices, including the Excel mobile app for iOS and Android, as well as the Excel application for Mac.