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.

PivotTable

Impossible PivotTables 1 – Calculated Fields

By Jeff Lenning | June 27, 2018 |

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…

Read More

Set Default PivotTable Layout Options

By Jeff Lenning | April 5, 2018 |

If you use PivotTables often, you’ve probably wished there was a way to set default PivotTable layout options. For example, if you prefer Tabular instead of the default Compact, it sure would be nice if all new PivotTables used the Tabular layout. Well, depending on the version of Excel you have, you may in fact be…

Read More

PivotTable from Many CSV Files

By Jeff Lenning | March 7, 2018 |

In this post, we’ll summarize data from multiple CSV files with a PivotTable. Specifically, we’ll use a Get & Transform query (Power Query) to retrieve and prepare data from numerous CSV files. Then, we’ll send the query results into the data model (Power Pivot). Then, we’ll build our summary report using a PivotTable based on…

Read More

How to Build a PivotTable with the Data Model

By Jeff Lenning | February 22, 2018 |

Traditional PivotTables are an incredible feature of Excel, but, they are not without limits. Many of the typical restrictions are removed when you use the data model rather than a single Excel table. If you’d like to learn how to build a PivotTable using the data model, and learn what the data model is, strap…

Read More

Import and Clean Bank Activity

By Jeff Lenning | February 15, 2018 |

If you have ever downloaded bank activity from your bank’s website, you know there is some amount of cleaning you need to do before the data is ready to use. Perhaps the next step is to summarize the bank activity with a PivotTable, or, perhaps to perform a bank rec. Regardless of your ultimate objective,…

Read More

One-Click Data Model Date Table

By Jeff Lenning | January 31, 2018 |

Using the Data Model (or Power Pivot) helps us build some amazing PivotTables. A date table (or calendar table) is integral to most data models as they allow us to group reports by various date periods and use a wide variety of time intelligence functions. Although there are many ways to build such a date…

Read More

Get & Transform: An Alternative to Simple PivotTable Reports

By Jeff Lenning | September 27, 2017 |

I love PivotTables, and use them all the time. But, when our needs are simple, we can easily summarize data with a Get & Transform query instead. Why? To simplify our workbooks and improve our efficiency. Let me demonstrate. Objective Before we dig into the mechanics, let’s just be clear about our goal. Let’s say…

Read More

Most Recent Transaction Date

By Jeff Lenning | March 9, 2017 |

I recently received a question about how to find the most recent transaction date of a list of items. There are a few fun ways to accomplish this, and so I thought I’d walk through three options. Thanks Darrell for your question! Objective The exact question from Darrell is: “I have a data table of…

Read More

Daily Bank Balance with a PivotTable

By Jeff Lenning | October 20, 2016 |

When you download banking activity, you typically get the transactions, but not the daily bank balance. To compute the daily bank balance, you need to summarize the transactions by day and display a running total for all days, even those without any activity. This post demonstrates how to build such a report using a PivotTable. Overview…

Read More

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