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

Basic Excel Time-Savings Tips

By Jeff Lenning | January 24, 2018 |

If you like shortcuts, features, and tools that can save you time in Excel, I’d like to call your attention to a Journal of Accountancy article that includes tips from several Excel experts. I’m honored that my tips were included in the article 🙂 The article includes these main categories of tips: Keyboard shortcuts Quick…

Read More

Comparing Lists with Conditional Formatting

By Jeff Lenning | January 17, 2018 |

Comparing lists is a fairly common task in Excel, and as with anything, there are many ways to approach it. When we compare two lists, we are essentially trying to find out which names appear in both lists, or, which names appear in only one list. Tony posted a blog comment about how to perform…

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 Hack #9: Partial Match

By Jeff Lenning | January 3, 2018 |

Let’s say we want VLOOKUP to match the lookup value “North Region” with “North Region Subtotal” stored in the lookup range. We started this series by looking at the 4th argument. We know it can be TRUE or FALSE. FALSE means exact match and TRUE means approximate match. So, what exactly is an approximate match?…

Read More

VLOOKUP Hack #8: Extra Spaces

By Jeff Lenning | December 12, 2017 |

Here’s a VLOOKUP question for you: What happens when the lookup value includes extra spaces? For example, a few trailing spaces, or leading spaces, or, extra spaces in the middle of the text string. When the lookup value has extra spaces, but the lookup range values don’t, VLOOKUP runs into problems. This leads us to…

Read More

VLOOKUP Hack #7: Different Columns

By Jeff Lenning | December 6, 2017 |

In this post, we’ll continue hacking the 1st argument. Here is the basic setup. We are building a report. The report structure uses multiple columns to store the report labels, such as sales, cost of sales, selling expenses, and so on. So, can we write a formula that works when the lookup values are stored…

Read More

VLOOKUP Hack #6: Different Data Types

By Jeff Lenning | November 29, 2017 |

We started this blog series by examining the 4th argument. We’ve since hacked the 3rd argument and then the 2nd argument. Now, as you can imagine, it is time to hack the 1st argument. There are many fun hacks we can do with the 1st argument, so, I’ll cover them over a few posts. In…

Read More

VLOOKUP Hack #5: Different Tables

By Jeff Lenning | November 15, 2017 |

We are right in the middle of a blog series called VLOOKUP Hacks. We started by exploring the 4th VLOOKUP argument. Then we hacked the 3rd argument. Now, as you may have imagined, it is time to hack the 2rd argument. In this post, we’ll allow the user to retrieve values from different lookup tables.…

Read More

VLOOKUP Hack #4: Column Labels

By Jeff Lenning | November 8, 2017 |

This is the 4th post in the VLOOKUP Hacks series. The first three posts have explored the 4th argument. Now, we are going to explore a hack for the 3rd argument. In this post, we’ll hack the 3rd argument so that it references column labels instead of the column position. Check it. Note: depending on…

Read More

VLOOKUP Hack #3: Exact Match

By Jeff Lenning | November 1, 2017 |

In this post, the 3rd in the VLOOKUP Hacks series, we’ll dig even deeper into the 4th argument. We’ll see how we can leverage it to help with our reconciliations and other list comparisons. Let’s pretend for a moment that we need to compare two lists. We’d like to find out which items in one…

Read More