Category Archives: Functions

VLOOKUP vs INDEX-MATCH

There are numerous lookup functions in Excel, and often, their capabilities overlap. And, that is true with many things in Excel…there are often multiple ways to accomplish any given task. Performing lookups is no exception. So, how are we supposed to know which lookup function to use? Often, Excel users try to decide between VLOOKUP […]



Add and Subtract Hours

Excel time zones by Jeff Lenning

I was recently working on a workbook, and had to convert an event time from one time zone to another. Basically, I wanted to enter the time of day into a cell, and then I needed a formula to compute the event time in a different time zone. This post walks through the formula to […]



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


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