How to Filter Strikethrough in Excel: Step-by-Step Guide

SenseBridge

2 min read

·

24-10-2024

1

0

Share

Filtering strikethrough text in Excel can be a little tricky since Excel doesn’t provide a direct option for it. However, with a combination of conditional formatting, custom formulas, and filtering techniques, you can manage this task effectively. In this step-by-step guide, we'll walk you through the process of identifying and filtering strikethrough text in Excel. 🗂️

Understanding Strikethrough in Excel

Strikethrough is a formatting option that allows you to put a line through your text. This feature is often used to indicate that an item is completed or to show the difference between old and new information. In Excel, strikethrough can be applied to any cell, but it isn’t immediately recognizable when filtering data.

Step 1: Apply Strikethrough Formatting

Before we can filter strikethrough text, we first need to make sure we have some cells with strikethrough formatting.

1. Select the cell(s) where you want to apply strikethrough.
2. Right-click the selection and choose Format Cells.
3. Go to the Font tab.
4. Check the box next to Strikethrough.
5. Click OK.

Your selected text will now have a line through it! ✅

Step 2: Create a Helper Column

Since Excel doesn’t allow you to filter directly by strikethrough text, we will create a helper column to identify these cells.

1. Next to your data (let's assume your data is in column A), create a new column (Column B) for your helper function.

2. In cell B1, input the following formula:

   =IF(GET.CELL(24, A1), "Strikethrough", "Not Strikethrough")
   

3. Press Enter. This formula checks if the text in column A has strikethrough formatting.

4. Copy this formula down for all rows of your data.

Important Note:

The GET.CELL function is a legacy Excel function, so you may need to use it within a named range. If you encounter any issues, consider using VBA for a more robust solution.

Step 3: Use Name Manager for GET.CELL Function

To use GET.CELL, you'll need to set it up through the Name Manager.

1. Click on the Formulas tab.

2. Select Name Manager.

3. Click on New and create a new name (e.g., CheckStrikethrough).

4. In the "Refers to" field, input the formula:

   =GET.CELL(24, A1)
   

5. Click OK and close the Name Manager.

Now you can use the CheckStrikethrough named range in your helper column.

Step 4: Filtering the Data

Now that we have our helper column filled with the statuses of strikethrough, we can filter our data:

1. Select your data range including the helper column.
2. Go to the Data tab.
3. Click on Filter.
4. Click the dropdown arrow in your helper column (Column B).
5. Uncheck Not Strikethrough and leave Strikethrough checked.
6. Click OK.

You will now see only the rows that have strikethrough text! 🎉

Summary of Steps

Troubleshooting Common Issues

1. GET.CELL not returning values: Ensure that you are applying the formula correctly and that the data is in the correct format.
2. Strikethrough not appearing in filtered results: Check if the filtering was applied to the correct range and that your helper column is accurate.

With these steps, you should be able to effectively filter for strikethrough text in Excel. This method not only helps to identify completed tasks but also improves your ability to manage and organize your data efficiently. Happy Excel-ing! 📊✨