Historically, when it was time for Microsoft to release the next version of Excel, everyone would be buzzing, excited, and talking about the new features, functions, and improvements. I know that my heart would pitter-patter as the new version was installing on my system. But, things are different now. In fact, in a way, the […]
Let’s say we are building a query to clean up and prepare some data for use, and we have a specific value we need to use within the query. But, we would like to be able to change the value as desired, before refreshing the query. One option would be to enter and update that […]
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 […]
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. […]
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 […]
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 […]
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 […]
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 […]
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 […]
Sometimes in Excel, we want to use formulas to compute row values, but, the number of rows is dynamic and changes periodically. For example, let’s say we want to create an amortization schedule and use it for a variety of loans. Some loans are paid in 36 months, some in 120 months, and some in […]