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
In this post, we’ll learn how to add up a column of numbers based on the values in another column. For example, we are trying to analyze product sales based on average customer rating. That is, customers rate our products on a scale of 1 to 10, and so each product has an average rating…
In this post, we are going to use the data model to improve our reporting process. This is the fourth post in the Stop Wasting Time series, and this is where everything we’ve learned comes together. By the time we have finished this post, updating our report will be extremely easy. In fact, it will…
We are in the middle of a series called Stop Wasting Time, where we are learning about Excel features that help us improve how we update a report. In the beginning of our journey, we were updating the report manually. Now, as we’ve learned more, we’ve been able to automate parts of it. That is,…
This is the second post in the Stop Wasting Time series. In this post, we learn how Power Query can help us update our report in less time. After all, why would we want to spend more time than necessary updating it? That is called “wasting time” and we just don’t like wasting such a…
This is the first post in a series that shows how to transform a monthly report from being updated manually to automatically. Along the way, we’ll see how classic Excel features like tables and SUMIFS can help somewhat, and then how modern Excel features like Power Query and the data model can help even 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…
There are numerous lookup functions in Excel, and often, their capabilities overlap. And, that is true with many things in Excel…there are often multiple ways to accomplish any given task. Performing lookups is no exception. So, how are we supposed to know which lookup function to use? Often, Excel users try to decide between VLOOKUP…
When you need to perform a lookup, your instinct tells you to use VLOOKUP. But, when your lookup uses multiple conditions and columns, you may be inclined to use SUMIFS. However, when the value you need to return is a text string, rather than a numeric value, you are precluded from using SUMIFS since it only…
Of all the functions introduced in Excel 2007, 2010, and 2013, my personal favorite is SUMIFS. The SUMIFS function performs multiple condition summing. The function is designed with AND logic, but, there are several techniques that allow us to use OR logic instead. This post explores a few of them. Note: if your version of…
Hi guys! Just wanted to draw your attention to my April 2014 Journal of Accountancy article that discusses the power of mapping tables. If you’ve not experimented with this technique, it is worth checking out. In summary, a mapping table sits between the data and report sheets and enables you to translate labels and aggregate…