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.


Default Workbook

By Jeff Lenning | March 3, 2016 |

Excel users find themselves making the same customizations over and over again. This post demonstrates how to store customizations such as cell formatting, headers, print preferences, the default PivotTable style, and cell styles, in a template workbook. Plus, we’ll see how the name and location of the template determines when it is used, and, if it…

Read More
Record Excel Macro by Jeff Lenning

Macro Toolbox

By Jeff Lenning | February 18, 2016 |

When storing utility macros, Excel users often elect to save them in the Personal Macro Workbook. The advantage is that the macros are available whenever Excel is running. The disadvantage is that if you switch or upgrade computers, you may forget to take a backup of the Personal Macro Workbook and thus lose your macros. In…

Read More

Macro to Quickly Format PivotTable Values

By Jeff Lenning | February 4, 2016 |

PivotTable users frequently spend time assigning the same number format to PivotTable values. To my knowledge, there isn’t a built-in setting that allows us to define a default value field format. But, it is pretty easy to set up a macro that instantly assigns a desired format. This post walks through the steps of creating such a macro. Objective…

Read More

Accounting Number Formats QAT

By Jeff Lenning | January 20, 2016 |

Excel has cell formatting designed just for accountants. Not surprisingly, it is called the Accounting Number Format. The built-in ribbon commands apply the format with two decimals and the currency symbol. This post demonstrates how to set up two handy QAT icons so that we can quickly apply this format with no decimals and without a currency…

Read More
Excel SUBTOTAL function by Jeff Lenning

Sum by Color

By Jeff Lenning | January 13, 2016 |

If you need to compute the total for certain cells based on their font or fill color, you may have noticed that Excel formulas operate on stored values, not displayed values. That means that functions such as SUM and SUMIFS operate on the underlying cell values and disregard cell formatting, such as font or fill…

Read More

Open Excel Files Faster

By Jeff Lenning | December 9, 2015 |

I’m not sure about you, but, sometimes I sort of miss the old days of Excel when Ctrl+O immediately displayed the Open dialog box. This dialog box allowed me to quickly navigate my computer and find the file I wanted to open. However, when Microsoft introduced the Backstage, this keyboard shortcut stopped displaying the Open…

Read More

Fill Down through Blank Rows

By Jeff Lenning | December 2, 2015 |

Are you familiar with the double-click shortcut to fill formulas down? If so, have you noticed it stops filling down at the first blank row? This post will discuss the double-click shortcut as well as a simple workaround for how to fill it down through a report range even when there are blank rows in…

Read More
Excel Featured by Jeff Lenning


By Jeff Lenning | November 12, 2015 |

The SUBTOTAL is a wonderful alternative to the SUM function, and this post shows how to set up a shortcut button that inserts the SUBTOTAL function. Our new shortcut button will be similar to the AutoSum button that inserts the SUM function because it will automatically include the cells above. Objective Before we get started, let’s…

Read More
Tree map by Jeff Lenning

Excel 2016

By Jeff Lenning | November 5, 2015 |

Microsoft recently released Office 2016, and with it, the next version of my favorite program, Excel. If you would like to get a quick overview of some of the enhancements, please check out this CalCPA Magazine article where I describe the Excel 2016 improvements that caught my eye. Excel 2016 – CalCPA Magazine Article With…

Read More
Lookup Range by Jeff Lenning

Return the Sum of Two or More Columns with VLOOKUP

By Jeff Lenning | October 29, 2015 |

If you have ever wished that the VLOOKUP function could return the sum of two or more related columns, this trick will get you there. Objective Before we get into the details, let’s be clear about our objective. We have some transactions that were exported from our accounting system as shown below. We would like…

Read More