Tricks for hiding and unhiding Excel rows and columns

By David H. Ringstrom, CPA

Hiding and unhiding rows and columns in Excel can be a mundane task that many users take for granted. However, it can also be a time consuming procedure if carried out frequently. For instance, what if you want to unhide just one row or column within a hidden set? Many users unhide the whole set, and then rehide the other rows, but there's a better way. You can also use simple keystroke commands to hide and unhide columns or rows. In this article I'll show you the best ways to manage hidden rows and columns in your worksheets, which will include a couple of features that many Excel users have never tried.

Hiding Rows and Columns

Let's first explore the traditional approaches to hiding rows and columns. Going forward I'll only explain rows — simply replace the word Row with Column in any menu commands that I describe if you want to hide or unhide columns.

First, select the row or rows that you wish to hide or unhide, and then carry out these steps:
 

• Excel 2007: On the Home tab of the menu ribbon, choose Format in the Cells section of the ribbon, and then choose Hide & Unhide, and then either Hide Rows or Unhide Rows.
  
• Excel 2003 and earlier: Choose Format, Row from the menu, and then choose either Hide or Unhide.
  

Tip: Remember, to unhide rows you must select rows on either side of the hidden set. If you're trying to unhide rows at the top of the worksheet, click on the first visible row, and then move your mouse up to the top of the screen.

Keyboard shortcuts

Alternatively, you can select a row or rows, and then press Ctrl+9 to issue the Hide command. To unhide rows, press Ctrl+Shift+9. For columns, use Ctrl+0 (that's a zero) or Ctrl+Shift+0, respectively.

Selective Unhiding

Sometimes you may want to unhide just a single row or column. There are two ways to do so:

• If you know the row number or column letter, press the F5 key to display the Go To window shown in Figure 1, and then type the address of a single cell in the desired range. For example, type A3 for a row, or G1 for a column. Excel will select the hidden cell, and from there you can carry out the corresponding menu command or keyboard shortcut.
   
• If you don't know the exact address of the cell you're looking for, press Ctrl-F to display the Find window, and search for a word within the hidden column or row. As with the Go To command, Excel will select the hidden cell, which you can then unhide.
   

Figure 1: Use the Go To command to select a single row or column that you can then unhide.

Group Rows or Columns

Excel's Group feature is an effective alternative to manually hiding or unhiding rows and columns. Select the rows or columns you with to hide, and then carry out this command:
 

• Excel 2007: Click the Group icon in the Outline section of the Data ribbon.
   
• Excel 2003 and earlier: Choose Data, Group and Outline, and then Group.
   

As shown in Figure 2, Excel adds buttons outside the worksheet frame that you can use to toggle the hidden or visible status of rows or columns. To remove grouping, select the group, and then issue the corresponding Ungroup command, which is adjacent to the Group commands described above.

Figure 2: Click 2 to display all hidden rows or columns, or 1 to hide them again, or just click the button to toggle a single section.

Custom Views

Many Excel users overlook the Custom Views feature, which among other things, allows you to save sets of hidden rows or columns. Before you start hiding rows or columns, first create a view that displays the entire worksheet:
 

• Excel 2007: Choose Custom Views in the Workbook Views section of the View ribbon. Click Add, and then assign a name, such as All Columns. Make sure that Hidden Row, Columns, and Filter Settings is selected, and then click OK.
   
• Excel 2003 and earlier: Choose View, and then Custom Views. From there, the commands are the same as described in Excel 2007.

Next, hide rows and columns as desired, and then save a second custom view. You can now toggle between views as needed. Issue the Custom Views command, select a view from the list, and then click Show, as shown in Figure 3.

Figure 3: The Custom Views feature allows you to hide or unhide rows and columns en masse.

David Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm. Contact David at david@acctadv.com.