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

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

SUBTOTAL Macro

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

WORKDAY Function

By Jeff Lenning | October 8, 2015 |

When you need to compute a future date and exclude weekends, you may want to consider exploring the WORKDAY function. In this post, we’ll use the WORKDAY function to prepare a simple project plan and then display it with a Gantt chart. Objective We have a project that we are managing and it has several…

Read More

Multi-Column Lookup with VLOOKUP and SUMIFS

By Jeff Lenning | September 17, 2015 |

When you need to perform a lookup, your instinct tells you to use VLOOKUP. But, when your lookup uses multiple conditions and columns, you may be inclined to use SUMIFS. However, when the value you need to return is a text string, rather than a numeric value, you are precluded from using SUMIFS since it only…

Read More