Are hidden columns, rows, or cells driving you crazy in Excel?
You’re not alone.
They can be an absolute nightmare, especially when somebody else created the sheet and you’re stuck trying to wrangle it, or you might not even know that the hidden cells exist in the first place.
If you know how to unhide cells, and you know there are hidden cells there, then you’re all set – but you wouldn’t be here!
So let’s look at how to unhide cells in Excel, how to unhide rows in Excel, and how to unhide columns in Excel.
We’ll also go over some different methods that you can try since there’s usually more than one way to do something, and knowing the alternatives can help you fly through your sheets with more ease.
We’ll start with columns, and then we’ll tackle rows, and finally cells.
Some of the methods that you would use to unhide columns will also work to unhide rows.
Unhiding All Columns at Once
Sometimes, your spreadsheet will have numerous hidden columns that you’ve got to deal with.
If you don’t want to go on a scavenger hunt to track all of them down, here are a few ways to unhide every column at the same time.
This is good if you’re taking over a sheet that somebody else made and you want to get a better picture of what’s going on, especially for a more complex Excel spreadsheet.
Unhide Columns Using Format
This is the easiest way to unhide every cell all at once, and chances are this will do the trick for you, but if not, there are some other ways to accomplish this featured below.
It’s a three-step process to unhide every column in Excel at once:
- Select every cell in your worksheet by clicking on the small triangle located to the top left of the sheet.
- Use your mouse to right-click within the worksheet.
- Click the Unhide button.
It’s that easy, there’s a feature to unhide columns built right into the first layer of menus when you right-click.
In a couple of clicks, every single hidden column on your entire worksheet will now be visible.
Unhide Columns Using a Keyboard Shortcut
There’s also a handy keyboard shortcut that will unhide all of the columns for you.
Sometimes, a keyboard shortcut will work for someone, and it won’t work for somebody else, or it might work for you on some days, and not other days – we’ve all experienced finicky shortcuts, but it’s still worth a try – odds are it’ll get the job done, and if not, you can do it the manual way mentioned above.
Here’s the keyboard shortcut for unhiding columns:
- Begin by selecting any cell in your worksheet area.
- Press the control key on your keyboard, then press A A. It’s Control, then A, then A again, you need to press A twice.
- Now, press the Alt key then H O U L. That’s Alt, then H, then O, then U, then L.
Unhiding All Columns using VBA
In some cases, it may be advantageous to use VBA for this.
VBA stands for visual basic for applications, and it’s meant for power users, so if you’re more of a casual Excel user, you may want to skip this – mind you – it’s also an easy way to dip your toes in the water, so it can also be a great way to explore and learn more.
But if you’re just looking to unhide your columns here and there, so you can get on with your work, this won’t be the most practical way unless you don’t mind rolling up your sleeves and setting this up.
VBA involves running lines of code inside of a visual basic editor, and here’s the code you’ll need to execute in order to unhide your columns:
Sub UnhideColumns ()
Cells.EntireColumn.Hidden = False
Unhiding Columns That Are In Between Other Columns
Now that we’ve shown how to unhide every single column, let’s look at how to unhide columns in between your selected columns.
This is useful when you have a handful of hidden columns, but you don’t want to unhide every single one of them.
Method 1: Unhiding Columns in Between Using Your Mouse
If you’re a mouse user, some people might give you a hard time and encourage you to learn keyboard shortcuts for everything, but sometimes it’s easier to just use the mouse – no judgments!
All you have to do is to look at the top of your worksheet, where the column you want to unhide would be located if it weren’t hidden.
For example, if you want to unhide column C, then look at the line in between B and D, at the top where the column labels are:
You’ll see a little extra line at the top, in between B and D, and you can simply click that little line, and drag your mouse to the right, and column C (or whatever your hidden column is) will appear out of hiding.
Method 2: Keyboard Shortcut for Hiding Columns in Between
To accomplish this, you’ll want to select the columns on either side of the column you’re trying to unhide.
For instance, if column B is hidden, you would select A and C.
Next, press the following keys, one at a time: ALT H O U L. That’s the Alt key, then H, then O, then U, then L.
Beware A Column Width of Zero
If you’ve made it this far, and nothing has worked yet, and you’re starting to pull your hair out and you want to scream – here’s something you’ve got to try.
If your column width, for the hidden column, is set to zero then you won’t be able to see it, even if you’ve done all of the above steps.
To find out if your column width is set to zero, type the address of one of the cells from that column in the box.
For example, if you have column C hidden, and you want to unhide it, go to the name box and type C1:
If a thin line appears in the location of where C1 would be located, you’ll know that the column’s width is zero.
It’s there, it’s not hidden, you just can’t see it because it’s too small.
If that’s the case, here is how to change your column width to be wider than zero, so that you’ll be able to see and interact with it.
Click over to the Home tab, and click the Format option in the Cells group.
Next, there’s an option for Column Width:
This opens a new window, where you can view the current width and input your desired width:
How To Unhide Rows in Excel
So far, we’ve gone over columns, and dealing with rows is very similar.
You may come across hidden rows for the same reasons that you’ll encounter hidden columns, and here are a few different ways to unhide them.
How To Unhide Every Row in an Excel Spreadsheet
If you’d like to unhide every hidden row at the same time, here’s how to do just that in five simple steps.
- Start by clicking or navigating over to the Home tab.
- Click the Format button to bring up the Format options menu.
- Look for the section titled Visibility.
- Hover over the Hide and Unhide menu, without clicking, to reveal the menu nested below it.
- Select the Unhide Rows option, which will reveal every single hidden row on your entire workspace.
This method is good if someone else made the sheet, or you just can’t recall how many hidden rows you may have if you want to bring all of them to light.
Unhiding Individual Rows in Excel
- Start by highlighting the row above and the row below the row that you want to unhide.
- Next, right-click anywhere within the rows that you have selected, and then click the “Unhide” button.
- This is a very easy method because it’s featured as a top-level option in the right-click menu.
If that doesn’t work for you, here’s something else you can try…
Unhiding Individual Excel Rows With Your Mouse
As with columns, you can also use your mouse to manually click and drag to reveal hidden columns.
Simply look at the location of where the hidden rows would be, move your mouse to the left side of the worksheet where the labels are, look for the slightly thicker line where your row should be, click it, and draw it to reveal your hidden row.
It’s that simple!
How to Unhide Cells in Excel Spreadsheets
While most people are looking for information about how to hide and unhide rows and columns, occasionally somebody will reach out to ask about how to unhide cells in Excel.
Since every cell is a part of a row and a column, the way to unhide cells is the same as what you would do to unhide rows or columns, depending on which method you’ve used to hide a particular cell.
However, if you want to hide the contents of an individual cell so that you can’t see what’s in it (whether it’s a formula, data, or something else).
To accomplish this, follow these steps:
- Select the single cell or the range of cells that you would like to hide, and then click the Format menu, and then choose the Cells option.
- Click on the Number tab under the Cells option, then click Custom.
- Highlight the contents of the Type text box, and write an apostrophe there instead (‘), then click on the OK button to exit the menu.
Now, instead of seeing the previous contents of the cell, you’ll see an ‘ instead.
The ‘ is useful because it lets you know that there is hidden content in that cell, however, you could use a space instead.
The problem with using a spacebar instead of ‘ for the hidden cell’s content is that there are likely many empty-looking cells on your sheet, so you might not remember which ones are empty and which ones are hidden – this is why the apostrophe is useful.
It’s small but still gives you an instant visual queue.
If you’ve hidden a cell’s contents and you want to see them again, simply repeat the same process that you used to hide it, except this time instead of a custom view, choose one of the other options from the Number tab.
Final Thoughts on How to Unhide Columns, Rows, and Cells in Excel
Hiding and unhiding rows and columns function very similarly, however, it’s a bit different when you’re dealing with individual cells.
Unless you’re hiding an entire row or an entire column, you can obscure the data of an individual cell or an individual group of cells, but they won’t simply disappear from the sheet (Unless you hide the entire row or column that the particular cells are a part of.)
That wasn’t so bad, right?
Unhiding columns in Excel, unhiding Rows in excel, and unhiding Cells in Excel is something that you can accomplish with a number of different methods, so choose the one that suits you the best.