Contextures News

Excel Formula Challenge

November 1, 2016

Quickly find and delete Excel rows, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Find & Delete Rows

If you're cleaning up a worksheet, you can use Excel's Find All and Delete Sheet Rows commands to make the job easier. Be sure to make a backup copy of your file, before making drastic changes like this.

For example, product names are in column H, and I want to delete all the orders for Bran. Watch the short video on my blog, or follow the steps below:

  • Select column H, where the Product is listed
  • On the Ribbon’s Home tab, click Find & Select, and then click Find.
  • In the Find and Replace dialog box, type "Bran" in the Find What box.
  • If you want to Match Case, to Match Entire Cell Contents, click the Options button and select those settings
  • Click Find All, to see a list of cells with "Bran", then scroll through the list, to see what was selected
  • Select any item in the list, and press Ctrl+A, to select the entire list. That also selects all the "Bran" cells on the worksheet.
  • On the Ribbon’s Home tab, click the arrow beside Delete, and then click Delete Sheet Rows.

There is no confirmation message -- all the selected rows are deleted, and the other product orders remain on the worksheet. You can Undo right away, if you don't like the results.

find and delete rows

The Count is Wrong

Would you like an Excel challenge? Last week, someone emailed me their Excel file, because a simple COUNTIF formula wasn't working. To show you the problem, I made a sample file that you can download. In column A, 100 cells have a one-letter code -- A, B or C.

To get a total count for each code, there are COUNTIF formulas. When the counts are totalled, the result is 99, instead of 100. How would you troubleshoot that, to find the problem? And how would you fix it?

I'll show my answer in next week's newsletter!

countif problem

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Charts - Ann K. Emery has updated her Data Visualization Checklist, so download the new version. It will help you check your charts, to make sure that they're clear and effective. (Level - All)

Excel Tables - If you like to show a Totals row when you're using Excel Tables, it can be a little tricky to add new rows at the end. Zack Barresse shows how to include new rows below the total row manually, or with a bit of code. (Level - Intermediate)

Also, see all my Excel products on my Contextures website, and my weekly collection of humorous Excel tweets.

After the Frost

We had frost last week, so almost everything in the garden is dead now, except these two green tomatoes, a few marigolds, and the weeds, of course. They're very hardy! You can see the poor sedum in the background, with all its flowers turned brown. On the bright side, it isn't snowing yet!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.


Last updated: May 24, 2021 7:34 PM