Contextures News

Excel Formula Challenge

March 21, 2017

Solve the formula challenge, 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.

Name Fix Challenge

If people enter their names in a sign-up form, you might end up with a mixture of upper and lower case letters. You can see an example in the screen shot below.

This week's challenge is to clean up that list. Using Excel formulas, make these changes to the list:

  1. Show the corrected name in column B, with only the first letter of each name in upper case. For example, Fred Jones instead of FRED JONES.
  2. Add an X in column C, to mark the names that were fixed.
  3. In cell E1, show the number of names that were fixed.

You can type your own list, or download my sample file. I'll show you my formulas in next week's newsletter. For inspiration, there are videos and links on my Functions page.

fix the names challenge

Freeze and Zoom

If you're working with large sheets in Excel, you might need to zoom out, to see more of the data. Or, zoom in, to see the details. The Freeze Panes feature helps too -- it locks cells at the top and/or the left.

You can use the commands on Excel's View tab, to zoom or freeze, one sheet at a time. If you want to quickly set up all the sheets at once, you can use the macros on my website's Freeze and Zoom Sheets page

freeze and zoom macros

The page also has a chart that shows what sections get frozen, based on which cell(s) you have selected.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Start/End - If a person participated in several activities, when did the first one start, and the last one end? Ken Puls shows how to calculate the dates in Power Query. Or, in a normal Excel table, use a pivot table to show the Min start date, and the Max end date. (Level - All)

Programming - Whether you're programming in Excel, or something else, Andrew Wulf says there are 6 basic goals for every project. Are some of your macros still being used, many years after you wrote them? (Level - Intermediate/Advanced)

Also see: My Excel Products || Excel Events || Weekly Excel Humour

Old School

Our granddaughter was visiting last week, and we watched Little House on the Prairie. I showed her this old picture of her dad, at the Old Britannia Schoolhouse, here in Mississauga. It's a one-room school from a similar time period (1860s), and a fun field trip for students. He had to stand on the log of shame, but least he didn't have to wear the Dunce cap!

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.




Get weekly Excel tips from Debra






Last updated: July 21, 2017 9:38 AM