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.

SUBTOTAL

SUBTOTAL Macro v2

By Jeff Lenning | September 12, 2018 |

The SUBTOTAL function is one of my favorites. So, I wrote a post about it a while back that provided a macro to insert it. The macro worked kinda like the AutoSum command, but it inserted the SUBTOTAL function instead of the SUM function. Gary posted a suggestion for enhancing the macro, so, I wrote…

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

Display a Negative Number as Positive

By Jeff Lenning | June 12, 2015 |

With a simple custom format code, we can display negative numbers as positive…but…why would we want to? So that we can simplify our formulas and make our workbooks more reliable of course. Let’s check it out. Objective Before we get to the mechanics, let’s confirm our goal. We have a worksheet, in this case a little…

Read More
Excel Balance Sheet

Create a Balance Sheet with Excel

By Jeff Lenning | November 21, 2013 |

Excel Balance Sheet This post illustrates how to create a financial statement such as a balance sheet with built-in Excel features and functions. Our primary objective is to build a workbook that efficiently pulls values from the trial balance into the balance sheet. Overview For the purpose of this post, let’s assume we have a…

Read More