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

Dynamic Arrays 2

By Jeff Lenning | August 17, 2020 |

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

Dynamic Arrays 1

By Jeff Lenning | August 11, 2020 |

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

Find Last Occurrence

By Jeff Lenning | July 28, 2020 |

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

CONVERT Units of Measure

By Jeff Lenning | July 22, 2020 |

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

Initial STOCKHISTORY Announcement

By Jeff Lenning | June 10, 2020 |

Microsoft just announced a new Excel function called STOCKHISTORY. This function provides a way to retrieve historical stock quotes directly into Excel. As it is being rolled out over time, I haven’t had a chance to use it yet. But as soon as my copy of Excel receives the function, I’ll write a post about…

Read More

List of Worksheets in a Drop Down

By Jeff Lenning | May 27, 2020 |

In this post, I’ll answer a question I received from Sara about how to create a drop down with each worksheet name and reference it in a formula to retrieve values from the selected sheet. As this is Excel, there are a variety of alternatives to accomplish this. In this post, I’ll demonstrate one such…

Read More

Excel and Budgeting

By Jeff Lenning | May 19, 2020 |

I recently wrote an article for the Minnesota Society of CPAs about a technique that helps you create budgets faster. Even if you don’t prepare budgets, the underlying features and functions used can be applied elsewhere. In summary, we use a data validation drop-down to provide several Budget Methods. Once you select a budget method…

Read More

Split Amount into Monthly Columns

By Jeff Lenning | May 12, 2020 |

Let’s say you need to take an amount and split it evenly into monthly columns. For example, perhaps you need to recognize revenue over time. Or, perhaps you have spent some money and you need to allocate the expense over time. There are other illustrations, but the basic idea is that you have a total…

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

Remove Duplication in Formulas with LET

By Jeff Lenning | April 28, 2020 |

Have you ever written a formula that repeats the same expression multiple times? I know I have. One simple example is IF((A1-B1)=0,0,A1-B1). Notice how the expression A1-B1 is used twice? This is a simple example of duplicating an expression within a formula. In general, we want to try to remove/minimize such duplication to make the…

Read More