How to Hide Multiple Blank Rows in Excel

Excel users can efficiently manage large datasets by hiding multiple blank rows simultaneously instead of using manual methods. By utilizing the Go To Special feature to select all blank cells within a range, users can instantly hide the corresponding rows with a keyboard shortcut.

This technique is reversible and compatible with both Windows and Mac operating systems. It offers a safer alternative to deleting rows, as it preserves data while improving the visual organization of spreadsheets for reports and data analysis.

What You’ll Learn in This Guide

In this quick tutorial, you’ll learn how to:

Select all blank rows in an Excel Sheet at once
Hide multiple blank rows in Excel instantly
Save hours of manual work when dealing with large datasets
Avoid the tedious “right-click → hide” for each individual row

Tools & Requirements

Requirement	Details
Software	Microsoft Excel (all versions)
OS	Windows / Mac (works on both)
Skill Level	Beginner
Time to Implement	Under 30 seconds

The Problem — Why Manual Hiding Fails

When you have a dataset with hundreds or thousands of blank rows scattered throughout:

Manual Approach	Problem
Right-click each blank row → Hide	Extremely slow for 100+ rows
Right-click each blank row → Delete	Risky — may delete data by mistake; also slow
Filter for blanks → Delete visible	Works but changes row structure

The Solution: Use Go To Special → Blanks to select ALL blank cells at once, then Ctrl+9 to hide their entire rows instantly.

🎬 Watch the Full Tutorial

Step-by-Step Walkthrough

Step 1: Select Your Data Range

Click and drag to select the columns containing your data
In the example: Columns A through D (4 columns)

Tip: You can select entire columns (click column headers A, B, C, D) or a specific range (e.g., A1:D1000)

Step 2: Open the Go To Special Dialog

Keyboard Shortcut (Fastest):

Press F5 → Click Special…
OR Press Ctrl+G → Click Special…

Ribbon Method:

Home tab → Find & Select (far right) → Go To Special…

Step 3: Select “Blanks”

In the Go To Special dialog, select Blanks
Click OK

What happens: Excel instantly selects every blank cell within your selected range. All blank cells are now highlighted with a gray selection border.

Step 4: Hide the Blank Rows

Press Ctrl+9 (Control key + number 9)

That’s it! All rows containing blank cells are now hidden simultaneously.

Visual Proof — Before vs After

Before (with blank rows)	After (Ctrl+9)
Row 5: blank	Hidden
Row 12: blank	Hidden
Row 27: blank	Hidden
Row 45: blank	Hidden
… hundreds more	All hidden instantly

Keyboard Shortcut Cheat Sheet

Action	Windows	Mac
Open Go To dialog	`F5` or `Ctrl+G`	`F5` or `Ctrl+G`
Go To Special	Click “Special…”	Click “Special…”
Select Blanks	Arrow keys + Enter	Arrow keys + Enter
Hide Rows	`Ctrl+9`	`Cmd+9`
Unhide Rows	`Ctrl+Shift+9`	`Cmd+Shift+9`

Comparison: This Method vs. Alternatives

Method	Speed	Safety	Reversible	Best For
Go To Special + Ctrl+9 (This tutorial)	⚡ Instant	✅ Safe	✅ Yes (Ctrl+Shift+9)	Large datasets, recurring cleanup
Filter → Delete visible rows	Fast	⚠️ Deletes data	❌ No	Permanent cleanup
Right-click each row → Hide	🐢 Very slow	✅ Safe	✅ Yes	< 10 blank rows
VBA Macro	Fast	✅ Safe	✅ Yes	Automation, repeated tasks
Power Query	Medium	✅ Safe	✅ Yes	Data transformation pipelines

Pro Tips & Gotchas

✅ Works With:

Entire columns selected — selects blanks in entire columns (may be slow on 1M+ rows)
Specific range selected — faster, more precise (recommended: A1:D10000)
Multiple non-adjacent columns — hold Ctrl while clicking column headers

⚠️ Watch Out For:

Header rows — If row 1 has blanks, it will be hidden too. Select from row 2 onward (A2:D1000)
Entire column selection — Selecting A:D selects all 1,048,576 rows; Go To Special scans all of them. Use a defined range for speed.
Merged cells — May behave unexpectedly; unmerge first if issues arise

🔄 To Unhide Rows Later:

Select rows above and below the hidden section → Press Ctrl+Shift+9 (Windows) or Cmd+Shift+9 (Mac)

Use Cases — When to Use This

Scenario	Why This Method Wins
Imported data with gaps	CSVs, database exports often have blank rows
Monthly report cleanup	Recurring task — 30 seconds vs 30 minutes
Data received from others	Quickly clean up before analysis
Dashboard preparation	Hide blank rows for cleaner presentation
Before pivot tables	Blank rows can cause “(blank)” items in pivots

Key Takeaways

✅ F5 → Special → Blanks = Select ALL blank cells instantly
✅ Ctrl+9 = Hide selected rows (entire rows, not just cells)
✅ Ctrl+Shift+9 = Unhide rows (remember this!)
✅ Zero macros, zero formulas — pure built-in Excel feature
✅ Works on Windows AND Mac — cross-platform compatible
✅ Reversible — data is hidden, not deleted