Data Import & Export

How to Fix Excel Filtering When Merged Cells Hide Rows

Camila Milosovich Sep 28, 2026 869 views

Question details

The user needs to correctly filter a dataset where merged cells or blank rows under a category label prevent all related rows from being displayed.

How to Fix Excel Filtering When Merged Cells Hide Rows

Product: Microsoft Excel
Device & OS: not provided
Scenario: Applying an AutoFilter to a column that contains merged cells acting as category headers for multiple rows.
Observed behavior: The AutoFilter only returns the first row associated with the merged cell, effectively hiding the rest of the related rows because they are treated as blank.

Before you start

Identify which columns contain the merged cells and clear any active filters in your spreadsheet so that no hidden rows are accidentally excluded from the data update.

Solution 1Recommended

Unmerge Cells and Fill Blanks with Go To Special

This is the most reliable method to ensure every row has a filterable value. By unmerging the cells and copying the category label to all corresponding blank rows, AutoFilter will function perfectly.

Excel's AutoFilter evaluates each row individually. When cells are merged, only the top-left cell retains the data, leaving the subsequent rows empty. Filling these empty rows is required for the filter to recognize them.

Unmerge the column

Select the column containing the merged cells. Go to the Home tab and click 'Merge & Center' to unmerge them.

Select blank cells

With the column still selected, press F5 or Ctrl+G to open the Go To dialog. Click 'Special', select 'Blanks', and click OK.

Enter the fill formula

Type an equals sign (=) and press the Up arrow key on your keyboard to reference the cell directly above the first blank cell (e.g., =A2).

Apply formula to all blanks

Press Ctrl+Enter simultaneously. This applies the formula to all selected blank cells, filling them with the correct category labels.

Convert formulas to values

Select the entire column, copy it (Ctrl+C), right-click, and select 'Paste as Values' to remove the formulas and keep the static text.

Unmerge Cells and Fill Blanks with Go To Special

Hide Repeated Labels: If you prefer the visual appearance of merged cells, you can select the duplicated values and change their font color to match the cell background, hiding them from view while keeping them filterable.

Efficient Data Management

Seamlessly Fix Data Filtering Issues with WPS Spreadsheet

WPS Spreadsheet provides powerful tools like Go To Special and AutoFilter, allowing you to quickly unmerge cells, fill missing data, and accurately filter complex datasets without hassle.

1. Open Your Spreadsheet: Launch WPS Office and open your Excel document containing the merged cells.
2. Unmerge and Locate Blanks: Select the column, click 'Merge and Center' to unmerge, then press Ctrl+G to open the Go To dialog and select 'Blanks'.
3. Fill the Blank Rows: Type '=' and press the Up arrow key, then press Ctrl+Enter to fill the empty rows with the category values.
4. Filter the Data: Select your header row, navigate to the Data tab, and click the Filter icon to display all related rows flawlessly.

Fully compatible with Microsoft Excel formats (.xlsx and .xls).Intuitive Go To Special feature to rapidly locate and fill blank cells.Free, lightweight, and user-friendly interface for seamless daily data analysis.

Frequently Asked Questions

Why does AutoFilter fail when applied to merged cells?

When cells are merged in Excel, only the topmost left cell physically contains the data. The other cells within the merged block become blank. Because AutoFilter evaluates row by row, it only finds the value in the first row and hides the blank ones.

Can I filter data without unmerging the original cells?

Yes, you can create a 'Helper Column' next to your dataset. In this new column, use a formula to extract or reference the values from the merged column, fill it down, and apply your filter to the helper column instead.

How can I make unmerged cells look like they are still merged?

After unmerging and filling the blank cells, you can apply Conditional Formatting to hide duplicated text, or simply change the font color of the repeated text to match the white background of the cell.

Excel & Data Problems