Recent Tip Posts

• Largest Number Inside Alphanumeric String A work colleague shared this challenge: extract the largest number from this text  as420lkjs09nsdk2324lkjsd099 !  The answer is 2324. How do we...

  ---

• Dynamic Ranges Using Index Function Robert H. Gascon's comment from my last post convinced me that I should be using the INDEX function for dynamic ranges not the volatile OFFSET...

  ---

• What is this formula doing? =SUMPRODUCT(MID(G2,{1;2;3},1)*1)  I found this formula in a notebook. Can you understand it's purpose? I'll explain it in this post. I'll also...

  ---

• False Friends in Excel The term False Friends is used in various fields including linguistics and chess. In this post I'll explain how it applies to Microsoft Excel!...

  ---

• Excel Running Template My super fit cousin Joe Perry contacted me with a few questions about an Excel spreadsheet he uses to track his running. ...

  ---

• Allocating costs In this post I'll show you how to allocate expenses (or any number) over a period of time. I will use years but it could also be months, weeks or...

  ---

• Ken Puls Power Query Challenge #2 In this post I'll review my solution to Ken Puls' Power Query challenge #2. Online challenges are fun and it's a great way to learn from others....

  ---

• free data from Data.world MOMA artists In February my wife and I were planning a trip to New York City. I stumbled upon an interesting data-set that MOMA had shared with data.world. ...

  ---

• Normalize Data using Excel Get & Transform In my last post I used formulas to solve a messy data disaster. In this post/video I'll show you how to use Excel's amazing Get & Transform...

  ---

• Data Disaster: non normalized data Imagine a weekly task of sending out a simple report. To do this you rely on data from someone else. When the layout of the data changes chaos...

  ---