Publications

Author and award winning instructor Jeff Lenning CPA has written several books and 40+ articles featured in various publications. We've compiled them all in a single place for easy reference.

LET Function

By Jeff Lenning | July 6, 2021 | Comments Off on LET Function

Often, our Excel formulas are simple and short. As such, they are easy to read, understand and maintain over time. An example of such a formula is: =SUM(A1) But, as the complexity of our workbooks increases, so can the complexity of our formulas. Sometimes, we end up with unwieldy formulas that are long and  difficult…

Read More

LAMBDA Function

By Jeff Lenning | May 13, 2021 | Comments Off on LAMBDA Function

Excel has a lot of useful functions: FILTER, SUMIFS, VLOOKUP, XLOOKUP, SUM, SUBTOTAL and more. All in, Excel has about 500 functions, so it’s likely there’s one that returns the result you need. But now and again you may find yourself where there isn’t a built-in function that returns the value you are trying to…

Read More

FILTER Function

By Jeff Lenning | November 2, 2020 | Comments Off on FILTER Function

Question: What is the best Excel function? Typical responses are VLOOKUP, INDEX, MATCH, XLOOKUP and SUMIFS, which are among Excel’s highlights. Follow-up question: If there was a function that could do the work of these, would it be the Greatest of All Time (GOAT)? Well, the FILTER function is too new to declare it the…

Read More

Dynamic Arrays

By Jeff Lenning | June 19, 2020 | Comments Off on Dynamic Arrays

Each new version of Excel includes a variety of minor updates that are nice, but not earth shattering. They can be helpful, but don’t necessarily have a big impact on our workbooks. Indeed, if you ignore them, you can continue using Excel just like before and you’ll probably be just fine. However, we occasionally receive…

Read More

Using Excel to budget faster, easier

By Jeff Lenning | May 27, 2020 | Comments Off on Using Excel to budget faster, easier

Microsoft Excel is always moving forward, which means you need to, as well. In this article, I’ll discuss an Excel technique that can be used to make budgeting faster and easier ⁠— a prize for anyone willing to learn it. The overall idea is to create several budget methods and allow the user to pick…

Read More

Welcome X Functions – XLOOKUP

By Jeff Lenning | February 11, 2020 | Comments Off on Welcome X Functions – XLOOKUP

VLOOKUP is perhaps the most iconic function in Excel and users have come to adore it. When you search for a list of top Excel functions, VLOOKUP is there. And has been … for decades. As great as VLOOKUP is, it has limitations. For example, column order matters. It was designed to look for a…

Read More

Make Quick Work with 10 Excel Skills

By Jeff Lenning | September 18, 2019 | Comments Off on Make Quick Work with 10 Excel Skills

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

Get Current and Historical Stock Prices into Excel

By Jeff Lenning | July 15, 2019 | Comments Off on Get Current and Historical Stock Prices into Excel

Microsoft recently updated the built-in tools for retrieving stock quotes into Excel. If you’re typing closing prices into Excel manually, this article may be able to help you get it done faster. Specifically, we’ll talk about how the Stock data type retrieves current quotes and related information, and how Power Query can retrieve historical stock…

Read More

Power and Pivot

By Jeff Lenning | January 7, 2019 | Comments Off on Power and Pivot

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 column will talk you through them. And, while we’re at it, we’ll also talk about Pivot Tables and Pivot Charts. Let’s zoom out for a moment for…

Read More

Data Model Walkthrough

By Jeff Lenning | July 10, 2018 | Comments Off on Data Model Walkthrough

Have you heard people talking about Power Pivot and the data model, but you aren’t exactly sure what they are or how to use them? Well, in this article, we’ll use them to build a basic PivotTable. Before we jump into Excel, here’s some super-fast background. Although Excel is used for many different things, workbooks…

Read More