Pivot Table Issues

How to Show Pivot Table Rows Where Two Columns Contain Data in Excel

Maira Mehtab Oct 9, 2026 868 views

Question details

The user needs to filter a pivot table to display only the rows where two specific selected columns both contain values, without losing sorting and drill-down capabilities.

How to Filter Pivot Table Rows to Show Data in Two Columns

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Filtering pivot table data to exclude rows that have missing, empty, or zero values in one or both of two target columns.
Observed behavior: Standard value counting filters often apply to the grand total instead of evaluating individual column data, failing to correctly isolate rows where both columns possess values.

Before you start

Ensure your source data has clear column headers and no merged cells before attempting to apply advanced filters or adding helper columns to your pivot table.

Solution 1Recommended

Apply Value Filters to Each Pivot Table Field

Use the built-in Value Filters in your pivot table to individually exclude empty or zero values for both columns.

This is the most direct approach to filtering pivot table rows. By applying a Value Filter to the specific fields rather than relying on overall row totals, you ensure the pivot table strictly evaluates the presence of data in both columns independently.

Access Value Filters

In your pivot table, right-click a cell in the first target column you want to check for data, or click the drop-down arrow on the Row Labels.

Set Filter Criteria

Select 'Filter' from the context menu, then choose 'Value Filters'. Select 'Does Not Equal' from the logic dropdown.

Specify the Empty Value

Enter the value that represents an empty cell in your dataset (for example, type 0 or leave it blank depending on your data). Click 'OK'.

Repeat for the Second Column

Right-click a cell in the second target column and apply the exact same Value Filter steps to ensure both columns must have data to be displayed.

Apply Value Filters to Each Pivot Table Field

Drill-down Preserved: Using Value Filters directly on the pivot table fields preserves your ability to double-click cells and drill down into the underlying source data.

Solution 2

Add a Helper Column to the Source Data

Create a formula in your original dataset to flag rows with data in both columns, then use this flag to filter the pivot table.

Advanced Pivot Table Management

Effortlessly Filter Multi-Column Data with WPS Spreadsheet

WPS Spreadsheet offers powerful, highly compatible data analysis tools. Whether you are applying complex Value Filters or adding helper formulas to source data, WPS provides a smooth, intuitive experience to get your pivot tables perfectly organized.

1. Open Your Dataset: Launch WPS Spreadsheet and open the file containing your source data and pivot table.
2. Access Filter Options: Click the filter icon on your pivot table row labels and navigate to Value Filters.
3. Apply Conditions: Set the condition to 'Does Not Equal' 0 or empty for the required columns, and click OK to instantly update the view.

Fully compatible with Microsoft Excel (.xlsx) file formats and pivot structuresIntuitive Value Filter interface for precision data extractionRobust formula engine for complex helper column calculationsFree, lightweight, and fast alternative to heavy office suites

microsoft office alternative - wps office

Frequently Asked Questions

Why does my 'greater than zero' value filter apply to the grand total instead of the columns?

When you apply a generic value filter in standard pivot table settings, Excel often evaluates the Grand Total column by default. To filter specific columns, you must right-click the exact field you want to filter and apply the Value Filter there, or use a helper column in the source data.

Will using a helper column prevent me from drilling down into my pivot table?

No, adding a helper column to your source data and filtering by it in the pivot table will completely preserve your sorting and drill-down (double-click) capabilities.

What if the empty cells actually contain invisible spaces?

If your empty cells contain space characters, a standard blank check won't work. You can modify your helper column formula to use the TRIM function, such as =IF(AND(TRIM([@Column1])<>"",TRIM([@Column2])<>""),"Include","Exclude").

Excel & Data Problems