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.

SUMIFS

Stop Wasting Time 1

By Jeff Lenning | April 3, 2019 |

This is the first post in a series that shows how to transform a monthly report from being updated manually to automatically. Along the way, we’ll see how classic Excel features like tables and SUMIFS can help somewhat, and then how modern Excel features like Power Query and the data model can help even more.…

Read More

VLOOKUP Hack #10: Maslow’s Hammer

By Jeff Lenning | January 10, 2018 |

Psychologist Abraham Maslow wrote a line that is often referred to as Maslow’s Hammer. It goes something like this: “I suppose it is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail.” I’ve heard the simplified version more often: “If all you have is a…

Read More

VLOOKUP vs INDEX-MATCH

By Jeff Lenning | May 25, 2017 |

There are numerous lookup functions in Excel, and often, their capabilities overlap. And, that is true with many things in Excel…there are often multiple ways to accomplish any given task. Performing lookups is no exception. So, how are we supposed to know which lookup function to use? Often, Excel users try to decide between VLOOKUP…

Read More

Multi-Column Lookup with VLOOKUP and SUMIFS

By Jeff Lenning | September 17, 2015 |

When you need to perform a lookup, your instinct tells you to use VLOOKUP. But, when your lookup uses multiple conditions and columns, you may be inclined to use SUMIFS. However, when the value you need to return is a text string, rather than a numeric value, you are precluded from using SUMIFS since it only…

Read More

SUMIFS with OR

By Jeff Lenning | August 14, 2014 |

Of all the functions introduced in Excel 2007, 2010, and 2013, my personal favorite is SUMIFS. The SUMIFS function performs multiple condition summing. The function is designed with AND logic, but, there are several techniques that allow us to use OR logic instead. This post explores a few of them. Note: if your version of…

Read More

Recent JofA Article

By Jeff Lenning | April 24, 2014 |

Hi guys! Just wanted to draw your attention to my April 2014 Journal of Accountancy article that discusses the power of mapping tables. If you’ve not experimented with this technique, it is worth checking out. In summary, a mapping table sits between the data and report sheets and enables you to translate labels and aggregate…

Read More

Use the Column Header to Retrieve Values from an Excel Table

By Jeff Lenning | January 24, 2014 |

This post discusses ways to retrieve aggregated values from a table based on the column labels. Overview Beginning with Excel 2007, we can store data in a table with the Insert > Table Ribbon command icon. If you haven’t yet explored this incredible feature, please check out this CalCPA Magazine article Excel Rules. Frequently, we…

Read More

VLOOKUP on Two or More Criteria Columns

By Jeff Lenning | January 10, 2014 |

If you have ever tried to use a VLOOKUP function with two or more criteria columns, you’ve quickly discovered that it just wasn’t built for that purpose. Fortunately, there is another function that may work as an alternative to VLOOKUP depending on what you want to return. Multi-Column Lookup Objective First, let’s confirm our objective…

Read More
Excel Balance Sheet

Create a Balance Sheet with Excel

By Jeff Lenning | November 21, 2013 |

Excel Balance Sheet This post illustrates how to create a financial statement such as a balance sheet with built-in Excel features and functions. Our primary objective is to build a workbook that efficiently pulls values from the trial balance into the balance sheet. Overview For the purpose of this post, let’s assume we have a…

Read More

Multiple Condition Summing in Excel with SUMIFS

By Jeff Lenning | March 15, 2013 |

The SUMIFS function is for sure, without a doubt, one of my most favorite Excel functions of all time.  I’m sure I use it in well over half of my workbooks, and it is a must-know function for Excel users.  I’ll try to get you warmed up, but just know that this post only gets…

Read More