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

Excel Calendar Table

By Jeff Lenning | November 23, 2020 |

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

Dependent Drop Downs with FILTER

By Jeff Lenning | November 17, 2020 |

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

FILTER GOAT CalCPA Article

By Jeff Lenning | November 10, 2020 |

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

Treasure Maps 1

By Jeff Lenning | October 13, 2020 |

This is the first post in the Treasure Maps series, where we’ll be discussing the treasure (efficiency) that can be achieved by using mapping tables. Let’s review the big picture before jumping into the mechanics. Let’s say we export some data from a system, and it looks something like this (we’ll call this Point A):…

Read More

FILTER vs VLOOKUP, INDEX/MATCH, XLOOKUP, SUMIFS

By Jeff Lenning | October 8, 2020 |

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

Perform Lookups with FILTER 3

By Jeff Lenning | October 5, 2020 |

This is the third post in this series, where we are doing lookup tasks with the FILTER function instead of VLOOKUP. In the first post, we discovered that the FILTER function supports multiple return columns. In the second post, we discovered that it also supports multiple lookup columns. In this post, we’ll discover that it…

Read More

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

Historical Stock Quotes with STOCKHISTORY

By Jeff Lenning | September 9, 2020 |

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

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