You need to quickly hide or unhide rows and columns in Excel to clean up your view. The Ctrl+9 and Ctrl+0 keyboard shortcuts provide the fastest method for this task. This article explains how these shortcuts work and the exact steps to use them. You will also learn how to troubleshoot when they do not respond as expected.
Key Takeaways: Excel Hide and Unhide Shortcuts
- Ctrl+9: Hides the selected rows in your Excel worksheet instantly.
- Ctrl+0 (zero): Hides the selected columns in your Excel worksheet instantly.
- Ctrl+Shift+9: Unhides any hidden rows within the current selection.
- Ctrl+Shift+0: Unhides any hidden columns within the current selection.
How the Hide and Unhide Shortcuts Work
The Ctrl+9 and Ctrl+0 shortcuts are built-in keyboard commands in Excel. They directly manipulate the row height or column width property to hide data from view. Hiding does not delete your data; it only changes its display. The data remains in the cells and all formulas continue to reference it correctly.
These shortcuts require you to select entire rows or columns first. You can select multiple non-adjacent rows or columns by holding the Ctrl key while clicking their headers. The unhide shortcuts, Ctrl+Shift+9 and Ctrl+Shift+0, work by selecting a range that spans the hidden area. For example, to unhide row 5, you must select rows 4 and 6.
Prerequisites for Using the Shortcuts
Your keyboard must have a numeric keypad for the Ctrl+0 shortcut to function as intended on some systems. The zero key on the top row of the main keyboard area may not work. Also, the worksheet cannot be protected with the option to format rows and columns locked. If it is, you must unprotect the sheet first under Review > Unprotect Sheet.
Steps to Hide and Unhide Rows and Columns
Follow these steps to use the keyboard shortcuts for hiding and revealing data.
- Select the target rows
Click on the row number header on the left side of the sheet. To select multiple rows, click and drag over the headers or hold Ctrl while clicking. - Hide the selected rows
Press Ctrl+9 on your keyboard. The selected rows will immediately disappear from view. A double line between row headers indicates where rows are hidden. - Select the target columns
Click on the column letter header at the top of the sheet. To select multiple columns, click and drag across the headers or hold Ctrl while clicking. - Hide the selected columns
Press Ctrl+0 (the zero key) on your keyboard. The selected columns will vanish. A double line between column headers shows the hidden columns. - Unhide rows
Select the rows above and below the hidden rows. For instance, to unhide row 5, select rows 4 and 5 and 6. Then press Ctrl+Shift+9. - Unhide columns
Select the columns to the left and right of the hidden columns. To unhide column C, select columns B and D. Then press Ctrl+Shift+0.
Using the Mouse and Menu Method
If the keyboard shortcuts are not working, you can use the ribbon menu. Select your rows or columns. Then go to the Home tab on the ribbon. In the Cells group, click Format. Hover over Hide & Unhide in the menu and choose Hide Rows, Hide Columns, Unhide Rows, or Unhide Columns.
Common Mistakes and Limitations
These shortcuts are powerful but have specific behaviors you should know.
Ctrl+0 Does Nothing or Opens a Dialog
On some Windows systems, Ctrl+0 is a system-level shortcut for zooming or another function. This can block Excel from using it. To fix this, you can change the keyboard shortcut in Windows settings or use the ribbon menu method instead. Another workaround is to use the zero key on the numeric keypad, not the number row.
Cannot Unhide the First Row or Column
If row 1 or column A is hidden, you cannot select the row or column above it to use the unhide shortcut. To fix this, click in the Name Box next to the formula bar. Type A1 and press Enter. Then go to Home > Format > Hide & Unhide > Unhide Rows or Unhide Columns.
Shortcuts Do Not Work on Protected Sheets
If your worksheet is protected, the shortcuts for formatting will be disabled. You must first remove sheet protection. Go to Review > Unprotect Sheet. You may need to enter a password if one was set.
Keyboard Shortcut vs. Mouse Method Comparison
| Item | Keyboard Shortcut (Ctrl+9 / Ctrl+0) | Mouse and Ribbon Menu |
|---|---|---|
| Speed | Instant, one key press | Slower, multiple clicks required |
| Precision | Requires correct initial selection | Same selection requirement |
| Accessibility | May conflict with system shortcuts | Always available if menu is not grayed out |
| Best for | Frequent, repetitive hiding tasks | When teaching others or shortcuts fail |
You can now quickly hide and reveal data in your sheets using Ctrl+9 and Ctrl+0. Remember to select entire rows or columns before using the shortcuts. If the shortcuts do not work, check for system conflicts or sheet protection. For more control, try using the Group feature under Data > Outline > Group to create collapsible sections.