How To Unhide Columns In Excel

When you’re working with large datasets, you might like to hide some columns. In some cases, you have columns with data that don’t need to be changed and are only used in calculations. In instances like this, you can hide the column for a better overall experience for the user. Or maybe, some of the columns in your sheet contain sensitive data which the user doesn’t need to interact with. We’re trying to say there are many reasons for hiding or unhiding columns.

There are different situations when dealing with hidden columns in Excel, as well. For instance:

• More than one column is hidden, and you intend to unhide all of them at once
• You’re looking to unhide specific columns
• You just want to unhide the first column
• You don’t want the users to be able to unhide columns at all

So let’s see how to unhide columns in Excel. We’re going to go through all of these cases. Sometimes we’ll offer different ways for each of them. 

Unlock valuable insights with our Data Visualization and Data Analysis Services, transforming complex data into clear, actionable strategies for informed decision-making.

Unhiding a Column in Excel

The easiest way to unhide a column in Excel is as follows:

1. Highlight the range before and after the hidden column. For example, if column B is hidden, select columns A and C.
2. Right-click on the selected columns and then click Unhide

How to Unhide All Columns in Excel

Regardless of how many hidden columns you have in your worksheet, Excel provides an option to unhide all of them at once. Follow these steps to unhide all columns in Excel:

1. First, click on the triangle icon on the top-left corner of the worksheet or press Ctrl+A.
2. Then go to the “format option” in the “Cells” tab. Then click Hide & Unhide > Unhide Columns.

Unhide All Columns Using VBA Macro

Another way for unhiding columns in Excel is by using macros.  Instead of going through all the steps and unhiding the columns, you can just add this macro to the workbook:

Sub UnhideAllColumns ()

    Cells.EntireColumn.Hidden = False

EndSub

This macro will automatically unhide all the columns in the sheet.

If you’re interested in using macros for other means, you can get in touch with us for consultation.

How to Show Specific Hidden Columns

Sometimes you might have a table with many hidden columns, but you would like to show only a few of them. Follow these steps:

1. Select the columns to each side of the column you want to unhide. For example, if you want to unhide column C, select columns B and D.
2. Navigate to Home > Cells. Click on Format. Then navigate to Hide & unhide > Unhide columns. 

An alternative and faster way for doing this is selecting the columns like before and then press the shortcut keys: Ctrl + Shift + 0

Unhiding a Column Using the Go To Option

There is yet another way to unhide a specific column. Suppose that you want to unhide column A. To do this first you have to select a cell in that column.

1. Click on the Home tab. Navigate to Find & Select menu and then select Go To
2. In the new window, enter A1 in the Reference field and then press OK. This is the cell we have to select, which we talked about earlier.
3. The cell A1 is now selected, but that’s not visible to you.
4. Click on the Format menu and then navigate to Hide & Unhide > Unhide Columns.

Unhiding All Columns Using the Go To Special Option

Finding all the hidden columns can be a gruelling task. You can manually check all the columns and see which letters are missing, but obviously, that’s difficult, and it’s prone to errors.  

You can use the Go To Special option to automatically unhide columns. Just follow these steps:

1. Press Ctrl (Cmd) + A to select all the cells.
2. Click on the Find & Select option in the “Home” tab, and then select Go To Special
3. In the new window, select the Visible Cells Only option and then click OK

This will cause the borders adjacent to the hidden columns to change colour and become visible. 

How to Check the Number of Hidden Columns

If you would like to know how many columns are currently hidden on your sheet without having to unhide them, follow these steps:

1. Go to the File tab and then click on the Check of Issues option. 
2. Select Inspect Document
3. Save the changes before proceeding
4. In the Document Inspector window, select the Hidden Rows and Columns box
5. Click the Inspect button. Excel will search for hidden columns and rows, and it will generate a report.

You can follow this instruction just in the Windows version of Excel.

Boost your productivity by getting a free consultation from Excel experts, and discover tailored solutions to optimize your data management and analysis.

How to Disable Unhiding Columns in Excel

Sometimes for security purposes, you might not like users to be able to unhide the columns. It’s possible that your sheet contains sensitive data, and that’s why you would like to hide the columns. 

Let’s take a look at how to disable unhiding columns:

1. Click on the triangle icon on the top left corner of the sheet or press Ctrl + A (Cmd + A in macOS) to select everything on the sheet
2. Right-click on the highlighted area and select Format Cells
3. In the new window, go to the Protection tab and uncheck the Locked box and click OK
4. Select the columns for which you would like to disable unhiding. For example Column A and C
5. Right-click on one of the highlighted columns and select Format Cells
6. In the new window, navigate to the Protection tab and check the Locked box and click OK
7. Now you have to hide the columns you previously selected by navigating to Format > Hide & Unhide > Hide Columns.
8. Go to the Review tab and click on the Protect Sheet option
9. The boxes for Select locked cells and Select unlocked cells have to be ticked. After you have verified this, enter your password and click OK

Now, if someone tries to unhide the columns which you selected, the unhide option will fade out, and it will be disabled.