by Alexander Frolov, updated on
From this article, you'll learn how to unhide columns in Excel 2016 - 2007. It will teach you to show all hidden columns or just the ones you select, how to unhide first column, and more.
The possibility to hide columns in Excel is really helpful. It's possible to conceal some columns by using the Hide feature or by setting the column width to zero. If you happen to work with Excel files where some columns are hidden, you may want to know how to unhide columns in Excel to view all data.
In this post I'll share how to show hidden columns using the standard Excel Unhide option, a macro, the Go To Special functionality and Document Inspector.
Whether you have one or several hidden columns in your table, you can easily display them all at once using the Excel Unhide option.
Tip. You can also press the keyboard shortcut Ctrl+A several times until the entire list is highlighted.
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.
If you have an Excel table where multiple columns are hidden and want to show only some of them, follow the steps below.
Or you can right-click the selection and choose Unhide from the context menu, or just press the Unhide columns shortcut: Ctrl + Shift + 0
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.
Though there is nothing before column A to select, we could select cell A1 to unhide the first column. Here's how:
That's it! This will show column A and leave the other columns hidden.
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.
You will see the entire visible part of the table highlighted and the column borders adjacent to the hidden columns borders will become whitish.
Tip. You can do the same using this short path: F5>Special > Visible cells only. The shortcut funs can just press the Alt + ; (semicolon) hotkey.
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.
Just click on the Yes or No buttons.
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.
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.
Tip. You can select several columns by keeping the Ctrl button pressed.
Note. If you leave any part of the document available for editing a smart person can insert a formula in another column that will refer to your protected hidden column. For example, you hide column A, then another user types =A1 into B1, copies the formula down the column and gets all data from column A in column B.
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.
If any questions left, feel free to comment on the post using the form below. Be happy and excel in Excel!
Table of contents