How to sort or filter data by strikethrough in Excel?
Excel users frequently apply strikethrough formatting to mark entries and items that are outdated, completed, or no longer relevant in a dataset. When working with large lists—such as inventory records, task trackers, or contact logs—it becomes practical to distinguish these marked entries quickly. However, when you wish to organize or isolate all rows containing strikethroughs, Excel does not provide built-in sort or filter options for this formatting directly. This limitation can slow down your workflow, especially during tasks like auditing, cleanup, or reporting. Fortunately, several effective approaches exist to help you sort or filter based on strikethrough, enabling you to focus on relevant data or take further action on marked entries.
Sort or filter data by strikethrough with User Defined Function
Filter data by strikethrough with Kutools for Excel
Alternative: Detect and filter strikethrough cells with Excel formula and helper column
Sort or filter data by strikethrough with User Defined Function
Although Excel provides flexible tools for managing text and numbers, its standard Sort and Filter features do not allow you to directly work with cell formatting, such as strikethrough. This limitation means you cannot, for example, sort all rows with strikethrough to the bottom, or filter them out, without extra steps. To address this, you can create a simple User Defined Function (UDF) using VBA, which helps identify cells containing strikethrough formatting. Once identified, you can easily apply Excel's Sort or Filter tools to this helper data.
Consider the following dataset where certain rows feature strikethrough text for completed items. By following the steps below, you can effectively sort or filter strikethrough entries:
1. Press ALT + F11 simultaneously to open the Microsoft Visual Basic for Applications window. If this is your first time using VBA, ensure your Excel settings allow macros to run.
2. Within the VBA window, click Insert > Module. Then, copy and paste the following code into the Module. This UDF will help detect whether a cell uses the strikethrough format.
Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function
3. After pasting the code, remember to save and close the VBA window. In a blank cell adjacent to your data—typically the first row of a new column—enter the following formula =HasStrike(A2). In this example, the formula is entered in cell C2. This formula will return TRUE for cells with strikethrough and FALSE for normal entries.
Note: In the sample formula, A2 refers to the cell being checked for strikethrough. If your data begins on another row or column, adjust the cell reference accordingly. To avoid potential errors, always double-check that the target cell reference matches your data range.
4. Drag the fill handle (the small square at the bottom right of the formula cell) downward or across to apply the formula to the rest of your dataset. As shown below, all cells with strikethrough will display TRUE, while others will show FALSE.
Tip: For larger datasets, you may want to double-click the fill handle to auto-fill all remaining rows in your table. If your data changes, you may need to reapply the formula to updated rows.
5. With this new helper column, you can now use built-in sorting and filtering options in Excel:
A: Sort the strikethrough data at the bottom or top of the records
(1.) Select any cell within your data range, and click Data > Sort. This opens the Sort dialog box.
(2.) If prompted by the Sort Warning dialog, select Expand the selection to ensure the sort applies to the whole row, preserving the integrity of your data.
(3.) In the Sort dialog box, set your helper column (Column C) as the sort criteria from the Column drop-down menu. For Sort On, choose Values. You can then set the Order to display strikethrough entries at either the bottom (Smallest to Largest) or the top (Largest to Smallest) of your dataset, depending on your preference.
(4.) Click OK to complete the sort. Now, all strikethrough rows will appear grouped together at the top or bottom of your table as desired.
Tip: When sorting, if you have header rows, double-check that only your data is selected. If strikethrough data should appear at the top, select Largest to Smallest in the order dropdown.
B: Filter the records by strikethrough
(1.) Select your complete range of data, for example, A1 : C14. Then click Data > Filter to enable filter drop-downs on each column.
(2.) Click the filter drop-down arrow next to your helper column header (e.g., C1) and check only the TRUE checkbox. This will quickly display only rows containing strikethrough formatting.
(3.) Click OK to apply the filter; only strikethrough-marked entries remain visible.
Note: Once you've completed sorting or filtering, you may wish to delete the helper column to tidy up your worksheet. Be sure that you no longer need to reapply these steps before removing the column.
Precaution: If your data contains formulas referencing other sheets, or if you copy the sheet elsewhere, check that the UDF code remains available. Also, remembered that changes in formatting after the UDF is applied require refreshing the helper column.
Troubleshooting: If you receive #NAME? errors when entering formulas, confirm that your VBA code was saved, and macros are enabled. If sorting or filtering results do not update as expected, try recalculating your sheet (press F9) or re-entering the formula.
Filter data by strikethrough with Kutools for Excel
If you're seeking a highly efficient solution for filtering strikethrough-formatted cells without using formulas or VBA, Kutools for Excel offers a straightforward way with its Filter Strikethrough feature. This feature enables you to instantly isolate all entries formatted with strikethrough, saving both time and effort in data management tasks.
After installing Kutools for Excel, follow these steps to filter cells with strikethrough formatting directly:
1. Select the column you wish to filter for strikethrough cells, then navigate to Kutools Plus > Special Filter > Filter Strikethrough.
2. A prompt box will appear, informing you of the number of cells matching the strikethrough criteria.
3. Click OK to filter; all cells containing strikethrough formatting will now be displayed.
Click Download Kutools for Excel and free trial Now!
Advantages: Kutools for Excel automates the process, requiring no manual flagging, formulas, or knowledge of VBA. This method is especially valuable for frequent Excel users who work with formatting-based data segmentation. Kutools also provides additional features for batch formatting and filtering tasks.
Alternative: Detect and filter strikethrough cells with Excel helper column and manual flag
In circumstances where VBA is restricted or not permitted—such as in certain work environments or when sharing files with users unfamiliar with enabling macros—you might prefer a formula-based approach using manual flagging.
Create a new column next to your data, and manually enter TRUE for rows that display strikethrough formatting, and FALSE otherwise. Although manual, this approach requires no special permissions and is suitable for small or occasional datasets.
After flagging rows, use the standard Sort and Filter features as described above to organize or isolate your strikethrough-marked data. This method is less error-prone in environments where VBA may not be available and ensures compatibility across various Excel versions.
Tip: Use the Find and Replace feature (Ctrl+F) with "Format" set to strikethrough to help locate and manually mark such cells quickly.
Related articles:
How to sort rows by odd or even numbers in Excel?
How to sort rows to put the blank cells on top in Excel?
How to sort data by the most frequent value in Excel?
How to sort email address by domain in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in