Before you analyze your data it’s important to decide if you need to unpivot it. Why unpivot? What’s unpivot? Are there exceptions? Continue reading
Why and How to Unpivot Data
Rearranging Data
Rearranging data is initially about recognizing the pattern. This screenshot is from Ken Puls. I instantly saw the steps to rearrange the data (thanks to Ken’s online course!) Continue reading
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 function. Continue reading
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 expand it for a more complex case. Continue reading
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! Continue reading
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. Continue reading
Harvard CEO Ranking
Harvard Business Review ranked the top 100 CEOs with three weighted categories (80%, 10%, 10%). I downloaded their data and added some formulas to modify the weighting. Does it make a difference? Continue reading
The Learning Curve in Excel
I’ve had this ‘Business Formulas’ book sitting around for years. I decided to explore the learning curve formula: (a) create it in Excel and (b) think about the factors that go into learning Excel.
Continue reading
Excel Sorting Challenge
In this guest post Alan Murray (computergaga) compares two solutions to a challenging data sorting issue. I know my preference, what about you?
Continue reading
Concatenate Values to Create a Key
Excel users who don’t know this concept waste hours manually looking through their data. A filter helps but creating a valid lookup key is what Mr Carlson needs! Continue reading