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.”

About Doug Midkiff

I’m really good at Excel. I’m also a Texan, which seems to be a trend among OwenBloggers these days (you can’t stop us, you can only hope to contain us). After graduating from Texas, (Hook’em) I spent four years as a financial analyst before finding my way to Owen where I’m concentrating in finance with an emphasis on real estate. I love my wife, indie coffee shops, disc golf, soccer, web comics, Google maps, urbanism, sustainability, and warm weather.
This entry was posted in Doug Midkiff '12, Excel Tip of the Week and tagged , , , . Bookmark the permalink.

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

  1. Thanks , I’ve recently been looking for info about this topic for a while and yours is the best I’ve came upon till now. However, what about the conclusion? Are you positive in regards to the source?
    Cheap Louis Vuitton trunks http://www.indianpod.com/louisvuitton/Cheap-Louis-Vuitton-trunks.html

  2. Nathan Wineinger says:

    -On the topic of the thicker gray lines between row/column numbers that denote hidden rows/columns between them. Is there a way to make these slightly thicker gray lines bolder. Its not a problem if you only have a few hidden areas to sort through but in the case of thousands of rows to sort through this could be a problem if you were to miss one.

  3. Bryan says:

    Is there a way to see a summary of which rows or columns are hidden? Sometimes I want to selectively ‘unhide’ certain row or columns rather than ‘unhidng’ all rows or columns and then have to ‘re-hide’ them. Thanks.

  4. seo says:

    Hello friends, its impressive piece of writing on the topic of educationand fully defined, keep it up all the time.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>