How to unhide and show hidden columns in Excel

How to unhide all columns in Excel

Whether you have one or several hidden columns in your table, you can easily display them all at once using the Excel Unhide option.

1. Click on a small triangle in the upper-left corner of your table to select the entire worksheet.
   

   Tip. You can also press the keyboard shortcut Ctrl+A several times until the entire list is highlighted.

2. Now just right-click the selection and pick the Unhide option from the context menu.
   

Unhide all columns in Excel automatically with VBA macro

You'll find the macro below really helpful if you often get worksheets with hidden columns and don't want to waste your time on searching and showing them. Just add the macro and forget the unhide routine.

If you don't know VBA too well, feel free to explore its possibilities by reading our article How to insert and run macros.

How to show hidden columns that you select

If you have an Excel table where multiple columns are hidden and want to show only some of them, follow the steps below.

1. Select the columns to the left and right of the column you want to unhide. For example, to show hidden column B, select columns A and C.
   
2. Go to the Home tab > Cells group, and click Format > Hide & Unhide > Unhide columns.
   

Or you can right-click the selection and choose Unhide from the context menu, or just press the Unhide columns shortcut: Ctrl + Shift + 0

How to unhide first column in Excel

Unhiding columns in Excel may seem easy until you have several hidden columns but need to display only the left-most one. Pick one of the tricks below to unhide only the first column in your table.

Show all hidden columns in Excel via Go To Special

It can be rather difficult to find all hidden columns in a worksheet. Of course, you can review the column letters. However, it's not an option if your worksheet contains numerous, like more than 20, hidden columns. Still there is one trick to help you locate hidden columns in Excel.

1. Open your workbook and navigate to the Home tab.
2. Click on the Find & Select icon and pick the Go to Special… option from the menu list.
   
3. On the Go To Special dialog box, select the Visible cells only radio button and click OK.
   

You will see the entire visible part of the table highlighted and the column borders adjacent to the hidden columns borders will become whitish.

Check how many hidden columns there are in a workbook

If you want to check the entire workbook for hidden columns before searching for their location, the Go To Special functionality may not be the best option. You should employ Document Inspector in this case.

1. Go to File and click on the Check for issue icon. Select the Inspect Document option. This option examines your file for hidden properties and personal details.
   
2. You may see the notification to save the latest changes before using Document Inspector in order to make sure you keep the important data.
   

   Just click on the Yes or No buttons.

3. This will open the Document Inspector window with all the available properties. Make sure the Hidden Rows and Columns option is checked.
   
4. Press the Inspect button and the tool will start looking for hidden rows and columns.
5. As soon as the search is over, you'll see the inspection results.
   

This window also lets you delete hidden data if you don't trust them. Simply click Remove All.

This feature can appear helpful if you need to know if there are any hidden columns in Excel at all before you navigate to them.

Disable unhiding columns in Excel

Say, you hide some columns with important data like formulas or confidential information. Before you share the table with your colleagues you need to make sure no one will unhide the columns.

1. Click on the small Select all icon on the intersection of row numbers and column letters to select the entire table.
   
2. Right-click on the highlighted list and pick the Format Cells… option from the menu.
   
3. On the Format Cells window go to the Protection tab and unselect the Locked checkbox.
   
4. Click OK to save the changes.
5. Now select the column or columns you want to protect from being unhidden.
   

   Tip. You can select several columns by keeping the Ctrl button pressed.

6. Click on one of the highlighted columns and select the Format Cells… option again.
   
7. When you see the Format Cells window, navigate to the Protection tab and tick the Locked checkbox.
   
8. Click OK to save the changes.
9. Hide the columns: select them, right-click and pick the Hide option from the pop-up menu.
   
10. Now navigate to the Review tab and click on the Protect Sheet icon.
    
11. Make sure the checkboxes Select locked cells and Select unlocked cells are ticked. Then enter and reenter the password.
    
12. From now on, anyone who tries to unhide the columns in your Excel table will get the Unhide option disabled.
    

Now you know how to show hidden columns in your Excel worksheets. Those who prefer to keep their data unseen, can benefit from the possibility to disable the Unhide option. A helpful macro will save your time on unhiding columns every so often.