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

Unique Data Validation Drop-Down From Duplicate Table Data

By Jeff Lenning | September 10, 2014 |

In this post, we’ll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values. Objective Before we get started on the mechanics, let’s take a look at what we are trying to achieve. We have a data table that is exported from our…

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

Invitation to the Excel Modeling World Championships 2014

By Jeff Lenning | August 27, 2014 |

Hi all! We have been formally invited to participate in the upcoming Excel modeling championship. Wow…a world-wide Excel competition…awesome! The invitation from John Persico, one of the lead organizers of the ModelOff event, is below…check it out! Invitation to the Excel Modeling World Championships 2014 We’d like to invite members of the Excel University community…

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. Objective Let’s be clear about…

Read More

Create an Income Statement with a PivotTable

By Jeff Lenning | July 31, 2014 |

Generally, I prefer to prepare financial statements with formulas and functions such as SUMIFS because there are no layout or structure limitations imposed. This means I can design the report to my exacting specifications. However, it is interesting to note that PivotTables support basic formulas, and using a PivotTable formula to create a calculated item…

Read More

VLOOKUP and Wildcards

By Jeff Lenning | July 24, 2014 |

In a previous post, we explored how to use the TRIM function to remove trailing spaces from lookup values used in a VLOOKUP function. In this post, we’ll tackle the reverse issue, where the values in the lookup column contain trailing spaces, by using wildcards in the VLOOKUP function. Note: depending on your version of…

Read More

List Comparisons CalCPA Magazine Article

By Jeff Lenning | July 3, 2014 |

Hi guys! Just wanted to draw your attention to a recent California CPA Magazine article I wrote about comparing lists using Excel. Have you ever wanted to ask Excel to identify which items from one list appear on another? For example, which check numbers on the check register appear on the bank download, as illustrated…

Read More

Create Keyboard Shortcuts for your Favorite Excel Commands

By Jeff Lenning | June 19, 2014 |

Generally, you can improve your speed by keeping your hands on your keyboard. But, what do you do if there is no built-in keyboard shortcut to execute your favorite command? Well, one approach is to customize the QAT. This post discusses the Quick Access Toolbar and the related keyboard shortcuts it creates. QAT Shortcuts Microsoft…

Read More

Create Fiscal Year Periods with VLOOKUP

By Jeff Lenning | June 12, 2014 |

I’m a huge fan of the VLOOKUP function, and am surprised by its day-to-day utility for accountants. In this post, we use the VLOOKUP function to convert or translate calendar year transaction dates into fiscal year periods, such as a fiscal quarter. To accomplish this, we’ll first need to investigate in detail the function’s fourth…

Read More

Table Total Row

By Jeff Lenning | June 5, 2014 |

The Table feature of Excel remains one of my favorites. This post explores one very specific aspect of the feature, the total row. Overview One of Microsoft’s greatest gifts of Excel 2007 was the introduction of the table feature. It allows us to convert an ordinary range into a table (Insert > Table, or, Ctrl+T).…

Read More