Unhide and hide columns in Excel

It can be difficult for one to focus on a spreadsheet when it contains hundreds or thousands of rows and columns. Sometimes, you may only want to present the critical component of the data in the spreadsheet which only resides in some columns or rows. One way to improve the readability of the spreadsheet is to hide some of the rows or columns that contain less critical information.

This article will walk you through how you can show or hide columns and rows and also some of the common problems faced by unhiding columns in Excel.

How to show or unhide columns and rows in Excel?

Let's use the example below, a dataset showing the different vitamin sales for the different quarters of the year 2022. 

Say if you want to perform two tasks:

- first, to hide columns B, C and D, showing only Q4 and the Total column

- second, to hide Vitamin A and Vitamin B which resides in row 3 and row 4.

I will show you three methods of how you can achieve this.

Using the context menu

To hide columns using the context menu :

Step 1: Click and highlight columns B, C, D

Step 2: Right-click and select Hide from the context menu

To unhide columns, you can perform the following :

Step 1: Click and highlight on either side of the hidden columns

Step 2: Right-click and select Unhide from the context menu

To hide rows using the context menu :

Step 1: Click and highlight rows 4 and 5

Step 2: Right-click and select Hide from the context menu

To unhide rows, you can perform the following :

Step 1: Click and highlight on either side of the hidden rows

Step 2: Right-click and select Unhide from the context menu

Using shortcut keys

To hide columns using shortcut keys :

Step 1: Click and highlight columns B, C, D

Step 2: Press Ctrl + 0 on your keyboard (at the same time)

To unhide columns using shortcut keys :

Step 1: Click and highlight columns B, C, D

Step 2: Press Ctrl + Shift + 0 on your keyboard (at the same time)

To hide rows using shortcut keys :

Step 1: Click on rows 4 and 5

Step 2: Press Ctrl + 9 on your keyboard (at the same time)

To unhide rows using shortcut keys :

Step 1: Click on rows 4 and 5

Step 2: Press Ctrl + Shift + 9 on your keyboard (at the same time)

Using the Group button on the Data tab

To hide columns using the Group button :

Step 1: Click and highlight columns B, C, D

Step 2: Click on the Group button on the Data tab

Step 3: Click on the "-" button to collapse the columns

To unhide the hidden columns, just click on the "+" button

To unhide rows using the Group button :

Step 1: Click and highlight rows 4 and 5

Step 2: Click on the Group button on the Data tab

Step 3: Click on the "-" button to collapse the rows

To unhide the hidden rows, just click on the "+" button

Hiding or unhiding all columns and rows at one go?

If you have hidden a couple of rows or columns, it can be pretty challenging to scroll through the entire spreadsheet to find them and unhide them individually. Luckily, Excel has made it easy for you. 

To hide all rows, 

Step1: Click in the space between the row and column identifiers

Step2: Hover the mouse over the row identifier, right-click and select Hide from the context menu

To hide all columns,

Step1: Click in the space between the row and column identifiers

Step2: Hover the mouse over the columns identifier, right-click and select Hide from the context menu

To unhide all rows, right-click on a row identifier and select Unhide from the context menu

To unhide all columns, right-click on a column identifier and select Unhide from the context menu

Why is unhiding columns in Excel not working at times?

Sometimes, you have overlooked clicking on the row or column identifier before right-clicking the unhide selection on the context menu.

One thing to note is that the shortcut keys shown above may not work for some versions of Windows. To work around this, you can first try to configure the input language hotkey. Take a look at this video at the top of this article to see how you can configure the input language hotkey.

If configuring the input language hotkey does not work,  try using Alt, H, O, U, and L, one key at a time to unhide all the columns.

-Alt : toggle the main Excel ribbon

-H : select the Home tab

-O : select the Format button

-U : select Hide & Unhide

-L : select Unhide Columns

Conclusion

Hope this article helps you to make your spreadsheet easier to scan and less difficult to work with by hiding some cells, rows or columns.