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.

INDEX

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

Most Recent Transaction Date

By Jeff Lenning | March 9, 2017 |

I recently received a question about how to find the most recent transaction date of a list of items. There are a few fun ways to accomplish this, and so I thought I’d walk through three options. Thanks Darrell for your question! Objective The exact question from Darrell is: “I have a data table of…

Read More

Moving, Rolling, and Trailing Averages

By Jeff Lenning | March 19, 2015 |

The terms Moving, Rolling, and Trailing are commonly used to describe the same calculation idea…that we want to operate on the previous say 3, 6, or 12 data rows. In this post, we’ll allow the user to define the number of rows to include and use the OFFSET function to dynamically define the desired range. Objective…

Read More

Sum Last N Columns

By Jeff Lenning | March 12, 2015 |

If you have a data table that is updated frequently, for example, a new column is added each month, you may want to find the sum of the last three columns. But, you don’t want to rewrite your formula each time you add a new column. Fortunately, you can accomplish this task with two lookup functions,…

Read More

Simulate Structured References in Named Ranges

By Jeff Lenning | October 23, 2014 |

The table feature introduced in Excel 2007 is amazing and has nearly eliminated the need to build dynamic named ranges since tables auto-expand. Beyond auto-expansion, tables offer numerous other benefits, including, structured references which allow us to refer to an area within the table, such as a specific column. In this post, we’ll examine a method…

Read More

Date Data Validation Drop-Down

By Jeff Lenning | September 18, 2014 |

In this post, we’ll create two data validation drop-down cells that provide the ability to select From and To dates based on the transaction dates stored in the source data. This will ensure that the date selections fall within a valid range of dates, that is, those months with data in the table. This technique…

Read More

Transpose Values and Formulas in Excel

By Jeff Lenning | September 4, 2014 |

In this post, we’ll explore three methods for transposing data in Excel. The first method can be used when you just want to quickly to transpose the values manually. The second method can be used when you want formulas to perform the transposition automatically based on the labels. The third method can be applied when…

Read More

Create Dependent Drop-down Lists with Conditional Data Validation

By Jeff Lenning | February 7, 2014 |

This post explores macro-free methods for using Excel’s data validation feature to create an in-cell drop-down that displays choices depending on the value selected in a previous in-cell drop-down. Overview As with just about anything in Excel, there are several ways to achieve the goal. This post explores three such solutions, and if you have a…

Read More

How to Return a Value Left of VLOOKUP’s Lookup Column

By Jeff Lenning | August 29, 2013 |

If you have ever tried to return a value that physically lies to the left of the lookup column, you quickly realize that this task is difficult to accomplish with the VLOOKUP function.  This is a perfect time to move beyond the VLOOKUP function and explore the INDEX and MATCH functions. Note: depending on your…

Read More