title
š§āāļø Pivot Table Excel Tips & Tricks
description
In this step-by-step tutorial, learn the top 14 best pivot table tips and tricks in Microsoft Excel. For example, use natural language to create pivot tables, get automatic insights on your data using ML, use multiple tables as sources, add calculated fields, quickly filter data with slicers & timelines, build dashboards, and many more. These are sure to take your analysis skills with pivottables to the next level. And most importantly, you'll be able to answer business questions even faster.
š Additional resources
- Sample data to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whLkz3v27FyEe1w4O_A?e=GiHJal
- How to use Pivot Tables Tutorial for Beginners: https://youtu.be/m0wI61ahfLc
- How to use Power Pivot in Excel: https://youtu.be/rB_IiYbOo7w
- Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com
ā Timestamps
0:00 Introduction
0:34 Natural language pivot table
2:37 Click values for details
3:18 Pivot table with multiple sheets
5:39 Field layout & sorting options
6:28 Calculated fields
7:30 Sort values
7:57 Show value as
9:14 Summarize values
10:16 Slicer
11:37 Timeline
12:25 Pivot charts
13:08 Pivot table layouts
14:46 Grouping
15:16 Customize row labels
16:44 Wrap up
š Watch related playlists and videos
- Playlist with all my videos on Excel: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY
š© Connect with me on social
- LinkedIn: https://www.linkedin.com/in/kevinstratvert/
- Discord: https://discord.gg/kHuujY2pZf
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818
- TikTok: https://www.tiktok.com/@kevinstratvert
- Instagram: https://www.instagram.com/kevinstratvert/
š Subscribe to my YouTube channel
https://www.youtube.com/user/kevlers?sub_confirmation=1
š¬ Want to watch again? Navigate back to my YouTube channel quickly
http://www.kevinstratvert.com
š Support me with your Amazon purchases: https://amzn.to/3kCP2yz
ā As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert
detail
{'title': 'š§\u200dāļø Pivot Table Excel Tips & Tricks', 'heatmap': [{'end': 94.956, 'start': 59.669, 'weight': 0.961}, {'end': 212.498, 'start': 187.597, 'weight': 0.789}, {'end': 262.547, 'start': 213.038, 'weight': 0.764}, {'end': 439.773, 'start': 414.375, 'weight': 0.867}, {'end': 725.549, 'start': 698.29, 'weight': 0.789}, {'end': 903.713, 'start': 873.693, 'weight': 0.751}, {'end': 925.008, 'start': 906.115, 'weight': 0.722}], 'summary': 'Learn 14 tips for excel pivot tables, including using natural language, analyzing data, creating pivot tables from multiple data tables, optimizing usage with custom layouts and calculated fields, and utilizing slicers and timelines for effective data filtering.', 'chapters': [{'end': 80.886, 'segs': [{'end': 30.025, 'src': 'embed', 'start': 0.009, 'weight': 0, 'content': [{'end': 5.453, 'text': 'Bye Hey everyone, Kevin here.', 'start': 0.009, 'duration': 5.444}, {'end': 14.777, 'text': 'Today I want to show you my favorite top 14 tips and tricks for pivot tables in Microsoft Excel.', 'start': 5.693, 'duration': 9.084}, {'end': 20.42, 'text': 'There are lots of great ones that I think will make data analysis even easier for you.', 'start': 15.338, 'duration': 5.082}, {'end': 25.122, 'text': 'If you want to jump around this video, feel free to use the timestamps down below.', 'start': 21.14, 'duration': 3.982}, {'end': 30.025, 'text': "Also, if you want to follow along, I've included sample data in the description.", 'start': 25.843, 'duration': 4.182}], 'summary': 'Kevin shares 14 useful tips for pivot tables in excel to ease data analysis.', 'duration': 30.016, 'max_score': 0.009, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ09.jpg'}], 'start': 0.009, 'title': 'Excel pivot tables tips & tricks', 'summary': 'Showcases 14 tips for using pivot tables in microsoft excel, including utilizing natural language to create pivot tables and providing insights on data analysis.', 'chapters': [{'end': 80.886, 'start': 0.009, 'title': 'Excel pivot tables tips & tricks', 'summary': 'Showcases 14 tips for using pivot tables in microsoft excel, including utilizing natural language to create pivot tables and providing insights on data analysis.', 'duration': 80.877, 'highlights': ['Excel can automatically create pivot tables using natural language to answer business questions, making data analysis easier for users.', "Excel's 'analyze data' feature provides insights on data, making it easier for users to understand and interpret the information.", 'The video includes 14 tips and tricks for working with pivot tables in Microsoft Excel, demonstrating various functionalities to streamline data analysis.']}], 'duration': 80.877, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ09.jpg', 'highlights': ['Excel can automatically create pivot tables using natural language to answer business questions, making data analysis easier for users.', 'The video includes 14 tips and tricks for working with pivot tables in Microsoft Excel, demonstrating various functionalities to streamline data analysis.', "Excel's 'analyze data' feature provides insights on data, making it easier for users to understand and interpret the information."]}, {'end': 501.232, 'segs': [{'end': 107.76, 'src': 'embed', 'start': 81.406, 'weight': 1, 'content': [{'end': 87.631, 'text': 'Now, before I ask my specific question, down below I can see different recommendations or suggestions.', 'start': 81.406, 'duration': 6.225}, {'end': 94.956, 'text': 'Basically, this is Excel looking at all of my data over here and it comes up with interesting views of my data.', 'start': 88.131, 'duration': 6.825}, {'end': 101.218, 'text': "I could choose one of these and I could insert a pivot table, I could insert a pivot chart, and that's pretty easy.", 'start': 95.516, 'duration': 5.702}, {'end': 104.859, 'text': 'But I came here with a specific question in mind.', 'start': 101.598, 'duration': 3.261}, {'end': 107.76, 'text': 'I want to know how many cookies we sold to Acme Bytes.', 'start': 105.199, 'duration': 2.561}], 'summary': 'Using excel, the speaker explores data views and seeks to find the quantity of cookies sold to acme bytes.', 'duration': 26.354, 'max_score': 81.406, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ081406.jpg'}, {'end': 154.884, 'src': 'embed', 'start': 129.335, 'weight': 3, 'content': [{'end': 134.46, 'text': 'When I click on this, this opens up a new sheet with a pivot table created for me.', 'start': 129.335, 'duration': 5.125}, {'end': 139.641, 'text': "Here I'll zoom in a little bit and we see that it filtered it down to the customer I was interested in.", 'start': 134.98, 'duration': 4.661}, {'end': 144.842, 'text': 'It looks like it also figured out the date and here it tells me how many cookies we sold.', 'start': 140.061, 'duration': 4.781}, {'end': 154.884, 'text': "So here it created a pivot table on my behalf and I didn't have to do any of the heavy lifting of going in and choosing what filters or rows or values I want.", 'start': 145.262, 'duration': 9.622}], 'summary': 'Automated pivot table creation, filtered by customer, showing cookies sold.', 'duration': 25.549, 'max_score': 129.335, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0129335.jpg'}, {'end': 220.644, 'src': 'heatmap', 'start': 173.71, 'weight': 5, 'content': [{'end': 177.332, 'text': "But let's say I wanna see the specifics of some of those orders.", 'start': 173.71, 'duration': 3.622}, {'end': 180.254, 'text': 'Maybe I wanna know the revenue, the cost of some of those orders.', 'start': 177.432, 'duration': 2.822}, {'end': 183.155, 'text': 'Here I could simply double click on that value.', 'start': 180.674, 'duration': 2.481}, {'end': 187.137, 'text': 'And here I get another Excel sheet generated for me.', 'start': 183.515, 'duration': 3.622}, {'end': 191.04, 'text': 'and I see every single order by the customer Acme Bytes.', 'start': 187.597, 'duration': 3.443}, {'end': 193.642, 'text': 'I could see what product they ordered, the revenue, the cost.', 'start': 191.1, 'duration': 2.542}, {'end': 198.826, 'text': 'So this allows me to very easily jump into the details directly from a pivot table.', 'start': 194.002, 'duration': 4.824}, {'end': 204.711, 'text': 'Tip number three, you can create a pivot table using multiple tables of data.', 'start': 199.227, 'duration': 5.484}, {'end': 212.498, 'text': 'How do you do this? Well, first off, you need at least two tables and you also need some common field between those tables.', 'start': 205.132, 'duration': 7.366}, {'end': 220.644, 'text': 'Here, for example, I have a table with customer information for the Kevin Cookie Company, and this table has a customer ID.', 'start': 213.038, 'duration': 7.606}], 'summary': 'Pivot table allows easy access to order details and revenue data. multiple tables can be used with a common field.', 'duration': 46.934, 'max_score': 173.71, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0173710.jpg'}, {'end': 240.058, 'src': 'embed', 'start': 213.038, 'weight': 9, 'content': [{'end': 220.644, 'text': 'Here, for example, I have a table with customer information for the Kevin Cookie Company, and this table has a customer ID.', 'start': 213.038, 'duration': 7.606}, {'end': 228.87, 'text': "Here I'm going to jump to another table with all of our orders, and here too I have a field with the customer ID,", 'start': 221.164, 'duration': 7.706}, {'end': 231.271, 'text': 'so I can connect these two tables together.', 'start': 228.87, 'duration': 2.401}, {'end': 240.058, 'text': 'To connect them, go up to the tab on top called data, and within data, within the section called data tools, click on relationships.', 'start': 231.772, 'duration': 8.286}], 'summary': 'Connecting tables to establish relationships between customer information and orders at the kevin cookie company.', 'duration': 27.02, 'max_score': 213.038, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0213038.jpg'}, {'end': 282.744, 'src': 'heatmap', 'start': 213.038, 'weight': 8, 'content': [{'end': 220.644, 'text': 'Here, for example, I have a table with customer information for the Kevin Cookie Company, and this table has a customer ID.', 'start': 213.038, 'duration': 7.606}, {'end': 228.87, 'text': "Here I'm going to jump to another table with all of our orders, and here too I have a field with the customer ID,", 'start': 221.164, 'duration': 7.706}, {'end': 231.271, 'text': 'so I can connect these two tables together.', 'start': 228.87, 'duration': 2.401}, {'end': 240.058, 'text': 'To connect them, go up to the tab on top called data, and within data, within the section called data tools, click on relationships.', 'start': 231.772, 'duration': 8.286}, {'end': 244.412, 'text': 'Here, I can define relationships between these two tables.', 'start': 241.069, 'duration': 3.343}, {'end': 251.699, 'text': 'To create a new relationship, click on new, and here I need to define what tables I want to relate.', 'start': 244.933, 'duration': 6.766}, {'end': 256.003, 'text': "Here I'll select my customer table and I'll also select the order table.", 'start': 252.14, 'duration': 3.863}, {'end': 262.547, 'text': 'Over on the right hand side, I needed to specify what key I want to connect these on.', 'start': 256.863, 'duration': 5.684}, {'end': 270.272, 'text': "So what is the common value between these two tables? And as we saw when we looked at these two tables, it's the customer ID.", 'start': 262.888, 'duration': 7.384}, {'end': 276.115, 'text': "So here I'll select customer ID and in the order table, I'll also select the customer ID.", 'start': 270.672, 'duration': 5.443}, {'end': 278.436, 'text': "This looks good, so I'll click on OK.", 'start': 276.575, 'duration': 1.861}, {'end': 282.744, 'text': "I've now defined all my relationships, so I'll close this.", 'start': 279.622, 'duration': 3.122}], 'summary': 'Connecting customer and order tables using customer id for data analysis.', 'duration': 69.706, 'max_score': 213.038, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0213038.jpg'}, {'end': 332.353, 'src': 'embed', 'start': 304.256, 'weight': 7, 'content': [{'end': 307.718, 'text': "I'll add my new pivot table to a new worksheet and then click on OK.", 'start': 304.256, 'duration': 3.462}, {'end': 316.822, 'text': 'This now drops me into a new pivot table and I can now construct my pivot table using fields from both of these different tables.', 'start': 308.098, 'duration': 8.724}, {'end': 322.785, 'text': "So here for example, maybe I'll expand the customer table and let me pull the customer name down to rows.", 'start': 317.262, 'duration': 5.523}, {'end': 330.632, 'text': 'and maybe I want to know how many cookies did each customer order, units sold, and that sits within the order table.', 'start': 323.405, 'duration': 7.227}, {'end': 332.353, 'text': 'Here I could take units sold.', 'start': 331.012, 'duration': 1.341}], 'summary': 'Creating a new pivot table with data from multiple tables to analyze customer orders and units sold.', 'duration': 28.097, 'max_score': 304.256, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0304256.jpg'}, {'end': 369.804, 'src': 'embed', 'start': 345.064, 'weight': 0, 'content': [{'end': 350.729, 'text': 'Here I can see all of my fields, but to truly see this full list I have to scroll down.', 'start': 345.064, 'duration': 5.665}, {'end': 354.533, 'text': "Let's say maybe I want to see all of these fields all at once.", 'start': 351.13, 'duration': 3.403}, {'end': 358.676, 'text': 'I can click on this settings gear and here I could shift my field layout.', 'start': 355.033, 'duration': 3.643}, {'end': 364.541, 'text': "I'll take this one as an example and here I can see all of my fields side by side with my areas.", 'start': 358.997, 'duration': 5.544}, {'end': 369.804, 'text': 'Also, when I look over at my fields, finding a field might be difficult.', 'start': 365.482, 'duration': 4.322}], 'summary': 'User wants to view all fields at once, adjusts layout for better visibility.', 'duration': 24.74, 'max_score': 345.064, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0345064.jpg'}, {'end': 439.773, 'src': 'heatmap', 'start': 414.375, 'weight': 0.867, 'content': [{'end': 423.34, 'text': "Instead, I'll click into my pivot table, I'll go up to pivot table analyze, and under fields, items, and sets, I can add a new calculated field.", 'start': 414.375, 'duration': 8.965}, {'end': 423.96, 'text': 'Click on this.', 'start': 423.44, 'duration': 0.52}, {'end': 430.745, 'text': "Within the calculated field prompt here I can type in a name for my new field and I'll type in profit.", 'start': 424.46, 'duration': 6.285}, {'end': 436.51, 'text': 'Down below I can enter in the formula and profit is the revenue minus the cost.', 'start': 431.246, 'duration': 5.264}, {'end': 439.773, 'text': "That looks good so I'll click on add and then click on OK.", 'start': 436.931, 'duration': 2.842}], 'summary': "Creating a new calculated field 'profit' in a pivot table by subtracting revenue from cost.", 'duration': 25.398, 'max_score': 414.375, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0414375.jpg'}, {'end': 490.669, 'src': 'embed', 'start': 461.772, 'weight': 6, 'content': [{'end': 468.437, 'text': 'Over here, I can right click and within this menu, I can go down to sort and I can select largest to smallest.', 'start': 461.772, 'duration': 6.665}, {'end': 473.622, 'text': 'And right here, I can see that Acme Bites is our most profitable customer.', 'start': 468.858, 'duration': 4.764}, {'end': 477.425, 'text': 'Trey Delicious, we need to work on driving some more profitability.', 'start': 474.022, 'duration': 3.403}, {'end': 482.646, 'text': 'Tip number seven, I can show values differently within my pivot table.', 'start': 477.885, 'duration': 4.761}, {'end': 490.669, 'text': "Now right here with this profit column, it's just showing me the pure and total amount of profit for each one of these customers.", 'start': 483.146, 'duration': 7.523}], 'summary': 'Pivot table reveals acme bites as top customer; improvement needed for trey delicious.', 'duration': 28.897, 'max_score': 461.772, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0461772.jpg'}], 'start': 81.406, 'title': 'Excel pivot table tips and tricks', 'summary': 'Covers how to use excel pivot tables to quickly analyze and summarize data, create pivot tables using multiple data tables and defining relationships, and provides tips for optimizing pivot table usage, including customizing field layout and adding new calculated fields.', 'chapters': [{'end': 193.642, 'start': 81.406, 'title': 'Excel pivot table tips', 'summary': 'Discusses how to use excel pivot tables to quickly analyze and summarize data, demonstrating the automated data analysis feature and the ability to drill down into specific details, such as selling 255,000 cookies to acme bytes.', 'duration': 112.236, 'highlights': ['Excel automatically analyzed the data and determined that 255,000 cookies were sold to the customer Acme Bytes, showcasing the automated data analysis feature.', 'By clicking into the values in a pivot table, users can access additional details and generate a new Excel sheet, providing a deeper dive into specific orders and related revenue and cost.', 'The demonstration of how Excel simplifies the process of creating pivot tables by automatically filtering data and generating a table, saving time and effort for the user.']}, {'end': 344.584, 'start': 194.002, 'title': 'Pivot table creation and relationship building', 'summary': 'Discusses creating a pivot table using multiple tables of data, explaining the process of defining relationships between tables and constructing a pivot table with fields from different tables, with a tip on changing field layout and sorting options.', 'duration': 150.582, 'highlights': ['Defining relationships between tables The process of defining relationships between tables is explained, involving the selection of tables to relate, specifying the common key, and creating relationships, enabling the creation of a new data model.', 'Creating a pivot table using multiple tables The process of creating a pivot table using data from multiple tables is detailed, demonstrating the establishment of relationships between tables and constructing a pivot table with fields from different tables.', 'Constructing a pivot table with fields from different tables The construction of a pivot table using fields from different tables is explained, exemplifying the inclusion of customer name from one table and units sold from another table, showcasing the ability to combine data from diverse sources.', 'Changing field layout and sorting options The tip on changing the field layout and sorting options within a pivot table is provided, offering insights into customizing the display and sorting of data for enhanced analysis and presentation.']}, {'end': 501.232, 'start': 345.064, 'title': 'Pivot table tips and tricks', 'summary': 'Introduces various tips for optimizing pivot table usage, such as customizing field layout, adding new calculated fields, and sorting and displaying values in different ways to enhance data analysis and visualization.', 'duration': 156.168, 'highlights': ['7 Showing values differently within the pivot table allows for better analysis, such as determining profitability rank or percentage of total profit per customer.', '6 Easily sorting values within the pivot table enables quick identification of the most profitable customer and areas that require improvement.', '5 Adding new calculated fields provides flexibility in customizing data presentation, such as including additional columns like profit based on a specific formula.']}], 'duration': 419.826, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ081406.jpg', 'highlights': ['Excel automatically analyzed the data and determined that 255,000 cookies were sold to the customer Acme Bytes, showcasing the automated data analysis feature.', 'By clicking into the values in a pivot table, users can access additional details and generate a new Excel sheet, providing a deeper dive into specific orders and related revenue and cost.', 'The demonstration of how Excel simplifies the process of creating pivot tables by automatically filtering data and generating a table, saving time and effort for the user.', 'Defining relationships between tables The process of defining relationships between tables is explained, involving the selection of tables to relate, specifying the common key, and creating relationships, enabling the creation of a new data model.', 'Creating a pivot table using multiple tables The process of creating a pivot table using data from multiple tables is detailed, demonstrating the establishment of relationships between tables and constructing a pivot table with fields from different tables.', 'Constructing a pivot table with fields from different tables The construction of a pivot table using fields from different tables is explained, exemplifying the inclusion of customer name from one table and units sold from another table, showcasing the ability to combine data from diverse sources.', 'Changing field layout and sorting options The tip on changing the field layout and sorting options within a pivot table is provided, offering insights into customizing the display and sorting of data for enhanced analysis and presentation.', 'Showing values differently within the pivot table allows for better analysis, such as determining profitability rank or percentage of total profit per customer.', 'Easily sorting values within the pivot table enables quick identification of the most profitable customer and areas that require improvement.', 'Adding new calculated fields provides flexibility in customizing data presentation, such as including additional columns like profit based on a specific formula.']}, {'end': 1028.993, 'segs': [{'end': 731.613, 'src': 'heatmap', 'start': 690.065, 'weight': 0, 'content': [{'end': 692.406, 'text': 'I could just click on Acme Bytes and look at that.', 'start': 690.065, 'duration': 2.341}, {'end': 695.929, 'text': 'My view is automatically updated using these slicers.', 'start': 692.486, 'duration': 3.443}, {'end': 698.01, 'text': 'It makes filtering a lot easier.', 'start': 696.069, 'duration': 1.941}, {'end': 705.195, 'text': 'Tip number 10, we can insert a timeline to also very quickly filter our data based on dates.', 'start': 698.29, 'duration': 6.905}, {'end': 712.2, 'text': "Once again, right up on top under pivot table analyze under filter, there's the option to insert a timeline.", 'start': 705.995, 'duration': 6.205}, {'end': 715.702, 'text': "Over here, I'll select date and then click on OK.", 'start': 712.72, 'duration': 2.982}, {'end': 725.549, 'text': "So let's say that maybe my manager comes up to me and he wants to know how much all of our customers earned between January and April in 2020.", 'start': 716.342, 'duration': 9.207}, {'end': 731.613, 'text': 'Well, luckily I now have this handy new timeline and I could use this to very quickly get that answer.', 'start': 725.549, 'duration': 6.064}], 'summary': 'Using slicers and timeline to filter data quickly and efficiently.', 'duration': 41.548, 'max_score': 690.065, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0690065.jpg'}, {'end': 764.154, 'src': 'embed', 'start': 736.517, 'weight': 5, 'content': [{'end': 741.763, 'text': 'It automatically updates the profit amount for each customer between that time range.', 'start': 736.517, 'duration': 5.246}, {'end': 746.127, 'text': "Now my boss thinks I'm a superstar and I'll probably get a promotion.", 'start': 742.283, 'duration': 3.844}, {'end': 747.668, 'text': 'Tip number 11.', 'start': 746.648, 'duration': 1.02}, {'end': 751.63, 'text': '11, you can use a pivot chart to visualize your data.', 'start': 747.668, 'duration': 3.962}, {'end': 759.912, 'text': 'When you have your cursor within your pivot table, go up to pivot table analyze and over under tools, you can insert a pivot chart.', 'start': 752.15, 'duration': 7.762}, {'end': 764.154, 'text': "I'll click on that and I'll go with one of these column charts and then click on OK.", 'start': 760.392, 'duration': 3.762}], 'summary': 'Automatically updates profit for each customer, leading to recognition and potential promotion. tip 11: use pivot chart to visualize data.', 'duration': 27.637, 'max_score': 736.517, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0736517.jpg'}, {'end': 836.592, 'src': 'embed', 'start': 804.962, 'weight': 1, 'content': [{'end': 806.382, 'text': "Maybe I don't want to see that.", 'start': 804.962, 'duration': 1.42}, {'end': 814.646, 'text': 'Well, up here I can go over to design when I have the pivot table selected, and right here I can choose to not show any subtitles.', 'start': 806.482, 'duration': 8.164}, {'end': 817.007, 'text': 'So there you see the subtitles disappeared.', 'start': 814.986, 'duration': 2.021}, {'end': 819.568, 'text': 'I can always click back if I want to bring them back.', 'start': 817.107, 'duration': 2.461}, {'end': 822.909, 'text': 'Also, when I go down here, I see the grand total.', 'start': 820.288, 'duration': 2.621}, {'end': 827.69, 'text': "I could click over here and I could turn off the grand totals if I don't want to see them.", 'start': 823.049, 'duration': 4.641}, {'end': 829.79, 'text': 'And once again, I could bring them back if I want.', 'start': 827.71, 'duration': 2.08}, {'end': 836.592, 'text': 'Along with turning subtotals and grand totals on or off, I could also change the layout of this pivot table.', 'start': 830.13, 'duration': 6.462}], 'summary': 'Demonstrating how to toggle subtitles and grand totals on a pivot table.', 'duration': 31.63, 'max_score': 804.962, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0804962.jpg'}, {'end': 869.23, 'src': 'embed', 'start': 842.193, 'weight': 2, 'content': [{'end': 845.914, 'text': 'Here I see the customer and the products directly underneath the customer name.', 'start': 842.193, 'duration': 3.721}, {'end': 850.577, 'text': 'Now I could change this to outline form and here I get a separate column with the product.', 'start': 846.354, 'duration': 4.223}, {'end': 854.68, 'text': 'Now this could be beneficial if say maybe you want to filter the product separately.', 'start': 850.937, 'duration': 3.743}, {'end': 858.983, 'text': 'This might make it a little bit easier and I have other options like tabular form.', 'start': 854.72, 'duration': 4.263}, {'end': 864.908, 'text': 'You can go through here and you can experiment with these different formats to see which one you like the best.', 'start': 859.344, 'duration': 5.564}, {'end': 869.23, 'text': "Now, let's say you go through and you customize what your pivot table looks like.", 'start': 865.368, 'duration': 3.862}], 'summary': 'Demonstrates different formats for presenting customer and product data, offering versatility for customization.', 'duration': 27.037, 'max_score': 842.193, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0842193.jpg'}, {'end': 903.713, 'src': 'heatmap', 'start': 873.693, 'weight': 0.751, 'content': [{'end': 882.678, 'text': 'If you want to set the default, you can go to the file menu, go down to options, and under data, you can click on edit default layout.', 'start': 873.693, 'duration': 8.985}, {'end': 887.481, 'text': 'And here you can choose what it should look like every time you create a pivot table.', 'start': 883.098, 'duration': 4.383}, {'end': 891.563, 'text': 'Tip number 13, you can group your row labels together.', 'start': 887.941, 'duration': 3.622}, {'end': 899.309, 'text': "Let's say that for whatever arbitrary reason, I want to know how my customers who start with A compare to other customers.", 'start': 892.003, 'duration': 7.306}, {'end': 903.713, 'text': 'So ABC Groceries and Acme Bytes both meet that criteria.', 'start': 899.729, 'duration': 3.984}], 'summary': 'Set default layout for pivot tables and group row labels. compare customer data.', 'duration': 30.02, 'max_score': 873.693, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0873693.jpg'}, {'end': 935.155, 'src': 'heatmap', 'start': 906.115, 'weight': 0.722, 'content': [{'end': 908.456, 'text': "I'll right click and I'll select group.", 'start': 906.115, 'duration': 2.341}, {'end': 911.239, 'text': "So now you see that they're part of group one.", 'start': 908.937, 'duration': 2.302}, {'end': 917.424, 'text': "Here I'm going to collapse all of the fields and now I can compare this group to all of my other customers.", 'start': 911.739, 'duration': 5.685}, {'end': 925.008, 'text': "Tip number 14, and unfortunately, this is the last tip of today, but hopefully you've learned some good ones already.", 'start': 917.904, 'duration': 7.104}, {'end': 932.013, 'text': 'With tip number 14, you can update the row labels to appear how you want them to appear.', 'start': 925.689, 'duration': 6.324}, {'end': 935.155, 'text': 'Now, right now, this is just sorted alphabetically.', 'start': 932.693, 'duration': 2.462}], 'summary': 'Demonstrating how to group data for comparison and customize row labels in excel.', 'duration': 29.04, 'max_score': 906.115, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0906115.jpg'}, {'end': 963.001, 'src': 'embed', 'start': 935.515, 'weight': 6, 'content': [{'end': 940.48, 'text': 'but for whatever reason, my manager really loves Trade Delicious as a customer.', 'start': 935.515, 'duration': 4.965}, {'end': 948.689, 'text': "I don't know why, they're our least profitable customer, but you know, whatever, and he always likes to see Trade Delicious at the top of the list.", 'start': 940.901, 'duration': 7.788}, {'end': 954.655, 'text': "Here, I can select Trade Delicious, I can right click, and I can move this up the list if I'd like.", 'start': 949.129, 'duration': 5.526}, {'end': 958.518, 'text': 'Here I can move it to the beginning, I can move it up, down, or to the end.', 'start': 955.015, 'duration': 3.503}, {'end': 963.001, 'text': "Alternatively, I can press control X on that item, that'll cut it.", 'start': 958.958, 'duration': 4.043}], 'summary': 'Manager favors trade delicious despite being least profitable customer.', 'duration': 27.486, 'max_score': 935.515, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0935515.jpg'}], 'start': 501.632, 'title': 'Pivot table tips and tricks', 'summary': 'Covers tips and tricks for pivot tables, including showing values as percent of column total, ranking largest to smallest, utilizing slicers and timelines for data filtering, creating pivot charts, and customizing pivot table layouts, introducing 14 tips for effective use.', 'chapters': [{'end': 667.991, 'start': 501.632, 'title': 'Pivot table tips and tricks', 'summary': 'Covers pivot table tips and tricks including showing values as percent of column total, ranking largest to smallest, summarizing values, using slicers for quick data filtering, and customizing pivot table headers.', 'duration': 166.359, 'highlights': ['Acme Bytes makes up over 30% of the profit, ranking as the number one customer. Acme Bytes contributes over 30% of the profit, ranking as the top customer in the pivot table.', 'Using slicers to quickly filter data based on specific criteria such as customer name and product. Slicers are used to efficiently filter data based on customer name and product, enabling quick analysis and data manipulation.', 'Showing values as percent of column total and customizing pivot table headers for better representation of data. The option to show values as percent of column total and the ability to customize pivot table headers enhance the representation and interpretation of data.', 'Changing the summarization of values within the pivot table from sum to count for a more meaningful analysis. The process of changing the summarization of values from sum to count within the pivot table enables a more meaningful analysis, especially for items such as orders by customer.']}, {'end': 1028.993, 'start': 668.511, 'title': 'Pivot table tips and tricks', 'summary': 'Introduces 14 tips and tricks for effectively using pivot tables, including utilizing slicers and timelines for data filtering, creating pivot charts for visualizing data, customizing pivot table layouts, grouping row labels, and updating row labels to appear as desired.', 'duration': 360.482, 'highlights': ['Utilizing slicers and timelines for data filtering The chapter highlights the use of slicers and timelines to quickly filter data, enabling the user to analyze the profit amount for each customer between specific time ranges, resulting in improved data visualization and decision-making.', 'Creating pivot charts for visualizing data The chapter emphasizes the creation of pivot charts to visualize profit by customer over specific time frames, illustrating the dynamic updating of the pivot chart based on the applied filters, enhancing data interpretation and presentation.', 'Customizing pivot table layouts The chapter explains the customization of pivot table layouts, including the ability to turn off subtotals and grand totals, change the layout format, and set default layout preferences, providing flexibility in presenting and analyzing pivot table data.', 'Grouping row labels and updating row labels The chapter demonstrates the grouping of row labels to compare specific customer groups and the updating of row labels to appear as desired, offering efficient organization and customization of pivot table data for improved analysis and presentation.']}], 'duration': 527.361, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/3g1CSG5zvQ0/pics/3g1CSG5zvQ0501632.jpg', 'highlights': ['Acme Bytes makes up over 30% of the profit, ranking as the number one customer.', 'Utilizing slicers and timelines for data filtering.', 'Creating pivot charts for visualizing data.', 'Showing values as percent of column total and customizing pivot table headers for better representation of data.', 'Customizing pivot table layouts.', 'Changing the summarization of values within the pivot table from sum to count for a more meaningful analysis.', 'Grouping row labels and updating row labels.']}], 'highlights': ['Excel can automatically create pivot tables using natural language to answer business questions, making data analysis easier for users.', 'The video includes 14 tips and tricks for working with pivot tables in Microsoft Excel, demonstrating various functionalities to streamline data analysis.', "Excel's 'analyze data' feature provides insights on data, making it easier for users to understand and interpret the information.", 'Excel automatically analyzed the data and determined that 255,000 cookies were sold to the customer Acme Bytes, showcasing the automated data analysis feature.', 'By clicking into the values in a pivot table, users can access additional details and generate a new Excel sheet, providing a deeper dive into specific orders and related revenue and cost.', 'The demonstration of how Excel simplifies the process of creating pivot tables by automatically filtering data and generating a table, saving time and effort for the user.', 'Defining relationships between tables The process of defining relationships between tables is explained, involving the selection of tables to relate, specifying the common key, and creating relationships, enabling the creation of a new data model.', 'Creating a pivot table using multiple tables The process of creating a pivot table using data from multiple tables is detailed, demonstrating the establishment of relationships between tables and constructing a pivot table with fields from different tables.', 'Constructing a pivot table with fields from different tables The construction of a pivot table using fields from different tables is explained, exemplifying the inclusion of customer name from one table and units sold from another table, showcasing the ability to combine data from diverse sources.', 'Changing field layout and sorting options The tip on changing the field layout and sorting options within a pivot table is provided, offering insights into customizing the display and sorting of data for enhanced analysis and presentation.', 'Showing values differently within the pivot table allows for better analysis, such as determining profitability rank or percentage of total profit per customer.', 'Easily sorting values within the pivot table enables quick identification of the most profitable customer and areas that require improvement.', 'Adding new calculated fields provides flexibility in customizing data presentation, such as including additional columns like profit based on a specific formula.', 'Acme Bytes makes up over 30% of the profit, ranking as the number one customer.', 'Utilizing slicers and timelines for data filtering.', 'Creating pivot charts for visualizing data.', 'Showing values as percent of column total and customizing pivot table headers for better representation of data.', 'Customizing pivot table layouts.', 'Changing the summarization of values within the pivot table from sum to count for a more meaningful analysis.', 'Grouping row labels and updating row labels.']}