# 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

## Dynamic Date Report Header For The Period Ending

| March 27, 2014 |

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…

## PivotTable Percentage of Parent Total

| March 20, 2014 |

This post demonstrates how to set up a PivotTable to show 100% on the subtotal lines when using the show values as a percentage of parent total option. Overview Before we begin, let’s review our objective. We want to summarize our data by region, and within each region, by rep. We also want to show…

## Clean up Reports with IFERROR

| March 6, 2014 |

In the old days, we could use a combination of the IF and ISERROR functions to clean up reports. This method was good, and worked for many years…but starting with Excel 2007 there is an easier alternative. This post explores the IFERROR function. Overview The IFERROR function enables us to use a single function to…

## Remove Extra Spaces from Lookup Values with TRIM

| February 27, 2014 |

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…

## Pull Budget Values into an Income Statement

| February 13, 2014 |

In this post, we explore a way to pull budget values into an income statement exported from QuickBooks, and demonstrate how to handle the fact that the extract uses new columns to indent. Overview When the budget and actual data reside in the same application, creating a variance report is easy. However, when the actual…

## Create Dependent Drop-down Lists with Conditional Data Validation

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

| January 31, 2014 |

Excel University Volume 3 is far enough along for me to share the table of contents. I have to say, I’m very excited about this volume as we cover some very cool things. I can’t wait for this volume to make its way through the editing and interior design stages. We are targeting a Q3…

## Use the Column Header to Retrieve Values from an Excel Table

| January 24, 2014 |

This post discusses ways to retrieve aggregated values from a table based on the column labels. Overview Beginning with Excel 2007, we can store data in a table with the Insert > Table Ribbon command icon. If you haven’t yet explored this incredible feature, please check out this CalCPA Magazine article Excel Rules. Frequently, we…

## VLOOKUP on Two or More Criteria Columns

| January 10, 2014 |

If you have ever tried to use a VLOOKUP function with two or more criteria columns, you’ve quickly discovered that it just wasn’t built for that purpose. Fortunately, there is another function that may work as an alternative to VLOOKUP depending on what you want to return. Video Multi-Column Lookup Objective First, let’s confirm our…