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.
Jeff Lenning
The past several posts have illustrated various ways to apply dynamic array formulas and their spill ranges. Kent asked if we could use spill ranges to create a dynamic chart to visualize the future value of an investment portfolio. For example, start with someone’s current portfolio value and then apply an assumed growth rate and…
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…
One reason we love PivotTables is that they automatically expand to include new items. For example, we have a PivotTable that summarizes transactions by account. Then next month, we paste new transactions into the data table and there is a new account. To get this new account included in our report, all we do is…
This is the second post in a series on dynamic array formulas and spill ranges. In this post, we’ll create a dynamic amortization schedule … and it is actually pretty fun! Before we had spill ranges, it was a hassle to update an amortization schedule when the number of periods changed. That is, you may…
A while ago, I wrote about creating dependent (aka cascading, dynamic, or conditional) drop-downs using data validation. This is where you have a primary drop-down, and the choices in the related secondary drop-down depend on the selection made in the first drop-down. Well, this process became MUCH easier with the introduction of dynamic array functions…
A few weeks ago I wrote a blog post on how to create a graphical calendar using a PivotTable. I received a few questions about how to insert event names into the calendar. Generally, PivotTable value fields support numeric values (such as day 1, 2, 3), and not text values (such as Filing Deadline or…
My Excel-friend John Michaloudis has compiled a podcast with tips from 17 Excel experts and MVPs. He does this annually, and this year he has put together a great collection of tips. This year, you’ll hear tips from: Bill Jelen John Michaloudis Mynda Treacy Jeff Lenning Jon Acampora Danielle Stein Fairhurst Ken Puls Kyle Pew…
Excel has numerous date-related features and functions. In this post, we’ll explore a few of them. We need an illustration that will tie them all together, so, we’ll create a graphical calendar with a PivotTable. Even if you don’t need a graphical calendar in your workbooks, the underlying mechanics that enable us to build it…
You’ve heard the terms “Power BI,” “Power Query” and “Power Pivot,” but maybe aren’t sure what they are. Good news! They are free tools from Microsoft and this article will talk you through them. And, while we’re at it, we’ll also talk about Pivot Tables and Pivot Charts. Power and Pivot
Let’s say you have a single-column list of transactions. You want to add up the amount values, which are on every say 5th row. This is a perfect task for Power Query. So, in this post, I’ll demonstrate how to set up a query that removes and keeps a defined number of rows in a…