Need a hand to unhide columns in Excel?
There are different methods or unhiding available to you, depending on the version of Excel you are using.
I'll take you through the method that will work on any version.
Then I'll quickly cover what's on offer in the newer versions and then I'll take you through how to unhide column A or row one, which is a bit tricky.
So stick with me and I'll show you a tip on how to get them back.
How to unhide columns in Excel (in any version)
Hiding columns is actually quite easy. It's the getting them back that's a bit of a challenge!
In the example below you can see that columns B and C and H to J are missing, so of course they have been hidden.
So let's look at getting them back, or unhiding them.
You may have tried selecting a column next to the hidden column and then doing a right-click, and selecting Unhide from the shortcut menu, but had no success.
That's exactly what most people do, and sadly, it doesn’t work, which can be a little bit scary when you've hidden data and then you can't get it back again (yikes).
How to unhide columns in Excel
A lot of people may tell you just to go and select the entire worksheet before you unhide.
Let me explain why this isn’t my preferred way of unhiding.
You can select the entire worksheet simply by clicking on that little triangle in the top left corner of the worksheet
Then just do a right-click over any column letter and then select Unhide.
But just a word of warning here. When you have several columns hidden, like in the example below where columns B and C and H to J have been hidden, every column will become unhidden if you unhide this way.
So if you're working in a file you share with other people, and you have just unhidden all of their work as well, they may not be that happy with you.
So that's probably not my recommended way of unhiding. It will work, but it will actually unhide everything.
Unhiding only the columns you need
This is what I would recommend you do.
Select the columns on either side of the hidden columns.
Now do a right-click Unhide.
And the hidden columns will now be visible AND all other hidden columns remain hidden.
So that's a great way to unhide columns when you have randomly hidden columns and you want to avoid unhiding ALL of the columns.
Unhiding columns in newer versions of Excel
If you're in a newer version of Excel, like I am, and you have a really steady hand, you can try clicking and dragging on the double lines up in the Column Header bar.
Just click and drag and you will unhide each column.
How to unhide rows in Excel
You can use the same steps to unhide rows in Excel.
Just select on either side and then right-click.
And then select Unhide from the shortcut menu.
And they will once again be visible.
BTW, you can also click and drag on the double-lines if you are working in a newer version of Excel.
How to unhide column A and row 1
Let's cover a challenge that a lot of people come across, and that's where column A and row 1 are hidden and you're trying to get them back.
Pretty challenging if you can't select on either side in order to get them back ( sigh).
You could definitely click the triangle to select the entire worksheet, and do the right-click, Unhide, but of course as soon as you do that it will unhide ALL hidden columns, so if that’s not what you are wanting, it’s not ideal.
Instead, follow this little trick.
What you can do is use the Go To dialog box. You can open this on the Home tab, Find & Select, Go to Special or by pressing the F5 key on your keyboard (F5 is at the top of your keyboard).
Once the Go To dialog box is displayed type a1 into the Reference box, and then click on OK, which will take you to cell A1.
Now from the Home tab, you go over to Format and you go down to Hide & Unhide.
Now depending on what you're actually trying to unhide here, select the option you require.
And your row or column will now be visible.
BTW, if you are using a newer version of Excel and you have a very steady hand, you can try to click and drag them back to being visible again
How to locate and find hidden rows and columns
I get asked a lot, "how can I quickly find hidden columns within a worksheet"?
This is a good skill to have if you are working in shared files and you're not sure where others have hidden data. Best to be aware of it before you start making changes to the workbook.
To locate hidden rows or columns first select the entire worksheet. Clicking on the arrow in the top left to select is a great way to do this.
Now press F5 on you keyboard to open the Go To dialog box.
Click Special, Visible cells only.
Border lines will now indicate where data is hidden.
Check out the video below to see the 'finding hidden columns' steps in action. I made this video a long time ago but it's still relevant and super useful today
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
Let me help you learn how to subtract in Excel. It’s simple when you’re shown how, but read to the end to make sure you understand what happens when you multiply and subtract, or divide and subtract in the same formula. This is a tricky one, so please don’t skip over it!
Learn the BEST Excel keyboard shortcuts. Perfect for Beginners and for those who want to save time and work smarter. It doesn’t matter if I’m delivering a Stage 1 or a Stage 3 Excel training course – when I’m demonstrating to the class I whip around the screen, format, select and modify and update using shortcut
If you share Excel files outside of your organisation you’ll enjoy this post where I share with you how to remove Excel formulas and keep the results so that you can confidently send a file knowing you aren’t disclosing confidential information inside of your formulas.
Learn how to move, copy and paste information in Excel. Once you have mastered Cut, Copy and Paste you will find yourself shifting data around easily and reusing copied data, saving you the need to recreate the same information again. All Windows applications have access to the Windows Clipboard. This is a special area used for
Learn how to add colour, borders and alignment settings to your Excel data to give your worksheet a more professional look and to make it easier for others to read.
Formatting lets you change the appearance of data to control exactly how Excel will display the data on a worksheet. An example of formatting is to add dollar signs, percentage signs and decimal places to Excel data.