# 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

## Table Total Row

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

## Format Locked or Unlocked Cells

| May 7, 2014 |

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…

## Recent CalCPA Article

| May 2, 2014 |

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

## Recent JofA Article

| April 24, 2014 |

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…

## Conditional Averages with AVERAGEIFS

| April 17, 2014 |

The AVERAGEIFS function can compute averages for transactions that meet a set of criteria. In this post, we’ll use it to create a report by customer that ignores zero value transactions. Overview Although SUMIFS was probably the most popular multiple condition function introduced in Excel 2007, it wasn’t the only one. Microsoft released AVERAGEIFS which…

## Free Kindle Version of EU V2

| April 10, 2014 |

To help celebrate the conclusion of the traditional busy-season, I’m pleased to announce that we are giving away free digital copies of Excel University Volume 2 on April 16, 2014. You can download the book directly from Amazon and read it with a Kindle device or with the free Kindle app. The book can be used…

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