Excel: How to Hide Rows in a Table (3 Ways)

How-to basics · Updated November 22, 2026

The fastest way to hide rows in an Excel table is to select the row(s), right-click, and choose Hide (or press Ctrl+9 on Windows, Cmd+9 on Mac). For a more dynamic approach that works with filters, use the table’s built-in filter arrows to deselect the values you don’t want to see. Below, we cover both methods plus a third using the ribbon, and explain the differences between hiding rows in a formatted Excel Table versus a plain range.

What “Hiding” Means in Excel (and What It Doesn’t)

Hiding a row in Excel does not delete data. The row still exists, and its values remain in the worksheet; it is simply not displayed. Hidden rows are indicated by a thicker line between row numbers (for example, between row 3 and row 5 when row 4 is hidden). This is different from filtering, which temporarily hides rows based on criteria but also changes the visible state of the table’s filter buttons. Hiding is a manual, static action—unlike filtering, it does not update automatically when data changes.

One common misconception: hiding rows in an Excel Table (created via Ctrl+T) behaves the same as hiding rows in a normal range. The table structure stays intact, and hidden rows are simply not shown. However, if you copy a filtered table, only visible rows are copied by default—this is a key difference to remember.

Method 1: Right-Click Hide (Simplest)

  1. Select the row number(s) you want to hide. To select multiple non-adjacent rows, hold Ctrl while clicking row numbers.
  2. Right-click the selected row number(s).
  3. Choose Hide from the context menu.

To unhide, select the rows above and below the hidden area (e.g., rows 3 and 5), right-click, and choose Unhide. Keyboard shortcut: select the surrounding rows and press Ctrl+Shift+9 (Windows) or Cmd+Shift+9 (Mac).

Method 2: Keyboard Shortcut (Fastest for Power Users)

Select the row(s) you want to hide, then press Ctrl+9 (Windows) or Cmd+9 (Mac). This works identically to the right-click method. To unhide, select the rows around the hidden ones and press Ctrl+Shift+9 (Windows) or Cmd+Shift+9 (Mac).

Note: If you select an entire table and press Ctrl+9, it hides all rows in the selection—which is rarely what you want. Always select only the specific rows to hide.

Method 3: Ribbon Command (Home Tab)

Select the row(s), then go to Home > Cells > Format > Hide & Unhide > Hide Rows. This is a longer path but useful if you prefer menus. To unhide, select the surrounding rows, then use the same path and choose Unhide Rows.

On Excel for the web (browser version), the right-click method and the ribbon path both work. Keyboard shortcuts Ctrl+9 and Cmd+9 are also supported in the web version on Windows and Mac respectively.

Hiding Rows vs. Filtering a Table

If you are working with a proper Excel Table (created with Ctrl+T), you have a third option: use the filter drop-down arrows in the header row. Click the arrow on a column, uncheck the values you want to hide, and click OK. This hides rows dynamically based on the selected criteria.

Key differences:

  • Manual hide is static—rows stay hidden until you unhide them, even if data changes.
  • Filtering is dynamic—rows appear/disappear as you change filter criteria, and the filter state is saved with the table.
  • Filtering also affects other operations: if you copy a filtered table, only visible rows are copied. Manual hide does not affect copying—hidden rows are still copied unless you use Go To Special > Visible Cells Only.

For most users, filtering is the better choice when you need to temporarily focus on subsets of data, because it is easier to change and to clear.

Common Problems and How to Fix Them

Problem: You can’t unhide rows because the hidden row is at the very top (row 1). To unhide row 1, click the “Select All” button (the triangle at the intersection of row numbers and column letters), then go to Home > Format > Hide & Unhide > Unhide Rows. Alternatively, press Ctrl+Shift+9 after selecting the entire sheet.

Problem: Hiding rows breaks a chart or formula. Hidden rows are still included in formulas that reference the entire column (e.g., =SUM(A:A)). If you want to exclude hidden rows from calculations, use the SUBTOTAL function with function number 109 for SUM, or 103 for COUNTA. For example, with values in A2:A20, =SUBTOTAL(109,A2:A20) sums only visible rows. This is a common trick for dashboards.

Problem: You accidentally hid rows and can’t remember which ones. Select the entire sheet (click the triangle), then use Ctrl+Shift+9 to unhide all rows. This is a quick reset.

Hiding Rows in a Table vs. a Range: Any Difference?

Technically, no. Excel Tables are just a defined range with special features (structured references, filter buttons, banded rows). Hiding rows in a Table works exactly the same as in a normal range—the rows disappear, and the table’s structure remains. However, there is one subtlety: if you hide a row that contains a table’s total row, the total row disappears entirely, and you must unhide it to see it again. Also, when you filter a Table, the total row (if enabled) automatically adjusts to show only visible data—this does not happen with manual hide.

If you plan to hide rows frequently, consider using a Table with filters instead of manual hiding, because the filter state is easier to manage and to communicate to others who open the file.

Frequently Asked Questions

How do I hide rows based on a cell value (e.g., hide all rows where column B says “No”)? Use a filter: select the header row, go to Data > Filter (or press Ctrl+Shift+L), then click the filter arrow in column B and uncheck “No”. This hides those rows. For an automatic approach that updates when values change, you would need VBA or a helper column with a formula and then filter on that column.

Can I hide rows in Excel for Mac the same way? Yes. Right-click works, and the keyboard shortcuts are Cmd+9 to hide and Cmd+Shift+9 to unhide. The ribbon path is the same (Home tab > Format > Hide & Unhide).

Why does my hidden row still show in a printout? By default, hidden rows are not printed. If they appear, you likely have “Print” set to include hidden rows—check Page Layout > Print Titles > Sheet and ensure “Row and column headings” is unchecked, but more importantly, check that the row is truly hidden (look for the thicker line). If it’s filtered out, it won’t print either. If you see a gap, you may have accidentally set a print area that includes the hidden row.

How do I hide rows that are empty? Select the data range, press F5 (Go To), click Special, choose Blanks, then click OK. This selects all empty cells in the range. Then press Ctrl+9 to hide those rows. Note: this hides rows where any selected cell is blank—if you have a row with some data and one blank cell, it will hide the whole row. To hide only rows where all cells are blank, use a helper column with =COUNTA(A2:C2)=0, filter on that column, and hide the filtered rows.

Is there a way to hide rows without affecting the row numbers? No—when you hide a row, the row numbers skip (e.g., 1,2,4). This is normal. If you want to “remove” rows visually without skipping numbers, you would need to group them (using Data > Outline > Group) and then collapse the group, which shows a plus/minus button instead of a gap.

Does hiding rows affect formulas like VLOOKUP? No. Hidden rows are still there, so VLOOKUP, INDEX/MATCH, and other functions will still find values in hidden rows. Only the SUBTOTAL function (with certain function numbers) and AGGREGATE (with option 5 or 7) can ignore hidden rows. If you need to exclude hidden rows from a lookup, you must use a more complex array formula or a helper column.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.