Hiding Columns and Rows in Excel

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


Make your worksheets look more compact and focused by hiding unwanted columns or rows.

Hidden rows/columns still work in calculations, they can be referenced in formulas etc.

It’s pretty straightforward.  Select the columns or rows, right-click and choose Hide.

hiding columns and rows in excel microsoft office 31980 - Hiding Columns and Rows in Excel

Keyboard shortcuts

Type Control + 9  to hide a row.

Control + 0 to hide a column

More on unhiding below but for the moment …: to unhide just add the Shift key:

Control + Shift + 9 – unhides a row

Control + Shift + 0 – unhides a column.

Hide Rows or Hide Columns

Go to Home | Cells | Format and select Hide & Unhide | Hide Rows.

hiding columns and rows in excel microsoft office 31981 - Hiding Columns and Rows in Excel

Now the month and total columns appear beside each other.

hiding columns and rows in excel microsoft office 31982 - Hiding Columns and Rows in Excel

One reason to hide a row or column is so you can take a quick screen image of a summary without the intervening columns.

Finding Hidden columns/rows

Hidden rows or columns have a double line in the row/column list

hiding columns and rows in excel microsoft office 31983 - Hiding Columns and Rows in Excel

That indicates one of more hidden row or column.

Finding hidden the first row

If we have hidden the first row, we can’t click on the rows on either side to unhide it, so what do we do? In this case, we just need to click the letter at the top of any column to select that column, then use either the first or third methods above – type Control+Shift+9 or use the Format button on the ribbon. The second, right-click method does not seem to work in this case.

Copying hidden columns or rows

Watch out when selecting across hidden rows or columns, what you select will INCLUDE the hidden elements.

Here’s a selection which includes hidden columns B to F

hiding columns and rows in excel microsoft office 31984 - Hiding Columns and Rows in Excel

Copy that selection to a new location and you get everything, including the hidden parts made visible again.

hiding columns and rows in excel microsoft office 31985 - Hiding Columns and Rows in Excel

There’s no Paste Special option to workaround that.  All you can do is delete or re-hide the unwated

Hidden isn’t Private

Keep in mind that Hidden Excel elements like rows, columns, cells etc are NOT private or secure.

Don’t use Hidden to keep confidential info away from prying eyes.

The Office Document Inspector will warn you about Hidden columns and rows.

Unhide

To unhide select the columns/rows both sides of the hidden ones, right-click and choose unhide.

Hit Control+Shift+9 to unhide a row (Note the addition of the Shift key.)

Hit Control+Shift+0 to unhide a column

Go to Home | Cells | Format and select Hide & Unhide | Unhide Rows.

Not sure where the hidden rows/columns are?

You don’t have to go hunting through your worksheet to find all the hidden rows and columns. If you just want to unhide everything, regardless of what or where it is, just type Control+A to select the whole worksheet.

Now the rows/columns before and after all of your hidden ones are all selected, so you can then use any of the methods above to unhide everything. (This method will also unhide the first row/column.)


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.