Unpivot Excel Data with Power Query Step by Step Video

This step-by-step video shows how to unpivot data in Excel, using Power Query. This creates better source data that you can use to build flexible pivot tables. And it doesn’t change your original data – you can leave that as is!

Continue reading “Unpivot Excel Data with Power Query Step by Step Video”

Excel Pivot Table Error Message Reference Not Valid

Tuesday morning, I opened an Excel file that I’d been working on the day before. Immediately, an error message appeared, “Reference isn’t valid”. I’ve seen that error with Excel charts before, but this turned out to be a pivot table problem.

Continue reading “Excel Pivot Table Error Message Reference Not Valid”

Excel With Olympic Athlete Data for Winter Fun

Are you watching any of the 2022 Winter Olympics coverage? I hear the medal count reports on the morning radio, but that’s about all I’ve seen!

Back in 2010, the Winter Olympics were in Vancouver, Canada, so we watched lots of events that year. I downloaded some athlete bios that year, and had fun turning that data into pivot tables.

Continue reading “Excel With Olympic Athlete Data for Winter Fun”

Analyze Top Product Sales with Pivot Table % Running Total

In an Excel pivot table, you can use the % Running Total calculation, to show the current running total amount, divided by the grand total. For example, see what percentage of total sales come from the top 3 products.

Continue reading “Analyze Top Product Sales with Pivot Table % Running Total”

Excel Pivot Table Refresh Error with Data Model

A simple data change can cause a strange pivot table refresh error, if you added the data to the Data Model. The problem is easy to fix, once you see what’s causing it, but the error message, shown below, isn’t much help! I’d never heard of function PCMinorObjectCollection, had you?

Continue reading “Excel Pivot Table Refresh Error with Data Model”

Pivot Table from Filtered List Visible Rows

When you create a pivot table in Excel, it doesn’t matter if there are filters applied in the source data table. The pivot table includes all the data, whether it’s hidden or not. But, if you’d like to create a pivot table from filtered list visible rows only, you can try this technique.

Continue reading “Pivot Table from Filtered List Visible Rows”