Blog

Number Format Macro

Excel users often spend a bunch of time applying the same format to number cells. Some numbers are stored values and others are calculated with formulas. The task of manually applying the same format to all of the numbers in the worksheet, especially when the numbers aren’t in a continuous range, can feel quite repetitive. […]


Default Workbook

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


Macro Toolbox

Record Excel Macro by Jeff Lenning

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




Sum by Color

Excel SUBTOTAL function by Jeff Lenning

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


Open Excel Files Faster

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


Fill Down through Blank Rows

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


SUBTOTAL Macro

Excel Featured by Jeff Lenning

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


Excel 2016

Tree map by Jeff Lenning

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