13 Feb 2018

## 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? (more…)

08 Feb 2018

## 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. (more…)

01 Feb 2018

## 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. (more…)

29 Jan 2018

## 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. (more…)

26 Jan 2018

## 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! (more…)

21 Jan 2018

## 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 (more…)

17 Jan 2018

## 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. (more…)

13 Jan 2018

## 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. (more…)

11 Jan 2018

## 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! (more…)

07 Jan 2018