Tricks for Hiding and Unhiding Excel Rows and Columns
By David H. Ringstrom, CPA
Hiding and unhiding rows and columns are mundane tasks that many users take for granted. However, sometimes simple tasks can trip up Excel users, like unhiding just one row or column within a hidden set. Other users don't know simple keystroke commands that can streamline hiding and unhiding columns or rows. In this article, I'll explore these techniques as well as discuss two powerful alternatives to manually hide and unhide rows and columns. I'll also discuss how to re-enable an Excel keyboard shortcut that's disabled in any operating system subsequent to Windows XP.
- Excel 2007 and later: On the Home tab, 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, and then 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.
- Click on Language within the Control Panel.
- Click Advanced Settings, and then click the Change Language Bar Hot Keys link.
- Click Change Key Sequence, select Not Assigned in the Switch Keyboard Layout section, and then click OK as needed.
Windows Vista or Windows 7:
- Click Region and Language within the Control Panel.
- Choose the Keyboards and Languages tab, and then click Change Keyboards.
- Click the Advanced Key Settings tab, and then click Change Key Sequence.
- Select Not Assigned in the Switch Keyboard Layout section, and then click OK as needed.
- No Control Panel changes are necessary.
- Excel 2007 and later: Choose Find and Select on the Home tab, and then click Go To.
- Excel 2003 and earlier: Choose Edit and then Go To.
2. 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. You can also use menu commands to display the Find dialog box:
- Excel 2007 and later: Choose Find and Select on the Home tab, and then click Find.
- Excel 2003 and earlier: Choose Edit, and then Find.
- Excel 2007 or later: 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 3, Excel adds a button 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.
- Excel 2007 and later: 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/or 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 View.