# 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

## Date Data Validation Drop-Down

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

## Unique Data Validation Drop-Down From Duplicate Table Data

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

## Transpose Values and Formulas in Excel

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

## Invitation to the Excel Modeling World Championships 2014

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

## SUMIFS with OR

| 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. Note: if your version of…

## Create an Income Statement with a PivotTable

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

## VLOOKUP and Wildcards

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

## List Comparisons CalCPA Magazine Article

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

## Create Keyboard Shortcuts for your Favorite Excel Commands

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