Ever filtered an #Excel #PivotTable and noticed that dates with no sales suddenly disappear? 😟
A student asked @excelguru.ca: "Do I need to write a complicated #DAX measure to keep empty dates visible in my report?"
The good news: you don’t! 😀
Here's what you do:

Ever filtered an #Excel #PivotTable and noticed that dates with no sales suddenly disappear? 😟
A student specifically asked recently: "Do I need to write a complicated #DAX measure to keep empty dates visible in my report?"
The good news: you don’t! 😀
Here's what you do:

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!🧵

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). 🏨
🧵

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

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

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.

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

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.

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

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. 🚩
🧵

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

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

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:

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

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:

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

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.

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: