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.
Features
John asked if there was a way to create a list of all table names in the workbook, along with the underlying table reference. In this post, I’ll demonstrate how this can be accomplished with Power Query and an Excel formula. Objective Before we get into the mechanics, let’s confirm our goal here. We have…
Read More
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
This is the third post in the Treasure Maps series where we are talking about various ways to implement mapping tables. In this series, we are using mapping tables to help translate labels between the data and the report. In the first post, we used SUMIFS to pull the values into the mapping table. In…
Read More
This is the second post in the Treasure Maps series, where we are discussing various ways to implement mapping tables. In the first post, we covered SUMIFS. In this post, we’ll talk about Power Query. Overview Let’s say we have a list of transactions in a CSV file, like this: We would like to use…
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 received a question from Sam, who asked how to insert a variable number of rows (X rows) into a table based on a column value. That is, by the number specified in a column. In this post, we’ll accomplish this request with Power Query. Objective Before we jump into Excel, let’s confirm our objective.…
Read More