5 Ways To Filter Strikethrough In Excel

Strikethrough is a formatting option in Excel that allows you to cross out text in a cell, indicating that it is no longer relevant, incorrect, or completed. However, when working with large datasets, it can be challenging to filter out cells with strikethrough formatting. In this article, we will explore five ways to filter strikethrough in Excel, making it easier to manage and analyze your data.

Understanding Strikethrough Formatting

Before we dive into the methods for filtering strikethrough, it's essential to understand how strikethrough formatting works in Excel. Strikethrough is a type of font formatting that can be applied to text in a cell. To apply strikethrough, select the cell or range of cells, go to the Home tab in the ribbon, and click on the Font group dialog box launcher. In the Font tab, check the box next to Strikethrough.

Method 1: Using the Filter Button

The first method to filter strikethrough in Excel is by using the Filter button. This method is straightforward and easy to use.

1. Select the cell or range of cells that you want to filter.
2. Go to the Data tab in the ribbon and click on the Filter button.
3. In the filter dropdown menu, click on "Font Color" and select " Strikethrough" from the options.
4. Click OK to apply the filter.

Method 2: Using Conditional Formatting

Conditional formatting is a powerful tool in Excel that allows you to format cells based on specific conditions. You can use conditional formatting to highlight cells with strikethrough formatting.

1. Select the cell or range of cells that you want to format.
2. Go to the Home tab in the ribbon and click on the Conditional Formatting button.
3. Select "New Rule" from the dropdown menu.
4. Choose "Use a formula to determine which cells to format."
5. Enter the formula =CELL("format",A1) (assuming the cell is in column A) and click OK.
6. Select the formatting options and click OK.

Method 3: Using VBA Macro

If you need to filter strikethrough frequently, you can create a VBA macro to automate the process.

1. Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic.
2. In the Visual Basic Editor, click on "Insert" and select "Module."
3. Paste the following code into the module:

Sub FilterStrikethrough()
    Dim cell As Range
    For Each cell In Selection
        If cell.Font.Strikethrough = True Then
            cell.EntireRow.Hidden = True
        End If
    Next cell
End Sub

4. Save the module and close the Visual Basic Editor.
5. To run the macro, select the cell or range of cells and press Alt + F8.

Method 4: Using Power Query

Power Query is a powerful tool in Excel that allows you to manipulate and analyze data. You can use Power Query to filter strikethrough.

1. Select the cell or range of cells that you want to filter.
2. Go to the Data tab in the ribbon and click on the "From Table/Range" button.
3. In the Power Query Editor, click on the "Add Column" button.
4. Select "Custom Column" and enter the formula = if Text.Contains([Cell],"~") then "Strikethrough" else "No Strikethrough"
5. Click OK to apply the formula.
6. Click on the "Filter" button and select "Strikethrough" from the dropdown menu.

Method 5: Using Excel Formulas

The final method to filter strikethrough in Excel is by using Excel formulas. You can use the CELL function to detect strikethrough formatting.

1. Select the cell or range of cells that you want to filter.
2. Enter the formula =IF(CELL("format",A1) = "~~~", "Strikethrough", "No Strikethrough") (assuming the cell is in column A)
3. Press Enter to apply the formula.
4. Use the AutoFilter feature to filter the results.

Gallery of Filter Strikethrough in Excel

Filter Strikethrough in Excel Image Gallery

Conclusion

Filtering strikethrough in Excel can be a challenging task, but with the right methods, you can easily manage and analyze your data. In this article, we explored five ways to filter strikethrough in Excel, including using the Filter button, conditional formatting, VBA macro, Power Query, and Excel formulas. By using these methods, you can streamline your workflow and make data analysis more efficient.

Share Your Thoughts

Have you ever struggled with filtering strikethrough in Excel? Which method do you find most effective? Share your thoughts and experiences in the comments below.