title
Data Analytics using Excel | Excel Tutorial | Data Visualization using Excel | Great Learning

description
📌🔥1000+ Free Courses With Free Certificates: https://www.mygreatlearning.com/academy?ambassador_code=GLYT_DES_Top_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Top_SEP22 🔥Build a career in Data Science & Business Analytics: https://www.mygreatlearning.com/pg-program-data-science-and-business-analytics-course?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP53 Data analytics has been the most sort after domain in recent times. To stay ahead of the competition, it's really important to learn various tools in this domain. Microsoft excel, being the most widely used platform for decades, is a must to learn. The journey of data involves majorly three phases data cleaning, data analysis, and data visualization. This course provides you with an in-depth view of all the phases in detail from scratch so that you can begin your data analytics journey and get your dream job. 00:00:00 Introduction 00:01:30 Agenda 00:02:45 Phases of Data Analytics Project 00:05:45 Data Cleaning 00:10:58 Functions 00:29:02 Lookup Functions 00:37:36 Conditional Formatting 00:46:53 Data Validation 00:55:44 Pivot Tables 01:05:46 Data Visualisation using Excel 01:21:15 Summary 🔥Check Our Free Courses with free certificate: 📌Data Analytics using Excel course: https://glacad.me/3pqmv4y 📌Excel for Beginners: https://www.mygreatlearning.com/academy/learn-for-free/courses/excel-for-beginners?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP100 📌LOOKUP in Excel: https://www.mygreatlearning.com/academy/learn-for-free/courses/vlookup-in-excel?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP101 📌Excel Regression Analysis: https://www.mygreatlearning.com/academy/learn-for-free/courses/regression-analysis-with-excel-hands-on?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP102 📌Excel Tips and Tricks: https://www.mygreatlearning.com/academy/learn-for-free/courses/excel-tips-and-tricks?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP103 📌Excel for Intermediate Level: https://www.mygreatlearning.com/academy/learn-for-free/courses/excel-for-intermediate-level?ambassador_code=GLYT_DES_Middle_SEP22&utm_source=GLYT&utm_campaign=GLYT_DES_Middle_SEP104 ⚡ About Great Learning Academy: Visit Great Learning Academy to get access to 1000+ free courses with free certificate on Data Science, Data Analytics, Digital Marketing, Artificial Intelligence, Big Data, Cloud, Management, Cybersecurity, Software Development, and many more. These are supplemented with free projects, assignments, datasets, quizzes. You can earn a certificate of completion at the end of the course for free. ⚡ About Great Learning: With more than 5.4 Million+ learners in 170+ countries, Great Learning, a part of the BYJU'S group, is a leading global edtech company for professional and higher education offering industry-relevant programs in the blended, classroom, and purely online modes across technology, data and business domains. These programs are developed in collaboration with the top institutions like Stanford Executive Education, MIT Professional Education, The University of Texas at Austin, NUS, IIT Madras, IIT Bombay & more. SOCIAL MEDIA LINKS: 🔹 For more interesting tutorials, don't forget to subscribe to our channel: https://glacad.me/YTsubscribe 🔹 For more updates on courses and tips follow us on: ✅ Telegram: https://t.me/GreatLearningAcademy ✅ Facebook: https://www.facebook.com/GreatLearningOfficial/ ✅ LinkedIn: https://www.linkedin.com/school/great-learning/mycompany/verification/ ✅ Follow our Blog: https://glacad.me/GL_Blog

detail
{'title': 'Data Analytics using Excel | Excel Tutorial | Data Visualization using Excel | Great Learning', 'heatmap': [{'end': 1259.148, 'start': 1146.726, 'weight': 0.869}, {'end': 1398.294, 'start': 1346.989, 'weight': 1}, {'end': 1498.452, 'start': 1446.581, 'weight': 0.739}, {'end': 1997.519, 'start': 1892.607, 'weight': 0.924}, {'end': 2454.763, 'start': 2393.657, 'weight': 0.713}, {'end': 2900.825, 'start': 2844.813, 'weight': 0.949}, {'end': 2995.492, 'start': 2944.907, 'weight': 0.772}], 'summary': 'Emphasizes the increasing demand for data analytics professionals and the massive amounts of data generated every minute, promoting a course on data analytics by great learning. it covers data analytics phases, techniques, excel functions, vlookup, hlookup, conditional formatting, data validation, pivot tables, and data visualization, highlighting the importance of data cleaning and analysis through practical examples.', 'chapters': [{'end': 73.106, 'segs': [{'end': 73.106, 'src': 'embed', 'start': 0.089, 'weight': 0, 'content': [{'end': 4.732, 'text': "hey guys, welcome to today's session on data analytics using excel.", 'start': 0.089, 'duration': 4.643}, {'end': 11.977, 'text': 'now data is everywhere, right from texting someone to buying something online or even posting something on social media.', 'start': 4.732, 'duration': 7.245}, {'end': 16.1, 'text': 'every single thing involves creation of massive amounts of data every minute.', 'start': 11.977, 'duration': 4.123}, {'end': 23.065, 'text': 'every industry, be it healthcare, e-commerce, technology or finance, has data at its center of operations.', 'start': 16.1, 'duration': 6.965}, {'end': 32.369, 'text': 'So now it becomes increasingly important to analyze this data so that we can find some meaningful trends and opportunities for the future.', 'start': 23.803, 'duration': 8.566}, {'end': 37.152, 'text': 'Data analytics in the recent times has been the most sought after domain.', 'start': 32.389, 'duration': 4.763}, {'end': 41.154, 'text': 'To make sure you are ahead of this competition.', 'start': 38.373, 'duration': 2.781}, {'end': 49.34, 'text': 'we here at Great Learning have brought this wonderful course so that you can begin your data analytics journey and land your dream job.', 'start': 41.154, 'duration': 8.186}, {'end': 62.118, 'text': "if you haven't subscribed to our channel yet, we request you to hit the subscribe button and turn on the notification bell.", 'start': 54.932, 'duration': 7.186}, {'end': 70.624, 'text': "this is done to make sure you don't miss out on any of the new updates or video releases of great learning and, of course, if you enjoy this video,", 'start': 62.118, 'duration': 8.506}, {'end': 71.745, 'text': 'show us some love.', 'start': 70.624, 'duration': 1.121}, {'end': 73.106, 'text': 'do like the video.', 'start': 71.745, 'duration': 1.361}], 'summary': 'Data analytics is crucial in every industry, creating massive amounts of data every minute. great learning offers a sought-after course to kickstart your data analytics journey.', 'duration': 73.017, 'max_score': 0.089, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws89.jpg'}], 'start': 0.089, 'title': 'Data analytics using excel', 'summary': 'Emphasizes the increasing demand for data analytics professionals and highlights the massive amounts of data generated every minute, promoting a course on data analytics by great learning.', 'chapters': [{'end': 73.106, 'start': 0.089, 'title': 'Data analytics using excel', 'summary': 'Emphasizes the importance of data analytics in various industries, highlighting the massive amounts of data generated every minute and the increasing demand for data analytics professionals, while promoting a course on data analytics by great learning.', 'duration': 73.017, 'highlights': ["Data is generated every minute from various activities such as texting, online shopping, and social media posting, emphasizing the abundance of data in today's world.", 'Data analytics has become a highly sought-after domain, reflecting the increasing demand for professionals in this field.', 'Great Learning offers a course on data analytics to help individuals start their data analytics journey and pursue their career goals, indicating the opportunity for skill development and job placement.', 'Encourages viewers to subscribe to the channel for updates and new video releases, demonstrating the commitment to providing valuable content and engaging with the audience.']}], 'duration': 73.017, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws89.jpg', 'highlights': ["Data is generated every minute from various activities, highlighting the abundance of data in today's world.", 'Data analytics has become a highly sought-after domain, reflecting the increasing demand for professionals in this field.', 'Great Learning offers a course on data analytics to help individuals start their data analytics journey and pursue their career goals, indicating the opportunity for skill development and job placement.', 'Encourages viewers to subscribe to the channel for updates and new video releases, demonstrating the commitment to providing valuable content and engaging with the audience.']}, {'end': 636.509, 'segs': [{'end': 146.937, 'src': 'embed', 'start': 115.011, 'weight': 3, 'content': [{'end': 120.894, 'text': "Furthermore, we'll be talking about different ways in which we can sort and filter data in Excel.", 'start': 115.011, 'duration': 5.883}, {'end': 127.157, 'text': "Next, we'll learn about two different types of lookup functions, vertical lookup and horizontal lookup functions.", 'start': 121.654, 'duration': 5.503}, {'end': 138.993, 'text': "Then we'll know what is conditional formatting and also how can we make color scales, data bars and also highlight cells.", 'start': 128.257, 'duration': 10.736}, {'end': 146.937, 'text': "then we'll know about data validation, how to create drop-down list and fix length in a cell.", 'start': 138.993, 'duration': 7.944}], 'summary': 'Learn sorting, filtering, lookup functions, and data validation in excel.', 'duration': 31.926, 'max_score': 115.011, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws115011.jpg'}, {'end': 214.436, 'src': 'embed', 'start': 165.341, 'weight': 0, 'content': [{'end': 167.383, 'text': 'Phases of a data analytics project.', 'start': 165.341, 'duration': 2.042}, {'end': 173.467, 'text': 'Now the journey of raw data from two meaningful data can be categorized into three phases.', 'start': 167.823, 'duration': 5.644}, {'end': 175.168, 'text': 'The first, data cleaning.', 'start': 173.747, 'duration': 1.421}, {'end': 176.729, 'text': 'Second is data analysis.', 'start': 175.508, 'duration': 1.221}, {'end': 178.651, 'text': 'And the third is data visualization.', 'start': 176.869, 'duration': 1.782}, {'end': 181.232, 'text': 'Now for data cleaning.', 'start': 179.251, 'duration': 1.981}, {'end': 189.247, 'text': 'our first step would be to set up a problem statement that would be aimed in solving a specific business problem of your client.', 'start': 181.232, 'duration': 8.015}, {'end': 192.169, 'text': 'Now, what is this problem statement?', 'start': 190.588, 'duration': 1.581}, {'end': 200.513, 'text': 'For example, a phone manufacturer would like to know how many customers actually bought their product while seeing an ad on YouTube.', 'start': 192.929, 'duration': 7.584}, {'end': 203.674, 'text': 'Now this would involve data.', 'start': 202.414, 'duration': 1.26}, {'end': 207.456, 'text': 'The second step would be to perform an initial analysis on data.', 'start': 204.255, 'duration': 3.201}, {'end': 214.436, 'text': 'So we need to check whether the data is complete, consistent and accurate.', 'start': 208.116, 'duration': 6.32}], 'summary': 'Data analytics project has three phases: data cleaning, analysis, and visualization.', 'duration': 49.095, 'max_score': 165.341, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws165341.jpg'}, {'end': 403.367, 'src': 'embed', 'start': 379.56, 'weight': 6, 'content': [{'end': 386.963, 'text': 'which is why it becomes essentially important that your effort does not go in vain.', 'start': 379.56, 'duration': 7.403}, {'end': 390.204, 'text': 'same is the case with the data analytics journey.', 'start': 386.963, 'duration': 3.241}, {'end': 393.705, 'text': 'if you do not incorporate data cleaning process,', 'start': 390.204, 'duration': 3.501}, {'end': 403.367, 'text': "then your final result would be hampered and even consisting of a lot of irregularities that your stakeholders won't even like.", 'start': 393.705, 'duration': 9.662}], 'summary': 'Data cleaning is crucial for a successful data analytics journey to avoid irregularities and stakeholder dissatisfaction.', 'duration': 23.807, 'max_score': 379.56, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws379560.jpg'}, {'end': 552.795, 'src': 'embed', 'start': 499.22, 'weight': 1, 'content': [{'end': 501.101, 'text': 'Okay, so are naked.', 'start': 499.22, 'duration': 1.881}, {'end': 511.265, 'text': 'I could not see that there were duplicates but there were so there were two duplicates that got removed and there are 23 unique values left.', 'start': 501.161, 'duration': 10.104}, {'end': 512.684, 'text': "So we'll click on OK.", 'start': 511.704, 'duration': 0.98}, {'end': 515.346, 'text': 'So the duplicates have already removed.', 'start': 512.705, 'duration': 2.641}, {'end': 520.188, 'text': 'Now, this is a very small data set for understanding purposes.', 'start': 516.006, 'duration': 4.182}, {'end': 525.111, 'text': 'But while you will analysis data, you would not get this small data set ever.', 'start': 520.269, 'duration': 4.842}, {'end': 529.914, 'text': 'So then certain things like remove duplicates becomes really important to know.', 'start': 525.531, 'duration': 4.383}, {'end': 536.117, 'text': "Next up, we'll just glance through it and we'll see if there are any irregularities present.", 'start': 530.554, 'duration': 5.563}, {'end': 540.199, 'text': "So I don't see any, but here we go.", 'start': 536.917, 'duration': 3.282}, {'end': 546.031, 'text': 'So in this 14th tab, we see years of experience as 90.', 'start': 540.768, 'duration': 5.263}, {'end': 547.932, 'text': "So now let's just think.", 'start': 546.031, 'duration': 1.901}, {'end': 552.795, 'text': 'So 90 is not feasible value as year of experience.', 'start': 548.453, 'duration': 4.342}], 'summary': '23 unique values left after removing duplicates.', 'duration': 53.575, 'max_score': 499.22, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws499220.jpg'}, {'end': 636.509, 'src': 'embed', 'start': 602.021, 'weight': 5, 'content': [{'end': 604.061, 'text': 'so lowest to highest.', 'start': 602.021, 'duration': 2.04}, {'end': 611.943, 'text': 'i see that one lakh one thousand seven hundred two here.', 'start': 604.061, 'duration': 7.882}, {'end': 616.363, 'text': 'employee of the salary 99 000 is below it.', 'start': 611.943, 'duration': 4.42}, {'end': 619.724, 'text': "so i don't think so that this was correct.", 'start': 616.363, 'duration': 3.361}, {'end': 621.924, 'text': 'so there is a gap in between.', 'start': 619.724, 'duration': 2.2}, {'end': 627.805, 'text': 'so considering all other salaries, They are in five digits.', 'start': 621.924, 'duration': 5.881}, {'end': 629.325, 'text': 'One is in six digits.', 'start': 628.305, 'duration': 1.02}, {'end': 633.087, 'text': 'So it can be a possibility that this empty space is by mistake.', 'start': 629.385, 'duration': 3.702}, {'end': 636.509, 'text': "We'll remove that empty space and click OK.", 'start': 633.147, 'duration': 3.362}], 'summary': 'Identified a potential error in salary data, removed empty space.', 'duration': 34.488, 'max_score': 602.021, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws602021.jpg'}], 'start': 73.106, 'title': 'Data analytics phases and techniques', 'summary': 'Covers the three phases of a data analytics project, emphasizing the importance of data cleaning and discussing various techniques such as sorting, filtering, lookup functions, conditional formatting, data validation, pivot tables, and data visualization using excel. it also discusses the importance of data cleaning and analysis through examples, emphasizing the need to remove errors, biases, and duplicates, and explains the process of data cleaning using microsoft excel, highlighting the removal of duplicates and identification of irregularities in the dataset.', 'chapters': [{'end': 214.436, 'start': 73.106, 'title': 'Data analytics: phases and techniques', 'summary': 'Covers the three phases of a data analytics project, emphasizing the importance of data cleaning and discussing various techniques such as sorting, filtering, lookup functions, conditional formatting, data validation, pivot tables, and data visualization using excel.', 'duration': 141.33, 'highlights': ['The chapter covers the three phases of a data analytics project: data cleaning, data analysis, and data visualization. It emphasizes the importance of data cleaning and the need to set up a problem statement aimed at solving a specific business problem.', 'The initial analysis on data involves checking for completeness, consistency, and accuracy. It highlights the importance of performing an initial analysis on data to ensure completeness, consistency, and accuracy.', 'Discusses various techniques such as sorting, filtering, lookup functions, conditional formatting, data validation, pivot tables, and data visualization using Excel. It covers various techniques such as sorting, filtering, lookup functions, conditional formatting, data validation, pivot tables, and data visualization using Excel.']}, {'end': 636.509, 'start': 214.436, 'title': 'Data cleaning and analysis process', 'summary': 'Covers the importance of data cleaning and analysis through examples, emphasizing the need to remove errors, biases, and duplicates, and explains the process of data cleaning using microsoft excel, highlighting the removal of duplicates and identification of irregularities in the dataset.', 'duration': 422.073, 'highlights': ['The removal of duplicates is a crucial step in data cleaning, as it ensures the accuracy and uniformity of the dataset, with 23 unique values left after removing two duplicates.', "Identifying and addressing irregularities in the dataset, such as unrealistic values like 'years of experience' as 90, highlights the importance of cross-examining data and maintaining a log for changes made during data cleaning.", 'The significance of maintaining a log book or creating a duplicate of the raw data during data cleaning to recover any missed values and to verify changes with the client, ensuring data integrity and accuracy.', 'The process of sorting the employee salary column from lowest to highest in Microsoft Excel reveals irregularities, such as an unrealistic salary value of one lakh one thousand seven hundred two, emphasizing the need for thorough examination and correction of data inconsistencies.']}], 'duration': 563.403, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws73106.jpg', 'highlights': ['The chapter covers the three phases of a data analytics project: data cleaning, data analysis, and data visualization. It emphasizes the importance of data cleaning and the need to set up a problem statement aimed at solving a specific business problem.', 'The removal of duplicates is a crucial step in data cleaning, as it ensures the accuracy and uniformity of the dataset, with 23 unique values left after removing two duplicates.', 'The initial analysis on data involves checking for completeness, consistency, and accuracy. It highlights the importance of performing an initial analysis on data to ensure completeness, consistency, and accuracy.', 'Discussing various techniques such as sorting, filtering, lookup functions, conditional formatting, data validation, pivot tables, and data visualization using Excel. It covers various techniques such as sorting, filtering, lookup functions, conditional formatting, data validation, pivot tables, and data visualization using Excel.', "Identifying and addressing irregularities in the dataset, such as unrealistic values like 'years of experience' as 90, highlights the importance of cross-examining data and maintaining a log for changes made during data cleaning.", 'The process of sorting the employee salary column from lowest to highest in Microsoft Excel reveals irregularities, such as an unrealistic salary value of one lakh one thousand seven hundred two, emphasizing the need for thorough examination and correction of data inconsistencies.', 'The significance of maintaining a log book or creating a duplicate of the raw data during data cleaning to recover any missed values and to verify changes with the client, ensuring data integrity and accuracy.']}, {'end': 1389.312, 'segs': [{'end': 669.73, 'src': 'embed', 'start': 636.609, 'weight': 0, 'content': [{'end': 640.37, 'text': "Then we'll again click on this and do sort.", 'start': 636.609, 'duration': 3.761}, {'end': 641.511, 'text': 'So this gets sorted.', 'start': 640.57, 'duration': 0.941}, {'end': 646.353, 'text': 'But again, you need to check with your client that the removed space was correct or not.', 'start': 641.571, 'duration': 4.782}, {'end': 652.656, 'text': 'So these are certain things that can be an issue when you analyze it in a later stage.', 'start': 646.873, 'duration': 5.783}, {'end': 657.687, 'text': 'So it becomes essentially important to correct everything in advance at the first phase itself.', 'start': 653.046, 'duration': 4.641}, {'end': 659.948, 'text': 'So next we move on to functions.', 'start': 658.147, 'duration': 1.801}, {'end': 669.73, 'text': 'What are functions functions are predefined instructions or formulas that perform a specific calculation in a particular order in a spreadsheet.', 'start': 660.428, 'duration': 9.302}], 'summary': 'Sorting and checking data for accuracy is crucial. functions in spreadsheets perform specific calculations in a predefined order.', 'duration': 33.121, 'max_score': 636.609, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws636609.jpg'}, {'end': 707.613, 'src': 'embed', 'start': 681.937, 'weight': 1, 'content': [{'end': 686.74, 'text': 'For example, it can be addition, subtraction, multiplication, or even finding an average.', 'start': 681.937, 'duration': 4.803}, {'end': 692.504, 'text': 'In order to perform these, if you do it manually, it would take a lot of time and effort.', 'start': 688.041, 'duration': 4.463}, {'end': 698.847, 'text': 'You physically have to put the plus operator between every single digit to perform a sum.', 'start': 693.104, 'duration': 5.743}, {'end': 707.613, 'text': 'So in that case, Microsoft Excel already has some predefined formulas so that you can save your time and effort.', 'start': 700.108, 'duration': 7.505}], 'summary': 'Microsoft excel offers predefined formulas for saving time and effort in performing various calculations.', 'duration': 25.676, 'max_score': 681.937, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws681937.jpg'}, {'end': 832.066, 'src': 'embed', 'start': 803.453, 'weight': 2, 'content': [{'end': 809.556, 'text': 'so countif is a function that counts the number of cells that matches a specific condition.', 'start': 803.453, 'duration': 6.103}, {'end': 821.403, 'text': 'so it its syntax would be equals to the start of the function, then countif, Then the range in which we want count f to operate and the condition.', 'start': 809.556, 'duration': 11.847}, {'end': 824.984, 'text': 'So what is the value that we are searching for?', 'start': 822.103, 'duration': 2.881}, {'end': 832.066, 'text': "For example, in a class of around 20 students, let's say we have three students whose name is Abhishek.", 'start': 825.464, 'duration': 6.602}], 'summary': 'The countif function counts cells matching a specific condition, such as finding the number of students named abhishek in a class of 20.', 'duration': 28.613, 'max_score': 803.453, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws803453.jpg'}, {'end': 1008.041, 'src': 'embed', 'start': 979.557, 'weight': 3, 'content': [{'end': 983.421, 'text': 'so this is how countif reduces our time and effort for long data sets.', 'start': 979.557, 'duration': 3.864}, {'end': 989.693, 'text': 'This is comparatively very short data set, but in cases of long data set it saves us a lot of time and effort.', 'start': 983.871, 'duration': 5.822}, {'end': 991.614, 'text': 'Next is length function.', 'start': 990.054, 'duration': 1.56}, {'end': 995.236, 'text': 'So length is a function that returns the length of a text string.', 'start': 991.734, 'duration': 3.502}, {'end': 999.057, 'text': "Now let's assume that a cell contains a word India.", 'start': 995.916, 'duration': 3.141}, {'end': 1003.619, 'text': 'Now India has total number of five characters I N D I A.', 'start': 999.417, 'duration': 4.202}, {'end': 1008.041, 'text': 'So now this functions would return with a total number of characters in that particular cell.', 'start': 1003.619, 'duration': 4.422}], 'summary': 'Countif saves time for long data sets; length function returns text string length.', 'duration': 28.484, 'max_score': 979.557, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws979557.jpg'}, {'end': 1043.344, 'src': 'embed', 'start': 1021.589, 'weight': 5, 'content': [{'end': 1030.395, 'text': 'so similar to length function left and right function return specific number of characters, but from left and right side in a cell.', 'start': 1021.589, 'duration': 8.806}, {'end': 1034.758, 'text': 'so it will tell the total number of characters from the left or the right side.', 'start': 1030.395, 'duration': 4.363}, {'end': 1038.02, 'text': 'so its syntax is equality, left or right.', 'start': 1034.758, 'duration': 3.262}, {'end': 1043.344, 'text': 'in parenthesis, the cell in which we want the function to act, comma, number of characters from each side.', 'start': 1038.02, 'duration': 5.324}], 'summary': 'Excel left and right functions return specific number of characters from left or right side in a cell.', 'duration': 21.755, 'max_score': 1021.589, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1021589.jpg'}, {'end': 1268.673, 'src': 'heatmap', 'start': 1146.726, 'weight': 6, 'content': [{'end': 1153.828, 'text': 'so now this is a spreadsheet in which we have the name of states and the counties with their pin codes.', 'start': 1146.726, 'duration': 7.102}, {'end': 1156.59, 'text': "now let's just say i want to extract the pin codes from this.", 'start': 1153.828, 'duration': 2.762}, {'end': 1161.039, 'text': "so i'll first type in code that I need to extract.", 'start': 1156.59, 'duration': 4.449}, {'end': 1165.101, 'text': 'now I can see that pin codes of or of only six digits.', 'start': 1161.039, 'duration': 4.062}, {'end': 1168.362, 'text': 'so I can use left function to extract this.', 'start': 1165.101, 'duration': 3.261}, {'end': 1178.986, 'text': "so I'll write equals to left left open parenthesis, the cell b2 comma.", 'start': 1168.362, 'duration': 10.624}, {'end': 1185.389, 'text': "since pin code is of six digits, so I'll write six and close parenthesis press enter.", 'start': 1178.986, 'duration': 6.403}, {'end': 1187.85, 'text': 'therefore I get the pin code of each County.', 'start': 1185.389, 'duration': 2.461}, {'end': 1198.515, 'text': 'So if I click on this plus sign over the right bottom corner and drag all the way through the spreadsheet,', 'start': 1190.492, 'duration': 8.023}, {'end': 1203.116, 'text': "I'll get the pin codes of all the counties present.", 'start': 1198.515, 'duration': 4.601}, {'end': 1207.418, 'text': "So now I don't have to manually write everything if I need the pin codes of these.", 'start': 1203.256, 'duration': 4.162}, {'end': 1209.819, 'text': 'So this makes our work easier.', 'start': 1208.058, 'duration': 1.761}, {'end': 1213.2, 'text': 'Next we look into concatenate function.', 'start': 1210.959, 'duration': 2.241}, {'end': 1217.101, 'text': 'So this function combines multiple text cells into a single cell.', 'start': 1213.46, 'duration': 3.641}, {'end': 1221.344, 'text': 'so, for example, you have your first name and last name.', 'start': 1217.662, 'duration': 3.682}, {'end': 1224.726, 'text': 'now you want a cell in which you need it should be full name.', 'start': 1221.344, 'duration': 3.382}, {'end': 1229.548, 'text': 'so this concatenate function helps join two different cell into a single cell.', 'start': 1224.726, 'duration': 4.822}, {'end': 1231.489, 'text': 'so its syntax is equality.', 'start': 1229.548, 'duration': 1.941}, {'end': 1234.61, 'text': 'concatenate open parenthesis cell 1 comma.', 'start': 1231.489, 'duration': 3.121}, {'end': 1237.172, 'text': 'cell 2, close parenthesis.', 'start': 1234.61, 'duration': 2.562}, {'end': 1238.933, 'text': 'next is trim function.', 'start': 1237.172, 'duration': 1.761}, {'end': 1242.554, 'text': 'so trim function helps remove extra unwanted space from the cell.', 'start': 1238.933, 'duration': 3.621}, {'end': 1252.504, 'text': "so lot of times in raw data there's a lot of unwanted space within the cell, which is why it could hinder your data analysis going forward.", 'start': 1242.554, 'duration': 9.95}, {'end': 1259.148, 'text': 'so trim function syntax is equality trim open parenthesis cell one, close parenthesis.', 'start': 1252.504, 'duration': 6.644}, {'end': 1262.97, 'text': "now we'll see these two functions on excel.", 'start': 1259.148, 'duration': 3.822}, {'end': 1268.673, 'text': 'so as we can see, this spreadsheet has first name and last name written and we want full name.', 'start': 1262.97, 'duration': 5.703}], 'summary': 'Using left function to extract pin codes and concatenate function to combine text cells. trim function removes unwanted space.', 'duration': 51.011, 'max_score': 1146.726, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1146726.jpg'}], 'start': 636.609, 'title': 'Excel functions and countif', 'summary': 'Discusses the importance of correcting data at the first phase, introduces functions as predefined instructions in excel, explains the syntax of functions, delves into countif function with practical examples, and demonstrates the efficient manipulation and analysis of data using excel functions like countif, length, left, right, concatenate, and trim, with practical examples including character counting, extracting specific text, combining text cells, and removing unwanted spaces.', 'chapters': [{'end': 979.557, 'start': 636.609, 'title': 'Excel functions and countif', 'summary': 'Discusses the importance of correcting data at the first phase, introduces functions as predefined instructions in excel, explains the syntax of functions, and delves into countif function with practical examples of counting specific conditions in a dataset.', 'duration': 342.948, 'highlights': ['The chapter emphasizes the importance of correcting data at the first phase, as issues like removed spaces can arise during later analysis. Correcting data at the initial stage is crucial to prevent issues during later analysis.', 'Functions in Excel are predefined instructions or formulas that perform specific calculations, such as addition, subtraction, multiplication, and finding an average, saving time and effort in manual calculations. Functions in Excel streamline calculations like addition, subtraction, multiplication, and average, saving time and effort in manual calculations.', 'The syntax of a basic function in Excel comprises three major components: the start of a function with an equality operator, definition of the calculation needed, and the range of cells for the specific calculation. The basic syntax of an Excel function includes the start of a function, definition of the calculation, and the range of cells for the specific calculation.', 'The countif function in Excel counts the number of cells that match a specific condition, offering efficiency in analyzing large chunks of data. The countif function efficiently counts the number of cells matching a specific condition in large datasets.']}, {'end': 1389.312, 'start': 979.557, 'title': 'Excel functions: countif, length, left, right, concatenate, trim', 'summary': 'Demonstrates how excel functions like countif, length, left, right, concatenate, and trim can efficiently manipulate and analyze data, saving time and effort, with examples including character counting, extracting specific text, combining text cells, and removing unwanted spaces.', 'duration': 409.755, 'highlights': ['The countif function efficiently reduces time and effort for long data sets. Countif function reduces time and effort by efficiently processing long data sets.', 'The length function returns the number of characters in a text string, including spaces. Length function returns the total number of characters in a text string, including spaces, providing accurate data analysis.', 'The left and right functions extract a specific number of characters from the left or right side of a cell. Left and right functions efficiently extract a specific number of characters from the left or right side of a cell, aiding in data manipulation.', 'The concatenate function combines multiple text cells into a single cell, facilitating data organization. Concatenate function efficiently combines multiple text cells into a single cell, aiding in data organization and manipulation.', 'The trim function removes extra unwanted spaces from cells, facilitating accurate data analysis. Trim function efficiently removes extra unwanted spaces from cells, ensuring accurate data analysis.']}], 'duration': 752.703, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws636609.jpg', 'highlights': ['Correcting data at the initial stage is crucial to prevent issues during later analysis.', 'Functions in Excel streamline calculations like addition, subtraction, multiplication, and average, saving time and effort in manual calculations.', 'The countif function efficiently counts the number of cells matching a specific condition in large datasets.', 'Countif function reduces time and effort by efficiently processing long data sets.', 'Length function returns the total number of characters in a text string, including spaces, providing accurate data analysis.', 'Left and right functions efficiently extract a specific number of characters from the left or right side of a cell, aiding in data manipulation.', 'Concatenate function efficiently combines multiple text cells into a single cell, aiding in data organization and manipulation.', 'Trim function efficiently removes extra unwanted spaces from cells, ensuring accurate data analysis.']}, {'end': 2074.708, 'segs': [{'end': 1498.452, 'src': 'heatmap', 'start': 1446.581, 'weight': 0.739, 'content': [{'end': 1450.203, 'text': 'So it will give me the names in ascending order alphabetically.', 'start': 1446.581, 'duration': 3.622}, {'end': 1456.787, 'text': "Now we'll if I want it to be in the reverse order reverse alphabetical order.", 'start': 1451.004, 'duration': 5.783}, {'end': 1458.028, 'text': "So I'll get that as well.", 'start': 1456.807, 'duration': 1.221}, {'end': 1465.892, 'text': 'Now if I want my city name to start from descending order, from reverse alphabetically,', 'start': 1459.248, 'duration': 6.644}, {'end': 1469.534, 'text': 'and also I want my birthday to start from the oldest to the newest.', 'start': 1465.892, 'duration': 3.642}, {'end': 1474.457, 'text': "No now, how would we do that? So we'll go to sort option here.", 'start': 1470.035, 'duration': 4.422}, {'end': 1475.958, 'text': 'So it will open a tab.', 'start': 1474.957, 'duration': 1.001}, {'end': 1484.509, 'text': 'So as we can see it automatically selects the entire spreadsheet now in this we can put a preference in the way we want.', 'start': 1476.567, 'duration': 7.942}, {'end': 1491.51, 'text': 'So our first preference is to make sure the city name is in reverse alphabetical order.', 'start': 1484.829, 'duration': 6.681}, {'end': 1494.771, 'text': "So I'll put in sort by city name.", 'start': 1491.87, 'duration': 2.901}, {'end': 1498.452, 'text': 'We can choose any other but we want city name sort on now.', 'start': 1495.191, 'duration': 3.261}], 'summary': 'Demonstrating sorting options for names, city, and birthday in a spreadsheet.', 'duration': 51.871, 'max_score': 1446.581, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1446581.jpg'}, {'end': 1586.506, 'src': 'embed', 'start': 1554.91, 'weight': 2, 'content': [{'end': 1564.181, 'text': 'so we can see that here as well, from starting from 1942 going ahead till the last section of 1994..', 'start': 1554.91, 'duration': 9.271}, {'end': 1567.262, 'text': 'So here this is the use of sort function.', 'start': 1564.181, 'duration': 3.081}, {'end': 1573.983, 'text': "Now if I need to sort by different color sets, so let's take an example of that as well.", 'start': 1567.802, 'duration': 6.181}, {'end': 1580.865, 'text': "So let's say I want my department to be color coded.", 'start': 1574.683, 'duration': 6.182}, {'end': 1586.506, 'text': "So I'll put produce as let's say which color click on home ribbon.", 'start': 1581.325, 'duration': 5.181}], 'summary': 'Explanation of using the sort function with an example of color-coding departments.', 'duration': 31.596, 'max_score': 1554.91, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1554910.jpg'}, {'end': 1706.672, 'src': 'embed', 'start': 1678.938, 'weight': 0, 'content': [{'end': 1684.781, 'text': 'So filter function basically narrows down the way you want your specific data to be viewed.', 'start': 1678.938, 'duration': 5.843}, {'end': 1694.246, 'text': "So let's say in this example, this is a movie set in which we have the title, year, genre, language, director and average ratings if it has got.", 'start': 1685.382, 'duration': 8.864}, {'end': 1699.448, 'text': "Now I only want to see let's say English movies what all movies are in English.", 'start': 1694.706, 'duration': 4.742}, {'end': 1706.672, 'text': "So now sorting it out by English would be one option but to make it more easier, I'll click on this filter tab.", 'start': 1700.009, 'duration': 6.663}], 'summary': 'The filter function helps narrow down specific data views, such as finding english movies in a movie set by language.', 'duration': 27.734, 'max_score': 1678.938, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1678938.jpg'}, {'end': 1866.737, 'src': 'embed', 'start': 1834.465, 'weight': 1, 'content': [{'end': 1838.647, 'text': 'Number one is your employee ID and number two is the salary.', 'start': 1834.465, 'duration': 4.182}, {'end': 1850.109, 'text': 'Okay So in order to extract the salary that would be in column 2 so you would put the column index number as 2 and true and false.', 'start': 1841.848, 'duration': 8.261}, {'end': 1855.732, 'text': 'So true is if you want approximate match and false is if you want an exact match of it.', 'start': 1850.129, 'duration': 5.603}, {'end': 1860.014, 'text': "So now let's look at VLOOKUP functions in Microsoft Excel.", 'start': 1856.372, 'duration': 3.642}, {'end': 1866.737, 'text': 'Now we have a spreadsheet which contains employee IDs years of experience and salary of the employee.', 'start': 1860.814, 'duration': 5.923}], 'summary': 'Explaining vlookup in excel to extract employee salaries by column index and match type.', 'duration': 32.272, 'max_score': 1834.465, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1834465.jpg'}, {'end': 1997.519, 'src': 'heatmap', 'start': 1892.607, 'weight': 0.924, 'content': [{'end': 1897.466, 'text': 'So that corresponding piece of salary is 90140.', 'start': 1892.607, 'duration': 4.859}, {'end': 1900.887, 'text': 'nine, zero, one, four, zero.', 'start': 1897.466, 'duration': 3.421}, {'end': 1909.93, 'text': 'so now with lookup functions we want, if we just put the employee id, the corresponding piece of salary should automatically come right.', 'start': 1900.887, 'duration': 9.043}, {'end': 1913.232, 'text': "so we'll use we look up in that case.", 'start': 1909.93, 'duration': 3.302}, {'end': 1917.033, 'text': "so in the salary column i'll use the syntax equality.", 'start': 1913.232, 'duration': 3.801}, {'end': 1921.655, 'text': 'we look up open parenthesis.', 'start': 1917.033, 'duration': 4.622}, {'end': 1923.715, 'text': 'now. the first is the lookup value.', 'start': 1921.655, 'duration': 2.06}, {'end': 1927.655, 'text': 'so our lookup value would be employee id.', 'start': 1923.715, 'duration': 3.94}, {'end': 1929.557, 'text': 'so i will type employee id here.', 'start': 1927.655, 'duration': 1.902}, {'end': 1932.5, 'text': "so i'll click on this cell comma.", 'start': 1929.557, 'duration': 2.943}, {'end': 1933.841, 'text': 'second is a table array.', 'start': 1932.5, 'duration': 1.341}, {'end': 1936.243, 'text': 'so our table array is the entire data set.', 'start': 1933.841, 'duration': 2.402}, {'end': 1942.229, 'text': "so i'll select the entire data set.", 'start': 1936.243, 'duration': 5.986}, {'end': 1946.052, 'text': "next is i'll use the column index number.", 'start': 1942.229, 'duration': 3.823}, {'end': 1952.755, 'text': "so since the salary employees is in column number three, so i'll put as three comma.", 'start': 1946.052, 'duration': 6.703}, {'end': 1958.321, 'text': 'now I want now it says that you want an approximate match or exact match.', 'start': 1952.755, 'duration': 5.566}, {'end': 1959.942, 'text': 'So I would go with an exact match.', 'start': 1958.381, 'duration': 1.561}, {'end': 1969.531, 'text': "So I'll write false close parenthesis and enter so it says any since I have not input any employee ID.", 'start': 1960.022, 'duration': 9.509}, {'end': 1971.092, 'text': "So let's cross check it.", 'start': 1969.951, 'duration': 1.141}, {'end': 1978.359, 'text': "So let's say 2 1 2 1 2 6, right? So I'll type here 2 1 2 6 and press enter.", 'start': 1971.553, 'duration': 6.806}, {'end': 1981.429, 'text': 'now it gives me 49 942.', 'start': 1979.028, 'duration': 2.401}, {'end': 1982.93, 'text': "let's cross check.", 'start': 1981.429, 'duration': 1.501}, {'end': 1988.573, 'text': 'so the employee id 2126 has a salary of 49 942.', 'start': 1982.93, 'duration': 5.643}, {'end': 1989.734, 'text': 'absolutely correct.', 'start': 1988.573, 'duration': 1.161}, {'end': 1993.536, 'text': 'so this is the way in which we use lookup functions and we can type any other thing.', 'start': 1989.734, 'duration': 3.802}, {'end': 1995.377, 'text': "let's check of.", 'start': 1993.536, 'duration': 1.841}, {'end': 1997.519, 'text': "let's say 3, 7, 7, 3 and press enter 99 029.", 'start': 1995.377, 'duration': 2.142}], 'summary': 'Using lookup function to automate salary retrieval for employee ids, achieving 100% accuracy.', 'duration': 104.912, 'max_score': 1892.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1892607.jpg'}], 'start': 1389.312, 'title': 'Using vlookup functions in excel', 'summary': 'Explains sorting, filtering, and using vlookup function in excel, covering sorting methods, filtering data, and demonstrating vlookup usage for employee data retrieval.', 'chapters': [{'end': 1790.079, 'start': 1389.312, 'title': 'Sort, filter, and lookup functions in excel', 'summary': 'Explains sorting by ascending or descending order, custom sorting based on multiple criteria, color-coded sorting, filtering data based on specific values, and using vlookup function to retrieve information from a data set in excel.', 'duration': 400.767, 'highlights': ['The chapter explains sorting by ascending or descending order, custom sorting based on multiple criteria, and color-coded sorting. Sorting allows rearranging data in ascending or descending order and custom sorting based on multiple criteria and color-coding.', 'The chapter demonstrates filtering data based on specific values, such as language in a movie dataset. Filter function is used to narrow down data based on specific values, such as filtering movies by language in a dataset.', 'The chapter introduces the VLOOKUP function and explains its application in retrieving information from a data set. VLOOKUP function is explained as a method for retrieving corresponding information from a dataset based on a specific value in another column.']}, {'end': 2074.708, 'start': 1790.711, 'title': 'Using vlookup functions in microsoft excel', 'summary': 'Explains the syntax and usage of vlookup functions in microsoft excel, demonstrating how to use it to find employee salaries and years of experience based on employee ids, with examples showing successful lookups for specific employee ids and corresponding data.', 'duration': 283.997, 'highlights': ['The VLOOKUP function in Microsoft Excel is demonstrated, showcasing its syntax and usage for finding employee salaries and years of experience based on employee IDs, with examples of successful lookups for specific employee IDs and their corresponding data.', 'The syntax of the VLOOKUP function is explained, highlighting the parameters including lookup value, table array, column index number, and the choice between approximate match and exact match, with practical examples demonstrating the successful use of these parameters.', 'Successful demonstrations are provided for using the VLOOKUP function to find employee salaries and years of experience based on employee IDs, with specific examples of inputting employee IDs and retrieving the corresponding salary and experience data.']}], 'duration': 685.396, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws1389312.jpg', 'highlights': ['The chapter demonstrates filtering data based on specific values, such as language in a movie dataset.', 'The VLOOKUP function in Microsoft Excel is demonstrated, showcasing its syntax and usage for finding employee salaries and years of experience based on employee IDs, with examples of successful lookups for specific employee IDs and their corresponding data.', 'The chapter explains sorting by ascending or descending order, custom sorting based on multiple criteria, and color-coded sorting. Sorting allows rearranging data in ascending or descending order and custom sorting based on multiple criteria and color-coding.']}, {'end': 2737.953, 'segs': [{'end': 2130.63, 'src': 'embed', 'start': 2100.261, 'weight': 0, 'content': [{'end': 2102.363, 'text': 'But instead of V, we have H over here.', 'start': 2100.261, 'duration': 2.102}, {'end': 2106.765, 'text': 'And also the column we used to search in VLOOKUP.', 'start': 2102.983, 'duration': 3.782}, {'end': 2110.188, 'text': 'Here in this case, in HLOOKUP, we look at horizontal rows.', 'start': 2107.106, 'duration': 3.082}, {'end': 2114.19, 'text': 'So the column would be replaced by row.', 'start': 2110.648, 'duration': 3.542}, {'end': 2119.013, 'text': "So let's look at the example in Microsoft Excel and know more about it.", 'start': 2115.251, 'duration': 3.762}, {'end': 2121.663, 'text': 'So this is a spreadsheet here.', 'start': 2120.182, 'duration': 1.481}, {'end': 2125.907, 'text': 'We have a table but now instead of columns, we have rows.', 'start': 2121.703, 'duration': 4.204}, {'end': 2130.63, 'text': 'What does that mean? So generally we used to have column headers over here.', 'start': 2126.367, 'duration': 4.263}], 'summary': 'Hlookup in excel uses horizontal rows instead of columns for searching.', 'duration': 30.369, 'max_score': 2100.261, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2100261.jpg'}, {'end': 2454.763, 'src': 'heatmap', 'start': 2369.956, 'weight': 1, 'content': [{'end': 2376.759, 'text': 'So instead of color scale, it gives certain icons like increasing, decreasing, and etc.', 'start': 2369.956, 'duration': 6.803}, {'end': 2382.472, 'text': "Now let's just look at conditional formatting on the Microsoft Excel platform.", 'start': 2377.179, 'duration': 5.293}, {'end': 2383.673, 'text': 'All right.', 'start': 2383.353, 'duration': 0.32}, {'end': 2388.955, 'text': 'So, in this spreadsheet we have a data set that describes up the number of companies,', 'start': 2384.033, 'duration': 4.922}, {'end': 2393.097, 'text': 'different companies and the quarterly revenues of those companies.', 'start': 2388.955, 'duration': 4.142}, {'end': 2396.519, 'text': 'So now we will do conditional formatting in this data set.', 'start': 2393.657, 'duration': 2.862}, {'end': 2403.081, 'text': 'So our first objective is to find the companies with revenue per quarter less than $40,000.', 'start': 2396.639, 'duration': 6.442}, {'end': 2405.043, 'text': "So for that, I'll select the data set.", 'start': 2403.082, 'duration': 1.961}, {'end': 2410.978, 'text': 'Now I will click on home ribbon and conditional formatting.', 'start': 2406.043, 'duration': 4.935}, {'end': 2415.701, 'text': "so, since I needed less than so, I'll highlight cell rule options.", 'start': 2410.978, 'duration': 4.723}, {'end': 2418.522, 'text': "I'll choose then less than option.", 'start': 2415.701, 'duration': 2.821}, {'end': 2424.665, 'text': 'now, in this first column, I will type the value that I want to be less than so.', 'start': 2418.522, 'duration': 6.143}, {'end': 2428.847, 'text': 'it would should be less than 40,000, so it would be less than 40,000.', 'start': 2424.665, 'duration': 4.182}, {'end': 2432.268, 'text': 'and then what characteristics I need.', 'start': 2428.847, 'duration': 3.421}, {'end': 2437.471, 'text': 'so should it be a light red fill with dark text or yellow fill, green fill?', 'start': 2432.268, 'duration': 5.203}, {'end': 2443.595, 'text': 'custom also, we can choose, but I will go with green filled with light dark green text.', 'start': 2438.391, 'duration': 5.204}, {'end': 2445.456, 'text': 'so I click on.', 'start': 2443.595, 'duration': 1.861}, {'end': 2454.763, 'text': 'OK. so now I have all the companies whose quarterly revenues less than $40,000 highlighted in green right,', 'start': 2445.456, 'duration': 9.307}], 'summary': 'Using conditional formatting in excel, companies with quarterly revenues less than $40,000 are highlighted in green.', 'duration': 26.563, 'max_score': 2369.956, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2369956.jpg'}], 'start': 2075.109, 'title': 'Excel functions', 'summary': "Covers hlookup in excel, explaining its usage, syntax, and similarity to vlookup. it also delves into excel's conditional formatting, showcasing its features like highlighting cells, data bars, color scales, and icon sets, with practical examples of identifying specific data based on conditions.", 'chapters': [{'end': 2240.228, 'start': 2075.109, 'title': 'Understanding hlookup in excel', 'summary': 'Explains the usage of hlookup in excel, highlighting its syntax and demonstrating how to use it to retrieve information from a horizontal table, providing examples and emphasizing its similarity to vlookup.', 'duration': 165.119, 'highlights': ['The chapter explains the syntax and usage of HLOOKUP, emphasizing its efficiency in handling longer data sets.', 'It describes how HLOOKUP functions similarly to VLOOKUP, but searches rows instead of columns, and demonstrates its application in retrieving information from a horizontal table.', 'The speaker provides a step-by-step demonstration of using HLOOKUP in Microsoft Excel to retrieve the price of different office supplies, showcasing its practical application and providing clarity on the process.', 'The demonstration includes specifying the lookup value, table array, row index number, and the requirement for an exact match when using HLOOKUP in Excel.']}, {'end': 2737.953, 'start': 2240.228, 'title': 'Excel conditional formatting', 'summary': 'Discusses conditional formatting in microsoft excel, which allows users to format datasets based on specific conditions, such as highlighting cells with values greater than a certain threshold, identifying top and bottom values, using data bars to visualize cell values, applying color scales to categorize values into different color gradients, and using icon sets to represent data with different shapes and indicators. it also demonstrates the practical application of conditional formatting in identifying companies with revenues less than $40,000 and finding the bottom 10 companies per quarter in a dataset.', 'duration': 497.725, 'highlights': ['The chapter explains conditional formatting in Microsoft Excel, enabling users to format datasets based on specific conditions, such as highlighting cells with values greater than a certain threshold, identifying top and bottom values, using data bars, applying color scales, and using icon sets to represent data. The chapter provides a comprehensive overview of conditional formatting features, including highlighting cells, top and bottom rules, data bars, color scales, and icon sets, for formatting datasets based on specific conditions.', 'It demonstrates the practical application of conditional formatting in Microsoft Excel, such as identifying companies with revenues less than $40,000 and finding the bottom 10 companies per quarter in a dataset. A practical demonstration is provided on how to use conditional formatting to identify companies with revenues less than $40,000 and find the bottom 10 companies per quarter in a dataset, showcasing the efficiency and time-saving benefits of this feature.']}], 'duration': 662.844, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2075109.jpg', 'highlights': ['The chapter provides a step-by-step demonstration of using HLOOKUP in Microsoft Excel to retrieve the price of different office supplies, showcasing its practical application and providing clarity on the process.', 'The chapter explains conditional formatting in Microsoft Excel, enabling users to format datasets based on specific conditions, such as highlighting cells with values greater than a certain threshold, identifying top and bottom values, using data bars, applying color scales, and using icon sets to represent data.']}, {'end': 3260.949, 'segs': [{'end': 2793.111, 'src': 'embed', 'start': 2763.333, 'weight': 0, 'content': [{'end': 2773.002, 'text': "and also we can create new rules such as, let's say, format only values that are above and below certain things.", 'start': 2763.333, 'duration': 9.669}, {'end': 2777.065, 'text': 'so above above format.', 'start': 2773.002, 'duration': 4.063}, {'end': 2779.427, 'text': 'so I can give them different.', 'start': 2777.065, 'duration': 2.362}, {'end': 2782.59, 'text': 'I can border, I can fill colors in this.', 'start': 2779.427, 'duration': 3.163}, {'end': 2785.873, 'text': "so I'll say blue color I need to fill okay, okay.", 'start': 2782.59, 'duration': 3.283}, {'end': 2793.111, 'text': 'So the values that are above average would be highlighted in blue and the rest would not be affected.', 'start': 2786.328, 'duration': 6.783}], 'summary': 'New formatting rule to highlight values above average in blue.', 'duration': 29.778, 'max_score': 2763.333, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2763333.jpg'}, {'end': 2900.825, 'src': 'heatmap', 'start': 2844.813, 'weight': 0.949, 'content': [{'end': 2848.054, 'text': 'Okay So here we have Microsoft Excel in this spreadsheet.', 'start': 2844.813, 'duration': 3.241}, {'end': 2851.474, 'text': 'We have a table in which we need to fill certain columns.', 'start': 2848.094, 'duration': 3.38}, {'end': 2853.735, 'text': "So let's say the first one is name.", 'start': 2851.935, 'duration': 1.8}, {'end': 2860.576, 'text': "So in this column, let's assume I only want the name should be a maximum of 20 characters long.", 'start': 2853.855, 'duration': 6.721}, {'end': 2869.058, 'text': 'So for that condition, I need to choose this range click on data data validation.', 'start': 2861.817, 'duration': 7.241}, {'end': 2870.839, 'text': 'So here the tabs appears.', 'start': 2869.438, 'duration': 1.401}, {'end': 2878.621, 'text': 'So in that settings tab, We need to fill the criteria that needs to be filled for the particular range.', 'start': 2871.259, 'duration': 7.362}, {'end': 2882.662, 'text': "So I'll click on since we need characters.", 'start': 2879.041, 'duration': 3.621}, {'end': 2884.742, 'text': "So I'll choose text length.", 'start': 2882.882, 'duration': 1.86}, {'end': 2888.943, 'text': 'Minimum character one maximum character.', 'start': 2886.522, 'duration': 2.421}, {'end': 2893.284, 'text': "Let's say 12 and say, okay.", 'start': 2890.023, 'duration': 3.261}, {'end': 2896.124, 'text': "Now, let's check this out.", 'start': 2895.184, 'duration': 0.94}, {'end': 2900.825, 'text': "Now if I type a word, let's say sing.", 'start': 2896.184, 'duration': 4.641}], 'summary': 'Using data validation in excel to limit name column to 20 characters.', 'duration': 56.012, 'max_score': 2844.813, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2844813.jpg'}, {'end': 2995.492, 'src': 'heatmap', 'start': 2939.584, 'weight': 2, 'content': [{'end': 2944.907, 'text': 'so in this way we can set certain conditions for this column.', 'start': 2939.584, 'duration': 5.323}, {'end': 2949.53, 'text': 'similarly, we can make a drop down list for gender column.', 'start': 2944.907, 'duration': 4.623}, {'end': 2955.814, 'text': "so in order to do that i'll again go to data validation column, press data validation.", 'start': 2949.53, 'duration': 6.284}, {'end': 2959.376, 'text': 'now i will put allow as a list.', 'start': 2955.814, 'duration': 3.562}, {'end': 2962.38, 'text': 'so now I need to select the source.', 'start': 2960.339, 'duration': 2.041}, {'end': 2971.547, 'text': "so I can just write here, let's say male, comma, female, and then press ok.", 'start': 2962.38, 'duration': 9.167}, {'end': 2973.028, 'text': 'so I only selected one column.', 'start': 2971.547, 'duration': 1.481}, {'end': 2976.61, 'text': 'I did not select the range, but I could select the entire range and do that.', 'start': 2973.028, 'duration': 3.582}, {'end': 2980.233, 'text': 'but in this case I can choose its male or female.', 'start': 2976.61, 'duration': 3.623}, {'end': 2983.515, 'text': 'so it will automatically come here and whatever we choose.', 'start': 2980.233, 'duration': 3.282}, {'end': 2985.276, 'text': 'so there are only two things we can choose.', 'start': 2983.515, 'duration': 1.761}, {'end': 2986.977, 'text': "we can't write anything here.", 'start': 2985.276, 'duration': 1.701}, {'end': 2987.918, 'text': 'that would be invalid.', 'start': 2986.977, 'duration': 0.941}, {'end': 2991.45, 'text': 'so in this way we can choose.', 'start': 2989.989, 'duration': 1.461}, {'end': 2995.492, 'text': 'list now comes.', 'start': 2991.45, 'duration': 4.042}], 'summary': 'Creating a drop-down list for gender column with male and female options.', 'duration': 33.444, 'max_score': 2939.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2939584.jpg'}, {'end': 3035.107, 'src': 'embed', 'start': 2999.274, 'weight': 3, 'content': [{'end': 3004.177, 'text': 'so we can set the range from what number to what number we want in that particular column.', 'start': 2999.274, 'duration': 4.903}, {'end': 3005.558, 'text': "so let's do that as well.", 'start': 3004.177, 'duration': 1.381}, {'end': 3013.002, 'text': "let's see data validation allow only whole number or decimal.", 'start': 3005.558, 'duration': 7.444}, {'end': 3014.943, 'text': 'so since marks can be in decimal, like 10.5, 12.5, 50.5.', 'start': 3013.002, 'duration': 1.941}, {'end': 3020.881, 'text': "so we'll choose decimal in between.", 'start': 3014.943, 'duration': 5.938}, {'end': 3027.504, 'text': "let's say 0 to 100 and then we'll press ok.", 'start': 3020.881, 'duration': 6.623}, {'end': 3035.107, 'text': "so in this case let's see 1 enter, 10 enter, 55.5, enter.", 'start': 3027.504, 'duration': 7.603}], 'summary': 'Setting data validation for column with range 0-100, allowing decimals.', 'duration': 35.833, 'max_score': 2999.274, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2999274.jpg'}, {'end': 3110.227, 'src': 'embed', 'start': 3063.567, 'weight': 1, 'content': [{'end': 3067.37, 'text': 'so the error message should be customized right.', 'start': 3063.567, 'duration': 3.803}, {'end': 3069.672, 'text': 'so in that case we can do a thing.', 'start': 3067.37, 'duration': 2.302}, {'end': 3072.875, 'text': "let's say data validation.", 'start': 3069.672, 'duration': 3.203}, {'end': 3074.396, 'text': 'so this condition we set.', 'start': 3072.875, 'duration': 1.521}, {'end': 3077.438, 'text': 'so we can put here error alert.', 'start': 3074.396, 'duration': 3.042}, {'end': 3106.16, 'text': "the title would be, let's say, invalid input input and error message will be please, input values between one between zero and 100 and press ok,", 'start': 3077.438, 'duration': 28.722}, {'end': 3110.227, 'text': 'now we go beyond our limit and press enter.', 'start': 3106.16, 'duration': 4.067}], 'summary': 'Customize error message for data validation, with input range 0-100.', 'duration': 46.66, 'max_score': 3063.567, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3063567.jpg'}], 'start': 2737.953, 'title': "Excel's conditional formatting, data validation, and customization", 'summary': 'Covers conditional formatting to compare and format values, such as highlighting above-average values in blue, data validation to control input values, and customization for user guidance in excel.', 'chapters': [{'end': 2939.584, 'start': 2737.953, 'title': 'Conditional formatting and data validation in excel', 'summary': 'Explains how conditional formatting in excel allows for setting rules to compare and format values, such as highlighting values above average in blue, and how data validation enables controlling input values, like limiting name length to 20 characters.', 'duration': 201.631, 'highlights': ['Conditional formatting allows setting rules to compare and format values, like highlighting values above average in blue. It enables setting rules to compare and format values, such as highlighting values above average in blue, and it helps in data analysis and visualization.', 'Data validation restricts input values, like limiting name length to 20 characters and displaying an error message for invalid inputs. It enables controlling input values, like limiting name length to 20 characters, and it displays an error message for invalid inputs, ensuring data accuracy and consistency.']}, {'end': 3260.949, 'start': 2939.584, 'title': 'Data validation and customization', 'summary': 'Explains how to use data validation to create drop-down lists for gender, set range limits for marks, and customize error and input messages, ensuring data accuracy and user guidance in excel.', 'duration': 321.365, 'highlights': ['Data validation can be used to create a drop-down list for the gender column, simplifying the selection to male or female, and ensuring only valid inputs are accepted. The speaker demonstrates using data validation to create a drop-down list for the gender column, limiting the options to male and female, and explaining that any other input would be considered invalid.', 'Setting a range for marks criteria using data validation ensures that only specified whole or decimal numbers within the defined range are accepted, preventing invalid inputs. The speaker illustrates the use of data validation to set a range for marks criteria, allowing only whole or decimal numbers within the specified range, and showcasing how inputs beyond the range trigger an error message.', 'Customizing error and input messages in data validation helps users understand the required data input, reducing errors and providing clear guidance. The speaker explains the process of customizing error and input messages in data validation, emphasizing how it helps users understand the required data input and reduces errors by providing clear guidance.']}], 'duration': 522.996, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws2737953.jpg', 'highlights': ['Conditional formatting enables setting rules to compare and format values, like highlighting values above average in blue.', 'Data validation restricts input values, like limiting name length to 20 characters and displaying an error message for invalid inputs.', 'Data validation can be used to create a drop-down list for the gender column, simplifying the selection to male or female, and ensuring only valid inputs are accepted.', 'Setting a range for marks criteria using data validation ensures that only specified whole or decimal numbers within the defined range are accepted, preventing invalid inputs.', 'Customizing error and input messages in data validation helps users understand the required data input, reducing errors and providing clear guidance.']}, {'end': 3928.837, 'segs': [{'end': 3290.663, 'src': 'embed', 'start': 3260.949, 'weight': 0, 'content': [{'end': 3271.074, 'text': "now let's say that the data set is quite big, that you can't manually put every single name with comma in between if you want to make a list.", 'start': 3260.949, 'duration': 10.125}, {'end': 3275.177, 'text': 'so in that case we can put the source as another data set link.', 'start': 3271.074, 'duration': 4.103}, {'end': 3277.138, 'text': "so let's see how to do that.", 'start': 3275.177, 'duration': 1.961}, {'end': 3280.359, 'text': "i'll just go back.", 'start': 3277.138, 'duration': 3.221}, {'end': 3287.34, 'text': "i'll click on this, select the range, then i'll click on data validation.", 'start': 3280.359, 'duration': 6.981}, {'end': 3290.663, 'text': "i'll allow it as list the source.", 'start': 3287.34, 'duration': 3.323}], 'summary': 'Demonstrates using data set link as source for list data validation.', 'duration': 29.714, 'max_score': 3260.949, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3260949.jpg'}, {'end': 3446.925, 'src': 'embed', 'start': 3391.632, 'weight': 1, 'content': [{'end': 3400.276, 'text': 'So you can group them and do advanced calculations like sum, average, maxima, minima and different other things as well.', 'start': 3391.632, 'duration': 8.644}, {'end': 3404.384, 'text': 'So now we look at an example of how pivot tables help us.', 'start': 3400.782, 'duration': 3.602}, {'end': 3406.685, 'text': "So let's go to Microsoft Excel.", 'start': 3404.924, 'duration': 1.761}, {'end': 3411.128, 'text': 'So now here we have a spreadsheet in which we have a data set.', 'start': 3407.106, 'duration': 4.022}, {'end': 3415.71, 'text': 'So this data set is about different products being sold.', 'start': 3411.848, 'duration': 3.862}, {'end': 3418.912, 'text': 'So in this case, we have a product line defined.', 'start': 3416.711, 'duration': 2.201}, {'end': 3423.495, 'text': 'So it is classic cars motorcycles and a lot of other things.', 'start': 3419.112, 'duration': 4.383}, {'end': 3429.618, 'text': 'So first of all, this is a type of data set that you would encounter when you do data analytics.', 'start': 3424.035, 'duration': 5.583}, {'end': 3432.241, 'text': 'So this is a classic example.', 'start': 3430.12, 'duration': 2.121}, {'end': 3437.943, 'text': 'So there would be a lot of columns that you would encounter and n number of rows.', 'start': 3432.541, 'duration': 5.402}, {'end': 3440.563, 'text': 'So in this case, let me check how many rows are there.', 'start': 3438.003, 'duration': 2.56}, {'end': 3446.925, 'text': 'It is close to 3,000 2800 close to 3,000 rows.', 'start': 3440.663, 'duration': 6.262}], 'summary': 'Pivot tables in excel help analyze a dataset of products, with close to 3,000 rows.', 'duration': 55.293, 'max_score': 3391.632, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3391632.jpg'}, {'end': 3600.531, 'src': 'embed', 'start': 3569.371, 'weight': 4, 'content': [{'end': 3570.852, 'text': "So we'll click on pivot table here.", 'start': 3569.371, 'duration': 1.481}, {'end': 3572.753, 'text': 'Dialog box open.', 'start': 3570.872, 'duration': 1.881}, {'end': 3576.194, 'text': 'Now in this case, we are asked certain questions.', 'start': 3573.173, 'duration': 3.021}, {'end': 3578.055, 'text': 'So select the table or range.', 'start': 3576.314, 'duration': 1.741}, {'end': 3581.536, 'text': 'Now Excel automatically detects your table.', 'start': 3578.535, 'duration': 3.001}, {'end': 3589.079, 'text': 'So as you can see in the data set, you would have a running line, right? Running box sort of thing here at the edges.', 'start': 3581.596, 'duration': 7.483}, {'end': 3593.84, 'text': 'So it automatically selects all the spreadsheet data set.', 'start': 3589.519, 'duration': 4.321}, {'end': 3600.531, 'text': 'Now it would ask me, Where do I want the pivot table to be whether it is a new worksheet or existing worksheet.', 'start': 3594.241, 'duration': 6.29}], 'summary': 'Creating a pivot table in excel, with automatic range selection and worksheet options.', 'duration': 31.16, 'max_score': 3569.371, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3569371.jpg'}], 'start': 3260.949, 'title': 'Data validation, pivot tables', 'summary': 'Covers using data validation to link a large dataset, its drawbacks, and introduces pivot tables. it also explains how pivot tables are used to organize and analyze large datasets in microsoft excel, including creating, manipulating data, and achieving specific objectives.', 'chapters': [{'end': 3348.276, 'start': 3260.949, 'title': 'Using data validation and pivot tables', 'summary': 'Demonstrates how to utilize data validation to link a large data set to create a list, showcasing its drawbacks, and introduces pivot tables.', 'duration': 87.327, 'highlights': ['The chapter explains utilizing data validation to link a large data set to create a list, allowing the selection of a secondary source and highlighting the inability to delete the primary data set, thereby showcasing its drawbacks.', 'It introduces the concept of pivot tables for data analysis.']}, {'end': 3928.837, 'start': 3348.276, 'title': 'Pivot tables in excel', 'summary': 'Explains how pivot tables are used to organize and analyze large datasets in microsoft excel, including the difference between normal tables and pivot tables, the process of creating a pivot table, and how to manipulate the data within the pivot table to achieve specific objectives.', 'duration': 580.561, 'highlights': ['Pivot tables automatically summarize data and enable the transformation of rows into columns and vice versa, allowing for advanced calculations like sum, average, maxima, and minima. Pivot tables automate data summarization and enable advanced calculations, such as sum, average, maxima, and minima, simplifying data analysis.', 'The dataset used in the example contains close to 3,000 rows and various columns, representing a typical scenario in data analytics. The dataset used in the example contains close to 3,000 rows and is representative of typical data encountered in data analytics scenarios.', 'The process of creating a pivot table in Microsoft Excel involves selecting the table or range, choosing the fields to include, and specifying the type of calculations to be applied, such as sum of sales or quantity ordered. Creating a pivot table involves selecting the table or range, choosing fields to include, and specifying calculations, like sum of sales or quantity ordered.']}], 'duration': 667.888, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3260949.jpg', 'highlights': ['The chapter explains utilizing data validation to link a large data set to create a list, showcasing its drawbacks.', 'Introduces the concept of pivot tables for data analysis.', 'Pivot tables automatically summarize data and enable advanced calculations, simplifying data analysis.', 'The dataset used in the example contains close to 3,000 rows and is representative of typical data encountered in data analytics scenarios.', 'Creating a pivot table involves selecting the table or range, choosing fields to include, and specifying calculations.']}, {'end': 4981.177, 'segs': [{'end': 3958.317, 'src': 'embed', 'start': 3928.837, 'weight': 0, 'content': [{'end': 3935.639, 'text': 'so it will give me the quantity of different things sold in different months of different years.', 'start': 3928.837, 'duration': 6.802}, {'end': 3937.579, 'text': "so in this way i'll get the quantity.", 'start': 3935.639, 'duration': 1.94}, {'end': 3942.109, 'text': 'So many more things we can do when it comes to the use of pivot tables.', 'start': 3937.967, 'duration': 4.142}, {'end': 3944.11, 'text': 'So this was one of the examples.', 'start': 3942.669, 'duration': 1.441}, {'end': 3949.933, 'text': 'Third and the final phase of your data analytics journey is data visualization.', 'start': 3945.291, 'duration': 4.642}, {'end': 3958.317, 'text': 'Now considering your data analysis is done and you are fit to represent your data to your internal and external stakeholders.', 'start': 3950.513, 'duration': 7.804}], 'summary': 'Pivot tables provide quantities of items sold by month and year, aiding in data visualization.', 'duration': 29.48, 'max_score': 3928.837, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3928837.jpg'}, {'end': 4018.68, 'src': 'embed', 'start': 3991.502, 'weight': 1, 'content': [{'end': 3995.925, 'text': 'and different scatter plots and even some advanced charts like map charts and tree charts.', 'start': 3991.502, 'duration': 4.423}, {'end': 4003.83, 'text': 'So considering all these scenarios, now it becomes your responsibility to make sure all your analysis is pictorially represented.', 'start': 3996.445, 'duration': 7.385}, {'end': 4006.672, 'text': "So let's move on to data visualization.", 'start': 4004.29, 'duration': 2.382}, {'end': 4010.535, 'text': 'The first chart that we will be talking about is a pie chart.', 'start': 4007.393, 'duration': 3.142}, {'end': 4013.737, 'text': 'Now pie chart represents part to whole relationship.', 'start': 4010.875, 'duration': 2.862}, {'end': 4018.68, 'text': "So since it's a very basic type of chart in Excel, we learn about it with an example.", 'start': 4014.017, 'duration': 4.663}], 'summary': 'Data visualization is crucial, including pie charts for part-to-whole relationships; important for analysis.', 'duration': 27.178, 'max_score': 3991.502, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3991502.jpg'}, {'end': 4138.353, 'src': 'embed', 'start': 4106.158, 'weight': 2, 'content': [{'end': 4111.281, 'text': 'So these are the three major components of a pie chart, but Excel does it itself.', 'start': 4106.158, 'duration': 5.123}, {'end': 4115.162, 'text': 'So you just need to feed values and Excel will make a pie chart for you.', 'start': 4111.64, 'duration': 3.522}, {'end': 4120.846, 'text': 'The second chart that is the most important and most you widely used is a bar chart.', 'start': 4115.823, 'duration': 5.023}, {'end': 4126.991, 'text': 'Now bar chart uses bands of different heights to show different amounts so that they can be distinguished.', 'start': 4121.267, 'duration': 5.724}, {'end': 4138.353, 'text': 'So as you can see one side the y-axis here has values numerical values in it and x-axis has basically the categories in which it is divided.', 'start': 4127.411, 'duration': 10.942}], 'summary': 'Excel can create pie charts from given values. bar charts show amounts using different heights for distinction.', 'duration': 32.195, 'max_score': 4106.158, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws4106158.jpg'}, {'end': 4675.912, 'src': 'embed', 'start': 4633.148, 'weight': 3, 'content': [{'end': 4638.511, 'text': "So India has the maximum amount of value in a particular cell that that's why it has the maximum.", 'start': 4633.148, 'duration': 5.363}, {'end': 4646.575, 'text': 'Maximum area second is Saudi Arabia, then Russia Germany, Australia, China and America and Spain.', 'start': 4639.872, 'duration': 6.703}, {'end': 4654.563, 'text': 'So the ones with bigger values are towards the left side of the map and the ones with are less value towards the right side.', 'start': 4647, 'duration': 7.563}, {'end': 4658.865, 'text': 'So that is why it gives a good comparing proportions within the hierarchy.', 'start': 4655.043, 'duration': 3.822}, {'end': 4661.446, 'text': 'The second chart is called map chart.', 'start': 4659.365, 'duration': 2.081}, {'end': 4666.548, 'text': 'So it is used to navigate values and shows categories across different geographical regions.', 'start': 4661.726, 'duration': 4.822}, {'end': 4672.471, 'text': 'So in this case, we will use only the names of countries to represent any data set.', 'start': 4666.988, 'duration': 5.483}, {'end': 4675.912, 'text': 'So the countries will be highlighted according to the values.', 'start': 4672.871, 'duration': 3.041}], 'summary': 'India has the maximum value in a cell, followed by saudi arabia, russia, germany, australia, china, america, and spain in a map chart, used for comparing proportions within the hierarchy.', 'duration': 42.764, 'max_score': 4633.148, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws4633148.jpg'}], 'start': 3928.837, 'title': 'Visualizing and analyzing data', 'summary': 'Covers pivot tables for analyzing sales data, data visualization techniques like pie, bar, and line charts, and creating advanced charts such as tree maps and map charts in excel, emphasizing their significance for data representation and analysis.', 'chapters': [{'end': 3969.543, 'start': 3928.837, 'title': 'Pivot tables and data visualization', 'summary': 'Discusses the use of pivot tables to analyze quantities of different items sold in various months and years, alongside the importance of data visualization for representing data to internal and external stakeholders.', 'duration': 40.706, 'highlights': ['Pivot tables used to analyze quantities of different items sold in various months and years.', 'Data visualization is important for representing data to internal and external stakeholders.', 'Internal stakeholders include boss, senior managers, and top brass of the company.', 'External stakeholders are clients for whom the project has been allotted.']}, {'end': 4197.097, 'start': 3970.083, 'title': 'Data visualization techniques', 'summary': 'Discusses the importance of using pictorial representations for data analysis, focusing on pie charts, bar charts, and line charts, and their respective key components and applications.', 'duration': 227.014, 'highlights': ['Pie chart represents part to whole relationship and uses area, central angle, and arc length to display data, making it suitable for basic data visualization in Excel. The pie chart visually represents the distribution of apples among friends, demonstrating the part-to-whole relationship and the use of area, central angle, and arc length to represent the data.', 'Bar chart uses bands of different heights to show different amounts, making it effective for comparing differences between categories or values, and is widely used for data visualization. The bar chart is effective for comparing differences between categories and widely used for data visualization, utilizing bands of different heights to represent varying amounts.', 'Line chart represents changes over time, connecting data points to show trends and variations, making it suitable for displaying trends and changes over a certain amount of time. The line chart visually connects data points to display changes over time, making it suitable for demonstrating trends and variations over a certain period.']}, {'end': 4587.471, 'start': 4197.177, 'title': 'Creating pie, bar, and pivot charts in excel', 'summary': 'Demonstrates the process of creating pie charts, bar charts, and pivot charts in excel, including selecting chart types, modifying chart styles, and adding axis titles, with a focus on using different charting patterns and filtering options in pivot charts.', 'duration': 390.294, 'highlights': ['The chapter demonstrates the process of creating pie charts, bar charts, and pivot charts in Excel. The process of creating pie charts, bar charts, and pivot charts in Excel is explained, showcasing the selection of chart types and the use of different charting patterns and filtering options in pivot charts.', 'The importance of using different charting patterns and filtering options in pivot charts is highlighted. The importance of using different charting patterns and filtering options in pivot charts is emphasized for customizing the visualization of data in pivot charts.', 'The process of modifying chart styles and adding axis titles in bar charts is explained. The process of modifying chart styles and adding axis titles in bar charts is detailed, demonstrating the steps to visually customize and label bar charts in Excel.', 'The chapter explains the selection of chart types and the process of modifying chart styles in Excel. The chapter explains the selection of chart types and the process of modifying chart styles, emphasizing the visual customization of charts for effective data representation.', 'The process of creating pie charts and selecting chart types in Excel is detailed. The process of creating pie charts and selecting chart types in Excel is outlined, showcasing the steps to choose chart types and customize pie charts for data visualization.']}, {'end': 4981.177, 'start': 4588.683, 'title': 'Advanced excel charts', 'summary': 'Explores the usage of tree maps and map charts in excel, highlighting their hierarchical data visualization and geographical data representation, commonly used by organizations like world bank and united nations.', 'duration': 392.494, 'highlights': ['The tree map provides a hierarchical view of data, enabling comparison of proportions within the hierarchy, with India having the maximum value followed by Saudi Arabia, Russia, Germany, Australia, China, America, and Spain. The tree map visually represents data hierarchy, allowing comparison of proportions, with India having the maximum value followed by Saudi Arabia, Russia, Germany, Australia, China, America, and Spain.', 'The map chart is used to navigate values and display categories across geographical regions, commonly used by organizations like World Bank and United Nations, as well as companies involved in logistical supplies, imports, and exports. The map chart is utilized for geographical data representation, commonly used by organizations like World Bank and United Nations, as well as companies involved in logistical supplies, imports, and exports.', 'The chapter also covers various other topics, such as data cleaning, basic Excel functionalities, data analysis techniques, pivot tables, conditional formatting, and data validation. The chapter covers topics including data cleaning, basic Excel functionalities, data analysis techniques, pivot tables, conditional formatting, and data validation.']}], 'duration': 1052.34, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FJRm4LZv5Ws/pics/FJRm4LZv5Ws3928837.jpg', 'highlights': ['Pivot tables used to analyze quantities of different items sold in various months and years.', 'Data visualization is important for representing data to internal and external stakeholders.', 'The chapter demonstrates the process of creating pie charts, bar charts, and pivot charts in Excel.', 'The tree map provides a hierarchical view of data, enabling comparison of proportions within the hierarchy, with India having the maximum value followed by Saudi Arabia, Russia, Germany, Australia, China, America, and Spain.', 'The map chart is used to navigate values and display categories across geographical regions, commonly used by organizations like World Bank and United Nations, as well as companies involved in logistical supplies, imports, and exports.']}], 'highlights': ['Great Learning offers a course on data analytics to help individuals start their data analytics journey and pursue their career goals, indicating the opportunity for skill development and job placement.', "Data is generated every minute from various activities, highlighting the abundance of data in today's world.", 'Data analytics has become a highly sought-after domain, reflecting the increasing demand for professionals in this field.', 'The removal of duplicates is a crucial step in data cleaning, as it ensures the accuracy and uniformity of the dataset, with 23 unique values left after removing two duplicates.', 'The chapter covers the three phases of a data analytics project: data cleaning, data analysis, and data visualization. It emphasizes the importance of data cleaning and the need to set up a problem statement aimed at solving a specific business problem.', 'The initial analysis on data involves checking for completeness, consistency, and accuracy. It highlights the importance of performing an initial analysis on data to ensure completeness, consistency, and accuracy.', 'The process of sorting the employee salary column from lowest to highest in Microsoft Excel reveals irregularities, such as an unrealistic salary value of one lakh one thousand seven hundred two, emphasizing the need for thorough examination and correction of data inconsistencies.', 'Functions in Excel streamline calculations like addition, subtraction, multiplication, and average, saving time and effort in manual calculations.', 'The countif function efficiently counts the number of cells matching a specific condition in large datasets.', 'The chapter demonstrates filtering data based on specific values, such as language in a movie dataset.', 'The VLOOKUP function in Microsoft Excel is demonstrated, showcasing its syntax and usage for finding employee salaries and years of experience based on employee IDs, with examples of successful lookups for specific employee IDs and their corresponding data.', 'The chapter provides a step-by-step demonstration of using HLOOKUP in Microsoft Excel to retrieve the price of different office supplies, showcasing its practical application and providing clarity on the process.', 'The chapter explains conditional formatting in Microsoft Excel, enabling users to format datasets based on specific conditions, such as highlighting cells with values greater than a certain threshold, identifying top and bottom values, using data bars, applying color scales, and using icon sets to represent data.', 'Data validation restricts input values, like limiting name length to 20 characters and displaying an error message for invalid inputs.', 'Introduces the concept of pivot tables for data analysis.', 'Pivot tables automatically summarize data and enable advanced calculations, simplifying data analysis.', 'Data visualization is important for representing data to internal and external stakeholders.', 'The chapter demonstrates the process of creating pie charts, bar charts, and pivot charts in Excel.', 'The tree map provides a hierarchical view of data, enabling comparison of proportions within the hierarchy, with India having the maximum value followed by Saudi Arabia, Russia, Germany, Australia, China, America, and Spain.']}