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.
Techniques
In this post, we’ll explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table. Objective Before digging into the mechanics, let’s review our objective with an example. We would like to allow our…
Read More
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
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
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
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…
Read More
This post explores options for formatting cells that are locked, or unlocked, in an Excel worksheet. Scenario Let’s pretend we have a worksheet that helps a user compare three different loans. The user is required to enter information, such as interest rate and number of years, into designated input cells, but shouldn’t be allowed to…
Read More
How do you ensure your workbook is accurate? One approach is to set up a structured error check sheet. My recent California CPA Magazine TBRG (May 2014) article discusses the ingredients needed to build a reliable error check sheet. Here are the relevant links: Article: Put Errors in Check Sample workbook: Download Excel File
Read More
Hi guys! Just wanted to draw your attention to my April 2014 Journal of Accountancy article that discusses the power of mapping tables. If you’ve not experimented with this technique, it is worth checking out. In summary, a mapping table sits between the data and report sheets and enables you to translate labels and aggregate…
Read More
As a general rule, it is a good idea to delegate as many tasks to Excel as possible. Report headers are no exception, especially for recurring-use workbooks. This post explores the functions needed to create a dynamic report header, such as “For the Period Ending December 31, 2015.” Overview Let’s assume we use the same…
Read More
This post discusses one way to enable our lookup functions, such as VLOOKUP, to work even when the lookup values contain extra spaces. We’ll use the TRIM function to dynamically remove padding from the lookup values. Overview We’ll use a specific example to demonstrate this function. Let’s say we’ve exported a partial income statement from…
Read More