Often you may want to filter cells in Excel based on whether or not the cells have a strikethrough.
For example, suppose we have the following dataset and we’d like to filter the cells to only show the ones that have a strikethrough:
The easiest way to do this is by writing some code in VBA in Excel.
The following step-by-step example shows exactly how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Show the Developer Tab in Excel
Next, we need to make sure the Developer tab is visible on the top ribbon in Excel.
To do so, click the File tab, then click Options, then click Customize Ribbon.
Under the section called Main Tabs, check the box next to Developer, then click OK:
Step 3: Create a Macro Using VBA
Next, click the Developer tab along the top ribbon and then click the Visual Basic icon:
Next, click the Insert tab and then click Module from the dropdown menu:
Next, paste the following code into the module code editor:
Function FindStrikethrough(r As Range)
FindStrikethrough = r.Font.Strikethrough
End Function
The following screenshot shows how to do so:
Once you’ve entered the code, feel free to close out of the VB Editor. The code will automatically be saved.
Step 4: Use the Macro to Check if Cells have Strikethrough
Next, type the following formula into cell B2 to check if cell A2 has a strikethrough or not:
=FindStrikethrough(A2)
Click and drag this formula down to each remaining cell in column B:
Column B now returns TRUE or FALSE to indicate if the each corresponding cell in column A has a strikethrough or not.
Step 5: Filter by Strikethrough
Now that we have a column with TRUE and FALSE values, we can simply filter the rows based on the values in this new column.
To do so, highlight the cell range A1:B11, then click the Data tab along the top ribbon, then click the Filter icon within the Sort & Filter group.
Then click the dropdown arrow next to the header Strikethrough and uncheck the box next to FALSE:
Once you click OK, the rows will be filtered to only show the ones where the team name has a strikethrough:
Note: Feel free to uncheck the box next to TRUE to instead filter for cells that do not have a strikethrough.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Filter by Number of Characters in Excel
How to Use a Case Sensitive Filter in Excel
How to Filter by Multiple Colors in Excel
