3 Easy Ways to Filter Cells with Strikethrough in Excel

Sharing is caring!

Strikethrough formatting in Excel can be useful for marking completed tasks or highlighting specific data. However, filtering cells with strikethrough isn’t as straightforward as filtering by text or numbers.

Fortunately, there are three effective methods to achieve this: using VBA (Visual Basic for Applications), employing Find & Replace, or utilizing the Kutools Add-in.

In this comprehensive guide, we will explain all three approaches step-by-step to help you filter strikethrough cells efficiently and confidently.

Method 1: Using VBA to Filter Strikethrough Cells

If you’re comfortable using VBA, this is a powerful and automated method to filter cells with strikethrough formatting. By creating a User Defined Function (UDF), you can identify which cells have strikethrough applied and then filter them accordingly.

Step 1: Open the VBA Editor

1. Press ALT + F11 to open the Visual Basic for Applications editor.
2. In the editor, click Insert > Module to create a new module.

Step 2: Add the VBA Code

Copy and paste the following code into the module window:

Function CheckStrikethrough(rng As Range) As Boolean
    CheckStrikethrough = rng.Font.Strikethrough
End Function

This function checks whether the text in the specified range has the strikethrough format applied. It returns TRUE for cells with strikethrough and FALSE for others.

Step 3: Use the Function in Excel

1. Close the VBA editor and return to your Excel worksheet by pressing ALT + Q.
2. In a new column (e.g., Column B), enter the formula:
   =CheckStrikethrough(A2)
   Replace A2 with the reference to the first cell in your dataset.
3. Drag the fill handle down to apply the formula to all rows. This will return TRUE for cells with strikethrough formatting and FALSE for others.

Step 4: Filter the Results

1. Select your dataset along with the new helper column.
2. Go to the Data tab and click Filter.
3. Use the dropdown in the helper column to filter rows where the value is TRUE. This will display only the cells with strikethrough formatting.

Example of Using VBA to filter cells with Strikethrough

By applying the filter, you can isolate and manage the rows that contain strikethrough formatting, making it easier to review or modify your data.

Method 2: Using Find & Replace to Filter Strikethrough Cells

If you’re not familiar with VBA or prefer a manual approach, you can use Excel’s Find & Replace feature. This method temporarily highlights strikethrough cells, allowing you to filter them by color.

Step 1: Select Your Data Range

1. Highlight the range of cells where you want to check for strikethrough formatting.
2. If your data includes multiple columns, ensure the selection covers all relevant cells to avoid missing any strikethrough entries.

Step 2: Open Find & Replace

1. Press CTRL + F to open the Find and Replace dialog box.
2. Click Options to expand the dialog.
3. Click the Format… button and navigate to the Font tab.
4. Check the box for Strikethrough and click OK.

Step 3: Find All Strikethrough Cells

1. Click Find All to display all cells with strikethrough formatting.
2. Press CTRL + A to select all found cells.
3. Close the dialog box.

Step 4: Highlight the Selected Cells

With the selected cells still selected, apply a fill color (e.g., blue) from the Home tab to mark them visually. This step makes it easier to identify and filter the cells later.

Step 5: Filter by Color

1. Click on any cell in your dataset and go to the Data tab.
2. Click Filter to enable filtering.
3. Use the filter dropdown on your column header and choose Filter by Color to display only the cells with the fill color applied.

Example of Using Find & Replace Method

By filtering by color, you can quickly isolate cells with strikethrough formatting and perform further actions as needed.

Method 3: Using Kutools Add-In to Filter Strikethrough Cells

Kutools for Excel is a powerful third-party add-in that simplifies complex tasks in Excel, including filtering cells with strikethrough formatting. This method is perfect for users who want a straightforward solution without using VBA or manual processes.

Prerequisites: Make sure you have Kutools for Excel installed. You can download it from the official website if you don’t have it already.

Steps:

1. Open your Excel worksheet and locate the column containing the cells with strikethrough that you want to filter.
2. Select the entire column by clicking on the column header (e.g., A, B, C).
3. Access the Kutools Filter Strikethrough utility:
   • Go to the Kutools Plus tab in the Excel ribbon.
   • In the Special Filter group, click on Filter Strikethrough.
4. Confirmation:
   • A dialog box will pop up, indicating the number of cells found with strikethrough.
   • Click OK to confirm and proceed with the filtering.
5. View the filtered results:
   • Excel will now display only the rows containing cells with strikethrough in the selected column. All other rows will be hidden.

Additional Tips:

• You can clear the filter and show all rows again by clicking the Filter icon in the Data tab and selecting Clear.
• Kutools also offers other special filters, such as filtering by font color, background color, or bold/italic formatting. You can explore these options in the Kutools Plus > Special Filter menu.

Example of Using Kutools to Filter Strikethrough Cells

By using Kutools, you can simplify the process and save time, especially when working with large datasets.

Comparison of Methods to Filter by Strikethrough in Excel

When to Use Each Method

• Use VBA if you frequently work with strikethrough formatting and need an automated, repeatable solution.
• Use Find & Replace if you’re working on a one-time task or prefer not to use VBA. This method is straightforward and accessible for all users.
• Use Kutools if you want a fast, hassle-free solution with minimal manual effort, especially for large or complex datasets.

Key Tips for Managing Strikethrough Data

1. Backup Your Data: Before applying any changes, ensure your data is backed up to avoid accidental loss or formatting errors.
2. Combine with Conditional Formatting: Use conditional formatting to highlight or manage strikethrough cells dynamically. This can be especially helpful for large datasets.
3. Organize Your Workflow: Clearly label helper columns or filtered results to avoid confusion during analysis or reporting.
4. Review Your Results: After filtering, double-check the highlighted cells or filtered rows to ensure no data is missed or incorrectly categorized.

Whether you prefer the automated VBA approach, the manual Find & Replace method, or the comprehensive Kutools solution, all three options provide reliable solutions for working with strikethrough data.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.