title
How to Clean, Analyze and Present Data with Excel (FREE Adv. Course)

description
You asked for it. Here is my free course on advanced data analytics. 💥Want more... get my Excel School program 👉 https://chandoo.org/wp/excel-school-program/ In this comprehensive video, learn: 1) How to approach a data analysis project 2) How to systematically clean data 3) Doing EDA with Excel formulas & tables 4) How to use Power Query to combine two datasets 5) Statistical Analysis of data 6) Using formulas like COUNTIFS, SUMIFS, XLOOKUP 7) Making an information finder with your data 8) Male vs. Female Analysis with Pivot tables 9) Calculating Bonus based on business rules 10) Visual analytics of data with 4 topics 11) Analysing the salary spread (Histograms & Box plots) 12) Relationship between Salary & Rating 13) Staff growth over time - trend analysis 14) Regional Scorecard to compare NZ with India You will also learn below Excel features: 1) Using Tables 2) Working with Power Query 3) Formulas 4) Pivot Tables 5) Conditional formatting 6) Charts 7) Data Validation 8) Keyboard Shortcuts & tricks 9) Dashboard Design 💥📁 Sample files: ================ Blank data file (perfect for following along) - https://chandoo.org/wp/wp-content/uploads/2023/06/blank-data-file.xlsx Completed workbook - https://chandoo.org/wp/wp-content/uploads/2023/06/data-analysis-03.xlsx ⏱ Video Timestamps: ================== 0:00 - Introduction & Topic Coverage 1:50 - Data Cleaning with Excel 6:17 - Ad-hoc Analysis of Data 10:23 - Using Power Query to combine & clean data in one go 21:25 - 5 Business Questions for Data Analysis 23:12 - Quick Analysis of Data - Summaries & Statistics 31:17 - Building an information finder using Lookup Formulas 36:59 - Information Finder v2.0 41:43 - Male vs. Female Comparison with Pivots 48:15 - Calculating Bonus based on Business Rules 51:03 - 4 Data Visualization Themes 52:46 - Understanding the salary spread with histograms & box plots 59:50 - Salary vs. Employee Rating Correlation 1:08:50 - Employee Trend over time 1:19:19 - Creating a Report Card to compare NZ vs. India 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: Beginner to PRO Data Analysis Excel Course - https://youtu.be/v2oNWja7M2E 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': 'How to Clean, Analyze and Present Data with Excel (FREE Adv. Course)', 'heatmap': [{'end': 729.119, 'start': 666.19, 'weight': 0.73}, {'end': 1230.741, 'start': 1164.646, 'weight': 0.722}, {'end': 1729.243, 'start': 1667.13, 'weight': 1}, {'end': 2061.387, 'start': 1999.102, 'weight': 0.823}, {'end': 2448.75, 'start': 2329.412, 'weight': 0.905}, {'end': 2839.339, 'start': 2776.963, 'weight': 0.721}, {'end': 3061.16, 'start': 2996.201, 'weight': 0.72}], 'summary': 'Tutorial on excel covers data processing, power query for data analysis, bonus calculator creation, information retriever building, pivot tables, and visualizing data. it includes insights such as average salary, female staff ratio, salary distribution, and company growth trends, with an emphasis on practical demonstrations, and addresses business questions and problems, providing a comprehensive learning experience.', 'chapters': [{'end': 466.393, 'segs': [{'end': 78.766, 'src': 'embed', 'start': 0.703, 'weight': 0, 'content': [{'end': 6.047, 'text': 'Microsoft Excel continues to be the number one software for data analysis work.', 'start': 0.703, 'duration': 5.344}, {'end': 16.033, 'text': 'So in this video, let me explain how we can clean structure, analyze and present data using Excel.', 'start': 6.747, 'duration': 9.286}, {'end': 24.759, 'text': 'This is a really long video and we are going to cover quite a bit of width and depth of Excel features in this video.', 'start': 16.454, 'duration': 8.305}, {'end': 34.55, 'text': 'for the purpose of this exercise, i am going to use a fictional data set of awesome chocolates employee data.', 'start': 25.82, 'duration': 8.73}, {'end': 40.096, 'text': 'the sample data set is available for you to download in the video description below.', 'start': 34.55, 'duration': 5.546}, {'end': 50.566, 'text': 'in this video, we are going to learn how to use Excel in general, how to work with power query for data management and data cleansing.', 'start': 40.096, 'duration': 10.47}, {'end': 58.213, 'text': 'how to use simple as well as complex formulas to analyze data and answer business questions.', 'start': 50.566, 'duration': 7.647}, {'end': 64.7, 'text': 'how to use pivot tables to structure your data and quickly calculate various types of outcomes.', 'start': 58.213, 'duration': 6.487}, {'end': 75.805, 'text': 'how to create and format Excel charts, how to use data validation, how to use slicers, conditional formatting and a whole bunch of other things.', 'start': 65.239, 'duration': 10.566}, {'end': 78.766, 'text': 'Along the course of this,', 'start': 77.005, 'duration': 1.761}], 'summary': 'Microsoft excel, data analysis, cleaning, structuring, analyzing, presenting data, power query, pivot tables, formulas, charts, data validation, slicers, conditional formatting covered in a comprehensive video.', 'duration': 78.063, 'max_score': 0.703, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI703.jpg'}, {'end': 174.134, 'src': 'embed', 'start': 140.58, 'weight': 7, 'content': [{'end': 145.964, 'text': 'So our first task is to take all of this data, consolidate it,', 'start': 140.58, 'duration': 5.384}, {'end': 152.828, 'text': 'clean it and make sure that it is in the ready to use form from a data analysis perspective.', 'start': 145.964, 'duration': 6.864}, {'end': 162.675, 'text': 'Whenever you have data like this, it is a big challenge to understand and use the data if the data is not properly maintained.', 'start': 154.629, 'duration': 8.046}, {'end': 174.134, 'text': 'here, as i can see, there is a lot of information and we are not even sure if there is any duplicates, missing elements or anything else in the data.', 'start': 163.41, 'duration': 10.724}], 'summary': 'Consolidate and clean data for analysis, addressing duplicates and missing elements.', 'duration': 33.554, 'max_score': 140.58, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI140580.jpg'}, {'end': 417.836, 'src': 'embed', 'start': 372.786, 'weight': 5, 'content': [{'end': 376.067, 'text': 'You just want to keep the data as it is in separate tables.', 'start': 372.786, 'duration': 3.281}, {'end': 380.289, 'text': 'But you would like to do some quick summary analysis of the data.', 'start': 376.647, 'duration': 3.642}, {'end': 385.971, 'text': 'Like, for example, you want to know how many employees are there or what is the average salary, etc.', 'start': 380.629, 'duration': 5.342}, {'end': 393.256, 'text': 'what you could do with tables is, when you have a table, you can select any cell in the table, go to the table,', 'start': 387.131, 'duration': 6.125}, {'end': 398.24, 'text': 'design ribbon and from here enable the total row option.', 'start': 393.256, 'duration': 4.984}, {'end': 405.587, 'text': 'this will add a total row at the very bottom and here it tells me we have 100 people in the new zealand data.', 'start': 398.24, 'duration': 7.347}, {'end': 414.133, 'text': "this in itself looks suspicious to me, because i don't think we have that many people in new zealand, but we will investigate that a little later.", 'start': 406.227, 'duration': 7.906}, {'end': 417.836, 'text': "let's say i want to see what the average salary is, so i can go to here.", 'start': 414.133, 'duration': 3.703}], 'summary': 'Data tables enable quick analysis, e.g., 100 employees in new zealand, suspicion raised for investigation.', 'duration': 45.05, 'max_score': 372.786, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI372786.jpg'}], 'start': 0.703, 'title': 'Excel and data processing', 'summary': 'Covers using excel for data analysis, power query, complex formulas, pivot tables, and chart formatting. it also discusses data consolidation, cleaning, and analysis with excel tables, and obtaining insights such as number of employees and average salary.', 'chapters': [{'end': 108.197, 'start': 0.703, 'title': 'Excel data analysis and presentation', 'summary': 'Covers how to use microsoft excel for data analysis, including working with power query, using simple and complex formulas, creating pivot tables, formatting charts, and utilizing various excel features.', 'duration': 107.494, 'highlights': ['Microsoft Excel is the number one software for data analysis work Excel continues to be the top software for data analysis.', 'Covering width and depth of Excel features The video covers a wide range of Excel features extensively.', 'Using power query for data management and data cleansing The chapter explains how to work with power query for data management and cleansing.', 'Learning to use simple and complex formulas to analyze data The chapter teaches how to use both simple and complex formulas for data analysis.', 'Creating and formatting Excel charts The video demonstrates how to create and format Excel charts for data visualization.']}, {'end': 466.393, 'start': 114.063, 'title': 'Data consolidation and cleaning', 'summary': 'Discusses the challenges of consolidating and cleaning data from two different locations, using excel tables to format and analyze the data, and enabling the total row option to obtain insights such as the number of employees and average salary.', 'duration': 352.33, 'highlights': ['Enabling the total row option adds a total row at the bottom of the table, revealing that there are 100 people in the New Zealand data, prompting further investigation. Enabling the total row option in the table reveals that there are 100 people in the New Zealand data, which seems suspicious and prompts further investigation.', 'Using Excel tables to quickly apply formatting, add borders and highlights, and set up filters for the data, making it easier to analyze different aspects such as departments, age range, and ratings. Excel tables allow for quick application of formatting, addition of borders and highlights, and setting up filters for the data, simplifying the analysis of departments, age range, and ratings.', 'Discussing the challenges of understanding and using unorganized data, emphasizing the importance of properly maintaining data for effective data analysis. Highlighting the challenge of understanding and using unorganized data, emphasizing the importance of properly maintaining data for effective data analysis.']}], 'duration': 465.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI703.jpg', 'highlights': ['Microsoft Excel is the number one software for data analysis work Excel continues to be the top software for data analysis.', 'Covering width and depth of Excel features The video covers a wide range of Excel features extensively.', 'Using power query for data management and data cleansing The chapter explains how to work with power query for data management and cleansing.', 'Learning to use simple and complex formulas to analyze data The chapter teaches how to use both simple and complex formulas for data analysis.', 'Creating and formatting Excel charts The video demonstrates how to create and format Excel charts for data visualization.', 'Enabling the total row option adds a total row at the bottom of the table, revealing that there are 100 people in the New Zealand data, prompting further investigation. Enabling the total row option in the table reveals that there are 100 people in the New Zealand data, which seems suspicious and prompts further investigation.', 'Using Excel tables to quickly apply formatting, add borders and highlights, and set up filters for the data, making it easier to analyze different aspects such as departments, age range, and ratings. Excel tables allow for quick application of formatting, addition of borders and highlights, and setting up filters for the data, simplifying the analysis of departments, age range, and ratings.', 'Discussing the challenges of understanding and using unorganized data, emphasizing the importance of properly maintaining data for effective data analysis. Highlighting the challenge of understanding and using unorganized data, emphasizing the importance of properly maintaining data for effective data analysis.']}, {'end': 1356.975, 'segs': [{'end': 528.693, 'src': 'embed', 'start': 500.54, 'weight': 2, 'content': [{'end': 507.166, 'text': "So I'm going to select all of these again and then press control shift 3 to turn the date formatting correctly.", 'start': 500.54, 'duration': 6.626}, {'end': 517.096, 'text': 'So these two shortcuts control shift 4 for currency, control shift 3 for dates are really simple to remember and very handy to press,', 'start': 507.707, 'duration': 9.389}, {'end': 519.158, 'text': 'and they quickly change the formatting for you.', 'start': 517.096, 'duration': 2.062}, {'end': 528.693, 'text': 'So using that total row, we can add quick analysis of our information to the tables.', 'start': 522.389, 'duration': 6.304}], 'summary': 'Shortcuts control shift 4 for currency, control shift 3 for dates are handy and quickly change formatting.', 'duration': 28.153, 'max_score': 500.54, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI500540.jpg'}, {'end': 652.033, 'src': 'embed', 'start': 625.984, 'weight': 0, 'content': [{'end': 634.327, 'text': 'For our next step, we are gonna take the New Zealand data and India data and then combine everything into one consolidated table.', 'start': 625.984, 'duration': 8.343}, {'end': 639.669, 'text': 'And then that is the table that we will use to do all our data analysis.', 'start': 634.847, 'duration': 4.822}, {'end': 648.672, 'text': 'To do this kind of a thing, we are gonna use the data ribbon get and transform data area.', 'start': 642.55, 'duration': 6.122}, {'end': 652.033, 'text': 'This is what normally we refer to as Power Query.', 'start': 649.072, 'duration': 2.961}], 'summary': 'Combine new zealand and india data into a consolidated table for data analysis using power query.', 'duration': 26.049, 'max_score': 625.984, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI625984.jpg'}, {'end': 729.119, 'src': 'heatmap', 'start': 666.19, 'weight': 0.73, 'content': [{'end': 670.912, 'text': 'but they might be called different or they might appear on different parts of the screen.', 'start': 666.19, 'duration': 4.722}, {'end': 679.697, 'text': 'So refer to the help on the web or somewhere if you get stuck or post a comment so that I can help you out where to find these things.', 'start': 671.412, 'duration': 8.285}, {'end': 690.968, 'text': "So now that we have data and India data in separate tabs, let's go ahead and combine these two into one giant table and then do the analysis.", 'start': 681.263, 'duration': 9.705}, {'end': 696.531, 'text': "First up, let's take this data and load it into Power Query.", 'start': 692.669, 'duration': 3.862}, {'end': 705.235, 'text': 'To do this, we can just select any cell here and then from the data ribbon, click on from table slash range option.', 'start': 696.971, 'duration': 8.264}, {'end': 715.729, 'text': 'This will load my NZStaff into the Power Query as a table.', 'start': 710.225, 'duration': 5.504}, {'end': 719.552, 'text': 'If you have never used Power Query before.', 'start': 716.99, 'duration': 2.562}, {'end': 729.119, 'text': 'Power Query is a data pre-processing engine that we can use to kind of tell Excel how we would like our data to be cleaned,', 'start': 719.552, 'duration': 9.567}], 'summary': 'Combine data from different tabs, load into power query for analysis.', 'duration': 62.929, 'max_score': 666.19, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI666190.jpg'}, {'end': 1006.446, 'src': 'embed', 'start': 983.185, 'weight': 3, 'content': [{'end': 991.232, 'text': 'The first cleanup activity that I would like to do is check the name column and if there is any duplicate values, take out those duplicates.', 'start': 983.185, 'duration': 8.047}, {'end': 998.138, 'text': "So to do that, we'll just select this column, right click and then say remove duplicates.", 'start': 992.894, 'duration': 5.244}, {'end': 1006.446, 'text': 'What Power Query will do at this point is it will keep the first occurrence of the name and then subsequent occurrences of the same name will be deleted.', 'start': 998.779, 'duration': 7.667}], 'summary': 'Remove duplicate values from the name column using power query.', 'duration': 23.261, 'max_score': 983.185, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI983185.jpg'}, {'end': 1078.683, 'src': 'embed', 'start': 1051.387, 'weight': 4, 'content': [{'end': 1057.531, 'text': 'Anytime a gender value is not present, what we really want to do is code them as other gender.', 'start': 1051.387, 'duration': 6.144}, {'end': 1064.295, 'text': 'So to do this, we can use a replacement type of a step within Power Query.', 'start': 1059.212, 'duration': 5.083}, {'end': 1068.857, 'text': 'So select the gender column, right click and then say replace values.', 'start': 1064.695, 'duration': 4.162}, {'end': 1072.359, 'text': 'The missing genders are called null here.', 'start': 1070.298, 'duration': 2.061}, {'end': 1078.683, 'text': 'So we can say value to find would be null in small letters and then replace with other.', 'start': 1072.579, 'duration': 6.104}], 'summary': "Replace missing gender values with 'other' using power query.", 'duration': 27.296, 'max_score': 1051.387, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1051387.jpg'}, {'end': 1230.741, 'src': 'heatmap', 'start': 1164.646, 'weight': 0.722, 'content': [{'end': 1169.809, 'text': 'click on the little arrow next to close and load and use the third option close and load two.', 'start': 1164.646, 'duration': 5.163}, {'end': 1177.414, 'text': "And then for now I'm just going to say just make a connection, don't load anything and click okay.", 'start': 1171.331, 'duration': 6.083}, {'end': 1184.617, 'text': 'So this will load all the three queries that we built, nzstaff, indiastaff and finalstaff as connections.', 'start': 1178.094, 'duration': 6.523}, {'end': 1191.04, 'text': "Now let's add a new tab and this tab, we will call this as all staff.", 'start': 1185.557, 'duration': 5.483}, {'end': 1194.778, 'text': 'This is our master data.', 'start': 1192.777, 'duration': 2.001}, {'end': 1203.564, 'text': "I'm going to right-click here, tab color, and then make the color yellow so we can quickly spot it when you have multiple spreadsheets.", 'start': 1195.239, 'duration': 8.325}, {'end': 1208.047, 'text': 'In this all staff page, I want to load all the staff.', 'start': 1203.684, 'duration': 4.363}, {'end': 1218.074, 'text': 'Right-click this one, and then right-click on the staff, and then say load to, and load it as a table in the existing worksheet here.', 'start': 1208.307, 'duration': 9.767}, {'end': 1221.911, 'text': 'so that it will load the all staff data here.', 'start': 1219.468, 'duration': 2.443}, {'end': 1230.741, 'text': 'We started with the blue table, orange table, combined and cleaned everything to end up with this data set that has all our employees.', 'start': 1222.672, 'duration': 8.069}], 'summary': "Loaded three queries as connections, created 'all staff' tab, and loaded all staff data.", 'duration': 66.095, 'max_score': 1164.646, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1164646.jpg'}], 'start': 467.074, 'title': 'Excel and power query for data analysis', 'summary': 'Covers formatting date and currency in excel, removing duplicate data, and using power query to combine and clean data, resulting in a master dataset for analysis, with a focus on answering five business questions.', 'chapters': [{'end': 750.07, 'start': 467.074, 'title': 'Excel data formatting and analysis', 'summary': 'Explains how to format date and currency in excel using shortcuts, identifies and removes duplicate data using conditional formatting and manual deletion, and demonstrates using power query to combine and clean data from multiple tabs for analysis.', 'duration': 282.996, 'highlights': ['Excel stores dates in number format, representing the number of days since 1st January 1900. The date 44,446 represents the number of days since the start of the Excel calendar, which begins on 1st January 1900.', 'Shortcut keys control shift 3 and control shift 4 format dates and currency, simplifying formatting tasks. Using shortcuts like control shift 3 for dates and control shift 4 for currency simplifies the process of formatting in Excel.', 'Using conditional formatting to identify duplicate values in a column and manual deletion to resolve duplicate data issues. The speaker demonstrates using conditional formatting to identify duplicate values in a column and manually deleting the second occurrence to resolve duplicate data issues.', 'Introduction to Power Query for combining and cleaning data from multiple tabs for analysis. The chapter introduces Power Query, a data pre-processing engine, to combine and clean data from multiple tabs for analysis purposes.']}, {'end': 1356.975, 'start': 751.27, 'title': 'Combining and cleaning staff data', 'summary': 'Discusses combining new zealand and india staff data using power query, duplicating tables, appending queries, removing duplicates, replacing missing gender values, and cleaning the date joined column, ultimately leading to a master dataset for further analysis, with a focus on answering five business questions.', 'duration': 605.705, 'highlights': ['The chapter discusses combining New Zealand and India staff data using Power Query, duplicating tables, appending queries, removing duplicates, replacing missing gender values, and cleaning the date joined column, ultimately leading to a master dataset. The process involves combining and cleaning staff data from New Zealand and India using Power Query to create a master dataset.', "The process involves appending the India staff data with the NZ staff data to create a new query called 'staff', which contains all the combined data from India and New Zealand. Appending the India staff data with the NZ staff data creates a new query called 'staff', containing the combined data from both countries.", 'The chapter highlights the process of removing duplicate values from the name column in the consolidated data. The removal of duplicate values from the name column in the consolidated data is emphasized.', "The process of replacing missing gender values with 'other' is discussed as a step within Power Query. Replacing missing gender values with 'other' is highlighted as a step within Power Query.", 'The chapter emphasizes the action of cleaning the date joined column by converting it to a date type and then loading the data into Excel. The cleaning of the date joined column by converting it to a date type and loading the data into Excel is emphasized.']}], 'duration': 889.901, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI467074.jpg', 'highlights': ['The chapter introduces Power Query, a data pre-processing engine, to combine and clean data from multiple tabs for analysis purposes.', 'The process involves combining and cleaning staff data from New Zealand and India using Power Query to create a master dataset.', 'Using shortcuts like control shift 3 for dates and control shift 4 for currency simplifies the process of formatting in Excel.', 'The removal of duplicate values from the name column in the consolidated data is emphasized.', "Replacing missing gender values with 'other' is highlighted as a step within Power Query."]}, {'end': 1871.348, 'segs': [{'end': 1553.848, 'src': 'embed', 'start': 1523.735, 'weight': 0, 'content': [{'end': 1527.738, 'text': 'make a note that this is after we have removed all the duplicates.', 'start': 1523.735, 'duration': 4.003}, {'end': 1533.403, 'text': 'so if you actually count how many people are there in the original data sets, this number will be a little less than that,', 'start': 1527.738, 'duration': 5.665}, {'end': 1537.266, 'text': 'because some people are removed due to duplication.', 'start': 1533.403, 'duration': 3.863}, {'end': 1545.253, 'text': 'next up, average of the salary column is using average function, average of salary column.', 'start': 1537.266, 'duration': 7.987}, {'end': 1553.848, 'text': 'so select like that average salary is 77, 173.', 'start': 1545.253, 'duration': 8.595}], 'summary': 'After removing duplicates, the original dataset has slightly fewer people, with an average salary of $77,173.', 'duration': 30.113, 'max_score': 1523.735, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1523735.jpg'}, {'end': 1611.722, 'src': 'embed', 'start': 1581.949, 'weight': 1, 'content': [{'end': 1588.493, 'text': 'if all the salaries are arranged in ascending or descending order, what is the middle point looking like?', 'start': 1581.949, 'duration': 6.544}, {'end': 1589.934, 'text': "so let's calculate median.", 'start': 1588.493, 'duration': 1.441}, {'end': 1597.678, 'text': 'here the median can be done with median function and again select the column and we can see that.', 'start': 1589.934, 'duration': 7.744}, {'end': 1603.679, 'text': 'So, for example, here my average is 77, 000, whereas median is 75, 000..', 'start': 1598.777, 'duration': 4.902}, {'end': 1611.722, 'text': 'This kind of a difference indicates that there are probably a few people who have very high salaries,', 'start': 1603.679, 'duration': 8.043}], 'summary': 'The median salary is $75,000, which is lower than the average, indicating a few very high salaries.', 'duration': 29.773, 'max_score': 1581.949, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1581949.jpg'}, {'end': 1729.243, 'src': 'heatmap', 'start': 1667.13, 'weight': 1, 'content': [{'end': 1674.339, 'text': 'the tenure can be calculated by taking this date and subtracting that from the current date.', 'start': 1667.13, 'duration': 7.209}, {'end': 1681.127, 'text': 'to get the current date, we can use the today function and then minus of this.', 'start': 1674.339, 'duration': 6.788}, {'end': 1683.529, 'text': 'that will give you a number.', 'start': 1681.127, 'duration': 2.402}, {'end': 1692.738, 'text': 'unfortunately, here excel has formatted that number as a date, But if I select the column and then change the formatting to general,', 'start': 1683.529, 'duration': 9.209}, {'end': 1695.779, 'text': 'we can see how many days each of these people have been with us.', 'start': 1692.738, 'duration': 3.041}, {'end': 1700.022, 'text': 'For example, this person, Deepali Charan, has been with us for 879 days.', 'start': 1696.54, 'duration': 3.482}, {'end': 1705.984, 'text': 'Now this kind of a number looks a little bit ridiculous.', 'start': 1703.001, 'duration': 2.983}, {'end': 1714.471, 'text': 'So what I want to do is do this calculation and then divide that with 365 so that we can get that in years.', 'start': 1706.364, 'duration': 8.107}, {'end': 1718.054, 'text': "And then let's just apply a number formatting here.", 'start': 1715.391, 'duration': 2.663}, {'end': 1722.177, 'text': '2.41 years for this person, 0.93 for that person.', 'start': 1718.074, 'duration': 4.103}, {'end': 1729.243, 'text': "Now let's calculate the average of that average of staff tenure column.", 'start': 1722.778, 'duration': 6.465}], 'summary': 'Calculating staff tenure in days and converting to years, average tenure is 1.67 years.', 'duration': 62.113, 'max_score': 1667.13, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1667130.jpg'}, {'end': 1705.984, 'src': 'embed', 'start': 1683.529, 'weight': 3, 'content': [{'end': 1692.738, 'text': 'unfortunately, here excel has formatted that number as a date, But if I select the column and then change the formatting to general,', 'start': 1683.529, 'duration': 9.209}, {'end': 1695.779, 'text': 'we can see how many days each of these people have been with us.', 'start': 1692.738, 'duration': 3.041}, {'end': 1700.022, 'text': 'For example, this person, Deepali Charan, has been with us for 879 days.', 'start': 1696.54, 'duration': 3.482}, {'end': 1705.984, 'text': 'Now this kind of a number looks a little bit ridiculous.', 'start': 1703.001, 'duration': 2.983}], 'summary': 'Excel formatted number as date, deepali charan has been with us for 879 days.', 'duration': 22.455, 'max_score': 1683.529, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1683529.jpg'}, {'end': 1828.97, 'src': 'embed', 'start': 1798.595, 'weight': 2, 'content': [{'end': 1805.281, 'text': 'Control shift 5 or control percentage symbol on your keyboard and that will apply the percentage formatting to this.', 'start': 1798.595, 'duration': 6.686}, {'end': 1807.824, 'text': '47% female ratio.', 'start': 1805.301, 'duration': 2.523}, {'end': 1808.885, 'text': 'Pretty good.', 'start': 1808.444, 'duration': 0.441}, {'end': 1812.448, 'text': 'Almost one in every two employees is a female at Awesome Chocolates.', 'start': 1809.045, 'duration': 3.403}, {'end': 1814.918, 'text': 'This is a quick summary.', 'start': 1813.857, 'duration': 1.061}, {'end': 1821.223, 'text': 'Like I said, we could use either formulas or pivot tables to build these kind of things.', 'start': 1815.599, 'duration': 5.624}, {'end': 1828.97, 'text': 'As I plan to introduce pivot tables later on in this video, I thought we can learn some of the formula versions of this and do that.', 'start': 1821.864, 'duration': 7.106}], 'summary': '47% female ratio at awesome chocolates. pivot tables to be introduced later.', 'duration': 30.375, 'max_score': 1798.595, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1798595.jpg'}, {'end': 1886.919, 'src': 'embed', 'start': 1849.067, 'weight': 4, 'content': [{'end': 1855.429, 'text': 'and we just want to see what percentage of our staff have more than ninety thousand dollar as salary.', 'start': 1849.067, 'duration': 6.362}, {'end': 1863.671, 'text': 'so calculate this using a formula either one formula or a bunch of different formulas and then print that value there as your homework.', 'start': 1855.429, 'duration': 8.242}, {'end': 1871.348, 'text': 'So that is the first section of the business question, which is doing a quick summary of our data.', 'start': 1864.943, 'duration': 6.405}, {'end': 1886.919, 'text': 'For our second section, we would like to build a information retriever or information finder wherein, if I type an employee name,', 'start': 1876.932, 'duration': 9.987}], 'summary': 'Calculate percentage of staff with salary > $90,000. build an employee information retriever.', 'duration': 37.852, 'max_score': 1849.067, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1849067.jpg'}], 'start': 1357.556, 'title': 'Creating excel bonus calculator', 'summary': 'Demonstrates how to build a bonus calculator in excel, using count, average, and median functions to analyze employee data and reveal insights such as average salary, age, and female staff ratio for a company with 183 employees. it also covers calculating average tenure and female ratio, with the female ratio at approximately 47% and includes a homework problem of calculating the percentage of employees earning more than $90,000.', 'chapters': [{'end': 1634.114, 'start': 1357.556, 'title': 'Building bonus calculator in excel', 'summary': 'Focuses on building a bonus calculator in excel, utilizing functions like count, average, and median to analyze employee data, revealing insights such as the average salary and age, and the female staff ratio.', 'duration': 276.558, 'highlights': ['Calculating count of employees using count a function, revealing 183 employees after removing duplicates. Counting 183 unique employees after removing duplicates from the data set.', 'Determining the average salary using the average function, resulting in an average salary of $77,173. Obtaining the average salary of $77,173 from the data set.', 'Calculating the median of salary to assess the distribution, revealing a median salary of $75,000, indicating potential outliers with high salaries. Identifying a median salary of $75,000, suggesting potential outliers with high salaries affecting the average.', 'Analyzing the female staff ratio by calculating the percentage of female staff against the total staff. Assessing the percentage of female staff relative to the total staff count.']}, {'end': 1871.348, 'start': 1634.775, 'title': 'Calculating average tenure and female ratio', 'summary': 'Discusses adding a tenure column to calculate average tenure and female ratio in excel for a company with 183 employees, including examples of actual calculations and a homework problem. the female ratio is approximately 47% and the audience is tasked with calculating the percentage of employees earning more than $90,000.', 'duration': 236.573, 'highlights': ['The female ratio is approximately 47%, indicating that almost one in every two employees is female at Awesome Chocolates.', 'Example calculation shows Deepali Charan has been with the company for 879 days, equivalent to 2.41 years, with another employee at 0.93 years.', 'The audience is given a homework problem to calculate the percentage of employees earning more than $90,000 using a formula or a series of formulas.']}], 'duration': 513.792, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1357556.jpg', 'highlights': ['Calculating count of employees using count a function, revealing 183 employees after removing duplicates.', 'Determining the average salary using the average function, resulting in an average salary of $77,173.', 'The female ratio is approximately 47%, indicating that almost one in every two employees is female at Awesome Chocolates.', 'Example calculation shows Deepali Charan has been with the company for 879 days, equivalent to 2.41 years, with another employee at 0.93 years.', 'The audience is given a homework problem to calculate the percentage of employees earning more than $90,000 using a formula or a series of formulas.']}, {'end': 2852.854, 'segs': [{'end': 1932.234, 'src': 'embed', 'start': 1876.932, 'weight': 0, 'content': [{'end': 1886.919, 'text': 'For our second section, we would like to build a information retriever or information finder wherein, if I type an employee name,', 'start': 1876.932, 'duration': 9.987}, {'end': 1891.863, 'text': 'I would like to see their entire details printed to me neatly underneath.', 'start': 1886.919, 'duration': 4.944}, {'end': 1902.798, 'text': 'This sort of an operation is called looking up, and we can use the lookup functions in Excel to do this work.', 'start': 1895.973, 'duration': 6.825}, {'end': 1904.539, 'text': 'We can also do it manually.', 'start': 1903.058, 'duration': 1.481}, {'end': 1915.366, 'text': 'For example, if I have a name like Bar Fonny and I just want to know where this person works, when they have joined our organization,', 'start': 1904.659, 'duration': 10.707}, {'end': 1917.447, 'text': 'how much salary we are paying them, etc.', 'start': 1915.366, 'duration': 2.081}, {'end': 1932.234, 'text': 'i can use the filter button here and then just type bar and you know we can see that the name shows up there and then click ok and then quickly see all the details about them.', 'start': 1918.448, 'duration': 13.786}], 'summary': 'Creating an information retriever to find employee details using lookup functions in excel or manually.', 'duration': 55.302, 'max_score': 1876.932, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1876932.jpg'}, {'end': 1984.64, 'src': 'embed', 'start': 1956.634, 'weight': 5, 'content': [{'end': 1958.895, 'text': 'There are two kinds of lookup functions that we could use.', 'start': 1956.634, 'duration': 2.261}, {'end': 1963.477, 'text': 'We could use either XLOOKUP or VLOOKUP.', 'start': 1958.975, 'duration': 4.502}, {'end': 1974.874, 'text': 'if you have excel 365, i highly recommend that you use the xlookup function, but if you do have any other older version of excel, use the vlookup.', 'start': 1964.867, 'duration': 10.007}, {'end': 1981.118, 'text': "i'm going to build the formulas with both of them so that you're familiar with it, but later on we'll just stick to one of them,", 'start': 1974.874, 'duration': 6.244}, {'end': 1984.64, 'text': "because it's that way, it is simple and the video will move faster.", 'start': 1981.118, 'duration': 3.522}], 'summary': 'Comparison of xlookup and vlookup for excel functions, based on version compatibility and recommendation for excel 365 users.', 'duration': 28.006, 'max_score': 1956.634, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1956634.jpg'}, {'end': 2061.387, 'src': 'heatmap', 'start': 1999.102, 'weight': 0.823, 'content': [{'end': 2005.405, 'text': 'So we could use XLOOKUP like this, XLOOKUP, lookup bar phony, so the L17.', 'start': 1999.102, 'duration': 6.303}, {'end': 2007.987, 'text': 'And then you need to specify the lookup array.', 'start': 2006.006, 'duration': 1.981}, {'end': 2012.99, 'text': "Where is this name? It's in the staff table name column.", 'start': 2008.607, 'duration': 4.383}, {'end': 2014.957, 'text': 'then what do we want?', 'start': 2014.096, 'duration': 0.861}, {'end': 2017.738, 'text': 'we want their entire record.', 'start': 2014.957, 'duration': 2.781}, {'end': 2021.02, 'text': 'what is their gender, age rating, everything.', 'start': 2017.738, 'duration': 3.282}, {'end': 2033.126, 'text': "so i'm going to say staff table, and then we can say from gender, colon tenure.", 'start': 2021.02, 'duration': 12.106}, {'end': 2042.551, 'text': "so we want all of these columns for that person and then, when you close, you're going to get their entire record here for bar phony.", 'start': 2033.126, 'duration': 9.425}, {'end': 2048.656, 'text': 'this is their record Because there are multiple values returned by XLOOKUP.', 'start': 2042.551, 'duration': 6.105}, {'end': 2057.123, 'text': "it's gonna spill the values in this direction because the data is also in the horizontal direction, so the XLOOKUP will also go in like this.", 'start': 2048.656, 'duration': 8.467}, {'end': 2061.387, 'text': 'Many times, this format may not work from a presentation perspective.', 'start': 2057.704, 'duration': 3.683}], 'summary': 'Using xlookup to retrieve entire record from staff table.', 'duration': 62.285, 'max_score': 1999.102, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1999102.jpg'}, {'end': 2093.694, 'src': 'embed', 'start': 2063.589, 'weight': 2, 'content': [{'end': 2071.396, 'text': 'So in that case, what we could do is we can say XLOOKUP bar FONI in the staff name column.', 'start': 2063.589, 'duration': 7.807}, {'end': 2074.676, 'text': 'then return array instead of typing it.', 'start': 2072.674, 'duration': 2.002}, {'end': 2076.237, 'text': 'you can also select the column.', 'start': 2074.676, 'duration': 1.561}, {'end': 2081.482, 'text': "so i'm just gonna select like that and then, instead of just doing xlookup alone,", 'start': 2076.237, 'duration': 5.245}, {'end': 2093.694, 'text': 'we can use the transpose function around it so that it will be transposed so the horizontal data becomes vertical and we will get a format like this.', 'start': 2081.482, 'duration': 12.212}], 'summary': 'Using xlookup and transpose function to convert horizontal data to vertical format.', 'duration': 30.105, 'max_score': 2063.589, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2063589.jpg'}, {'end': 2249.075, 'src': 'embed', 'start': 2222.503, 'weight': 1, 'content': [{'end': 2227.585, 'text': 'For our third one, we need to build an information finder version 2.0.', 'start': 2222.503, 'duration': 5.082}, {'end': 2236.869, 'text': 'This is where, if I provide a department name, I would like to see who is working in that department, what are their salaries,', 'start': 2227.585, 'duration': 9.284}, {'end': 2238.89, 'text': 'how old they are and few other details.', 'start': 2236.869, 'duration': 2.021}, {'end': 2242.232, 'text': "We're going to do this in a separate tab.", 'start': 2240.131, 'duration': 2.101}, {'end': 2249.075, 'text': "This way it's easier to work with this without getting confused with so many other details that are on the other page.", 'start': 2242.412, 'duration': 6.663}], 'summary': 'Develop version 2.0 of an information finder to display department staff details in a separate tab.', 'duration': 26.572, 'max_score': 2222.503, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2222503.jpg'}, {'end': 2325.63, 'src': 'embed', 'start': 2301.21, 'weight': 3, 'content': [{'end': 2311.605, 'text': 'What is the headcount of that department? Sounds good? So to build this kind of a thing, we need to use new functions available in Excel 365.', 'start': 2301.21, 'duration': 10.395}, {'end': 2317.107, 'text': "So this part of the video will be very hard to follow if you're using an old version of Excel.", 'start': 2311.605, 'duration': 5.502}, {'end': 2322.909, 'text': 'So here we can use the filter function, filter staff table.', 'start': 2318.187, 'duration': 4.722}, {'end': 2325.63, 'text': "Initially, we'll just get everything.", 'start': 2324.21, 'duration': 1.42}], 'summary': 'Using new excel 365 functions to filter staff table for headcount analysis.', 'duration': 24.42, 'max_score': 2301.21, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2301210.jpg'}, {'end': 2448.75, 'src': 'heatmap', 'start': 2329.412, 'weight': 0.905, 'content': [{'end': 2338.563, 'text': 'So staff table include by saying staff table department is equal to C5.', 'start': 2329.412, 'duration': 9.151}, {'end': 2344.427, 'text': 'If I say this, then this is going to take the staff table,', 'start': 2340.864, 'duration': 3.563}, {'end': 2350.671, 'text': 'filter it down to just the sales department and give me a list of all the employees in the sales department.', 'start': 2344.427, 'duration': 6.244}, {'end': 2353.953, 'text': 'You can see that everybody here is salesperson only.', 'start': 2350.931, 'duration': 3.022}, {'end': 2357.284, 'text': 'so this is the data.', 'start': 2355.663, 'duration': 1.621}, {'end': 2359.344, 'text': "like i said, we don't want all of this.", 'start': 2357.284, 'duration': 2.06}, {'end': 2364.867, 'text': 'what we want is name, salary and then the rating columns.', 'start': 2359.344, 'duration': 5.523}, {'end': 2373.41, 'text': 'so if i see put a number here, then essentially we want column 1, column 7 and column 4 of this data.', 'start': 2364.867, 'duration': 8.543}, {'end': 2381.181, 'text': 'So, instead of just filtering, what we want is we want to choose the columns column one, seven and four.', 'start': 2374.318, 'duration': 6.863}, {'end': 2385.323, 'text': 'This is where a new function called choose columns comes into picture.', 'start': 2381.762, 'duration': 3.561}, {'end': 2393.367, 'text': 'Choose columns from the filter result, and then we want column one, seven, and four in that order.', 'start': 2385.924, 'duration': 7.443}, {'end': 2400.491, 'text': 'So this is gonna just take out that entire thing, shrink it down to these three columns, rearrange them in this order.', 'start': 2394.688, 'duration': 5.803}, {'end': 2410.215, 'text': 'Once this data is there, then what we want is we want to sort this by the salary column so that the highest salaries goes to the top.', 'start': 2401.65, 'duration': 8.565}, {'end': 2415.852, 'text': 'For this, we can then send this output to the sort function.', 'start': 2411.169, 'duration': 4.683}, {'end': 2420.655, 'text': 'So sort this entire thing by the second column.', 'start': 2416.393, 'duration': 4.262}, {'end': 2424.538, 'text': 'Second column has my salary in the descending order.', 'start': 2420.976, 'duration': 3.562}, {'end': 2426.479, 'text': 'Descending order stands for minus one here.', 'start': 2424.598, 'duration': 1.881}, {'end': 2431.102, 'text': 'So like that, and then we will get the information.', 'start': 2427.12, 'duration': 3.982}, {'end': 2437.567, 'text': 'So these are our salespeople, names, salaries, and their rating.', 'start': 2431.543, 'duration': 6.024}, {'end': 2445.809, 'text': 'Quite a few people are making more than 100, 000 there, but we also have some people that are at 30, 000, 40, 000.', 'start': 2438.547, 'duration': 7.262}, {'end': 2448.75, 'text': 'Next up, we would like to count how many such people are there.', 'start': 2445.809, 'duration': 2.941}], 'summary': 'Filter and organize staff data, sort by salary, with some salaries exceeding 100,000. plan to count the number of staff meeting criteria.', 'duration': 119.338, 'max_score': 2329.412, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2329412.jpg'}, {'end': 2574.843, 'src': 'embed', 'start': 2549.849, 'weight': 4, 'content': [{'end': 2555.631, 'text': "since i already have an existing thing set up, i'm going to select male versus female and put the pivot table here.", 'start': 2549.849, 'duration': 5.782}, {'end': 2563.994, 'text': 'One additional thing that you may want to do and I personally like to do this every time is add this data to the data model option.', 'start': 2556.667, 'duration': 7.327}, {'end': 2573.282, 'text': 'What this does is it enables some special features in your pivot tables so that you could answer some very interesting questions quickly as well.', 'start': 2564.794, 'duration': 8.488}, {'end': 2574.843, 'text': "Let's click OK.", 'start': 2574.103, 'duration': 0.74}], 'summary': 'Setting up pivot table for gender analysis with data model option for enhanced features.', 'duration': 24.994, 'max_score': 2549.849, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2549849.jpg'}, {'end': 2839.339, 'src': 'heatmap', 'start': 2776.963, 'weight': 0.721, 'content': [{'end': 2780.563, 'text': "let's apply some quick formatting to format the values in the pivot table.", 'start': 2776.963, 'duration': 3.6}, {'end': 2789.826, 'text': 'you can right click on the number and then use the number format option and select the appropriate format that you want.', 'start': 2780.563, 'duration': 9.263}, {'end': 2802.335, 'text': "click ok, If you don't want to see the grand total, you can also go to the pivot table, design ribbon and then from here,", 'start': 2789.826, 'duration': 12.509}, {'end': 2804.677, 'text': 'grand total and you can turn that off.', 'start': 2802.335, 'duration': 2.342}, {'end': 2811.581, 'text': 'As I mentioned, this is the information for both countries.', 'start': 2808.819, 'duration': 2.762}, {'end': 2815.164, 'text': "Let's say I want this but only for New Zealand.", 'start': 2812.162, 'duration': 3.002}, {'end': 2822.169, 'text': 'What we can also do is right click on the country option here and then add it as a slicer.', 'start': 2815.965, 'duration': 6.204}, {'end': 2835.056, 'text': 'This will give you a slicer which will give you two buttons for one for each country and these are interactive.', 'start': 2827.492, 'duration': 7.564}, {'end': 2839.339, 'text': "So if I tap on New Zealand, I'll see the numbers for New Zealand alone.", 'start': 2835.076, 'duration': 4.263}], 'summary': 'Format pivot table values, remove grand total, use slicer for country selection.', 'duration': 62.376, 'max_score': 2776.963, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2776963.jpg'}], 'start': 1876.932, 'title': 'Building an information retriever and data analysis in excel', 'summary': 'Covers building an information retriever in excel using lookup functions to quickly retrieve employee details, highlighting xlookup and vlookup functions, and demonstrating data analysis using functions like filter, sort, and countifs, followed by introducing pivot tables.', 'chapters': [{'end': 1932.234, 'start': 1876.932, 'title': 'Building an information retriever', 'summary': "Discusses building an information retriever using lookup functions in excel to quickly retrieve an employee's details, such as their workplace, joining date, and salary, by typing the employee's name and using the filter function.", 'duration': 55.302, 'highlights': ["Using lookup functions in Excel to quickly retrieve employee details, such as workplace, joining date, and salary, by typing the employee's name.", 'Describing the process of using the filter function to see all the details about a specific employee after typing their name.']}, {'end': 2300.91, 'start': 1932.234, 'title': 'Excel lookup functions tutorial', 'summary': 'Highlights the use of xlookup and vlookup functions in excel, recommending xlookup for excel 365 users, demonstrating how to use xlookup to retrieve an entire record and transpose data, and emphasizing the simplicity and dynamic nature of xlookup over vlookup. it also outlines the creation of an information finder version 2.0 to retrieve and sort employee information based on department, including sorting by salary and displaying the total number of employees.', 'duration': 368.676, 'highlights': ['The chapter emphasizes the use of XLOOKUP and VLOOKUP functions in Excel, recommending XLOOKUP for Excel 365 users and demonstrating its usage to retrieve an entire record for a specific entry, showcasing its dynamic nature and simplicity over VLOOKUP.', 'The tutorial illustrates the process of transposing data using XLOOKUP to convert horizontal data into a vertical format, enabling dynamic and user-friendly data presentation.', 'The creation of an information finder version 2.0 is detailed, outlining the objective of retrieving and sorting employee information based on department, including sorting by salary in descending order and displaying the total number of employees.']}, {'end': 2852.854, 'start': 2301.21, 'title': 'Excel data analysis', 'summary': 'Demonstrates data analysis using excel 365, showcasing functions like filter, choose columns, sort, and countifs to manipulate and analyze staff data, followed by introducing a pivot table for detailed analysis of gender, age, salary, and tenure.', 'duration': 551.644, 'highlights': ['The chapter demonstrates various functions like filter, choose columns, sort, and COUNTIFS to manipulate and analyze staff data, providing insights like the number of people in a department, their salaries, and the count of different gender groups. The functions demonstrated include filter, choose columns, sort, and COUNTIFS to manipulate and analyze staff data, providing insights like the number of people in a department, their salaries, and the count of different gender groups.', 'The explanation of using the pivot table for detailed analysis of gender, age, salary, and tenure, along with the interactive feature of slicers for country-wise analysis, adds depth to the data analysis process. The pivot table is used for detailed analysis of gender, age, salary, and tenure, along with the interactive feature of slicers for country-wise analysis, adding depth to the data analysis process.']}], 'duration': 975.922, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI1876932.jpg', 'highlights': ['Demonstrating the process of using the filter function to see all the details about a specific employee after typing their name.', 'The creation of an information finder version 2.0 is detailed, outlining the objective of retrieving and sorting employee information based on department, including sorting by salary in descending order and displaying the total number of employees.', 'The tutorial illustrates the process of transposing data using XLOOKUP to convert horizontal data into a vertical format, enabling dynamic and user-friendly data presentation.', 'The chapter demonstrates various functions like filter, choose columns, sort, and COUNTIFS to manipulate and analyze staff data, providing insights like the number of people in a department, their salaries, and the count of different gender groups.', 'The explanation of using the pivot table for detailed analysis of gender, age, salary, and tenure, along with the interactive feature of slicers for country-wise analysis, adds depth to the data analysis process.', 'The chapter emphasizes the use of XLOOKUP and VLOOKUP functions in Excel, recommending XLOOKUP for Excel 365 users and demonstrating its usage to retrieve an entire record for a specific entry, showcasing its dynamic nature and simplicity over VLOOKUP.', "Using lookup functions in Excel to quickly retrieve employee details, such as workplace, joining date, and salary, by typing the employee's name."]}, {'end': 3154.748, 'segs': [{'end': 2953.497, 'src': 'embed', 'start': 2852.854, 'weight': 0, 'content': [{'end': 2858.519, 'text': 'you can clear the filter and then select both of them to see how it is happening.', 'start': 2852.854, 'duration': 5.665}, {'end': 2868.787, 'text': 'together with slicers, pivot tables become so much more easy to work with and answer any kind of question without writing complex formulas.', 'start': 2858.519, 'duration': 10.268}, {'end': 2877.614, 'text': 'imagine to get these kind of numbers earlier we had to write some not so complex but still complex formulas.', 'start': 2868.787, 'duration': 8.827}, {'end': 2883.406, 'text': 'So this is why I do like pivot tables to do some of the ad hoc analysis.', 'start': 2878.601, 'duration': 4.805}, {'end': 2893.415, 'text': 'That said, I like the flexibility of Excel functions because that lets me build more complex answers or control things in a better way as well.', 'start': 2884.547, 'duration': 8.868}, {'end': 2900.295, 'text': "Now let's go back to our data and our last thing is to do a bonus calculation.", 'start': 2894.673, 'duration': 5.622}, {'end': 2903.076, 'text': 'The bonus calculation is really simple.', 'start': 2901.016, 'duration': 2.06}, {'end': 2907.578, 'text': 'We would like to give 3% bonus on salary.', 'start': 2903.797, 'duration': 3.781}, {'end': 2916.502, 'text': 'So whatever is your salary, your bonus would be 3% of that, assuming you have been with the organization for at least two years.', 'start': 2907.958, 'duration': 8.544}, {'end': 2920.163, 'text': 'If not, your bonus would be 2%.', 'start': 2917.422, 'duration': 2.741}, {'end': 2921.384, 'text': "so it's a simple rule.", 'start': 2920.163, 'duration': 1.221}, {'end': 2925.346, 'text': 'at awesome chocolates have you been with us for longer than two years.', 'start': 2921.384, 'duration': 3.962}, {'end': 2927.047, 'text': 'then you get three percent.', 'start': 2925.346, 'duration': 1.701}, {'end': 2929.368, 'text': 'else you get two percent.', 'start': 2927.047, 'duration': 2.321}, {'end': 2932.57, 'text': 'we can calculate the bonus as a new column here.', 'start': 2929.368, 'duration': 3.202}, {'end': 2934.17, 'text': 'because everything is touching each other.', 'start': 2932.57, 'duration': 1.6}, {'end': 2942.635, 'text': "i'm going to introduce some columns here so that there is a little bit more room for our table and we can calculate the bonus as an extra column.", 'start': 2934.17, 'duration': 8.465}, {'end': 2945.343, 'text': 'now You can do it in two places.', 'start': 2942.635, 'duration': 2.708}, {'end': 2953.497, 'text': "You can add a column to your table or if you're comfortable with Power Query, you can also do this as a step in Power Query too.", 'start': 2945.423, 'duration': 8.074}], 'summary': 'Pivot tables and slicers make analysis easier and bonus calculation is 3% for over 2 years, 2% otherwise.', 'duration': 100.643, 'max_score': 2852.854, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2852854.jpg'}, {'end': 3061.16, 'src': 'heatmap', 'start': 2978.156, 'weight': 2, 'content': [{'end': 2985.476, 'text': 'if my tenure is greater than two, because tenure is already in years, we just say two.', 'start': 2978.156, 'duration': 7.32}, {'end': 2990.718, 'text': 'then it needs to be three percent, else this will be two percent.', 'start': 2985.476, 'duration': 5.242}, {'end': 2996.201, 'text': 'so this, if formula tells me what the percentage is, this is not enough.', 'start': 2990.718, 'duration': 5.483}, {'end': 3004.025, 'text': 'we need to now take that percentage, multiply that with the salary and then that will give me the bonus value.', 'start': 2996.201, 'duration': 7.824}, {'end': 3005.886, 'text': 'if you want, you can round this bonus.', 'start': 3004.025, 'duration': 1.861}, {'end': 3010.842, 'text': "so i'm gonna round it up to the nearest number.", 'start': 3005.886, 'duration': 4.956}, {'end': 3011.542, 'text': 'so round it up.', 'start': 3010.842, 'duration': 0.7}, {'end': 3013.403, 'text': 'zero means no more decimal points.', 'start': 3011.542, 'duration': 1.861}, {'end': 3017.725, 'text': 'just take it up to the next highest number and then that will give you the bonus.', 'start': 3013.403, 'duration': 4.322}, {'end': 3028.67, 'text': "let's apply some currency formatting on this little thing and we will get the bonus values.", 'start': 3017.725, 'duration': 10.945}, {'end': 3034.873, 'text': "let's say as you are looking at this, your boss suddenly comes up and then says oh, you calculated the bonus, good job,", 'start': 3028.67, 'duration': 6.203}, {'end': 3039.18, 'text': 'Can you tell me who are the employees that are getting the highest bonuses?', 'start': 3035.336, 'duration': 3.844}, {'end': 3041.061, 'text': 'I just want to know the top 10 names.', 'start': 3039.28, 'duration': 1.781}, {'end': 3044.124, 'text': 'You can do lots of different things.', 'start': 3042.242, 'duration': 1.882}, {'end': 3049.409, 'text': "You could write a formula, you could do a pivot table and all sorts of things, but I'll show you the quickest way to get there.", 'start': 3044.144, 'duration': 5.265}, {'end': 3056.335, 'text': 'Click on that little arrow, number filters, and then select top 10.', 'start': 3050.69, 'duration': 5.645}, {'end': 3061.16, 'text': 'Just click okay, and you will see the top 10 employees making highest bonus.', 'start': 3056.335, 'duration': 4.825}], 'summary': 'Tenure greater than 2 years gets 3% bonus; otherwise 2%. apply currency formatting to get top 10 employees with highest bonuses.', 'duration': 78.179, 'max_score': 2978.156, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2978156.jpg'}, {'end': 3108.871, 'src': 'embed', 'start': 3080.055, 'weight': 8, 'content': [{'end': 3087.259, 'text': 'So we are going to look at the salary spread to begin with, how the salaries are spread, what the distribution looks like.', 'start': 3080.055, 'duration': 7.204}, {'end': 3090.06, 'text': 'are there some extreme values or not?', 'start': 3087.259, 'duration': 2.801}, {'end': 3093.042, 'text': 'So that is the first topic for our data analysis.', 'start': 3090.241, 'duration': 2.801}, {'end': 3101.347, 'text': 'And then the second question that I would like to answer is is there any relationship between salary and employee ratings?', 'start': 3093.642, 'duration': 7.705}, {'end': 3108.871, 'text': 'For example, are the exceptional people getting paid more or are the poor people getting paid less??', 'start': 3102.028, 'duration': 6.843}], 'summary': 'Analyzing salary distribution and its relationship with employee ratings.', 'duration': 28.816, 'max_score': 3080.055, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3080055.jpg'}, {'end': 3163.975, 'src': 'embed', 'start': 3130.4, 'weight': 9, 'content': [{'end': 3134.126, 'text': 'Can we tell anything about the future based on that information?', 'start': 3130.4, 'duration': 3.726}, {'end': 3135.714, 'text': 'And then, finally,', 'start': 3134.713, 'duration': 1.001}, {'end': 3150.245, 'text': 'I would like to prepare a India versus New Zealand report card so that we could compare both countries and understand various key statistics and business metrics from the HR perspective,', 'start': 3135.714, 'duration': 14.531}, {'end': 3151.766, 'text': 'and you know, make it fun.', 'start': 3150.245, 'duration': 1.521}, {'end': 3154.748, 'text': 'So that is the scope of this part of the video.', 'start': 3152.266, 'duration': 2.482}, {'end': 3163.975, 'text': 'Again, we are going to introduce some very fun and useful chart options in Excel as well as conditional formatting and data validation.', 'start': 3155.609, 'duration': 8.366}], 'summary': 'Prepare india vs. new zealand hr report card with fun excel charts and data validation.', 'duration': 33.575, 'max_score': 3130.4, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3130400.jpg'}], 'start': 2852.854, 'title': 'Excel functions, pivot tables, and bonus calculations', 'summary': 'Covers the efficiency of pivot tables and excel functions in ad hoc analysis, along with a bonus calculation of 3% for employees with over two years of tenure and 2% for others. it also details the process of calculating bonuses based on tenure using if function, multiplying it with the salary, rounding it up, and applying currency formatting. additionally, it demonstrates data analysis and visualization techniques, including identifying top employees with highest bonuses, salary distribution, relationship between salary and employee ratings, company growth trajectory, and hr statistics comparison between india and new zealand.', 'chapters': [{'end': 2953.497, 'start': 2852.854, 'title': 'Using pivot tables and excel functions for data analysis', 'summary': 'Discusses the efficiency of pivot tables and excel functions in ad hoc analysis, highlighting the ease of working with slicers, the ability to answer complex questions without complex formulas, and a bonus calculation of 3% for employees with over two years of tenure and 2% for others.', 'duration': 100.643, 'highlights': ['Pivot tables, in combination with slicers, provide a simplified way to analyze data and answer complex questions without the need for intricate formulas, enhancing the ease of ad hoc analysis.', 'The bonus calculation entails a 3% bonus on salary for employees with a tenure of at least two years and a 2% bonus for those with less tenure, offering a simple yet effective rule for bonus allocation.', 'The flexibility of Excel functions is valued for enabling the construction of more complex answers and better control over data analysis compared to pivot tables.', 'The option to calculate the bonus as a new column in the data table is presented as a practical method, providing flexibility and ease in conducting the bonus calculation process.']}, {'end': 3034.873, 'start': 2954.67, 'title': 'Excel bonus calculation', 'summary': 'Covers the process of calculating bonuses in excel based on tenure, using if function to determine the percentage, multiplying it with the salary, rounding it up, and applying currency formatting.', 'duration': 80.203, 'highlights': ['The bonus percentage is determined based on tenure, where it is 3% if the tenure is more than two years, else it is 2%.', 'The bonus value is calculated by multiplying the determined percentage with the salary and then rounding it up to the nearest whole number.', 'Applying currency formatting on the calculated bonus provides the final bonus values.']}, {'end': 3154.748, 'start': 3035.336, 'title': 'Excel data analysis and visualization', 'summary': 'Demonstrates how to quickly identify the top 10 employees with the highest bonuses in excel and then goes on to discuss data analysis topics including salary distribution, relationship between salary and employee ratings, company growth trajectory, and comparison of key statistics between india and new zealand from an hr perspective.', 'duration': 119.412, 'highlights': ["The chapter explains the quickest way to identify the top 10 employees with the highest bonuses in Excel by using the 'number filters' and 'select top 10' option, providing a time-saving method for data analysis.", "It covers various data analysis topics such as salary distribution, analyzing the relationship between salary and employee ratings, and evaluating the company's growth trajectory from its first employee to the current headcount of 183 employees.", 'Additionally, it discusses the preparation of an India versus New Zealand report card to compare key statistics and business metrics from an HR perspective, aiming to make the process engaging and insightful.']}], 'duration': 301.894, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI2852854.jpg', 'highlights': ['Pivot tables, in combination with slicers, provide a simplified way to analyze data and answer complex questions without the need for intricate formulas, enhancing the ease of ad hoc analysis.', 'The bonus calculation entails a 3% bonus on salary for employees with a tenure of at least two years and a 2% bonus for those with less tenure, offering a simple yet effective rule for bonus allocation.', "The chapter explains the quickest way to identify the top 10 employees with the highest bonuses in Excel by using the 'number filters' and 'select top 10' option, providing a time-saving method for data analysis.", 'The flexibility of Excel functions is valued for enabling the construction of more complex answers and better control over data analysis compared to pivot tables.', 'The bonus percentage is determined based on tenure, where it is 3% if the tenure is more than two years, else it is 2%.', 'The option to calculate the bonus as a new column in the data table is presented as a practical method, providing flexibility and ease in conducting the bonus calculation process.', 'The bonus value is calculated by multiplying the determined percentage with the salary and then rounding it up to the nearest whole number.', 'Applying currency formatting on the calculated bonus provides the final bonus values.', "It covers various data analysis topics such as salary distribution, analyzing the relationship between salary and employee ratings, and evaluating the company's growth trajectory from its first employee to the current headcount of 183 employees.", 'Additionally, it discusses the preparation of an India versus New Zealand report card to compare key statistics and business metrics from an HR perspective, aiming to make the process engaging and insightful.']}, {'end': 3941.191, 'segs': [{'end': 3184.472, 'src': 'embed', 'start': 3155.609, 'weight': 0, 'content': [{'end': 3163.975, 'text': 'Again, we are going to introduce some very fun and useful chart options in Excel as well as conditional formatting and data validation.', 'start': 3155.609, 'duration': 8.366}, {'end': 3171.041, 'text': "let's go so.", 'start': 3164.415, 'duration': 6.626}, {'end': 3180.048, 'text': 'our first topic in data analysis section is understand the salary spread and you could do this in few different ways.', 'start': 3171.041, 'duration': 9.007}, {'end': 3184.472, 'text': 'so if you want to do it quick and dirty, here is how i would do it.', 'start': 3180.048, 'duration': 4.424}], 'summary': 'Introducing fun and useful chart options in excel, along with conditional formatting and data validation for data analysis.', 'duration': 28.863, 'max_score': 3155.609, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3155609.jpg'}, {'end': 3308.327, 'src': 'embed', 'start': 3281.443, 'weight': 1, 'content': [{'end': 3291.289, 'text': 'these charts are newly introduced into excel 2016 and 19, so if you are using a really old version of excel, you will not find them in your excel.', 'start': 3281.443, 'duration': 9.846}, {'end': 3302.401, 'text': "so from insert you're looking for this histogram or statistic chart option, and from here you could try either histogram or box and whisker plot.", 'start': 3291.989, 'duration': 10.412}, {'end': 3308.327, 'text': "i'm going to try the histogram option first and this will give me a histogram of our salary spread.", 'start': 3302.401, 'duration': 5.926}], 'summary': 'Excel 2016 and 2019 introduce new charts like histogram and box plot.', 'duration': 26.884, 'max_score': 3281.443, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3281443.jpg'}, {'end': 3396.965, 'src': 'embed', 'start': 3364.427, 'weight': 2, 'content': [{'end': 3369.049, 'text': 'And then most of the majority of our employees are between 40 to 80, 000.', 'start': 3364.427, 'duration': 4.622}, {'end': 3375.133, 'text': 'And then between 80 and 90, we surprisingly have very few people, but then it picks up again.', 'start': 3369.049, 'duration': 6.084}, {'end': 3381.857, 'text': 'And we do have a significant number of people between 110 to 120.', 'start': 3375.293, 'duration': 6.564}, {'end': 3385.199, 'text': 'So this kind of tells a story of what is happening.', 'start': 3381.857, 'duration': 3.342}, {'end': 3389.201, 'text': 'And with about just 180 people there is not much.', 'start': 3385.239, 'duration': 3.962}, {'end': 3396.965, 'text': 'but if you apply this technique over a large set of data, then you can quickly get a sense of how the values are spread.', 'start': 3389.201, 'duration': 7.764}], 'summary': 'Most employees earn between 40,000 to 120,000, with a significant number in the 110-120 range.', 'duration': 32.538, 'max_score': 3364.427, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3364427.jpg'}, {'end': 3503.815, 'src': 'embed', 'start': 3476.098, 'weight': 3, 'content': [{'end': 3488.133, 'text': 'so essentially, this kind of tells me that middle class or the mid half of our employees, their salaries go from maybe around 55, 50 000,', 'start': 3476.098, 'duration': 12.035}, {'end': 3493.13, 'text': '55 000 all the way up to 100 000, and then we do have some people here, some people there.', 'start': 3488.133, 'duration': 4.997}, {'end': 3495.831, 'text': 'there seem to be no other extreme values.', 'start': 3493.73, 'duration': 2.101}, {'end': 3503.815, 'text': "but i'm just going to select this go here and yeah, make sure that show outlier points is checked.", 'start': 3495.831, 'duration': 7.984}], 'summary': 'The middle half of employees earn $55,000 to $100,000, with no extreme values.', 'duration': 27.717, 'max_score': 3476.098, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3476098.jpg'}, {'end': 3731.709, 'src': 'embed', 'start': 3707.12, 'weight': 4, 'content': [{'end': 3715.926, 'text': 'our exceptional staff are paid really high, so they have 92 000, but everybody else seem to be around the same ballpark.', 'start': 3707.12, 'duration': 8.806}, {'end': 3720.87, 'text': 'one way to read this better is if we arrange these ratings in a better order.', 'start': 3715.926, 'duration': 4.944}, {'end': 3724.472, 'text': 'right now they are in the alphabetical order, and that is not a good way to do it.', 'start': 3720.87, 'duration': 3.602}, {'end': 3731.709, 'text': 'so To fix this problem, what we can do is select the label that is exceptional, and we want this to be up top.', 'start': 3724.472, 'duration': 7.237}], 'summary': 'Staff have high pay at 92,000, need to re-arrange ratings to prioritize exceptional label.', 'duration': 24.589, 'max_score': 3707.12, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3707120.jpg'}], 'start': 3155.609, 'title': 'Excel data analysis and salary distribution', 'summary': 'Introduces excel data analysis options such as conditional formatting and statistical charts to visualize salary spreads. it details the analysis of salary distribution, showing most employees earning between 40,000 to 80,000. additionally, it explores the relationship between salary and employee rating, indicating that exceptional staff are paid significantly higher.', 'chapters': [{'end': 3332.362, 'start': 3155.609, 'title': 'Excel data analysis options', 'summary': 'Introduces various excel data analysis options including conditional formatting and statistical charts to visualize and understand salary spreads, demonstrating the use of color scales, sort orders, and statistical charts in excel 2016 and 19.', 'duration': 176.753, 'highlights': ['The chapter introduces conditional formatting in Excel to visualize salary spreads using color scales and sort orders to quickly understand the salary distribution. Demonstrates the use of color scales to represent higher and lower salaries, and applying a sort order to visualize the spread of salaries.', 'The chapter explains the use of statistical charts like histograms and box and whisker plots in Excel 2016 and 19 to analyze salary spreads and provides guidance on customizing the bin width for better analysis. Introduces the use of statistical charts in Excel 2016 and 19 to create histograms and box and whisker plots for analyzing salary spreads and provides instructions on customizing the bin width for more meaningful analysis.']}, {'end': 3941.191, 'start': 3332.362, 'title': 'Salary distribution analysis', 'summary': "Details the analysis of salary distribution, showing majority employees earning between 40,000 to 80,000, and a box plot revealing the mid half of employees' salaries ranging from around 55,000 to 100,000. additionally, it explores the relationship between salary and employee rating through a pivot table, indicating that exceptional staff are paid significantly higher.", 'duration': 608.829, 'highlights': ['Majority employees earn between 40,000 to 80,000. The analysis reveals that most of the employees fall within the salary range of 40,000 to 80,000.', "Box plot shows mid half of employees' salaries ranging from around 55,000 to 100,000. The box plot diagram demonstrates that the middle chunk of employees' salaries spans from approximately 55,000 to 100,000.", 'Exceptional staff are paid significantly higher with an average of 92,000. The pivot table indicates that exceptional staff receive an average salary of 92,000, significantly higher than other rating groups.']}], 'duration': 785.582, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3155609.jpg', 'highlights': ['The chapter introduces conditional formatting in Excel to visualize salary spreads using color scales and sort orders to quickly understand the salary distribution.', 'The chapter explains the use of statistical charts like histograms and box and whisker plots in Excel 2016 and 19 to analyze salary spreads and provides guidance on customizing the bin width for better analysis.', 'Majority employees earn between 40,000 to 80,000.', "Box plot shows mid half of employees' salaries ranging from around 55,000 to 100,000.", 'Exceptional staff are paid significantly higher with an average of 92,000.']}, {'end': 4689.74, 'segs': [{'end': 4043.106, 'src': 'embed', 'start': 4017.887, 'weight': 0, 'content': [{'end': 4022.95, 'text': 'So earlier when we were looking at the average, the average is only 76, 799.', 'start': 4017.887, 'duration': 5.063}, {'end': 4030.337, 'text': 'And we thought on average, an employee who rated average has lower salary.', 'start': 4022.951, 'duration': 7.386}, {'end': 4037.082, 'text': 'But if you look at the actual spread here, you can see that somebody rated average, that is, three.', 'start': 4030.757, 'duration': 6.325}, {'end': 4043.106, 'text': 'their salary could be anywhere from 19, 000 all the way up to 148, 000..', 'start': 4037.082, 'duration': 6.024}], 'summary': 'Average employee salary is $76,799, with a wide range from $19,000 to $148,000.', 'duration': 25.219, 'max_score': 4017.887, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI4017887.jpg'}, {'end': 4089.896, 'src': 'embed', 'start': 4064.885, 'weight': 2, 'content': [{'end': 4070.47, 'text': "because maybe there's like two people here, two people there, and that's why the average is coming up to be ninety two thousand,", 'start': 4064.885, 'duration': 5.585}, {'end': 4074.391, 'text': 'Whereas this spread shows a little bit more wild thing.', 'start': 4071.13, 'duration': 3.261}, {'end': 4083.454, 'text': 'So using a scatter plot is a really fun and powerful way to quickly see how your data is and understand.', 'start': 4075.231, 'duration': 8.223}, {'end': 4086.875, 'text': 'The only way we could do this is if both access are number.', 'start': 4084.074, 'duration': 2.801}, {'end': 4089.896, 'text': 'Our salary is already a number, but the rating was text.', 'start': 4087.215, 'duration': 2.681}], 'summary': 'Scatter plot reveals salary average of 92,000, with some variation.', 'duration': 25.011, 'max_score': 4064.885, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI4064885.jpg'}, {'end': 4265.573, 'src': 'embed', 'start': 4206.655, 'weight': 3, 'content': [{'end': 4208.858, 'text': 'I just want to know how many names are there?', 'start': 4206.655, 'duration': 2.203}, {'end': 4211.08, 'text': "So I'll take the name and put that there.", 'start': 4208.878, 'duration': 2.202}, {'end': 4214.683, 'text': 'So total is 183.', 'start': 4211.76, 'duration': 2.923}, {'end': 4218.567, 'text': 'Uh, in 2020, when we started our company, we brought in 37 people.', 'start': 4214.683, 'duration': 3.884}, {'end': 4222.03, 'text': 'So really small 2021 was our big year.', 'start': 4218.647, 'duration': 3.383}, {'end': 4227.655, 'text': 'We hired 82 people, then 62 more in 2023, we just getting started.', 'start': 4222.09, 'duration': 5.565}, {'end': 4230.358, 'text': 'So we brought in maybe two people so far.', 'start': 4227.755, 'duration': 2.603}, {'end': 4239.859, 'text': 'At this point, you can go and insert a chart like a line chart that shows you that trend.', 'start': 4232.294, 'duration': 7.565}, {'end': 4250.687, 'text': 'You can see what is going on.', 'start': 4249.506, 'duration': 1.181}, {'end': 4253.228, 'text': 'This chart is linked to the pivot table.', 'start': 4251.227, 'duration': 2.001}, {'end': 4258.652, 'text': 'If your pivot table expands, it will show you the same expansion in the visual.', 'start': 4253.349, 'duration': 5.303}, {'end': 4261.114, 'text': "I'm just going to expand everything out.", 'start': 4258.672, 'duration': 2.442}, {'end': 4265.573, 'text': 'So this is how our company has been growing.', 'start': 4262.711, 'duration': 2.862}], 'summary': 'Company hired 183 total employees, with 82 in 2021 and 62 in 2023.', 'duration': 58.918, 'max_score': 4206.655, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI4206655.jpg'}, {'end': 4322.097, 'src': 'embed', 'start': 4290.363, 'weight': 6, 'content': [{'end': 4300.334, 'text': 'So a good way to visualize what we want is if we can get cumulative totals that is running total of these numbers over the entire period of time.', 'start': 4290.363, 'duration': 9.971}, {'end': 4306.999, 'text': 'So to do this, you could kind of build some formulas or do complex things,', 'start': 4301.295, 'duration': 5.704}, {'end': 4314.604, 'text': 'or you can right click on the number here and then say show value as and then running total option here.', 'start': 4306.999, 'duration': 7.605}, {'end': 4322.097, 'text': 'If I do a running total,', 'start': 4320.876, 'duration': 1.221}], 'summary': 'Visualize cumulative totals by using running total option.', 'duration': 31.734, 'max_score': 4290.363, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI4290363.jpg'}], 'start': 3942.032, 'title': 'Visualizing data and company growth', 'summary': 'Demonstrates visualizing salary versus rating data using a scatter plot in excel and discusses analyzing company growth trends, including the hiring trends and the visualization of cumulative totals, with 82 people hired in the year 2021 being the most significant.', 'chapters': [{'end': 4206.655, 'start': 3942.032, 'title': 'Visualizing salary and rating data', 'summary': 'Demonstrates how to visualize salary versus rating data using a scatter plot in excel, revealing wide salary ranges for average ratings and a more concentrated range for exceptional ratings, prompting further data investigation and also discusses analyzing company growth trends using pivot tables.', 'duration': 264.623, 'highlights': ['The scatter plot reveals a wide salary range for average ratings, with salaries ranging from 19,000 to 148,000, prompting further data investigation. Wide salary range for average ratings, salaries ranging from 19,000 to 148,000.', 'The scatter plot shows a more concentrated salary range for exceptional ratings, with salaries mainly at 109,000 and 75,000, resulting in an average of 92,000. Concentrated salary range for exceptional ratings, average salary of 92,000.', 'The discussion emphasizes the importance of visualizing data using scatter plots to quickly understand data patterns and potential anomalies. Importance of visualizing data using scatter plots to understand data patterns and anomalies.', 'The chapter also discusses using pivot tables to analyze company growth trends by grouping the data into year and month levels for a more comprehensive trend analysis. Using pivot tables to analyze company growth trends by grouping data into year and month levels.']}, {'end': 4689.74, 'start': 4206.655, 'title': 'Company growth and hiring trends', 'summary': 'Discusses the growth of the company, including hiring trends and the visualization of cumulative totals, with 82 people hired in the year 2021 being the most significant, and the use of formulas and functions in excel to calculate running total of employees.', 'duration': 483.085, 'highlights': ['The company experienced significant growth in 2021, hiring 82 people, followed by 62 more in 2023. In 2021, the company brought in 82 new employees, indicating substantial growth, followed by the addition of 62 more employees in 2023.', "Discussion on visualizing cumulative totals and trends using a line chart linked to a pivot table. The chapter discusses the utilization of a line chart linked to a pivot table for visualizing cumulative totals and trends in the company's growth.", 'Explanation of using formulas and functions in Excel to calculate running total of employees, emphasizing the dynamic nature of the formula. The transcript explains the use of formulas and functions in Excel to calculate the running total of employees, highlighting the dynamic nature of the formula, ensuring adaptability with changing data.']}], 'duration': 747.708, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI3942032.jpg', 'highlights': ['Wide salary range for average ratings, salaries ranging from 19,000 to 148,000.', 'Concentrated salary range for exceptional ratings, average salary of 92,000.', 'Importance of visualizing data using scatter plots to understand data patterns and anomalies.', 'Using pivot tables to analyze company growth trends by grouping data into year and month levels.', 'In 2021, the company brought in 82 new employees, indicating substantial growth, followed by the addition of 62 more employees in 2023.', "The chapter discusses the utilization of a line chart linked to a pivot table for visualizing cumulative totals and trends in the company's growth.", 'The transcript explains the use of formulas and functions in Excel to calculate the running total of employees, highlighting the dynamic nature of the formula, ensuring adaptability with changing data.']}, {'end': 5550.619, 'segs': [{'end': 4739.286, 'src': 'embed', 'start': 4690.241, 'weight': 0, 'content': [{'end': 4693.027, 'text': "If we didn't hire anybody in a month, that will be simply zero.", 'start': 4690.241, 'duration': 2.786}, {'end': 4701.584, 'text': "So now that all the individual numbers are there, if I add all of these up, we'll get 183 as you can see in the status bar here.", 'start': 4694.54, 'duration': 7.044}, {'end': 4711.75, 'text': "So the running total would be just sum of R3 to R3, but we'll make the first part of the sum absolute.", 'start': 4702.485, 'duration': 9.265}, {'end': 4715.872, 'text': 'So it will be $R$3, last part is relative.', 'start': 4711.81, 'duration': 4.062}, {'end': 4720.695, 'text': 'And then when you fill this guy down, it will be a running total.', 'start': 4716.332, 'duration': 4.363}, {'end': 4723.478, 'text': 'so now that all the information is here,', 'start': 4721.297, 'duration': 2.181}, {'end': 4734.384, 'text': 'i can just select my month column and then select the running total column and go to insert line chart and add a line chart.', 'start': 4723.478, 'duration': 10.906}, {'end': 4739.286, 'text': 'that kind of shows us how our company is growing, so you could see what is happening.', 'start': 4734.384, 'duration': 4.902}], 'summary': 'Total of 183 new hires in a month, tracked using running total chart.', 'duration': 49.045, 'max_score': 4690.241, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI4690241.jpg'}, {'end': 4829.663, 'src': 'embed', 'start': 4767.114, 'weight': 1, 'content': [{'end': 4781.519, 'text': 'So maybe we can make a report like this, where it kind of has two parts NZ India on both sides, and here we would like to show similar information.', 'start': 4767.114, 'duration': 14.405}, {'end': 4789.804, 'text': 'so maybe I want to show the head count, for example 86 and then female ratio.', 'start': 4781.519, 'duration': 8.285}, {'end': 4798.65, 'text': "let's just say 48 percent and then average salary 75k And then similar kind of information here.", 'start': 4789.804, 'duration': 8.846}, {'end': 4805.779, 'text': 'And then I want to show department wise head counts as a graph on both sides.', 'start': 4798.67, 'duration': 7.109}, {'end': 4808.663, 'text': "And that's it.", 'start': 4807.461, 'duration': 1.202}, {'end': 4811.246, 'text': 'But if you want, you could also add other stuff here.', 'start': 4808.823, 'duration': 2.423}, {'end': 4814.931, 'text': "So let's go ahead and make this in Excel.", 'start': 4811.967, 'duration': 2.964}, {'end': 4822.215, 'text': 'a new sheet.', 'start': 4821.454, 'duration': 0.761}, {'end': 4827.261, 'text': "i'm gonna make a layout for our india versus new zealand scorecard.", 'start': 4822.215, 'duration': 5.046}, {'end': 4829.663, 'text': 'so we need three sets of information.', 'start': 4827.261, 'duration': 2.402}], 'summary': 'Create a report comparing nz and india with 86 head count, 48% female ratio, and 75k average salary, and department-wise head counts as graphs.', 'duration': 62.549, 'max_score': 4767.114, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI4767114.jpg'}, {'end': 5474.891, 'src': 'embed', 'start': 5451.351, 'weight': 5, 'content': [{'end': 5459.197, 'text': 'so if your data changes, for example, either you have more people in new zealand or india and one of these tables change,', 'start': 5451.351, 'duration': 7.846}, {'end': 5461.078, 'text': 'all you have to do is update the data.', 'start': 5459.197, 'duration': 1.881}, {'end': 5468.308, 'text': "so, for example, let's just take this person and then make their salary 65 000.", 'start': 5461.078, 'duration': 7.23}, {'end': 5474.891, 'text': "So we changed one person's salary and I can come here right click and then refresh.", 'start': 5468.308, 'duration': 6.583}], 'summary': 'Updating data in the tables allows for easy changes and refresh.', 'duration': 23.54, 'max_score': 5451.351, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI5451351.jpg'}], 'start': 4690.241, 'title': 'Monthly hiring, company growth, and scorecard creation', 'summary': 'Covers the calculation of monthly hiring total, resulting in 183, and creating a line chart for company growth. additionally, it explains the dynamic scorecard creation for india and new zealand, comparing employee headcount, female ratio, and average salary with excel functions and pivot tables.', 'chapters': [{'end': 4739.286, 'start': 4690.241, 'title': 'Monthly hiring and company growth', 'summary': 'Explains the process of calculating a running total for monthly hiring, resulting in a total of 183, and creating a line chart to visualize company growth.', 'duration': 49.045, 'highlights': ['The running total of monthly hiring amounts to 183 as calculated from individual numbers, providing a clear quantitative result.', 'The process of creating a line chart to visualize company growth is demonstrated, offering a practical application of the calculated data.', 'The method of calculating a running total by summing individual numbers is explained, providing a fundamental understanding of the process.']}, {'end': 5550.619, 'start': 4739.286, 'title': 'Creating india vs new zealand scorecard', 'summary': 'Details the process of creating a dynamic scorecard comparing employee headcount, female ratio, and average salary for india and new zealand, utilizing excel functions and pivot tables, ultimately providing an elegant and presentable comparison.', 'duration': 811.333, 'highlights': ['Creating a dynamic scorecard comparing employee headcount, female ratio, and average salary for India and New Zealand The process involves setting up a layout in Excel to showcase headcount, female percentage, and average salary for both countries, using Excel functions such as COUNTIFS and AVERAGEIFS to calculate the required metrics.', 'Utilizing pivot tables to display department-wise headcounts for India and New Zealand Pivot tables are used to showcase department-level distribution of employees for both countries, with the results being presented as bar charts, allowing for a clear visual comparison of departmental distribution.', 'Emphasizing the dynamic nature of the scorecard and its ability to update with changes in data The presenter highlights the dynamic nature of the scorecard, emphasizing that it automatically updates with changes in data, ensuring that any alterations in employee numbers or salary reflect accurately in the comparison.']}], 'duration': 860.378, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/H6k28jhclwI/pics/H6k28jhclwI4690241.jpg', 'highlights': ['The running total of monthly hiring amounts to 183 as calculated from individual numbers, providing a clear quantitative result.', 'Creating a dynamic scorecard comparing employee headcount, female ratio, and average salary for India and New Zealand The process involves setting up a layout in Excel to showcase headcount, female percentage, and average salary for both countries, using Excel functions such as COUNTIFS and AVERAGEIFS to calculate the required metrics.', 'The process of creating a line chart to visualize company growth is demonstrated, offering a practical application of the calculated data.', 'Utilizing pivot tables to display department-wise headcounts for India and New Zealand Pivot tables are used to showcase department-level distribution of employees for both countries, with the results being presented as bar charts, allowing for a clear visual comparison of departmental distribution.', 'The method of calculating a running total by summing individual numbers is explained, providing a fundamental understanding of the process.', 'Emphasizing the dynamic nature of the scorecard and its ability to update with changes in data The presenter highlights the dynamic nature of the scorecard, emphasizing that it automatically updates with changes in data, ensuring that any alterations in employee numbers or salary reflect accurately in the comparison.']}], 'highlights': ['Excel is the top software for data analysis.', 'The video covers a wide range of Excel features extensively.', 'Power query is explained for data management and cleansing.', 'Teaches simple and complex formulas for data analysis.', 'Demonstrates creating and formatting Excel charts.', 'Enabling total row option prompts further investigation.', 'Excel tables simplify data analysis of different aspects.', 'Emphasizes the importance of maintaining organized data.', 'Introduces Power Query for combining and cleaning data.', 'Shortcuts simplify the process of formatting in Excel.', 'Emphasizes the removal of duplicate values from the data.', 'Highlights the step of replacing missing gender values.', 'Calculates count of employees using count a function.', 'Determines the average salary using the average function.', 'Reveals the female staff ratio at approximately 47%.', 'Example calculation shows employee tenure in years.', 'Audience given a homework problem for salary percentage.', 'Demonstrates using the filter function for employee details.', 'Details the creation of an information finder version 2.0.', 'Illustrates transposing data using XLOOKUP for presentation.', 'Demonstrates various functions for staff data manipulation.', 'Explains using pivot table for detailed data analysis.', 'Emphasizes the use of XLOOKUP and VLOOKUP functions.', 'Pivot tables combined with slicers simplify data analysis.', 'Explains the bonus calculation rule based on tenure.', 'Quickest way to identify top 10 employees with highest bonuses.', 'Flexibility of Excel functions for complex answers.', 'Calculates bonus percentage based on employee tenure.', 'Practical method of calculating bonus as a new column.', 'Bonus value calculated by multiplying percentage with salary.', 'Applies currency formatting on the calculated bonus.', 'Covers various data analysis topics such as salary distribution.', 'Preparation of India versus New Zealand report card.', 'Introduces conditional formatting to visualize salary spreads.', 'Explains the use of statistical charts for salary analysis.', 'Majority employees earn between 40,000 to 80,000.', "Box plot shows mid half of employees' salaries.", 'Exceptional staff are paid significantly higher.', 'Wide salary range for average ratings.', 'Concentrated salary range for exceptional ratings.', 'Importance of visualizing data using scatter plots.', 'Using pivot tables to analyze company growth trends.', 'Company brought in 82 new employees in 2021.', 'Utilizes line chart linked to pivot table for growth visualization.', 'Calculates running total of employees using formulas.', 'Running total of monthly hiring amounts to 183.', 'Creates a dynamic scorecard comparing employee metrics.', 'Demonstrates creating a line chart to visualize company growth.', 'Utilizes pivot tables to display department-wise headcounts.', 'Explains the method of calculating a running total.', 'Emphasizes the dynamic nature of the scorecard.']}