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

Excel Breakout Puzzle 7

By Jeff Lenning | August 12, 2019 |

The next Excel breakout puzzle is ready! And it’s a goodie 🙂 I realize fall doesn’t officially start for a few weeks, but, our kids start school next week and so it already feels like fall is upon us. Fall means back-to-school, cooler weather, falling leaves, and … american football. My son plays, and so…

Read More

Battle of Heavyweights: Round 1

By Jeff Lenning | August 7, 2019 |

It is time! In the first post, we met VLOOKUP. In the second post, we met SUMIFS. And now, the battle begins! Here is how it will work. The competition will consist of several rounds. In each round, we will use VLOOKUP and then SUMIFS to perform the same task. Then, we’ll determine who wins…

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

Battle of Heavyweights: VLOOKUP

By Jeff Lenning | July 24, 2019 |

This is the first post in a series called Battle of Heavyweights. In the series, we’ll use a boxing metaphor to compare VLOOKUP and SUMIFS. Now, as you read the next paragraph, use your best announcer’s voice and be sure to imagine the roar of a cheering crowd 🙂 “Ladies and gentlemen, welcome to the…

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

Stock Quotes CalCPA Article

By Jeff Lenning | July 15, 2019 |

Microsoft recently updated the built-in tools for retrieving stock quotes into Excel. If you’re typing closing prices into Excel manually, my recent California CPA Magazine article may be able to help you get it done faster. Specifically, it talks about how the Stock data type retrieves current quotes and related information, and how Power Query…

Read More

CONCAT or TEXTJOIN

By Jeff Lenning | July 10, 2019 |

In a previous post, we talked about how the CONCAT function joins values to create a combined text string. As a quick review, the CONCAT function is designed to replace the CONCATENATE function and supports range references as well as cell references. Well, as soon as we start playing around with CONCAT to join range…

Read More

CONCATENATE or CONCAT

By Jeff Lenning | July 1, 2019 |

Quick question: how do we combine or join values from multiple cells to create one big text string? We’ve been able to use the concatenation operator (&) or the CONCATENATE function for decades. The CONCATENATE function has been a great friend over the years, and has enabled some wonderful formulas. But, now there’s a new…

Read More

SWITCH or IFS

By Jeff Lenning | June 25, 2019 |

In the previous post, we learned that the SWITCH function allows us to look at a value and then return different results based on that value. But, what if instead of looking at one specific value, we need to consider multiple values in order to determine which result to return? Traditionally, we may have approached…

Read More

CHOOSE or SWITCH

By Jeff Lenning | June 3, 2019 |

For decades, Excel users have had the CHOOSE function and it has been able to power some wonderful formulas. But recently, we received the SWITCH function which provides much more flexibility. From a high level, both functions enable us to analyze a value and then return various results based on that value. But, SWITCH offers…

Read More