title
Microsoft Excel Tutorial for Beginners | Excel Training | Excel Formulas and Functions | Edureka

description
🔥Advanced MS Excel Certification Training (Use Code "𝐘𝐎𝐔𝐓𝐔𝐁𝐄𝟐𝟎") : https://www.edureka.co/search 🔴 Subscribe to Edureka YouTube channel for latest Excel video updates: http://bit.ly/2ADbbb1 This Edureka video on "Excel Full Course" is a complete guide for learning Microsoft Excel which covers in-depth knowledge about different excel formulas and functions, conditional formatting, excel Lookup, excel macros & VBA in Ms Excel. Below are the topics covered in this Excel Tutorial: 00:00 Introduction 1:08 Excel Functions 1:08 SUM Function 3:58 SUMIF Function 13:27 COUNT Function 17:27 COUNTIF Function 19:37 COUNTIFS Function 22:22 AVERAGE Function 27:17 ROUND Function 31:52 CONCATENATE Function 37:17 INDEX Function 40:02 OFFSET Function 44:57 MATCH Function 51:07 MAX & TRIM Function 53:57 LEN Function 55:07 SQRT Function 56:12 IF Function 1:07:42 AND Function 1:12:32 TODAY Function 1:13:17 Quick Access Bar 1:18:37 Conditional Fomatting 1:37:58 Color Scale 1:38:33 Using Excel Formulas for Conditional Formatting 1:47:48 Logical Excel Functions 1:57:58 IFERROR Function 2:00:43 IFNA Function 2:03:41 NESTED IF Function 1:11:46 TEXT Functions 2:11:56 LOWER Function 2:13:16 UPPER Function 2:16:46 RANDBETWEEN Function 2:17:31 NAME RANGEs IN Excel 2:20:11 Lookup Functions 2:20:26 VLOOKUP Function 2:22:51 HLOOKUP Function 2:23:56 Formula Auditing 2:24:01 Trace Precedents & Dependents 2:25:26 Error Checking 2:26:46 Show Formulas 2:27:16 Text To Column 2:29:51 Data Validation 2:40:16 What-if Tools 2:49:42 Scenario Manager 2:54:27 Data Table 2:56:17 SORT & FILTER 3:00:02 WRAP Text 3:01:22 Merge Cell & Insert Comments 3:11:52 Large Function 3:23:32 Charts In Excel 3:41:32 Pivot Tables 3:56:18 Data Analysis Using Excel 3:58:38 Slicers In Pivot Tables 4:02:09 VBA & Macros 4:15:19 Assign A Macros 4:15:24 Record A Macros 4:15:29 Debugging Check out Edureka Excel Tutorial playlist here: https://bit.ly/34uWKDA Check out Edureka Excel Tutorial blog list here: https://bit.ly/2xpc1K7 📢📢 𝐓𝐨𝐩 𝟏𝟎 𝐓𝐫𝐞𝐧𝐝𝐢𝐧𝐠 𝐓𝐞𝐜𝐡𝐧𝐨𝐥𝐨𝐠𝐢𝐞𝐬 𝐭𝐨 𝐋𝐞𝐚𝐫𝐧 𝐢𝐧 𝟐𝟎𝟐𝟒 𝐒𝐞𝐫𝐢𝐞𝐬 📢📢 ⏩ NEW Top 10 Technologies To Learn In 2024 - https://www.youtube.com/watch?v=vaLXPv0ewHU 📌𝐓𝐞𝐥𝐞𝐠𝐫𝐚𝐦: https://t.me/edurekaupdates 📌𝐓𝐰𝐢𝐭𝐭𝐞𝐫: https://twitter.com/edurekain 📌𝐋𝐢𝐧𝐤𝐞𝐝𝐈𝐧: https://www.linkedin.com/company/edureka 📌𝐈𝐧𝐬𝐭𝐚𝐠𝐫𝐚𝐦: https://www.instagram.com/edureka_learning/ 📌𝐅𝐚𝐜𝐞𝐛𝐨𝐨𝐤: https://www.facebook.com/edurekaIN/ 📌𝐒𝐥𝐢𝐝𝐞𝐒𝐡𝐚𝐫𝐞: https://www.slideshare.net/EdurekaIN 📌𝐂𝐚𝐬𝐭𝐛𝐨𝐱: https://castbox.fm/networks/505?country=IN 📌𝐌𝐞𝐞𝐭𝐮𝐩: https://www.meetup.com/edureka/ 📌𝐂𝐨𝐦𝐦𝐮𝐧𝐢𝐭𝐲: https://www.edureka.co/community/ - - - - - - - - - - - - - - - - - #edureka #exceledureka #excelTutorial #excelTutorialForBeginners #excel #excelCompleteCourse For more information, Please write back to us at sales@edureka.in or call us at IND: 9606058406 / US: +18338555775 (toll-free).

detail
{'title': 'Microsoft Excel Tutorial for Beginners | Excel Training | Excel Formulas and Functions | Edureka', 'heatmap': [{'end': 8721.485, 'start': 8401.319, 'weight': 1}], 'summary': 'Tutorial on microsoft excel for beginners covers advanced excel functions and formulas, data analysis, formatting, logical functions, data tables, scenario manager, data analysis techniques, pivot table functionality, and excel vba and macro recording, demonstrating practical examples with quantifiable outcomes.', 'chapters': [{'end': 555.872, 'segs': [{'end': 48.441, 'src': 'embed', 'start': 11.425, 'weight': 0, 'content': [{'end': 14.668, 'text': 'Hi guys, welcome to this interesting session on MS Excel.', 'start': 11.425, 'duration': 3.243}, {'end': 19.792, 'text': "So in this session, you'll get a complete overview of advanced MS Excel 2016.", 'start': 14.908, 'duration': 4.884}, {'end': 26.057, 'text': "So we'll start this session by understanding custom and conditional formatting and then we'll go through advanced functions and formulas.", 'start': 19.792, 'duration': 6.265}, {'end': 34.064, 'text': "Once you understand these two topics, we'll go through advanced data tools and data analysis and finally end the session with introduction to macros.", 'start': 26.457, 'duration': 7.607}, {'end': 37.907, 'text': "So today we have a special guest Trisha who's going to take this session forward.", 'start': 34.444, 'duration': 3.463}, {'end': 39.228, 'text': 'So over to you Trisha.', 'start': 38.267, 'duration': 0.961}, {'end': 45.661, 'text': 'Hi, my name is Trisha Jaktiani and we are going to do the advanced module for 2016 Excel version.', 'start': 40.299, 'duration': 5.362}, {'end': 48.441, 'text': "So what's new in 2016?", 'start': 46.361, 'duration': 2.08}], 'summary': 'Advanced ms excel 2016 session covers custom formatting, functions, data tools, analysis, and macros, presented by trisha jaktiani.', 'duration': 37.016, 'max_score': 11.425, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11425.jpg'}, {'end': 158.172, 'src': 'embed', 'start': 132.226, 'weight': 3, 'content': [{'end': 136.348, 'text': 'the sum function will automatically incorporate the new data entered.', 'start': 132.226, 'duration': 4.122}, {'end': 137.808, 'text': 'Say we have a new employee.', 'start': 136.588, 'duration': 1.22}, {'end': 145.651, 'text': 'All right, so I enter the name of the employee here and I enter the employee salary just for our example as 50, 000 rupees.', 'start': 138.048, 'duration': 7.603}, {'end': 149.573, 'text': 'Once I enter the employee salary in the new column,', 'start': 146.091, 'duration': 3.482}, {'end': 158.172, 'text': 'what happens is you will see that the total salary that we had calculated in column J2 has automatically changed and it is now adding that 50,', 'start': 149.573, 'duration': 8.599}], 'summary': 'The sum function automatically updates with new data; e.g., entering 50,000 rupees for a new employee updates the total salary.', 'duration': 25.946, 'max_score': 132.226, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ132226.jpg'}, {'end': 255.109, 'src': 'embed', 'start': 225.842, 'weight': 5, 'content': [{'end': 229.726, 'text': 'anything that I am putting other than the build function numbers true or false or cell references.', 'start': 225.842, 'duration': 3.884}, {'end': 230.788, 'text': "I'll put something like this.", 'start': 229.786, 'duration': 1.002}, {'end': 235.473, 'text': 'So we will explore this in detail in the next function, which is the SUMIF function.', 'start': 231.448, 'duration': 4.025}, {'end': 241.279, 'text': 'The SUMIF function sums a range based on the single condition or the criteria.', 'start': 235.753, 'duration': 5.526}, {'end': 244.823, 'text': "So let's explore the function with the example on my sheet.", 'start': 241.86, 'duration': 2.963}, {'end': 250.767, 'text': 'In order to start with this function, First, we need to understand the term syntax.', 'start': 245.524, 'duration': 5.243}, {'end': 255.109, 'text': 'To understand any function for that matter, we will have to understand what is syntax.', 'start': 251.227, 'duration': 3.882}], 'summary': 'Exploring the sumif function to sum a range based on a single condition or criteria.', 'duration': 29.267, 'max_score': 225.842, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ225842.jpg'}, {'end': 302.943, 'src': 'embed', 'start': 277.978, 'weight': 2, 'content': [{'end': 286.147, 'text': 'So if you see when I open the SUMIF function, for the SUMIF function, the inputs required are range, criteria and the sum range.', 'start': 277.978, 'duration': 8.169}, {'end': 294.956, 'text': "Now taking the same example as used for the SUM function, let's say we need to find the total salaries paid to the employees in the south region.", 'start': 286.627, 'duration': 8.329}, {'end': 299.3, 'text': 'So if you can see our list, We have this list of employees.', 'start': 295.296, 'duration': 4.004}, {'end': 302.943, 'text': 'I just need to find all the people working in the South region.', 'start': 299.72, 'duration': 3.223}], 'summary': 'Explaining the inputs for the sumif function and using an example to find total salaries in the south region.', 'duration': 24.965, 'max_score': 277.978, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ277978.jpg'}, {'end': 411.909, 'src': 'embed', 'start': 382.483, 'weight': 4, 'content': [{'end': 386.587, 'text': "but you're trying to get the total salaries paid to the employees in the South region.", 'start': 382.483, 'duration': 4.104}, {'end': 392.912, 'text': 'We will have to select the sum range as salary, which is our edge to edge column close the bracket and press enter.', 'start': 386.647, 'duration': 6.265}, {'end': 398.462, 'text': 'Now what do we get here is the total salaries paid to the employees in the south region.', 'start': 393.459, 'duration': 5.003}, {'end': 403.945, 'text': "This is only one answer that I require for the whole list and that's the reason I have put it in this column.", 'start': 398.622, 'duration': 5.323}, {'end': 407.707, 'text': "Let's look at another example for the same SUMIF function.", 'start': 404.505, 'duration': 3.202}, {'end': 411.909, 'text': 'We need a total salary of the employees whose salary is more than 15, 000 rupees.', 'start': 408.207, 'duration': 3.702}], 'summary': 'Using sumif to find total salaries in south region and above 15,000 rupees', 'duration': 29.426, 'max_score': 382.483, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ382483.jpg'}], 'start': 11.425, 'title': 'Advanced ms excel 2016 and excel functions: sum and sumif', 'summary': 'Provides an overview of advanced ms excel 2016, including custom and conditional formatting, advanced functions and formulas, data tools and analysis, and new features. it introduces the sum function for calculating total salary and explains the usage of sumif function with examples.', 'chapters': [{'end': 87.307, 'start': 11.425, 'title': 'Advanced ms excel 2016 overview', 'summary': 'Provides an overview of advanced ms excel 2016, including topics such as custom and conditional formatting, advanced functions and formulas, data tools and analysis, and new features in 2016 excel like new charts, smart lookup, enhanced pivot tables, and additional functions. trisha jaktiani introduces the session and highlights the sum function as an important feature, demonstrating its usage in calculating the total salary for all employees.', 'duration': 75.882, 'highlights': ['Trisha Jaktiani introduces the session and highlights the new features in 2016 Excel, including new charts like waterfall, Pareto, whiskers, features like tell me, smart lookup, enhanced pivot table with multi-selection options for slices, search functions, and a pivot field list, and additional functions.', 'The chapter covers topics such as custom and conditional formatting, advanced functions and formulas, advanced data tools and data analysis, and introduction to macros.', 'Trisha Jaktiani emphasizes the importance of the SUM function in MS Excel and demonstrates its usage in calculating the total salary for all employees in the organization.']}, {'end': 555.872, 'start': 88.027, 'title': 'Excel functions: sum and sumif', 'summary': "Covers the use of excel's sum function to calculate the total salary paid to employees, and the explanation of syntax and usage of the sumif function to find the total salaries based on specific criteria, using examples and detailed explanations.", 'duration': 467.845, 'highlights': ['Explanation of using the SUM function to calculate the total salary paid to employees', 'Explanation of the foundational concepts in Excel, including inbuilt functions, numbers, true or false, and cell references', 'Explanation of the SUMIF function and its practical application', 'Detailed explanation of the syntax and arguments required for using the SUMIF function', 'Demonstration of using double quotes for non-numeric criteria in the SUMIF function']}], 'duration': 544.447, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11425.jpg', 'highlights': ['Trisha Jaktiani introduces new features in Excel 2016, including new charts and enhanced pivot table options.', 'The chapter covers custom and conditional formatting, advanced functions, data tools, and macros.', 'Importance of the SUM function in Excel for calculating total salary is emphasized.', 'Explanation of using the SUM function to calculate total employee salary.', 'Detailed explanation of the SUMIF function and its practical application.', 'Demonstration of using double quotes for non-numeric criteria in the SUMIF function.']}, {'end': 2476.049, 'segs': [{'end': 584.029, 'src': 'embed', 'start': 555.872, 'weight': 1, 'content': [{'end': 559.155, 'text': 'hope this clarifies how to use the summit function.', 'start': 555.872, 'duration': 3.283}, {'end': 567.764, 'text': 'Just to summarize, SUMIF function will give you the total of any cell reference or cell range where a criteria is met.', 'start': 559.901, 'duration': 7.863}, {'end': 574.126, 'text': "Okay Let's see what is the next formula or function that we have on our list, which is the SUMIF function.", 'start': 568.104, 'duration': 6.022}, {'end': 584.029, 'text': 'What is the SUMIF function? Again, just like the SUM function, SUMIF function is the function to some cells that meet multiple criterias.', 'start': 574.566, 'duration': 9.463}], 'summary': 'The sumif function calculates the total of cells meeting specific criteria.', 'duration': 28.157, 'max_score': 555.872, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ555872.jpg'}, {'end': 676.102, 'src': 'embed', 'start': 641.907, 'weight': 3, 'content': [{'end': 645.03, 'text': 'The second criteria that we have here is the south region.', 'start': 641.907, 'duration': 3.123}, {'end': 647.832, 'text': 'So the second criteria becomes region as south.', 'start': 645.41, 'duration': 2.422}, {'end': 653.657, 'text': 'And the third criteria that I have now here is salary is more than 10, 000.', 'start': 649.614, 'duration': 4.043}, {'end': 655.639, 'text': 'So I just put the greater than sign.', 'start': 653.657, 'duration': 1.982}, {'end': 657.76, 'text': 'Okay, so that becomes our three criteria.', 'start': 655.919, 'duration': 1.841}, {'end': 664.446, 'text': 'As I have three criteria, SUMIF function will not work because SUMIF function is only used to get outputs for single criteria.', 'start': 658.361, 'duration': 6.085}, {'end': 666.928, 'text': 'So we will be using the SUMIF function.', 'start': 664.866, 'duration': 2.062}, {'end': 676.102, 'text': 'The input required for the SUMIFS function are the first thing that it requires is the sum range which in our case is the salary.', 'start': 667.498, 'duration': 8.604}], 'summary': 'Using sumifs function with 3 criteria, including salary > 10,000.', 'duration': 34.195, 'max_score': 641.907, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ641907.jpg'}, {'end': 815.321, 'src': 'embed', 'start': 785.501, 'weight': 2, 'content': [{'end': 789.383, 'text': 'Close the double quotes, close our bracket, enter.', 'start': 785.501, 'duration': 3.882}, {'end': 791.544, 'text': "You'll get the total salary as 55, 825 rupees.", 'start': 789.583, 'duration': 1.961}, {'end': 803.304, 'text': 'This is the total salary paid to the employees in the sales department working for the south region and if they are earning salary more than 10,', 'start': 794.249, 'duration': 9.055}, {'end': 804.075, 'text': '000 rupees.', 'start': 803.304, 'duration': 0.771}, {'end': 808.657, 'text': 'Moving on to the next one which is our count function.', 'start': 804.695, 'duration': 3.962}, {'end': 815.321, 'text': 'The count function as the name suggests provides the count of the sales containing numeric value.', 'start': 809.298, 'duration': 6.023}], 'summary': 'Total salary: 55,825 rupees for sales dept. in south region. count function: provides count of sales with numeric value.', 'duration': 29.82, 'max_score': 785.501, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ785501.jpg'}, {'end': 1163.792, 'src': 'embed', 'start': 1135.707, 'weight': 7, 'content': [{'end': 1138.328, 'text': "So I'll put Edwin again as I told you earlier.", 'start': 1135.707, 'duration': 2.621}, {'end': 1140.429, 'text': 'These are not case sensitive.', 'start': 1138.608, 'duration': 1.821}, {'end': 1144.826, 'text': 'so you can put admin in small letters and double quotes.', 'start': 1140.805, 'duration': 4.021}, {'end': 1145.947, 'text': 'close the bracket.', 'start': 1144.826, 'duration': 1.121}, {'end': 1147.207, 'text': 'enter the total.', 'start': 1145.947, 'duration': 1.26}, {'end': 1150.288, 'text': 'employees working in the admin department are 11.', 'start': 1147.207, 'duration': 3.081}, {'end': 1158.111, 'text': 'okay, just to cross verify, we go and do a filter identify how many employees are there in the admin department.', 'start': 1150.288, 'duration': 7.823}, {'end': 1159.292, 'text': 'I filter by admin.', 'start': 1158.111, 'duration': 1.181}, {'end': 1161.912, 'text': 'I select the whole data now, only the admin department.', 'start': 1159.292, 'duration': 2.62}, {'end': 1163.792, 'text': 'if I select, it is showing me as 11..', 'start': 1161.912, 'duration': 1.88}], 'summary': '11 employees work in the admin department.', 'duration': 28.085, 'max_score': 1135.707, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ1135707.jpg'}, {'end': 1476.656, 'src': 'embed', 'start': 1444.693, 'weight': 0, 'content': [{'end': 1447.315, 'text': 'So it will not add up and it will not divide it.', 'start': 1444.693, 'duration': 2.622}, {'end': 1451.158, 'text': 'So we have a very good example, which is the employee code data.', 'start': 1447.755, 'duration': 3.403}, {'end': 1458.004, 'text': "If I'm just trying to get an average of this salary information, I will just get the same answer even if I do as average A.", 'start': 1451.518, 'duration': 6.486}, {'end': 1463.768, 'text': 'However, if I do average A with the employee data, where I have the text as well as the numbers,', 'start': 1458.004, 'duration': 5.764}, {'end': 1471.252, 'text': 'it will calculate the numbers which I have with just add all the numbers plus where I have the text, it will take it as true.', 'start': 1463.768, 'duration': 7.484}, {'end': 1476.656, 'text': 'Each text will be taken as one and if there is any blank space, it will take that as zero.', 'start': 1471.513, 'duration': 5.143}], 'summary': "Using 'average a' with employee data calculates numbers and treats text as true, considering each text as one and blank space as zero.", 'duration': 31.963, 'max_score': 1444.693, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ1444693.jpg'}, {'end': 1774.281, 'src': 'embed', 'start': 1747.638, 'weight': 5, 'content': [{'end': 1752.943, 'text': 'because the second number, which is 2, it is not either 5 or more than 5.', 'start': 1747.638, 'duration': 5.305}, {'end': 1754.625, 'text': "that's the reason the 7 is left as 7.", 'start': 1752.943, 'duration': 1.682}, {'end': 1757.848, 'text': 'okay, so this is showing is 1 more 4.7.', 'start': 1754.625, 'duration': 3.223}, {'end': 1759.349, 'text': 'this becomes a round function.', 'start': 1757.848, 'duration': 1.501}, {'end': 1761.091, 'text': 'the next one is the roundup function.', 'start': 1759.349, 'duration': 1.742}, {'end': 1765.413, 'text': 'Roundup function always rounds a number up away from zero.', 'start': 1761.63, 'duration': 3.783}, {'end': 1769.817, 'text': 'From our example, we want round a number up to one decimal point.', 'start': 1765.774, 'duration': 4.043}, {'end': 1774.281, 'text': 'We will input the formula in cell C2 is equal to roundup.', 'start': 1770.037, 'duration': 4.244}], 'summary': 'Explanation of round and roundup functions, using 4.7 as example.', 'duration': 26.643, 'max_score': 1747.638, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ1747638.jpg'}, {'end': 2110.931, 'src': 'embed', 'start': 2085.763, 'weight': 6, 'content': [{'end': 2092.485, 'text': 'As soon as that cross sign appears, I just need to double click on that data for it to copy it to the rest of the cells.', 'start': 2085.763, 'duration': 6.722}, {'end': 2100.929, 'text': 'Now when I double click on that data, it will only work on the cells if there is any data on the left or right of that cell.', 'start': 2093.246, 'duration': 7.683}, {'end': 2110.931, 'text': "So if I had entered the same formula in some other cell, say for example here, Enter now if I try to double click on this it doesn't work.", 'start': 2101.61, 'duration': 9.321}], 'summary': 'Double click copies data to cells if adjacent data exists.', 'duration': 25.168, 'max_score': 2085.763, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ2085763.jpg'}, {'end': 2217.231, 'src': 'embed', 'start': 2189.374, 'weight': 4, 'content': [{'end': 2195.918, 'text': 'Now you can select E1 or E2, whichever the first text is, then your space and the last name cell.', 'start': 2189.374, 'duration': 6.544}, {'end': 2199.32, 'text': 'This is how you can use any function for that matter.', 'start': 2196.318, 'duration': 3.002}, {'end': 2200.821, 'text': 'You can also do SUMIF with this.', 'start': 2199.34, 'duration': 1.481}, {'end': 2204.564, 'text': 'If I put SUM, it will give me all the functions that is starting with SUM.', 'start': 2201.302, 'duration': 3.262}, {'end': 2208.147, 'text': 'So I can use some a function it will automatically open the bracket for me.', 'start': 2205.046, 'duration': 3.101}, {'end': 2217.231, 'text': "This is only easier way or a trick to actually ensure that you don't miss out on opening the bracket or you don't make any spelling mistake when entering a function.", 'start': 2208.187, 'duration': 9.044}], 'summary': 'Select e1 or e2, then use sumif for functions. easier way to avoid mistakes.', 'duration': 27.857, 'max_score': 2189.374, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ2189374.jpg'}], 'start': 555.872, 'title': 'Using excel functions and formulas', 'summary': 'Covers the usage of sumif, count, countif, countifs, average, averagea, averageif, round, roundup, rounddown, concatenate, index, and offset functions in excel, with practical examples and detailed explanations, including a demonstration of using sumif to find total salaries of employees meeting specific criteria and an average salary of 20,334 rupees.', 'chapters': [{'end': 1113.384, 'start': 555.872, 'title': 'Understanding sumif, count, and countif', 'summary': 'Explains the sumif function for multiple criteria, using an example with three criteria to find the total salaries of employees in the sales department in the south region earning more than 10,000 rupees, followed by demonstrations of the count and counta functions, and the explanation of the countif function.', 'duration': 557.512, 'highlights': ['The example of using the SUMIF function with three criteria to find the total salaries of employees in the sales department in the South region earning more than 10,000 rupees.', 'Demonstration of the COUNT function to count the cells with numeric values in the salary column.', 'Explanation and demonstration of the COUNTA function to count the cells containing numeric data and text or characters, using the region column as an example.', 'Explanation and demonstration of the COUNTIF function, which counts cells in a range that meet a particular condition, using the example of identifying the number of employees working in the admin department.']}, {'end': 1842.913, 'start': 1113.84, 'title': 'Excel functions and formulas', 'summary': 'Covers the usage of countifs, average, averagea, averageif, round, roundup, and rounddown functions in excel, including practical examples and detailed explanations, with a total of 11 employees in the admin department, 9 employees in the marketing department earning salary more than 15,000 rupees, and an average salary of 20,334 rupees.', 'duration': 729.073, 'highlights': ['The COUNTIFS function is used to count the number of cell ranges that meet multiple conditions, such as identifying 11 employees in the admin department.', 'The AVERAGE function calculates the average of arguments, returning the average salary paid to the employees in the organization as 20,334 rupees.', 'The AVERAGEA function gives an average of cells containing numbers as well as text or characters, considering true as one and false as zero for calculation.', 'The AVERAGEIF function returns the average salary paid to the employees in the south region, showcasing the practical use of the function with a specific criteria.', 'The ROUND function is exemplified with examples of rounding numbers to a specified number of decimal points, demonstrating its functionality with practical examples.']}, {'end': 2189.014, 'start': 1842.913, 'title': 'Excel: rounding and concatenation', 'summary': 'Explains how to round down numbers in excel using the roundown function, and how to use the concatenate function to join text from different cells, with examples and a tip on avoiding spelling mistakes when entering functions.', 'duration': 346.101, 'highlights': ['The ROUNDOWN function in Excel is used to round down numbers by removing all decimal points, demonstrated with examples resulting in 114 and 114.72, also explaining how it handles numbers greater than 5.', 'The CONCATENATE function in Excel is explained with a demonstration of combining text from different cells, with a focus on including a space between the joined text and a tip on avoiding spelling mistakes when entering functions.', 'The use of the fill handle in Excel to copy a formula to adjacent cells is demonstrated, with an explanation of the conditions under which it can be used.', 'A tip is provided on avoiding spelling mistakes when entering functions in Excel by using the autocomplete feature to select the desired function from a list of suggestions.']}, {'end': 2476.049, 'start': 2189.374, 'title': 'Excel functions and tricks', 'summary': 'Explains how to use excel functions like sumif, concat, index, and offset with practical examples, providing insights into their usage and syntax.', 'duration': 286.675, 'highlights': ['The chapter demonstrates how to use the INDEX function to retrieve the quantity of a specific product, such as oranges, from a table, with a practical example resulting in a quantity of 10.', 'It explains the OFFSET function, highlighting the difference from the INDEX function and how it returns a reference to a range based on a given number of rows and columns from a given reference.', 'The chapter also covers practical tips like using functions for easier data entry and avoiding spelling mistakes, providing a comprehensive guide to leveraging Excel functions effectively.']}], 'duration': 1920.177, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ555872.jpg', 'highlights': ['Demonstration of using the SUMIF function with three criteria to find total salaries of employees in the sales department in the South region earning more than 10,000 rupees.', 'Explanation and demonstration of the COUNTA function to count the cells containing numeric data and text or characters, using the region column as an example.', 'The example of using the INDEX function to retrieve the quantity of a specific product, such as oranges, from a table, with a practical example resulting in a quantity of 10.', 'The COUNTIFS function is used to count the number of cell ranges that meet multiple conditions, such as identifying 11 employees in the admin department.', 'The AVERAGE function calculates the average of arguments, returning the average salary paid to the employees in the organization as 20,334 rupees.', 'The ROUNDOWN function in Excel is used to round down numbers by removing all decimal points, demonstrated with examples resulting in 114 and 114.72, also explaining how it handles numbers greater than 5.', 'The CONCATENATE function in Excel is explained with a demonstration of combining text from different cells, with a focus on including a space between the joined text and a tip on avoiding spelling mistakes when entering functions.', 'A tip is provided on avoiding spelling mistakes when entering functions in Excel by using the autocomplete feature to select the desired function from a list of suggestions.']}, {'end': 4155.827, 'segs': [{'end': 2739.346, 'src': 'embed', 'start': 2711.722, 'weight': 4, 'content': [{'end': 2716.124, 'text': "Let's use the match to figure out what row oranges is in.", 'start': 2711.722, 'duration': 4.402}, {'end': 2721.827, 'text': 'So what we did earlier was we were trying to find the quantity of the oranges is 10.', 'start': 2716.504, 'duration': 5.323}, {'end': 2727.889, 'text': 'This time I am trying to find which row does the orange product sit.', 'start': 2721.827, 'duration': 6.062}, {'end': 2729.47, 'text': 'So what does match do?', 'start': 2728.308, 'duration': 1.162}, {'end': 2731.453, 'text': 'is match will identify.', 'start': 2729.47, 'duration': 1.983}, {'end': 2739.346, 'text': 'if you give a name in the match function, like for example oranges, it will identify which row it is sitting under.', 'start': 2731.453, 'duration': 7.893}], 'summary': 'Using the match function to find the row of oranges product.', 'duration': 27.624, 'max_score': 2711.722, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ2711722.jpg'}, {'end': 2824.172, 'src': 'embed', 'start': 2781.442, 'weight': 7, 'content': [{'end': 2782.523, 'text': 'so what do I do?', 'start': 2781.442, 'duration': 1.081}, {'end': 2784.404, 'text': 'is I enter b to b?', 'start': 2782.523, 'duration': 1.881}, {'end': 2786.965, 'text': 'this way it will automatically look for it comma.', 'start': 2784.404, 'duration': 2.561}, {'end': 2793.6, 'text': 'as soon as I put comma, there is an option for less than exact match and greater than in match function.', 'start': 2786.965, 'duration': 6.635}, {'end': 2798.162, 'text': 'We have to look for the exact match of the look of value.', 'start': 2794.021, 'duration': 4.141}, {'end': 2802.484, 'text': 'So if it is oranges, it should only look for oranges and not anything else.', 'start': 2798.303, 'duration': 4.181}, {'end': 2806.146, 'text': 'So we will select the exact match close the bracket enter.', 'start': 2802.504, 'duration': 3.642}, {'end': 2807.867, 'text': 'It will give me a three answer.', 'start': 2806.366, 'duration': 1.501}, {'end': 2814.009, 'text': 'Now, if you go back to your index function, if you see the index function, the first thing that we gave was a 2 to D 7,', 'start': 2808.027, 'duration': 5.982}, {'end': 2815.79, 'text': 'because we are telling them that this table.', 'start': 2814.009, 'duration': 1.781}, {'end': 2817.371, 'text': 'look for the data in this table.', 'start': 2815.79, 'duration': 1.581}, {'end': 2818.791, 'text': 'Next one was row number.', 'start': 2817.751, 'duration': 1.04}, {'end': 2824.172, 'text': 'Why did we look at row number? Because we were looking at row number where the oranges sit, which is three.', 'start': 2818.951, 'duration': 5.221}], 'summary': "Using 'exact match' in function to find 'oranges' at row 3.", 'duration': 42.73, 'max_score': 2781.442, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ2781442.jpg'}, {'end': 3195.432, 'src': 'embed', 'start': 3172.418, 'weight': 13, 'content': [{'end': 3182.184, 'text': 'How do I ensure now that the data without the spaces is copied and pasted in these cells? What do I do for that is I select this data.', 'start': 3172.418, 'duration': 9.766}, {'end': 3186.127, 'text': 'I copy I go back to my cell where I have the original data.', 'start': 3182.504, 'duration': 3.623}, {'end': 3191.81, 'text': 'I click on this I right click on the first cell and there is something called a space special.', 'start': 3186.407, 'duration': 5.403}, {'end': 3193.011, 'text': 'Now, why am I doing a paste?', 'start': 3191.97, 'duration': 1.041}, {'end': 3195.432, 'text': 'special is because, if you see, there is a formula in this cell.', 'start': 3193.011, 'duration': 2.421}], 'summary': "Copy and paste data without spaces using 'space special' function to maintain formulas.", 'duration': 23.014, 'max_score': 3172.418, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ3172418.jpg'}, {'end': 3256.821, 'src': 'embed', 'start': 3230.47, 'weight': 14, 'content': [{'end': 3236.669, 'text': 'So moving on now to the next function on our list, which is the len function.', 'start': 3230.47, 'duration': 6.199}, {'end': 3241.334, 'text': 'What does the len function do? Len function returns the number of characters in a string.', 'start': 3237.029, 'duration': 4.305}, {'end': 3248.382, 'text': 'So when I say a number of characters in a string, that means that it is looking at the number of cells that contain a text.', 'start': 3241.494, 'duration': 6.888}, {'end': 3256.821, 'text': 'What is the number or what is the length of that text? For example, if I want to know in our cell Raymond acre is how many characters.', 'start': 3248.442, 'duration': 8.379}], 'summary': 'The len function returns the number of characters in a string.', 'duration': 26.351, 'max_score': 3230.47, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ3230470.jpg'}, {'end': 3389.567, 'src': 'embed', 'start': 3363.408, 'weight': 2, 'content': [{'end': 3368.029, 'text': 'again, this is a demo which you can look at to identify, if you want to just come back and see this.', 'start': 3363.408, 'duration': 4.621}, {'end': 3371.475, 'text': 'examples. Next is a very important function called as a IF function.', 'start': 3368.029, 'duration': 3.446}, {'end': 3379.12, 'text': 'The IF function is very important because it gives you an option to get the required data if a particular condition is met.', 'start': 3371.895, 'duration': 7.225}, {'end': 3384.183, 'text': 'For example, in I have a list of employees going back to my sheet.', 'start': 3379.34, 'duration': 4.843}, {'end': 3389.567, 'text': 'in the IF function sheet I have the list of employees data with their department information.', 'start': 3384.183, 'duration': 5.384}], 'summary': 'Introduction to if function for data retrieval based on conditions.', 'duration': 26.159, 'max_score': 3363.408, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ3363408.jpg'}, {'end': 3427.521, 'src': 'embed', 'start': 3402.462, 'weight': 0, 'content': [{'end': 3408.89, 'text': 'So against each employees who are in the sales department, the result should show in the commission column as 20, 000.', 'start': 3402.462, 'duration': 6.428}, {'end': 3411.093, 'text': 'So for example, in the first one, it is admin.', 'start': 3408.89, 'duration': 2.203}, {'end': 3412.375, 'text': 'So here it will show as zero.', 'start': 3411.173, 'duration': 1.202}, {'end': 3413.516, 'text': 'Second one, it is sales.', 'start': 3412.595, 'duration': 0.921}, {'end': 3416.078, 'text': 'So here it should show as 20, 000.', 'start': 3413.837, 'duration': 2.241}, {'end': 3417.098, 'text': 'Next one is admin again.', 'start': 3416.078, 'duration': 1.02}, {'end': 3419.319, 'text': 'So again zero marketing again zero.', 'start': 3417.138, 'duration': 2.181}, {'end': 3421.719, 'text': 'We are only looking at 20, 000 for the sales department.', 'start': 3419.339, 'duration': 2.38}, {'end': 3427.521, 'text': 'So against each employee, we should be able to see the exact amount that needs to be paid as a commission.', 'start': 3421.739, 'duration': 5.782}], 'summary': "In the sales department, each employee's commission should be 20,000.", 'duration': 25.059, 'max_score': 3402.462, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ3402462.jpg'}, {'end': 3581.946, 'src': 'embed', 'start': 3519.323, 'weight': 1, 'content': [{'end': 3522.206, 'text': 'I can double click on that to get the answer for each one of them.', 'start': 3519.323, 'duration': 2.883}, {'end': 3523.707, 'text': 'now the formula is dragged.', 'start': 3522.506, 'duration': 1.201}, {'end': 3526.85, 'text': 'you can see that the formula is dragged from d1 in i2.', 'start': 3523.707, 'duration': 3.143}, {'end': 3529.112, 'text': 'you can see d2 is equal to sales.', 'start': 3526.85, 'duration': 2.262}, {'end': 3534.597, 'text': 'while i go down, the formula changes the cell references from d2 to d3.', 'start': 3529.112, 'duration': 5.485}, {'end': 3540.021, 'text': 'why? because it is now moving down, going to the next row d3, then d4, then d5 and d6 accordingly.', 'start': 3534.597, 'duration': 5.424}, {'end': 3550.063, 'text': 'So it is automatically calculating from the respective cells for the second employee which is so much, and they issue in the sales department.', 'start': 3542.096, 'duration': 7.967}, {'end': 3554.086, 'text': 'It is checking and the summons department is in d3.', 'start': 3550.123, 'duration': 3.963}, {'end': 3557.609, 'text': 'So Excel is automatically taken d3 as the cell reference.', 'start': 3554.326, 'duration': 3.283}, {'end': 3562.513, 'text': "Next we are checking for Kuldeep Sharma Kuldeep Sharma's department detail is in d4.", 'start': 3558.169, 'duration': 4.344}, {'end': 3568.177, 'text': 'The cell reference is automatically depict as d4 is equal to sales because it is not says it is admin.', 'start': 3562.753, 'duration': 5.424}, {'end': 3570.199, 'text': 'It is automatically giving it as a zero.', 'start': 3568.217, 'duration': 1.982}, {'end': 3573.303, 'text': 'So I hope the if function is very very clear.', 'start': 3570.942, 'duration': 2.361}, {'end': 3576.064, 'text': "It's a very easy formula, but very important.", 'start': 3573.363, 'duration': 2.701}, {'end': 3580.426, 'text': 'So again, looking at it, the logical test which we have given as d2 is equal to sales.', 'start': 3576.264, 'duration': 4.162}, {'end': 3581.946, 'text': 'the value of true is because.', 'start': 3580.426, 'duration': 1.52}], 'summary': 'Excel automatically calculates and updates cell references in the formula as it moves down the rows, providing a clear demonstration of the functionality and importance of the if function.', 'duration': 62.623, 'max_score': 3519.323, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ3519323.jpg'}, {'end': 3632.433, 'src': 'embed', 'start': 3603.114, 'weight': 10, 'content': [{'end': 3610.156, 'text': 'second logical test is salary is more than 5, 000, but less than 10, 000..', 'start': 3603.114, 'duration': 7.042}, {'end': 3612.597, 'text': 'This is my second logical test.', 'start': 3610.156, 'duration': 2.441}, {'end': 3617.899, 'text': 'third logical test is salary more than 10, 000 but less than 15, 000.', 'start': 3612.597, 'duration': 5.302}, {'end': 3625.846, 'text': 'fourth logical test is salary more than 15, 000, but is less than 20, 000..', 'start': 3617.899, 'duration': 7.947}, {'end': 3631.351, 'text': 'And my fifth and the last logical test is salary is more than 20, 000.', 'start': 3625.846, 'duration': 5.505}, {'end': 3632.433, 'text': 'These are my conditions.', 'start': 3631.352, 'duration': 1.081}], 'summary': 'Logical tests for salary ranges from 5,000 to 20,000.', 'duration': 29.319, 'max_score': 3603.114, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ3603114.jpg'}, {'end': 3850.784, 'src': 'embed', 'start': 3829.212, 'weight': 6, 'content': [{'end': 3838.617, 'text': 'Last one, we have identified that, out of these 25 employees that we removed, we identified that only 41 employees are left,', 'start': 3829.212, 'duration': 9.405}, {'end': 3840.778, 'text': 'who are earning for more than 20, 000..', 'start': 3838.617, 'duration': 2.161}, {'end': 3843.079, 'text': 'Why we are saying more than 20, 000?', 'start': 3840.778, 'duration': 2.301}, {'end': 3850.784, 'text': 'Because we have put everyone in a bucket where either they are earning less than 5, 000, less than 10, 000, less than 15, 000 or less than 20, 000..', 'start': 3843.079, 'duration': 7.705}], 'summary': 'Out of 25 removed employees, only 41 left earning over 20,000.', 'duration': 21.572, 'max_score': 3829.212, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ3829212.jpg'}, {'end': 4088.458, 'src': 'embed', 'start': 4063.431, 'weight': 9, 'content': [{'end': 4070.794, 'text': 'And function checks if all logical arguments inside the function are true, and returns the true value if they are true,', 'start': 4063.431, 'duration': 7.363}, {'end': 4072.915, 'text': 'and false value if one of them is false.', 'start': 4070.794, 'duration': 2.121}, {'end': 4082.268, 'text': 'for example an and function like we are going to any sheet and trying to identify if logical test that we give like in a function.', 'start': 4073.254, 'duration': 9.014}, {'end': 4083.911, 'text': 'we are giving different logical tests right?', 'start': 4082.268, 'duration': 1.643}, {'end': 4088.458, 'text': "So if you're giving more than one logical test, if they are true,", 'start': 4084.271, 'duration': 4.187}], 'summary': "The 'and' function checks if all logical arguments are true, returning true or false value accordingly.", 'duration': 25.027, 'max_score': 4063.431, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ4063431.jpg'}, {'end': 4144.245, 'src': 'embed', 'start': 4119.542, 'weight': 12, 'content': [{'end': 4124.854, 'text': 'one is the department has sales And the second condition is your region as West.', 'start': 4119.542, 'duration': 5.312}, {'end': 4131.358, 'text': 'So only if both the conditions are met then only the answer should be true or the result should be true.', 'start': 4125.354, 'duration': 6.004}, {'end': 4133.099, 'text': 'Otherwise it should show me as false.', 'start': 4131.518, 'duration': 1.581}, {'end': 4138.321, 'text': "So how do I do that? I'll put an and function in the cell where I want to answer.", 'start': 4133.299, 'duration': 5.022}, {'end': 4144.245, 'text': 'So I want an answer for each one of them, because I want true everywhere where there is sales, and West wherever there is sales,', 'start': 4138.361, 'duration': 5.884}], 'summary': "Use an 'and' function to check if department has sales and region is west, returning true if both conditions are met, false otherwise.", 'duration': 24.703, 'max_score': 4119.542, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ4119542.jpg'}], 'start': 2476.069, 'title': 'Excel functions and data analysis', 'summary': 'Covers excel index, offset, match, and various other functions, with practical examples and quantifiable outcomes. it demonstrates the application of these functions to calculate commissions, categorize employee salaries, and analyze employee salary data, resulting in specific income ranges and criteria-based identification of employees.', 'chapters': [{'end': 2513.998, 'start': 2476.069, 'title': 'Excel index and offset functions', 'summary': 'Explains the difference between the index and offset functions in excel, with the index function selecting values based on row and column numbers, while the offset function uses a reference cell to determine the position of the selected value.', 'duration': 37.929, 'highlights': ['The INDEX function selects values based on row and column numbers.', 'The OFFSET function uses a reference cell to determine the position of the selected value.', 'The difference between the INDEX and OFFSET functions is that in the INDEX function you give the table, while in the OFFSET function, you give a reference to one cell.']}, {'end': 2985.451, 'start': 2514.058, 'title': 'Excel offset and match functions', 'summary': 'Explains the offset function and its syntax, demonstrating its usage to calculate the sum of data from february to may for east and west, and then introduces the match function to identify the position of a specific item in a range of cells, followed by its application within the index function to create a dynamic solution for data analysis.', 'duration': 471.393, 'highlights': ['The chapter explains the offset function and its syntax, demonstrating its usage to calculate the sum of data from February to May for East and West.', 'Introduces the match function to identify the position of a specific item in a range of cells.', 'Demonstrates the application of the match function within the index function to create a dynamic solution for data analysis.']}, {'end': 3345.444, 'start': 2985.711, 'title': 'Excel functions and examples', 'summary': 'Demonstrates the usage of various excel functions including min, max, trim, len, and sqrt, providing examples and practical application for each function, with specific explanations and quantifiable outcomes.', 'duration': 359.733, 'highlights': ['The min function is used to identify the minimum salary of employees, resulting in a minimum salary of 5950 rupees for the employees.', 'The max function is utilized to determine the maximum salary paid to the employees, providing the maximum salary amount.', 'The trim function effectively removes unwanted spaces from the text, ensuring proper data formatting and integrity.', 'The len function accurately calculates the number of characters in a string, providing a clear understanding of the text length and format.', 'The sqrt function is used to calculate the square roots of numbers, offering precise mathematical operations and results.']}, {'end': 3758.004, 'start': 3345.444, 'title': 'Excel functions demo', 'summary': 'Discusses the practical application of excel functions, including if and ifs, to calculate commissions and categorize employee salaries, demonstrating the use of logical tests and value assignments. the if function is used to assign a commission of 20,000 rupees to sales department employees and zero to others, while the ifs function is used to categorize employee salaries into different brackets, with 10 employees earning less than 5,000 rupees and 10 earning less than 10,000 rupees.', 'duration': 412.56, 'highlights': ['The IF function is used to assign a commission of 20,000 rupees to sales department employees and zero to others.', 'The IFs function is used to categorize employee salaries into different brackets.', 'Logical tests and value assignments are demonstrated within the context of the IF and IFs functions.']}, {'end': 4155.827, 'start': 3758.084, 'title': 'Employee salary data analysis', 'summary': "Presents a method of categorizing employee salaries into different buckets based on specific income ranges, resulting in 66 employees earning more than 15,000, 25 employees earning less than 20,000, and 41 employees earning more than 20,000. it also explains the application of the 'if' function and the 'and' function for identifying employees meeting specific criteria based on department and region.", 'duration': 397.743, 'highlights': ['66 employees earning more than 15,000', '25 employees earning less than 20,000', '41 employees earning more than 20,000', "Usage of 'IF' function for salary categorization", "Application of 'AND' function for identifying specific employee criteria"]}], 'duration': 1679.758, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ2476069.jpg', 'highlights': ['The IFs function is used to categorize employee salaries into different brackets.', 'The INDEX function selects values based on row and column numbers.', 'The OFFSET function uses a reference cell to determine the position of the selected value.', 'Demonstrates the application of the match function within the index function to create a dynamic solution for data analysis.', 'Logical tests and value assignments are demonstrated within the context of the IF and IFs functions.', 'The min function is used to identify the minimum salary of employees, resulting in a minimum salary of 5950 rupees for the employees.', 'The max function is utilized to determine the maximum salary paid to the employees, providing the maximum salary amount.', 'The IF function is used to assign a commission of 20,000 rupees to sales department employees and zero to others.', 'The difference between the INDEX and OFFSET functions is that in the INDEX function you give the table, while in the OFFSET function, you give a reference to one cell.', 'The chapter explains the offset function and its syntax, demonstrating its usage to calculate the sum of data from February to May for East and West.', 'The trim function effectively removes unwanted spaces from the text, ensuring proper data formatting and integrity.', 'The len function accurately calculates the number of characters in a string, providing a clear understanding of the text length and format.', 'The sqrt function is used to calculate the square roots of numbers, offering precise mathematical operations and results.', 'Introduces the match function to identify the position of a specific item in a range of cells.', '66 employees earning more than 15,000', '41 employees earning more than 20,000', '25 employees earning less than 20,000', "Usage of 'IF' function for salary categorization", "Application of 'AND' function for identifying specific employee criteria"]}, {'end': 6168.754, 'segs': [{'end': 5176.764, 'src': 'embed', 'start': 5147.412, 'weight': 9, 'content': [{'end': 5150.374, 'text': 'Next is these numbers that are less than so?', 'start': 5147.412, 'duration': 2.962}, {'end': 5157.497, 'text': 'if I want to know, any employees earning salary less than 10, 000 should be colored in green,', 'start': 5150.374, 'duration': 7.123}, {'end': 5163.1, 'text': 'and also their number format should now be in a currency format with a rupee sign.', 'start': 5157.497, 'duration': 5.603}, {'end': 5166.322, 'text': 'So I go to highlight, sell rules, click on less than value.', 'start': 5163.14, 'duration': 3.182}, {'end': 5172.783, 'text': 'This time it has to be 10, 000 again, but my color has to change and the color is green, dark green text.', 'start': 5166.722, 'duration': 6.061}, {'end': 5176.764, 'text': 'Like I said, you can always use the custom format to change your formatting.', 'start': 5173.104, 'duration': 3.66}], 'summary': 'Format employees earning less than 10,000 in green with rupee currency format.', 'duration': 29.352, 'max_score': 5147.412, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ5147412.jpg'}, {'end': 5412.198, 'src': 'embed', 'start': 5383.866, 'weight': 16, 'content': [{'end': 5386.307, 'text': 'So you will again have to be like in the first module.', 'start': 5383.866, 'duration': 2.441}, {'end': 5392.69, 'text': 'we learned when we were doing a formula for SUMIF we have to write mktg and not m-a-r-k-e-t-i-n-g.', 'start': 5386.307, 'duration': 6.383}, {'end': 5394.931, 'text': 'Same goes with the conditional formatting.', 'start': 5392.87, 'duration': 2.061}, {'end': 5400.953, 'text': 'We will have to put mktg as your requirement because then Excel will look for mktg in those cells.', 'start': 5394.951, 'duration': 6.002}, {'end': 5405.775, 'text': 'Once it finds the mktg what is Excel supposed to do? It has to color them in green.', 'start': 5401.393, 'duration': 4.382}, {'end': 5408.356, 'text': 'So I select the green fill with dark green text.', 'start': 5406.075, 'duration': 2.281}, {'end': 5412.198, 'text': 'You can again select like I said from the custom formatting whichever color you want.', 'start': 5408.737, 'duration': 3.461}], 'summary': "Using 'mktg' instead of 'marketing' for formula sumif and conditional formatting in excel.", 'duration': 28.332, 'max_score': 5383.866, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ5383866.jpg'}, {'end': 5580.926, 'src': 'embed', 'start': 5548.07, 'weight': 14, 'content': [{'end': 5551.195, 'text': "24, 500 is half of 49, 000 and that's the reason you will see that the data bar is now become half.", 'start': 5548.07, 'duration': 3.125}, {'end': 5565.862, 'text': 'If you go even further down, like go to say 10, 000 or something, you will see, because 10, 000 is even half of 24,', 'start': 5558.365, 'duration': 7.497}, {'end': 5572.218, 'text': '000 data bar has even become smaller, which is it has gone half of 24, 000 amount.', 'start': 5565.862, 'duration': 6.356}, {'end': 5580.926, 'text': 'So the data bar which is there that gives you a graphical representation of where your data sets as compared to the highest value in your data.', 'start': 5572.578, 'duration': 8.348}], 'summary': 'Data bar represents values, halves at 24,500 & 10,000.', 'duration': 32.856, 'max_score': 5548.07, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ5548070.jpg'}, {'end': 5681.69, 'src': 'embed', 'start': 5652.23, 'weight': 15, 'content': [{'end': 5653.151, 'text': 'There are three colors.', 'start': 5652.23, 'duration': 0.921}, {'end': 5658.214, 'text': 'As soon as I click on the ones which I have just made the rules for, I can see there are three colors.', 'start': 5653.611, 'duration': 4.603}, {'end': 5659.555, 'text': 'One is the lowest value.', 'start': 5658.334, 'duration': 1.221}, {'end': 5660.716, 'text': 'I have red color.', 'start': 5659.815, 'duration': 0.901}, {'end': 5663.637, 'text': 'Then for the highest value, I have given green color.', 'start': 5660.956, 'duration': 2.681}, {'end': 5668.861, 'text': 'For the midpoint, now what is the midpoint? Midpoint will be the 50th percentile.', 'start': 5664.118, 'duration': 4.743}, {'end': 5672.583, 'text': 'We can also change the percentile to numbers.', 'start': 5669.161, 'duration': 3.422}, {'end': 5674.725, 'text': "Say I don't want to keep a percentile.", 'start': 5672.723, 'duration': 2.002}, {'end': 5676.626, 'text': 'I want to keep something like a number.', 'start': 5674.885, 'duration': 1.741}, {'end': 5681.69, 'text': 'Say 24, 000 because half of 49, 000 is 24, 500 rather.', 'start': 5676.846, 'duration': 4.844}], 'summary': 'Three colors assigned based on rules with lowest value in red, highest in green, and midpoint at 50th percentile or 24,000.', 'duration': 29.46, 'max_score': 5652.23, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ5652230.jpg'}, {'end': 5769.046, 'src': 'embed', 'start': 5741.479, 'weight': 5, 'content': [{'end': 5744.62, 'text': "We've also seen how we can customize those cells.", 'start': 5741.479, 'duration': 3.141}, {'end': 5750.281, 'text': 'If I want to click on these and then change the color scale, I can click on more rules.', 'start': 5744.82, 'duration': 5.461}, {'end': 5753.802, 'text': 'I can always change the color scale to two color scale.', 'start': 5750.661, 'duration': 3.141}, {'end': 5758.223, 'text': 'So instead of three color scale, now it will be only two color scale, which is red and yellow.', 'start': 5753.842, 'duration': 4.381}, {'end': 5760.884, 'text': 'And so the lowest value will be red.', 'start': 5758.603, 'duration': 2.281}, {'end': 5765.965, 'text': 'The highest value will be yellow and the rest of the cells will be colored proportionately.', 'start': 5761.224, 'duration': 4.741}, {'end': 5769.046, 'text': 'This time you will see that the colors have really changed.', 'start': 5765.985, 'duration': 3.061}], 'summary': 'Customized cells with two-color scale: lowest value is red, highest value is yellow.', 'duration': 27.567, 'max_score': 5741.479, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ5741479.jpg'}, {'end': 6095.778, 'src': 'embed', 'start': 6067.56, 'weight': 0, 'content': [{'end': 6073.243, 'text': 'How does isFormula work? So there are different isFormulas that you can use in Excel.', 'start': 6067.56, 'duration': 5.683}, {'end': 6075.565, 'text': 'One of them is the isNumbers.', 'start': 6073.644, 'duration': 1.921}, {'end': 6080.668, 'text': 'What does it do is it will only identify if it is a number, it will say that it is true.', 'start': 6075.845, 'duration': 4.823}, {'end': 6082.969, 'text': 'If it is not a number, it will say false.', 'start': 6081.028, 'duration': 1.941}, {'end': 6087.332, 'text': 'For example, we just learned that if it is a number, it will automatically color that in blue.', 'start': 6083.029, 'duration': 4.303}, {'end': 6090.534, 'text': 'there is also an option, something called as is odd.', 'start': 6087.612, 'duration': 2.922}, {'end': 6095.778, 'text': 'that means if it is an odd number then it will highlight them in particular color that you mentioned.', 'start': 6090.534, 'duration': 5.244}], 'summary': "Excel's isformula function identifies numbers and odd numbers for formatting.", 'duration': 28.218, 'max_score': 6067.56, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6067560.jpg'}, {'end': 6151.633, 'src': 'embed', 'start': 6108.707, 'weight': 1, 'content': [{'end': 6114.49, 'text': 'then in the format values where this formula is true I click on is equal to is or open the bracket.', 'start': 6108.707, 'duration': 5.783}, {'end': 6118.432, 'text': 'now, this time again I have selected from the first cell in that column.', 'start': 6114.49, 'duration': 3.942}, {'end': 6121.034, 'text': "so I'll have to select the first cell, which is h1.", 'start': 6118.432, 'duration': 2.602}, {'end': 6126.717, 'text': 'close the bracket and now I will decide on which color I wanted say I want the yellow color.', 'start': 6121.034, 'duration': 5.683}, {'end': 6130.038, 'text': 'so I select the yellow color from the custom format and click on.', 'start': 6126.717, 'duration': 3.321}, {'end': 6134.921, 'text': 'ok, it will color all the ones which has odd numbers into yellow color.', 'start': 6130.038, 'duration': 4.883}, {'end': 6136.842, 'text': 'the rest of them will be left as it is.', 'start': 6134.921, 'duration': 1.921}, {'end': 6142.266, 'text': 'So these are is functions that you can use in order to get your data colored.', 'start': 6137.242, 'duration': 5.024}, {'end': 6146.628, 'text': 'You can also use any cell which is more than say 75, 000.', 'start': 6142.526, 'duration': 4.102}, {'end': 6151.633, 'text': 'Like we use more than data or greater than data information here in the conditional formatting.', 'start': 6146.629, 'duration': 5.004}], 'summary': 'Using excel, apply conditional formatting to color cells with odd numbers in yellow, leaving the rest unchanged.', 'duration': 42.926, 'max_score': 6108.707, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6108707.jpg'}], 'start': 4156.377, 'title': 'Excel functions and formatting', 'summary': 'Covers excel logical functions like and and or, today function, customizing quick access toolbar, conditional formatting for highlighting values, and demonstration of conditional formatting including data bars, color scales, and icon sets.', 'chapters': [{'end': 4409.238, 'start': 4156.377, 'title': 'Excel logical functions and today function', 'summary': 'Explains how to use logical functions like and and or in excel to evaluate multiple conditions and the today function to get the current date in an excel worksheet.', 'duration': 252.861, 'highlights': ['Explanation of OR function in Excel', 'Demonstration of the TODAY function in Excel', 'Explanation of AND function in Excel']}, {'end': 4877.802, 'start': 4409.619, 'title': 'Excel quick access toolbar', 'summary': 'Explains how to customize the quick access toolbar in excel, including adding, removing, and using various commands, such as save, open, email, and sorting options, and elaborates on conditional formatting features like highlighting duplicate values and applying formats based on specific criteria.', 'duration': 468.183, 'highlights': ['The Quick Access Toolbar in Excel can be customized by adding frequently used commands, such as save, open, email, and sorting options, to improve efficiency in accessing and utilizing specific functions.', 'Conditional formatting in Excel enables the user to apply formats to cells based on specific criteria, such as highlighting duplicate values in a column, to identify and rectify potential data entry errors efficiently.', 'The unique value of employee codes is emphasized, demonstrating the use of conditional formatting to identify any duplicate values and potential data entry errors, such as mistakenly added codes for different employees.']}, {'end': 5146.797, 'start': 4878.222, 'title': 'Excel conditional formatting', 'summary': 'Explains how to use conditional formatting in excel to highlight duplicate values and cells with values greater than a specified number, such as highlighting all employees earning more than 10,000 rupees and formatting their salaries as currency.', 'duration': 268.575, 'highlights': ['Using conditional formatting to highlight duplicate values in Excel.', 'Applying conditional formatting to highlight cells with values greater than a specified number.', 'Formatting cells as currency based on a condition in Excel.']}, {'end': 5508.663, 'start': 5147.412, 'title': 'Excel conditional formatting demo', 'summary': 'Demonstrates how to use conditional formatting in excel to highlight cells with values less than 10,000 in green and in currency format with a rupee sign, remove conditional formatting, apply a new rule to color cells between 10,000 and 20,000 in yellow, and use text-based conditional formatting. it also explains data bars, color scales, and icon sets for visual data comparison.', 'duration': 361.251, 'highlights': ['Demonstrates how to use conditional formatting to highlight cells with values less than 10,000 in green and in currency format with a rupee sign', 'Explains the process of removing conditional formatting and applying a new rule to color cells between 10,000 and 20,000 in yellow', "Describes the use of text-based conditional formatting to color specific text values, such as 'mktg', in green", 'Explains the concept of data bars, color scales, and icon sets for visual data comparison in Excel']}, {'end': 6168.754, 'start': 5509.023, 'title': 'Excel conditional formatting demonstration', 'summary': 'Demonstrates the application of data bars, color scales, and icon sets in excel conditional formatting, explaining their visual representation of data distribution, and the use of formulas for cell highlighting.', 'duration': 659.731, 'highlights': ['The highest salary given to the employees in the organization is 49,000, and all other salary amounts are compared to this value, influencing the data bar size accordingly.', 'Color scales help understand data distribution and variation, with cells shaded in gradations of colors corresponding to minimum, midpoint, and maximum thresholds.', 'Conditional formatting with icon sets provides visual cues about the highest, lowest, and midpoint values, allowing customization of directional or shape-based icon sets.', 'The demonstration also covers the use of formulas to identify and highlight cells meeting specific conditions, such as identifying numeric employee codes for highlighting in blue color.']}], 'duration': 2012.377, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ4156377.jpg', 'highlights': ['Demonstration of conditional formatting to highlight cells with values greater than a specified number', 'Demonstrates how to use conditional formatting to highlight cells with values less than 10,000 in green and in currency format with a rupee sign', "Describes the use of text-based conditional formatting to color specific text values, such as 'mktg', in green", 'Color scales help understand data distribution and variation, with cells shaded in gradations of colors corresponding to minimum, midpoint, and maximum thresholds', 'Conditional formatting with icon sets provides visual cues about the highest, lowest, and midpoint values, allowing customization of directional or shape-based icon sets', 'The Quick Access Toolbar in Excel can be customized by adding frequently used commands, such as save, open, email, and sorting options, to improve efficiency in accessing and utilizing specific functions', 'Conditional formatting in Excel enables the user to apply formats to cells based on specific criteria, such as highlighting duplicate values in a column, to identify and rectify potential data entry errors efficiently', 'Explains the process of removing conditional formatting and applying a new rule to color cells between 10,000 and 20,000 in yellow', 'The unique value of employee codes is emphasized, demonstrating the use of conditional formatting to identify any duplicate values and potential data entry errors, such as mistakenly added codes for different employees', 'The highest salary given to the employees in the organization is 49,000, and all other salary amounts are compared to this value, influencing the data bar size accordingly', 'Explains the concept of data bars, color scales, and icon sets for visual data comparison in Excel', 'Using conditional formatting to highlight duplicate values in Excel', 'Formatting cells as currency based on a condition in Excel', 'Demonstration also covers the use of formulas to identify and highlight cells meeting specific conditions, such as identifying numeric employee codes for highlighting in blue color', 'Explanation of OR function in Excel', 'Demonstration of the TODAY function in Excel', 'Explanation of AND function in Excel']}, {'end': 6839.107, 'segs': [{'end': 6194.678, 'src': 'embed', 'start': 6169.074, 'weight': 4, 'content': [{'end': 6175.801, 'text': "I will take H1 again because I'm selecting the whole cell is equal to H1 is greater than 10, 000 same thing.", 'start': 6169.074, 'duration': 6.727}, {'end': 6180.446, 'text': "However, we are using the formula to determine that now this time I'm going to color in blue.", 'start': 6175.982, 'duration': 4.464}, {'end': 6181.748, 'text': 'So I click on OK.', 'start': 6180.947, 'duration': 0.801}, {'end': 6186.413, 'text': 'So wherever there is a salary more than 10, 000 rupees, it will automatically color in blue.', 'start': 6182.27, 'duration': 4.143}, {'end': 6190.635, 'text': 'Anything that is less than 10, 000, it will leave the previous formatting that we had.', 'start': 6187.013, 'duration': 3.622}, {'end': 6194.678, 'text': 'Same thing, you can use the less than sign also wherever you need to.', 'start': 6191.216, 'duration': 3.462}], 'summary': 'Using excel formula to highlight salaries over 10,000 rupees in blue.', 'duration': 25.604, 'max_score': 6169.074, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6169074.jpg'}, {'end': 6378.853, 'src': 'embed', 'start': 6349.931, 'weight': 1, 'content': [{'end': 6352.672, 'text': 'So the first condition is D1 is equal to sales.', 'start': 6349.931, 'duration': 2.741}, {'end': 6355.514, 'text': 'We will always put sales in the double quotes.', 'start': 6353.013, 'duration': 2.501}, {'end': 6359.135, 'text': 'Second condition is E1 is equal to North.', 'start': 6355.854, 'duration': 3.281}, {'end': 6361.677, 'text': 'So I put in the double quotes again North.', 'start': 6359.495, 'duration': 2.182}, {'end': 6363.759, 'text': 'No case sensitivity in this.', 'start': 6362.098, 'duration': 1.661}, {'end': 6372.587, 'text': 'I close the bracket so my condition is has to be fulfilled like if this department is sales and the region is not I want the color as say green.', 'start': 6363.939, 'duration': 8.648}, {'end': 6375.43, 'text': 'So I select the green color in the custom formatting.', 'start': 6373.088, 'duration': 2.342}, {'end': 6378.853, 'text': 'I click on OK and then I click on OK again.', 'start': 6375.75, 'duration': 3.103}], 'summary': 'Set conditional formatting rules: if department is sales and region is north, apply green color.', 'duration': 28.922, 'max_score': 6349.931, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6349931.jpg'}, {'end': 6571.59, 'src': 'embed', 'start': 6542.039, 'weight': 5, 'content': [{'end': 6543.879, 'text': 'So for example, I drag it here.', 'start': 6542.039, 'duration': 1.84}, {'end': 6547.66, 'text': 'Wherever I can see true, those are the ones which are failed results.', 'start': 6544.239, 'duration': 3.421}, {'end': 6551.421, 'text': "So this one, G is scored 32 and that's the reason it is showing true.", 'start': 6547.98, 'duration': 3.441}, {'end': 6554.582, 'text': 'For E, it is showing true because they have only scored 30.', 'start': 6551.761, 'duration': 2.821}, {'end': 6555.422, 'text': 'That means it has failed.', 'start': 6554.582, 'duration': 0.84}, {'end': 6560.583, 'text': 'And again for C, it is 28 and because they have scored less than 35, they have failed.', 'start': 6555.822, 'duration': 4.761}, {'end': 6563.046, 'text': 'So wherever you can see true, they have all failed.', 'start': 6560.865, 'duration': 2.181}, {'end': 6568.869, 'text': 'So, similarly, if I wanted to add this along with the if function, how can I do it with the if function?', 'start': 6563.526, 'duration': 5.343}, {'end': 6571.59, 'text': 'So I can do if, starting with a logical test.', 'start': 6569.069, 'duration': 2.521}], 'summary': 'Using logical tests to identify failed scores; g=32, e=30, c=28.', 'duration': 29.551, 'max_score': 6542.039, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6542039.jpg'}, {'end': 6631.364, 'src': 'embed', 'start': 6601.771, 'weight': 2, 'content': [{'end': 6604.632, 'text': 'So if there are two conditions, only one of the conditions is met.', 'start': 6601.771, 'duration': 2.861}, {'end': 6612.934, 'text': 'If no conditions are met, that means condition A, condition B, both are not met or more than one condition is met, ZOR function returns false.', 'start': 6604.992, 'duration': 7.942}, {'end': 6622.679, 'text': 'That means if I put a condition say condition a condition B if condition a and B both are met then those or function will give you a false answer.', 'start': 6613.334, 'duration': 9.345}, {'end': 6631.364, 'text': 'If none of them are met neither a neither B then also it will give a false only one of the condition has to be met a or B.', 'start': 6623.099, 'duration': 8.265}], 'summary': 'Zor function returns false if no conditions are met, and only one condition needs to be met for it to return true.', 'duration': 29.593, 'max_score': 6601.771, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6601771.jpg'}, {'end': 6827.017, 'src': 'embed', 'start': 6798.242, 'weight': 0, 'content': [{'end': 6805.304, 'text': 'Similar to how we use the not function with an if, we can use the if function with an and or a or.', 'start': 6798.242, 'duration': 7.062}, {'end': 6808.086, 'text': 'So to start with, we are looking at our sheet.', 'start': 6805.725, 'duration': 2.361}, {'end': 6814.088, 'text': 'On my sheet, you can see that I have the name of the students, their scores in math and their score in science.', 'start': 6808.506, 'duration': 5.582}, {'end': 6818.85, 'text': "So what I'll do is I need to know the students who have passed.", 'start': 6814.648, 'duration': 4.202}, {'end': 6820.672, 'text': 'How do I know if they are passed?', 'start': 6819.431, 'duration': 1.241}, {'end': 6827.017, 'text': 'If the student has scored, you can see it here the condition if the student has scored 35 or more marks in each subject,', 'start': 6820.812, 'duration': 6.205}], 'summary': 'Using if function to identify students with scores of 35 or more in math and science.', 'duration': 28.775, 'max_score': 6798.242, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6798242.jpg'}], 'start': 6169.074, 'title': 'Excel formatting and logical functions', 'summary': 'Explores effective use of excel conditional formatting, including color formatting based on conditions, logical functions like and, or, and not, as well as the xor function in computer selection and if function in bonus calculation and student results assessment.', 'chapters': [{'end': 6601.451, 'start': 6169.074, 'title': 'Excel conditional formatting', 'summary': 'Explores how to effectively use conditional formatting in excel, including applying color formatting based on specific conditions, using logical functions such as and and or, and using not function to identify failed students based on a specific threshold.', 'duration': 432.377, 'highlights': ['Explaining the use of AND and OR functions in conditional formatting', 'Demonstrating the use of NOT function to identify failed students in a spreadsheet', 'Illustrating the application of IF function in conjunction with logical tests']}, {'end': 6839.107, 'start': 6601.771, 'title': 'Xor and if logical functions', 'summary': 'Explains the xor function, demonstrating its use in selecting computers based on hard drive and ram criteria, and also illustrates the application of the if function to calculate employee bonuses based on department and student pass/fail results based on subject scores.', 'duration': 237.336, 'highlights': ['XOR function explained for selecting computers based on hard drive and RAM criteria', 'Application of IF function to calculate employee bonuses based on department', 'Use of IF function with AND logical function for student pass/fail results']}], 'duration': 670.033, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6169074.jpg', 'highlights': ['XOR function explained for selecting computers based on hard drive and RAM criteria', 'Application of IF function to calculate employee bonuses based on department', 'Use of IF function with AND logical function for student pass/fail results', 'Explaining the use of AND and OR functions in conditional formatting', 'Demonstrating the use of NOT function to identify failed students in a spreadsheet', 'Illustrating the application of IF function in conjunction with logical tests']}, {'end': 8618.068, 'segs': [{'end': 6864.248, 'src': 'embed', 'start': 6839.147, 'weight': 3, 'content': [{'end': 6844.692, 'text': 'Why and because I need to ensure that they have scored 35 in math and they have scored 35 in science.', 'start': 6839.147, 'duration': 5.545}, {'end': 6846.253, 'text': 'So I start with an if function.', 'start': 6844.912, 'duration': 1.341}, {'end': 6853.52, 'text': 'If function, because I have a true result that I have to give and a false result that I have to give, which is if this is true,', 'start': 6846.493, 'duration': 7.027}, {'end': 6855.061, 'text': 'give me pass or give me fail.', 'start': 6853.52, 'duration': 1.541}, {'end': 6857.103, 'text': 'So I can only do that in if function.', 'start': 6855.401, 'duration': 1.702}, {'end': 6864.248, 'text': 'Now with a logical test when I had one logical test I put it as department is equal to sales, but now I have two logical tests.', 'start': 6857.583, 'duration': 6.665}], 'summary': 'Using an if function to ensure students score at least 35 in math and science, giving pass or fail results based on the logical tests.', 'duration': 25.101, 'max_score': 6839.147, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6839147.jpg'}, {'end': 7177.303, 'src': 'embed', 'start': 7146.196, 'weight': 13, 'content': [{'end': 7148.358, 'text': 'To do this, we will use the if error function.', 'start': 7146.196, 'duration': 2.162}, {'end': 7153.321, 'text': 'So how do we do that? So I will start by typing and function.', 'start': 7148.398, 'duration': 4.923}, {'end': 7155.063, 'text': 'If you see that I have already put the result here.', 'start': 7153.341, 'duration': 1.722}, {'end': 7162.068, 'text': "So instead of doing that, I will first do an if error function in another cell and I'll show you how does that work.", 'start': 7155.463, 'duration': 6.605}, {'end': 7166.477, 'text': 'Now what am I trying to do is if you see the syntax of the if-error function it says value.', 'start': 7162.535, 'duration': 3.942}, {'end': 7169.719, 'text': 'Value that means which value are you looking at whether there is a error.', 'start': 7166.657, 'duration': 3.062}, {'end': 7173.161, 'text': "So I'm looking at C2 comma if value if error.", 'start': 7170.119, 'duration': 3.042}, {'end': 7177.303, 'text': "If it is an error what should the value be in case if it's an error? I need a zero.", 'start': 7173.581, 'duration': 3.722}], 'summary': 'Demonstrating the use of if-error function with an example in excel.', 'duration': 31.107, 'max_score': 7146.196, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7146196.jpg'}, {'end': 7284.171, 'src': 'embed', 'start': 7251.28, 'weight': 0, 'content': [{'end': 7253.002, 'text': 'Otherwise the function returns the cell value.', 'start': 7251.28, 'duration': 1.722}, {'end': 7254.863, 'text': 'So similar to the if error function.', 'start': 7253.022, 'duration': 1.841}, {'end': 7259.968, 'text': 'So one of the shortcomings of error function is that it acts as a catch all errors.', 'start': 7255.124, 'duration': 4.844}, {'end': 7263.771, 'text': 'So even if it is a div error any value reference it catches everything.', 'start': 7260.208, 'duration': 3.563}, {'end': 7266.493, 'text': 'This means that it covers several errors,', 'start': 7264.231, 'duration': 2.262}, {'end': 7278.869, 'text': "and the consequences of this error is that if error isn't really helpful in helping you distinguish between the different types of error and sometimes may hide a particular error that you would like to fix,", 'start': 7267.185, 'duration': 11.684}, {'end': 7284.171, 'text': 'if any function is used to trap and handle only any errors that may arise in the formula.', 'start': 7278.869, 'duration': 5.302}], 'summary': 'The iferror function catches all errors, making it difficult to distinguish between different types of errors and potentially hiding specific errors, but the ifna function can be used to trap and handle only #n/a errors in a formula.', 'duration': 32.891, 'max_score': 7251.28, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7251280.jpg'}, {'end': 7332.848, 'src': 'embed', 'start': 7307.678, 'weight': 1, 'content': [{'end': 7314.28, 'text': "So if you're getting that kind of an error, then you will have to definitely use a function which will tell you that this data is not available.", 'start': 7307.678, 'duration': 6.602}, {'end': 7317.641, 'text': "if you're getting a ref error, that means there is a referencing error.", 'start': 7314.54, 'duration': 3.101}, {'end': 7321.283, 'text': "that means the data is there, but we don't even know if the data is there or not.", 'start': 7317.641, 'duration': 3.642}, {'end': 7327.066, 'text': "but the referencing error means we have not given a right reference to that, the cell reference, and that's the reason I'm getting an error.", 'start': 7321.283, 'duration': 5.783}, {'end': 7332.848, 'text': "so to ensure that I don't hide the reference error, I only highlight the any error.", 'start': 7327.066, 'duration': 5.782}], 'summary': 'To handle errors, use a function to detect data availability and avoid reference errors by ensuring correct cell referencing.', 'duration': 25.17, 'max_score': 7307.678, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7307678.jpg'}, {'end': 7383.883, 'src': 'embed', 'start': 7359.04, 'weight': 5, 'content': [{'end': 7364.468, 'text': "So if I'm going to say to Excel that if the answer is any, what answer is any?", 'start': 7359.04, 'duration': 5.428}, {'end': 7370.155, 'text': 'this one is any, then give me a blank data or give me no data available again.', 'start': 7364.468, 'duration': 5.687}, {'end': 7373.72, 'text': "Like I said, it's a free text, but you have to put it in the inverted commas.", 'start': 7370.195, 'duration': 3.525}, {'end': 7376.358, 'text': 'Once you have that, you will be able to get that result.', 'start': 7374.116, 'duration': 2.242}, {'end': 7377.579, 'text': 'So no data available.', 'start': 7376.378, 'duration': 1.201}, {'end': 7379.12, 'text': 'Now I can just drag the formula down.', 'start': 7377.619, 'duration': 1.501}, {'end': 7383.883, 'text': 'So for each one of them, wherever the data is not available, it will give me the result as no data available.', 'start': 7379.14, 'duration': 4.743}], 'summary': "Using excel to display 'no data available' for blank results.", 'duration': 24.843, 'max_score': 7359.04, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7359040.jpg'}, {'end': 7437.986, 'src': 'embed', 'start': 7400.854, 'weight': 4, 'content': [{'end': 7401.794, 'text': 'close the bracket.', 'start': 7400.854, 'duration': 0.94}, {'end': 7404.575, 'text': 'enter wherever there is no data available.', 'start': 7401.794, 'duration': 2.781}, {'end': 7407.597, 'text': 'it will automatically give me the result as no data available.', 'start': 7404.575, 'duration': 3.022}, {'end': 7408.257, 'text': 'the rest of them.', 'start': 7407.597, 'duration': 0.66}, {'end': 7411.919, 'text': 'wherever the data is available, the, if any error, will give me the the correct answer.', 'start': 7408.257, 'duration': 3.662}, {'end': 7416.501, 'text': 'only if there is an error it will look for that error and replace it with a no data available.', 'start': 7411.919, 'duration': 4.582}, {'end': 7419.384, 'text': "Now let's move on to the nested if function.", 'start': 7417.102, 'duration': 2.282}, {'end': 7424.629, 'text': 'So how does the nested if function work? Nested if is not an inbuilt function.', 'start': 7419.764, 'duration': 4.865}, {'end': 7426.771, 'text': 'In fact, it is not a function at all.', 'start': 7425.169, 'duration': 1.602}, {'end': 7434.658, 'text': 'When you have multiple conditions to meet, the false value in if is being replaced by another if function to make a further test.', 'start': 7426.951, 'duration': 7.707}, {'end': 7437.986, 'text': "For example, let's look at my nested if function.", 'start': 7435.265, 'duration': 2.721}], 'summary': 'Explains handling of data and nested if function.', 'duration': 37.132, 'max_score': 7400.854, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7400854.jpg'}, {'end': 7521.192, 'src': 'embed', 'start': 7495.88, 'weight': 8, 'content': [{'end': 7502.242, 'text': "why I'm using if is because I know that when I am using a if function, the if function has the true and the false value.", 'start': 7495.88, 'duration': 6.362}, {'end': 7505.242, 'text': 'here I have to see if the sales is true, then give me 10, 000.', 'start': 7502.242, 'duration': 3}, {'end': 7507.283, 'text': 'if it is not true, then I have to look for other things.', 'start': 7505.242, 'duration': 2.041}, {'end': 7513.286, 'text': "So the first and the foremost that I'm going to do is I need to identify if this cell contains sales.", 'start': 7507.822, 'duration': 5.464}, {'end': 7516.048, 'text': "If it contains sales, then I'll give 10, 000.", 'start': 7513.626, 'duration': 2.422}, {'end': 7518.69, 'text': 'So let me put it in a different bucket in the first place.', 'start': 7516.048, 'duration': 2.642}, {'end': 7521.192, 'text': 'If it is sales, I have to give 10, 000.', 'start': 7519.131, 'duration': 2.061}], 'summary': 'Using if function to give $10,000 for sales, otherwise look for other things.', 'duration': 25.312, 'max_score': 7495.88, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7495880.jpg'}, {'end': 7562.358, 'src': 'embed', 'start': 7535.987, 'weight': 6, 'content': [{'end': 7540.009, 'text': 'Now marketing, I have to remember that the spelling is a little different from what we normally write.', 'start': 7535.987, 'duration': 4.022}, {'end': 7544.871, 'text': 'So 8, 000, then bucket four contains R and D.', 'start': 7540.909, 'duration': 3.962}, {'end': 7547.372, 'text': 'For R and D, the bonus is 4, 000.', 'start': 7544.871, 'duration': 2.501}, {'end': 7549.593, 'text': 'Bucket five contains others.', 'start': 7547.372, 'duration': 2.221}, {'end': 7555.415, 'text': 'Now what is others? The ones which are not falling in these lists are all others.', 'start': 7550.333, 'duration': 5.082}, {'end': 7562.358, 'text': 'So looking at this data, you should be able to identify how are we going to put it in the list that we have here.', 'start': 7556.275, 'duration': 6.083}], 'summary': 'Marketing bonus: r&d $4,000, others unidentified.', 'duration': 26.371, 'max_score': 7535.987, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7535987.jpg'}, {'end': 7890.59, 'src': 'embed', 'start': 7867.683, 'weight': 10, 'content': [{'end': 7875.985, 'text': 'if, again, b3 is equal to rnd, then give me 4000, the last one that we did where there was 3000.', 'start': 7867.683, 'duration': 8.302}, {'end': 7878.765, 'text': "so i've not given any cell references this time.", 'start': 7875.985, 'duration': 2.78}, {'end': 7881.866, 'text': "i've immediately just taken the result in the commas.", 'start': 7878.765, 'duration': 3.101}, {'end': 7883.306, 'text': "you'll see that the difference now.", 'start': 7881.866, 'duration': 1.44}, {'end': 7885.627, 'text': 'there you are getting all zero zeros or some other numbers.', 'start': 7883.306, 'duration': 2.321}, {'end': 7887.067, 'text': 'here you can get a better result.', 'start': 7885.627, 'duration': 1.44}, {'end': 7889.209, 'text': 'wherever there is admin, it will give you 5000.', 'start': 7887.367, 'duration': 1.842}, {'end': 7890.59, 'text': 'marketing it will give you 8000.', 'start': 7889.209, 'duration': 1.381}], 'summary': 'Using specified conditions, the values of different cells can be determined, such as 4000 for one condition and 5000 for another.', 'duration': 22.907, 'max_score': 7867.683, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ7867683.jpg'}, {'end': 8045.826, 'src': 'embed', 'start': 8014.974, 'weight': 9, 'content': [{'end': 8016.037, 'text': 'So I select say three.', 'start': 8014.974, 'duration': 1.063}, {'end': 8019.809, 'text': 'Once I select three, it will give me the first three characters from the left.', 'start': 8016.378, 'duration': 3.431}, {'end': 8021.615, 'text': 'This is how your left function works.', 'start': 8019.99, 'duration': 1.625}, {'end': 8025.101, 'text': 'Now similar to the left function, we have the right function.', 'start': 8021.98, 'duration': 3.121}, {'end': 8026.461, 'text': 'So is equal to right.', 'start': 8025.441, 'duration': 1.02}, {'end': 8027.761, 'text': 'I select the same name.', 'start': 8026.721, 'duration': 1.04}, {'end': 8029.562, 'text': 'I give three characters from the right.', 'start': 8028.002, 'duration': 1.56}, {'end': 8031.262, 'text': 'It will give me AJA.', 'start': 8029.662, 'duration': 1.6}, {'end': 8033.663, 'text': 'Why? Because it is taking it from the right.', 'start': 8031.402, 'duration': 2.261}, {'end': 8036.104, 'text': 'This is how your right function will work.', 'start': 8034.003, 'duration': 2.101}, {'end': 8037.604, 'text': 'Moving on to the next one.', 'start': 8036.424, 'duration': 1.18}, {'end': 8042.405, 'text': 'What is the next function that we have on our list? We have MID function in the list.', 'start': 8037.684, 'duration': 4.721}, {'end': 8045.826, 'text': 'The MID function works in a similar fashion as the left and the right.', 'start': 8042.965, 'duration': 2.861}], 'summary': 'Using left and right functions to extract characters, followed by introduction of mid function.', 'duration': 30.852, 'max_score': 8014.974, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ8014974.jpg'}, {'end': 8438.885, 'src': 'embed', 'start': 8407.342, 'weight': 2, 'content': [{'end': 8411.603, 'text': 'Moving on to the next function that we have on our list are the lookup functions.', 'start': 8407.342, 'duration': 4.261}, {'end': 8415.471, 'text': 'Under lookup function, the first function that we are going to see is the VLOOKUP.', 'start': 8412.389, 'duration': 3.082}, {'end': 8417.972, 'text': 'VLOOKUP is the most well-known Excel function.', 'start': 8415.491, 'duration': 2.481}, {'end': 8428.078, 'text': 'VLOOKUP is an inbuilt function that is designed to work with data that is organized into columns like this that we have here, into the columns.', 'start': 8418.753, 'duration': 9.325}, {'end': 8438.885, 'text': 'For a specified value, the function finds the value in one column of the data and returns the corresponding value from another column.', 'start': 8429.019, 'duration': 9.866}], 'summary': 'Vlookup is an excel function designed to work with organized data, finding and returning corresponding values.', 'duration': 31.543, 'max_score': 8407.342, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ8407342.jpg'}], 'start': 6839.147, 'title': 'Excel functions and formulas', 'summary': 'Covers the use of excel functions like if, if error, if any error, iferror, and ifna, along with nested if function for logical tests, error handling, and conditional calculations. it also discusses text functions including left, right, mid, len, exact, rnbetween, and lookup functions like vlookup and hlookup, providing practical examples and detailed explanations.', 'chapters': [{'end': 7307.338, 'start': 6839.147, 'title': 'Excel functions: if, if error, if any error', 'summary': 'Explains the use of excel functions if, if error, and if any error to perform logical tests, handle errors, and manage data in excel, including examples and step-by-step instructions.', 'duration': 468.191, 'highlights': ['The chapter explains the use of Excel functions If, If Error, and If Any Error to perform logical tests, handle errors, and manage data in Excel.', 'Using the If function to check if students have scored 35 in math and science.', 'Using the If function to determine if students can register for the next class based on their certification and prep test scores.', 'Demonstrating the use of If Error function to handle errors like div/0.', 'Explaining the limitations of If Error function and introducing If Any Error function to trap and handle specific errors.']}, {'end': 7632.541, 'start': 7307.678, 'title': 'Excel error handling and nested if function', 'summary': 'Discusses using iferror and ifna functions to handle excel errors, and demonstrates the nested if function for conditional calculations, with examples of vlookup and bonus calculation, providing practical insights into error handling and conditional logic in excel.', 'duration': 324.863, 'highlights': ['The chapter discusses using the IFERROR and IFNA functions to handle Excel errors.', 'The transcript demonstrates the nested IF function for conditional calculations, with examples of VLOOKUP and bonus calculation.']}, {'end': 7989.864, 'start': 7632.841, 'title': 'Excel nested if function', 'summary': 'Explains the nested if function in excel, illustrating various conditions and their corresponding values, and also discusses the usage of the lower, upper, and proper text functions.', 'duration': 357.023, 'highlights': ['The chapter explains the nested If function in Excel, illustrating various conditions and their corresponding values.', 'The chapter discusses the usage of the Lower, Upper, and Proper text functions.']}, {'end': 8618.068, 'start': 7990.104, 'title': 'Text functions and excel formulas', 'summary': 'Explains various text functions in excel, including left, right, mid, len, exact, rnbetween, name ranges, and lookup functions like vlookup and hlookup, providing practical examples and detailed explanations of their usage and syntax.', 'duration': 627.964, 'highlights': ['VLOOKUP function in Excel', 'Name ranges in Excel', 'Left, right, and mid functions in Excel', 'RnBetween function in Excel', 'HLOOKUP function in Excel']}], 'duration': 1778.921, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ6839147.jpg', 'highlights': ['Using the If function to determine if students can register for the next class based on their certification and prep test scores.', 'Demonstrating the use of If Error function to handle errors like div/0.', 'The chapter explains the nested If function in Excel, illustrating various conditions and their corresponding values.', 'The chapter discusses using the Lower, Upper, and Proper text functions.', 'The chapter discusses using the IFERROR and IFNA functions to handle Excel errors.', 'The chapter explains the use of Excel functions If, If Error, and If Any Error to perform logical tests, handle errors, and manage data in Excel.', 'The transcript demonstrates the nested IF function for conditional calculations, with examples of VLOOKUP and bonus calculation.', 'VLOOKUP function in Excel', 'Name ranges in Excel', 'Left, right, and mid functions in Excel', 'RnBetween function in Excel', 'HLOOKUP function in Excel', 'Explaining the limitations of If Error function and introducing If Any Error function to trap and handle specific errors.', 'Using the If function to check if students have scored 35 in math and science.']}, {'end': 9983.355, 'segs': [{'end': 9215.839, 'src': 'embed', 'start': 9189.367, 'weight': 1, 'content': [{'end': 9193.31, 'text': 'If they have registration number 102, they should not be able to enter the form here.', 'start': 9189.367, 'duration': 3.943}, {'end': 9194.41, 'text': 'So how do I do that?', 'start': 9193.61, 'duration': 0.8}, {'end': 9200.014, 'text': "I will first select all the cells where I'm sure that they are going to enter the registration form number.", 'start': 9194.911, 'duration': 5.103}, {'end': 9202.162, 'text': 'Now I go again to the data validation.', 'start': 9200.41, 'duration': 1.752}, {'end': 9204.116, 'text': 'under data validation this time.', 'start': 9202.836, 'duration': 1.28}, {'end': 9207.858, 'text': "I'll not select list when do I select list is when I have this source with me this time.", 'start': 9204.157, 'duration': 3.701}, {'end': 9208.678, 'text': "I don't have a source.", 'start': 9207.898, 'duration': 0.78}, {'end': 9211.939, 'text': 'They can enter any number from 1 to 101 any number.', 'start': 9208.998, 'duration': 2.941}, {'end': 9215.839, 'text': 'They cannot select anything below 1 and anything beyond 1 0 1.', 'start': 9212.299, 'duration': 3.54}], 'summary': 'Restrict entry to registration number 1-101 in data validation.', 'duration': 26.472, 'max_score': 9189.367, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ9189367.jpg'}, {'end': 9410.217, 'src': 'embed', 'start': 9380.839, 'weight': 2, 'content': [{'end': 9382.82, 'text': 'There is one option you can see in the style.', 'start': 9380.839, 'duration': 1.981}, {'end': 9386.623, 'text': 'You can keep it as stop because you are stopping the user to enter the data.', 'start': 9383.24, 'duration': 3.383}, {'end': 9393.831, 'text': 'In the title, you can say error and then error messages or you can say warning or not warning.', 'start': 9387.228, 'duration': 6.603}, {'end': 9394.891, 'text': 'You can say something error.', 'start': 9393.851, 'duration': 1.04}, {'end': 9400.954, 'text': 'And then in the error message, you can say please enter data between one to 101.', 'start': 9395.611, 'duration': 5.343}, {'end': 9403.775, 'text': 'Okay, this is how you can do it.', 'start': 9400.954, 'duration': 2.821}, {'end': 9410.217, 'text': 'So what happens is now when I go onto this and enter 102, see, the error message has changed.', 'start': 9404.235, 'duration': 5.982}], 'summary': 'The option to display error/warning messages is demonstrated, prompting users to enter data between 1 to 101.', 'duration': 29.378, 'max_score': 9380.839, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ9380839.jpg'}, {'end': 9679.354, 'src': 'embed', 'start': 9650.165, 'weight': 0, 'content': [{'end': 9655.449, 'text': "It's useful to answer what if type question because you can adjust one cell entry to see the result.", 'start': 9650.165, 'duration': 5.284}, {'end': 9660.612, 'text': 'The tool is you often use in finance sales and forecasting scenarios, but there are other uses.', 'start': 9655.469, 'duration': 5.143}, {'end': 9662.634, 'text': "Let's look at a simple example.", 'start': 9661.393, 'duration': 1.241}, {'end': 9665.235, 'text': 'The current model contains the following information.', 'start': 9662.654, 'duration': 2.581}, {'end': 9668.117, 'text': 'Number of units 500 which has been sold.', 'start': 9665.616, 'duration': 2.501}, {'end': 9672.352, 'text': 'The retail price of the unit is $25.', 'start': 9668.538, 'duration': 3.814}, {'end': 9679.354, 'text': 'Selling discount that we are giving on this or we have already given to the customer is 10% and the revenue we have to calculate.', 'start': 9672.352, 'duration': 7.002}], 'summary': 'A tool to adjust cell entries for analysis. example: 500 units sold at $25, with 10% discount.', 'duration': 29.189, 'max_score': 9650.165, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ9650165.jpg'}], 'start': 8618.108, 'title': 'Excel functions and analysis', 'summary': 'Covers hlookup, formula auditing, data validation, and what-if analysis in excel, demonstrating various techniques such as trace settings, precedence, dependence, and the application of tools like goal seek, data tables, and scenario managers, with practical examples.', 'chapters': [{'end': 8659.593, 'start': 8618.108, 'title': 'Hlookup and formula auditing', 'summary': 'Covers hlookup, demonstrating its functionality with an example, and goes on to explain formula auditing, specifically focusing on trace settings, precedence, and dependence.', 'duration': 41.485, 'highlights': ['The chapter covers HLOOKUP and its functionality with an example.', 'Explains formula auditing, focusing on trace settings, precedence, and dependence.', 'Tracing precedence and dependence help the user to identify the reference cells used in the formula or function.', "Precedence are cells or ranges that affect the active cell's value."]}, {'end': 9254.053, 'start': 8660.253, 'title': 'Excel formula auditing and data validation', 'summary': 'Explains the use of formula auditing tools to trace precedents and dependents, identify and rectify errors, and show formulas in excel. it also demonstrates the application of data validation to control and validate user inputs in cells.', 'duration': 593.8, 'highlights': ['The chapter explains the use of formula auditing tools to trace precedents and dependents, identify and rectify errors, and show formulas in Excel.', 'It also demonstrates the application of data validation to control and validate user inputs in cells.']}, {'end': 9722.233, 'start': 9254.293, 'title': 'Excel data validation and what-if analysis', 'summary': 'Covers excel data validation techniques including setting input messages and custom error messages, and also explains the what-if tools such as goal seek, data tables, and scenario managers, with examples and practical applications.', 'duration': 467.94, 'highlights': ['The chapter covers Excel data validation techniques including setting input messages and custom error messages.', 'Explains the what-if tools such as goal seek, data tables, and scenario managers, with examples and practical applications.', 'Demonstrates the use of COUNTIF function for avoiding duplicate entries in data validation.']}, {'end': 9983.355, 'start': 9722.733, 'title': 'Excel what-if analysis', 'summary': 'Discusses the application of goal seek and data tables in excel, where goal seek is used to find the necessary input to achieve a desired output and data tables are utilized to analyze the impact of changing variables on the final result.', 'duration': 260.622, 'highlights': ['The chapter discusses the application of Goal Seek and Data Tables in Excel.', 'Goal Seek is used to find the necessary input to achieve a desired output.', 'Data Tables are utilized to analyze the impact of changing variables on the final result.']}], 'duration': 1365.247, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ8618108.jpg', 'highlights': ['The chapter covers Excel data validation techniques with input and error messages.', 'Explains what-if tools like goal seek, data tables, and scenario managers with practical examples.', 'Demonstrates HLOOKUP functionality and formula auditing with trace settings, precedence, and dependence.', 'Discusses the application of Goal Seek and Data Tables in Excel for analysis and finding necessary inputs.']}, {'end': 11660.767, 'segs': [{'end': 10244.44, 'src': 'embed', 'start': 10223.823, 'weight': 0, 'content': [{'end': 10238.915, 'text': "now, the next thing that i have to do is i'll have to put a formula in my list wherein i'm saying that number of books that i have sold at the higher price and number of books that i have sold at a lower price and what is the unit price of which is sold.", 'start': 10223.823, 'duration': 15.092}, {'end': 10244.44, 'text': 'so i have started putting it in the list where i say that the number of books that has been sold at a higher price is 60..', 'start': 10238.915, 'duration': 5.525}], 'summary': 'Formulating a list of books sold: 60 at higher price.', 'duration': 20.617, 'max_score': 10223.823, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10223823.jpg'}, {'end': 10481.262, 'src': 'embed', 'start': 10445.982, 'weight': 1, 'content': [{'end': 10446.662, 'text': 'which you can see.', 'start': 10445.982, 'duration': 0.68}, {'end': 10448.763, 'text': 'it is now 90%, and so it is showing that.', 'start': 10446.662, 'duration': 2.101}, {'end': 10454.785, 'text': 'But it will also show you the other percentages that we had decided which is 60, 70, 80, 90.', 'start': 10449.063, 'duration': 5.722}, {'end': 10460.848, 'text': 'You can actually click on summary and you can get this data in a tabular format where you can see the data in the format that you wanted.', 'start': 10454.785, 'duration': 6.063}, {'end': 10464.469, 'text': 'So you can now decide if it is 80% or 90% that you want to sell your books at.', 'start': 10460.888, 'duration': 3.581}, {'end': 10466.65, 'text': 'This is how your scenario managers work.', 'start': 10464.729, 'duration': 1.921}, {'end': 10468.751, 'text': "Let's move and see to the next one.", 'start': 10467.09, 'duration': 1.661}, {'end': 10471.499, 'text': 'We have already seen the gold seek and the data tables.', 'start': 10469.318, 'duration': 2.181}, {'end': 10473.459, 'text': 'How is the duplicate removals work?', 'start': 10471.519, 'duration': 1.94}, {'end': 10475.18, 'text': 'On an Excel sheet.', 'start': 10473.879, 'duration': 1.301}, {'end': 10481.262, 'text': "if you have a duplicate value in any of your cells, for example, let's go to one of the values that I can see here.", 'start': 10475.18, 'duration': 6.082}], 'summary': 'Scenario manager allows you to view and analyze percentages and make decisions based on the data presented.', 'duration': 35.28, 'max_score': 10445.982, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10445982.jpg'}, {'end': 10512.83, 'src': 'embed', 'start': 10485.163, 'weight': 7, 'content': [{'end': 10488.024, 'text': 'We have already seen the conditional formatting, how to highlight duplicates.', 'start': 10485.163, 'duration': 2.861}, {'end': 10491.685, 'text': 'We select the whole cell, highlight cell rules and then click on duplicate values.', 'start': 10488.364, 'duration': 3.321}, {'end': 10494.046, 'text': 'All the duplicate values will be selected here.', 'start': 10492.025, 'duration': 2.021}, {'end': 10496.345, 'text': 'Now this was only to highlight the duplicates.', 'start': 10494.784, 'duration': 1.561}, {'end': 10502.587, 'text': 'If I want to remove the duplicate, I can click on data under data in the data tools group, I can see remove duplicates.', 'start': 10496.385, 'duration': 6.202}, {'end': 10503.827, 'text': 'I can click on remove duplicates.', 'start': 10502.647, 'duration': 1.18}, {'end': 10507.208, 'text': 'It will select the whole table that you have on the list.', 'start': 10504.287, 'duration': 2.921}, {'end': 10512.83, 'text': 'You can either select the whole table or deselect and select only the ones which you want to remove duplicates from.', 'start': 10507.569, 'duration': 5.261}], 'summary': 'The process to highlight and remove duplicates in excel, including specific steps and actions.', 'duration': 27.667, 'max_score': 10485.163, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10485163.jpg'}, {'end': 10604.025, 'src': 'embed', 'start': 10578.756, 'weight': 8, 'content': [{'end': 10588.8, 'text': 'you are trying to send to make it look better and even work on it in such a way that your data looks better and you are able to get the desired results.', 'start': 10578.756, 'duration': 10.044}, {'end': 10592.861, 'text': 'so the first thing that we are going to see is the data sanitization is a sorting.', 'start': 10588.8, 'duration': 4.061}, {'end': 10597.463, 'text': 'any data that you have in the list can be sorted as per your requirement.', 'start': 10592.861, 'duration': 4.602}, {'end': 10604.025, 'text': 'So, for example, if you ever go to a departmental store, every department has different kinds of product.', 'start': 10597.833, 'duration': 6.192}], 'summary': 'Improve data presentation and sorting for better results.', 'duration': 25.269, 'max_score': 10578.756, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10578756.jpg'}, {'end': 10686.797, 'src': 'embed', 'start': 10660.951, 'weight': 9, 'content': [{'end': 10665.875, 'text': 'click on sort and then I can select from the list which data I want to sort it through.', 'start': 10660.951, 'duration': 4.924}, {'end': 10671.579, 'text': 'So sort by, I say, region, and A to Z, that means your alphabetical order.', 'start': 10666.195, 'duration': 5.384}, {'end': 10674.261, 'text': 'So I click on okay, it will sort as per the region.', 'start': 10671.619, 'duration': 2.642}, {'end': 10679.445, 'text': 'So all the east will be together, all the north will be together, then your west, then your south.', 'start': 10674.281, 'duration': 5.164}, {'end': 10682.607, 'text': 'This is how your data will be sorted as per the request.', 'start': 10679.825, 'duration': 2.782}, {'end': 10684.035, 'text': 'next is the.', 'start': 10683.354, 'duration': 0.681}, {'end': 10685.456, 'text': 'in the list is the filter.', 'start': 10684.035, 'duration': 1.421}, {'end': 10686.797, 'text': 'how does the filter work?', 'start': 10685.456, 'duration': 1.341}], 'summary': 'Data can be sorted by region alphabetically, and filtered by specific criteria.', 'duration': 25.846, 'max_score': 10660.951, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10660951.jpg'}, {'end': 10876.376, 'src': 'embed', 'start': 10846.349, 'weight': 10, 'content': [{'end': 10852.474, 'text': 'looking at the same example here, i have wrapped the text, but you can see that the data has become a little bigger.', 'start': 10846.349, 'duration': 6.125}, {'end': 10856.057, 'text': 'now this question is applicable to this whole data.', 'start': 10852.474, 'duration': 3.583}, {'end': 10863.164, 'text': "if i don't want to show that in one cell, i can make it into two or three different cells together, so that becomes.", 'start': 10856.758, 'duration': 6.406}, {'end': 10869.43, 'text': 'this row will be little smaller and you will be able to see it together, so i can merge this three cells together.', 'start': 10863.164, 'duration': 6.266}, {'end': 10876.376, 'text': 'now what happens is the data is still visible with a smaller row height and you have merged the cell,', 'start': 10869.43, 'duration': 6.946}], 'summary': 'Data was wrapped to reduce cell size, and cells were merged to make data visible with smaller row height.', 'duration': 30.027, 'max_score': 10846.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10846349.jpg'}, {'end': 10955.615, 'src': 'embed', 'start': 10918.926, 'weight': 11, 'content': [{'end': 10922.069, 'text': 'If I now want to delete the comment I can right click and delete the comment.', 'start': 10918.926, 'duration': 3.143}, {'end': 10923.77, 'text': 'This is how your comments work.', 'start': 10922.469, 'duration': 1.301}, {'end': 10926.632, 'text': 'Moving on to the next one insert shapes.', 'start': 10924.13, 'duration': 2.502}, {'end': 10932.076, 'text': 'Depending on your requirement, you sometimes will need to insert the shapes on your Excel sheet.', 'start': 10927.168, 'duration': 4.908}, {'end': 10935.28, 'text': 'There are different types of shapes that you can use.', 'start': 10932.436, 'duration': 2.844}, {'end': 10939.427, 'text': 'Under the insert command, under the illustrations group, you will see something called as shapes.', 'start': 10935.561, 'duration': 3.866}, {'end': 10945.153, 'text': 'Under shapes, you have this arrow, square boxes, round, circle, even the block arrows.', 'start': 10939.952, 'duration': 5.201}, {'end': 10946.773, 'text': 'All these information can be used.', 'start': 10945.373, 'duration': 1.4}, {'end': 10949.114, 'text': 'The callouts are there which you can use for your advantage.', 'start': 10946.793, 'duration': 2.321}, {'end': 10955.615, 'text': 'So I can just use this kind of shapes that you are requiring and then make the changes as per in the shapes.', 'start': 10949.494, 'duration': 6.121}], 'summary': 'Excel allows insertion of various shapes and callouts for customization.', 'duration': 36.689, 'max_score': 10918.926, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10918926.jpg'}, {'end': 11005.865, 'src': 'embed', 'start': 10977.724, 'weight': 13, 'content': [{'end': 10980.445, 'text': 'So instead of putting that comment, you can also use a call out in there.', 'start': 10977.724, 'duration': 2.721}, {'end': 10982.526, 'text': 'You can do a lot of things with the shapes.', 'start': 10980.765, 'duration': 1.761}, {'end': 10985.528, 'text': 'Moving on to the next one is the insert text box.', 'start': 10983.066, 'duration': 2.462}, {'end': 10988.749, 'text': 'Similar to the shapes, you can also insert a text box.', 'start': 10985.568, 'duration': 3.181}, {'end': 10990.69, 'text': 'There you will not get any colors and all.', 'start': 10988.949, 'duration': 1.741}, {'end': 10992.211, 'text': 'It is just the text box.', 'start': 10991.01, 'duration': 1.201}, {'end': 10996.953, 'text': 'That means you can add a text box and enter your data as per the data that you need.', 'start': 10992.231, 'duration': 4.722}, {'end': 10998.963, 'text': 'So I put a text box here.', 'start': 10997.443, 'duration': 1.52}, {'end': 10999.803, 'text': 'I enter the data.', 'start': 10999.083, 'duration': 0.72}, {'end': 11005.865, 'text': 'It will hide all the grid lines at the back and you will be able to see the text box on your sheet.', 'start': 11000.144, 'duration': 5.721}], 'summary': 'Using shapes and text boxes to input data and enhance visibility on sheets.', 'duration': 28.141, 'max_score': 10977.724, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ10977724.jpg'}, {'end': 11041.019, 'src': 'embed', 'start': 11016.047, 'weight': 2, 'content': [{'end': 11022.028, 'text': 'advanced filter also has its benefits and it can work with the more than two or three conditions that you have on the cell.', 'start': 11016.047, 'duration': 5.981}, {'end': 11025.965, 'text': 'Where I have my example, I have the employee code details.', 'start': 11022.62, 'duration': 3.345}, {'end': 11030.152, 'text': 'The first name, last name, department, region has been given to me with the salary information.', 'start': 11026.266, 'duration': 3.886}, {'end': 11034.499, 'text': 'First thing that I have to do with the advanced filter is to create a criteria range.', 'start': 11030.633, 'duration': 3.866}, {'end': 11036.798, 'text': 'That means I have to create a criteria table.', 'start': 11034.878, 'duration': 1.92}, {'end': 11039.579, 'text': 'When I create a criteria table, I have to see what is my criteria.', 'start': 11037.159, 'duration': 2.42}, {'end': 11041.019, 'text': 'My first criteria is department.', 'start': 11039.599, 'duration': 1.42}], 'summary': 'The advanced filter can handle more than two or three conditions, such as creating a criteria table for filtering employee details.', 'duration': 24.972, 'max_score': 11016.047, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11016047.jpg'}, {'end': 11255.681, 'src': 'embed', 'start': 11225.395, 'weight': 3, 'content': [{'end': 11227.056, 'text': 'The first one is average function.', 'start': 11225.395, 'duration': 1.661}, {'end': 11232.521, 'text': "What does the average function do? It's an inbuilt function in Excel that is categorized as statistical function.", 'start': 11227.557, 'duration': 4.964}, {'end': 11236.664, 'text': 'Average does exactly what it says and works similar to sum.', 'start': 11233.081, 'duration': 3.583}, {'end': 11240.307, 'text': 'It will return the average value of the given series of numbers in Excel.', 'start': 11237.185, 'duration': 3.122}, {'end': 11246.836, 'text': "Let's say we need to identify the average salary paid to the employees in the organization.", 'start': 11240.868, 'duration': 5.968}, {'end': 11249.818, 'text': 'Where is our salary data? In the H column.', 'start': 11247.457, 'duration': 2.361}, {'end': 11255.681, 'text': "So what I'll do is I'll enter the average function followed by the open bracket.", 'start': 11250.338, 'duration': 5.343}], 'summary': 'The average function in excel calculates the average value of a series of numbers, such as identifying the average salary in a given column.', 'duration': 30.286, 'max_score': 11225.395, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11225395.jpg'}, {'end': 11363.313, 'src': 'embed', 'start': 11337.267, 'weight': 4, 'content': [{'end': 11342.468, 'text': 'The Excel mode function returns the most frequently occurring numbers in the numeric data set.', 'start': 11337.267, 'duration': 5.201}, {'end': 11345.369, 'text': 'This function only works with numbers.', 'start': 11343.248, 'duration': 2.121}, {'end': 11352.53, 'text': 'It will identify amount or the number that occurs maximum time in the range of cells.', 'start': 11346.009, 'duration': 6.521}, {'end': 11356.431, 'text': 'So, for example, here we have the basic salary.', 'start': 11353.29, 'duration': 3.141}, {'end': 11363.313, 'text': 'where I can see the highest number is 49, 000, and then there are few employees earning the same salary.', 'start': 11356.431, 'duration': 6.882}], 'summary': 'Excel mode function identifies most frequent numbers in numeric data set, such as highest salary of $49,000.', 'duration': 26.046, 'max_score': 11337.267, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11337267.jpg'}, {'end': 11422.335, 'src': 'embed', 'start': 11390.584, 'weight': 6, 'content': [{'end': 11394.365, 'text': 'Moving on to the next one is the standard deviation function.', 'start': 11390.584, 'duration': 3.781}, {'end': 11401.261, 'text': "What is a standard deviation? It's a formula is to identify the standard deviation of the set of numbers.", 'start': 11395.157, 'duration': 6.104}, {'end': 11406.765, 'text': 'The standard deviation function calculates the standard deviation in the sample set of data.', 'start': 11402.102, 'duration': 4.663}, {'end': 11415.651, 'text': 'Standard deviation is a measure of how much variance there is in a set of numbers compared to the average of the numbers.', 'start': 11407.505, 'duration': 8.146}, {'end': 11422.335, 'text': 'Standard deviation dot S function is meant to estimate standard deviation in a sample.', 'start': 11416.471, 'duration': 5.864}], 'summary': 'Standard deviation function calculates variance in a sample set of data.', 'duration': 31.751, 'max_score': 11390.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11390584.jpg'}], 'start': 9983.395, 'title': 'Excel data analysis', 'summary': "Delves into using data tables and scenario manager for profit analysis, excel's scenario manager for profit scenarios and data sanitization, data management techniques including sorting, filtering, and text manipulation, working with comments, shapes, text boxes, and advanced filter, and applying advanced filter and statistical functions in excel.", 'chapters': [{'end': 10305.387, 'start': 9983.395, 'title': 'Using data tables and scenario manager for profit analysis', 'summary': 'Explores the use of data tables to analyze revenue based on changing discount percentages and sale prices, as well as the application of scenario manager to determine profit from selling books at different prices and percentages.', 'duration': 321.992, 'highlights': ['Using data tables to analyze revenue based on changing discount percentages and sale prices', 'Application of scenario manager to determine profit from selling books at different prices and percentages']}, {'end': 10597.463, 'start': 10305.387, 'title': 'Excel scenario manager and data sanitization', 'summary': "Demonstrates using excel's scenario manager to analyze different profit scenarios by changing the percentage of books sold at higher prices and also covers the process of removing duplicates from a dataset.", 'duration': 292.076, 'highlights': ['Using scenario manager to analyze different profit scenarios by changing the percentage of books sold at higher prices.', 'Process of removing duplicates from a dataset in Excel.', 'Explanation of data sanitization through sorting in Excel.']}, {'end': 10883.3, 'start': 10597.833, 'title': 'Excel data management', 'summary': 'Explains how to sort, filter, and manage text and cell merging in excel, exemplifying data organization and manipulation, including sorting data by region and filtering salary ranges, and demonstrating how to handle text overflow and merge cells.', 'duration': 285.467, 'highlights': ['The chapter explains sorting data by region and filtering salary ranges as key features of data organization and manipulation in Excel.', 'The chapter illustrates techniques for handling text overflow and merging cells as essential elements of data management in Excel.']}, {'end': 11126.222, 'start': 10883.8, 'title': 'Working with comments, shapes, text boxes, and advanced filter in excel', 'summary': 'Explains how to insert and manage comments, shapes, and text boxes in excel, and demonstrates the use of advanced filter with multiple criteria and range selections.', 'duration': 242.422, 'highlights': ['The chapter explains how to insert and manage comments in Excel, including adding, editing, and deleting comments, and using callouts instead of comments.', 'It demonstrates the process of inserting and customizing different shapes in Excel, including arrows, square boxes, circles, and callouts.', 'The chapter also covers inserting and managing text boxes in Excel, which can be used to display data without gridlines.', 'The chapter demonstrates the usage of advanced filter in Excel, explaining the creation of criteria range and the application of multiple filtering conditions.']}, {'end': 11660.767, 'start': 11126.222, 'title': 'Excel advanced filter and statistical functions', 'summary': 'Covers the application of advanced filter in excel to display employees in the marketing department with specific criteria, and statistical functions including average, median, mode, standard deviation, and large function, with examples and their results.', 'duration': 534.545, 'highlights': ['The advanced filter in Excel is used to display employees in the marketing department with specific criteria, including region and salary range, enabling efficient data manipulation and analysis.', 'The average function in Excel calculates the average salary paid to employees in the organization, providing a clear insight into the average compensation received by the employees.', 'The large function in Excel is utilized to identify the nth largest value from the sample, facilitating the retrieval of specific data points such as the highest salary in the employee list.', 'The mode function in Excel identifies the value that occurs most frequently in a given range of cells, aiding in the determination of the most common salary in the dataset.', 'The standard deviation function in Excel calculates the measure of variance in a set of numbers, aiding in assessing the distribution of salaries in the dataset.']}], 'duration': 1677.372, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ9983395.jpg', 'highlights': ['Using data tables to analyze revenue based on changing discount percentages and sale prices', 'Application of scenario manager to determine profit from selling books at different prices and percentages', 'The advanced filter in Excel is used to display employees in the marketing department with specific criteria, enabling efficient data manipulation and analysis', 'The average function in Excel calculates the average salary paid to employees in the organization, providing a clear insight into the average compensation received by the employees', 'The large function in Excel is utilized to identify the nth largest value from the sample, facilitating the retrieval of specific data points such as the highest salary in the employee list', 'The mode function in Excel identifies the value that occurs most frequently in a given range of cells, aiding in the determination of the most common salary in the dataset', 'The standard deviation function in Excel calculates the measure of variance in a set of numbers, aiding in assessing the distribution of salaries in the dataset', 'Process of removing duplicates from a dataset in Excel', 'Explanation of data sanitization through sorting in Excel', 'The chapter explains sorting data by region and filtering salary ranges as key features of data organization and manipulation in Excel', 'The chapter illustrates techniques for handling text overflow and merging cells as essential elements of data management in Excel', 'The chapter explains how to insert and manage comments in Excel, including adding, editing, and deleting comments, and using callouts instead of comments', 'It demonstrates the process of inserting and customizing different shapes in Excel, including arrows, square boxes, circles, and callouts', 'The chapter also covers inserting and managing text boxes in Excel, which can be used to display data without gridlines', 'The advanced filter in Excel is used to display employees in the marketing department with specific criteria, including region and salary range, enabling efficient data manipulation and analysis']}, {'end': 12942.258, 'segs': [{'end': 11713.924, 'src': 'embed', 'start': 11684.822, 'weight': 0, 'content': [{'end': 11686.503, 'text': 'Earlier it was 49, 000 was the largest.', 'start': 11684.822, 'duration': 1.681}, {'end': 11696.565, 'text': "so this way, if you keep on changing anybody else's salary, say i make it to 65 000, just to get a gist of how it works.", 'start': 11688.82, 'duration': 7.745}, {'end': 11699.506, 'text': 'if you will see that earlier it was 49 000 was the largest.', 'start': 11696.565, 'duration': 2.941}, {'end': 11703.228, 'text': 'now the 60 000 has become the largest salary or the second largest salary.', 'start': 11699.506, 'duration': 3.722}, {'end': 11704.589, 'text': 'same goes with the small.', 'start': 11703.228, 'duration': 1.361}, {'end': 11711.433, 'text': 'i am trying to identify what is the smallest salary that i am paying or the lowest salary that i am paying to the employee.', 'start': 11704.589, 'duration': 6.844}, {'end': 11713.924, 'text': 'Now again I have to give the K value.', 'start': 11711.843, 'duration': 2.081}], 'summary': 'Salary ranges shifted from 49,000 to 60,000, with new lowest and highest salaries identified.', 'duration': 29.102, 'max_score': 11684.822, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11684822.jpg'}, {'end': 11761.591, 'src': 'embed', 'start': 11725.911, 'weight': 2, 'content': [{'end': 11727.052, 'text': 'Is it the third that you want to know?', 'start': 11725.911, 'duration': 1.141}, {'end': 11733.776, 'text': 'So, according to what is your requirement, you can put the number there as say if I put two.', 'start': 11727.592, 'duration': 6.184}, {'end': 11740.099, 'text': 'So it will give me the second lowest salary paid to the employees in the organization, which is $7, 000.', 'start': 11733.836, 'duration': 6.263}, {'end': 11745.523, 'text': "If I make the lowest salary as even more lower, so let's see what is our lowest salary.", 'start': 11740.1, 'duration': 5.423}, {'end': 11748.164, 'text': 'It is 5950.', 'start': 11746.063, 'duration': 2.101}, {'end': 11751.846, 'text': "Let's make one of them to 5500.", 'start': 11748.164, 'duration': 3.682}, {'end': 11761.591, 'text': 'What will happen is now if I go back and see the second lowest salary value, it is now 5950 because the first has now changed to 5500.', 'start': 11751.846, 'duration': 9.745}], 'summary': 'Finding second lowest salary: $7,000 to $5,950 with changes to lowest salaries.', 'duration': 35.68, 'max_score': 11725.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11725911.jpg'}, {'end': 11827.12, 'src': 'embed', 'start': 11797.52, 'weight': 4, 'content': [{'end': 11801.824, 'text': "However, if you're not done it, which is not a big deal, the Excel is already doing it for you.", 'start': 11797.52, 'duration': 4.304}, {'end': 11808.49, 'text': 'Correlation coefficient formula are used to find out how strong a relationship is between the two data.', 'start': 11802.564, 'duration': 5.926}, {'end': 11815.996, 'text': 'Like here in our case, the relation between the age and the relation between the glucose level of the person.', 'start': 11809.07, 'duration': 6.926}, {'end': 11819.799, 'text': 'Is it that the age increases and the glucose level increases?', 'start': 11816.717, 'duration': 3.082}, {'end': 11822.522, 'text': 'or is it that the age decreases and the glucose level decreases??', 'start': 11819.799, 'duration': 2.723}, {'end': 11827.12, 'text': 'Or is it that the age increases and glucose level decreases and vice versa?', 'start': 11823.278, 'duration': 3.842}], 'summary': 'Correlation coefficient measures relationship strength between age and glucose levels.', 'duration': 29.6, 'max_score': 11797.52, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11797520.jpg'}, {'end': 12210.237, 'src': 'embed', 'start': 12180.267, 'weight': 5, 'content': [{'end': 12189.892, 'text': 'Similarly, if there is a decrease in the S&P 500, accordingly, the stock prices of A will decrease by 1%.', 'start': 12180.267, 'duration': 9.625}, {'end': 12191.794, 'text': "That's how the exact correlation is.", 'start': 12189.892, 'duration': 1.902}, {'end': 12196.396, 'text': 'While in the previous example, there is 0.5 correlation.', 'start': 12192.374, 'duration': 4.022}, {'end': 12203.72, 'text': 'That means when there is a one-year increase in the age, 0.5% of increase in the glucose level.', 'start': 12196.436, 'duration': 7.284}, {'end': 12210.237, 'text': "So that's how the correlation is identified using the CORAL function in MS Excel.", 'start': 12204.332, 'duration': 5.905}], 'summary': 'S&p 500 decrease corresponds to 1% stock decrease, age increase relates to 0.5% glucose increase, demonstrated by coral function in ms excel.', 'duration': 29.97, 'max_score': 12180.267, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ12180267.jpg'}, {'end': 12280.16, 'src': 'embed', 'start': 12252.566, 'weight': 6, 'content': [{'end': 12255.027, 'text': 'That means there is a data that is already available.', 'start': 12252.566, 'duration': 2.461}, {'end': 12267.232, 'text': 'where I have the data for the region along or rather the sales data for each region which is split into two years which is 2016 and 2017.', 'start': 12255.845, 'duration': 11.387}, {'end': 12268.813, 'text': 'This data is in millions.', 'start': 12267.232, 'duration': 1.581}, {'end': 12279.279, 'text': 'So you can see that the Mumbai has done a sales of 65 million in 2016 while it has done the sale of 70 million in 2017.', 'start': 12269.593, 'duration': 9.686}, {'end': 12280.16, 'text': 'Same goes with London.', 'start': 12279.279, 'duration': 0.881}], 'summary': 'Sales data for mumbai and london: 65 million in 2016, 70 million in 2017.', 'duration': 27.594, 'max_score': 12252.566, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ12252566.jpg'}, {'end': 12344.784, 'src': 'embed', 'start': 12321.655, 'weight': 7, 'content': [{'end': 12329.859, 'text': 'That means whenever you select any data and then click on insert it will give you an option as recommended chart,', 'start': 12321.655, 'duration': 8.204}, {'end': 12336.121, 'text': 'where the Excel will recommend you the types of chart that will look good with these kind of data,', 'start': 12329.859, 'duration': 6.262}, {'end': 12339.863, 'text': 'or rather it will properly represent this data which you are looking at.', 'start': 12336.121, 'duration': 3.742}, {'end': 12344.784, 'text': 'So I select the data that I want the graph for and then I go to recommended charts.', 'start': 12340.363, 'duration': 4.421}], 'summary': 'Excel offers recommended charts based on selected data.', 'duration': 23.129, 'max_score': 12321.655, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ12321655.jpg'}, {'end': 12386.169, 'src': 'embed', 'start': 12360.215, 'weight': 8, 'content': [{'end': 12365.199, 'text': "So you'll see the first one, the column chart is here even in the icon from 2016.", 'start': 12360.215, 'duration': 4.984}, {'end': 12368.341, 'text': 'First 2D chart that you can see is the column chart.', 'start': 12365.199, 'duration': 3.142}, {'end': 12372.584, 'text': 'I click on that and the data is coming next to one another.', 'start': 12368.621, 'duration': 3.963}, {'end': 12381.807, 'text': 'When is the column chart really useful? The column chart is useful in such kind of datas where there is a comparison required.', 'start': 12373.223, 'duration': 8.584}, {'end': 12386.169, 'text': 'So column charts use vertical bars to represent data.', 'start': 12382.408, 'duration': 3.761}], 'summary': 'Column chart, first introduced in 2016, useful for data comparison.', 'duration': 25.954, 'max_score': 12360.215, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ12360215.jpg'}, {'end': 12531.807, 'src': 'embed', 'start': 12501.903, 'weight': 9, 'content': [{'end': 12504.625, 'text': 'I click on plus and I click on data labels.', 'start': 12501.903, 'duration': 2.722}, {'end': 12508.488, 'text': 'It will automatically add data labels for both the bars.', 'start': 12504.965, 'duration': 3.523}, {'end': 12510.689, 'text': "I don't have to select each of the bars.", 'start': 12509.008, 'duration': 1.681}, {'end': 12513.071, 'text': 'This is how your data labels are added.', 'start': 12511.35, 'duration': 1.721}, {'end': 12518.677, 'text': 'Now if you can see there is an axis Y which is given here which has some numbers.', 'start': 12513.573, 'duration': 5.104}, {'end': 12527.964, 'text': "Why those numbers are there? Because earlier we did not have data labels and that's the reason we could see with these numbers where our taser sits.", 'start': 12519.258, 'duration': 8.706}, {'end': 12531.807, 'text': 'So just about 60 somewhere between 60 and 70.', 'start': 12528.485, 'duration': 3.322}], 'summary': 'Data labels added automatically, no need to select each bar. axis y numbers indicate taser position, around 60-70.', 'duration': 29.904, 'max_score': 12501.903, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ12501903.jpg'}], 'start': 11660.768, 'title': 'Analyzing data in excel', 'summary': 'Covers various data analysis techniques in excel, including identifying largest and smallest salaries, using correlation function, sales data comparison, and excel chart customization. it demonstrates the impact of salary changes, correlation coefficient calculation, sales data comparison, and chart customization for effective data presentation.', 'chapters': [{'end': 11751.846, 'start': 11660.768, 'title': 'Identifying largest and smallest salaries', 'summary': "Demonstrates how to identify the largest and smallest salaries in an organization, showing the impact of salary changes and the use of a variable 'k' to determine the specific lowest salary paid.", 'duration': 91.078, 'highlights': ['The largest salary changed from 49,000 to 60,000 after a salary adjustment, demonstrating the impact of changes on identifying the largest salary.', 'By adjusting salaries, the second largest salary changed from 24,500 to 49,000, highlighting the dynamic nature of salary rankings within the organization.', "Using the variable 'K', the chapter illustrates how to determine the second lowest salary, which is $7,000, showcasing the flexibility in identifying specific lowest salaries.", 'The lowest salary in the organization was identified as $5,500, emphasizing the ability to track and analyze the smallest salaries paid to employees.']}, {'end': 12252.526, 'start': 11751.846, 'title': 'Correlation function in excel', 'summary': 'Explains the correlation function in excel, which calculates the correlation coefficient between variables, with examples showing the use of the function and its implications, such as a 0.53 correlation between age and glucose level indicating a positive relationship, and a 0.9 correlation between s&p 500 change and stock a change showing a strong positive relationship.', 'duration': 500.68, 'highlights': ['The correlation function in Excel calculates the correlation coefficient between variables, such as age and glucose level, with a 0.53 correlation indicating a positive relationship between the two variables.', 'An example shows a 0.9 correlation between S&P 500 change and stock A change, indicating that for every 1% increase or decrease in S&P 500, there is a corresponding 1% increase or decrease in stock A prices.', 'The chapter also introduces the concept of using charts in Excel to graphically represent data, with a focus on column charts to illustrate workbook data graphically.']}, {'end': 12543.493, 'start': 12252.566, 'title': 'Sales data comparison and chart creation', 'summary': 'Discusses the comparison of sales data for different regions in 2016 and 2017, and the process of creating a recommended column chart in excel to visually represent the data, with a focus on adding and removing data labels for better visualization.', 'duration': 290.927, 'highlights': ['The sales data for different regions in 2016 and 2017 is provided, with Mumbai showing a sales of 65 million in 2016 and 70 million in 2017, London with 55 million in 2016 and 65 million in 2017, and USA with 45 million and 52 million for 2016 and 2017 respectively.', 'The process of creating a recommended column chart in Excel is explained, where the software recommends the types of chart that will properly represent the selected data.', 'The importance of column charts in comparing data is emphasized, with a focus on their use of vertical bars to represent data, particularly for comparing information like the sales data for different years.', "The process of adding and removing data labels in the created chart is demonstrated, providing options to add data labels for individual bars or using the 'plus' sign to add data labels automatically for both bars."]}, {'end': 12942.258, 'start': 12543.974, 'title': 'Excel chart customization', 'summary': 'Discusses customizing excel charts, including removing chart elements, adding a chart title, and changing data range, and highlights how to remove grid lines, switch row and column data, and create different chart types like line and pie charts to effectively present data.', 'duration': 398.284, 'highlights': ['Removing grid lines', 'Switching row and column data', 'Creating a line chart for showing trends', 'Importance of pie charts in comparing proportions', 'Removing primary vertical axis from the chart']}], 'duration': 1281.49, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ11660768.jpg', 'highlights': ['The largest salary changed from 49,000 to 60,000 after a salary adjustment, demonstrating the impact of changes on identifying the largest salary.', 'By adjusting salaries, the second largest salary changed from 24,500 to 49,000, highlighting the dynamic nature of salary rankings within the organization.', "Using the variable 'K', the chapter illustrates how to determine the second lowest salary, which is $7,000, showcasing the flexibility in identifying specific lowest salaries.", 'The lowest salary in the organization was identified as $5,500, emphasizing the ability to track and analyze the smallest salaries paid to employees.', 'The correlation function in Excel calculates the correlation coefficient between variables, such as age and glucose level, with a 0.53 correlation indicating a positive relationship between the two variables.', 'An example shows a 0.9 correlation between S&P 500 change and stock A change, indicating that for every 1% increase or decrease in S&P 500, there is a corresponding 1% increase or decrease in stock A prices.', 'The sales data for different regions in 2016 and 2017 is provided, with Mumbai showing a sales of 65 million in 2016 and 70 million in 2017, London with 55 million in 2016 and 65 million in 2017, and USA with 45 million and 52 million for 2016 and 2017 respectively.', 'The process of creating a recommended column chart in Excel is explained, where the software recommends the types of chart that will properly represent the selected data.', 'The importance of column charts in comparing data is emphasized, with a focus on their use of vertical bars to represent data, particularly for comparing information like the sales data for different years.', "The process of adding and removing data labels in the created chart is demonstrated, providing options to add data labels for individual bars or using the 'plus' sign to add data labels automatically for both bars."]}, {'end': 14412.166, 'segs': [{'end': 12967.671, 'src': 'embed', 'start': 12942.258, 'weight': 0, 'content': [{'end': 12948.583, 'text': 'which book type is giving me the maximum revenue, while which book type is giving me the lowest revenue?', 'start': 12942.258, 'duration': 6.325}, {'end': 12953.646, 'text': "To do that I can use a pie chart, because it's going to give me the part of a pie like Here.", 'start': 12948.903, 'duration': 4.743}, {'end': 12956.727, 'text': 'you will see that the orange part is actually the mystery part.', 'start': 12953.646, 'duration': 3.081}, {'end': 12961.649, 'text': 'Mystery is being sold maximum part of the time and the revenue is also generated.', 'start': 12957.067, 'duration': 4.582}, {'end': 12967.671, 'text': 'Now similarly like we did it for the column chart, you can also use the data tables in the pie chart as well.', 'start': 12962.169, 'duration': 5.502}], 'summary': 'Mystery books generate maximum revenue, shown in pie chart.', 'duration': 25.413, 'max_score': 12942.258, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ12942258.jpg'}, {'end': 13152.907, 'src': 'embed', 'start': 13113.295, 'weight': 2, 'content': [{'end': 13121.236, 'text': 'However, if you have a bigger data, or even it will look better if you have the bar charts, which is shown in the horizontal level, like this,', 'start': 13113.295, 'duration': 7.941}, {'end': 13129.298, 'text': 'because you can clearly read the headings here on the left hand side and your bar charts goes on the right again, removing the grid lines,', 'start': 13121.236, 'duration': 8.062}, {'end': 13130.878, 'text': 'adding the data labels will remain the same.', 'start': 13129.298, 'duration': 1.58}, {'end': 13134.351, 'text': 'Moving on to the next one, that is a surface chart.', 'start': 13131.388, 'duration': 2.963}, {'end': 13138.056, 'text': 'What is a surface chart and how is it useful?', 'start': 13134.652, 'duration': 3.404}, {'end': 13146.724, 'text': 'surface charts are useful when you want to find the optimum combination between two sets of data, like in our list we have the marketing,', 'start': 13138.76, 'duration': 7.964}, {'end': 13152.907, 'text': 'finance and effort, and they are which field is used or which department is used?', 'start': 13146.724, 'duration': 6.183}], 'summary': 'Surface charts help find optimal data combinations for departments.', 'duration': 39.612, 'max_score': 13113.295, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13113295.jpg'}, {'end': 13194.055, 'src': 'embed', 'start': 13167.493, 'weight': 1, 'content': [{'end': 13172.119, 'text': 'How much is there in the expenses that has been formed part of these three departments?', 'start': 13167.493, 'duration': 4.626}, {'end': 13177.036, 'text': "So, if I want to know these, so I'm trying to combine the recruitment part.", 'start': 13172.611, 'duration': 4.425}, {'end': 13180.34, 'text': "I'm trying to identify the combination of marketing, finance and effort.", 'start': 13177.076, 'duration': 3.264}, {'end': 13183.603, 'text': 'How much recruitment has been done in the combination of all three.', 'start': 13180.7, 'duration': 2.903}, {'end': 13186.687, 'text': "In this kind of a situation, I'll be using the surface chart.", 'start': 13183.623, 'duration': 3.064}, {'end': 13188.809, 'text': 'So how do I use the surface chart??', 'start': 13187.087, 'duration': 1.722}, {'end': 13189.69, 'text': 'Where is it located??', 'start': 13188.849, 'duration': 0.841}, {'end': 13194.055, 'text': "Again, if I go to the amended chart also, I'll be able to see the surface chart under recommended chart.", 'start': 13190.15, 'duration': 3.905}], 'summary': 'Analyzing expenses and recruitment across marketing, finance, and effort using surface chart.', 'duration': 26.562, 'max_score': 13167.493, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13167493.jpg'}, {'end': 13233.586, 'src': 'embed', 'start': 13204.942, 'weight': 16, 'content': [{'end': 13207.144, 'text': 'So it gives you the effort marketing and finance.', 'start': 13204.942, 'duration': 2.202}, {'end': 13214.149, 'text': 'So you will see that the combination of the recruitment here, which is this part, is showing your total recruitment for all the three departments.', 'start': 13207.684, 'duration': 6.465}, {'end': 13216.019, 'text': 'Same goes with environment.', 'start': 13214.699, 'duration': 1.32}, {'end': 13219.961, 'text': 'How much environment is affected due to finance, marketing and effort.', 'start': 13216.1, 'duration': 3.861}, {'end': 13224.682, 'text': 'Asset that has been used by these three departments and so on and so forth.', 'start': 13220.361, 'duration': 4.321}, {'end': 13227.623, 'text': 'So how we are adding the values to this.', 'start': 13225.123, 'duration': 2.5}, {'end': 13229.264, 'text': 'So this is how it shows.', 'start': 13227.984, 'duration': 1.28}, {'end': 13233.586, 'text': 'You can again remove the grid lines if you want from this list so that it looks a little better.', 'start': 13229.744, 'duration': 3.842}], 'summary': 'Analyzing the impact of marketing, finance, and effort on recruitment, environment, and asset utilization.', 'duration': 28.644, 'max_score': 13204.942, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13204942.jpg'}, {'end': 13340.383, 'src': 'embed', 'start': 13313.666, 'weight': 13, 'content': [{'end': 13318.39, 'text': 'If you have well-organized source data, you can create a pivot table in less than a minute.', 'start': 13313.666, 'duration': 4.724}, {'end': 13320.171, 'text': "So let's see how we can do that.", 'start': 13318.85, 'duration': 1.321}, {'end': 13324.294, 'text': 'First of all, I have a sheet with all the data information there.', 'start': 13320.811, 'duration': 3.483}, {'end': 13331.597, 'text': 'Like in our sheet, I have the list of employee code, their names, department region, branch and their salary information.', 'start': 13324.713, 'duration': 6.884}, {'end': 13340.383, 'text': 'I am going to create a pivot table in order to show the different departments salary, how much salary are we paying to different departments.', 'start': 13332.298, 'duration': 8.085}], 'summary': 'Pivot table can be created in less than a minute using well-organized source data.', 'duration': 26.717, 'max_score': 13313.666, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13313666.jpg'}, {'end': 13381.996, 'src': 'embed', 'start': 13352.63, 'weight': 15, 'content': [{'end': 13355.745, 'text': 'After I click on Insert, There is an option called Pivot Table.', 'start': 13352.63, 'duration': 3.115}, {'end': 13356.865, 'text': 'I click on Pivot Table.', 'start': 13355.885, 'duration': 0.98}, {'end': 13358.406, 'text': 'A new window will pop up.', 'start': 13357.345, 'duration': 1.061}, {'end': 13364.008, 'text': 'In the Pivot Table Wizard, I have to select the table or the range for which the Pivot Table is to be created.', 'start': 13358.906, 'duration': 5.102}, {'end': 13367.33, 'text': 'So I select range A1 to H101.', 'start': 13364.608, 'duration': 2.722}, {'end': 13371.331, 'text': 'The next one that I have to select is either the new sheet or the existing sheet.', 'start': 13368.01, 'duration': 3.321}, {'end': 13372.432, 'text': 'New sheet.', 'start': 13371.932, 'duration': 0.5}, {'end': 13374.473, 'text': 'that means when I click New Sheet and click on OK,', 'start': 13372.432, 'duration': 2.041}, {'end': 13381.996, 'text': 'it will automatically create a new sheet in the existing workbook and the Pivot Table will be placed in that new sheet.', 'start': 13374.473, 'duration': 7.523}], 'summary': 'Create pivot table from range a1 to h101, on a new sheet.', 'duration': 29.366, 'max_score': 13352.63, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13352630.jpg'}, {'end': 13486.381, 'src': 'embed', 'start': 13455.87, 'weight': 4, 'content': [{'end': 13458.011, 'text': "So let's start with the values area, which is this one.", 'start': 13455.87, 'duration': 2.141}, {'end': 13463.437, 'text': 'The values area is a large rectangular area below to the right of the column in the row heading.', 'start': 13458.556, 'duration': 4.881}, {'end': 13466.257, 'text': 'The values area calculates and counts data.', 'start': 13463.917, 'duration': 2.34}, {'end': 13471.258, 'text': 'That means anything that you enter here, it will give you the calculation of that value.', 'start': 13466.717, 'duration': 4.541}, {'end': 13477.899, 'text': 'For example, if I pick salary, which I drag and put it on the values column, it will automatically give me the sum of the salary.', 'start': 13471.298, 'duration': 6.601}, {'end': 13480.52, 'text': 'You can see it says sum of basic salary.', 'start': 13478.46, 'duration': 2.06}, {'end': 13482.84, 'text': 'That means the total salary that has been paid.', 'start': 13480.54, 'duration': 2.3}, {'end': 13486.381, 'text': 'You can change this to any other field.', 'start': 13483.44, 'duration': 2.941}], 'summary': 'The values area calculates and counts data, e.g. sum of salary.', 'duration': 30.511, 'max_score': 13455.87, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13455870.jpg'}, {'end': 13549.108, 'src': 'embed', 'start': 13515.97, 'weight': 5, 'content': [{'end': 13519.354, 'text': 'fields such as sum of revenue, count of units, or average of price.', 'start': 13515.97, 'duration': 3.384}, {'end': 13521.897, 'text': 'Row area, which is this part.', 'start': 13519.935, 'duration': 1.962}, {'end': 13531.789, 'text': 'Placing a data field in a row area displays the unique value from that field down the rows on the left side of the pivot table.', 'start': 13522.498, 'duration': 9.291}, {'end': 13539.704, 'text': 'That means because I said that I want to do it at the department, when I drag it to the rows, it will pick up all the unique value.', 'start': 13532.421, 'duration': 7.283}, {'end': 13549.108, 'text': 'So even if admin is coming 10 times in our database, like admin comes a lot of times here, it will only show once in the pivot table.', 'start': 13540.144, 'duration': 8.964}], 'summary': 'Placing data field in row area displays unique values down the rows in pivot table.', 'duration': 33.138, 'max_score': 13515.97, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13515970.jpg'}, {'end': 13637.4, 'src': 'embed', 'start': 13599.135, 'weight': 6, 'content': [{'end': 13600.716, 'text': 'This is how it is giving you the split.', 'start': 13599.135, 'duration': 1.581}, {'end': 13603.658, 'text': 'Next one is the filter column or the filter area.', 'start': 13601.296, 'duration': 2.362}, {'end': 13609.162, 'text': 'Filter area is an optional set of one or more dropdown list at the top of the pivot table.', 'start': 13604.038, 'duration': 5.124}, {'end': 13612.584, 'text': 'Here the filter area contains the region field.', 'start': 13609.662, 'duration': 2.922}, {'end': 13616.867, 'text': 'The pivot table is set to show all the region like this.', 'start': 13613.184, 'duration': 3.683}, {'end': 13621.955, 'text': 'If I put it on the top here, it is actually an optional area, but it will only show me the region on the top.', 'start': 13617.714, 'duration': 4.241}, {'end': 13623.556, 'text': 'Then it will not show in the column.', 'start': 13622.415, 'duration': 1.141}, {'end': 13627.337, 'text': 'The filter area allows you to easily apply filters to the pivot table.', 'start': 13624.096, 'duration': 3.241}, {'end': 13637.4, 'text': 'For example, if I leave my region here and try to put a branch in the filters column, I can select which branch I need the data for.', 'start': 13627.657, 'duration': 9.743}], 'summary': 'Pivot table includes filter area with region dropdown list, allowing easy application of filters.', 'duration': 38.265, 'max_score': 13599.135, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13599135.jpg'}, {'end': 13727.828, 'src': 'embed', 'start': 13698.753, 'weight': 3, 'content': [{'end': 13702.354, 'text': "I go to data and the same way as we do the sort, I'll just click on sort there.", 'start': 13698.753, 'duration': 3.601}, {'end': 13709.037, 'text': 'Once I do that, it will give me a wizard where I have to select whether you have to sort as per smallest to largest or largest to smallest.', 'start': 13702.834, 'duration': 6.203}, {'end': 13710.977, 'text': 'I will do as largest to smallest.', 'start': 13709.057, 'duration': 1.92}, {'end': 13718.68, 'text': "And I can see that in the sales department, I'm giving the maximum salary, which is 3,45, 075.", 'start': 13711.718, 'duration': 6.962}, {'end': 13720.961, 'text': 'So this is how your sort works in the pivot table.', 'start': 13718.68, 'duration': 2.281}, {'end': 13727.828, 'text': 'So there are other things that you can also use in the pivot table which are really, really important that we are going to see here.', 'start': 13721.505, 'duration': 6.323}], 'summary': 'Sorting in pivot table by maximum salary, 3,45,075.', 'duration': 29.075, 'max_score': 13698.753, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13698753.jpg'}, {'end': 13827.489, 'src': 'embed', 'start': 13762.02, 'weight': 7, 'content': [{'end': 13769.946, 'text': 'So the same way, similarly like I did it for putting the branch on the top or the region on the top where I could filter the data,', 'start': 13762.02, 'duration': 7.926}, {'end': 13772.268, 'text': 'I can use the slicer to filter this data.', 'start': 13769.946, 'duration': 2.322}, {'end': 13781.095, 'text': 'So if I click on slicer and select which field I want to filter it with, like if I want to filter it with the hire date.', 'start': 13772.688, 'duration': 8.407}, {'end': 13783.355, 'text': 'I can select an hire date and click okay.', 'start': 13781.495, 'duration': 1.86}, {'end': 13789.637, 'text': 'Now it will give me a slicer or the list of hire dates which I can select from.', 'start': 13784.075, 'duration': 5.562}, {'end': 13798.859, 'text': 'So if I select only this hire date, it will automatically give me data for only the employees who have been hired on this particular date.', 'start': 13790.077, 'duration': 8.782}, {'end': 13801.679, 'text': 'So this is how your slicer works.', 'start': 13799.819, 'duration': 1.86}, {'end': 13808.441, 'text': 'However, now you can see that in the slicer okay, I can only select one data or one date at a time.', 'start': 13802.3, 'duration': 6.141}, {'end': 13814.505, 'text': 'But if I want to select more dates, then there is this something called as multi-select, which is on the top right-hand corner.', 'start': 13809.264, 'duration': 5.241}, {'end': 13817.226, 'text': 'This is only available in 2016 version.', 'start': 13814.525, 'duration': 2.701}, {'end': 13824.408, 'text': 'So when I click on multi-select, it will give me options to select multiple dates at a time in the slicer.', 'start': 13817.606, 'duration': 6.802}, {'end': 13827.489, 'text': 'So it is not necessary that you have to only do the dates.', 'start': 13824.908, 'duration': 2.581}], 'summary': 'Using slicer to filter data by hire date; multi-select allows choosing multiple dates at once.', 'duration': 65.469, 'max_score': 13762.02, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13762020.jpg'}, {'end': 13913.314, 'src': 'embed', 'start': 13885.379, 'weight': 9, 'content': [{'end': 13892.225, 'text': 'by going to analyze button and then pivot chart, it will automatically create the chart that I want to.', 'start': 13885.379, 'duration': 6.846}, {'end': 13896.469, 'text': 'Similarly, as we get the column chart in this case, so I click OK.', 'start': 13892.485, 'duration': 3.984}, {'end': 13899.792, 'text': 'Once I do that, it will automatically create your chart.', 'start': 13897.089, 'duration': 2.703}, {'end': 13907.388, 'text': 'In the chart, similar to the normal chart, you can give the name as a chart title that we are looking at.', 'start': 13900.501, 'duration': 6.887}, {'end': 13913.314, 'text': 'Add the data labels by clicking on this plus sign and click on add data or just add data labels.', 'start': 13907.989, 'duration': 5.325}], 'summary': 'Using the analyze button and pivot chart, creates the desired chart automatically.', 'duration': 27.935, 'max_score': 13885.379, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ13885379.jpg'}, {'end': 14215.102, 'src': 'embed', 'start': 14188.488, 'weight': 11, 'content': [{'end': 14197.513, 'text': 'The analysis tool pack is an Excel add-in program that provides data analysis tools for financial, statistical, and engineering data analysis.', 'start': 14188.488, 'duration': 9.025}, {'end': 14202.455, 'text': 'To load the data analysis tool pack, you have to execute some steps in Excel.', 'start': 14198.053, 'duration': 4.402}, {'end': 14205.617, 'text': "So how do you do that? Let's go to our Excel.", 'start': 14202.936, 'duration': 2.681}, {'end': 14208.438, 'text': 'On the file tab, I click on the options.', 'start': 14206.137, 'duration': 2.301}, {'end': 14215.102, 'text': 'Once I click on the options, under the add-ins, select the analysis tool pack and click on OK.', 'start': 14209.099, 'duration': 6.003}], 'summary': 'The excel analysis tool pack offers tools for financial, statistical, and engineering data analysis. it can be loaded by selecting it under add-ins in excel options.', 'duration': 26.614, 'max_score': 14188.488, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ14188488.jpg'}], 'start': 12942.258, 'title': 'Visualizing revenue data and pivot table functionality', 'summary': 'Explores using chart types like pie chart, bar chart, and surface chart to visualize revenue data, emphasizing the significance of mystery books. it also explains the functionality of a pivot table, including the values area, row area, column area, and filter area, demonstrating sorting, slicing, and creating pivot charts with slicers.', 'chapters': [{'end': 13204.602, 'start': 12942.258, 'title': 'Visualizing revenue data', 'summary': 'Explores using different chart types like pie chart, bar chart, and surface chart to visualize revenue data, emphasizing the significance of mystery books in generating maximum revenue and the utility of surface charts for analyzing the combination of marketing, finance, and effort.', 'duration': 262.344, 'highlights': ['Mystery books generate the maximum revenue, as indicated by the orange section of the pie chart.', 'Utilizing the surface chart to analyze the combination of marketing, finance, and effort in terms of recruitment and expenses.', 'Exploring the use of bar charts for data with larger headings and how they differ from column charts.']}, {'end': 13455.85, 'start': 13204.942, 'title': 'Excel charts and pivot tables', 'summary': 'Explains how to use surface charts in excel to visualize data and illustrates the process of creating a pivot table, which can be done in less than a minute if the source data is well-organized.', 'duration': 250.908, 'highlights': ['The chapter explains how to use surface charts in Excel to visualize data, with the effort, marketing, and finance being compared in terms of their impact on recruitment, environment, and assets.', 'It also demonstrates the process of creating a pivot table, which can be done in less than a minute if the source data is well-organized, with all the headings from the database automatically appearing in the pivot table fields.', "It highlights the speed and efficiency of pivot tables by emphasizing that they are incredibly fast compared to building an equivalent report manually, and that they provide a quick way to show different departments' salary information.", 'It covers the step-by-step process of creating a pivot table, including selecting the table or range, choosing the location for the pivot table, and understanding the four fields in the pivot table.', 'It provides insights into the impact of effort, marketing, and finance on recruitment, environment, and assets, with specific quantifiable data points such as the range of impact and the corresponding color codes on the surface chart.']}, {'end': 13680.856, 'start': 13455.87, 'title': 'Pivot table functionality', 'summary': 'Explains the functionality of a pivot table, including the values area for calculating data, row area for displaying unique values, column area for displaying headings, and filter area for applying filters to the pivot table, demonstrating the use of these areas with specific examples and options.', 'duration': 224.986, 'highlights': ['The values area calculates and counts data, allowing for different calculations such as sum, count, average, max, min, and standard deviation to be selected based on the data field.', 'Placing a data field in a row area displays the unique values from that field down the rows on the left side of the pivot table, grouping and summing the data accordingly.', 'The filter area contains dropdown lists to easily apply filters to the pivot table, allowing for the selection of specific data based on the applied filters.']}, {'end': 13863.981, 'start': 13681.056, 'title': 'Pivot table sorting and slicing', 'summary': 'Demonstrates how to sort pivot table data to find the department with the highest salary and how to use slicers for filtering data by date and other fields, with the option for multi-select in the 2016 version.', 'duration': 182.925, 'highlights': ['The maximum salary of 3,45,075 is given in the sales department.', 'Slicers in the pivot table enable filtering of data by hire date or other fields, allowing for data analysis based on specific criteria.', 'The 2016 version allows for multi-select in slicers, enabling the selection of multiple dates or other information for filtering.']}, {'end': 14412.166, 'start': 13864.662, 'title': 'Pivot chart creation and slicers in excel', 'summary': 'Discusses creating pivot charts from pivot tables, including adding data labels and hiding fields, as well as using slicers to link and filter data across multiple pivot tables. it also covers how to enable and use the analysis tool pack in excel for data analysis.', 'duration': 547.504, 'highlights': ['Pivot chart creation and customization', 'Using slicers to filter data in pivot tables', 'Enabling and utilizing the analysis tool pack in Excel']}], 'duration': 1469.908, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ12942258.jpg', 'highlights': ['Mystery books generate the maximum revenue, as indicated by the orange section of the pie chart.', 'Utilizing the surface chart to analyze the combination of marketing, finance, and effort in terms of recruitment and expenses.', 'Exploring the use of bar charts for data with larger headings and how they differ from column charts.', 'The maximum salary of 3,45,075 is given in the sales department.', 'The values area calculates and counts data, allowing for different calculations such as sum, count, average, max, min, and standard deviation to be selected based on the data field.', 'Placing a data field in a row area displays the unique values from that field down the rows on the left side of the pivot table, grouping and summing the data accordingly.', 'The filter area contains dropdown lists to easily apply filters to the pivot table, allowing for the selection of specific data based on the applied filters.', 'Slicers in the pivot table enable filtering of data by hire date or other fields, allowing for data analysis based on specific criteria.', 'The 2016 version allows for multi-select in slicers, enabling the selection of multiple dates or other information for filtering.', 'Pivot chart creation and customization', 'Using slicers to filter data in pivot tables', 'Enabling and utilizing the analysis tool pack in Excel', 'The chapter explains how to use surface charts in Excel to visualize data, with the effort, marketing, and finance being compared in terms of their impact on recruitment, environment, and assets.', 'It also demonstrates the process of creating a pivot table, which can be done in less than a minute if the source data is well-organized, with all the headings from the database automatically appearing in the pivot table fields.', "It highlights the speed and efficiency of pivot tables by emphasizing that they are incredibly fast compared to building an equivalent report manually, and that they provide a quick way to show different departments' salary information.", 'It covers the step-by-step process of creating a pivot table, including selecting the table or range, choosing the location for the pivot table, and understanding the four fields in the pivot table.', 'It provides insights into the impact of effort, marketing, and finance on recruitment, environment, and assets, with specific quantifiable data points such as the range of impact and the corresponding color codes on the surface chart.']}, {'end': 15845.315, 'segs': [{'end': 14462.046, 'src': 'embed', 'start': 14435.444, 'weight': 3, 'content': [{'end': 14439.632, 'text': 'Once I click on OK the answer will be displayed summary output box here.', 'start': 14435.444, 'duration': 4.188}, {'end': 14440.834, 'text': 'What does that say?', 'start': 14440.153, 'duration': 0.681}, {'end': 14449.931, 'text': 'So if you see the results here, you can see that there is a range that supplies some basic regression statistics, including the R square.', 'start': 14441.475, 'duration': 8.456}, {'end': 14451.923, 'text': 'So what is it doing??', 'start': 14450.763, 'duration': 1.16}, {'end': 14454.144, 'text': "It's giving you the basic regression statistics.", 'start': 14451.983, 'duration': 2.161}, {'end': 14457.805, 'text': "regression statistics means we ever trying to find the regression, and that's the reason.", 'start': 14454.144, 'duration': 3.661}, {'end': 14462.046, 'text': 'that is what is giving you the standard error, the number of observations.', 'start': 14457.805, 'duration': 4.241}], 'summary': 'The summary output box displays basic regression statistics, including the r square, upon clicking ok.', 'duration': 26.602, 'max_score': 14435.444, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ14435444.jpg'}, {'end': 14579.191, 'src': 'embed', 'start': 14552.008, 'weight': 2, 'content': [{'end': 14557.07, 'text': 'So macros are small programs that record the keystrokes as you perform a task in Excel.', 'start': 14552.008, 'duration': 5.062}, {'end': 14561.351, 'text': 'This will be saved in the visual basic module which is a type of program file.', 'start': 14557.41, 'duration': 3.941}, {'end': 14566.373, 'text': 'So when you run the macro it will repeat the keystrokes that are saved in the module and thus repeat your actions.', 'start': 14561.711, 'duration': 4.662}, {'end': 14570.603, 'text': 'This is why they are great to automate repetitive tasks in Excel.', 'start': 14566.84, 'duration': 3.763}, {'end': 14573.646, 'text': "So let's start with to see how does the macro work.", 'start': 14570.964, 'duration': 2.682}, {'end': 14579.191, 'text': 'So I have the sheet with me where I have some data which is entered on one of the sheets.', 'start': 14574.287, 'duration': 4.904}], 'summary': 'Macros in excel automate repetitive tasks by recording and repeating keystrokes.', 'duration': 27.183, 'max_score': 14552.008, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ14552008.jpg'}, {'end': 15109.962, 'src': 'embed', 'start': 15084.295, 'weight': 1, 'content': [{'end': 15089.357, 'text': 'Now there is also something called as the another button which is the button form control.', 'start': 15084.295, 'duration': 5.062}, {'end': 15093.818, 'text': 'If you do the form control it automatically gives you an option to add the macro button.', 'start': 15089.677, 'duration': 4.141}, {'end': 15097.259, 'text': 'So for example if I go to insert I click on form button.', 'start': 15093.878, 'duration': 3.381}, {'end': 15099.84, 'text': 'I select where I need that button.', 'start': 15097.819, 'duration': 2.021}, {'end': 15103.738, 'text': 'It will automatically ask you whether you want to assign a macro.', 'start': 15100.376, 'duration': 3.362}, {'end': 15109.962, 'text': 'Under assign macro button window I will just select which macro I need to assign it to and then I click on OK.', 'start': 15103.858, 'duration': 6.104}], 'summary': 'Using form control allows adding macro button, simplifying macro assignment.', 'duration': 25.667, 'max_score': 15084.295, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ15084295.jpg'}, {'end': 15352.242, 'src': 'embed', 'start': 15328.656, 'weight': 0, 'content': [{'end': 15336.079, 'text': "Sometimes what happens is, what is first of all debugging that we need to know? Now we've already learned how the VBA functions work.", 'start': 15328.656, 'duration': 7.423}, {'end': 15337.68, 'text': 'How do you record a macro??', 'start': 15336.44, 'duration': 1.24}, {'end': 15340.46, 'text': 'How does the VBA look when you record a macro??', 'start': 15338, 'duration': 2.46}, {'end': 15347.861, 'text': 'However, as with the case with any code, even the macro code can have defects and macro may not run as you expected.', 'start': 15340.88, 'duration': 6.981}, {'end': 15352.242, 'text': 'This requires examination of the code to find the defects and correct them.', 'start': 15348.422, 'duration': 3.82}], 'summary': 'Debugging vba macro code is essential for identifying and correcting defects.', 'duration': 23.586, 'max_score': 15328.656, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ15328656.jpg'}], 'start': 14412.916, 'title': 'Excel vba and macro recording', 'summary': 'Covers the process of recording a macro in excel using vba, including the steps to add a developer tab, the types of macro recording, and demonstration of recording an absolute macro with specific keystrokes. it also details the process of adding a command button to run macros, assigning macros to buttons, and the process of debugging a macro in excel vba, with practical examples of debugging and macro recording.', 'chapters': [{'end': 14508.983, 'start': 14412.916, 'title': 'Regression analysis in excel', 'summary': 'Explains how to perform regression analysis in excel, including selecting output range, interpreting regression statistics, analysis of variance, and regression line calculation.', 'duration': 96.067, 'highlights': ['Excel provides basic regression statistics, including R square, standard error, and number of observations.', 'Analysis of variance (Innova) information includes degrees of freedom, sum of squares, mean square value, F value, and significance of F.', 'Regression tool supplies information about the regression line, including coefficient standard error, t stat, and probability value for both intercept and independent value.', 'Excel also generates a simple scatter chart to visualize the regression data.']}, {'end': 14980.477, 'start': 14509.223, 'title': 'Excel vba and macro recording', 'summary': 'Explains the process of recording a macro in excel using vba, including the steps to add a developer tab, the types of macro recording (absolute and relative referencing), and the demonstration of recording an absolute macro with specific keystrokes.', 'duration': 471.254, 'highlights': ['The chapter explains the process of recording a macro in Excel using VBA', 'The types of macro recording (absolute and relative referencing) are discussed', 'Demonstration of recording an absolute macro with specific keystrokes is provided']}, {'end': 15308.845, 'start': 14981.175, 'title': 'Adding and running macros with command buttons', 'summary': 'Details the process of adding a command button to run macros, assigning macros to buttons, and the difference between absolute and relative referencing in vba, with an emphasis on the ease of running macros using command buttons.', 'duration': 327.67, 'highlights': ['The process of adding a command button to run macros and assigning macros to buttons is explained.', 'The concept of absolute and relative referencing in VBA is detailed, focusing on the difference between the two referencing methods.', 'The ease of running macros using command buttons is emphasized, highlighting the convenience of running macros without repeatedly going back to the sheet.']}, {'end': 15845.315, 'start': 15309.229, 'title': 'Excel vba: debugging and macros', 'summary': 'Covers the process of debugging a macro in excel vba, including stepping through the code, creating a summary sheet, and using breakpoints for error identification and correction, providing practical examples of debugging and macro recording.', 'duration': 536.086, 'highlights': ['The process of debugging a macro in Excel VBA, including stepping through the code, creating a summary sheet, and using breakpoints for error identification and correction, providing practical examples of debugging and macro recording.', 'Practical demonstration of stepping through the code in Excel VBA, utilizing tools such as F8 to execute each line of the macro and detect errors, with detailed examples of code execution and error identification.', 'Explanation of creating a summary sheet and entering data for Excel tests, with a demonstration of running a macro to automate the process and potential error scenarios, providing a practical understanding of macro creation and debugging.', 'Illustration of using breakpoints in Excel VBA to identify and rectify errors in the code execution process, enabling a systematic approach to error identification and correction in macro development.']}], 'duration': 1432.399, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/RdTozKPY_OQ/pics/RdTozKPY_OQ14412916.jpg', 'highlights': ['The process of debugging a macro in Excel VBA, including stepping through the code, creating a summary sheet, and using breakpoints for error identification and correction, providing practical examples of debugging and macro recording.', 'The process of adding a command button to run macros and assigning macros to buttons is explained.', 'The chapter explains the process of recording a macro in Excel using VBA', 'Excel provides basic regression statistics, including R square, standard error, and number of observations.']}], 'highlights': ['Demonstration of using the SUMIF function with three criteria to find total salaries of employees in the sales department in the South region earning more than 10,000 rupees.', 'The largest salary changed from 49,000 to 60,000 after a salary adjustment, demonstrating the impact of changes on identifying the largest salary.', 'The chapter covers custom and conditional formatting, advanced functions, data tools, and macros.', 'Explains the process of removing conditional formatting and applying a new rule to color cells between 10,000 and 20,000 in yellow', 'The chapter covers Excel data validation techniques with input and error messages.', 'The process of creating a recommended column chart in Excel is explained, where the software recommends the types of chart that will properly represent the selected data.', "The process of adding and removing data labels in the created chart is demonstrated, providing options to add data labels for individual bars or using the 'plus' sign to add data labels automatically for both bars.", 'The chapter explains the process of recording a macro in Excel using VBA', 'The chapter covers inserting and managing text boxes in Excel, which can be used to display data without gridlines', 'The chapter explains sorting data by region and filtering salary ranges as key features of data organization and manipulation in Excel']}