No, Excel does not automatically show only cells with data; you must use filtering, Go To Special, or hiding methods to exclude blank cells from view. The quickest way is to select your data range, press Ctrl+Shift+L to turn on AutoFilter, then click the dropdown arrow on a column and uncheck the “(Blanks)” checkbox. This hides rows that contain empty cells in that column, leaving only rows with data visible.
Why Excel Shows Blank Cells by Default
Excel treats every cell in a worksheet as part of a giant grid, whether it contains a value, a formula, or nothing at all. When you scroll or print, Excel includes all cells that fall within the “used range”—the area from A1 to the last cell you ever edited or formatted. Even if you delete a value, the cell still exists and is considered part of the sheet. This is why blank rows or columns appear when you have data scattered across a large area. Excel has no built-in “hide empty cells” toggle because the grid model assumes you want to see the full layout. Instead, you apply a view-level filter or hide rows manually to achieve the effect.
Method 1: AutoFilter to Show Only Rows with Data
AutoFilter is the most practical way to temporarily hide blank rows without deleting anything. Here’s the exact process for Microsoft 365 on Windows:
- Select any cell inside your data range (e.g., A1).
- Press
Ctrl+Shift+L(Mac:Cmd+Shift+L) to add filter arrows to the header row. - Click the dropdown arrow on the column that you want to use for filtering—for example, column A.
- In the dropdown list, uncheck the box next to (Blanks). If you don’t see it, scroll to the bottom of the list.
- Click OK. Excel hides all rows where that column’s cell is empty.
To filter across multiple columns, repeat the process on each column. To clear all filters, press Ctrl+Shift+L again or go to Data > Clear. Note that filtering only hides rows; it does not delete data. On Excel for the web, the steps are identical, but the keyboard shortcut is not supported—use the Data tab > Filter button instead.
Method 2: Go To Special to Select and Delete Blanks
If you want to permanently remove blank cells from a range (not just hide them), use Go To Special. This is useful when you have a single column with gaps and want to compact the data upward. Steps:
- Select the range that contains blanks (e.g., A1:A100).
- Press
F5(Mac:Ctrl+G) to open the Go To dialog. - Click Special….
- Choose Blanks and click OK. Excel selects all empty cells in the range.
- Right-click any selected blank cell and choose Delete….
- In the dialog, select Shift cells up and click OK.
This shifts all non-blank cells upward, removing gaps. Be careful: if you have data in adjacent columns, this will misalign rows. Only use this when you want to remove blanks from a single column or when you understand the alignment consequences. There is no undo after you save, so consider copying the sheet first.
Method 3: Hide Rows Manually or with a Formula
For a one-off view, you can manually select blank rows and hide them. Click the row number, hold Ctrl to select multiple non-adjacent rows that are blank, then right-click and choose Hide. To unhide, select rows around the hidden ones, right-click, and choose Unhide. This works but is tedious for large datasets.
A more dynamic approach uses a helper column with a formula to flag rows that have any data. For example, with data in A2:C100, put this in D2:
=IF(COUNTA(A2:C2)>0,"Show","Hide")
Copy down to D100. Then use AutoFilter on column D to filter for “Show”. This hides rows where every cell in A:C is empty. The formula returns “Show” if at least one cell in that row has data. To filter for rows that have data in a specific column, use =IF(A2<>"","Show","Hide") instead.
Common Problems and Misconceptions
A frequent issue is that the “(Blanks)” option doesn’t appear in the filter dropdown. This happens when Excel thinks the column has no blanks because the blanks are actually spaces (a space character looks empty but isn’t). To fix, use =TRIM(A2) to remove spaces, or use Find & Replace: select the range, press Ctrl+H, leave “Find what” empty, type a single space in “Replace with”, and click Replace All. Another misconception is that filtering blanks will delete them—it only hides rows. Also, filtering on one column does not automatically hide rows that are blank in other columns; you must apply filters to each column you care about. Finally, if your data is in an Excel Table (created with Ctrl+T), the filter arrows are already there, and the “(Blanks)” checkbox works the same way.
Comparing Filtering vs. Hiding vs. Deleting
- Filtering is non-destructive, reversible, and great for analysis. It hides entire rows based on criteria. Downside: you must reapply filters after editing data.
- Hiding rows is also non-destructive but manual. It’s fine for a few rows, but impractical for large datasets.
- Deleting blanks with Go To Special permanently removes cells, shifting data up. This changes your data structure and cannot be undone after saving. Use only when you want a clean, compact column.
For most users, filtering is the best choice because it preserves the original data and can be toggled off instantly. If you need a printable report without blanks, filtering is also the cleanest method.
Using Excel Tables for Automatic Filtering
If you convert your data range to an Excel Table (select the range, press Ctrl+T), Excel adds filter arrows automatically. Tables also expand automatically when you add new rows, so the filter range stays current. To filter out blanks in a table, click the dropdown on a column and uncheck “(Blanks)”. Tables also make it easier to use structured references in formulas, like =COUNTA(Table1[Column1]). On Mac, the shortcut is Ctrl+T as well, and the web version supports tables too. One caveat: tables don’t allow you to have blank rows in the middle—they treat them as separate tables, so keep your data contiguous.
Frequently Asked Questions
How do I show only rows with data in Excel?
Use AutoFilter: select your data, press Ctrl+Shift+L, then in the dropdown of a key column, uncheck “(Blanks)”. This hides rows where that column is empty. For rows that are entirely empty, use a helper column with =COUNTA(A2:C2) and filter for values greater than 0.
Why doesn’t Excel have a “hide empty cells” option?
Because Excel is a grid-based program, not a database. Every cell exists by default. The designers intended you to use filters or manual hiding to control visibility. There is no global setting to auto-hide blanks, but you can create a custom view or use a macro to automate it.
Can I filter out blank cells in Excel without affecting other columns?
Yes, filtering hides entire rows, so if you filter on one column, rows with blanks in that column are hidden, but rows with data in other columns are also hidden if they have a blank in the filtered column. To keep rows that have data in any column, use the COUNTA helper method described above.
Does filtering delete my data?
No, filtering only hides rows. The data remains in the sheet. To permanently remove blanks, you must delete them, but that changes the data structure. Always keep a backup copy before deleting.
How do I unhide all rows after filtering?
Go to the Data tab and click Clear (the eraser icon) to remove all filters. Or press Ctrl+Shift+L to toggle off filters. If you manually hid rows, select the entire sheet (click the triangle at top-left), right-click a row number, and choose Unhide.
Is there a way to print only the cells with data?
Yes, after filtering out blanks, set the print area to the visible range. Select the visible data, go to Page Layout > Print Area > Set Print Area. Then print. Alternatively, use the “Print Active Sheets” option, but it will print all rows unless you filter first. For a more automated approach, use the SUBTOTAL function to count visible rows, but that doesn’t affect printing.
Related
- Excel Hourly Schedule Template: How to Make One
- Excel: How to Add Lines in a Cell (3 Easy Ways)
- Excel Hotel Group Jobs: Hiring, Pay, and How to Apply
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Named Ranges: Formulas That WorkYes, you can use formulas inside named ranges in Excel, but with a key distinction: a named range…
- Conditional Formatting If Statements in Excel: A GuideIn Excel, an “if statement” for conditional formatting is usually a formula-based rule that returns TRUE or FALSE;…
- Link Excel Data Validation to a Table the Right WayYes. In Excel for Microsoft 365 on Windows, the reliable way to link a dropdown list to a…
- Excel GROUPBY: Filter by Multiple Criteria in ExcelIn Excel for Microsoft 365, pass a Boolean filter array to GROUPBY’s seventh argument to include only rows…
- Extract the First Word from a Cell in Excel with a FormulaTo extract the first word from a cell in Excel, enter =LEFT(TRIM(A2),FIND(" ",TRIM(A2)&" ")-1) in an empty cell,…
- VLOOKUP Between Two Sheets in Excel: A Practical GuideTo look up a value from one Excel sheet on another, enter a VLOOKUP formula such as =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.