Power Query is a powerful and intriguing tool. However, there are stumbling blocks that make it challenging. Let’s audit and then modify some M code! Continue reading
Auditing & Editing M code (Power Query)
Post Category → Tips
Subtotal helper column
This formula =COUNTIFS($A$7:$A$309,$AK4,$B$7:$B$309,1) considers all rows within the given range when counting. What if we want to exclude filtered and/or hidden rows? Continue reading
3 Newly Added Charts in Excel 2019 You Must Learn to Create
This Guest Post is contributed by Aprajita Sharma from ExcelChamps. Learn about 3 new charts available in Excel 2019 (or with a Office 365 subscription). Continue reading
Partial Match Count with a Condition
Once again I discovered a formula that I had written down in a notebook. What is it doing? Is there an easier way to solve this? Continue reading
Sorted Data Validation List
This post from ExcelFORO explains how to use VBA to create a sorted data validation list. It’s a great solution! Are there other ways to solve this? Continue reading
Weighted Average
I’ll show you the weighted average formula, how it calculates, and I’ll prove that the answer is correct! Continue reading
Countifs confirms Vlookup
So many times vlookup (or index & match) saves the day and gets your answer! ….wait…are you sure it’s correct??? Read this post to see why you should use countifs to confirm your vlookup. Continue reading
Why and How to Unpivot Data
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
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
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