In this week’s roundup, create a seating plan, do case sensitive lookups, learn Excel from Joel Spolsky, and more.
1. 7 Ways to Sum
Last week, I posted a video that shows 7 ways to sum in Excel. The quickest way to get a total is with the AutoSum button – just select the amounts, and a blank cell, then click the button. You can also see screenshots from Excel 3.0, and see how we did things in the old days (1990). Were you even using a computer, way back then?
2. Mark Completed Items With Strikethrough
Do you ever use the Strikethrough font format, to mark completed items in a list? It might look nice, but you can’t filter or sort by that formatting. If you have to use it, Sumit Bansal shows how to apply the strikethrough with conditional formatting, based on the contents of another cell. Then, you can use that other column for sorts and filters.
3. Case Sensitive Lookups
Usually it doesn’t matter if text is upper or lower case, when you’re doing a lookup in Excel. But, if you have to do a case-sensitive lookup, Chandoo show how to use EXACT, with other functions.
And be sure to read the story about Chandoo, and his Excel journey, on the PC Mag website.
4. Seating Plan
On the Tekhnologic blog, a classroom seating plan uses a drop down list of names with a little twist. In addition to the standard list of names, there is a random name at the top, and an option for leaving an empty seat
5. Date Calculations
Mike Alexander shared his list of 130 date calculations — more that you’ll ever need! For example, find the date of next week’s Tuesday, or the last Friday of the current month.
6. Learn Excel from Joel Spolsky
I’ve read many of Joel Spolsky’s articles, but this “You Suck at Excel” video is the first time that I’ve seen him teaching Excel tips. His goal is to teach his team members the “bare minimum of non-incompetence”. He was a program manager on Microsoft’s Excel team from 1991-1994, and led their VBA strategy.
Unfortunately, Mike Alexander predicts that Microsoft will eventually rename VBA to “Old People Magic”.
7. Excel Humour
Finally, for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets. Here’s one of my favourite tweets from this week’s collection.