Quickly delete/hide records (rows) with Strikethrough format by using Find and a couple of simple techniques.

I had encountered a file with thousands of records (rows) that keeps track of details of different jobs.  Finished jobs were identified by Strikethrough format.  In other words, records without Strikethrough are unfinished jobs requiring our attention.

The challenge is…. Tens of hundreds of records (rows) with strikethrough are sitting irregularly in between of records (rows) without strikethrough.   Can you imagine how difficult it is to focus on unfinished jobs?

Look at the mini example below:

The objective is to hide or delete all records (rows) with a strikethrough format.  i.e. only active records stay.

In Excel 2007 / 2010, we can apply filter by Font colors or Cells colors, that would be great if we can also filter according to other format.  However, this is no such thing yet.

That’s why I am going to show you the few steps to do it.

CTRL F to open the Find dialogue box –> click on Options>>

(Note: if you want to find cells with strikethrough for a particular column (or range), select the column (or range) beforehand)

Click on Format –> Format…  (you may also try “Choose Format From Cell…” if you are looking for specific formats)

Go to Font tab –> Check Strikethrough  (pls ensure you have select only Strikethrough but no any other format) –> OK

Now you see  format in the Preview.  (DO NOT input anything in “Find what:”.  In this way, Excel will find only cells with the format your specified, i.e. Strikethrough.)

Click Find All

The Findings are displayed in the lower part of the dialogue box…

Press CTRL A (the shortcut for Select ALL); make sure to do this inside the result found

All cells with Strikethrough (the 9 cells found) are being selected

Close the Find dialogue box. (note: pay attention not to click on any cell on the worksheet, otherwise the selection may change)

To Hide the rows with cells selected, simply press CTRL 9

or use the icons on the ribbon as showed below:

Here’s the result: (note: if you apply filter, all the hidden rows will reappear)

To Delete rows with cells selected, Go to Home Tab –> Delete –> Delete Sheet Rows as shown in the screenshot below:

In case you are still using Excel 2003

Here’s the result

Isn’t is easy?

As a final note, DO NOT use strikethrough format as a way to distinguish inactive records from others as you CANNOT filter strikethrough.  Why don’t have a field of “Status”?????

A well-thought designed worksheet helps every one who works with it.

Watch it in action on my YouTube channel: