How to Unhide Columns and Rows
in Excel + Hide Them (and more!)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Spreadsheets can get out of hand quickly—and when you have hundreds (or thousands) of rows and columns, it can be hard to get any work done.

Searching helps, but sometimes it’s a good idea to make the whole spreadsheet easier to look at.

That’s where hiding things comes in.

You can hide the contents of a single cell, an entire row, or an entire column. We’ll show you how to do each one – and how to unhide as well.

We’ll also go over the sometimes-frustrating (though easily solvable) problem of unhiding the first row or first column.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Want to practice hiding and unhiding cells, rows, and columns?

Download our example workbook and you can follow along!

cloud-download

BONUS: Download the Hide, Unhide Exercise File to go along with this post.

Hiding and unhiding cells

We’ll start small, with hiding the contents of a single cell.

Why would you want to hide only a single cell?

First of all, it makes a spreadsheet easier to look at, especially if you have cells with complicated contents.

You might also use it for the purposes of presentation. Keep your audience focused by hiding cell contents and revealing them when they’re needed.

Whatever the reason, here’s how you do it.

We’ll be using the first sheet in the example workbook here; feel free to open it up and follow along!

Kasper Langmann, Co-founder of Spreadsheeto

As you can see, this is an eyesore of a spreadsheet.

ugly-spreadsheet

Let’s start the clean-up process by hiding a single cell.

We’ll hide B3. First, click into B3, then press Ctrl + 1 to to open the Format Cells dialog.

You can also right-click on B3 and select Format Cells… from the context menu.

Kasper Langmann, Co-founder of Spreadsheeto
format-cells-context

You’ll see a few options running down the left side of the Format Cells menu:

format-cells-options

Select Custom at the bottom of the list, and click into the Type text box (it will probablysay “General”).

Then type three semi-colons (;;;):

three-semi-colons

Hit OK, and the contents of your cell will disappear.

hidden-cell-contents

Keep in mind that the cells contents are still there, as you can see if you look at the formula bar. They’re only prevented from displaying. This means any formulas that reference this cell will still work.

Kasper Langmann, Co-founder of Spreadsheeto

To reveal the contents of the cell, repeat the steps above and select General (or another type) from the format list.

You can also choose General from the Numbers section of the Home tab in the Ribbon:

unhide-cell

To hide the contents of multiple cells, use the process above, but select all the cells you want to hide before you start.

Hiding and unhiding rows

Hiding a single cell is great, but what if you want to hide a full row? You could use the same steps listed above to hide everything using the Format Cells menu, but there’s a much faster way.

Let’s hide row 4 to see how it works. Here’s the fast way to do it:

Click into row 4. Press Ctrl + 9.

That’s it.

You’ll see that row 4 is no longer listed:

row-4-hidden

You can also right-click on a row (make sure to select the entire row by right-clicking on the number to the left of the row) and select Hide from the context menu.

Kasper Langmann, Co-founder of Spreadsheeto
hide-row-context-menu

You can hide as many rows as you want with a single keyboard shortcut or a couple clicks—just select multiple rows instead of a single one.

Now that we’ve hidden a row, let’s take a look at unhiding it. Fortunately, it’s just as easy.

Highlight the rows on either side of the hidden row, and press Ctrl + Shift + 9. You’ll get your row back.

You can also highlight those two rows, right-click, and select Unhide:

unhide-row-context-menu

Like the data in hidden cells, the contents of hidden rows are still seen by formulas.

Try writing your own formula using the SUM function to see how it works—hide a row or two and see if the result of the formula changes.

Kasper Langmann, Co-founder of Spreadsheeto

Here’s something important to remember: this method of unhiding doesn’t work on the first row of the spreadsheet.

You can’t select the rows on either side of row 1. So what are we going to do?

Here, you can see that I’ve hidden row 1:

hidden-row-1

To select row 1, you’ll need to click into the cell selection box to the left of the formula bar and type in “A1”:

select-hidden-row-1

Now, press Ctrl + Shift + 9.

You can also use the Format menu in the Cells section of the Home tab in the ribbon:

format-menu-unhide-row

Hiding and unhiding columns

Hiding and unhiding columns is almost exactly the same as hiding and unhiding rows.

Let’s hide column B to see how it works.

Select the entire column by clicking on the B identifier above cell B1.

Then press Ctrl + 0 (zero). Your column will disappear.

You can also right-click and select Hide:

hide-column-context-menu

You’ll see that the B column is no longer listed:

hidden-b-column

To unhide, highlight the columns on either side of the hidden column and press Ctrl + Shift + 0 (zero).

Or highlight them, right-click, and select Unhide.

That’s all there is to it.

If you hide the first row, follow the same steps as listed in the previous section. Click into the cell selection box, type “A1,” and use the Format menu in the Home tab. (The keyboard shortcut isn’t as reliable as it is with hidden rows.)

Unhiding all rows and columns

If you’ve hidden a lot of rows and/or columns, it can be a pain to scroll through the entire spreadsheet to find them and unhide them individually.

There’s a better way.

First, select all the cells in the spreadsheet by pressing Ctrl + A or clicking in the space between the row and column identifiers:

click-select-all

Then use the standard methods of unhiding rows and columns.

Press Ctrl + Shift + 9 to unhide all rows or Ctrl + Shift + 0 (zero) to unhide all columns.

If this doesn’t work, then right-click on a row or column identifier and select Unhide.

Note that you need to select the identifier—you can’t just click anywhere and select Unhide using this particular method.

Kasper Langmann, Co-founder of Spreadsheeto

Wrapping things up…

When your spreadsheet starts getting difficult to look at, try hiding some cells, rows, or columns. It’ll make your document easier to scan and less difficult to work with.

And if you memorize the keyboard shortcuts, it will only take a few seconds for you to get everything cleaned up. That’s efficiency!

Did you try hiding a row or column that included a value present in a SUM function? If not, try it now!

Kasper Langmann, Co-founder of Spreadsheeto
2019-05-16T10:47:23+00:00