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

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

Retrieve First Occurrence After a Date

By Jeff Lenning | January 1, 2020 |

In this post, we’ll discuss one approach for retrieving the first occurrence (or min date) after a specified date. Let me back up. Michael asked for a way to find the first occurrence of an office visit (a date) after the date of being discharged from the hospital (and within 30 days). As this is…

Read More

Insert Repeating Items into a List

By Jeff Lenning | December 4, 2019 |

This post will demonstrate how to insert the same few items into a list and create a new row for each item (or each combination, if multiple items).  For example, let’s say we have a list of some sort … we’ll use a list of T-Shirts for this illustration. We have a few T-Shirt options…

Read More

Flash Fill Times

By Jeff Lenning | November 26, 2019 |

Flash Fill is a marvelous feature that is designed to fill values down based on an example in an adjacent column. That is, you have a column of values and then you enter an example of a derived value in an adjacent column. Flash Fill attempts to recognize the pattern from your example and then…

Read More

VLOOKUP Return Multiple Matching Rows and Columns

By Jeff Lenning | November 13, 2019 |

In this post, we’ll discuss a way to simulate using VLOOKUP to return multiple matching rows and/or columns. What do you mean by “simulate” Jeff? Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row … ignoring any…

Read More

Make Quick Work with these 10 Excel Skills

By Jeff Lenning | September 18, 2019 |

Excel is big … really big. It has hundreds of functions and features, but which of these have the most potential to save time and help us work faster? The answer depends on what you’re working on. For common accounting and finance tasks, I believe the following 10 Excel skills can help improve the efficiency…

Read More

Battle of Heavyweights: SUMIFS

By Jeff Lenning | July 24, 2019 |

This is the second post in the Battle of Heavyweights series, where we are comparing VLOOKUP to SUMIFS in order to understand their key differences. Knowing these key differences will help us determine which to use in a given workbook. In the previous post, we were introduced to VLOOKUP. Now, it is time to meet…

Read More

Fuzzy Match with Power Query

By Jeff Lenning | July 19, 2019 |

A few years ago, I wrote a post about how to perform fuzzy lookups using the “Fuzzy Lookup Add-In for Excel.” However, this capability is now available in Power Query!! Wait, what? Yes! Performing a fuzzy match is so much easier and far more intuitive in Power Query. This post walks through the basics ……

Read More