title
How to Create Pivot Table in Excel
description
🔥 Go from Excel novice to data analysis ninja in just 2 hours with my Excel for Beginners course: https://kevinstratvert.thinkific.com
In this step-by-step tutorial for beginners, learn how to create a pivot table in Microsoft Excel. With an Excel Pivot Table, you can very quickly and easily analyze data without needing to use any formulas or functions. You can generate insights just by dragging and dropping fields. We start with how to prep your data and then walk through all the core functionality of pivot tables, including sorting, filtering, inserting charts, slicers, timelines, and more. By the end of this tutorial, you'll be confident using pivot tables in Excel.
📚 RESOURCES
- Exercise workbook to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whO06YVUWhUW1CWJ93A?e=eOmbbu
- Excel for Beginners course: https://kevinstratvert.thinkific.com/
⌚ TIMESTAMPS
00:00 Introduction
00:41 Overview of data
01:56 Prep data
02:48 Turn data into table
03:43 Insert pivot table
04:16 Pivot table interface
04:59 Build basic pivot table with rows and values
06:27 Change formatting
06:40 Summarize values by
07:59 Remove fields
08:43 Sorting
09:47 Filtering
11:03 Show values as
11:54 Custom field headers
12:19 Multiple rows
12:50 Columns
13:30 Design
14:13 Pivot charts
14:44 Insert slicer
15:25 Insert timeline
15:56 Copy pivot table
16:13 Calculated fields
17:25 Refresh for new data
18:20 Source data
18:33 Recommended and Analyze Data
20:02 Wrap up
📺 RELATED VIDEOS
- Playlist with all my videos on Excel: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY
📩 NEWSLETTER
- Get the latest high-quality tutorial and tips and tricks videos emailed to your inbox each week: https://kevinstratvert.com/newsletter/
🔽 CONNECT WITH ME
- Official web site: http://www.kevinstratvert.com
- LinkedIn: https://www.linkedin.com/in/kevinstratvert/
- Discord: https://bit.ly/KevinStratvertDiscord
- 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/
🎒 MY COURSES
- Excel for Beginners in under 2 hours: https://kevinstratvert.thinkific.com/
🙏 REQUEST VIDEOS
https://forms.gle/BDrTNUoxheEoMLGt5
🔔 SUBSCRIBE ON YOUTUBE
https://www.youtube.com/user/kevlers?sub_confirmation=1
🙌 SUPPORT THE CHANNEL
- Hit the THANKS button in any video!
- Amazon affiliate link: https://amzn.to/3kCP2yz (Purchasing through this link gives me a small commission to support videos on this channel -- the price to you is the same)
#stratvert #excel
detail
{'title': 'How to Create Pivot Table in Excel', 'heatmap': [{'end': 243.7, 'start': 181.969, 'weight': 0.794}, {'end': 900.101, 'start': 860.464, 'weight': 0.914}], 'summary': 'Learn to create pivot tables in excel for quick data analysis without formulas or functions, using a sample workbook. analyze sales data for kevin cookie company, visualize revenue and order counts, and customize pivot tables for efficient data analysis and visualization, including profit analysis for customer profitability.', 'chapters': [{'end': 48.776, 'segs': [{'end': 48.776, 'src': 'embed', 'start': 0.149, 'weight': 0, 'content': [{'end': 1.491, 'text': 'Hi everyone, Kevin here.', 'start': 0.149, 'duration': 1.342}, {'end': 6.438, 'text': 'Today, I want to show you how you can create pivot tables in Excel.', 'start': 1.691, 'duration': 4.747}, {'end': 10.764, 'text': 'Here, I have lots and lots of data that I need to analyze.', 'start': 7.299, 'duration': 3.465}, {'end': 20.195, 'text': "Now I could build a table over on the side using formulas and functions to make sense of it, but that's going to take a long time.", 'start': 11.587, 'duration': 8.608}, {'end': 26.72, 'text': 'Instead, we can use something called pivot tables to analyze this data in just seconds.', 'start': 21.175, 'duration': 5.545}, {'end': 31.224, 'text': "And best of all, you don't have to know any formulas or functions.", 'start': 27.341, 'duration': 3.883}, {'end': 33.666, 'text': "It's all just drag and drop.", 'start': 31.965, 'duration': 1.701}, {'end': 38.491, 'text': "To follow along, I've included a sample workbook down below in the description.", 'start': 34.527, 'duration': 3.964}, {'end': 41.289, 'text': "All right, let's check this out.", 'start': 39.608, 'duration': 1.681}, {'end': 48.776, 'text': "Here I am in Excel and once again, if you want to follow along, I've included a sample workbook down below in the description.", 'start': 41.65, 'duration': 7.126}], 'summary': 'Learn to create pivot tables in excel for quick data analysis.', 'duration': 48.627, 'max_score': 0.149, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4149.jpg'}], 'start': 0.149, 'title': 'Creating pivot tables in excel', 'summary': 'Demonstrates how to create pivot tables in excel to analyze large amounts of data quickly without the need for formulas or functions, with a sample workbook provided for practice.', 'chapters': [{'end': 48.776, 'start': 0.149, 'title': 'Creating pivot tables in excel', 'summary': 'Demonstrates how to create pivot tables in excel to analyze large amounts of data quickly without the need for formulas or functions, with a sample workbook provided for practice.', 'duration': 48.627, 'highlights': ['Pivot tables can analyze large amounts of data in seconds without the need for formulas or functions, saving time and effort.', 'Sample workbook is provided in the description for practical application and hands-on learning.']}], 'duration': 48.627, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4149.jpg', 'highlights': ['Pivot tables can analyze large amounts of data in seconds without the need for formulas or functions, saving time and effort.', 'Sample workbook is provided in the description for practical application and hands-on learning.']}, {'end': 466.434, 'segs': [{'end': 95.712, 'src': 'embed', 'start': 68.672, 'weight': 0, 'content': [{'end': 74.698, 'text': 'So, here customer one, we made about a little over half a million dollars over 92 different orders.', 'start': 68.672, 'duration': 6.026}, {'end': 78.161, 'text': 'To build this table, I just used formulas and functions.', 'start': 75.499, 'duration': 2.662}, {'end': 81.784, 'text': 'So, here when I click into this cell, here we can see the function that I used.', 'start': 78.401, 'duration': 3.383}, {'end': 83.066, 'text': 'I used SUMIFS.', 'start': 81.865, 'duration': 1.201}, {'end': 87.37, 'text': 'And here for the number of orders, here too I also entered in a function.', 'start': 83.866, 'duration': 3.504}, {'end': 95.712, 'text': 'Now, of course this works just for a few specific questions, but it takes a bit of time to pull together something like this,', 'start': 88.427, 'duration': 7.285}], 'summary': 'Customer one generated over half a million dollars from 92 orders using sumifs and other functions.', 'duration': 27.04, 'max_score': 68.672, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo468672.jpg'}, {'end': 133.714, 'src': 'embed', 'start': 106.71, 'weight': 5, 'content': [{'end': 112.994, 'text': "Now, of course, I could build out this table to answer some of those questions, but that's going to take a long time.", 'start': 106.71, 'duration': 6.284}, {'end': 116.276, 'text': 'Instead, we can use something called pivot tables.', 'start': 113.695, 'duration': 2.581}, {'end': 120.239, 'text': 'Before we can insert a pivot table, we need to prep the data.', 'start': 116.897, 'duration': 3.342}, {'end': 124.662, 'text': 'Over here on the left-hand side, I have all of my data in a tabular format.', 'start': 120.999, 'duration': 3.663}, {'end': 128.003, 'text': 'So here I have my headers and then I have the associated data underneath.', 'start': 124.842, 'duration': 3.161}, {'end': 133.714, 'text': "Second, you'll also want to make sure that all of your column headers are descriptive of the data underneath.", 'start': 129.15, 'duration': 4.564}], 'summary': 'Use pivot tables to analyze tabular data efficiently.', 'duration': 27.004, 'max_score': 106.71, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4106710.jpg'}, {'end': 243.7, 'src': 'heatmap', 'start': 181.969, 'weight': 0.794, 'content': [{'end': 187.853, 'text': "To turn this into a table, go up to the insert tab up on top and here's the option to insert a table.", 'start': 181.969, 'duration': 5.884}, {'end': 192.656, 'text': "When I hover over this, you'll see that the shortcut key is Control T.", 'start': 188.453, 'duration': 4.203}, {'end': 199.52, 'text': "Here, I'll press Control T and here it automatically identifies all the data that should be included as part of this table.", 'start': 192.656, 'duration': 6.864}, {'end': 200.981, 'text': 'That all looks pretty good.', 'start': 200.12, 'duration': 0.861}, {'end': 206.347, 'text': 'And right here it also asks me if the table has headers and my table does have headers.', 'start': 201.722, 'duration': 4.625}, {'end': 209.591, 'text': "I'll make sure that box is checked and then click on OK.", 'start': 206.908, 'duration': 2.683}, {'end': 212.675, 'text': 'And this is now an official Excel table.', 'start': 210.372, 'duration': 2.303}, {'end': 218.695, 'text': 'On the table design tab over on the left-hand side, I can give this table a name.', 'start': 214.332, 'duration': 4.363}, {'end': 223.419, 'text': "I'll call it sales data and then hit enter, so this table now has a name.", 'start': 219.436, 'duration': 3.983}, {'end': 226.081, 'text': 'We are now ready to insert a pivot table.', 'start': 223.879, 'duration': 2.202}, {'end': 234.688, 'text': 'Up here under table design, I can summarize with a pivot table or alternatively, I can click on insert and here I can click on pivot table.', 'start': 226.942, 'duration': 7.746}, {'end': 243.7, 'text': 'When I click on that, it asks me what I would like to include as part of my pivot table, and we already created a table called Sales Data,', 'start': 235.754, 'duration': 7.946}], 'summary': "Demonstrating how to create an excel table and pivot table using shortcut keys and naming the table as 'sales data'.", 'duration': 61.731, 'max_score': 181.969, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4181969.jpg'}, {'end': 364.648, 'src': 'embed', 'start': 336.348, 'weight': 1, 'content': [{'end': 339.852, 'text': 'And over on the left-hand side, here I can see my pivot table start coming together.', 'start': 336.348, 'duration': 3.504}, {'end': 342.114, 'text': 'Here I see all of those customer IDs.', 'start': 340.272, 'duration': 1.842}, {'end': 346.057, 'text': 'Next, I want to add the revenue next to the customer.', 'start': 342.934, 'duration': 3.123}, {'end': 349.98, 'text': "I'll click back into sales data and here I have the total revenue.", 'start': 346.337, 'duration': 3.643}, {'end': 354.244, 'text': 'This is the sum of the revenue by each individual customer.', 'start': 350.581, 'duration': 3.663}, {'end': 360.006, 'text': "Let's go back to the pivot table and over here I have a field called revenue.", 'start': 355.245, 'duration': 4.761}, {'end': 364.648, 'text': "I'll click on that and this time I'll drag it down to values.", 'start': 360.807, 'duration': 3.841}], 'summary': 'Pivot table shows total revenue by customer ids.', 'duration': 28.3, 'max_score': 336.348, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4336348.jpg'}, {'end': 457.771, 'src': 'embed', 'start': 427.979, 'weight': 3, 'content': [{'end': 432.404, 'text': 'Here I had 92 orders for customer one, but here I have a massive number.', 'start': 427.979, 'duration': 4.425}, {'end': 440.567, 'text': 'What happened? Well, if we look down at values, this gave me the sum of all of the order IDs, so it summed up these order IDs.', 'start': 432.764, 'duration': 7.803}, {'end': 441.987, 'text': "Now, that doesn't make any sense.", 'start': 440.607, 'duration': 1.38}, {'end': 450.93, 'text': "I'll go back to the pivot table and here I can right click on this and I can go down to summarize values by and.", 'start': 442.587, 'duration': 8.343}, {'end': 453.49, 'text': "currently it's set to sum, but here I can change it to count.", 'start': 450.93, 'duration': 2.56}, {'end': 457.771, 'text': 'I could also change it to average, max, min, product, so I have all of these different options.', 'start': 453.91, 'duration': 3.861}], 'summary': 'Transcript addresses issue of summing order ids instead of counting them, demonstrating how to switch to count summary in pivot table.', 'duration': 29.792, 'max_score': 427.979, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4427979.jpg'}], 'start': 49.697, 'title': 'Sales data analysis and pivot tables in excel', 'summary': 'Covers the analysis of sales data for the kevin cookie company, revealing revenue and orders insights, including earning over half a million dollars from customer one over 92 orders. it also demonstrates creating pivot tables in excel to visualize and analyze revenue and order counts, resulting in clear insights for decision-making.', 'chapters': [{'end': 105.839, 'start': 49.697, 'title': 'Sales data analysis for kevin cookie company', 'summary': 'Covers the analysis of sales data for the kevin cookie company, including revenue and orders from customers, with a focus on using formulas and functions to derive insights, such as earning over half a million dollars from customer one over 92 orders.', 'duration': 56.142, 'highlights': ['Using SUMIFS function to calculate revenue earned from each customer, with customer one generating over half a million dollars.', 'Applying functions to determine the number of orders placed by each customer, such as 92 orders from customer one.', 'Exploring the time-consuming process of pulling together specific data and the potential for additional inquiries regarding revenue by customer and product, and orders in specific months.']}, {'end': 466.434, 'start': 106.71, 'title': 'Creating pivot tables in excel', 'summary': 'Demonstrates how to prepare data for a pivot table in excel, create an official excel table to facilitate future data inclusion, and use pivot table fields to summarize and calculate data, resulting in a clear visualization and analysis of revenue and order counts.', 'duration': 359.724, 'highlights': ['The process of preparing data for a pivot table, including ensuring descriptive column headers, deleting blank rows, and converting data into an official Excel table, is explained to facilitate easy analysis and future data inclusion. The speaker explains the importance of descriptive column headers, deleting blank rows, and converting data into an official Excel table to facilitate future data inclusion, which simplifies the process and ensures future data will be included in the analysis.', 'The steps for creating a pivot table in Excel, including selecting the data source, choosing to create a new worksheet, and utilizing pivot table fields to summarize and calculate data, are demonstrated to generate a clear visualization and analysis of revenue and order counts. The speaker demonstrates the steps for creating a pivot table in Excel, including selecting the data source, choosing to create a new worksheet, and utilizing pivot table fields to summarize and calculate data, resulting in a clear visualization and analysis of revenue and order counts.', 'The process of dragging and dropping fields, such as customer ID and revenue, into the pivot table to calculate and visualize the sum of revenue by each individual customer and the count of orders, is explained to effectively represent and analyze the data. The speaker explains the process of dragging and dropping fields into the pivot table to calculate and visualize the sum of revenue by each individual customer and the count of orders, providing an effective representation and analysis of the data.']}], 'duration': 416.737, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo449697.jpg', 'highlights': ['Earning over half a million dollars from customer one over 92 orders.', 'Creating pivot tables in Excel to visualize and analyze revenue and order counts, resulting in clear insights for decision-making.', 'Using SUMIFS function to calculate revenue earned from each customer, with customer one generating over half a million dollars.', 'Applying functions to determine the number of orders placed by each customer, such as 92 orders from customer one.', 'The process of dragging and dropping fields into the pivot table to calculate and visualize the sum of revenue by each individual customer and the count of orders, providing an effective representation and analysis of the data.', 'The process of preparing data for a pivot table, including ensuring descriptive column headers, deleting blank rows, and converting data into an official Excel table, is explained to facilitate easy analysis and future data inclusion.']}, {'end': 591.796, 'segs': [{'end': 495.883, 'src': 'embed', 'start': 467.214, 'weight': 0, 'content': [{'end': 473.196, 'text': 'So here I was able to build the exact same view simply by dragging and dropping my mouse.', 'start': 467.214, 'duration': 5.982}, {'end': 476.537, 'text': "I didn't have to enter a single formula or function in.", 'start': 473.676, 'duration': 2.861}, {'end': 479.538, 'text': 'Once again, pivot tables are so easy to use.', 'start': 477.017, 'duration': 2.521}, {'end': 487.06, 'text': "Now that I have my pivot table in place, it's so easy to analyze your data and to look at different views of your data.", 'start': 480.058, 'duration': 7.002}, {'end': 490.981, 'text': "Let's say maybe I no longer want to see the accounts of order IDs.", 'start': 487.84, 'duration': 3.141}, {'end': 495.883, 'text': 'Here, I simply click on that and I could drag it out and that updates my pivot table.', 'start': 491.741, 'duration': 4.142}], 'summary': 'Creating pivot table without formulas, easy data analysis and customization.', 'duration': 28.669, 'max_score': 467.214, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4467214.jpg'}, {'end': 604.84, 'src': 'embed', 'start': 571.571, 'weight': 3, 'content': [{'end': 575.512, 'text': "I'll move four to the beginning and we start with four followed by one, two, three, and five.", 'start': 571.571, 'duration': 3.941}, {'end': 583.454, 'text': "I can also press control X to cut this and I'll go down here and press paste and that pastes in customer four back down here.", 'start': 576.292, 'duration': 7.162}, {'end': 588.075, 'text': 'So, I can define the order in which all of these items appear in my pivot table.', 'start': 583.914, 'duration': 4.161}, {'end': 591.796, 'text': 'Along with sorting a list, I can also filter this list.', 'start': 588.835, 'duration': 2.961}, {'end': 596.397, 'text': "So, let's say I just want to see customers one, two, and three to see how much revenue they earned.", 'start': 592.036, 'duration': 4.361}, {'end': 604.84, 'text': "I can click on this dropdown and right here I can uncheck select all and here I'll select one, two, and three and then click on OK.", 'start': 597.017, 'duration': 7.823}], 'summary': 'Demonstrates sorting, filtering, and pasting in pivot table with data manipulation.', 'duration': 33.269, 'max_score': 571.571, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4571571.jpg'}], 'start': 467.214, 'title': 'Pivot table usage and customization', 'summary': 'Demonstrates how to easily build and analyze pivot tables, showcasing features like sorting and filtering for efficient data analysis and presentation.', 'chapters': [{'end': 591.796, 'start': 467.214, 'title': 'Pivot table usage and customization', 'summary': 'Demonstrates how to easily build, analyze, switch views, and customize pivot tables, showcasing features like sorting and filtering for efficient data analysis and presentation.', 'duration': 124.582, 'highlights': ['Pivot tables are easy to use for analyzing and switching views of data. The speaker emphasizes the ease of building, analyzing, and switching views of data using pivot tables, highlighting the simplicity of dragging and dropping to update the table.', 'Demonstrates customizing pivot table views by rearranging and sorting data. The speaker showcases the ability to easily rearrange the pivot table view by dragging and dropping fields, as well as demonstrating sorting functionality for analyzing data based on different criteria.', 'Illustrates the customization of pivot table order and appearance. The speaker explains the process of customizing the order and appearance of items within the pivot table, including moving items to specific positions and using cut and paste functionality to define the order of items.', 'Shows how to filter data within the pivot table. The speaker demonstrates the capability to filter data within the pivot table, providing an example of filtering to display specific customer data, showcasing the versatility and customization options available.']}], 'duration': 124.582, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4467214.jpg', 'highlights': ['Pivot tables are easy to use for analyzing and switching views of data.', 'Demonstrates customizing pivot table views by rearranging and sorting data.', 'Illustrates the customization of pivot table order and appearance.', 'Shows how to filter data within the pivot table.']}, {'end': 959.192, 'segs': [{'end': 683.882, 'src': 'embed', 'start': 655.158, 'weight': 0, 'content': [{'end': 659.998, 'text': 'To remove the filter over on the right-hand side, I can select product and drag that out,', 'start': 655.158, 'duration': 4.84}, {'end': 662.979, 'text': 'and now I see the revenue across all of my products once again.', 'start': 659.998, 'duration': 2.981}, {'end': 667.954, 'text': 'Looking up above here I see the revenue that the customer generated,', 'start': 664.232, 'duration': 3.722}, {'end': 673.677, 'text': 'but I would also like to know the percent of the total revenue that that customer contributed.', 'start': 667.954, 'duration': 5.723}, {'end': 679.459, 'text': "Over on the right-hand side here, I'll select revenue and drag it down to values again.", 'start': 674.637, 'duration': 4.822}, {'end': 683.882, 'text': "So, here I placed in revenue twice and currently it's exactly the same.", 'start': 680.18, 'duration': 3.702}], 'summary': 'Analyzing revenue data to understand customer contribution and product performance.', 'duration': 28.724, 'max_score': 655.158, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4655158.jpg'}, {'end': 723.283, 'src': 'embed', 'start': 696.671, 'weight': 1, 'content': [{'end': 702.135, 'text': 'and here I want to see the percent of the column total, because I want to know the percent revenue contribution.', 'start': 696.671, 'duration': 5.464}, {'end': 709.241, 'text': "I'll select this, and here I can see that customer three they drove $1.4 million of revenue,", 'start': 702.816, 'duration': 6.425}, {'end': 714.405, 'text': "and that's about 30% of the total amount of revenue that we earned here at The Kevin Cookie Company.", 'start': 709.241, 'duration': 5.164}, {'end': 720.602, 'text': "This data is really good, but these headers right up above, they just don't really make much sense.", 'start': 715.219, 'duration': 5.383}, {'end': 723.283, 'text': "Sum of revenue 2, that doesn't really tell me what this is.", 'start': 720.782, 'duration': 2.501}], 'summary': 'Customer 3 drove $1.4 million revenue, contributing to 30% of total revenue at the kevin cookie company.', 'duration': 26.612, 'max_score': 696.671, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4696671.jpg'}, {'end': 817.839, 'src': 'embed', 'start': 792.549, 'weight': 2, 'content': [{'end': 799.011, 'text': 'And one thing you might have noticed, this area down below, this maps to what we see in the pivot table.', 'start': 792.549, 'duration': 6.462}, {'end': 803.372, 'text': 'So here I have customer over here in rows and then we see customer over in rows.', 'start': 799.571, 'duration': 3.801}, {'end': 811.434, 'text': 'Product shows up as the columns and here we see product as the column and then values appear right here and here we see the values in the pivot table.', 'start': 804.072, 'duration': 7.362}, {'end': 817.839, 'text': "It's really easy to manipulate the data that appears in the pivot table and to look at your data in different ways,", 'start': 812.074, 'duration': 5.765}], 'summary': 'Demonstrates mapping data to pivot table for easy manipulation and analysis.', 'duration': 25.29, 'max_score': 792.549, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4792549.jpg'}, {'end': 900.101, 'src': 'heatmap', 'start': 860.464, 'weight': 0.914, 'content': [{'end': 867.689, 'text': 'Up on the top tabs, I can click on insert and here I could choose one of these charts or alternatively, I can press Alt F1 on my keyboard.', 'start': 860.464, 'duration': 7.225}, {'end': 873.373, 'text': "That's the shortcut key to insert a chart and here I see a chart that represents my pivot table.", 'start': 867.809, 'duration': 5.564}, {'end': 876.335, 'text': "Let's say maybe I no longer want to see product.", 'start': 874.214, 'duration': 2.121}, {'end': 882.98, 'text': "Here I could drag that out and you'll see that the chart automatically updated to reflect what's in the pivot table.", 'start': 876.595, 'duration': 6.385}, {'end': 884.921, 'text': 'So, these two are tied together.', 'start': 883.32, 'duration': 1.601}, {'end': 893.586, 'text': "To make it easy to analyze data, up above I'll click on Pivot Chart Analyze and there's an option to insert a slicer.", 'start': 886.162, 'duration': 7.424}, {'end': 894.727, 'text': "I'll select that.", 'start': 893.606, 'duration': 1.121}, {'end': 900.101, 'text': 'And here maybe I want to very quickly evaluate how much did different types of products earn.', 'start': 895.538, 'duration': 4.563}], 'summary': 'Using excel, a pivot chart was inserted and tied to a pivot table, with the ability to insert a slicer for quick data evaluation.', 'duration': 39.637, 'max_score': 860.464, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4860464.jpg'}, {'end': 902.483, 'src': 'embed', 'start': 874.214, 'weight': 3, 'content': [{'end': 876.335, 'text': "Let's say maybe I no longer want to see product.", 'start': 874.214, 'duration': 2.121}, {'end': 882.98, 'text': "Here I could drag that out and you'll see that the chart automatically updated to reflect what's in the pivot table.", 'start': 876.595, 'duration': 6.385}, {'end': 884.921, 'text': 'So, these two are tied together.', 'start': 883.32, 'duration': 1.601}, {'end': 893.586, 'text': "To make it easy to analyze data, up above I'll click on Pivot Chart Analyze and there's an option to insert a slicer.", 'start': 886.162, 'duration': 7.424}, {'end': 894.727, 'text': "I'll select that.", 'start': 893.606, 'duration': 1.121}, {'end': 900.101, 'text': 'And here maybe I want to very quickly evaluate how much did different types of products earn.', 'start': 895.538, 'duration': 4.563}, {'end': 902.483, 'text': "I'll check that box and then click on OK.", 'start': 900.561, 'duration': 1.922}], 'summary': 'Demonstrating how to update a pivot chart by inserting a slicer to quickly evaluate earnings of different product types.', 'duration': 28.269, 'max_score': 874.214, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4874214.jpg'}, {'end': 959.192, 'src': 'embed', 'start': 934.923, 'weight': 4, 'content': [{'end': 940.665, 'text': "I'll click on that, then check date, and then click on OK, and this inserts a timeline.", 'start': 934.923, 'duration': 5.742}, {'end': 950.429, 'text': "Here I can now select a time period and it'll automatically update my table and my pivot chart to reflect this date period that I selected.", 'start': 941.525, 'duration': 8.904}, {'end': 956.911, 'text': "Here I can click on this icon on the timeline and this icon on the slicer and that'll return it to the original state.", 'start': 951.109, 'duration': 5.802}, {'end': 959.192, 'text': 'This analysis is great.', 'start': 957.851, 'duration': 1.341}], 'summary': 'Using the timeline feature, data updates automatically, allowing easy analysis.', 'duration': 24.269, 'max_score': 934.923, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4934923.jpg'}], 'start': 592.036, 'title': 'Pivot table analysis', 'summary': 'Demonstrates using pivot table filtering to view revenue for specific customers and products, including the option to show revenue contribution as a percentage of the total. it also covers manipulating and customizing pivot tables in excel, including changing headers, adding and rearranging fields, changing table design, inserting charts and slicers, and using timelines, to easily analyze and visualize data.', 'chapters': [{'end': 696.671, 'start': 592.036, 'title': 'Pivot table filtering and revenue analysis', 'summary': 'Demonstrates using pivot table filtering to view revenue for specific customers and products, including the option to show revenue contribution as a percentage of the total.', 'duration': 104.635, 'highlights': ["Using pivot table filtering to view revenue for specific customers The speaker demonstrates unchecking 'select all' and selecting specific customers to view their revenue, allowing for targeted analysis.", "Filtering to view revenue for specific products The process of filtering revenue to display data for specific products, such as 'chocolate chip', is showcased, facilitating product-specific revenue analysis.", 'Showing revenue contribution as a percentage of the total The option to display revenue contribution as a percentage of the total revenue generated by a customer is illustrated, providing insights into customer contribution.']}, {'end': 959.192, 'start': 696.671, 'title': 'Pivot table analysis', 'summary': 'Demonstrates how to manipulate and customize a pivot table in excel, including changing headers, adding and rearranging fields, changing table design, inserting charts and slicers, and using timelines, to easily analyze and visualize data.', 'duration': 262.521, 'highlights': ['The chapter shows that customer three drove $1.4 million of revenue, representing 30% of the total revenue earned at The Kevin Cookie Company. Customer three contributed $1.4 million, accounting for 30% of the total revenue.', "The demonstration includes customizing headers in the pivot table to make them more meaningful, such as changing 'Sum of revenue 2' to 'Percent of revenue'. The pivot table headers were customized to make them more meaningful, like changing 'Sum of revenue 2' to 'Percent of revenue'.", 'The process of adding and rearranging fields in the pivot table, including moving the product field to columns and the customer field to rows, is explained. The process of adding and rearranging fields in the pivot table, including moving the product field to columns and the customer field to rows, is explained.', 'The capability of inserting charts and slicers into the pivot table to visually represent the data and easily evaluate different product earnings is demonstrated. The demonstration showcases the capability of inserting charts and slicers into the pivot table to visually represent the data and easily evaluate different product earnings.', 'The chapter also covers the use of timelines to select time periods and automatically update the table and pivot chart to reflect the chosen date range. The chapter also covers the use of timelines to select time periods and automatically update the table and pivot chart to reflect the chosen date range.']}], 'duration': 367.156, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4592036.jpg', 'highlights': ['Using pivot table filtering to view revenue for specific customers and products, including the option to show revenue contribution as a percentage of the total.', 'The chapter shows that customer three drove $1.4 million of revenue, representing 30% of the total revenue earned at The Kevin Cookie Company.', 'The process of adding and rearranging fields in the pivot table, including moving the product field to columns and the customer field to rows, is explained.', 'The capability of inserting charts and slicers into the pivot table to visually represent the data and easily evaluate different product earnings is demonstrated.', 'The chapter also covers the use of timelines to select time periods and automatically update the table and pivot chart to reflect the chosen date range.']}, {'end': 1230.259, 'segs': [{'end': 983.44, 'src': 'embed', 'start': 959.272, 'weight': 0, 'content': [{'end': 966.214, 'text': "I have a view of my customers and the revenue, but I also want to do some additional analysis, but I don't want to get rid of this original view.", 'start': 959.272, 'duration': 6.942}, {'end': 973.896, 'text': "Here I can copy the pivot table and I could scroll down the sheet and paste it and that'll give me another copy of this pivot table.", 'start': 966.914, 'duration': 6.982}, {'end': 977.818, 'text': 'For this new pivot table, I want to see the profit margin.', 'start': 974.397, 'duration': 3.421}, {'end': 983.44, 'text': 'Over on the right-hand side, I already have the revenue and here I can drag in the cost.', 'start': 978.698, 'duration': 4.742}], 'summary': 'Wants to analyze customer data without losing original view. copying pivot table for profit margin analysis.', 'duration': 24.168, 'max_score': 959.272, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4959272.jpg'}, {'end': 1053.247, 'src': 'embed', 'start': 1023.706, 'weight': 1, 'content': [{'end': 1030.512, 'text': 'Right down here, I can type in the formula and the formula for the profit margin is the revenue minus the cost.', 'start': 1023.706, 'duration': 6.806}, {'end': 1035.257, 'text': "I'll click on add and then click on okay and over here in the fields,", 'start': 1031.733, 'duration': 3.524}, {'end': 1041.142, 'text': "you'll see that there's now a new field called profit margin and it automatically adds it to my pivot table.", 'start': 1035.257, 'duration': 5.885}, {'end': 1045.844, 'text': 'Here, I could remove the revenue and now I can see my profit by customer.', 'start': 1041.863, 'duration': 3.981}, {'end': 1053.247, 'text': "I'm now satisfied with all of these different pivot tables, but I want to make sure that when I add new data that these views update.", 'start': 1046.505, 'duration': 6.742}], 'summary': 'Creating profit margin field in pivot table for customer analysis.', 'duration': 29.541, 'max_score': 1023.706, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo41023706.jpg'}, {'end': 1114.368, 'src': 'embed', 'start': 1088.293, 'weight': 3, 'content': [{'end': 1093.898, 'text': "I'd have to go up to change data source and I'd have to select the area of the data that we want to analyze.", 'start': 1088.293, 'duration': 5.605}, {'end': 1101.244, 'text': 'but because we made that a table, all I need to do is add that new data go up to pivot table analyze and then I can refresh the view.', 'start': 1093.898, 'duration': 7.346}, {'end': 1107.426, 'text': "One of the beautiful things about using pivot tables is it doesn't at all affect your original source data.", 'start': 1101.844, 'duration': 5.582}, {'end': 1108.746, 'text': "So, here's an example.", 'start': 1107.746, 'duration': 1}, {'end': 1114.368, 'text': "I can delete this pivot table altogether and here when I go back to my original data, it's untouched.", 'start': 1108.986, 'duration': 5.382}], 'summary': 'Pivot tables allow easy analysis and refreshing of data without affecting the original source, as demonstrated in the example.', 'duration': 26.075, 'max_score': 1088.293, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo41088293.jpg'}, {'end': 1215.43, 'src': 'embed', 'start': 1189.502, 'weight': 4, 'content': [{'end': 1195.867, 'text': 'As long as you have the right question, you can ask your question here and Excel will do all of the heavy lifting for you.', 'start': 1189.502, 'duration': 6.365}, {'end': 1198.729, 'text': "I figured I'd tell you about this one at the very end,", 'start': 1196.567, 'duration': 2.162}, {'end': 1203.533, 'text': 'otherwise you might not have watched the full video of how you can create a pivot table on your own.', 'start': 1198.729, 'duration': 4.804}, {'end': 1209.117, 'text': "All right, well, that's how quick and easy it is to analyze data in Excel using pivot tables.", 'start': 1203.933, 'duration': 5.184}, {'end': 1215.43, 'text': 'To learn even more about Excel, be sure to check out my Excel for Beginners course.', 'start': 1210.287, 'duration': 5.143}], 'summary': 'Excel can analyze data using pivot tables. check out excel for beginners course.', 'duration': 25.928, 'max_score': 1189.502, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo41189502.jpg'}], 'start': 959.272, 'title': 'Excel pivot tables for profit analysis', 'summary': 'Illustrates creating pivot tables to analyze customer profitability, including calculating profit margins and simplifying data analysis in excel, emphasizing efficiency and ease of use.', 'chapters': [{'end': 1045.844, 'start': 959.272, 'title': 'Customer profit analysis with pivot tables', 'summary': 'Illustrates how to create a new pivot table and calculate profit margin using the revenue and cost data, providing a new perspective on customer profitability.', 'duration': 86.572, 'highlights': ['A new pivot table is created to analyze profit margins, allowing for a different perspective on customer profitability and revenue.', 'The process of adding a calculated field for profit margin using the revenue and cost data is demonstrated, providing a quantitative measure of profitability for each customer.', 'The steps for creating a copy of the original pivot table and adding a calculated field for profit margin are explained in detail, enabling the user to perform additional analysis without altering the original view.']}, {'end': 1230.259, 'start': 1046.505, 'title': 'Excel pivot tables: analysis made simple', 'summary': 'Explains how to create and update pivot tables in excel, including the ease of refreshing data, generating pivot tables and charts, and asking questions about data, emphasizing the simplicity and efficiency of the process.', 'duration': 183.754, 'highlights': ['Pivot tables in Excel allow for easy data analysis and updating, as demonstrated by the simple process of adding new data and refreshing the pivot table view, showcasing the efficiency and convenience of the tool.', 'Excel provides the option to generate pivot tables and charts automatically, simplifying the analysis process and eliminating the need for creating pivot tables from scratch, enhancing efficiency and reducing time spent on data analysis.', "The feature of asking questions about the data in Excel further simplifies the analysis process, enabling users to obtain insights without the need to create pivot tables from scratch, exemplifying the user-friendly and efficient nature of Excel's data analysis capabilities.", 'The tutorial emphasizes the ease and efficiency of data analysis in Excel, highlighting the simplicity of creating and updating pivot tables, showcasing the user-friendly and time-saving nature of the process.']}], 'duration': 270.987, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/PdJzy956wo4/pics/PdJzy956wo4959272.jpg', 'highlights': ['A new pivot table is created to analyze profit margins, allowing for a different perspective on customer profitability and revenue.', 'The process of adding a calculated field for profit margin using the revenue and cost data is demonstrated, providing a quantitative measure of profitability for each customer.', 'The steps for creating a copy of the original pivot table and adding a calculated field for profit margin are explained in detail, enabling the user to perform additional analysis without altering the original view.', 'Pivot tables in Excel allow for easy data analysis and updating, as demonstrated by the simple process of adding new data and refreshing the pivot table view, showcasing the efficiency and convenience of the tool.', 'Excel provides the option to generate pivot tables and charts automatically, simplifying the analysis process and eliminating the need for creating pivot tables from scratch, enhancing efficiency and reducing time spent on data analysis.', "The feature of asking questions about the data in Excel further simplifies the analysis process, enabling users to obtain insights without the need to create pivot tables from scratch, exemplifying the user-friendly and efficient nature of Excel's data analysis capabilities.", 'The tutorial emphasizes the ease and efficiency of data analysis in Excel, highlighting the simplicity of creating and updating pivot tables, showcasing the user-friendly and time-saving nature of the process.']}], 'highlights': ['Pivot tables can analyze large amounts of data in seconds without the need for formulas or functions, saving time and effort.', 'Sample workbook is provided in the description for practical application and hands-on learning.', 'Creating pivot tables in Excel to visualize and analyze revenue and order counts, resulting in clear insights for decision-making.', 'Using SUMIFS function to calculate revenue earned from each customer, with customer one generating over half a million dollars.', 'Applying functions to determine the number of orders placed by each customer, such as 92 orders from customer one.', 'The process of dragging and dropping fields into the pivot table to calculate and visualize the sum of revenue by each individual customer and the count of orders, providing an effective representation and analysis of the data.', 'The process of preparing data for a pivot table, including ensuring descriptive column headers, deleting blank rows, and converting data into an official Excel table, is explained to facilitate easy analysis and future data inclusion.', 'Pivot tables are easy to use for analyzing and switching views of data.', 'Demonstrates customizing pivot table views by rearranging and sorting data.', 'Illustrates the customization of pivot table order and appearance.', 'Shows how to filter data within the pivot table.', 'Using pivot table filtering to view revenue for specific customers and products, including the option to show revenue contribution as a percentage of the total.', 'The chapter shows that customer three drove $1.4 million of revenue, representing 30% of the total revenue earned at The Kevin Cookie Company.', 'The process of adding and rearranging fields in the pivot table, including moving the product field to columns and the customer field to rows, is explained.', 'The capability of inserting charts and slicers into the pivot table to visually represent the data and easily evaluate different product earnings is demonstrated.', 'The chapter also covers the use of timelines to select time periods and automatically update the table and pivot chart to reflect the chosen date range.', 'A new pivot table is created to analyze profit margins, allowing for a different perspective on customer profitability and revenue.', 'The process of adding a calculated field for profit margin using the revenue and cost data is demonstrated, providing a quantitative measure of profitability for each customer.', 'The steps for creating a copy of the original pivot table and adding a calculated field for profit margin are explained in detail, enabling the user to perform additional analysis without altering the original view.', 'Pivot tables in Excel allow for easy data analysis and updating, as demonstrated by the simple process of adding new data and refreshing the pivot table view, showcasing the efficiency and convenience of the tool.', 'Excel provides the option to generate pivot tables and charts automatically, simplifying the analysis process and eliminating the need for creating pivot tables from scratch, enhancing efficiency and reducing time spent on data analysis.', "The feature of asking questions about the data in Excel further simplifies the analysis process, enabling users to obtain insights without the need to create pivot tables from scratch, exemplifying the user-friendly and efficient nature of Excel's data analysis capabilities.", 'The tutorial emphasizes the ease and efficiency of data analysis in Excel, highlighting the simplicity of creating and updating pivot tables, showcasing the user-friendly and time-saving nature of the process.']}