My wife and I recently visited Rouge National Urban Park. We wanted to spend some time away from TV, internet and Excel. We did…but I discovered some data in the Park! Continue reading
Rouge National Urban Park
Dynamic Hyperlinks in Excel to HockeyDB
About 16 years ago I discovered HOCKEYDB. I was hooked! Why? Growing up in the late 70s and 80s I was a huge hockey fan but without cable or the internet information was limited. I had so many unanswered questions. Continue reading
Chandoo’s Sequence Number Puzzle
I saw this sequencing challenge from Chandoo “Generate a new sequence number when value changes“. His formula solution is great but I kept thinking “What if we have 20000 rows?“ Continue reading
Rank within Groups in Excel
How can we rank within groups in Excel? Most experts will tell you to use the SUMPRODUCT function. Why? It’s the only solution they know or maybe they’re concerned about compatibility. Is anyone still using Excel 2003? Let’s compare SUMPRODUCT and COUNTIFS. Continue reading
Video 00173 Dynamically Combine Data From Excel Tables
An ounce of prevention is worth a pound of cure but how does this apply to Microsoft Excel? In this post I will show you a common example. Too late for prevention? I’ll show you two solutions. Continue reading
Video 00174 Ranking Values into Tiers in Excel
Last year I started visiting some REDDIT Excel groups. A few weeks ago I saw an interesting Excel question and provided a simple two step solution. Continue reading
Change Text to Sentence Case
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
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