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

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 (row) 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. 

This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

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

  1. Arjun Chhabra says:

    Very helpful! Read many articles, never found a solution this effective and simple. Thanks!


  2. Ireen says:

    Great article. Very handy, helped me a lot. Thank you!!!


  3. Stuart Coker says:

    I couldn’t resist commenting. Perfectly written!


  4. Phyllis says:

    Wonderful blog! I found it while browsing on Yahoo News.
    Do you have any tips on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem
    to get there! Cheers


  5. ashish mehra says:

    If you want to know more about “How To Delete Only Filtered Rows Without The Hidden Rows In Microsoft Excel”, check this link ……..



  6. Pingback: Remove cells wiith Strikethrough

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s