Excel Tip of the Week: Hiding (and Unhiding) Rows, Columns, and Sheets

Today’s topic is hiding rows, columns and sheets.

Excel allows you to hide specific rows or columns and even entire sheets. This feature can be useful if you don’t want other users to see or edit important information in a spreadsheet. Hiding things is very simple as you will see in the below examples. To follow along with the examples download this spreadsheet.

Hiding rows (instructions same for PC and Mac)

1. Navigate to the sheet titled “Hiding Rows”
2. We want to hide all the “N” states so select those rows (be sure to select the entire row)
3. From the “Home” menu in the “Cells” group select “Format” then “Hide & Unhide” then “Hide Rows” (Keyboard shortcut: Alt → H → O → U → R)
4. Your screen should now look like the picture below
5. To unhide a row or rows, you’ll need to highlight the two visible rows on either side of what has been hidden. Then, from the “Home” menu in the “Cells” group select “Format” then “Hide & Unhide” then “Unhide Rows” (Keyboard shortcut: Alt → H → O → U → O)

Hiding Columns (instructions same for PC and Mac)

1. Navigate to the sheet titled “Hiding Columns”
2. We want to hide all the months that end with the letter “y” so select columns A,B, E, and G (Bonus tip! Hold down the Ctrl button to select noncontiguous columns at once.)
3. From the “Home” menu in the “Cells” group select “Format” then “Hide & Unhide” then “Hide Columns” (Keyboard shortcut: Alt → H → O → U → C)
4. Your screen should now look like the picture below
5. To unhide a column or columns, you’ll need to highlight the two visible columns on either side of what has been hidden. Then, from the “Home” menu in the “Cells” group select “Format” then “Hide & Unhide” then “Unhide Columns” (Keyboard shortcut: Alt → H → O → U → L)

 Notes on Hiding Rows and Columns

Go back to the row example and notice how the row numbers jump from 9 to 18 with a thicker gray line between the two numbers. Excel doesn’t do anything else to indicate hidden rows which is both good and bad. It is good if you don’t want people to notice the hidden rows, but it is bad if you don’t notice it yourself and you needed to. The same applies to hiding columns, it can sometimes be hard to notice.

Also, you can hide or unhide rows and columns from the right-click menu once you’ve highlighted the rows or columns you want to hide.

Hiding a Sheet

Hiding a sheet is accessed from the same menu, but is very different from hiding a row or column. The entire sheet will be hidden from view, but all the formulas linking to that sheet will still function. This is typically used when there is a table with static data (not ever going to change) that needs to be referenced, but really doesn’t need to be seen. Here is how to hide and unhide a sheet:

1. Navigate to the sheet titled “Hiding Sheets”
2. PC: From the “Home” menu in the “Cells” group select “Format” then “Hide & Unhide” then “Hide Sheet” (Keyboard shortcut: Alt → H → O → U → S )
   Mac: From the menu bar select “Format” then “Sheet” then “Hide”
3. After clicking “Hide Sheet”, Excel should have removed the sheet and taken you back to the “Hiding Columns” Sheet
4. PC: To unhide the sheet, from the “Home” menu in the “Cells” group select “Format” then “Hide & Unhide” then “Unhide Sheet” (Keyboard shortcut: Alt → H → O → U → H )
   Mac: From the menu bar select “Format” then “Sheet” then “Unhide”
5. You should see a pop-up box that looks like the picture below.
6. If multiple sheets are hidden you will see multiple sheet names in this box, however you can only unhide one sheet at a time.

Final note: You can hide a sheet by right-clicking on its name (the tabs along the bottom) and selecting “hide.” Further, you can access the unhide box by right-clicking on any worksheet name tab and selecting “unhide.”