Excel University Blog
Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.
SUMIFS
A mapping table is a handy way to automatically translate labels between systems and reports. As with just about anything in Excel, there are many ways to implement a mapping table. For example, we could create a helper column to store the amounts with the SUMIFS function as I discussed in this Journal of Accountancy…
Read More
This is the first post in the Treasure Maps series, where we’ll be discussing the treasure (efficiency) that can be achieved by using mapping tables. Let’s review the big picture before jumping into the mechanics. Let’s say we export some data from a system, and it looks something like this (we’ll call this Point A):…
Read More
In this video, which is part of Microsoft’s Modern Excel webcast series, we’ll cover the basic syntax of FILTER and then see how it compares to VLOOKUP, INDEX/MATCH, XLOOKUP, and SUMIFS. The question is: can FILTER accomplish the tasks we typically associate with these powerful Excel functions? Note: not all versions of Excel contain the…
Read More
This is the second post in the Dynamic Arrays series. In the first post, we talked about how formulas can return multiple values, the resulting spill range, and two dynamic array functions. In this post, we’ll talk about how to refer to the spill range with other formulas. Video Spill Range The spill range includes…
Read More
Microsoft has been hard at work updating Excel’s calculation engine. Depending on the version of Excel you’re using, you either have—or will have when you update—access to some capabilities that are sure to change how you think about and use Excel. I wrote about one of the new capabilities called Dynamic Arrays for California CPA…
Read More
In this, our final analysis, we will briefly recap each of the 8 rounds and confirm we understand the key differences between these two incredible functions. Then, we’ll confirm we know when and how to apply each in practice. Let’s get to it. Winner? So, which function is better? Which one should we always use?…
Read More
The purpose of this round is to examine the ability of each function to perform a range lookup. A range lookup is where we aren’t looking for an exact matching value, but a value that falls between a range of values. So let’s get to it. Round 8 Each sales reps earns a bonus…
Read More
The purpose of this round is to examine the issue of multiple lookup columns. Often, we have a single unique ID that we can use as the lookup value. When that is true, great. But, what happens when we need to use multiple columns for our lookup value? That is exactly what this round is…
Read More
Welcome to Round 6! In this round, we’ll discover how both functions behave when we insert a new worksheet column. In other words, does inserting a new worksheet column break the formula? Or, will it keep working? Let’s find out … the round begins now. Round 6 We would like to retrieve some information…
Read More
Round 5 begins now! In this post, the goal is to understand the results of each function when no matching rows are found. That is, when the lookup value is not found in the data table. Do the functions return an error? The closest match? 0? Well, that is why we are here, so let’s…
Read More