As Puneet Gogia mentions in his post, we can change text using UPPER, LOWER and PROPER functions but we don’t have a SENTENCE function to capitalize the first letter of each sentence. Puneet solves it with a very interesting formula. How exactly does it work? Let’s audit it! Continue reading
Author Archives → Kevin Lehrbass
An off switch! What an excellent idea!
Some Excel models have so many complex formulas that they just can’t calculate any more. These models often need to be re-created. Some models can be saved by making formulas more efficient. One technique is to add an OFF SWITCH to heavy formulas. Continue reading
Video 00172 Unique Values for Data Validation Drop Down
We need to extract unique values from a list to be used in a data validation drop down list. There are many ways to solve this. The best solution depends on asking the right questions. Continue reading
Video 00158 Keyword Data Validation List
Is your data validation list is so long that the endless scrolling makes you want to close Excel and never use it again? This post will review several solutions to this problem. Continue reading
Becoming a Microsoft Excel master!
Do you want to become a Microsoft Excel master? The first step might be to convince yourself that it’s possible (it is!). Then, you need some good material and determination! Continue reading
Stand-up comedy about Spreadsheets
Stand-up mathematician Matt Parker did a comedy routine about spreadsheets. Hmm….why not create a post about this? How can I make it interesting? My quest to find an interesting angle led me to….well…creating a YouTube Transcript spreadsheeet! Continue reading
Video 00171 Create Christmas Lights Using Excel Sheets
Did you know that an Excel UDF can change the color of a sheet tab? David Hager’s post shows us how to do this! Somehow I came up with the idea to create Christmas Lights using Excel sheet tabs! Continue reading
Game Review: The Worksheet Saga
On Christmas day, when no one was looking, I peeked at twitter and saw a tweet about an Excel based game called The Worksheet Saga. My first thought was “oh…it’s based on VBA. I wonder if the code is unlocked?”. Read more to see how this game works and how I hacked it to win 🙂 Continue reading
Who is the blame for Subway Delays?
Subway riders cringe when a delay is announced. Emotions range from mild annoyance to outbursts of anger. Delays inconvenience us and reduce productivity. By default we blame subway management (TTC in Toronto). Is this fair? Wouldn’t it be nice if we had data to determine what causes the delays? Continue reading
Look for keywords inside of a column of text values
On October 17 we celebrated spreadsheet day. It was a day full of excitement around the globe! Crowds gathered around spreadsheets…dancing and drinking occurred into the wee hours of the morning. Part of the celebration included a lookup formula challenge from Debra Dalgleish (spreadsheet day creator). Continue reading