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.
Excel
This is the second post in a series where we are talking about how FILTER can be a nice alternative to traditional lookup functions such as VLOOKUP. In the first post, we saw how the FILTER function supports multiple return columns (it can return values from multiple columns). In this post, we’ll see how the…
Read More
This is the first post in a series where we’ll talk about how to use the FILTER function as an alternative to lookup functions including VLOOKUP. Why? Well, as we’ll discover in this series, the FILTER function offers several key benefits. For example, FILTER supports: Multiple return columns Multiple lookup columns Multiple matching row values…
Read More
Historically, we have created calculated columns by writing formulas. In this post, we’ll see that in some cases, clicking a few buttons in Power Query can be a nice alternative. Objective Before we get into the mechanics, let’s confirm our objective. Let’s say we have a table full of transactions, like this: We would like…
Read More
Let’s say we want to retrieve historical stock quotes for a specific security into Excel. Since this is Excel, there are of course several options. Before the STOCKHISTORY function, we could pull data from relevant web pages using Power Query (which I’ve written about before here). But, the most current versions of Excel now have…
Read More
This is the third post in the Dynamic Arrays series. In the first two posts, we discussed the spill range and the spill reference operator. If you’ve enjoyed the first two posts, I think you’ll enjoy this one even more because we get to talk about the FILTER and XLOOKUP functions. Video Overview So far,…
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
During the years we have been using Excel, we have come to understand that a formula calculates a value. Meaning, a single value. Right? Like … we write a formula, hit Enter, and the result is displayed in the cell. Easy … that is how we have been using Excel for decades. Multiple Values But…
Read More
I am excited to announce this year’s Excel University scholarship winners! These scholarships are supported by the Excel University Alumni Association, so thanks to all of you who have helped make these awards possible. I’d also like to that Safe Harbor LLP for their generous contribution to the scholarship fund this year! Each will receive…
Read More
I was recently asked how to find the last occurrence of a value in a column. Although there are multiple ways to accomplish this, in this post, I’ll talk about how to use XLOOKUP. Objective Before we jump into Excel, let’s confirm our objective. Let’s say we have a bunch of transactions that look like…
Read More
There are times I need to quickly convert a quantity from one unit of measure to another (inch, foot), or from one system of measurement to another (metric, imperial). For example, when I am entering meals into MyFitnessPal, preparing a meal in the kitchen, or working on a project in the garage. When I need…
Read More