Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Only Show Cells with Data in Excel

Cody Schneider••9 min read

Working with large Excel spreadsheets often means dealing with more empty cells than actual data. These blank rows and columns make your data harder to read, can cause errors in calculations, and lead to clunky, unprofessional-looking printouts. Fortunately, Excel provides several powerful ways to quickly hide this empty space so you can focus only on the information that matters. This guide walks you through the best methods, from simple filters to automated Power Query workflows.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Hiding Empty Rows Quickly with AutoFilter

The fastest and most common method for hiding empty rows is using Excel's built-in AutoFilter feature. This approach is perfect for a quick clean-up, especially when your dataset has rows that are completely blank or have a blank cell in a critical column.

Imagine you have a sales report, but it includes several empty rows between entries. Here’s how to hide them in seconds:

  1. Click any single cell inside your data range.
  2. Navigate to the Data tab on the Ribbon and click the large Filter icon. You'll see dropdown arrows appear in the header row of your table.
  3. Click the filter arrow for a column that you know should always contain data (like an Order ID or Customer Name).
  4. In the menu that appears, uncheck the box next to (Blanks) at the bottom of the list.
  5. Click OK.

Instantly, Excel will hide all rows where the cell in your selected column was empty. The blue row numbers on the side indicate that rows are hidden. To bring them back, simply go back to the same filter dropdown and select "(Select All)" or click the Clear button on the Data tab.

  • Good for: Quickly cleaning up lists where entire rows are blank or where key columns define a complete entry.
  • Keep in mind: This method only checks one column at a time. If a row has data in one column but is blank in the column you're filtering, it will be hidden. Choose your filter column wisely!

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Using ‘Go To Special’ to Find and Hide All Blank Rows

What if you want to hide rows that are entirely blank without relying on a single column? The "Go To Special" command is a more precise tool for this job. It can identify every single blank cell in your spreadsheet at once, allowing you to hide their corresponding rows.

This trick is incredibly useful for preparing data for printing or presentations, ensuring no awkward empty spaces break up the flow.

Here’s how to do it:

  1. Select the columns of your data that you want to check for blank rows. A pro tip is to click the letter of the first column, hold down Shift, and then click the letter of the last column to select them all.
  2. On the Home tab, find the Editing group and click on Find & Select, then choose Go To Special... from the dropdown menu. (Alternatively, just press Ctrl + G and click the "Special..." button).
  3. In the Go To Special dialog box, select the Blanks option and click OK.
  4. Excel will instantly highlight every blank cell within the range you selected.
  5. With the blank cells still highlighted, go back to the Home tab. In the Cells group, click the Delete dropdown. Choose Delete Sheet Rows.
**Careful!** This permanently deletes the rows. If you'd rather hide them non-destructively, follow steps 1-4 and then simply right-click on any of the selected blank cells, and choose **Hide** from the context menu. This is a much safer option if you think you might need the original layout later.

Hiding Unused or Empty Columns

Sometimes the problem isn’t empty rows but entire columns filled with nothing. This is especially common with data exports from financial or CRM systems, which often include dozens of extra fields you don't need. Hiding them makes your data more focused and manageable.

The Simple Manual Approach

The most straightforward method is to hide columns manually. It’s not fancy, but it gets the job done quickly if you only have a few to hide.

  1. Click the column letter (e.g., C, D) at the top of the worksheet to select the entire column.
  2. To select multiple columns at once:
  3. Right-click on any of the selected column headers.
  4. Choose Hide from the context menu.

To unhide the columns, select the columns on either side of the hidden ones (e.g., if F is hidden, select E and G), right-click, and choose Unhide.

An Advanced Trick to Find All Blank Columns

Manually selecting columns is fine for a few, but what if you have a massive sheet with 50 columns and you want to quickly find the 15 empty ones? Here's a clever way to identify blank columns automatically.

  1. Insert a temporary new row at the very top of your sheet. Right-click on row number 1 and choose Insert.
  2. In cell A1 of this new row (or whichever is your first column), enter the following formula. This formula counts every cell in its column that contains anything - text, numbers, or even an error:
  3. Click on the small square at the bottom right corner of cell A1 (the fill handle) and drag it horizontally across all the columns your data occupies.
  4. Your new top row will now show a count for each column. The columns with a '0' are completely empty.
  5. Now you can easily hold the Ctrl key, click on each column letter that has a zero in the first row, and then right-click to Hide them all at once.
  6. Once you're done, you can delete the temporary helper row.
GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Automating the Cleanup with Power Query

If you're constantly downloading the same messy report and repeating these cleaning steps, it's time to automate it with Power Query. Power Query is Excel's data transformation tool that lets you build a repeatable, refreshable process for cleaning data so you never have to do it manually again.

Think of it as recording your cleaning steps and being able to "play" them back with the click of a button.

Here’s a basic workflow to get started:

  1. Select your dataset and go to the Data tab.
  2. In the Get & Transform Data group, click From Table/Range.
  3. The Power Query Editor window will open, showing a preview of your data.
  4. To remove rows that are completely blank, go to the Home tab in the editor, click on Remove Rows, and then select Remove Blank Rows.
  5. To remove rows based on a specific blank column (like the AutoFilter method), click the filter dropdown arrow on that column's header. Uncheck (null) and click OK. Power Query will remember this step forever.
  6. On the right side of the screen, you'll see your "Applied Steps." Power Query has recorded everything you've done.
  7. When your data looks clean, click the Close & Load button in the top-left. Power Query will load the clean, filtered data into a new worksheet as a formatted Excel Table.

The truly powerful part? Next month, when you get the new messy data, simply paste it into your original source sheet and go to the new, clean sheet. Right-click inside the table and hit Refresh. All your cleaning steps will be re-applied to the new data automatically.

For Power Users: A Simple VBA Macro to Hide Empty Rows

If you're comfortable with a little more technical power, a simple Visual Basic for Applications (VBA) macro can do the job with a single click. A macro can be attached to a button on your worksheet, making it a very efficient solution for repeated use.

Heads up: Always save a backup of your file before running a macro for the first time.

  1. Open the VBA Editor by pressing Alt + F11 on your keyboard.
  2. In the VBA editor, go to Insert > Module to create a new module.
  3. Copy and paste the following code into the white module space:
Sub HideEmptyRows()
    Dim lastRow As Long
    Dim r As Long

    ' Disables screen updating to speed up the macro
    Application.ScreenUpdating = False

    ' Finds the last used row in the active sheet
    If WorksheetFunction.CountA(Cells) > 0 Then
        lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If

    ' Loops through rows from bottom to top
    For r = lastRow To 1 Step -1
        ' Checks if the entire row has no data
        If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
            Rows(r).Hidden = True
        End If
    Next r

    ' Re-enables screen updating
    Application.ScreenUpdating = True
End Sub
  1. Close the VBA Editor (click the X or press Alt + Q).
  2. Save your workbook as a Macro-Enabled Workbook (.xlsm), otherwise your code will be lost.
  3. To run the macro, press Alt + F8, select HideEmptyRows from the list, and click Run. The macro will instantly scan your active sheet and hide every totally blank row.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Final Thoughts

Hiding irrelevant cells is a fundamental step in making any spreadsheet easier to analyze and understand. Whether you prefer the straightforward click-and-go of AutoFilter, the precision of Go To Special, the repeatable efficiency of Power Query, or a custom VBA macro, Excel has a technique that fits your needs. Choosing the right one can save you valuable time, transforming a cluttered data dump into a focused, insightful report.

Manually cleaning data in Excel, even with these valuable tricks, is often just the start of a tedious reporting cycle. If you feel like your week is spent hiding rows and fighting with spreadsheets simply to build the same old reports, you're losing hours that could be spent on genuine analysis. At Graphed , we automate this entire reporting chore. You connect your data sources - like Google Ads, Shopify, Salesforce, or even Google Sheets - just one time. From there, you just ask for what you need in plain English. We build interactive, live-updating dashboards and charts in seconds, letting you go straight from your data to making decisions, with no spreadsheets in between.

Related Articles