Blog

Unique Data Validation Drop-Down From Duplicate Table Data

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 […]

Transpose Values and Formulas in Excel

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 […]

Invitation to the Excel Modeling World Championships 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 […]

SUMIFS with OR

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 […]

Create an Income Statement with a PivotTable

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 […]

VLOOKUP and Wildcards

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 […]

List Comparisons CalCPA Magazine Article

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 […]

Create Keyboard Shortcuts for your Favorite Excel Commands

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 […]

Create Fiscal Year Periods with VLOOKUP

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 […]

Table Total Row

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). […]