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

Dynamic Chart Title with Slicers

By Jeff Lenning | November 7, 2018 |

Here’s the situation. We have created a PivotTable and related PivotChart, and, since we are nice, we have also provided a Slicer so that the user can easily make selections. But, we’d like the report titles to dynamically update based on the selections made. As with anything in Excel, there are multiple ways to accomplish…

Read More

Sort a PivotTable with a Custom List

By Jeff Lenning | August 22, 2018 |

In this post, we’ll talk about how to use a custom list to automatically sort a PivotTable. By default, a PT is initially displayed in alphabetical order by the row labels. But, what if that isn’t what we want? Well, we can always manually sort each time we create a PT. But, there is a…

Read More

Edit PivotTable Values

By Jeff Lenning | August 14, 2018 |

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

Read More

Impossible PivotTables 4 – Average of Distinct Count

By Jeff Lenning | July 24, 2018 |

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…

Read More

Data Model Walkthrough CalCPA Article

By Jeff Lenning | July 17, 2018 |

Have you heard people talking about Power Pivot and the data model, but you aren’t exactly sure what they are or how to use them? Well, in this article, we’ll use them to build a basic PivotTable. Workbooks used to summarize exported data can potentially be improved by using Power Pivot and the Data Model. Check…

Read More

Impossible PivotTables 3 – Multiple Data Tables

By Jeff Lenning | July 10, 2018 |

This is the 3rd post in the Impossible PivotTables series. This series is designed to explore Power Pivot. I thought looking at a few limitations of traditional PivotTables would be a fun way to do this. So, in this post, we will look at how traditional PivotTables support a single data table while Power Pivot…

Read More

Impossible PivotTables 2 – Show Values As

By Jeff Lenning | July 2, 2018 |

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…

Read More

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