Function Problems

How to Filter Excel Column B When Column C Is Not Blank

Adam Davis Sep 27, 2026 869 views

Question details

The user needs to extract or return values from one column (Column B) based on whether adjacent cells in another column (Column C) contain data.

How to Filter Excel Column B When Column C Is Not Blank

Product: Spreadsheet
Device & OS: not provided
Scenario: Extracting specific data from a dataset dynamically while ignoring rows that have empty cells in a dependent column.
Observed behavior: The user wants to generate a clean list of values from Column B where Column C is not empty, either by dynamically filtering the results or removing the blank row entries entirely.

Before you start

Ensure your dataset does not contain merged cells in the columns you intend to filter, and verify whether the blank-appearing cells are truly empty or contain hidden spaces.

Solution 1Recommended

Use the FILTER Function

The most efficient way to dynamically return values from Column B based on Column C's non-blank status.

The FILTER function is a powerful dynamic array formula that can automatically extract data meeting specific criteria. Using the not-equal-to operator (<>) combined with an empty string ("") allows you to exclude blank cells effortlessly.

Select the target cell

Click on an empty cell where you want the filtered list from Column B to appear. Make sure there is enough empty space below it for the results to spill.

Enter the FILTER formula

Type the formula =FILTER(B7:B12, C7:C12<>"") into the formula bar, adjusting the ranges B7:B12 and C7:C12 to match your actual dataset.

Apply the formula

Press the Enter key. The filtered values from Column B will automatically populate the cells.

Dynamic Updates: The FILTER function automatically updates the results. If you add or remove data in Column C later, the extracted list will refresh instantly.

Solution 2

Use a VBA Macro to Delete Rows with Blank Cells

Use this solution if you want to permanently clean your dataset by removing rows where Column C is blank instead of just extracting them to a new location.

Solution 3

Modify Source Formulas

If Column B is generated by a formula (like XLOOKUP), you can adjust the formula itself to only return values when the dependent cell is not blank.

Filter Data Seamlessly

Use WPS Spreadsheet to Filter Data Efficiently

WPS Spreadsheet fully supports advanced array formulas like FILTER and provides powerful data processing tools that are highly compatible with Microsoft Excel formats.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx or .xls file.
2. Enter the formula: Select the destination cell and type the =FILTER(B:B, C:C<>"") formula.
3. View your filtered data: Press Enter to instantly display your clean data array without any lag.

Fully compatible with Microsoft Excel formulas, functions, and VBA macros.Supports dynamic array functions like FILTER effortlessly.Free, lightweight, and features a familiar tabbed interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the FILTER function returning a #CALC! error?

The #CALC! error occurs when the FILTER function finds no matches that meet your criteria. You can prevent this by adding a third argument to handle empty results, such as =FILTER(B7:B12, C7:C12<>"", "No Data").

Can I filter out cells that contain formulas returning empty strings?

Yes, using the <>"" criteria in the FILTER function successfully ignores both truly empty cells and cells that contain formulas returning empty text ("").

How do I filter multiple columns based on blanks in Column C?

You can expand the return array range in the first argument of the function. For example, using =FILTER(A7:B12, C7:C12<>"") will return both columns A and B for all rows where Column C is not blank.

Excel & Data Problems