Get access to the full course here 👉 xelplus.com/course/excel-power-pivot-dax/
Working with dates in Excel pivot tables can get tricky. When you drag and drop a date field in the pivot table Excel automatically groups the dates into days, months and years. But with if you want to see the actual date and not the grouping? How can you quickly ungroup the dates? hint: There's an Excel shortcut to ungroup dates in a Pivot Table.
What if you'd also like to create different date grouping not available in the Pivot table? For example create a pivot table report showing fiscal periods or the weekday, quarter etc.
Another challenge you might come across is showing ALL the dates in the pivot table and not only the ones that have values. How can you unhide these dates with no values? It's really easy to setup. We'll be creating an automatic calendar table in the data model and then we can connect our tables together via relationships.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/pivottable-dates-file
Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-courses
00:00 How to Deal with Dates in Pivot Tables
00:33 Automatic Grouping & Ungrouping of Dates
02:05 Create Your Own Groupings
04:18 Use a Date Table in the Data Model
06:25 Create Relationship with Calendar Table
07:09 Create Power Pivot Table
08:07 How to Sort by Date in a Pivot Table
09:46 How to Display Dates Without Data
10:59 Show Data by Quarters in Pivot Table
12:32 How to Extend Calendar Table
13:06 Wrap Up
🎬 LINKS to related videos:
- Excel Productivity for Lazy (but Smart) People: youtu.be/Gf4HmkR7_FE
- How Power Query Will Change the Way You Use Excel:
➡️ Join this channel to get access to perks: youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: xelplus.com/course-quiz/
🎥 RESOURCES I recommend: xelplus.com/resources/
🚩Let’s connect on social:
Instagram: instagram.com/lgharani
LinkedIn: linkedin.com/company/xelplus
This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
- Properly Handle Date Grouping and Missing Dates in Excel Pivot Tables (show all dates) ( Download)
- Grouping Dates in Pivot Table ( Download)
- Excel Pivot Tables: How to Group Dates into Years and Months ( Download)
- Excel Cannot Group Dates in Pivot Table Here's How to Fix it! 🔨 ( Download)
- Pivot Table Trick 4 - Group Date Fields (Must Know!) ( Download)
- Create Fiscal Year Filter and add Missing Dates in Pivot Tables! ( Download)
- 006. Use the Data Model for dates in Excel PIVOT TABLES (Missing dates, custom grouping, etc) ( Download)
- Pivot Table missing dates- see days with no activity ( Download)
- Creating Week-Wise Groups from Dates in Pivot Table | Excel Tutorial ( Download)
- Show Missing Dates in PivotTables - Monkey Shorts Episode 009 ( Download)
- EXCEL PRO TIP: PivotTable Date Grouping ( Download)
- Excel - Pivot Table Tutorial: How to Fill in Missing Dates & Ensure One Row Per Day - Episode 592 ( Download)
- Grouping Dates in Excel Pivot Table Show Extra Items ( Download)
- How to handle missing dates in Excel How to add missing dates in excel easily ( Download)
- Pivot Table Not Grouping Dates | Fix 2 ( Download)
