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.
Functions
Often, our Excel formulas are simple and short. As such, they are easy to read, understand and maintain over time. But, as the complexity of our workbooks increases, so can the complexity of our formulas. Sometimes, we end up with unwieldy formulas that are long and difficult to understand and maintain. One cause for such…
Read More
Excel has a lot of useful functions: FILTER, SUMIFS, VLOOKUP, XLOOKUP, SUM, SUBTOTAL and more. All in, Excel has about 500 functions, so it’s likely there’s one that returns the result you need. But now and again you may find yourself where there isn’t a built-in function that returns the value you are trying to…
Read More
This is the second post in the Slow to Fast series, where we are taking incremental steps to improving our reconciliations. In the first post we improved a purely manual process with conditional formatting. That approach works when the lists are relatively small, on the same sheet, and when we are only concerned with a…
Read More
The family of IFS functions, such as SUMIFS and COUNTIFS, use AND logic when evaluating multiple conditions. In other words, all conditions must be true to be included. And generally, this logic works well. However, there are situations when we’d like to use OR logic instead. That is, any of the conditions can be true…
Read More
John asked if there was a way to create a list of all table names in the workbook, along with the underlying table reference. In this post, I’ll demonstrate how this can be accomplished with Power Query and an Excel formula. Objective Before we get into the mechanics, let’s confirm our goal here. We have…
Read More
When you need to summarize values by date groups, a calendar table can be helpful. While Power Pivot has a button that will automatically create a new date table (shown in this post), Excel doesn’t have a similar command. So, in a recent Q&A session, Michael asked how to get that Power Pivot calendar table…
Read More
This post shows how to create multiple dependent drop downs using the FILTER function. These are also known as cascading or conditional drop downs, where the choices in a drop down depend on the selection made in a previous drop down. The technique presented enables you to create as many drop downs as you need,…
Read More
Question: What is the best Excel function? Typical responses are VLOOKUP, INDEX, MATCH, XLOOKUP and SUMIFS, which are among Excel’s highlights. Follow-up question: If there was a function that could do the work of these, would it be the Greatest of All Time (GOAT)? Well, the FILTER function is too new to declare it the…
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