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.

Posts

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

Speed Loop 3: Realize Gain

By Jeff Lenning | April 14, 2020 |

This is the third and final post in the Speed Loop series. In the first post, we identified and prepared our recurring-project workbooks for speed. In the second post, we increased their value by improving efficiency and accuracy. Now it is time to leverage our time savings. The final step is: Realize Gain. Before we…

Read More

Speed Loop 2: Appreciate Assets

By Jeff Lenning | April 6, 2020 |

This is the second post in the Speed Loop series, where I’m sharing the process that helped me dramatically improve efficiency. In the first post, we covered the Invest Time stage. We identified our recurring-project workbooks. We inserted our lead sheets. We are now ready to move on to the second stage: Appreciate Assets. Let’s…

Read More

Speed Loop 1: Invest Time

By Jeff Lenning | March 30, 2020 |

To work fast in Excel, you need two things: process and skills. Having one without the other means you may not be maximizing efficiency. By process, I mean the overall framework, strategy, or approach you use to optimize your workbooks. By skills, I mean Excel skills like XLOOKUP, SUMIFS, PivotTables, and Power Query. Both process…

Read More

How are you coping with this?

By Jeff Lenning | March 23, 2020 |

When I started this blog 7 years ago, my plan was to write about Excel ONLY. No other topics. Ever. But in all honesty, I’m a little preoccupied with COVID-19. So, I want to take one post to share how I’m coping with it and ask how you are coping with it. I hope that…

Read More

XLOOKUP Webinar

By Jeff Lenning | March 16, 2020 |

XLOOKUP is the next-gen lookup function designed to address the limitations of VLOOKUP and related workarounds. It combines the capabilities of numerous functions including VLOOKUP, INDEX, MATCH, HLOOKUP, and IFERROR. Many have asked me to teach this via webinar. So, I created this short webinar that covers the basics 🙂 This on-demand webinar is about…

Read More

Count Between Dates

By Jeff Lenning | March 4, 2020 |

Let’s say you measure something with a duration that can span multiple days. For example, a hotel guest can check in for multiple days, a patient can be in a hospital for multiple days, a widget can be in a specific manufacturing stage for multiple days, a book can be checked out for multiple days,…

Read More

Insert and Name Sheets from Cell Values

By Jeff Lenning | February 24, 2020 |

In this post, I’ll share a short snippet of VBA code that creates new worksheets based on the names stored in cell values. This can be helpful when you have a list of departments, and want to create one worksheet for each department. Or a list of accounts, or employees, or regions, and so on.…

Read More