How to Filter Strikethrough in Excel

Recently I was working on a project where I had to filter all the cells that had strikethrough formatting applied to it.

The issue is…

There is no built-in way to filter cells that have strikethrough applied to them.

So we need in a workaround.

In this article, I will cover two simple ways you can use to filter cells with strikethrough in Excel (one using Find & Replace and one using VBA).

So let’s dive in!

Filter Strikethrough Using Find & Replace

With Find & Replace, we can find and then give a color to all the cells with strikethrough format.

Once this is done, we can filter these cells based on the cell color (which is a built-in filter in Excel).

Below I have a data set where I have some cells in column A that have the strikethrough format applied to it and I want to filter them.

Here are the steps to filter strikethrough cells:

1. Select all cells in column A.
2. Hold the Control key and then press the F key. This will open the Find and Replace dialog box. Alternatively, you can also click on the Home tab, then click on the Find & Select icon in the Editing group and then click on the Find option.
3. In the Find and Replace dialog box, click on the Options button. Doing this will show you some additional options

4. Click on the Format button. This will open the Find Format dialog box

5. Go to the Font tab and check the Strikethrough option (listed in the Effect options).

6. Click OK.
7. Click on Find All. Doing this will find all the cells that have the strikethrough format applied to it and show you the list of all these cells below the Find and Replace dialog box. At this point, Excel has only selected the first cell where it found the strikethrough format.

8. Now hold the Control key and press the A key. This will select all the cells that has been found with the strikethrough format.

9. Close the Find and Replace dialog box.
10. Go to the Home tab and then give a fill color to all these selected cells. In this example I’ll go with the blue color.

11. Select and right-click on any one of the selected cells, go to the Filter option, and then click on Filter by Selected Cell’s Color

And that’s will do it!

Now you’ll only see the cells that had strikethrough formatting.

So while there is no direct option in Excel to filter strike through cells we first found them using Find and Replace and then give them a color.

This allowed us to use the built-in filter by color option in Excel, that enables us to also filter all the cells with Strikethrough.

Note: The color you apply will overwrite any existing cell colors. So if your data already uses cell colors for other purposes, this method might interfere with that formatting. Also, this method is temporary – if you later add more strikethrough text, you’ll need to repeat the process to update the filtered view.

Also read: Filter Cells with Bold Font Formatting in Excel

Filter Strikethrough Using VBA

You can also filter strikethough cells by creating your own custom function with VBA.

This function can then be used in a helper column and it would return TRUE if the referenced cell has strikethrough format applied, and FALSE and if not. Once you have this, you can filter the hypercolumn thereby also filtering the cells with strike through format.

Let me show you how to do this.

Below I have a data set where I have some cells in column A that have the strikethrough format applied to it and I want to filter them.

Here are steps to use VBA to filter strikethrough cells:

1. Go to the Developer tab and click on Visual Basic (or press Alt + F11)

2. In the VB Editor that opens, click on the Insert option and then click on Module. This will insert a new Module for your workbook.

3. Copy the below VBA code into the module code window

Function CheckSR(rng As Range)
CheckSR = rng.Font.Strikethrough
End Function

The above steps creates the UDF (User Defined Function) that you can now as a regular worksheet function.

4. Add a helper column next to your data. Let’s name this column as Helper (enter the text in the top most cell).

5. Enter the formula =CheckSR(A2) in cell C2 in the helper column. The function will return TRUE for cells with strikethrough and FALSE for those without. Copy this formula down for all the cells in the column

6. Apply a filter to your dataset. To do this, select any cell in the dataset and then use the keyboar shortcut Ctrl + Shift + L (or click on the Data tab and then click on the Filter icon)

7. Filter the helper column to show only TRUE values

This will show you all rows where the cells have strikethrough formatting applied.

Note: If you want to save this code in your Excel file so that it can be reused again you need to save your file with .XLSM extenstion

Both methods work great – the Find & Replace method is quick and doesn’t require VBA, while the VBA method gives you a reusable function you can use anytime you need to check for strikethrough formatting.

I hope you found this article helpful.

If you have any questions or suggestions, kindly leave them in the comments below.

Other Excel articles you may also like:

• Dynamic Excel Filter Search Box – Extract Data as you Type
• Excel Advanced Filter
• Excel Filter Function
• Count Colored Cells in Excel