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
This is the third and final post in the PivotTable Text Values Alternative series, where we are discussing alternatives to displaying text values in PivotTables. In the first post, we used Power Query as an alternative. In the second post, we combined multiple text values. In this post, we’ll use the FILTER function as an…
Read More
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
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