# Auditing & Editing M code (Power Query)

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

# 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