The Two-Second Fix for Counting Problems Problem: Your PivotTable is counting all rows in a column, even those with zeros. Solution: Replace zeros with nulls in Power Query! While "zero" is a number that Excel counts, "null" is empty and gets ignored. (Screenshot of a table containing data on the left and a PivotTable based on that data on the right. The Grand Total count on the PivotTable columns do NOT include the "0" entries.) (Skillwave Training logo)

One of our AKA participants wanted to know why their #PivotTable was inflating their counts numbers. They only wanted to count active line of credit accounts, but their count was including everything, even those with $0 balances. My solution? Replace zero with null in #PowerQuery. It's that simple!🧵

Count Values in a Column Using Power Query – The Simple Method In this Ask Ken Anything session of the Self Service BI Academy training course, Ken Puls tackles a common PivotTable surprise: Why does a PivotTable count three Lines of Credit as “used” when one…

A student asked why a #PivotTable counts 3 Lines of Credit as “used” when one of them has a zero balance and shouldn’t be counted. 🤷🏾‍ And do you need #PowerPivot and #DAX to fix it?
Nope! @excelguru.ca shows it takes just simple data-prep adjustment in #PowerQuery:

When Grand Totals Don't Add Up (On Purpose) A student had a question about controlling what appears in PivotTable totals. They needed the grand total to display a specific count rather than summing the values above. Ken Puls walked through using a VAR and SWITCH statement to detect filter context. By checking if a field "has one value" versus "multiple," you can write different DAX logic for individual rows versus subtotals versus grand totals. Takeaway: Master your filter context, and you can control every single cell in your reports. (Skillwave Training logo)

A question came up in an AKA session about controlling what appears in #PivotTable totals. The student had room costs where individual rows showed correctly, but they needed the grand total to display a specific count (88 rooms) rather than summing the values above it (which would give 176). 🏨
🧵

Count Values in a Column Using Power Query – The Simple Method In this Ask Ken Anything session of the Self Service BI Academy training course, Ken Puls tackles a common PivotTable surprise: Why does a PivotTable count three Lines of Credit as “used” when one…

Why does a #PivotTable count three Lines of Credit as “used” when one of them has a zero balance and shouldn’t be counted? 🤷🏾‍ And more importantly — do you need #PowerPivot and #DAX to fix it? Here, I'll show you that you don't: youtu.be/-X-ELoJ9Kiw

#PowerQuery #ExcelTips

When Grand Totals Don't Add Up (On Purpose) A student had a question about controlling what appears in PivotTable totals. They needed the grand total to display a specific count rather than summing the values above. Ken Puls walked through using a VAR and SWITCH statement to detect filter context. By checking if a field "has one value" versus "multiple," you can write different DAX logic for individual rows versus subtotals versus grand totals. Takeaway: Master your filter context, and you can control every single cell in your reports. (Skillwave Training logo)

A question came up in an AKA session about controlling what appears in #PivotTable totals. The student had room costs where individual rows showed correctly, but they needed the grand total to display a specific count (88 rooms) rather than summing the values above it (which would give 176). 🧵

The Two-Second Fix for Counting Problems Problem: Your PivotTable is counting all rows in a column, even those with zeros. Solution: Replace zeros with nulls in Power Query! While "zero" is a number that Excel counts, "null" is empty and gets ignored. (Screenshot of a table containing data on the left and a PivotTable based on that data on the right. The Grand Total count on the PivotTable columns do NOT include the "0" entries.) (Skillwave Training logo)

One of our AKA participants wanted to know why their #PivotTable was inflating their counts numbers. They only wanted to count active line of credit accounts, but their count was including everything, even those with $0 balances.

@excelguru.ca's solution? Replace zeros with nulls in #PowerQuery. 🧵

Creating Linked "Fact" Tables - Monkey Shorts Episode 010 Ken looks at a simple Power Pivot trick in this video which helps: -Prevent users from accidentally adding a "foreign key" to a PivotTable (resulting in cross filtering issues) -Keep data…

In this Monkey Shorts, I look at a simple #Excel #PowerPivot trick which helps:
✅ Prevent users from accidentally adding a "foreign key" to a #PivotTable (resulting in cross filtering issues)
✅ Keep data organized
✅ Visibly identify tables with measures on them

Creating Linked "Fact" Tables - Monkey Shorts Episode 010 Ken looks at a simple Power Pivot trick in this video which helps: -Prevent users from accidentally adding a "foreign key" to a PivotTable (resulting in cross filtering issues) -Keep data…

In this Monkey Shorts, @excelguru.ca looks at a simple #Excel #PowerPivot trick which helps:
✅Prevent users from accidentally adding a "foreign key" to a #PivotTable (resulting in cross filtering issues)
✅ Keep data organized
✅ Visibly identify tables with measures

Rather than let your users “freestyle” data entry, provide a template to promote consistency. Watching the #PivotTable update automatically after a simple refresh without having to touch the query makes the payoff obvious.

🧭 Takeaway: The best automation is the one that fits how your data arrives.

Setting PivotTable Defaults - Monkey Shorts Episode 008 Not satisfied with Compact Form for your Excel PivotTables? Tired of changing it to your preferred format every time you create one? There's a better way! In this video, Ken shows you how to modify…

Not satisfied with Compact Form for your #Excel PivotTables? Tired of changing it to your preferred format every time you create one? There's a better way! In this video, I'll show you how to modify your #PivotTable defaults: www.youtube.com/watch?v=eCut...

#DataAnalyticsTools #BusinessIntelligence

Setting PivotTable Defaults - Monkey Shorts Episode 008 Not satisfied with Compact Form for your Excel PivotTables? Tired of changing it to your preferred format every time you create one? There's a better way! In this video, Ken shows you how to modify…

Not satisfied with Compact Form for your #Excel PivotTables? Tired of changing it to your preferred format every time you create one? There's a better way! In this video, @excelguru.ca shows you how to modify your #PivotTable defaults: www.youtube.com/watch?v=eCut...

#DataAnalyticsTools

Solving the “Why Does Excel Rename My Field?” Mystery (Image of a man focused intently on the laptop in front of him) (Skillwave Training logo)

In our last AKA session of 2025, an attendee raised a deceptively simple question: “Why does Excel keep calling my column ‘Sum of Amount’ and can I stop it?” 😩
@excelguru.ca used this moment to explain how #Excel automatically assigns aggregations when numeric fields hit a #PivotTable. 🧵

Watching the #PivotTable update automatically after a simple refresh without having to touch the query makes the payoff obvious. 🔁😎

#BusinessIntelligence #DataAnalytics #DataPreparation

🧭Takeaway: The best automation is the one that fits how your data actually arrives.

Sorting Power Pivot backed Slicer and Pivots - Monkey Shorts Episode 006 In this episode, Ken demonstrates how to sort one Power Pivot column by another column, so that your Power Pivot backed Pivot Tables and Slicers display in the correct order.

In this Monkey shorts, I demonstrate how to sort one #PowerPivot column by another column, so that your Power Pivot backed Pivot Tables and Slicers display in the correct order. 🙌 www.youtube.com/watch?v=F0qG...

#ExcelTips #DataModeling #PivotTable #DataAnalytics #BusinessIntelligence

Sorting Power Pivot backed Slicer and Pivots - Monkey Shorts Episode 006 In this episode, Ken demonstrates how to sort one Power Pivot column by another column, so that your Power Pivot backed Pivot Tables and Slicers display in the correct order.

In this Monkey shorts, Ken Puls demonstrates how to sort one #PowerPivot column by another column, so that your Power Pivot backed Pivot Tables and Slicers display in the correct order. 🙌 www.youtube.com/watch?v=F0qG...

#ExcelTips #DataModeling #PivotTable #DataAnalytics #BusinessIntelligence

In our last Ask Ken Anything (AKA) session of the year, an attendee raised a deceptively simple question: “Why does Excel keep calling my column ‘Sum of Amount’ and can I stop it?” 😩
@excelguru.ca explained how #Excel automatically assigns aggregations when numeric fields hit a #PivotTable. 🧵

2️⃣ Add that numeric measure as a hidden helper column in the PivotTable.
3️⃣ Build conditional formatting rules that read those hidden numeric values.
4️⃣ Apply green/yellow/red formatting directly to the visible cells, just like a Power BI matrix.
Tue colour scale formatting inside a single #PivotTable!

When a PivotTable “Just Feels Wrong” A student asked Ken Puls why their PivotTable was giving weird blanks and not calculating month-over-month changes properly. Instead of jumping straight to formulas, Ken walked through why data normalization matters and how to calculate month-over-month changes using “Show Values As → Difference From.” Takeaway: Most pivot problems aren’t formula problems – they’re data structure problems. (Skillwave Training logo)

In the last AKA session, one of our students asked a question many #Excel users quietly struggle with: “Why is my #PivotTable giving me weird blanks and not calculating month-over-month changes properly?”

@excelguru.ca immediately spotted the red flag. 🚩
🧵

Granular PivotTable Layouts - Monkey Shorts Episode 003 In this episode of Monkey Shorts, Ken will show you how you can set different layouts for each column of your PivotTable.

In this episode of Monkey Shorts, I'll show you how you can set different layouts for each column of your #PivotTable: www.youtube.com/watch?v=6ewp...

#DataAnalysisTools #BusinessIntelligence #Excel

Granular PivotTable Layouts - Monkey Shorts Episode 003 In this episode of Monkey Shorts, Ken will show you how you can set different layouts for each column of your PivotTable.

In this episode of Monkey Shorts, @excelguru.ca will show you how you can set different layouts for each column of your #PivotTable: www.youtube.com/watch?v=6ewp...

#DataAnalysisTools #BusinessIntelligence #Excel

Catch PivotTable & Workbook Errors Smartly! 🚨 Errors during PivotTable refreshes can be easy to miss — but they can break your entire workflow. In this video, Ken Puls shows a smart, reliable method to detect refresh-time errors automatically…

Errors during #PivotTable refreshes in #Excel can be easy to miss, but they can break your entire workflow. @excelguru.ca has a smart, reliable method to detect refresh-time errors and surface them right on your worksheet so you never miss a critical issue again:

One click refresh of Power Query based Pivot Tables - Monkey Shorts Episode 002 Ever noticed that you have to refresh your Power Query twice in order to get the changes to show up in your Pivot Table? In this Episode of Monkey Shorts, Ken will show you how to fix this issue.

Ever noticed that you have to refresh your #PowerQuery twice in order to get the changes to show up in your #Excel #PivotTable? In this episode of Monkey Shorts, I'll show you how to fix this issue. 👏
www.youtube.com/watch?v=gTtt...

#DataPreparation #DataAnalyticsTools #BusinessIntelligenceTools

Catch PivotTable & Workbook Errors Smartly! 🚨 Errors during PivotTable refreshes can be easy to miss — but they can break your entire workflow. In this video, Ken Puls shows a smart, reliable method to detect refresh-time errors automatically…

Errors during #PivotTable refreshes in #Excel can be easy to miss — but they can break your entire workflow. Here's a smart, reliable method to detect refresh-time errors automatically and surface them right on your worksheet so you never miss a critical issue again:

One click refresh of Power Query based Pivot Tables - Monkey Shorts Episode 002 Ever noticed that you have to refresh your Power Query twice in order to get the changes to show up in your Pivot Table? In this Episode of Monkey Shorts, Ken will show you how to fix this issue.

Ever noticed that you have to refresh your #PowerQuery twice in order to get the changes to show up in your #Excel #PivotTable? In this Monkey Shorts, @excelguru.ca will show you how to fix this issue. 👏
www.youtube.com/watch?v=gTtt...

#DataPreparation #DataAnalyticsTools #BusinessIntelligence

Unlock the hidden power of Excel’s GROUPBY() function – multi-levels, custom headings & re-usable results. #PivotTable #ExcelTips for #Microsoft365 #Office2024 and #Excel online.
office-watch.com/2025/excel-g...

Stop Clicking Refresh! Auto-Refresh Your PivotTables in Excel (Beta Feature Preview) This video shows a Beta Channel preview of Microsoft’s upcoming PivotTable Auto Refresh feature — explained and demonstrated by Excel MVP Ken Puls (Excelguru / Skillwave). You can have your Excel…

Want your #Excel #PivotTable to refresh automatically—without clicking Refresh every time? 🔄
@excelguru.ca shows you the new PivotTable Auto Refresh feature (in the Beta Channel for Office 365) that keeps your reports accurate the moment your source data changes:

The group loved seeing both options demo’d live, sparking side discussions about month-to-month comparisons across years, fiscal calendars, 4-4-5 periods. 🧭 Takeaway: Understanding the subtle differences between slicers and timelines can completely change how your audience explores a #PivotTable.

PivotTable Auto-Refresh Want your Excel PivotTables to refresh automatically—without clicking Refresh every time? In this video, Excel MVP Ken Puls (Excelguru / Skillwave) shows you the new feature - PivotTable Auto Refresh…

Want your #Excel #PivotTable to refresh automatically—without clicking Refresh every time? 🔄 Check out the new PivotTable Auto Refresh feature (available in the Beta Channel for Office 365) that keeps your reports accurate the moment your source data changes:

Zero Means Nothing... Literally! Ever wondered how to make a PowerPivot-backed PivotTable suppress rows full of zeros? In our last Ask Ken Anything session, Ken Puls wowed the group with a simple DAX pattern to suppress zeros dynamically so only meaningful values remain. Screenshot of DAX formula: =VAR vAmount = SUM( Sales[Amount] ) VAR vResult = IF( vAmount = 0, BLAND(), vAmount) RETURN vResult (Skillwave Training logo)

Ever wondered how to make a #PowerPivot-backed #PivotTable suppress rows full of zeros? A student asked that exact question in an AKA session, and turned it into a great learning moment for everyone! I showed a simple #DAX pattern to suppress zeros dynamically so only meaningful values remain. 🧵 1/3

...month-to-month comparisons across years, fiscal calendars, 4-4-5 periods, and how the end users’ experience could change depending on which control you provided.

🧭 Takeaway: Understanding the subtle differences between slicers and timelines changes how your audience explores a #PivotTable.