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.
MATCH
In this post, we’ll learn how to format a range to automatically highlight any items found in a list. In other words, we will learn how to highlight transactions that have a status value that matches any of the status values in a specific list, such as Pending or Processing. This illustration uses status values,…
Read More
Hello and welcome to our latest tutorial! Today we’re diving into a common Excel challenge: finding the last date in a column. This is especially useful if you add rows to your data regularly. Let’s explore three different exercises to solve this problem. Video Tutorial Exercise 1: Using the TAKE Function in a Table Step-by-Step…
Read More
The post was written to answer a question I recently received: How can we retrieve the last row from a PivotTable? Buckle up as we solve this challenge by nesting two Excel functions into a single formula. Video Step-by-step Let’s visualize the essenence of the question. We have a PivotTable in our worksheet, perhaps something…
Read More
If you’ve used the VLOOKUP function before, you know that the lookup column is expected to be in the leftmost column within the range. Meaning, it’s going to look for a value in the leftmost column within the range and once it finds its match, it will shoot to the right to return the related…
Read More
In this article, we’ll show how to easily create and update Excel reports from QuickBooks. But, let’s back up a second. When QuickBooks has the report you want, awesome! But, when it doesn’t, we often turn to Excel. This can happen when QuickBooks doesn’t have all of the data you need for the report, for…
Read More
Looking to make work easier? Instead of trying to do it all, focus on learning these nine useful advanced Excel functions.
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
I recently wrote an article for the Minnesota Society of CPAs about a technique that helps you create budgets faster. Even if you don’t prepare budgets, the underlying features and functions used can be applied elsewhere. In summary, we use a data validation drop-down to provide several Budget Methods. Once you select a budget method…
Read More
Psychologist Abraham Maslow wrote a line that is often referred to as Maslow’s Hammer. It goes something like this: “I suppose it is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail.” I’ve heard the simplified version more often: “If all you have is a…
Read More
This is the 4th post in the VLOOKUP Hacks series. The first three posts have explored the 4th argument. Now, we are going to explore a hack for the 3rd argument. In this post, we’ll hack the 3rd argument so that it references column labels instead of the column position. Check it. Note: depending on…
Read More