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

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

Sum a Column Based on Values in Another

By Jeff Lenning | May 22, 2019 |

In this post, we’ll learn how to add up a column of numbers based on the values in another column. For example, we are trying to analyze product sales based on average customer rating. That is, customers rate our products on a scale of 1 to 10, and so each product has an average rating…

Read More

Stop Wasting Time 4

By Jeff Lenning | May 6, 2019 |

In this post, we are going to use the data model to improve our reporting process. This is the fourth post in the Stop Wasting Time series, and this is where everything we’ve learned comes together. By the time we have finished this post, updating our report will be extremely easy. In fact, it will…

Read More