Working with hidden rows and columns are essential Excel Skills. We explore the fastest and the easiest ways to unhide rows in Excel and teach you the keyboard shortcuts and tips that can help in other parts of navigating the worlds most popular data analytics software.
How to Identify Hidden Rows
When hidden rows are present in an Excel workbook, you may not spot them at first glance. It’s only upon further inspection that you may realize that your rows don’t total, or they’re missing data.
By looking on the left side of Excel, the row count will skip numbers in the typical sequence. For Example, if row 7 and row 9 are visible that means that row 8 is currently hidden.
Excel also notates that cells are hidden when there is a small blank gap in between the sequence of row numbers.
How to Unhide Specific Rows in Excel using Right Click
The context menu is a fancy name for the options menu that pops up when you right click your mouse in Windows. When you right click on a selected list of row numbers in Excel, a menu will appear with the option to Unhide.
Note: A quick way to select specific rows is to use your mouse and select visible rows using the row numbers at the left of the Excel Sheet. Alternatively you can use the keyboard shortcut SHIFT + SPACE to select a row, and then hold SHIFT while pressing UP OR DOWN ARROW to select individual rows.
How to Unhide All Rows and Columns with Context Menu
To unhide all rows in Excel, first Select All. You can select all by either pressing CTRL + A (Twice) or clicking the triangle in the upper left corner of the Excel Sheet. You can then right click the list of rows and select Unhide.
Note: You may need to press CTRL + A twice for this method to work. The first time you press it will select the active table, then the second time it will select the entire spreadsheet.
This method will unhide all rows and all columns of an Excel Worksheet.
How to Unhide All Rows or Columns by Double Clicking
You can also unhide rows or columns in Excel by double clicking on the hidden columns or hidden rows. To do so, simply double click on the missing rows that are notated by a brief space in-between the sequence number.
Identify Hidden Rows in Excel Using Go To – Special
Sometimes its not practical to identify all of the ridden rows in an Excel worksheet visually. Often this will be the case with large data sets or highly formatted reports. In this case, we recommend using the Excel Go To Function.
From an Excel worksheet, press F5. This will bring up the Go To pop out menu. It’s a feature that lets you type in a cell name or cell reference and Excel will automatically go to it. It can be a big time saver of not having to scroll through a lot of rows or columns.
From the Go To pop up menu, click on the Special Button at the bottom left. This allows you to select visible cells only.
After selecting all visible cells, the hidden rows or hidden columns will be visible with a double line through them. This doesn’t make the hidden rows visible, but it does help you identify where there are hidden crows and columns. Then you can selectively unhide rows or unhide columns.
Why Should You Hide Rows in Excel?
There are a few reasons that you would want to hide rows or columns in Excel. Here are a few of the most common:
-
- Hide Sensitive Data
-
- Limit the View of Unused Rows
-
- Easier Formatting for Presentation
-
- Active Filters in the Excel Worksheet
When working with sensitive data, a popular method to restrict the view is to hide the rows or columns prior to locking and password protecting the sheet. There may also be times when you want to retain the full dataset for future analysis, but only need to present a selected set of rows to management.
Working with Excel Filters and Hidden Rows
If you are using data filters, Excel will automatically hide rows. If this is unintentional then you may need to change the filter to select all. This will retain the filter on the page but allow you to see all of the possible rows.
When to Use Sum vs Subtotal in Excel with Hidden Rows
The SUM function is one of the most used formulas in Excel. When you SUM it will total all numbers above it, even if some of the rows are hidden. Use the Subtotal Function if you want to only add the rows above that are currently visible.