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.

FILTER

Perform Lookups with FILTER 2

By Jeff Lenning | September 29, 2020 |

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

Perform Lookups with FILTER 1

By Jeff Lenning | September 23, 2020 |

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

Dynamic Arrays 3

By Jeff Lenning | August 24, 2020 |

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

Dynamic Arrays Article

By Jeff Lenning | May 5, 2020 |

Microsoft has been hard at work updating Excel’s calculation engine. Depending on the version of Excel you’re using, you either have—or will have when you update—access to some capabilities that are sure to change how you think about and use Excel. I wrote about one of the new capabilities called Dynamic Arrays for California CPA…

Read More

Automatically Copy Transactions into Spill Ranges

By Jeff Lenning | March 13, 2019 |

Let’s say we have a data-entry table, where we type or paste new transactions. We would like a copy of each transaction to be directed to a specific worksheet, depending on the transaction type. For example, sales transactions should flow from the data-entry table into another worksheet that displays all sales transactions. Returns should flow…

Read More