title
Beginner to Pro FREE Excel Data Analysis Course

description
You asked for it. Here is my free course to help you kick start your data analytics journey. In this comprehensive video, learn: 1) How to approach a data analysis project 2) A real-world example data with 10 problems 3) Step by step walk thru 4) How to calculate descriptive statistics (mean, median, quartiles, range, distinct items & count) 5) Exploratory data analysis in Excel 6) Analysis with formulas vs. pivots (necessary techniques) 7) Top / bottom performing items 8) Anomaly detection 9) Best in class analysis 10) Complete report preparation 11) Combining data in different tables (spreadsheets) 12) Answering open ended questions 13) Preparing and using Excel charts 14) Challenges & homework assignment You will also learn below Excel features: 1) Using Tables 2) Formulas 3) Pivot Tables & Power Pivot measures 4) Conditional formatting 5) Charts 6) Data Validation 7) Keyboard Shortcuts & tricks ⏱ Video Timestamps: ================== 0:00 - Introduction 2:12 - Descriptive statistics in Excel 8:05 - Exploratory Data Analysis (EDA) with conditional formatting 13:20 - Sales by Country report with formulas 19:01 - Sales by Country report with Pivots 23:43 - Top 5 products with $ per unit 27:13 - Anomaly detection in your data 31:24 - Best in category analysis 33:36 - Profit analysis (combining two tables) 37:26 - Dynamic country level sales report 41:55 - Which products to discontinue (Open ended questions) 💥📁 Sample files: ================ Blank data file (perfect for following along) - https://chandoo.org/wp/wp-content/uploads/2021/08/beginner-DA-course-blank.xlsx Completed workbook - https://chandoo.org/wp/wp-content/uploads/2021/08/beginner-DA-course.xlsx ⛔⚠ Got an ERROR - Can't Change Array? ================================== Here is the fix - https://chandoo.org/wp/errors-with-data-analysis-course/ What to watch next? ================= I recommend setting aside sometime to practice what you just learned. But if you are in the mood to watch more, I suggest these videos: 5 key skills you need to be a GREAT data analyst - https://youtu.be/gVr9f1GJdZc 5 Excel Skills you need to focus on - https://youtu.be/cEuq_9CsHIY 15 Excel functions you should know - https://youtu.be/B5hayFelHDU My playlist on data analysis - https://www.youtube.com/watch?v=gVr9f1GJdZc&list=PLmejDGrsgFyDAWOAnEiK0P787q3grsk9R Want to learn more? Join my Excel School 👉 ===================================== If you want more step by step education & resources to be AWESOME at your work, consider joining my Excel School program. This online class will teach you everything you need for data analysis + reporting roles. Visit https://chandoo.org/wp/excel-school-program/ to sign up today. My Recommended Excel Books 📚👌: =============================== https://chandoo.org/wp/best-excel-power-bi-books/ 😎 SAY HELLO 👋 =============== Apart from YouTube, I frequently post on, my blog - https://chandoo.org/wp/ twitter - https://twitter.com/r1c1/ Instagram - https://www.instagram.com/chandoo.xlsx Have a beautiful day 🌼🌞😀 #DataAnalysis #Excel

detail
{'title': 'Beginner to Pro FREE Excel Data Analysis Course', 'heatmap': [{'end': 919.776, 'start': 881.871, 'weight': 0.711}, {'end': 2101.976, 'start': 2066.527, 'weight': 1}, {'end': 2397.025, 'start': 2363.093, 'weight': 0.815}], 'summary': 'An excel data analysis course covers 10 ways to analyze data, including descriptive statistics and visualization techniques. it explores sales analysis, profit analysis, and product discontinuation using quantifiable examples and specific excel techniques, such as pivot tables and conditional formatting.', 'chapters': [{'end': 319.923, 'segs': [{'end': 50.158, 'src': 'embed', 'start': 0.612, 'weight': 0, 'content': [{'end': 3.666, 'text': 'Welcome to Excel Data Analysis course.', 'start': 0.612, 'duration': 3.054}, {'end': 22.793, 'text': 'This is a completely self-contained data analysis course for people who are just getting started into data analytics with Excel or people who have been already doing a data analyst role for a while and just want to understand in a more structured manner from an experienced person.', 'start': 4.56, 'duration': 18.233}, {'end': 31.38, 'text': "In this video, I'm going to show you a sample data and then ten ways to analyze the data using Excel.", 'start': 23.094, 'duration': 8.286}, {'end': 35.103, 'text': 'We are going to look at seven key different techniques.', 'start': 31.9, 'duration': 3.203}, {'end': 44.032, 'text': "We'll talk about descriptive statistics, and then we will look at how to use Excel to do exploratory data analysis.", 'start': 35.704, 'duration': 8.328}, {'end': 50.158, 'text': 'And then we are also going to look at formulas as well as pivot tables to analyze the data.', 'start': 44.272, 'duration': 5.886}], 'summary': 'Excel data analysis course covers 10 ways to analyze data, including 7 key techniques using excel.', 'duration': 49.546, 'max_score': 0.612, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E612.jpg'}, {'end': 142.885, 'src': 'embed', 'start': 96.647, 'weight': 2, 'content': [{'end': 106.794, 'text': 'I suggest you to download the blank data file and then just use it to go through the material and then try to replicate the steps on the blank file.', 'start': 96.647, 'duration': 10.147}, {'end': 117.162, 'text': 'But there is also a completed workbook so that you can actually use it as inspiration or take that if you are feeling stuck or if you want just a hand somewhere.', 'start': 107.215, 'duration': 9.947}, {'end': 128.471, 'text': 'Finally, I highly recommend that you set aside time and watch this whole video entirely and practice the concepts if you are really serious about your data analyst skills.', 'start': 118.038, 'duration': 10.433}, {'end': 129.453, 'text': "Let's jump in.", 'start': 128.792, 'duration': 0.661}, {'end': 135.601, 'text': "We'll start our analysis with preparing some quick statistics on the sample data set here.", 'start': 129.592, 'duration': 6.009}, {'end': 142.885, 'text': 'now a good practice when you are doing this in excel data is to turn your raw data into a table format.', 'start': 136.121, 'duration': 6.764}], 'summary': 'Download the blank data file, use it to replicate steps, watch video for serious data analyst skills.', 'duration': 46.238, 'max_score': 96.647, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E96647.jpg'}, {'end': 277.63, 'src': 'embed', 'start': 249.061, 'weight': 5, 'content': [{'end': 253.043, 'text': "But it's a good idea to calculate both to understand what's happening in the data.", 'start': 249.061, 'duration': 3.982}, {'end': 255.765, 'text': 'Median refers to the midpoint of the amount.', 'start': 253.264, 'duration': 2.501}, {'end': 265.535, 'text': 'So, if all the amounts are arranged in ascending order, the middle value will be 3437, whereas average is nothing but sum of all the values,', 'start': 255.785, 'duration': 9.75}, {'end': 267.858, 'text': 'divided by count of the values.', 'start': 265.535, 'duration': 2.323}, {'end': 272.403, 'text': 'as you could see from here, our average is slightly higher than the median.', 'start': 267.858, 'duration': 4.545}, {'end': 277.63, 'text': 'that means there is probably more higher amounts in the data than there are lower amounts.', 'start': 272.403, 'duration': 5.227}], 'summary': 'Calculating median and average reveals more higher amounts in the data.', 'duration': 28.569, 'max_score': 249.061, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E249061.jpg'}], 'start': 0.612, 'title': 'Excel data analysis course', 'summary': 'Introduces a self-contained data analysis course covering ten ways to analyze data using excel, including descriptive statistics, exploratory data analysis, and visualization techniques. it also emphasizes the importance of practicing data analysis skills to improve proficiency.', 'chapters': [{'end': 117.162, 'start': 0.612, 'title': 'Excel data analysis course', 'summary': 'Introduces a comprehensive self-contained data analysis course for beginners and experienced individuals, covering ten ways to analyze data using excel, including descriptive statistics, exploratory data analysis, formulas, pivot tables, data consolidation, answering open-ended questions, and presenting analysis with various charting and visualization techniques.', 'duration': 116.55, 'highlights': ['The chapter covers ten ways to analyze data using Excel, including descriptive statistics, exploratory data analysis, formulas, pivot tables, data consolidation, answering open-ended questions, and presenting analysis with various charting and visualization techniques.', 'The course provides two workbooks, one blank for practice and one completed for reference, to help learners apply the techniques learned in the video.', 'The video is aimed at people new to data analytics with Excel as well as those already in a data analyst role, offering a structured understanding of data analysis from an experienced perspective.']}, {'end': 319.923, 'start': 118.038, 'title': 'Data analysis statistics practice', 'summary': 'Emphasizes the importance of practicing data analysis skills, including calculating average, median, minimum, and maximum values, and using table formats in excel, aiming to improve data analysis proficiency.', 'duration': 201.885, 'highlights': ['The chapter emphasizes the importance of practicing data analysis skills. It recommends setting aside time to watch the whole video and practice the concepts for serious improvement in data analyst skills.', 'Calculating average, median, minimum, and maximum values is crucial for understanding the data. It demonstrates the calculation of average and median amounts and units, highlighting the significance of understanding both average and median values in data analysis.', 'Using table formats in Excel is a good practice for data analysis. It explains the process of turning raw data into a table format, enabling easy reference and analysis of data in Excel.']}], 'duration': 319.311, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E612.jpg', 'highlights': ['The chapter covers ten ways to analyze data using Excel, including descriptive statistics, exploratory data analysis, formulas, pivot tables, data consolidation, answering open-ended questions, and presenting analysis with various charting and visualization techniques.', 'The video is aimed at people new to data analytics with Excel as well as those already in a data analyst role, offering a structured understanding of data analysis from an experienced perspective.', 'The course provides two workbooks, one blank for practice and one completed for reference, to help learners apply the techniques learned in the video.', 'The chapter emphasizes the importance of practicing data analysis skills. It recommends setting aside time to watch the whole video and practice the concepts for serious improvement in data analyst skills.', 'Using table formats in Excel is a good practice for data analysis. It explains the process of turning raw data into a table format, enabling easy reference and analysis of data in Excel.', 'Calculating average, median, minimum, and maximum values is crucial for understanding the data. It demonstrates the calculation of average and median amounts and units, highlighting the significance of understanding both average and median values in data analysis.']}, {'end': 750.044, 'segs': [{'end': 367.751, 'src': 'embed', 'start': 342.917, 'weight': 5, 'content': [{'end': 350.721, 'text': 'just as median refers to the middle point of the data, quartile would refer to the 1, 4th or the 3, 4th points of the data.', 'start': 342.917, 'duration': 7.804}, {'end': 355.443, 'text': 'so first quartile would be you can use the percentile formula.', 'start': 350.721, 'duration': 4.722}, {'end': 360.686, 'text': "so we'll say data amount and then the k will be 0.25.", 'start': 355.443, 'duration': 5.243}, {'end': 367.751, 'text': 'what this number suggests is first, one fourth of the values are less than or equal to 1652.', 'start': 360.686, 'duration': 7.065}], 'summary': 'First quartile: 25% of values ≤ 1652', 'duration': 24.834, 'max_score': 342.917, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E342917.jpg'}, {'end': 442.556, 'src': 'embed', 'start': 411.875, 'weight': 1, 'content': [{'end': 416.178, 'text': 'But another quick way of doing this is you can apply filters on your original table.', 'start': 411.875, 'duration': 4.303}, {'end': 423.323, 'text': 'A quick shortcut to enable the filters on your data is select any cell in the table and then press control shift L.', 'start': 416.578, 'duration': 6.745}, {'end': 427.666, 'text': 'If you press it once, the filters will be added and then if you press it again, they will go off.', 'start': 423.323, 'duration': 4.343}, {'end': 435.032, 'text': 'So once you have such filters, you can just click on that little arrow to quickly scan and see how many items are there.', 'start': 427.947, 'duration': 7.085}, {'end': 442.556, 'text': 'We seem to be having about 10 salespeople, five or six different geographies, but we have a whole heap of different products.', 'start': 435.612, 'duration': 6.944}], 'summary': 'Quickly apply filters to analyze data: 10 salespeople, 5-6 geographies, numerous products.', 'duration': 30.681, 'max_score': 411.875, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E411875.jpg'}, {'end': 480.704, 'src': 'embed', 'start': 452.541, 'weight': 0, 'content': [{'end': 455.322, 'text': 'This will only work in Excel 365.', 'start': 452.541, 'duration': 2.781}, {'end': 457.924, 'text': 'then you say unique of data product.', 'start': 455.322, 'duration': 2.602}, {'end': 462.748, 'text': 'what unique will do is it will give you a listing of all the products, one per row.', 'start': 457.924, 'duration': 4.824}, {'end': 469.654, 'text': "so if there is repetitions, it will remove them automatically and it will give you this list, since we don't want to see the names,", 'start': 462.748, 'duration': 6.906}, {'end': 471.196, 'text': 'we just want to count them.', 'start': 469.654, 'duration': 1.542}, {'end': 475.319, 'text': 'you can then take that and press, pass it to the count, a function.', 'start': 471.196, 'duration': 4.123}, {'end': 480.704, 'text': 'this will count how many products are there, and then you can see that there are 22 different products that we are selling.', 'start': 475.319, 'duration': 5.385}], 'summary': "In excel 365, using the 'unique' function gave a list of 22 different products.", 'duration': 28.163, 'max_score': 452.541, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E452541.jpg'}, {'end': 654.625, 'src': 'embed', 'start': 605.454, 'weight': 2, 'content': [{'end': 611.756, 'text': 'Another way to analyze the data when you have applied these kind of color scales is you can enable the filters.', 'start': 605.454, 'duration': 6.302}, {'end': 614.037, 'text': 'Again, the shortcut is control shift L.', 'start': 612.056, 'duration': 1.981}, {'end': 622.442, 'text': "And from there you can just sort it by largest to smallest to kind of get a feel of how high the numbers are, what's happening.", 'start': 614.737, 'duration': 7.705}, {'end': 631.298, 'text': 'So there is a couple of numbers in the 16 and 15, 000 row values, but it quickly falls down to 10, 000 and then 9,', 'start': 622.482, 'duration': 8.816}, {'end': 640.895, 'text': '000 and it seems to be settling in that ballpark of between 6 and 5, 000 before things kind of turn a little bad and go down all the way to zero.', 'start': 631.298, 'duration': 9.597}, {'end': 645.598, 'text': 'So this will also give you a solid indication of how the values are spread.', 'start': 641.375, 'duration': 4.223}, {'end': 648.18, 'text': 'You can do the same for units.', 'start': 646.419, 'duration': 1.761}, {'end': 650.142, 'text': "For units, we're going to try something else.", 'start': 648.26, 'duration': 1.882}, {'end': 652.684, 'text': 'Select the unit column, conditional formatting.', 'start': 650.402, 'duration': 2.282}, {'end': 654.625, 'text': "This time, let's apply data bars.", 'start': 652.744, 'duration': 1.881}], 'summary': 'Data analysis reveals values ranging from 16,000 to zero with most falling between 6,000 and 5,000.', 'duration': 49.171, 'max_score': 605.454, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E605454.jpg'}, {'end': 764.425, 'src': 'embed', 'start': 733.335, 'weight': 4, 'content': [{'end': 737.197, 'text': 'And you can see that the name Gigi appeared three times.', 'start': 733.335, 'duration': 3.862}, {'end': 743.921, 'text': "So she's probably one of our top salesperson and that's why her name appeared thrice in that list.", 'start': 738.158, 'duration': 5.763}, {'end': 750.044, 'text': 'If you spot any interesting trends within the product names, you can also get a feel like Mint Chip Choco appeared twice.', 'start': 744.461, 'duration': 5.583}, {'end': 757.557, 'text': 'So it seems to be one of our stronger products again just based on the intuition that we are developing at this point in time.', 'start': 750.685, 'duration': 6.872}, {'end': 764.425, 'text': 'Another way to do exploratory data analysis is to see if there is any sort of numbers that are repeating.', 'start': 758.74, 'duration': 5.685}], 'summary': 'Gigi appeared three times, indicating top salesperson. mint chip choco appeared twice, suggesting strong product. also exploring repeating numbers.', 'duration': 31.09, 'max_score': 733.335, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E733335.jpg'}], 'start': 319.923, 'title': 'Excel data analysis techniques', 'summary': 'Covers statistical analysis in excel for metrics like range and quartiles, and explores exploratory data analysis using conditional formatting. it also demonstrates techniques to visualize data distribution, identify above-average values, and analyze top salesperson performance with quantifiable examples.', 'chapters': [{'end': 511.898, 'start': 319.923, 'title': 'Quick statistical analysis in excel', 'summary': 'Explains how to calculate metrics like range, quartiles, and distinct counts using excel formulas, including examples with quantifiable data, and also explores exploratory data analysis with conditional formatting.', 'duration': 191.975, 'highlights': ['You can calculate the quartiles using the percentile formula, for example, the first quartile suggests that one fourth of the values are less than or equal to 1652. Calculating quartiles using the percentile formula, e.g., the first quartile indicating one fourth of the values being less than or equal to 1652.', 'You can use the unique function in Excel 365 to count the number of different products, resulting in 22 different products being identified. Utilizing the unique function in Excel 365 to determine the count of distinct products, revealing a total of 22 different products.', 'Enabling filters on the original table using the shortcut control shift L allows for quick scanning and identification of the number of items, such as salespeople, geographies, and products. Enabling filters on the original table using the shortcut control shift L to efficiently identify the count of items, like salespeople, geographies, and products.']}, {'end': 750.044, 'start': 512.357, 'title': 'Excel data analysis techniques', 'summary': 'Demonstrates how to use conditional formatting to visualize data distribution and identify above-average values in an excel worksheet, providing insights on the varied amount column and identifying top 10 items, with gigi appearing three times as the top salesperson.', 'duration': 237.687, 'highlights': ['Using conditional formatting to apply color scales for instant visualization of varied values in the amount column. The speaker demonstrates applying a color scale using conditional formatting to visualize the distribution of values in the amount column.', 'Utilizing the filter option to sort data by largest to smallest, providing insights into the data distribution and identifying trends. The speaker explains how to use the filter option to sort data by largest to smallest, gaining insights into the data distribution and identifying trends.', 'Applying conditional formatting to highlight above-average values in the amount column and identifying the top 10 items, revealing Gigi as a top salesperson with three appearances. The speaker showcases the process of highlighting above-average values and identifying the top 10 items in the amount column, leading to the discovery of Gigi as a top salesperson with three appearances.']}], 'duration': 430.121, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E319923.jpg', 'highlights': ['Utilizing the unique function in Excel 365 to determine the count of distinct products, revealing a total of 22 different products.', 'Enabling filters on the original table using the shortcut control shift L to efficiently identify the count of items, like salespeople, geographies, and products.', 'The speaker demonstrates applying a color scale using conditional formatting to visualize the distribution of values in the amount column.', 'The speaker explains how to use the filter option to sort data by largest to smallest, gaining insights into the data distribution and identifying trends.', 'The speaker showcases the process of highlighting above-average values and identifying the top 10 items in the amount column, leading to the discovery of Gigi as a top salesperson with three appearances.', 'You can calculate the quartiles using the percentile formula, for example, the first quartile suggests that one fourth of the values are less than or equal to 1652.']}, {'end': 1022.195, 'segs': [{'end': 813.599, 'src': 'embed', 'start': 750.685, 'weight': 0, 'content': [{'end': 757.557, 'text': 'So it seems to be one of our stronger products again just based on the intuition that we are developing at this point in time.', 'start': 750.685, 'duration': 6.872}, {'end': 764.425, 'text': 'Another way to do exploratory data analysis is to see if there is any sort of numbers that are repeating.', 'start': 758.74, 'duration': 5.685}, {'end': 771.651, 'text': 'So, for example, are there any situations where the same number of units are purchased by two different instances?', 'start': 764.445, 'duration': 7.206}, {'end': 781.239, 'text': 'So you can select the unit column, go to highlight and then duplicate values and it will highlight all the duplicate values in color.', 'start': 771.751, 'duration': 9.488}, {'end': 785.723, 'text': 'So I can see 459 is duplicated, 366, 348.', 'start': 781.259, 'duration': 4.464}, {'end': 795.097, 'text': "And you know you'll get for example 306 is fairly popular 303 and on all of that and that will give you a different feel for your data.", 'start': 785.723, 'duration': 9.374}, {'end': 800.665, 'text': 'So that is how you can use conditional formatting to do some exploratory data analysis.', 'start': 795.798, 'duration': 4.867}, {'end': 802.006, 'text': 'our third example.', 'start': 801.005, 'duration': 1.001}, {'end': 804.489, 'text': "let's analyze the sales by country.", 'start': 802.006, 'duration': 2.483}, {'end': 807.112, 'text': 'so what we want to do is we want to list all our countries.', 'start': 804.489, 'duration': 2.623}, {'end': 813.599, 'text': 'we just want to see how much is the total amount, how much is the total units, and get a feel of that information.', 'start': 807.112, 'duration': 6.487}], 'summary': 'The product is performing well, with repeated units and conditional formatting used for exploratory data analysis, and analysis of sales by country for total amount and units.', 'duration': 62.914, 'max_score': 750.685, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E750685.jpg'}, {'end': 919.776, 'src': 'heatmap', 'start': 873.943, 'weight': 1, 'content': [{'end': 879.889, 'text': 'So the formula that you need to learn is, this is one of the most important formulas when it comes to data analysis.', 'start': 873.943, 'duration': 5.946}, {'end': 881.491, 'text': 'It is the SUMIFS formula.', 'start': 880.029, 'duration': 1.462}, {'end': 887.417, 'text': 'So SUMIFS, what do you want to sum up amount columns? So that is my data table amount column.', 'start': 881.871, 'duration': 5.546}, {'end': 892.919, 'text': 'and then my data geography should match new zealand.', 'start': 888.098, 'duration': 4.821}, {'end': 899.661, 'text': "so we'll say data geography, point to c6 and when you close the bracket it will get you that number.", 'start': 892.919, 'duration': 6.742}, {'end': 901.581, 'text': "let's quickly apply some currency formatting.", 'start': 899.661, 'duration': 1.92}, {'end': 904.202, 'text': 'you can go to home, click on currency.', 'start': 901.581, 'duration': 2.621}, {'end': 910.303, 'text': 'alternatively, you can also press ctrl shift 4 to apply the currency formatting with two decimal places.', 'start': 904.202, 'duration': 6.101}, {'end': 915.414, 'text': "When you're doing analysis at this level, where the values are in hundreds and thousands,", 'start': 911.072, 'duration': 4.342}, {'end': 919.776, 'text': "you don't need to go and show the decimal points unless somebody really asks for them.", 'start': 915.414, 'duration': 4.362}], 'summary': 'The sumifs formula is crucial for data analysis, such as summing up amount columns and applying currency formatting with two decimal places.', 'duration': 25.718, 'max_score': 873.943, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E873943.jpg'}, {'end': 1013.303, 'src': 'embed', 'start': 982.481, 'weight': 3, 'content': [{'end': 988.324, 'text': "i'm going to select the headers, make them bold and add a little bit of background color on that.", 'start': 982.481, 'duration': 5.843}, {'end': 994.267, 'text': "select these two columns right, align them, because that's the numbers and that's how these are aligned.", 'start': 988.324, 'duration': 5.943}, {'end': 998.909, 'text': 'then, most importantly, apply a sort order that is logical.', 'start': 994.267, 'duration': 4.642}, {'end': 1006.295, 'text': 'so you could either sort them in alphabetical order or, because we are talking about sales by country, Take the amount column and sort this.', 'start': 998.909, 'duration': 7.386}, {'end': 1013.303, 'text': 'So quickly add filters largest to smallest and then disable the filters.', 'start': 1006.975, 'duration': 6.328}], 'summary': 'Format headers, align numbers, and sort sales data by amount column for logical order.', 'duration': 30.822, 'max_score': 982.481, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E982481.jpg'}], 'start': 750.685, 'title': 'Data analysis techniques and conditional formatting for sales analysis', 'summary': 'Covers exploratory data analysis techniques such as identifying repeating numbers and duplicate value highlighting, with specific examples and quantifiable data. it also demonstrates using conditional formatting for sales analysis by country, applying sumifs formula, and enhancing the analysis with formatting and sorting.', 'chapters': [{'end': 795.097, 'start': 750.685, 'title': 'Exploratory data analysis techniques', 'summary': 'Introduces exploratory data analysis techniques, including identifying repeating numbers and using duplicate value highlighting, demonstrating the process with specific examples and quantifiable data such as the number of duplicate values.', 'duration': 44.412, 'highlights': ['The process of exploratory data analysis involves identifying repeating numbers and using duplicate value highlighting.', 'Using the duplicate value highlighting technique, specific numbers such as 459, 366, and 348 were identified as duplicated, providing quantifiable data for analysis.', 'Exploring popular numbers like 306 and 303 offers a different perspective on the data, demonstrating the practical application of the technique.']}, {'end': 1022.195, 'start': 795.798, 'title': 'Conditional formatting for sales analysis', 'summary': 'Demonstrates how to analyze sales by country using conditional formatting to find the total amount and units, applying sumifs formula to calculate total amount by each country, and making the analysis presentable with formatting and sorting.', 'duration': 226.397, 'highlights': ['Applying SUMIFS formula to calculate total amount by each country The chapter explains the use of the SUMIFS formula to find the total amount by each country, providing a crucial formula for data analysis.', 'Making the analysis presentable with formatting and sorting The chapter details the process of making the analysis presentable by adding bold headers, background color, and logical sorting to enhance the visual representation of the data.', 'Using conditional formatting to find the total amount and units by country The chapter illustrates the use of conditional formatting to analyze sales by country, aiming to find the total amount and units for each country, enhancing the exploratory data analysis process.']}], 'duration': 271.51, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E750685.jpg', 'highlights': ['Using the duplicate value highlighting technique, specific numbers such as 459, 366, and 348 were identified as duplicated, providing quantifiable data for analysis.', 'Applying SUMIFS formula to calculate total amount by each country The chapter explains the use of the SUMIFS formula to find the total amount by each country, providing a crucial formula for data analysis.', 'Using conditional formatting to find the total amount and units by country The chapter illustrates the use of conditional formatting to analyze sales by country, aiming to find the total amount and units for each country, enhancing the exploratory data analysis process.', 'Making the analysis presentable with formatting and sorting The chapter details the process of making the analysis presentable by adding bold headers, background color, and logical sorting to enhance the visual representation of the data.', 'Exploring popular numbers like 306 and 303 offers a different perspective on the data, demonstrating the practical application of the technique.', 'The process of exploratory data analysis involves identifying repeating numbers and using duplicate value highlighting.']}, {'end': 2015.977, 'segs': [{'end': 1051.059, 'src': 'embed', 'start': 1022.595, 'weight': 6, 'content': [{'end': 1027.739, 'text': 'the next thing that you want to do is select all of this and add some simple borders around it.', 'start': 1022.595, 'duration': 5.144}, {'end': 1035.804, 'text': 'go to border and just set the color to a dull color and only add horizontal borders.', 'start': 1027.739, 'duration': 8.065}, {'end': 1040.127, 'text': "now the border color kind of matches the grid line, so we can't really see what is there.", 'start': 1035.804, 'duration': 4.323}, {'end': 1044.43, 'text': "but if i go to view and disable the grid lines, i'll get this nice little thing.", 'start': 1040.127, 'duration': 4.303}, {'end': 1051.059, 'text': 'The next thing that you may want to consider is adding some additional detail to this so that it looks visually interesting.', 'start': 1044.915, 'duration': 6.144}], 'summary': 'Add simple horizontal borders with dull color, disable grid lines, and consider adding visual detail.', 'duration': 28.464, 'max_score': 1022.595, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1022595.jpg'}, {'end': 1165.735, 'src': 'embed', 'start': 1119.568, 'weight': 3, 'content': [{'end': 1127.592, 'text': 'but select the units and just make them a different color so that they kind of blend into the background,', 'start': 1119.568, 'duration': 8.024}, {'end': 1133.955, 'text': 'so that not naturally we read these two and if needed, we will read that additional information.', 'start': 1127.592, 'duration': 6.363}, {'end': 1141.299, 'text': 'so now, if you compare this versus that, you will immediately notice how pleasant and informative this left hand side is.', 'start': 1133.955, 'duration': 7.344}, {'end': 1147.908, 'text': "in this example, i'm going to show you how to do sales by country using pivot tables.", 'start': 1141.986, 'duration': 5.922}, {'end': 1156.711, 'text': 'just to do a quick recap in the previous example we did the same with formulas, so our goal is to replicate most of this by using pivot tables.', 'start': 1147.908, 'duration': 8.803}, {'end': 1163.394, 'text': 'we go to this page, select a blank cell, go to insert pivot table And it will ask you where is your data?', 'start': 1156.711, 'duration': 6.683}, {'end': 1165.735, 'text': 'Our data is in the table data.', 'start': 1164.134, 'duration': 1.601}], 'summary': 'Using pivot tables for sales by country, replicating previous example with formulas.', 'duration': 46.167, 'max_score': 1119.568, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1119568.jpg'}, {'end': 1274.492, 'src': 'embed', 'start': 1248.652, 'weight': 4, 'content': [{'end': 1257.777, 'text': 'You can adjust the style and color by going to the design ribbon when you have selected the pivot table and you can, for example, add banded rows.', 'start': 1248.652, 'duration': 9.125}, {'end': 1264.24, 'text': 'You can change the color scheme and make it like a really strong color if you prefer.', 'start': 1257.937, 'duration': 6.303}, {'end': 1274.492, 'text': "How do we add the conditional formatting data bars here? Because we did this by having a separate column here, I'll try to replicate the same.", 'start': 1264.86, 'duration': 9.632}], 'summary': 'Adjust pivot table style and color, add banded rows, and replicate conditional formatting.', 'duration': 25.84, 'max_score': 1248.652, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1248652.jpg'}, {'end': 1398.007, 'src': 'embed', 'start': 1372.915, 'weight': 0, 'content': [{'end': 1381.517, 'text': 'I can kind of add some extra layer of filtering on top so that I can look at this report for an individual salesperson or individual product.', 'start': 1372.915, 'duration': 8.602}, {'end': 1386.878, 'text': "So for example, I'll right click on my salesperson field, add it as a slicer.", 'start': 1382.057, 'duration': 4.821}, {'end': 1390.519, 'text': "We'll get all our salespersons here as a box.", 'start': 1387.618, 'duration': 2.901}, {'end': 1398.007, 'text': "I'm just going to make this box nice and wide and go to the slicer options and set this to two columns.", 'start': 1391.559, 'duration': 6.448}], 'summary': 'Enhancing reporting with individual salesperson or product filtering, using slicers and columns.', 'duration': 25.092, 'max_score': 1372.915, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1372915.jpg'}, {'end': 1517.805, 'src': 'embed', 'start': 1492.492, 'weight': 1, 'content': [{'end': 1500.975, 'text': "The pivot table that you create is essentially similar to the pivots that we created in the earlier examples, but here there's a bit more convenience.", 'start': 1492.492, 'duration': 8.483}, {'end': 1504.176, 'text': "So now I'll take my product put here.", 'start': 1501.615, 'duration': 2.561}, {'end': 1507.377, 'text': "Let's add amount and units.", 'start': 1504.556, 'duration': 2.821}, {'end': 1509.959, 'text': 'So we get total amount and total units.', 'start': 1507.857, 'duration': 2.102}, {'end': 1515.043, 'text': "So for each of these products, that's the total amount and that's the how many units there is.", 'start': 1510.399, 'duration': 4.644}, {'end': 1517.805, 'text': 'Now we need to calculate a dollar per unit.', 'start': 1515.563, 'duration': 2.242}], 'summary': 'Creating a pivot table to calculate total amount and units for products, and then determining dollar per unit.', 'duration': 25.313, 'max_score': 1492.492, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1492492.jpg'}, {'end': 1874.022, 'src': 'embed', 'start': 1852.028, 'weight': 2, 'content': [{'end': 1860.453, 'text': 'and also the number of dots on the top indicate like how many values are really out there, and that will give you a different picture.', 'start': 1852.028, 'duration': 8.425}, {'end': 1866.878, 'text': 'So using these two graphs, this box plot and the XY scatter plot,', 'start': 1860.493, 'duration': 6.385}, {'end': 1874.022, 'text': "we are able to visually spot anomalies in the data and ask some more interesting questions, like you know what's going on with the USA,", 'start': 1866.878, 'duration': 7.144}], 'summary': "Box plot and xy scatter plot visually spot anomalies in the data, enabling analysis of usa's data.", 'duration': 21.994, 'max_score': 1852.028, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1852028.jpg'}, {'end': 1996.936, 'src': 'embed', 'start': 1970.486, 'weight': 5, 'content': [{'end': 1975.327, 'text': 'this will kind of change the pivot table to show these amounts alone.', 'start': 1970.486, 'duration': 4.841}, {'end': 1977.868, 'text': 'and there you go best salesperson by the country.', 'start': 1975.327, 'duration': 2.541}, {'end': 1979.568, 'text': 'how would we do the same for?', 'start': 1977.868, 'duration': 1.7}, {'end': 1982.269, 'text': 'but look at the lowest performing salesperson?', 'start': 1979.568, 'duration': 2.701}, {'end': 1984.466, 'text': 'All we have to do is make a copy.', 'start': 1982.665, 'duration': 1.801}, {'end': 1989.23, 'text': 'go back to the top 10 filter where it says top one.', 'start': 1984.466, 'duration': 4.764}, {'end': 1994.054, 'text': "change this to bottom one and we will get the corresponding person's name as well.", 'start': 1989.23, 'duration': 4.824}, {'end': 1996.936, 'text': 'Now here is a fun challenge for you.', 'start': 1994.574, 'duration': 2.362}], 'summary': 'Pivot table changes show best and lowest salespersons, with instructions for filtering.', 'duration': 26.45, 'max_score': 1970.486, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1970486.jpg'}], 'start': 1022.595, 'title': 'Excel data visualization techniques, sales analysis by country using pivot tables, and pivot tables for data analysis', 'summary': "Covers excel data visualization techniques including borders, conditional formatting, and color techniques, creating sales analysis reports by country using pivot tables with additional filtering, and utilizing pivot tables for data analysis, calculating dollar per unit, identifying anomalies using xy scatter plot and box plot, and evaluating salespersons' performance by country.", 'chapters': [{'end': 1141.299, 'start': 1022.595, 'title': 'Excel data visualization techniques', 'summary': 'Discusses using borders, conditional formatting, and color techniques to enhance data visualization in excel, creating visually appealing and informative reports.', 'duration': 118.704, 'highlights': ['Using borders with dull color and applying horizontal borders to visually separate data, enhancing the readability and visual appeal of the report.', "Utilizing conditional formatting and data bars to represent values visually, providing a visual indication of the data and enhancing the report's informativeness.", 'Applying color techniques to differentiate units from the main focus, improving the readability and emphasis on key information within the report.']}, {'end': 1447.928, 'start': 1141.986, 'title': 'Sales analysis by country using pivot tables', 'summary': 'Demonstrates how to create a sales analysis report by country using pivot tables, including formatting, sorting, and adding conditional formatting, as well as the advantage of using pivot tables for additional filtering.', 'duration': 305.942, 'highlights': ['Creating a sales analysis report by country using pivot tables The chapter demonstrates the process of creating a sales analysis report by country using pivot tables, including organizing the data by country, amount, and units, and applying formatting and sorting options for better visualization.', 'Adding conditional formatting and data bars to the pivot table The chapter explains the process of adding conditional formatting and data bars to the pivot table to visually represent the data, including steps for adding and customizing the data bars for better readability.', 'Utilizing pivot tables for additional filtering and analysis The chapter highlights the advantage of using pivot tables for additional filtering and analysis, allowing users to focus on individual salespersons or products, and demonstrates the use of slicers for this purpose.']}, {'end': 2015.977, 'start': 1448.488, 'title': 'Using pivot tables for data analysis', 'summary': 'Demonstrates how to use pivot tables in excel to calculate dollar per unit, identify anomalies in data using xy scatter plot and box plot, and determine best and worst performing salespersons by country using pivot tables, offering a logical and simpler way for data analysis.', 'duration': 567.489, 'highlights': ['The chapter demonstrates how to use pivot tables to calculate dollar per unit for each product, showing the total amount and units for each product and then calculating a composite number called sales per unit. Use of pivot tables to calculate dollar per unit, showing total amount and units for each product, calculating sales per unit.', 'The chapter illustrates how to identify anomalies in the data using XY scatter plot and box plot, visually spotting anomalies in the data and asking more interesting questions about the data distribution. Identification of anomalies using XY scatter plot and box plot, visually spotting anomalies in data.', 'The chapter explains how to determine best and worst performing salespersons by country using pivot tables, showcasing how to use pivot tables to identify the top and bottom performing salespersons by amount. Use of pivot tables to determine best and worst performing salespersons by country, identifying top and bottom performing salespersons by amount.']}], 'duration': 993.382, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E1022595.jpg', 'highlights': ['Utilizing pivot tables for additional filtering and analysis, allowing users to focus on individual salespersons or products, and demonstrates the use of slicers for this purpose.', 'The chapter demonstrates how to use pivot tables to calculate dollar per unit for each product, showing the total amount and units for each product and then calculating a composite number called sales per unit.', 'The chapter illustrates how to identify anomalies in the data using XY scatter plot and box plot, visually spotting anomalies in the data and asking more interesting questions about the data distribution.', 'Creating a sales analysis report by country using pivot tables, including organizing the data by country, amount, and units, and applying formatting and sorting options for better visualization.', 'Adding conditional formatting and data bars to the pivot table to visually represent the data, including steps for adding and customizing the data bars for better readability.', 'The chapter explains how to determine best and worst performing salespersons by country using pivot tables, showcasing how to use pivot tables to identify the top and bottom performing salespersons by amount.', 'Using borders with dull color and applying horizontal borders to visually separate data, enhancing the readability and visual appeal of the report.', "Utilizing conditional formatting and data bars to represent values visually, providing a visual indication of the data and enhancing the report's informativeness.", 'Applying color techniques to differentiate units from the main focus, improving the readability and emphasis on key information within the report.']}, {'end': 2484.33, 'segs': [{'end': 2070.228, 'src': 'embed', 'start': 2041.136, 'weight': 3, 'content': [{'end': 2044.557, 'text': 'We can combine this table and that table in a number of ways.', 'start': 2041.136, 'duration': 3.421}, {'end': 2051.76, 'text': 'In many times when you are doing the data analysis, such combination of data from two or three different tables is fairly common.', 'start': 2045.097, 'duration': 6.663}, {'end': 2057.141, 'text': 'I will show you one common Excel technique that we can use to combine data like this.', 'start': 2052.58, 'duration': 4.561}, {'end': 2059.382, 'text': 'It is to use the lookup formulas.', 'start': 2057.661, 'duration': 1.721}, {'end': 2063.005, 'text': 'So I will say cost per unit here.', 'start': 2060.123, 'duration': 2.882}, {'end': 2066.226, 'text': 'We will simply say XLOOKUP.', 'start': 2063.625, 'duration': 2.601}, {'end': 2070.228, 'text': 'You can also use VLOOKUP if you are using an older version of Excel.', 'start': 2066.527, 'duration': 3.701}], 'summary': 'Common excel technique for combining data using lookup formulas like xlookup or vlookup.', 'duration': 29.092, 'max_score': 2041.136, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2041136.jpg'}, {'end': 2101.976, 'src': 'heatmap', 'start': 2066.527, 'weight': 1, 'content': [{'end': 2070.228, 'text': 'You can also use VLOOKUP if you are using an older version of Excel.', 'start': 2066.527, 'duration': 3.701}, {'end': 2079.195, 'text': "Product name at the rate product and then select the product column here and then specify the product's cost.", 'start': 2070.609, 'duration': 8.586}, {'end': 2082.737, 'text': "So I'll say product's table cost per unit.", 'start': 2079.235, 'duration': 3.502}, {'end': 2085.018, 'text': 'so this xlookup function.', 'start': 2083.397, 'duration': 1.621}, {'end': 2091.065, 'text': 'it will look up 70 dark bytes in the products table and then tell you what is the cost per unit.', 'start': 2085.018, 'duration': 6.047}, {'end': 2094.469, 'text': 'it will do so for all other items as well, because it is a table.', 'start': 2091.065, 'duration': 3.404}, {'end': 2101.976, 'text': 'when you type formula in one cell, excel will automatically fill the same formula all the way through, giving you cost per unit everywhere.', 'start': 2094.469, 'duration': 7.507}], 'summary': 'Use xlookup to find cost per unit in excel, e.g. look up 70 dark bytes to get cost per unit.', 'duration': 35.449, 'max_score': 2066.527, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2066527.jpg'}, {'end': 2233.857, 'src': 'embed', 'start': 2180.852, 'weight': 0, 'content': [{'end': 2193.162, 'text': 'so we will add that as a measure, add measure, total profit, And this is nothing but sum of amount, minus sum of cost.', 'start': 2180.852, 'duration': 12.31}, {'end': 2199.186, 'text': 'And we will just say that should be a currency value and add the total profit as well.', 'start': 2193.942, 'duration': 5.244}, {'end': 2206.773, 'text': "So here I'll get a nice little report that shows me by each of our product what is the profit we are generating.", 'start': 2199.667, 'duration': 7.106}, {'end': 2209.535, 'text': 'So we made $800, 000 profit total all.', 'start': 2206.813, 'duration': 2.722}, {'end': 2211.437, 'text': 'And this is how that is spread up.', 'start': 2210.256, 'duration': 1.181}, {'end': 2216.721, 'text': 'I can apply some sort order on this to look at our most profitable products.', 'start': 2212.157, 'duration': 4.564}, {'end': 2222.987, 'text': "I can also drop these other fields because we're only interested in total profit.", 'start': 2217.842, 'duration': 5.145}, {'end': 2233.857, 'text': 'We can even add a slicer on one of these fields like geography so I can look at it by India, New Zealand, Australia or all the countries.', 'start': 2223.587, 'duration': 10.27}], 'summary': 'Analyzing total profit of $800,000 across products and countries using a report with slicer options.', 'duration': 53.005, 'max_score': 2180.852, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2180852.jpg'}, {'end': 2358.328, 'src': 'embed', 'start': 2329.889, 'weight': 5, 'content': [{'end': 2335.732, 'text': "But if you need some help or you're in a hurry and you just want to know how this is done, let's see how this works.", 'start': 2329.889, 'duration': 5.843}, {'end': 2340.294, 'text': 'In order to select the country, this kind of thing is called data validation.', 'start': 2336.072, 'duration': 4.222}, {'end': 2345.557, 'text': 'So what we need is we need a list of all the values listed somewhere on the spreadsheet.', 'start': 2340.714, 'duration': 4.843}, {'end': 2347.297, 'text': "It doesn't have to be on the same page.", 'start': 2345.637, 'duration': 1.66}, {'end': 2349.839, 'text': 'It can be in a separate tab as well.', 'start': 2347.818, 'duration': 2.021}, {'end': 2358.328, 'text': 'So, once these values are there, you select the cell, you go to data, click on data validation, specify a list,', 'start': 2350.399, 'duration': 7.929}], 'summary': 'Using data validation, select country from a list in spreadsheet.', 'duration': 28.439, 'max_score': 2329.889, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2329889.jpg'}, {'end': 2405.57, 'src': 'heatmap', 'start': 2363.093, 'weight': 6, 'content': [{'end': 2368.499, 'text': 'This will now give you a small drop down within the cell from where I can pick a country.', 'start': 2363.093, 'duration': 5.406}, {'end': 2370.14, 'text': 'so notice this cell.', 'start': 2369.039, 'duration': 1.101}, {'end': 2375.666, 'text': 'it is e4 where we are selecting the country to see the number of transactions within the country.', 'start': 2370.14, 'duration': 5.526}, {'end': 2383.334, 'text': 'we can use the count ifs formula and just count how many times the geography that is in e4 has appeared.', 'start': 2375.666, 'duration': 7.668}, {'end': 2389.4, 'text': 'and to see totals and averages we simply use some ifs or average ifs formulas.', 'start': 2383.334, 'duration': 6.066}, {'end': 2390.962, 'text': 'what about this part of the report?', 'start': 2389.4, 'duration': 1.562}, {'end': 2393.963, 'text': 'here again, we just need to list all the people.', 'start': 2391.622, 'duration': 2.341}, {'end': 2397.025, 'text': 'i have listed them in the alphabetical order.', 'start': 2393.963, 'duration': 3.062}, {'end': 2398.086, 'text': 'we only have 10 people.', 'start': 2397.025, 'duration': 1.061}, {'end': 2405.57, 'text': 'so i kind of pasted the values here, remove duplicates and sorted and the amount is some ifs with additional conditions.', 'start': 2398.086, 'duration': 7.484}], 'summary': "Using count ifs, average ifs to analyze 10 people's transaction data by country.", 'duration': 42.477, 'max_score': 2363.093, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2363093.jpg'}, {'end': 2448.537, 'src': 'embed', 'start': 2419.398, 'weight': 7, 'content': [{'end': 2426.681, 'text': "next, for the check on whether somebody has met the target of 12 000 or not, i'm using a if formula.", 'start': 2419.398, 'duration': 7.283}, {'end': 2433.064, 'text': 'if the amount is greater than 12 000, i want one else, i want negative one and then i select all of these cells.', 'start': 2426.681, 'duration': 6.383}, {'end': 2437.846, 'text': "i'm just gonna clear the formats here to show you how the conditional formatting done.", 'start': 2433.064, 'duration': 4.782}, {'end': 2441.169, 'text': 'so then we go to conditional formatting icon.', 'start': 2438.246, 'duration': 2.923}, {'end': 2448.537, 'text': 'pick the indicator of this tick mark and x mark, and that will give you that we go to manage rules,', 'start': 2441.169, 'duration': 7.368}], 'summary': 'Using an if formula to check if amount is greater than 12,000 for conditional formatting.', 'duration': 29.139, 'max_score': 2419.398, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2419398.jpg'}], 'start': 2016.558, 'title': 'Profit analysis by product and data validation', 'summary': 'Covers profit analysis by product in excel, resulting in a total profit of $800,000, and explains data validation techniques, including creating drop-down lists and applying conditional formatting with examples of countifs, sumifs, and if formulas.', 'chapters': [{'end': 2307.637, 'start': 2016.558, 'title': 'Profit analysis by product', 'summary': 'Discusses combining tables to analyze profits by product using excel, including calculating total cost and profit, creating a pivot table, and applying filters to analyze profits by country, resulting in a total profit of $800,000.', 'duration': 291.079, 'highlights': ['Creating a pivot table to analyze total profit by product The pivot table shows a total profit of $800,000 and allows for sorting and filtering by product and geography.', 'Calculating total cost and profit for each product The calculation reveals a total profit of $800,000 and highlights products with losses, providing insight into profitability.', 'Using lookup formulas (XLOOKUP) to combine data from different tables The XLOOKUP function is used to find the cost per unit for each product, enabling accurate cost calculations for profit analysis.', 'Applying filters to analyze profits by country and salesperson Filters are applied to analyze profits by country and salesperson, including the ability to check if sales targets are met, providing detailed insights into sales performance.']}, {'end': 2484.33, 'start': 2308.297, 'title': 'Data validation and conditional formatting', 'summary': 'Explains how to use data validation to create drop-down lists for selecting countries and how to apply conditional formatting to create indicators for meeting targets, with examples of using countifs, sumifs, and if formulas.', 'duration': 176.033, 'highlights': ['Data validation allows creating a drop-down list for selecting countries, facilitating the analysis of transactions within a specific country.', 'Using countifs formula to count the occurrences of a specific country, sumifs for obtaining totals, averages, and conditional formatting for creating indicators for meeting targets.', 'Applying conditional formatting to show indicators for meeting targets, utilizing if formula to determine if the amount is greater than a specific value, and using icon sets for visual representation of meeting targets.', 'Listing people in alphabetical order, removing duplicates, and using sumifs with additional conditions to calculate amounts, along with using data bars for visual representation.']}], 'duration': 467.772, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2016558.jpg', 'highlights': ['Covers profit analysis by product in excel, resulting in a total profit of $800,000', 'Creating a pivot table to analyze total profit by product The pivot table shows a total profit of $800,000 and allows for sorting and filtering by product and geography.', 'Calculating total cost and profit for each product The calculation reveals a total profit of $800,000 and highlights products with losses, providing insight into profitability.', 'Using lookup formulas (XLOOKUP) to combine data from different tables The XLOOKUP function is used to find the cost per unit for each product, enabling accurate cost calculations for profit analysis.', 'Applying filters to analyze profits by country and salesperson Filters are applied to analyze profits by country and salesperson, including the ability to check if sales targets are met, providing detailed insights into sales performance.', 'Data validation allows creating a drop-down list for selecting countries, facilitating the analysis of transactions within a specific country.', 'Using countifs formula to count the occurrences of a specific country, sumifs for obtaining totals, averages, and conditional formatting for creating indicators for meeting targets.', 'Applying conditional formatting to show indicators for meeting targets, utilizing if formula to determine if the amount is greater than a specific value, and using icon sets for visual representation of meeting targets.']}, {'end': 2955.494, 'segs': [{'end': 2644.917, 'src': 'embed', 'start': 2589.54, 'weight': 0, 'content': [{'end': 2593.543, 'text': "So we'll create this and I'm just going to put my product here.", 'start': 2589.54, 'duration': 4.003}, {'end': 2595.924, 'text': 'This will list one row per product.', 'start': 2594.083, 'duration': 1.841}, {'end': 2598.686, 'text': 'So we can see that all the 22 products are there.', 'start': 2596.305, 'duration': 2.381}, {'end': 2608.172, 'text': 'And for each of these products, I want to see total amount, total units, and then total profit.', 'start': 2599.567, 'duration': 8.605}, {'end': 2613.817, 'text': "Let's start by asking the question of which are our least selling products.", 'start': 2609.213, 'duration': 4.604}, {'end': 2617.841, 'text': 'So we can go and sort this from smallest to largest.', 'start': 2614.478, 'duration': 3.363}, {'end': 2624.667, 'text': 'You can see that 85% dark bars is one of our least selling products and we only sell 1000 units of that.', 'start': 2617.901, 'duration': 6.766}, {'end': 2633.431, 'text': 'But you can see that it does have fairly high profit amounts of $30, 000 profit out of $35, 000 sales.', 'start': 2626.388, 'duration': 7.043}, {'end': 2636.273, 'text': 'So the profit is fairly high.', 'start': 2633.511, 'duration': 2.762}, {'end': 2641.355, 'text': 'But because we are kind of comparing different numbers, looking at these alone is hard.', 'start': 2636.693, 'duration': 4.662}, {'end': 2643.256, 'text': "So I'm going to add a new measure.", 'start': 2641.415, 'duration': 1.841}, {'end': 2644.917, 'text': 'Right click, add measure.', 'start': 2643.436, 'duration': 1.481}], 'summary': 'Analyzing 22 products, 85% dark bars stands out as a least selling product with 1000 units sold, generating $30,000 profit from $35,000 sales.', 'duration': 55.377, 'max_score': 2589.54, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2589540.jpg'}, {'end': 2744.845, 'src': 'embed', 'start': 2715.916, 'weight': 3, 'content': [{'end': 2721.758, 'text': 'And you can see that Almond Choco is one of the smallest products that we sell in terms of revenue.', 'start': 2715.916, 'duration': 5.842}, {'end': 2732.801, 'text': 'And it also happens to have not a very great profit percentage, 44%, in contrast with some of these very high numbers in 60s and 80s.', 'start': 2722.338, 'duration': 10.463}, {'end': 2744.845, 'text': 'So if I want to drop this one, we are not going to really take a huge hit on our bottom line or top line, and we will still be fairly good.', 'start': 2733.401, 'duration': 11.444}], 'summary': 'Almond choco has low revenue and 44% profit, considering dropping it for minimal impact.', 'duration': 28.929, 'max_score': 2715.916, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2715916.jpg'}, {'end': 2788.361, 'src': 'embed', 'start': 2764.432, 'weight': 4, 'content': [{'end': 2773.495, 'text': 'you can add a slicer on on the country and then you can pick the country where you want to, for example, test your discontinuation process.', 'start': 2764.432, 'duration': 9.063}, {'end': 2776.076, 'text': "so let's go with the new zealand.", 'start': 2773.495, 'duration': 2.581}, {'end': 2780.298, 'text': 'within new zealand you can see that almond choco is actually one of our loss making products.', 'start': 2776.076, 'duration': 4.222}, {'end': 2785.92, 'text': 'it is um, giving you negative 468 percent loss.', 'start': 2780.298, 'duration': 5.622}, {'end': 2788.361, 'text': 'so um, this is not good, right this?', 'start': 2785.92, 'duration': 2.441}], 'summary': 'In new zealand, almond choco incurs a 468% loss, indicating a significant issue.', 'duration': 23.929, 'max_score': 2764.432, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2764432.jpg'}, {'end': 2835.535, 'src': 'embed', 'start': 2810.447, 'weight': 5, 'content': [{'end': 2815.85, 'text': 'To look at Canada, Almond Choco is one of the more profitable ones within Canada market.', 'start': 2810.447, 'duration': 5.403}, {'end': 2819.052, 'text': 'So probably not a good idea to drop Almond Choco for Canada.', 'start': 2815.87, 'duration': 3.182}, {'end': 2824.675, 'text': "But this is Baker's Chocolate Chips is a good candidate $98, 204 units.", 'start': 2819.572, 'duration': 5.103}, {'end': 2830.798, 'text': 'And we incur a massive loss on that probably because we have to ship those things all the way to Canada.', 'start': 2825.695, 'duration': 5.103}, {'end': 2835.535, 'text': 'So this is how you can make the decision.', 'start': 2831.832, 'duration': 3.703}], 'summary': "Almond choco is profitable in canada, baker's chocolate chips incurs a massive loss due to shipping.", 'duration': 25.088, 'max_score': 2810.447, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2810447.jpg'}, {'end': 2886.664, 'src': 'embed', 'start': 2861.175, 'weight': 6, 'content': [{'end': 2866.497, 'text': "but baker's choco chips is somewhere at the bottom with the very high profitability ratio.", 'start': 2861.175, 'duration': 5.322}, {'end': 2873.979, 'text': 'so probably not a good idea to drop this one straight away, but only discontinue it for certain geographies.', 'start': 2866.497, 'duration': 7.482}, {'end': 2881.762, 'text': 'so this is how you can go and come up with that recommendation to say that So product almond choco need to be discontinued across the board.', 'start': 2873.979, 'duration': 7.783}, {'end': 2886.664, 'text': "Baker's choco chips only for geographies A, B and C like that.", 'start': 2882.182, 'duration': 4.482}], 'summary': "Baker's choco chips is less profitable, consider discontinuing for certain geographies.", 'duration': 25.489, 'max_score': 2861.175, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2861175.jpg'}], 'start': 2484.33, 'title': 'Product analysis for discontinuation', 'summary': 'Covers creating a country-level sales report, analyzing product profitability, and making informed decisions on discontinuing products, including highlighting an 85% profit from the least selling product, identifying low-profit products like almond choco with 44% profit and low revenue, and recommending discontinuation based on profitability ratios and loss percentages.', 'chapters': [{'end': 2668.941, 'start': 2484.33, 'title': 'Country level sales report', 'summary': 'Discusses how to create a dynamic country-level sales report, using a pivot table to analyze product sales and profitability to make decisions on discontinuing products, with a highlight on the 85% profit from the least selling product.', 'duration': 184.611, 'highlights': ['Using a pivot table to analyze total amount, total units, and total profit for each product to determine the least selling products and their profitability, where 85% dark bars stands out with $30,000 profit out of $35,000 sales.', 'Demonstrating the process of calculating profit percent to enable easy comparison of product profitability, revealing that 85% dark bars has an 85% profit margin.', 'Encouraging viewers to take up the challenge to create a different report showcasing country-level data based on different criteria or metrics for further analysis and sharing their results.']}, {'end': 2764.432, 'start': 2668.941, 'title': 'Product profitability analysis', 'summary': 'Demonstrates how to analyze product profitability using criteria such as profit percentage and revenue, identifying a low-profit product, almond choco, with 44% profit and low revenue, and suggesting discontinuation to improve bottom line.', 'duration': 95.491, 'highlights': ['By analyzing the profit percentage and revenue, the product Almond Choco is identified as having only 44% profit and low revenue, suggesting it as a candidate for discontinuation.', 'The demonstration emphasizes the importance of considering profit percentage and revenue when evaluating product profitability, showcasing the impact of product discontinuation on the bottom line.', 'The speaker suggests using conditional formatting to quickly identify profitable products, citing an example of a product with 28% profit as a better choice compared to others.', 'The transcript advises on the use of sorting by amount to identify products bringing in the least revenue, demonstrating the practical application of this approach with the example of Almond Choco.']}, {'end': 2955.494, 'start': 2764.432, 'title': 'Product discontinuation analysis', 'summary': "Illustrates how to use data analysis to identify loss-making products and make informed decisions on discontinuation, such as recommending to discontinue almond choco across all markets and baker's choco chips for certain geographies based on profitability ratios and loss percentages.", 'duration': 191.062, 'highlights': ['Almond choco in New Zealand is generating a negative 468 percent loss, reconfirming the recommendation to discontinue it.', "Baker's Chocolate Chips in Canada is a good candidate for discontinuation, with a $98 profit and a massive loss incurred due to shipping costs.", "Recommendation to discontinue almond choco across all markets and baker's choco chips for certain geographies based on profitability ratios and loss percentages."]}], 'duration': 471.164, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/v2oNWja7M2E/pics/v2oNWja7M2E2484330.jpg', 'highlights': ['85% dark bars stands out with $30,000 profit out of $35,000 sales.', '85% dark bars has an 85% profit margin.', 'Using a pivot table to analyze total amount, total units, and total profit for each product to determine the least selling products and their profitability.', 'Almond Choco is identified as having only 44% profit and low revenue, suggesting it as a candidate for discontinuation.', 'Almond choco in New Zealand is generating a negative 468 percent loss, reconfirming the recommendation to discontinue it.', "Baker's Chocolate Chips in Canada is a good candidate for discontinuation, with a $98 profit and a massive loss incurred due to shipping costs.", "Recommendation to discontinue almond choco across all markets and baker's choco chips for certain geographies based on profitability ratios and loss percentages."]}], 'highlights': ['The course covers ten ways to analyze data using Excel, including descriptive statistics, exploratory data analysis, formulas, pivot tables, data consolidation, answering open-ended questions, and presenting analysis with various charting and visualization techniques.', 'Utilizing the unique function in Excel 365 to determine the count of distinct products, revealing a total of 22 different products.', 'Using the duplicate value highlighting technique, specific numbers such as 459, 366, and 348 were identified as duplicated, providing quantifiable data for analysis.', 'Utilizing pivot tables for additional filtering and analysis, allowing users to focus on individual salespersons or products, and demonstrates the use of slicers for this purpose.', 'Covers profit analysis by product in excel, resulting in a total profit of $800,000', '85% dark bars stands out with $30,000 profit out of $35,000 sales.', 'Almond Choco is identified as having only 44% profit and low revenue, suggesting it as a candidate for discontinuation.']}