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.
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. (Just click the Make Backup button in the Excel Tools add-in, if you have that)
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:
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.
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!
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)
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!
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 @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:53 AM