Author Archives: Jeff Lenning


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

2018 Scholarship Winners

I am thrilled to announce this year’s Excel University scholarship winners! These scholarships are supported by the Excel University Alumni Association, so thanks to all of you who have helped make these awards possible! A special thank you this year to Gary Zeune of The Pros and The Cons who has generously donated time helping […]

Edit PivotTable Values

In this post, we’ll talk about how to edit the value in a PivotTable cell. Wait…what!? I’ve been using PTs for a long time, and, I’ve always received an error when I try to edit the value in a PivotTable cell. Yes, we’ve been able to edit the label cells, but, not the value cells. […]

Excel Breakout Puzzle 5

I think it’s about time for another breakout puzzle … yes! This breakout puzzle is inspired by The Matrix. The Matrix? Yes, the movie, you know … with Neo. If you haven’t seen it, it was a popular movie about 20 years ago. (Wow does time fly.) Anyhow, I love that movie and so decided […]

Impossible PivotTables 4 – Average of Distinct Count

This is the 4th and final post in the Impossible PivotTables series, where we are exploring Power Pivot by looking at some limitations encountered with traditional PivotTables. In this post, we’ll look at how to compute the average when there are multiple rows per item. That is, where a simple sum divided by the count […]

Impossible PivotTables 2 – Show Values As

This is the second post in the Impossible PivotTables series, where we are exploring Power Pivot. In the first post, we examined the limitation of calculated field references in a traditional PivotTable. In this post, we’ll address the limitation of using the Show Values As option for subsequent calculations. If we use the Show Values […]

Impossible PivotTables 1 – Calculated Fields

This is the first post in a series called Impossible PivotTables. The purpose of this series is to explore Power Pivot. I thought a fun way to do that would be to demonstrate how using the data model enables us to build PivotTables that are either impossible with traditional PivotTables or that require workarounds. I […]