You can apply the strikethrough formatting to cells in an Excel dataset to indicate completed tasks, outdated information, etc. For example, in the dataset below completed tasks have a strikethrough formatting.
However, when you want to filter the cells with strikethrough formatting, you will discover that Excel does not have a built-in filter for this formatting. What to do?
I will show you workaround methods using Find and Replace and Filter features and a User-Defined Function to isolate and filter cells with strikethrough formatting.
Method #1: Use Find and Replace and Filter Features
You can use the Find and Replace feature to apply a color to the cells containing data with strikethrough and afterward use the Filter feature to filter the data by color.
Suppose you have the dataset below with strikethrough in some cells indicating completed tasks. You want to filter the data with strikethrough formatting.
You can use the steps below to filter the data with strikethrough.
Step #1: Use Find and Replace to Color Cells with Strikethrough
Follow the steps below.
- Click the Home tab, open the Find & Select drop-down list on the Editing group, and select the Replace option.
Alternatively, press CTRL + H.
The above step displays the Find and Replace feature with the Replace tab open.
- On the extreme right of the ‘Find what’ drop-down control, click the down arrow next to the Format command button and select the Choose Format From Cell option.
Notice that Excel hides the Find and Replace feature and displays an eyedropper icon next to the cursor.
- Mouse-select any cell in the dataset with a strikethrough formatting.
The above step redisplays the Find and Replace feature and returns the cursor to its normal appearance.
- On the extreme right of the ‘Replace with’ drop-down control, click the down arrow next to the Format command button and select the Format option.
Alternatively, click directly on the Format command button.
The above step opens the Replace Format dialog box.
- On the Replace Format dialog box, open the Fill tab, and select a background color from the color palette.
In this example, I have chosen a yellow background color.
Notice the preview of the background color you choose in the sample area near the bottom of the dialog box.
- Click OK.
- Click the Replace All command button on the Find and Replace feature.
Excel displays a message box indicating the number of replacements it has done.
- Click OK on the message box and click Close on the Find and Replace feature.
Notice that all cells with data with strikethrough formatting now have the background color you chose.
Step #2: Filter Data With Strikethrough Formatting
Use the steps below.
- Select any cell in the dataset.
- Open the Data tab and click the Filter button on the Sort & Filter group.
Alternatively, press CTRL + Shift + L.
The above step turns on filtering for the dataset. Excel displays a filtering button with a down arrow icon in row 1.
- Click the filter button, hover over the Filter by Color option, and select the target color on the Filter by Cell Color submenu.
The above step applies a filter to the dataset, filtering all cells with the strikethrough formatting.
Also read: How to Filter Cells with Notes in Excel?
Method #2: User-Defined Function to Filter Cells with Strikethrough
You can create a User-Defined Function to return TRUE or FALSE values in a helper column, indicating whether cells in a corresponding data column have a strikethrough formatting.
Then, filter the helper column to show only the cell with TRUE values, effectively displaying only rows with cells that have strikethrough formatting.
Suppose you have the dataset below with strikethrough in some cells indicating completed tasks. You want to filter the data with strikethrough formatting.
You can use the steps below to filter the data with strikethrough.
Step #1: Add a Helper Column to the Dataset
Add a helper column to the dataset and give it an appropriate title such as ‘Has Strikethrough.’
Step #2: Create a User-Defined Function
Copy the code snippet below to a standard module in the workbook containing the dataset.
Function HASSTRIKETHROUGH(rng As Range) As Boolean
HASSTRIKETHROUGH = rng.Font.Strikethrough
End FunctionSave the workbook as an Excel Macro-Enabled Workbook (*.xlsm) to retain the function for future use.
Step #3: Use the User-Defined Function in the Helper Column
Enter the formula below in the helper column.
=HASSTRIKETHROUGH(A2)
The formula returns TRUE if the data in the target cell in column A has a strikethrough effect and FALSE if it doesn’t.
Step #4: Filter the Helper Column
- Select any cell in the dataset.
- Open the Data tab and click the Filter button on the Sort & Filter group.
Alternatively, press CTRL + Shift + L.
The above step turns on filtering for the dataset. Notice the filtering buttons with a down arrow icon in row 1.
- Click the filter button in row 1 of the helper column and deselect the Select All option.
- Select the TRUE option and click OK.
The above step filters only TRUE values in the helper column, effectively displaying only rows where cells have a strikethrough formatting.
I have shown you ways to filter cells with strikethrough formatting in Excel. I hope you found the tutorial helpful.
Other Excel articles you may also like:
