Function Problems

How to Exclude Strikethrough Text from an Excel FILTER Formula

Partner Editor Sep 27, 2026 869 views

Question details

The user needs a method to exclude tasks that have been formatted with strikethrough from a dynamic calendar or report generated by the FILTER function.

How to Exclude Strikethrough Text from an Excel FILTER Formula

Product: Excel
Device & OS: not provided
Scenario: Filtering a combined task list to populate a report, while omitting tasks that have been crossed out or completed.
Observed behavior: The Excel FILTER function evaluates data values natively but cannot directly read font formatting (like strikethrough) as a logical condition, causing crossed-out tasks to remain in the filtered results.

Before you start

Because Excel formulas cannot natively read cell formatting, you must create a helper column in your source data to logically flag tasks that have a strikethrough applied before applying the FILTER function.

Solution 1Recommended

Use a Helper Column to Exclude Strikethrough Data

Create a dedicated helper column to flag tasks that have strikethrough formatting, and then incorporate this helper column as an exclusion criteria in your FILTER formula.

By assigning a specific text value (such as "No") to tasks that are crossed out, you can easily exclude them by multiplying the criteria arrays inside your FILTER function.

Add a Helper Column

Navigate to your source data sheet (e.g., 'Combined Pre-Post Tasks'). Insert a new column next to your task data and name it 'Include Task?' (for this example, we assume this is Column Z).

Identify Strikethrough Tasks

Manually type "No" into the helper column for any task row that has strikethrough formatting applied. Alternatively, you can use a custom VBA function to evaluate the formatting and return "No" automatically.

Apply the FILTER Formula

Select the cell where you want your filtered results to appear. Enter your FILTER formula to include your original criteria along with the exclusion rule. For example: =FILTER('Combined Pre-Post Tasks'!$D:$D, ('Combined Pre-Post Tasks'!$G:$G=A4) * ('Combined Pre-Post Tasks'!$Z:$Z<>"No"), "No Task")

Press Enter to Calculate

Hit Enter. The FILTER function will now evaluate both conditions and completely exclude the tasks you marked with "No" from the final output.

Use a Helper Column to Exclude Strikethrough Data

Automating Strikethrough Detection: If manually typing "No" is too tedious, you can automate it by writing a simple VBA User Defined Function (UDF) that checks the cell's Font.Strikethrough property and outputs "No" to the helper column automatically.

Advanced Data Filtering

Filter and Manage Your Data Seamlessly with WPS Office

WPS Spreadsheet provides full compatibility with standard Excel functions, including array formulas like FILTER. Easily sort, flag, and filter out strikethrough text efficiently in a lightweight workspace.

1. Open Your Spreadsheet in WPS: Launch WPS Office and open your .xlsx workbook containing the data you want to filter.
2. Create a Helper Column: Add a new column next to your source data to flag entries that feature strikethrough text.
3. Input the FILTER Formula: Use the exact same formula syntax (e.g., combining conditions with the asterisk (*) operator) to filter out rows marked for exclusion.
4. Format Automatically: Use the WPS Conditional Formatting rules located on the Home tab to automatically apply strikethrough visuals based on cell values.

Fully compatible with Microsoft Excel formulas and .xlsx files.Offers the same dynamic array functions like FILTER without compatibility loss.Free, lightweight, and highly responsive for large data sets.Built-in robust conditional formatting and data management tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can standard Excel formulas directly detect cell formatting like colors or strikethroughs?

No. Native Excel functions only evaluate the values stored inside cells, not their formatting. You must use a helper column, VBA (macros), or legacy Excel 4.0 macro functions to extract formatting properties.

What does the asterisk (*) do in the FILTER formula?

In Excel's dynamic array functions, the asterisk (*) acts as the logical 'AND' operator. It multiplies the Boolean arrays so that the formula only includes a row if all combined criteria are true.

Why does my FILTER formula return a #CALC! error?

The #CALC! error usually appears when the FILTER function finds no records that match your criteria. To fix this, provide an 'if_empty' argument at the end of the formula, such as "No Tasks" or "" (blank).

Is it possible to use Conditional Formatting to apply the strikethrough automatically?

Yes. Instead of manually applying strikethrough formatting, you can create a Conditional Formatting rule based on a cell's status (e.g., 'Status' = 'Done'). By doing this, your helper column logic and the strikethrough visual can share the same root criteria, eliminating manual data entry.

Excel & Data Problems