title
Excel Tutorial | Microsoft Excel Tutorial | Excel Training | Intellipaat

description
🔥Intellipaat Microsoft excel training: https://intellipaat.com/excel-training/ In this microsoft excel tutorial you will master how to use excel, complete ms excel formulas, excel tips and tricks, excel accounting, how to use excel vlookup, pivot table in excel, excel formulas and functions in detail. #ExcelTutorial #MicrosoftExcelTutorial #Excel 👉Following topics are covered in this video: 0:00 - Excel Tutorial 1:08 - Microsoft Excel Overview 4:52- Getting started with Microsoft Excel 5:33- Hands on with Microsoft excel 8:02 - Saving an Excel file 10:31- Excel Customization 13:21- Important areas of working 15:51- What is formula bar 19:18- ribbons 24:06- Formatting excel and shortcuts 33:04- Basic Formulas 50:00- Find and Replace 1:05:49- Text Functions-Formatting Data 1:39:25- count functions 1:40:45- Relative Reference 1:57:17- Absolute Reference 2:14:12- Mixed Reference 2:32:52- Data Validation 2:42:00- Different types of data validation 3:03:02- Conditional formatting 3:43:56- sorting data 4:06:15- filtering data 4:34:11- column chart 5:09:54- line chart 5:23:01- pie chart 5:36:51- protection 6:14:29- pivot tables 6:59:44- Vlookup 7:50:55- index function 7:51:51- Match function 8:30:39- lookup function 9:11:55- what is VBA 9:13:23- what is macro 9:14:37- Exploring Visual Basic Editor (VBE) 9:17:14- Using Macro Recorder 9:39:12- Recap 9:51:18- Variable, data types and constants 10:04:14- scope of a variable 10:15:09- Data Types 10:21:11- Constants 📌 Do subscribe to Intellipaat channel & get regular updates on videos: http://bit.ly/Intellipaat 🔗 Watch top technologies video tutorials here: https://bit.ly/2IAtGl2 📕 Read technologies tutorial here: https://bit.ly/1T3TUeK 📰Interested to read about technological blogs? Please read here: https://bit.ly/31OX4vi Are you looking for something more? Enroll in our microsoft excel training & certification course and become a certified professional (https://intellipaat.com/excel-training/). It is a 24 hrs instructor led training provided by Intellipaat which is completely aligned with industry standards and certification bodies. If you’ve enjoyed this microsoft excel training video, Like us and Subscribe to our channel for more similar informative videos. Got any questions about ms excel tutorial? Ask us in the comment section below. ---------------------------- Intellipaat Edge 1. 24*7 Life time Access & Support 2. Flexible Class Schedule 3. Job Assistance 4. Mentors with +14 yrs 5. Industry Oriented Course ware 6. Life time free Course Upgrade ------------------------------ Why microsoft excel is important? Microsoft Excel is the most-used spreadsheet program in the world. Excel can perform formula-based calculations and many other mathematical functions. The program also serves as a programming platform for Visual Basic for Applications. Because of its utility, Excel has become a staple in many enterprises. Why should you opt for a microsoft excel career? Microsoft Excel has proven to be advantageous for day to day business activities. The knowledge of Microsoft Excel has become essential for small business establishments to carry out their day to day business and reap the best results from their business. There is a huge demand for ms excel certified professional. The salaries for ms excel professional are also very good. ------------------------------ For more information: Please write us to sales@intellipaat.com or call us at: +91-7847955955 Website: https://intellipaat.com/excel-training/ Facebook: https://www.facebook.com/intellipaatonline/ LinkedIn: https://www.linkedin.com/in/intellipaat/ Twitter: https://twitter.com/Intellipaat

detail
{'title': 'Excel Tutorial | Microsoft Excel Tutorial | Excel Training | Intellipaat', 'heatmap': [{'end': 1910.482, 'start': 1523.105, 'weight': 0.903}, {'end': 4972.762, 'start': 4580.676, 'weight': 0.741}, {'end': 5740.567, 'start': 5344.043, 'weight': 0.75}, {'end': 34387.468, 'start': 33998.727, 'weight': 1}], 'summary': 'This microsoft excel tutorial covers major concepts including basic concepts, important formulas, data sorting and filtering techniques, pivot tables, vba macros, charting principles, creating dashboards, absolute reference, excel referencing, data validation, conditional formatting, advanced sorting, data filtering, chart creation, data security, pivot tables, vlookup function, using vlookup and index functions, index and match for data lookup, excel functions and troubleshooting, logical operations, vba for microsoft applications, excel macros, vba variables, and declarations.', 'chapters': [{'end': 98.022, 'segs': [{'end': 71.697, 'src': 'embed', 'start': 3.186, 'weight': 0, 'content': [{'end': 6.028, 'text': 'Hey guys, welcome to this session on Microsoft Excel.', 'start': 3.186, 'duration': 2.842}, {'end': 10.152, 'text': "In this video, we'll be learning all the major concepts in Microsoft Excel.", 'start': 6.749, 'duration': 3.403}, {'end': 14.395, 'text': 'Before moving on with the session, let us look at what we are going to learn.', 'start': 11.313, 'duration': 3.082}, {'end': 20.901, 'text': "At first, we'll be learning the basic concepts of Microsoft Excel like entering data and referencing in formulas.", 'start': 15.216, 'duration': 5.685}, {'end': 24.204, 'text': "And then we'll be learning the concept of conditional formatting.", 'start': 21.721, 'duration': 2.483}, {'end': 27.746, 'text': "After that, we'll learn more important formulas in Excel.", 'start': 25.284, 'duration': 2.462}, {'end': 33.38, 'text': 'After learning those formulas, now we will be learning the data sorting and data filtering techniques.', 'start': 28.917, 'duration': 4.463}, {'end': 38.023, 'text': 'After that, we will be learning data and file security.', 'start': 34.641, 'duration': 3.382}, {'end': 42.545, 'text': 'After that, we will be learning pivot tables in excel with examples.', 'start': 39.003, 'duration': 3.542}, {'end': 47.328, 'text': 'After learning pivot tables, now we are going to move on with VBA macros.', 'start': 43.526, 'duration': 3.802}, {'end': 53.012, 'text': 'Now, we will be learning about principles of charting and different techniques in charting.', 'start': 48.809, 'duration': 4.203}, {'end': 60.249, 'text': "And finally, we'll be creating dashboards and interactive components for the purpose of data analysis using Microsoft Excel.", 'start': 53.945, 'duration': 6.304}, {'end': 62.871, 'text': "Okay, now let's move on with the session.", 'start': 61.35, 'duration': 1.521}, {'end': 64.772, 'text': "Let's get started.", 'start': 63.912, 'duration': 0.86}, {'end': 71.697, 'text': 'To start off with, what is Microsoft Excel all about? To explain this, let me just take an example.', 'start': 65.533, 'duration': 6.164}], 'summary': 'Learn major concepts in microsoft excel including data entry, formulas, conditional formatting, sorting, security, pivot tables, vba macros, charting, and dashboards.', 'duration': 68.511, 'max_score': 3.186, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3186.jpg'}], 'start': 3.186, 'title': 'Microsoft excel learning session', 'summary': 'Covers major concepts in microsoft excel, including basic concepts, important formulas, data sorting and filtering techniques, data and file security, pivot tables, vba macros, principles of charting, and creating dashboards for data analysis using microsoft excel.', 'chapters': [{'end': 98.022, 'start': 3.186, 'title': 'Microsoft excel learning session', 'summary': 'Covers major concepts in microsoft excel, including basic concepts, important formulas, data sorting and filtering techniques, data and file security, pivot tables, vba macros, principles of charting, and creating dashboards for data analysis using microsoft excel.', 'duration': 94.836, 'highlights': ['The chapter includes learning the data sorting and data filtering techniques with Microsoft Excel.', 'The chapter covers pivot tables in excel with examples.', 'The chapter involves learning about principles of charting and different techniques in charting.', 'The chapter includes learning the concept of conditional formatting in Microsoft Excel.', 'The chapter covers learning more important formulas in Excel.']}], 'duration': 94.836, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3186.jpg', 'highlights': ['Covers major concepts in Microsoft Excel, including basic concepts, important formulas, data sorting and filtering techniques, data and file security, pivot tables, VBA macros, principles of charting, and creating dashboards for data analysis using Microsoft Excel.', 'The chapter covers pivot tables in Excel with examples.', 'The chapter includes learning the data sorting and data filtering techniques with Microsoft Excel.', 'The chapter involves learning about principles of charting and different techniques in charting.', 'The chapter includes learning the concept of conditional formatting in Microsoft Excel.', 'The chapter covers learning more important formulas in Excel.']}, {'end': 1330.69, 'segs': [{'end': 150.651, 'src': 'embed', 'start': 98.022, 'weight': 0, 'content': [{'end': 104.587, 'text': 'which team, what shift they are doing or any other information which could be helpful.', 'start': 98.022, 'duration': 6.565}, {'end': 112.144, 'text': 'so saying your boss or your manager comes a day and asks you to give an employee detail of a particular department.', 'start': 104.587, 'duration': 7.557}, {'end': 119.29, 'text': 'If you have it in a particular notepad, you probably have to search it between many,', 'start': 113.125, 'duration': 6.165}, {'end': 124.235, 'text': 'many sheets to go to that particular department and get the employee details.', 'start': 119.29, 'duration': 4.945}, {'end': 134.123, 'text': 'Further to that, if he asks you for multiple details and not just for an employee, but for many employees, imagine how difficult it can get.', 'start': 125.355, 'duration': 8.768}, {'end': 143.611, 'text': 'So the simple concept is you use something like a row to column or in lines, you mark it 1,, 2, 3,', 'start': 135.382, 'duration': 8.229}, {'end': 150.651, 'text': '4 and then you put all the employee details and the departments and certain other information.', 'start': 143.611, 'duration': 7.04}], 'summary': 'Manually searching for employee details in numerous sheets can be time-consuming and inefficient, but organizing information in a structured format can simplify and streamline the process.', 'duration': 52.629, 'max_score': 98.022, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc98022.jpg'}, {'end': 199.393, 'src': 'embed', 'start': 172.278, 'weight': 1, 'content': [{'end': 180.781, 'text': 'So one of the most important feature of Excel is for analysis to utilize mathematical operations, inbuilt functionalities,', 'start': 172.278, 'duration': 8.503}, {'end': 182.081, 'text': 'to get you the desired result.', 'start': 180.781, 'duration': 1.3}, {'end': 189.544, 'text': "So what is Microsoft all about Microsoft Excel all about? Well, in simple terms, it's a software applications.", 'start': 182.661, 'duration': 6.883}, {'end': 196.632, 'text': 'You can use it to perform a lot of logical mathematical statistic calculation on data.', 'start': 190.39, 'duration': 6.242}, {'end': 199.393, 'text': 'It has a basic feature of spreadsheet.', 'start': 197.632, 'duration': 1.761}], 'summary': 'Excel is a software for analysis and calculations, offering inbuilt functionalities for statistical and logical operations.', 'duration': 27.115, 'max_score': 172.278, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc172278.jpg'}, {'end': 385.987, 'src': 'embed', 'start': 353.461, 'weight': 2, 'content': [{'end': 356.243, 'text': 'You can see tabs, home, inset, page layout and so on.', 'start': 353.461, 'duration': 2.782}, {'end': 359.705, 'text': 'But then the first thing which you might have noticed is the sheet.', 'start': 356.623, 'duration': 3.082}, {'end': 362.847, 'text': 'So to explain this let me just take a notebook.', 'start': 360.325, 'duration': 2.522}, {'end': 372.521, 'text': 'So imagine a notebook, you have multiple sheets, right? Where you can write different, different topics, different subjects, different information.', 'start': 363.496, 'duration': 9.025}, {'end': 376.382, 'text': 'Similarly in Excel, you have multiple sheets for your usages.', 'start': 373.001, 'duration': 3.381}, {'end': 380.645, 'text': "Now it's not just that, it's just not limited to three sheets.", 'start': 377.023, 'duration': 3.622}, {'end': 385.987, 'text': 'We can add multiple sheets and how to do that, we will see in the later chapters.', 'start': 380.665, 'duration': 5.322}], 'summary': 'Excel has multiple sheets for organizing different information, not limited to three sheets.', 'duration': 32.526, 'max_score': 353.461, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc353461.jpg'}, {'end': 615.526, 'src': 'embed', 'start': 589.908, 'weight': 5, 'content': [{'end': 595.686, 'text': 'Now if I close this, and I do not save it, this particular data will not be there.', 'start': 589.908, 'duration': 5.778}, {'end': 601.292, 'text': 'So please remember, always when you do some changes in your Excel, you need to save it.', 'start': 596.147, 'duration': 5.145}, {'end': 611.763, 'text': 'Again, how do I do it? Simple, either go to Windows, click on Save, or you noticed there was a shortcut which showed you.', 'start': 601.993, 'duration': 9.77}, {'end': 615.526, 'text': 'If I just hover over it, you could see that there was a Ctrl S.', 'start': 611.783, 'duration': 3.743}], 'summary': 'Always remember to save changes in excel: go to windows and click save, or use the shortcut ctrl s.', 'duration': 25.618, 'max_score': 589.908, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc589908.jpg'}, {'end': 753.563, 'src': 'embed', 'start': 719.947, 'weight': 6, 'content': [{'end': 728.287, 'text': 'so I go again to Windows, I go to Excel options And I go to customization or customize.', 'start': 719.947, 'duration': 8.34}, {'end': 731.109, 'text': 'I have my show quick access toolbar.', 'start': 729.027, 'duration': 2.082}, {'end': 736.212, 'text': 'And what are the tabs I want to show? So here it is showing customize your quick access toolbar.', 'start': 731.949, 'duration': 4.263}, {'end': 742.136, 'text': "For now, I'm going to go and use the save button and move it towards my right.", 'start': 736.772, 'duration': 5.364}, {'end': 743.637, 'text': 'So I have a save button.', 'start': 742.656, 'duration': 0.981}, {'end': 753.563, 'text': "I also probably want to use, let's take a redo and add that.", 'start': 744.177, 'duration': 9.386}], 'summary': 'Customizing quick access toolbar in excel to add save and redo buttons.', 'duration': 33.616, 'max_score': 719.947, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc719947.jpg'}, {'end': 838.044, 'src': 'embed', 'start': 808.805, 'weight': 3, 'content': [{'end': 814.368, 'text': "It's spreadsheet, grid, ribbon, formula bar, status bar, which you will see.", 'start': 808.805, 'duration': 5.563}, {'end': 818.048, 'text': 'now going back to my spreadsheet.', 'start': 815.666, 'duration': 2.382}, {'end': 823.032, 'text': 'we spoke about sheets right sheet one, sheet two, sheet three.', 'start': 818.048, 'duration': 4.984}, {'end': 826.435, 'text': 'so what are the five important areas?', 'start': 823.032, 'duration': 3.403}, {'end': 834.001, 'text': 'let me show this again here you have your formula bar.', 'start': 826.435, 'duration': 7.566}, {'end': 838.044, 'text': 'here you have your state as well.', 'start': 834.001, 'duration': 4.043}], 'summary': 'The transcript discusses spreadsheet features including grid, ribbon, formula bar, and status bar.', 'duration': 29.239, 'max_score': 808.805, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc808805.jpg'}, {'end': 937.522, 'src': 'embed', 'start': 899.827, 'weight': 7, 'content': [{'end': 906.322, 'text': 'Now, important thing over here is, A cell is nothing but a reference or an interface of column and a row.', 'start': 899.827, 'duration': 6.495}, {'end': 912.165, 'text': "So simply put, this is nothing but it's in D column and six row.", 'start': 907.462, 'duration': 4.703}, {'end': 916.688, 'text': 'The same thing can be seen in the name box.', 'start': 912.786, 'duration': 3.902}, {'end': 921.632, 'text': "So we're talking about five things, right? So this is also the fifth thing, which is very important, a name box.", 'start': 916.869, 'duration': 4.763}, {'end': 937.522, 'text': "So once again, if I move here, can you tell me what is the reference of this particular cell? Well, if you haven't, Notice it is H7, H7, H7.", 'start': 922.512, 'duration': 15.01}], 'summary': 'A cell is in d column and six row, referred to as h7.', 'duration': 37.695, 'max_score': 899.827, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc899827.jpg'}, {'end': 1012.199, 'src': 'embed', 'start': 982.26, 'weight': 9, 'content': [{'end': 983.341, 'text': 'So we saw name box.', 'start': 982.26, 'duration': 1.081}, {'end': 984.662, 'text': 'We saw formula bar.', 'start': 983.661, 'duration': 1.001}, {'end': 989.166, 'text': 'Now what is the status? Here we can see there is a status called ready.', 'start': 985.122, 'duration': 4.044}, {'end': 993.887, 'text': 'So basically, this status bar shows the status of the spreadsheet.', 'start': 989.864, 'duration': 4.023}, {'end': 996.749, 'text': 'It tells us what is actually happening in the spreadsheet.', 'start': 994.087, 'duration': 2.662}, {'end': 999.15, 'text': "So right now it's telling I'm ready.", 'start': 997.209, 'duration': 1.941}, {'end': 1004.674, 'text': "What do you want me to do? So for now, I'll just go to the data and see if it changes anything.", 'start': 999.55, 'duration': 5.124}, {'end': 1009.477, 'text': 'We created the employee details, right? So let me just click on the employee details.', 'start': 1005.094, 'duration': 4.383}, {'end': 1012.199, 'text': 'Okay, it shows ready.', 'start': 1010.478, 'duration': 1.721}], 'summary': "Introduction to spreadsheet interface and status bar, indicating 'ready' status.", 'duration': 29.939, 'max_score': 982.26, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc982260.jpg'}, {'end': 1197.988, 'src': 'embed', 'start': 1165.321, 'weight': 10, 'content': [{'end': 1176.445, 'text': 'then we have insert page layout, formulas, data review, view and then some other additional tabs which I have added.', 'start': 1165.321, 'duration': 11.124}, {'end': 1181.267, 'text': 'So what are these? Now, these are nothing but these are called ribbons.', 'start': 1177.165, 'duration': 4.102}, {'end': 1183.728, 'text': 'Now, what do these ribbons do?', 'start': 1182.147, 'duration': 1.581}, {'end': 1191.166, 'text': 'These ribbons just have all your common tabs, usage tabs, together.', 'start': 1184.688, 'duration': 6.478}, {'end': 1193.527, 'text': 'let me explain that with an example.', 'start': 1191.166, 'duration': 2.361}, {'end': 1196.567, 'text': 'now, under your home tab, you have font.', 'start': 1193.527, 'duration': 3.04}, {'end': 1197.988, 'text': 'so what is basically font?', 'start': 1196.567, 'duration': 1.421}], 'summary': 'Transcript explains ribbons as common tabs, e.g. font under home tab.', 'duration': 32.667, 'max_score': 1165.321, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc1165321.jpg'}], 'start': 98.022, 'title': 'Efficient employee data management', 'summary': 'Discusses the inefficiency of manual employee data management, emphasizing the need for a streamlined system. it also introduces excel basics, customization, important areas, and functions.', 'chapters': [{'end': 150.651, 'start': 98.022, 'title': 'Efficient employee data management', 'summary': 'Discusses the inefficiency of manual employee data management, highlighting the challenges of retrieving specific employee details from multiple sheets and emphasizes the need for a streamlined system to organize and access this information.', 'duration': 52.629, 'highlights': ['Using a manual system to retrieve specific employee details from multiple sheets can be time-consuming and inefficient.', 'Organizing employee details using a row to column or in lines system can streamline the process and improve accessibility.']}, {'end': 403.393, 'start': 151.491, 'title': 'Introduction to excel basics', 'summary': 'Introduces microsoft excel as a software application for logical, mathematical, and statistical data analysis, with features including utilizing mathematical operations, creating charts, and storing data in a grid of cells arranged in rows and columns, emphasizing its usage to analyze and store data, create graphs, and charts.', 'duration': 251.902, 'highlights': ['Microsoft Excel is a software application for logical, mathematical, and statistical data analysis, emphasizing its usage to analyze and store data, create graphs, and charts.', 'Utilizing mathematical operations and inbuilt functionalities to get desired results is one of the most important features of Excel.', 'Excel allows creating multiple sheets, which are collectively referred to as a workbook, for organizing different information, similar to using multiple sheets in a notebook.']}, {'end': 780.966, 'start': 403.613, 'title': 'Excel basics and customization', 'summary': 'Discusses the basics of entering and saving data in excel, including naming files, using save as and save functions, and customizing the quick access toolbar for easier navigation and usage of excel.', 'duration': 377.353, 'highlights': ['The chapter discusses saving data in Excel, including using Save As for the first time and Save for subsequent modifications, with a shortcut of Ctrl S for saving.', 'The chapter explains the process of customizing the Quick Access Toolbar in Excel, allowing users to add, remove, and rearrange buttons for easier navigation and usage.', 'The chapter demonstrates the process of naming and saving an Excel file, highlighting the change in file name and extension after saving.']}, {'end': 1330.69, 'start': 780.966, 'title': 'Excel: important areas and functions', 'summary': 'Covers the five important areas of working in excel, including spreadsheet, grid, ribbon, formula bar, and status bar, along with their functionalities, such as referencing cells, using the formula bar, and understanding the status bar, as well as the significance of ribbons in grouping common tabs and functions.', 'duration': 549.724, 'highlights': ['Understanding the five important areas of working in Excel: spreadsheet, grid, ribbon, formula bar, and status bar.', 'Explaining the referencing of cells and the significance of the name box in Excel.', 'Detailing the functionalities of the formula bar, including showcasing cell content and inserting formulas.', 'Understanding the significance of the status bar in displaying the status of the spreadsheet and its activities.', 'Significance of ribbons in grouping common tabs and functions for easy access in Excel.']}], 'duration': 1232.668, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc98022.jpg', 'highlights': ['Organizing employee details using a row to column or in lines system can streamline the process and improve accessibility.', 'Microsoft Excel is a software application for logical, mathematical, and statistical data analysis, emphasizing its usage to analyze and store data, create graphs, and charts.', 'Excel allows creating multiple sheets, which are collectively referred to as a workbook, for organizing different information, similar to using multiple sheets in a notebook.', 'Understanding the five important areas of working in Excel: spreadsheet, grid, ribbon, formula bar, and status bar.', 'Utilizing mathematical operations and inbuilt functionalities to get desired results is one of the most important features of Excel.', 'The chapter discusses saving data in Excel, including using Save As for the first time and Save for subsequent modifications, with a shortcut of Ctrl S for saving.', 'The chapter explains the process of customizing the Quick Access Toolbar in Excel, allowing users to add, remove, and rearrange buttons for easier navigation and usage.', 'Understanding the referencing of cells and the significance of the name box in Excel.', 'Detailing the functionalities of the formula bar, including showcasing cell content and inserting formulas.', 'Understanding the significance of the status bar in displaying the status of the spreadsheet and its activities.', 'Significance of ribbons in grouping common tabs and functions for easy access in Excel.', 'Using a manual system to retrieve specific employee details from multiple sheets can be time-consuming and inefficient.']}, {'end': 3076.868, 'segs': [{'end': 1377.483, 'src': 'embed', 'start': 1330.69, 'weight': 2, 'content': [{'end': 1333.192, 'text': "that's when you come to this particular tab.", 'start': 1330.69, 'duration': 2.502}, {'end': 1338.576, 'text': 'you have sorting largest to smallest or smallest to largest.', 'start': 1333.192, 'duration': 5.384}, {'end': 1340.737, 'text': 'you want to filter data.', 'start': 1338.576, 'duration': 2.161}, {'end': 1342.279, 'text': 'you want to remove, duplicate.', 'start': 1340.737, 'duration': 1.542}, {'end': 1344.94, 'text': 'you want to consolidate, you want to validate.', 'start': 1342.279, 'duration': 2.661}, {'end': 1347.742, 'text': "that's when you come to data tab.", 'start': 1344.94, 'duration': 2.802}, {'end': 1352.206, 'text': "review is basically it's about protecting the workbook.", 'start': 1347.742, 'duration': 4.464}, {'end': 1354.427, 'text': 'you want to review certain content.', 'start': 1352.206, 'duration': 2.221}, {'end': 1356.449, 'text': 'you want to check for some spelling.', 'start': 1354.427, 'duration': 2.022}, {'end': 1358.37, 'text': 'you want to search in the dictionary.', 'start': 1356.449, 'duration': 1.921}, {'end': 1359.73, 'text': "That's when you can.", 'start': 1358.749, 'duration': 0.981}, {'end': 1364.253, 'text': 'And the last one, the view, is basically similar to what you observe over here.', 'start': 1360.37, 'duration': 3.883}, {'end': 1369.137, 'text': 'Zoom. or you want to freeze a particular row and move the rest,', 'start': 1365.114, 'duration': 4.023}, {'end': 1375.821, 'text': 'or freeze a particular column and move the rest and you want to put a page break or change the layout.', 'start': 1369.137, 'duration': 6.684}, {'end': 1377.483, 'text': "That's when you come to view.", 'start': 1376.262, 'duration': 1.221}], 'summary': 'Excel tabs: data tab for sorting, filtering, and validation; review for protecting workbook and checking content; view for zooming and layout changes.', 'duration': 46.793, 'max_score': 1330.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc1330690.jpg'}, {'end': 1910.482, 'src': 'heatmap', 'start': 1523.105, 'weight': 0.903, 'content': [{'end': 1533.27, 'text': 'Great So what if there is a lot of data and I really have to go to say a particular column which is like DO.', 'start': 1523.105, 'duration': 10.165}, {'end': 1542.907, 'text': 'Should I keep clicking on the arrow key to navigate there or is there a shortcut available? Yes, of course.', 'start': 1534.884, 'duration': 8.023}, {'end': 1547.289, 'text': "That's where the tricks are available and which we can use.", 'start': 1543.087, 'duration': 4.202}, {'end': 1552.931, 'text': 'To showcase that, let me just come up with a particular table with some data.', 'start': 1548.369, 'duration': 4.562}, {'end': 1556.252, 'text': "I'm going to randomly add some numbers out here.", 'start': 1553.831, 'duration': 2.421}, {'end': 1567.169, 'text': 'Let me just put the numbers and I will pull this autofill handler.', 'start': 1558.733, 'duration': 8.436}, {'end': 1569.47, 'text': "I'll talk about more of that shortly.", 'start': 1567.43, 'duration': 2.04}, {'end': 1577.151, 'text': 'And I will also pull the autofill handler towards my column so that the data is available.', 'start': 1570.43, 'duration': 6.721}, {'end': 1580.472, 'text': 'So here do not go by what the logic is.', 'start': 1577.631, 'duration': 2.841}, {'end': 1584.172, 'text': 'Just look at this particular table which has a lot of numbers.', 'start': 1581.052, 'duration': 3.12}, {'end': 1591.694, 'text': 'So imagine you have a database something similar to this and there is a lot of numbers available.', 'start': 1585.013, 'duration': 6.681}, {'end': 1593.054, 'text': 'How do you navigate?', 'start': 1592.274, 'duration': 0.78}, {'end': 1606.87, 'text': 'so, for example, if i have to really go to p3, which is this particular cell, should i from here keep holding my arrow key to navigate?', 'start': 1593.641, 'duration': 13.229}, {'end': 1611.433, 'text': 'or is there any shortcut available?', 'start': 1606.87, 'duration': 4.563}, {'end': 1614.716, 'text': "yes, there is, so i'm going back to my home key.", 'start': 1611.433, 'duration': 3.283}, {'end': 1618.699, 'text': "so that's where i came and now observe what happens.", 'start': 1614.716, 'duration': 3.983}, {'end': 1627.685, 'text': "i'm going to click on control and my arrow key, So I directly move to the table with the data is available.", 'start': 1618.699, 'duration': 8.986}, {'end': 1629.526, 'text': "OK, I'll do that again.", 'start': 1628.425, 'duration': 1.101}, {'end': 1633.348, 'text': 'Control arrow key.', 'start': 1631.787, 'duration': 1.561}, {'end': 1642.112, 'text': 'So now if I have to move to the last one, the one which I marked in the color yellow, I still click on control and the arrow key.', 'start': 1634.348, 'duration': 7.764}, {'end': 1643.653, 'text': 'There you go.', 'start': 1643.152, 'duration': 0.501}, {'end': 1652.497, 'text': "So Excel basically understands that I'm looking at the data and I want to go to the last cell where the data is available.", 'start': 1644.273, 'duration': 8.224}, {'end': 1659.997, 'text': "And when I use the control and the right arrow, it understands that, oh yeah, there's this data and let me move there.", 'start': 1653.072, 'duration': 6.925}, {'end': 1666.041, 'text': 'Similarly, if I click control again and the left arrow again,', 'start': 1660.857, 'duration': 5.184}, {'end': 1672.025, 'text': 'Excel understands that I have to go to the first cell where the data is available and actually moves out there.', 'start': 1666.041, 'duration': 5.984}, {'end': 1676.869, 'text': 'So your question would be what if I have to go to K3?', 'start': 1673.907, 'duration': 2.962}, {'end': 1684.672, 'text': 'Now, in this case, of course, Excel will not understand that it has to move here, and you have marked it in yellow.', 'start': 1678.789, 'duration': 5.883}, {'end': 1689.114, 'text': 'So you have to use your arrow keys to go to that particular cell.', 'start': 1685.192, 'duration': 3.922}, {'end': 1691.235, 'text': "Okay, that's great.", 'start': 1690.635, 'duration': 0.6}, {'end': 1694.857, 'text': 'So we understood control and usage of control with the arrow keys.', 'start': 1691.275, 'duration': 3.582}, {'end': 1703.802, 'text': 'Now what is shift all about? So let me just go to my first cell and click on control shift and arrow.', 'start': 1694.977, 'duration': 8.825}, {'end': 1707.864, 'text': 'Please note that I have to hold all the keys together.', 'start': 1704.342, 'duration': 3.522}, {'end': 1710.948, 'text': 'control shift and my down arrow key.', 'start': 1708.527, 'duration': 2.421}, {'end': 1721.333, 'text': 'So as soon as I do that you can see that Excel understands that the data has available in that particular column has to be highlighted,', 'start': 1711.828, 'duration': 9.505}, {'end': 1722.153, 'text': 'and it does so.', 'start': 1721.333, 'duration': 0.82}, {'end': 1732.398, 'text': 'If I use control shift again and my right arrow, it will subsequently highlight the complete data.', 'start': 1723.274, 'duration': 9.124}, {'end': 1735.639, 'text': 'Quite easy.', 'start': 1734.458, 'duration': 1.181}, {'end': 1738.758, 'text': "So quickly, I'll just show it once again.", 'start': 1736.437, 'duration': 2.321}, {'end': 1745.52, 'text': 'Control shift down arrow highlights the data completely in that particular column.', 'start': 1739.798, 'duration': 5.722}, {'end': 1747.621, 'text': "Again, I'm going back.", 'start': 1746.44, 'duration': 1.181}, {'end': 1755.904, 'text': 'Control shift right arrow, it highlights that particular data in the row.', 'start': 1748.381, 'duration': 7.523}, {'end': 1765.987, 'text': 'So if you use control shift and the arrow keys, they are good enough tips and tricks for you to navigate Excel accordingly.', 'start': 1756.864, 'duration': 9.123}, {'end': 1773.202, 'text': 'You need to practice this because the various combinations of control shift works differently.', 'start': 1766.84, 'duration': 6.362}, {'end': 1780.324, 'text': 'So the more you practice and understand the functionalities, the easier it will become for you to navigate Excel.', 'start': 1773.702, 'duration': 6.622}, {'end': 1784.806, 'text': 'Hope you learned something in terms of navigation.', 'start': 1781.385, 'duration': 3.421}, {'end': 1787.407, 'text': 'Alright, moving on.', 'start': 1786.206, 'duration': 1.201}, {'end': 1788.947, 'text': 'So we spoke about navigation.', 'start': 1787.587, 'duration': 1.36}, {'end': 1790.608, 'text': 'What are these shortcuts all about?', 'start': 1789.087, 'duration': 1.521}, {'end': 1792.648, 'text': 'Everyone likes a shortcut, right?', 'start': 1791.288, 'duration': 1.36}, {'end': 1799.675, 'text': 'We always find ways and means, do work in a shorter way or find out shortcuts.', 'start': 1792.908, 'duration': 6.767}, {'end': 1804.758, 'text': 'so, thankfully, excel, there are plenty of shortcuts.', 'start': 1799.675, 'duration': 5.083}, {'end': 1808.52, 'text': "i'll just show you the simplest shortcuts available.", 'start': 1804.758, 'duration': 3.762}, {'end': 1817.986, 'text': 'so if i click on alt, you could see that suddenly there is some highlighted letters in the spreadsheet.', 'start': 1808.52, 'duration': 9.466}, {'end': 1819.087, 'text': "so i'll do that again.", 'start': 1817.986, 'duration': 1.101}, {'end': 1824.55, 'text': "i'm just clicking on escape, now escape key, and now i clicked on alt key.", 'start': 1819.087, 'duration': 5.463}, {'end': 1829.716, 'text': 'As soon as I do that there is set of letters highlighted.', 'start': 1825.515, 'duration': 4.201}, {'end': 1838.479, 'text': "Now I could use the combination of letters and Alt key to get to where I want, which means let's take I want to go to Home tab.", 'start': 1830.356, 'duration': 8.123}, {'end': 1840.64, 'text': 'So it shows H.', 'start': 1838.919, 'duration': 1.721}, {'end': 1843.3, 'text': 'So if I click on H, just observe what happens.', 'start': 1840.64, 'duration': 2.66}, {'end': 1852.183, 'text': 'I have the Home tab and beautifully under the Home tab, all the respective shortcuts are highlighted.', 'start': 1844.961, 'duration': 7.222}, {'end': 1862.784, 'text': 'So you can see that I can use numbers, I could use letters and I could actually use the combination of these two to not even use mouse,', 'start': 1853.482, 'duration': 9.302}, {'end': 1864.105, 'text': 'but I could do my stuff.', 'start': 1862.784, 'duration': 1.321}, {'end': 1873.107, 'text': 'Okay, just for an example, I will go ahead and use H again to see what happens.', 'start': 1865.345, 'duration': 7.762}, {'end': 1875.007, 'text': 'So it goes to color.', 'start': 1874.027, 'duration': 0.98}, {'end': 1877.688, 'text': 'And then again, it shows the shortcuts.', 'start': 1875.247, 'duration': 2.441}, {'end': 1884.463, 'text': 'So if you keep using the shortcuts right, what happens is just like how we learned riding a bicycle.', 'start': 1878.028, 'duration': 6.435}, {'end': 1893.11, 'text': 'initially we had to remember so many things, but later it became automated, or just your mind gets used to it.', 'start': 1884.463, 'duration': 8.647}, {'end': 1902.656, 'text': 'So, similarly consciously, if you want to use shortcuts, if you keep using this alt and remember those letters which needs to be highlighted,', 'start': 1893.57, 'duration': 9.086}, {'end': 1907.9, 'text': "your brain memorizes it and then, there on, it's very easy for you to use shortcuts.", 'start': 1902.656, 'duration': 5.244}, {'end': 1910.482, 'text': "I'll just show you a small example of that.", 'start': 1908.761, 'duration': 1.721}], 'summary': 'Using control and shift with arrow keys to navigate and highlight data in excel, as well as utilizing alt key for shortcuts.', 'duration': 387.377, 'max_score': 1523.105, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc1523105.jpg'}, {'end': 2551.398, 'src': 'embed', 'start': 2516.073, 'weight': 0, 'content': [{'end': 2517.673, 'text': "I click on Clifford's weight.", 'start': 2516.073, 'duration': 1.6}, {'end': 2519.014, 'text': 'Come back.', 'start': 2518.374, 'duration': 0.64}, {'end': 2522.795, 'text': 'I click on my number 2.', 'start': 2520.234, 'duration': 2.561}, {'end': 2523.795, 'text': "And Gary's weight.", 'start': 2522.795, 'duration': 1}, {'end': 2527.255, 'text': 'And click OK.', 'start': 2524.995, 'duration': 2.26}, {'end': 2528.836, 'text': 'There I go.', 'start': 2528.316, 'duration': 0.52}, {'end': 2530.036, 'text': 'So now we learned.', 'start': 2529.036, 'duration': 1}, {'end': 2532.817, 'text': 'Addition subtractions.', 'start': 2531.036, 'duration': 1.781}, {'end': 2535.257, 'text': 'OK, some more things.', 'start': 2533.937, 'duration': 1.32}, {'end': 2539.158, 'text': 'Sum total of all the students weight.', 'start': 2536.037, 'duration': 3.121}, {'end': 2550.498, 'text': 'Simple, right? You need to keep doing equal to 57 plus 64 plus 83 plus 76 plus 69 plus 52 plus 74.', 'start': 2541.215, 'duration': 9.283}, {'end': 2551.398, 'text': 'There I go, 475.', 'start': 2550.498, 'duration': 0.9}], 'summary': "Learning addition and subtraction, the sum total of all students' weight is 475.", 'duration': 35.325, 'max_score': 2516.073, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc2516073.jpg'}, {'end': 2725.176, 'src': 'embed', 'start': 2687.605, 'weight': 1, 'content': [{'end': 2692.989, 'text': "I want to take average of three weights, Greg's, Richard and Denver.", 'start': 2687.605, 'duration': 5.384}, {'end': 2695.41, 'text': 'Greg, Richard and Denver.', 'start': 2693.829, 'duration': 1.581}, {'end': 2712.528, 'text': 'How do I do that? Again, simple, equal to, I would want to select Richard plus Greg plus Denver divided by 3.', 'start': 2695.491, 'duration': 17.037}, {'end': 2718.692, 'text': "That's what we learned in school, right? Add the weights, add the number of people, and divide it by the quantity.", 'start': 2712.528, 'duration': 6.164}, {'end': 2721.273, 'text': 'So, my average is 154.', 'start': 2719.352, 'duration': 1.921}, {'end': 2725.176, 'text': 'Let me just do this using an insert function.', 'start': 2721.273, 'duration': 3.903}], 'summary': 'Calculate average of 3 weights: greg, richard, and denver, resulting in an average of 154.', 'duration': 37.571, 'max_score': 2687.605, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc2687605.jpg'}], 'start': 1330.69, 'title': 'Excel functions and formulas', 'summary': 'Introduces microsoft excel tabs and functions, covering navigation and shortcuts, using formulas and functions, and basic formulas and functions, with emphasis on practical application and examples.', 'chapters': [{'end': 1377.483, 'start': 1330.69, 'title': 'Microsoft excel tabs and functions', 'summary': 'Introduces the different tabs in microsoft excel - data, review, and view, and their functions, including sorting, filtering, removing duplicates, consolidating, validating data, workbook protection, spell check, zooming, freezing rows or columns, and adjusting page layout.', 'duration': 46.793, 'highlights': ["The 'Data' tab offers functions such as sorting, filtering, removing duplicates, consolidating, and validating data.", "The 'Review' tab provides options for protecting the workbook, checking spelling, and searching in the dictionary.", "The 'View' tab includes functions for zooming, freezing rows or columns, and adjusting the page layout."]}, {'end': 2079.196, 'start': 1378.583, 'title': 'Excel navigation and shortcuts', 'summary': 'Covers an introduction to excel, including navigation using arrow keys and shortcuts such as control and arrow keys, control shift and arrow keys, and alt key for accessing shortcuts, with emphasis on the importance of practice and memorization. it also includes an exercise on basic formulas for addition in excel.', 'duration': 700.613, 'highlights': ['The chapter emphasizes the importance of practicing and memorizing navigation shortcuts in Excel, including control and arrow keys, control shift and arrow keys, and alt key for accessing shortcuts.', 'It demonstrates the usage of control and arrow keys for efficient navigation in Excel, showcasing how to directly move to specific cells or the last cell where data is available using the control and arrow keys.', 'The chapter explains the usage of control shift and arrow keys to highlight complete data in a particular column or row, emphasizing the importance of practicing to understand the functionalities and ease of navigation in Excel.', 'It showcases the usage of alt key for accessing shortcuts in Excel, enabling users to navigate and execute commands efficiently without using the mouse.', 'The chapter includes an exercise on basic formulas in Excel, specifically focusing on the addition of weights for students, demonstrating practical application of basic formulas.']}, {'end': 2405.586, 'start': 2079.916, 'title': 'Using excel formulas and functions', 'summary': 'Explains the process of adding student weights in excel using formulas and functions, emphasizing the importance of providing instructions to excel and demonstrating the use of the equal sign, cell references, arithmetic operators, and insert function feature to achieve the desired results.', 'duration': 325.67, 'highlights': ['The process of adding student weights in Excel using formulas and functions.', 'Emphasizing the importance of providing instructions to Excel.', 'Demonstrating the use of the insert function feature.']}, {'end': 3076.868, 'start': 2405.966, 'title': 'Excel basic formulas & functions', 'summary': 'Covers basic excel formulas and functions, including addition, subtraction, average calculation, autofill, and find and replace, with examples and practical application, demonstrating usage and results.', 'duration': 670.902, 'highlights': ["The sum total of all the students' weight is 475, calculated by adding individual weights manually and using the autosum function.", 'The average weight of three students, Greg, Richard, and Denver, is 154, calculated using the formula (Richard + Greg + Denver) / 3.', "Excel's autofill feature recognizes sequential data and fills it up automatically, saving time and effort while entering data.", 'The chapter concludes with an introduction to find and replace functionality in Excel, allowing users to search for specific data and replace it with new information, with a mention of advanced find.']}], 'duration': 1746.178, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc1330690.jpg', 'highlights': ["The sum total of all the students' weight is 475, calculated by adding individual weights manually and using the autosum function.", 'The average weight of three students, Greg, Richard, and Denver, is 154, calculated using the formula (Richard + Greg + Denver) / 3.', "The 'Data' tab offers functions such as sorting, filtering, removing duplicates, consolidating, and validating data.", "The 'Review' tab provides options for protecting the workbook, checking spelling, and searching in the dictionary.", "The 'View' tab includes functions for zooming, freezing rows or columns, and adjusting the page layout."]}, {'end': 4326.002, 'segs': [{'end': 3261.724, 'src': 'embed', 'start': 3225.623, 'weight': 0, 'content': [{'end': 3229.766, 'text': 'simple right, and see how useful it is when you have a lot of data.', 'start': 3225.623, 'duration': 4.143}, {'end': 3234.304, 'text': 'So this is a simple find and replace.', 'start': 3230.543, 'duration': 3.761}, {'end': 3244.347, 'text': 'OK, so if this is simple find and replace, what is an advanced find and replace? Actually, that is also pretty simple.', 'start': 3235.965, 'duration': 8.382}, {'end': 3248.409, 'text': 'OK, let me explain that with an example.', 'start': 3246.008, 'duration': 2.401}, {'end': 3253.95, 'text': 'I will just color this data in green.', 'start': 3250.569, 'duration': 3.381}, {'end': 3256.811, 'text': 'Probably two of them.', 'start': 3255.391, 'duration': 1.42}, {'end': 3258.952, 'text': "And I'm going to use.", 'start': 3257.892, 'duration': 1.06}, {'end': 3261.724, 'text': 'find and replace.', 'start': 3260.143, 'duration': 1.581}], 'summary': 'Demonstrating the usefulness of find and replace with a lot of data in a simple and advanced way.', 'duration': 36.101, 'max_score': 3225.623, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3225623.jpg'}, {'end': 3507.358, 'src': 'embed', 'start': 3478.815, 'weight': 1, 'content': [{'end': 3484.817, 'text': 'So into find and replace, we learned a simple find and replace, which is a control F, just select it and do it.', 'start': 3478.815, 'duration': 6.002}, {'end': 3490.438, 'text': "Then I did not demo that, but then that's for you to work and ask me questions if it is there.", 'start': 3485.657, 'duration': 4.781}, {'end': 3500.471, 'text': 'In advance we saw how we could find and replace by match case matching entire cell content and also by format.', 'start': 3491.061, 'duration': 9.41}, {'end': 3507.358, 'text': 'OK Before we recollect the session I have some exercise for you.', 'start': 3501.812, 'duration': 5.546}], 'summary': 'Learned simple find and replace, match case, entire cell content, and format for advanced usage.', 'duration': 28.543, 'max_score': 3478.815, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3478815.jpg'}, {'end': 3585.816, 'src': 'embed', 'start': 3560.549, 'weight': 3, 'content': [{'end': 3565.892, 'text': 'Now how do you find the price? You basically add cost and shipping which is these two.', 'start': 3560.549, 'duration': 5.343}, {'end': 3572.635, 'text': 'Then I want you to find the total price which is nothing but price into the total quantity which you give the total price.', 'start': 3566.412, 'duration': 6.223}, {'end': 3575.612, 'text': 'Then I want you to find this subtotal.', 'start': 3573.411, 'duration': 2.201}, {'end': 3581.654, 'text': 'Okay, subtotal is nothing but add the total, the complete totals and you get the subtotal.', 'start': 3576.292, 'duration': 5.362}, {'end': 3585.816, 'text': "Of course, tax is everyone, right? You can't let go of tax.", 'start': 3582.434, 'duration': 3.382}], 'summary': 'Calculate total price, subtotal, and tax for a purchase.', 'duration': 25.267, 'max_score': 3560.549, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3560549.jpg'}, {'end': 3737.866, 'src': 'embed', 'start': 3705.586, 'weight': 5, 'content': [{'end': 3714.508, 'text': 'to select the cost, use the addition symbol and the shipping and hit enter.', 'start': 3705.586, 'duration': 8.922}, {'end': 3717.668, 'text': 'well, you recollect, we had done the autofill handler right.', 'start': 3714.508, 'duration': 3.16}, {'end': 3720.329, 'text': 'so now again there is a pattern out here.', 'start': 3717.668, 'duration': 2.661}, {'end': 3724.958, 'text': 'So I could just use the autofill handler, and Excel will do the needful for me.', 'start': 3720.976, 'duration': 3.982}, {'end': 3732.263, 'text': 'So if you notice over here, the formula, if I look at your formula bar, it shows C8 plus D8.', 'start': 3725.479, 'duration': 6.784}, {'end': 3737.866, 'text': 'Again, C9 plus D9 and C10 plus D10.', 'start': 3733.183, 'duration': 4.683}], 'summary': 'Using autofill handler in excel to calculate cost by adding shipping, resulting in c8+d8, c9+d9, and c10+d10.', 'duration': 32.28, 'max_score': 3705.586, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3705586.jpg'}, {'end': 3799.363, 'src': 'embed', 'start': 3770.952, 'weight': 4, 'content': [{'end': 3775.354, 'text': 'So, I will simply use the autofill handler to get my desired results.', 'start': 3770.952, 'duration': 4.402}, {'end': 3777.975, 'text': 'There you go.', 'start': 3777.535, 'duration': 0.44}, {'end': 3779.532, 'text': "So I've found the price.", 'start': 3778.311, 'duration': 1.221}, {'end': 3781.453, 'text': "I've also found the total.", 'start': 3780.012, 'duration': 1.441}, {'end': 3786.756, 'text': 'Now, how do I get the subtotal? Subtotal is nothing but just add all this.', 'start': 3781.893, 'duration': 4.863}, {'end': 3797.422, 'text': 'Either I could use equal to sum, open brackets, select all the numbers and close the brackets.', 'start': 3787.156, 'duration': 10.266}, {'end': 3799.363, 'text': 'So that is one way of looking at it.', 'start': 3797.742, 'duration': 1.621}], 'summary': 'Using autofill handler to find price, total, and calculate subtotal by adding all numbers.', 'duration': 28.411, 'max_score': 3770.952, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3770952.jpg'}, {'end': 4118.134, 'src': 'embed', 'start': 4088.666, 'weight': 6, 'content': [{'end': 4095.208, 'text': "Because if you're working with formulas and your data type or your format is as text, it might throw up an error.", 'start': 4088.666, 'duration': 6.542}, {'end': 4103.83, 'text': "So it's important that when you're working on formulas respective to what particular format you're working with as a text or number,", 'start': 4095.428, 'duration': 8.402}, {'end': 4106.81, 'text': 'you retain the cell format as is.', 'start': 4103.83, 'duration': 2.98}, {'end': 4110.912, 'text': 'A couple of more things on formatting.', 'start': 4109.051, 'duration': 1.861}, {'end': 4118.134, 'text': 'We saw when we right-clicked, there were multiple formats, right? So let us just review what those are.', 'start': 4112.532, 'duration': 5.602}], 'summary': 'Retain cell format when working with formulas to avoid errors.', 'duration': 29.468, 'max_score': 4088.666, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4088666.jpg'}, {'end': 4226.103, 'src': 'embed', 'start': 4191.395, 'weight': 7, 'content': [{'end': 4202.62, 'text': "if I won't change it to another format, I can just go to format custom cells and this time I'm going to go day mm yy, so it's going to first channel,", 'start': 4191.395, 'duration': 11.225}, {'end': 4206.202, 'text': '2015 or even better.', 'start': 4202.62, 'duration': 3.582}, {'end': 4215.517, 'text': 'again right click format cells and I can go select month, day, year, hour and minutes.', 'start': 4206.202, 'duration': 9.315}, {'end': 4220.34, 'text': 'Yeah So this is how formatting can be useful.', 'start': 4217.238, 'duration': 3.102}, {'end': 4226.103, 'text': "Now, most likely you will use this format when you're building a spreadsheet,", 'start': 4220.84, 'duration': 5.263}], 'summary': 'Demonstrating date formatting in spreadsheet cells for better data organization.', 'duration': 34.708, 'max_score': 4191.395, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4191395.jpg'}, {'end': 4279.415, 'src': 'embed', 'start': 4247.516, 'weight': 8, 'content': [{'end': 4251.618, 'text': "Let's move on to the most important topic for today, which is text functions.", 'start': 4247.516, 'duration': 4.102}, {'end': 4253.319, 'text': 'All right.', 'start': 4252.899, 'duration': 0.42}, {'end': 4258.383, 'text': 'So I have an exercise planned out for you, but let me not go to this exercise.', 'start': 4253.84, 'duration': 4.543}, {'end': 4263.026, 'text': 'Let me just pick up this data and tell you how generally data behaves.', 'start': 4258.463, 'duration': 4.563}, {'end': 4271.732, 'text': 'When you move data from different applications from Microsoft or Notepad or from PDF, you paste the data into Excel.', 'start': 4263.53, 'duration': 8.202}, {'end': 4279.415, 'text': "So I just have a copy of some information out here and I'm just going to open Notepad quickly and paste this.", 'start': 4272.373, 'duration': 7.042}], 'summary': 'Text functions and data behavior when pasted into excel from various applications.', 'duration': 31.899, 'max_score': 4247.516, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4247516.jpg'}], 'start': 3076.868, 'title': 'Excel features and functions', 'summary': 'Covers excel find and replace, basic formula exercises, data formatting, and text functions, emphasizing time-saving benefits, basic calculations, and formatting best practices.', 'chapters': [{'end': 3507.358, 'start': 3076.868, 'title': 'Excel find and replace', 'summary': 'Explains the process of using find and replace in excel, highlighting the time-saving benefits of the feature, and also covers advanced options such as matching case, entire cell content, and format, exemplifying the use of find and replace by format with color changes.', 'duration': 430.49, 'highlights': ["Excel's find and replace feature saves time by efficiently replacing multiple instances of a specific text or data, as demonstrated by replacing 'M&A' with 'math' in a dataset of 120 rows, emphasizing the impracticality of manual replacement for larger datasets of 15,000 rows.", "The explanation of Excel's find and replace function includes a demonstration of the 'find all' and 'find next' options, providing a practical example of locating and navigating through instances of the target text or data within the spreadsheet.", 'The chapter elucidates advanced find and replace options such as matching case, entire cell content, and format, with a focus on the practical application of find and replace by format, exemplified by changing the color of specific cells using the feature.']}, {'end': 3928.102, 'start': 3508.719, 'title': 'Basic formula exercise', 'summary': 'Explains a basic formula exercise involving cost, shipping, total price, subtotal, tax, grand total, and money left over, with a focus on using excel functions and autofill handler to calculate the values.', 'duration': 419.383, 'highlights': ['The chapter explains a basic formula exercise involving cost, shipping, total price, subtotal, tax, grand total, and money left over.', 'The focus is on using Excel functions and the autofill handler to calculate the values.', 'The formula for finding the price involves adding the cost and shipping.', 'The total is calculated by multiplying the price with the quantity, demonstrating the use of multiplication in determining total cost.', "The subtotal is obtained by summing up the total costs, with emphasis on using Excel's autosum feature for convenience."]}, {'end': 4326.002, 'start': 3928.102, 'title': 'Excel data formatting & text functions', 'summary': 'Covers the basics of formatting data in excel, including changing cell formats, custom formatting, and the importance of using the right format when working with formulas. it also introduces the topic of text functions and explains the common issues when pasting data from different applications into excel.', 'duration': 397.9, 'highlights': ['The chapter emphasizes the importance of using the right format for cells when working with formulas, as the wrong format can lead to errors.', 'It explains the process of custom formatting in Excel, showcasing how to change the display of dates using different formats, such as day and month or month, day, year, hour, and minutes.', 'The transcript introduces the topic of text functions and mentions the common issues that arise when pasting data from different applications into Excel, providing a practical example using data from Microsoft Outlook or social sites.']}], 'duration': 1249.134, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc3076868.jpg', 'highlights': ["Excel's find and replace feature saves time by efficiently replacing multiple instances of a specific text or data, as demonstrated by replacing 'M&A' with 'math' in a dataset of 120 rows, emphasizing the impracticality of manual replacement for larger datasets of 15,000 rows.", 'The chapter elucidates advanced find and replace options such as matching case, entire cell content, and format, with a focus on the practical application of find and replace by format, exemplified by changing the color of specific cells using the feature.', "The explanation of Excel's find and replace function includes a demonstration of the 'find all' and 'find next' options, providing a practical example of locating and navigating through instances of the target text or data within the spreadsheet.", 'The chapter explains a basic formula exercise involving cost, shipping, total price, subtotal, tax, grand total, and money left over.', "The subtotal is obtained by summing up the total costs, with emphasis on using Excel's autosum feature for convenience.", 'The focus is on using Excel functions and the autofill handler to calculate the values.', 'The chapter emphasizes the importance of using the right format for cells when working with formulas, as the wrong format can lead to errors.', 'It explains the process of custom formatting in Excel, showcasing how to change the display of dates using different formats, such as day and month or month, day, year, hour, and minutes.', 'The transcript introduces the topic of text functions and mentions the common issues that arise when pasting data from different applications into Excel, providing a practical example using data from Microsoft Outlook or social sites.', 'The formula for finding the price involves adding the cost and shipping.', 'The total is calculated by multiplying the price with the quantity, demonstrating the use of multiplication in determining total cost.']}, {'end': 6818.351, 'segs': [{'end': 4384.523, 'src': 'embed', 'start': 4356.688, 'weight': 0, 'content': [{'end': 4359.809, 'text': 'Okay, so I go to cell D1.', 'start': 4356.688, 'duration': 3.121}, {'end': 4362.411, 'text': 'Wow, it looks that the data is there.', 'start': 4360.269, 'duration': 2.142}, {'end': 4369.134, 'text': 'Okay, so does it look for other cells? But please be very mindful that this is deceiving.', 'start': 4363.291, 'duration': 5.843}, {'end': 4375.075, 'text': "That's where your Formula bar plays a very, very important role.", 'start': 4369.974, 'duration': 5.101}, {'end': 4377.137, 'text': 'It shows the content of the cell.', 'start': 4375.276, 'duration': 1.861}, {'end': 4382.461, 'text': 'Here it shows that it is actually on all different rows and cells, but it is actually not.', 'start': 4377.657, 'duration': 4.804}, {'end': 4384.523, 'text': 'Let me just move it to A1.', 'start': 4383.022, 'duration': 1.501}], 'summary': 'The formula bar is crucial for accurately displaying data in cells, despite initial appearances.', 'duration': 27.835, 'max_score': 4356.688, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4356688.jpg'}, {'end': 4435.658, 'src': 'embed', 'start': 4406.673, 'weight': 1, 'content': [{'end': 4414.036, 'text': 'Always review your formula bar to see whether the data is working, is available, and not available in particular sheet.', 'start': 4406.673, 'duration': 7.363}, {'end': 4422.439, 'text': 'Great. So now we saw that, but when I pasted the data from the notepad to here,', 'start': 4415.336, 'duration': 7.103}, {'end': 4429.157, 'text': 'the data is spread across in all this particular cell but is pretty huge and vast.', 'start': 4422.439, 'duration': 6.718}, {'end': 4429.717, 'text': 'Look at this.', 'start': 4429.237, 'duration': 0.48}, {'end': 4433.598, 'text': 'I have to keep navigating it across till the last.', 'start': 4430.237, 'duration': 3.361}, {'end': 4435.658, 'text': "So that's how the data is.", 'start': 4434.298, 'duration': 1.36}], 'summary': 'Review formula bar for data availability and navigation in spreadsheet.', 'duration': 28.985, 'max_score': 4406.673, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4406673.jpg'}, {'end': 4972.762, 'src': 'heatmap', 'start': 4580.676, 'weight': 0.741, 'content': [{'end': 4584.978, 'text': 'So what Excel is telling me is that I will delimit the data.', 'start': 4580.676, 'duration': 4.302}, {'end': 4587.519, 'text': 'You tell me how do you want me to delimit.', 'start': 4585.678, 'duration': 1.841}, {'end': 4591.401, 'text': "So it's giving me delimiters or options to delimit the data.", 'start': 4587.779, 'duration': 3.622}, {'end': 4595.763, 'text': 'So I have tab, semicolon, a comma, a space or other.', 'start': 4592.141, 'duration': 3.622}, {'end': 4602.579, 'text': 'So Remember what I was talking about Excel that you need to add or keep giving information to Excel,', 'start': 4596.423, 'duration': 6.156}, {'end': 4605.38, 'text': 'and it will take that information and do the needful.', 'start': 4602.579, 'duration': 2.801}, {'end': 4611.822, 'text': 'So in this case, I am going to tell that dealing with the data, not by using tab, but by using semicolon.', 'start': 4605.88, 'duration': 5.942}, {'end': 4621.645, 'text': 'And how did I agree on using semicolon? If you notice the data closely or you review it, you see there are common aspects.', 'start': 4612.282, 'duration': 9.363}, {'end': 4625.406, 'text': 'Either you have a space or you have a semicolon.', 'start': 4622.245, 'duration': 3.161}, {'end': 4629.2, 'text': 'So you could use a space for semicolon to delimit the data.', 'start': 4626.019, 'duration': 3.181}, {'end': 4638.282, 'text': 'In this case, since my end result is I want it as first name dot last name at redifname.com.', 'start': 4630.24, 'duration': 8.042}, {'end': 4642.023, 'text': 'So use semicolon delimit the data.', 'start': 4638.822, 'duration': 3.201}, {'end': 4644.043, 'text': "Let's see how I do that.", 'start': 4642.923, 'duration': 1.12}, {'end': 4648.204, 'text': 'So just select semicolon out here.', 'start': 4645.144, 'duration': 3.06}, {'end': 4651.065, 'text': 'And as soon as you do that, it gives you a data preview.', 'start': 4648.724, 'duration': 2.341}, {'end': 4661.113, 'text': "You can see that it's basically breaking the data into first name, last name and a column being placed in between those.", 'start': 4651.165, 'duration': 9.948}, {'end': 4668.56, 'text': "so if I click on next and I have some other data format which for now I'm not going to use,", 'start': 4661.113, 'duration': 7.447}, {'end': 4673.505, 'text': "and it's asking me destination is going to be in the subsequent columns, is that okay with you?", 'start': 4668.56, 'duration': 4.945}, {'end': 4676.668, 'text': "that's fine, and I click finish.", 'start': 4673.505, 'duration': 3.163}, {'end': 4678.029, 'text': 'Wow, so there we go.', 'start': 4676.668, 'duration': 1.361}, {'end': 4686.905, 'text': "it's basically broken the data in first name and last name and actually spread this across to various columns.", 'start': 4678.029, 'duration': 8.876}, {'end': 4694.786, 'text': 'Easy, right? Okay, so here we have our data broken down into multiple columns.', 'start': 4688.685, 'duration': 6.101}, {'end': 4700.888, 'text': "Even though the data is spread into multiple columns, it doesn't still help me, right?", 'start': 4695.947, 'duration': 4.941}, {'end': 4712.075, 'text': 'Because I still have to use this data And for me to read this, I have to move across to all these columns and then individually,', 'start': 4701.468, 'duration': 10.607}, {'end': 4717.117, 'text': 'probably type in out here and then put at the rate creditmail.com.', 'start': 4712.075, 'duration': 5.042}, {'end': 4723.22, 'text': 'Is there an option to make this a little easier for me? Yes, of course there is.', 'start': 4718.118, 'duration': 5.102}, {'end': 4730.824, 'text': 'So now I would like to use this data, not in a row, but in a column format.', 'start': 4723.721, 'duration': 7.103}, {'end': 4736.019, 'text': 'which makes it more easier for me to review and then do some modifications.', 'start': 4731.598, 'duration': 4.421}, {'end': 4739.88, 'text': 'So for that, what I need to do is first select the data.', 'start': 4736.639, 'duration': 3.241}, {'end': 4744.401, 'text': "So this time I'm going to use control shift right key.", 'start': 4740.62, 'duration': 3.781}, {'end': 4745.201, 'text': 'So there you go.', 'start': 4744.641, 'duration': 0.56}, {'end': 4748.902, 'text': 'It selects the complete data available on that particular row.', 'start': 4745.361, 'duration': 3.541}, {'end': 4753.163, 'text': 'I copy the data which you can see by the dotted lines.', 'start': 4749.642, 'duration': 3.521}, {'end': 4756.504, 'text': 'I come to a new cell where I want to paste it.', 'start': 4753.723, 'duration': 2.781}, {'end': 4757.964, 'text': 'Right click.', 'start': 4757.324, 'duration': 0.64}, {'end': 4760.025, 'text': 'I go to paste special.', 'start': 4758.905, 'duration': 1.12}, {'end': 4763.886, 'text': 'And when I do paste special, I have a lot of options.', 'start': 4761.445, 'duration': 2.441}, {'end': 4768.429, 'text': 'I have formulas, value, formats, transpose and so on and so forth.', 'start': 4764.087, 'duration': 4.342}, {'end': 4775.673, 'text': "So the thing which I'm going to use is transpose, which basically transpose the rows into column format.", 'start': 4768.849, 'duration': 6.824}, {'end': 4778.635, 'text': 'Well, here we go.', 'start': 4777.334, 'duration': 1.301}, {'end': 4780.696, 'text': "So it's pretty simple.", 'start': 4779.295, 'duration': 1.401}, {'end': 4789.001, 'text': 'So we started in a different way and now we have got this particular data in a more readable format.', 'start': 4780.756, 'duration': 8.245}, {'end': 4800.442, 'text': 'So the end result, what I wanted to achieve, was to get as abhijit.narendra at threadfmail.com right.', 'start': 4790.222, 'duration': 10.22}, {'end': 4806.926, 'text': 'so here I am going to use some text functions before I do that.', 'start': 4800.442, 'duration': 6.484}, {'end': 4811.849, 'text': 'so if you notice this particular data, you have some space.', 'start': 4806.926, 'duration': 4.923}, {'end': 4816.457, 'text': 'so here, abhijit narendra, It does not have a space in front of him.', 'start': 4811.849, 'duration': 4.608}, {'end': 4823.579, 'text': 'But then if you see Achutha or Aadarsh or even Amneet, there is a small space showcasing.', 'start': 4816.958, 'duration': 6.621}, {'end': 4836.382, 'text': 'So this, how do I remove it? Any thoughts? Probably go here, click on sell and delete it.', 'start': 4824.159, 'duration': 12.223}, {'end': 4839.002, 'text': "What's your thought? Good idea.", 'start': 4837.042, 'duration': 1.96}, {'end': 4842.383, 'text': 'But then just imagine the number of times I have to do that.', 'start': 4839.502, 'duration': 2.881}, {'end': 4849.891, 'text': 'Is there more like an easily available formula which I can use in Excel? Yes, there is.', 'start': 4843.067, 'duration': 6.824}, {'end': 4855.015, 'text': "So for that, I'll use a formula called trim, which is very simple.", 'start': 4850.632, 'duration': 4.383}, {'end': 4864.261, 'text': 'You could just go here, equal to TRIM, open brackets.', 'start': 4855.615, 'duration': 8.646}, {'end': 4872.306, 'text': 'So you can see the syntax, right? So it tells trim, what is the text you want to trim? So select the text you want to trim, close brackets.', 'start': 4864.821, 'duration': 7.485}, {'end': 4875.871, 'text': 'Enter So you have done that.', 'start': 4873.47, 'duration': 2.401}, {'end': 4883.713, 'text': 'Again, if you are not aware, something which we learned in formulas, that you could actually research or find about that in the insert function.', 'start': 4876.351, 'duration': 7.362}, {'end': 4888.395, 'text': 'Try to locate if there is a formula available or if not, Google it.', 'start': 4884.234, 'duration': 4.161}, {'end': 4890.316, 'text': "So it's very easy for you to find out.", 'start': 4888.535, 'duration': 1.781}, {'end': 4892.376, 'text': 'For now, I know the formula.', 'start': 4890.876, 'duration': 1.5}, {'end': 4895.617, 'text': 'I know that there is a formula available and hence I used trim.', 'start': 4892.656, 'duration': 2.961}, {'end': 4900.659, 'text': 'So the way to learn Excel is again, you need to work, play around with it.', 'start': 4896.157, 'duration': 4.502}, {'end': 4908.195, 'text': "search as much as you can and then play around with all these formulas, then you'll come to know what is there, what is not available.", 'start': 4901.309, 'duration': 6.886}, {'end': 4916.341, 'text': "So for now, I'll just do it again, equal to trim, open brackets, text, close brackets.", 'start': 4908.975, 'duration': 7.366}, {'end': 4919.244, 'text': 'Perfect Okay.', 'start': 4917.963, 'duration': 1.281}, {'end': 4922.487, 'text': "So I'm not going to sit and write that for the old cells.", 'start': 4919.444, 'duration': 3.043}, {'end': 4927.871, 'text': "So I'm just going to use the autofill handler because I have a sequence over here, which the Excel will understand.", 'start': 4922.867, 'duration': 5.004}, {'end': 4931.979, 'text': 'I click on the small plus symbol, Double click on it.', 'start': 4928.451, 'duration': 3.528}, {'end': 4934.841, 'text': 'So it basically replicates the data across.', 'start': 4932.219, 'duration': 2.622}, {'end': 4940.845, 'text': 'So here you can see clearly that this particular cell still has a space in front of it.', 'start': 4935.341, 'duration': 5.504}, {'end': 4946.47, 'text': "However, here because I've used the trim functions, I do not have any particular cell.", 'start': 4941.346, 'duration': 5.124}, {'end': 4949.032, 'text': 'All right.', 'start': 4948.671, 'duration': 0.361}, {'end': 4956.237, 'text': 'So what we have learned from till now, we copied the data from a notepad, pasted in Excel.', 'start': 4949.392, 'duration': 6.845}, {'end': 4958.779, 'text': 'We found it was pretty difficult to read.', 'start': 4956.357, 'duration': 2.422}, {'end': 4961.075, 'text': 'So we use data.', 'start': 4959.454, 'duration': 1.621}, {'end': 4965.638, 'text': 'we use text to column to split the data into columns, then we copied the data,', 'start': 4961.075, 'duration': 4.563}, {'end': 4972.762, 'text': 'transposed it in a column and then we used one simple trim function to remove the space.', 'start': 4965.638, 'duration': 7.124}], 'summary': 'Excel data was split using text to column, transposed, and trimmed', 'duration': 392.086, 'max_score': 4580.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4580676.jpg'}, {'end': 4621.645, 'src': 'embed', 'start': 4587.779, 'weight': 4, 'content': [{'end': 4591.401, 'text': "So it's giving me delimiters or options to delimit the data.", 'start': 4587.779, 'duration': 3.622}, {'end': 4595.763, 'text': 'So I have tab, semicolon, a comma, a space or other.', 'start': 4592.141, 'duration': 3.622}, {'end': 4602.579, 'text': 'So Remember what I was talking about Excel that you need to add or keep giving information to Excel,', 'start': 4596.423, 'duration': 6.156}, {'end': 4605.38, 'text': 'and it will take that information and do the needful.', 'start': 4602.579, 'duration': 2.801}, {'end': 4611.822, 'text': 'So in this case, I am going to tell that dealing with the data, not by using tab, but by using semicolon.', 'start': 4605.88, 'duration': 5.942}, {'end': 4621.645, 'text': 'And how did I agree on using semicolon? If you notice the data closely or you review it, you see there are common aspects.', 'start': 4612.282, 'duration': 9.363}], 'summary': 'Instructing on data delimiters: tab, semicolon, comma, and space. opting for semicolon for data handling due to common aspects.', 'duration': 33.866, 'max_score': 4587.779, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4587779.jpg'}, {'end': 4748.902, 'src': 'embed', 'start': 4723.721, 'weight': 5, 'content': [{'end': 4730.824, 'text': 'So now I would like to use this data, not in a row, but in a column format.', 'start': 4723.721, 'duration': 7.103}, {'end': 4736.019, 'text': 'which makes it more easier for me to review and then do some modifications.', 'start': 4731.598, 'duration': 4.421}, {'end': 4739.88, 'text': 'So for that, what I need to do is first select the data.', 'start': 4736.639, 'duration': 3.241}, {'end': 4744.401, 'text': "So this time I'm going to use control shift right key.", 'start': 4740.62, 'duration': 3.781}, {'end': 4745.201, 'text': 'So there you go.', 'start': 4744.641, 'duration': 0.56}, {'end': 4748.902, 'text': 'It selects the complete data available on that particular row.', 'start': 4745.361, 'duration': 3.541}], 'summary': 'Using control shift right key selects complete data in a column format.', 'duration': 25.181, 'max_score': 4723.721, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4723721.jpg'}, {'end': 4983.108, 'src': 'embed', 'start': 4948.671, 'weight': 3, 'content': [{'end': 4949.032, 'text': 'All right.', 'start': 4948.671, 'duration': 0.361}, {'end': 4956.237, 'text': 'So what we have learned from till now, we copied the data from a notepad, pasted in Excel.', 'start': 4949.392, 'duration': 6.845}, {'end': 4958.779, 'text': 'We found it was pretty difficult to read.', 'start': 4956.357, 'duration': 2.422}, {'end': 4961.075, 'text': 'So we use data.', 'start': 4959.454, 'duration': 1.621}, {'end': 4965.638, 'text': 'we use text to column to split the data into columns, then we copied the data,', 'start': 4961.075, 'duration': 4.563}, {'end': 4972.762, 'text': 'transposed it in a column and then we used one simple trim function to remove the space.', 'start': 4965.638, 'duration': 7.124}, {'end': 4978.045, 'text': "Another thing about the trim function is it doesn't remove the space in between the cell.", 'start': 4973.763, 'duration': 4.282}, {'end': 4983.108, 'text': 'It removes the space in front or at the end of that particular cell.', 'start': 4978.185, 'duration': 4.923}], 'summary': 'Data was copied from notepad to excel, then split into columns and trimmed.', 'duration': 34.437, 'max_score': 4948.671, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4948671.jpg'}, {'end': 5054.605, 'src': 'embed', 'start': 5022.952, 'weight': 7, 'content': [{'end': 5031.934, 'text': 'So insert function, change to uppercase, click go.', 'start': 5022.952, 'duration': 8.982}, {'end': 5037.696, 'text': 'There is three, upper, lower and proper.', 'start': 5033.194, 'duration': 4.502}, {'end': 5041.257, 'text': 'So what is this? Converts a text string to all uppercase letters.', 'start': 5037.716, 'duration': 3.541}, {'end': 5050.083, 'text': 'Okay What is lower does? Converts all the letters in a text string to lower case and proper converts the text string to proper case.', 'start': 5042.017, 'duration': 8.066}, {'end': 5053.265, 'text': 'Great So we have seen that there is three options available.', 'start': 5050.663, 'duration': 2.602}, {'end': 5054.605, 'text': "So let's try all the three.", 'start': 5053.285, 'duration': 1.32}], 'summary': 'Demonstrated three text conversion options: uppercase, lowercase, proper case.', 'duration': 31.653, 'max_score': 5022.952, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc5022952.jpg'}, {'end': 5258.134, 'src': 'embed', 'start': 5223.929, 'weight': 8, 'content': [{'end': 5226.151, 'text': 'So there are multiple reasons what you might use.', 'start': 5223.929, 'duration': 2.222}, {'end': 5234.231, 'text': 'This exercise is just to showcase how you extract it and the usage may be depending on your process, when you want to use, etc.', 'start': 5226.684, 'duration': 7.547}, {'end': 5237.894, 'text': 'All right, so you saw how to extract the data from the left.', 'start': 5234.971, 'duration': 2.923}, {'end': 5244.039, 'text': 'But what if I just have to extract the data from the right? Well, I gave you the answer.', 'start': 5238.294, 'duration': 5.745}, {'end': 5246.101, 'text': 'It is by using write functions.', 'start': 5244.199, 'duration': 1.902}, {'end': 5258.134, 'text': 'So equal to R-I-G-H-T open brackets, select the text, close packet and 1.', 'start': 5246.821, 'duration': 11.313}], 'summary': 'Demonstrates data extraction from the right using the right function.', 'duration': 34.205, 'max_score': 5223.929, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc5223929.jpg'}, {'end': 5740.567, 'src': 'heatmap', 'start': 5344.043, 'weight': 0.75, 'content': [{'end': 5350.267, 'text': 'and try to get it into the format of abhijit.narendra at rediffmail.com.', 'start': 5344.043, 'duration': 6.224}, {'end': 5353.749, 'text': 'okay, so how do i do this so one.', 'start': 5350.267, 'duration': 3.482}, {'end': 5360.813, 'text': 'i could use the control f, replace the space with a dot and then add at the rediffmail.com.', 'start': 5353.749, 'duration': 7.064}, {'end': 5362.654, 'text': 'that is one way of it.', 'start': 5360.813, 'duration': 1.841}, {'end': 5369.859, 'text': 'or split this data again to abhijit and narendra and then add all the data together,', 'start': 5362.654, 'duration': 7.205}, {'end': 5375.945, 'text': 'or Add all the multiple strings together by using something called concatenate to get the desired result.', 'start': 5369.859, 'duration': 6.086}, {'end': 5384.687, 'text': "So for now I'm going to use the second option which is splitting the data and then adding it back or concatenating it together.", 'start': 5376.685, 'duration': 8.002}, {'end': 5390.769, 'text': "To do that first I'll have to select the complete list of data.", 'start': 5387.188, 'duration': 3.581}, {'end': 5394.11, 'text': 'I go back to my data tab.', 'start': 5392.53, 'duration': 1.58}, {'end': 5396.411, 'text': 'Text to columns.', 'start': 5395.531, 'duration': 0.88}, {'end': 5398.972, 'text': "I've selected my text to columns.", 'start': 5397.331, 'duration': 1.641}, {'end': 5401.44, 'text': 'And this time the delimiter.', 'start': 5400.12, 'duration': 1.32}, {'end': 5409.362, 'text': "What would be the delimiter? Not a semicolon because the delimiter is going to be space, right? So I'm going to use space.", 'start': 5401.82, 'duration': 7.542}, {'end': 5415.423, 'text': 'And then I click next and finish.', 'start': 5412.742, 'duration': 2.681}, {'end': 5417.483, 'text': "Well, it's not working.", 'start': 5415.983, 'duration': 1.5}, {'end': 5421.444, 'text': 'Any idea why? No.', 'start': 5417.943, 'duration': 3.501}, {'end': 5428.782, 'text': 'The reason for that is, if you go back and look at your insert function or your formula bar,', 'start': 5422.524, 'duration': 6.258}, {'end': 5437.327, 'text': 'can you see it is actually showing you the formula and not just the text, and that is the reason why the text to column did not apply.', 'start': 5428.782, 'duration': 8.545}, {'end': 5444.391, 'text': "this again reminds you that it's very important to know what you're working with, whether it is a text or it's a formula.", 'start': 5437.327, 'duration': 7.064}, {'end': 5449.814, 'text': "so if you're working on a formula, another formula will not work.", 'start': 5444.391, 'duration': 5.423}, {'end': 5453.276, 'text': 'okay, so how do i convert this or remove the formula?', 'start': 5449.814, 'duration': 3.462}, {'end': 5463.194, 'text': "simple, i've already selected the data, copy it, come to a new cell or in fact i can come back to the same cell right click, paste,", 'start': 5453.276, 'duration': 9.918}, {'end': 5466.155, 'text': 'special and values.', 'start': 5463.194, 'duration': 2.961}, {'end': 5469.396, 'text': 'so it removes the formulas and places only the values.', 'start': 5466.155, 'duration': 3.241}, {'end': 5473.838, 'text': 'immediately check how the data is represented in the formula bar.', 'start': 5469.396, 'duration': 4.442}, {'end': 5477.199, 'text': 'it is basically showcasing the first name and last name.', 'start': 5473.838, 'duration': 3.361}, {'end': 5479.28, 'text': 'so before it was showing trim.', 'start': 5477.199, 'duration': 2.081}, {'end': 5486.73, 'text': "so i have the data as is, and now i'm going to reapply the text to column function, So applying the text to column functions.", 'start': 5479.28, 'duration': 7.45}, {'end': 5489.172, 'text': 'now I use the delimit.', 'start': 5486.73, 'duration': 2.442}, {'end': 5491.774, 'text': 'Under delimit, I use next.', 'start': 5490.113, 'duration': 1.661}, {'end': 5495.037, 'text': "Instead of semicolon, this time it's space.", 'start': 5493.035, 'duration': 2.002}, {'end': 5498.38, 'text': 'I have the data broken into first name and last name.', 'start': 5495.497, 'duration': 2.883}, {'end': 5501.122, 'text': 'Click next and finish.', 'start': 5499.24, 'duration': 1.882}, {'end': 5507.247, 'text': 'So please note it transported or pasted the data in the next column.', 'start': 5501.782, 'duration': 5.465}, {'end': 5511.61, 'text': "So do remember that your next column is always free and doesn't have a cell content.", 'start': 5507.307, 'duration': 4.303}, {'end': 5515.773, 'text': 'If you have, then Excel will throw up an error telling that there is some content.', 'start': 5512.29, 'duration': 3.483}, {'end': 5521.918, 'text': 'Do you want me to replace it? And you can actually go ahead and click finish where it will replace that particular data.', 'start': 5515.953, 'duration': 5.965}, {'end': 5524.58, 'text': 'So we have come a long way.', 'start': 5523.259, 'duration': 1.321}, {'end': 5530.225, 'text': 'We got the data from here and we have got it and separated in the first name and last name format.', 'start': 5524.66, 'duration': 5.565}, {'end': 5538.872, 'text': 'So from here on, how do I add abhijit.narendra at ridofmail.com? So this is my end result.', 'start': 5531.466, 'duration': 7.406}, {'end': 5541.134, 'text': 'What I want is abhijit.', 'start': 5539.312, 'duration': 1.822}, {'end': 5547.315, 'text': 'dot, narendra at rediffmail.com.', 'start': 5541.991, 'duration': 5.324}, {'end': 5550.317, 'text': "that's how i want to get the end result.", 'start': 5547.315, 'duration': 3.002}, {'end': 5557.742, 'text': 'okay. so for this there is a simple function which i need to use.', 'start': 5550.317, 'duration': 7.425}, {'end': 5563.546, 'text': "for this, i'll use something called an inbuilt function which is concatenate.", 'start': 5557.742, 'duration': 5.804}, {'end': 5566.007, 'text': "now i'm not sure how to use concatenate right.", 'start': 5563.546, 'duration': 2.461}, {'end': 5572.518, 'text': "so for now, for the first time, i'm going to use insert function, search, concatenate.", 'start': 5566.007, 'duration': 6.511}, {'end': 5576.34, 'text': "oh, this already there because it's my frequently used function.", 'start': 5572.518, 'duration': 3.822}, {'end': 5585.867, 'text': 'if not, I could have just told joins text together and click on, go, it would have bought in the similar function.', 'start': 5576.34, 'duration': 9.527}, {'end': 5587.808, 'text': 'so I have concatenate out here.', 'start': 5585.867, 'duration': 1.941}, {'end': 5589.609, 'text': 'so how do I concatenate?', 'start': 5587.808, 'duration': 1.801}, {'end': 5590.55, 'text': 'I use this.', 'start': 5589.609, 'duration': 0.941}, {'end': 5599.069, 'text': 'so click OK, text one would be my first name and click my red button again.', 'start': 5590.55, 'duration': 8.519}, {'end': 5602.35, 'text': 'so text 2 note, I have to add a dot.', 'start': 5599.069, 'duration': 3.281}, {'end': 5604.071, 'text': "that's what I wanted, right.", 'start': 5602.35, 'duration': 1.721}, {'end': 5607.212, 'text': 'so if I just click a dot, that should be fine.', 'start': 5604.071, 'duration': 3.141}, {'end': 5615.456, 'text': 'and text 3 I select my last name so you could see the sample as how the data would say or the result format.', 'start': 5607.212, 'duration': 8.244}, {'end': 5618.237, 'text': 'result would be in similar way in the text 4.', 'start': 5615.456, 'duration': 2.781}, {'end': 5625.8, 'text': 'I could simply add at the rate rid of mail.com and leave the text file empty.', 'start': 5618.237, 'duration': 7.563}, {'end': 5628.64, 'text': "so that's where I get my recept.", 'start': 5625.8, 'duration': 2.84}, {'end': 5629.801, 'text': 'first name, dot.', 'start': 5628.64, 'duration': 1.161}, {'end': 5632.103, 'text': 'last name at rediff.com.', 'start': 5629.801, 'duration': 2.302}, {'end': 5636.866, 'text': 'so this I used using the functional argument or the wizard to get it.', 'start': 5632.103, 'duration': 4.763}, {'end': 5638.067, 'text': 'I can also type in.', 'start': 5636.866, 'duration': 1.201}, {'end': 5645.193, 'text': "but just that I need to be very careful when I'm using a formula and I need to be used to typing the formula with the right syntax.", 'start': 5638.067, 'duration': 7.126}, {'end': 5653.98, 'text': 'so if I have to do that, I will be using equal to concatenate open brackets.', 'start': 5645.193, 'duration': 8.787}, {'end': 5657.363, 'text': 'select the first text, then comma.', 'start': 5653.98, 'duration': 3.383}, {'end': 5665.974, 'text': 'Then, since dot is not available, I have to put it in quotes comma.', 'start': 5657.95, 'duration': 8.024}, {'end': 5670.457, 'text': 'select the second text, then again comma.', 'start': 5665.974, 'duration': 4.483}, {'end': 5678.361, 'text': 'I have to put at the rate within quotes comma.', 'start': 5670.457, 'duration': 7.904}, {'end': 5680.062, 'text': 'again, rediff mail.', 'start': 5678.361, 'duration': 1.701}, {'end': 5689.774, 'text': "So it's a little complicated, but as you get used to it, you will remember it.", 'start': 5683.972, 'duration': 5.802}, {'end': 5693.815, 'text': 'Yeah, so simple.', 'start': 5689.774, 'duration': 4.041}, {'end': 5697.517, 'text': "So just I'm going to use the autofill handler to do that.", 'start': 5693.815, 'duration': 3.702}, {'end': 5704.299, 'text': "There's some shortcuts available for concatenate as well, Which you could use once you are pretty experienced,", 'start': 5697.517, 'duration': 6.782}, {'end': 5709.861, 'text': 'or either you have worked on it a lot of times and it makes your life little easier.', 'start': 5704.299, 'duration': 5.562}, {'end': 5718.08, 'text': 'the simple way of using that would be equal to you select your first text and instead of you using concatenate,', 'start': 5709.861, 'duration': 8.219}, {'end': 5722.003, 'text': 'you could just use the and or add the sign.', 'start': 5718.08, 'duration': 3.923}, {'end': 5725.345, 'text': 'here we go, and then you could use the second text.', 'start': 5722.003, 'duration': 3.342}, {'end': 5731.088, 'text': 'this also concatenates, but then, if you want to use space, it would be comma.', 'start': 5725.345, 'duration': 5.743}, {'end': 5740.567, 'text': 'this works.', 'start': 5739.887, 'duration': 0.68}], 'summary': 'Data was split and concatenated to create email format in excel.', 'duration': 396.524, 'max_score': 5344.043, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc5344043.jpg'}, {'end': 5585.867, 'src': 'embed', 'start': 5557.742, 'weight': 10, 'content': [{'end': 5563.546, 'text': "for this, i'll use something called an inbuilt function which is concatenate.", 'start': 5557.742, 'duration': 5.804}, {'end': 5566.007, 'text': "now i'm not sure how to use concatenate right.", 'start': 5563.546, 'duration': 2.461}, {'end': 5572.518, 'text': "so for now, for the first time, i'm going to use insert function, search, concatenate.", 'start': 5566.007, 'duration': 6.511}, {'end': 5576.34, 'text': "oh, this already there because it's my frequently used function.", 'start': 5572.518, 'duration': 3.822}, {'end': 5585.867, 'text': 'if not, I could have just told joins text together and click on, go, it would have bought in the similar function.', 'start': 5576.34, 'duration': 9.527}], 'summary': 'Using concatenation and insert function for text manipulation.', 'duration': 28.125, 'max_score': 5557.742, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc5557742.jpg'}, {'end': 5909.276, 'src': 'embed', 'start': 5884.048, 'weight': 11, 'content': [{'end': 5889.85, 'text': "Okay, a lot of things, right? Just let me quickly see if I've covered everything in text functions.", 'start': 5884.048, 'duration': 5.802}, {'end': 5899.813, 'text': 'We saw extracting data, we saw left, right, center, and then formatting, we removed unwanted space, we joined several text strings together.', 'start': 5891.07, 'duration': 8.743}, {'end': 5904.595, 'text': 'We also transpose data into columns and the same you could do to rows.', 'start': 5899.833, 'duration': 4.762}, {'end': 5909.276, 'text': 'We saw text to columns, delimit, and the last thing which is count function.', 'start': 5905.233, 'duration': 4.043}], 'summary': 'Covered text functions: extract, format, transpose, join, text to columns, and count.', 'duration': 25.228, 'max_score': 5884.048, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc5884048.jpg'}, {'end': 6141.024, 'src': 'embed', 'start': 6119.763, 'weight': 12, 'content': [{'end': 6130.307, 'text': 'Now, even before I go ahead and explain to you about relative reference and we work on this particular exercise let me simply take you through a very easy exercise which gives you an understanding.', 'start': 6119.763, 'duration': 10.544}, {'end': 6133.349, 'text': "So while we're talking, I opened up a new spreadsheet.", 'start': 6130.827, 'duration': 2.522}, {'end': 6137.942, 'text': 'Let me just do a simple arithmetic problems.', 'start': 6134.679, 'duration': 3.263}, {'end': 6141.024, 'text': 'This you can really recollect from your chapter 3.', 'start': 6138.202, 'duration': 2.822}], 'summary': 'Introduction to relative reference and spreadsheet exercise in chapter 3.', 'duration': 21.261, 'max_score': 6119.763, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc6119763.jpg'}], 'start': 4326.002, 'title': 'Data manipulation in excel', 'summary': 'Covers topics such as data copying deception, text to column and transpose, data transformation using text functions, and practical exercises on excel functions and formulas, with specific examples and quantifiable data.', 'chapters': [{'end': 4435.658, 'start': 4326.002, 'title': 'Data copying deception', 'summary': 'Discusses the deceptive appearance of copied data in a spreadsheet, emphasizing the importance of the formula bar in verifying the actual location of the data and highlighting the challenges of navigating large pasted data.', 'duration': 109.656, 'highlights': ['The importance of reviewing the formula bar to verify the actual location and availability of data in the spreadsheet, ensuring accurate data representation.', 'Demonstration of how the appearance of data in different cells can be deceiving, emphasizing the need to use the formula bar for accurate verification.', 'Challenges of navigating large pasted data in a spreadsheet, illustrating the inconvenience and vastness of the spread data.']}, {'end': 4778.635, 'start': 4436.218, 'title': 'Data text to column and transpose in excel', 'summary': 'Explains the process of using text to column and transpose in excel to format data for sending emails, including delimiting the data, breaking it into multiple columns, and transposing rows into column format.', 'duration': 342.417, 'highlights': ['The process of using text to column and transpose in Excel to format data for sending emails', 'Delimiting the data using semicolon to break it into multiple columns for easier processing', 'Transposing rows into column format to make data review and modification easier']}, {'end': 5524.58, 'start': 4779.295, 'title': 'Excel data transformation and text functions', 'summary': 'Demonstrates the use of text functions in excel to transform data, including using the trim function to remove spaces, the upper, lower, and proper functions to change text case, and the left and right functions to extract characters. it also covers using text to columns to split data and the concatenate function for combining strings.', 'duration': 745.285, 'highlights': ['The chapter demonstrates the use of the TRIM function to remove spaces from text, making the data more readable.', 'The chapter explains the use of the UPPER, LOWER, and PROPER functions to convert text to uppercase, lowercase, and proper case, providing practical examples of each.', 'The chapter covers the use of the LEFT and RIGHT functions to extract characters from text, showcasing practical applications such as creating user login IDs.', 'The chapter discusses using TEXT TO COLUMNS to split data based on a delimiter, highlighting the importance of working with text versus formulas.', 'The chapter explains the use of the CONCATENATE function to combine strings, providing an alternative method for achieving the desired data format.']}, {'end': 6818.351, 'start': 5524.66, 'title': 'Excel functions and formulas', 'summary': 'Covers the usage of concatenate and count functions, the concept of relative reference, and practical exercises demonstrating the application of these functions in excel, with specific examples and quantifiable data.', 'duration': 1293.691, 'highlights': ['The chapter covers the usage of concatenate and count functions', 'The concept of relative reference is explained with practical exercises', 'Practical exercises demonstrate the application of these functions in Excel']}], 'duration': 2492.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc4326002.jpg', 'highlights': ['Demonstration of how the appearance of data in different cells can be deceiving, emphasizing the need to use the formula bar for accurate verification.', 'The importance of reviewing the formula bar to verify the actual location and availability of data in the spreadsheet, ensuring accurate data representation.', 'Challenges of navigating large pasted data in a spreadsheet, illustrating the inconvenience and vastness of the spread data.', 'The process of using text to column and transpose in Excel to format data for sending emails', 'Delimiting the data using semicolon to break it into multiple columns for easier processing', 'Transposing rows into column format to make data review and modification easier', 'The chapter demonstrates the use of the TRIM function to remove spaces from text, making the data more readable.', 'The chapter explains the use of the UPPER, LOWER, and PROPER functions to convert text to uppercase, lowercase, and proper case, providing practical examples of each.', 'The chapter covers the use of the LEFT and RIGHT functions to extract characters from text, showcasing practical applications such as creating user login IDs.', 'The chapter discusses using TEXT TO COLUMNS to split data based on a delimiter, highlighting the importance of working with text versus formulas.', 'The chapter explains the use of the CONCATENATE function to combine strings, providing an alternative method for achieving the desired data format.', 'The chapter covers the usage of concatenate and count functions', 'The concept of relative reference is explained with practical exercises', 'Practical exercises demonstrate the application of these functions in Excel']}, {'end': 7902.787, 'segs': [{'end': 6897.494, 'src': 'embed', 'start': 6863.872, 'weight': 0, 'content': [{'end': 6873.373, 'text': 'So basic I could use investment minus the total minus tax amount.', 'start': 6863.872, 'duration': 9.501}, {'end': 6880.876, 'text': 'so it will show a 500 and I could just copy that and again paste the formula down.', 'start': 6873.373, 'duration': 7.503}, {'end': 6888.059, 'text': 'Okay, so here it is showing me a negative value, because my total minus tax is actually 50, 000 rupees, 34, 000 rupees and 8, 500,', 'start': 6882.016, 'duration': 6.043}, {'end': 6897.494, 'text': 'whereas my investment is 8, 000, 45, 000 and 25, 000.', 'start': 6888.059, 'duration': 9.435}], 'summary': 'Investment calculation resulted in negative value due to high total minus tax amount.', 'duration': 33.622, 'max_score': 6863.872, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc6863872.jpg'}, {'end': 7006.64, 'src': 'embed', 'start': 6979.628, 'weight': 2, 'content': [{'end': 6989.572, 'text': 'So here simple relative reference at work and you saw how how relative reference can help you make your easy.', 'start': 6979.628, 'duration': 9.944}, {'end': 6995.435, 'text': 'Make your Excel task a little easier and do not have to repeat the formulas again and again.', 'start': 6990.493, 'duration': 4.942}, {'end': 6997.236, 'text': 'So great easy.', 'start': 6996.135, 'duration': 1.101}, {'end': 7002.938, 'text': 'Alright, so with you understanding related reference.', 'start': 6999.317, 'duration': 3.621}, {'end': 7006.64, 'text': "Now let's step back to a little bit more complex references.", 'start': 7003.078, 'duration': 3.562}], 'summary': 'Relative references in excel simplify tasks, reducing repetitive formulas, making work easier.', 'duration': 27.012, 'max_score': 6979.628, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc6979628.jpg'}, {'end': 7112.087, 'src': 'embed', 'start': 7061.741, 'weight': 3, 'content': [{'end': 7068.064, 'text': 'This is especially when you move the formula down or up that particular cell reference has to remain constant.', 'start': 7061.741, 'duration': 6.323}, {'end': 7070.325, 'text': 'You will see that in examples.', 'start': 7068.084, 'duration': 2.241}, {'end': 7075.747, 'text': 'So, at that point of time, we use something called the dollar sign, which I will show you,', 'start': 7070.945, 'duration': 4.802}, {'end': 7081.97, 'text': 'and we use that to block the position of that particular cell or the reference.', 'start': 7075.747, 'duration': 6.223}, {'end': 7092.018, 'text': 'This way, that particular reference where the position of the cell is hold constant blocked is nothing but called an absolute reference.', 'start': 7082.774, 'duration': 9.244}, {'end': 7099.902, 'text': "So you're making that as an absolute value, which means it remains intact no matter any changes to your formula.", 'start': 7092.378, 'duration': 7.524}, {'end': 7103.583, 'text': 'But that is what is absolute reference.', 'start': 7100.342, 'duration': 3.241}, {'end': 7110.006, 'text': "Understood? You'll understand it better when I do the demo.", 'start': 7105.224, 'duration': 4.782}, {'end': 7112.087, 'text': "So let's quickly move on to the demo.", 'start': 7110.246, 'duration': 1.841}], 'summary': 'Using dollar sign creates absolute reference, remains intact despite formula changes.', 'duration': 50.346, 'max_score': 7061.741, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc7061741.jpg'}, {'end': 7708.927, 'src': 'embed', 'start': 7679.591, 'weight': 1, 'content': [{'end': 7682.453, 'text': 'Here it is multiplying 4, but 6 remains constant.', 'start': 7679.591, 'duration': 2.862}, {'end': 7692.918, 'text': "So what I'm using here is actually absolute referencing, where 6 cell or the cell B3 is an absolute reference,", 'start': 7682.933, 'duration': 9.985}, {'end': 7696, 'text': "and I'm using absolute reference to complete my exercise.", 'start': 7692.918, 'duration': 3.082}, {'end': 7699.242, 'text': 'Simple? Good.', 'start': 7697, 'duration': 2.242}, {'end': 7703.564, 'text': 'So the exercise 1 for you to work on.', 'start': 7699.762, 'duration': 3.802}, {'end': 7708.927, 'text': "I'll just explain this to you and give you some time to work, and then we can come back.", 'start': 7703.584, 'duration': 5.343}], 'summary': 'Using absolute referencing, multiply 4 while keeping 6 constant. exercise 1 assigned.', 'duration': 29.336, 'max_score': 7679.591, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc7679591.jpg'}, {'end': 7781.294, 'src': 'embed', 'start': 7738.479, 'weight': 4, 'content': [{'end': 7741.88, 'text': 'you could use absolute reference and get it done.', 'start': 7738.479, 'duration': 3.401}, {'end': 7758.667, 'text': 'so quick, 10 minutes pause for you to try and then replay the video.', 'start': 7741.88, 'duration': 16.787}, {'end': 7764.169, 'text': 'okay, so hopefully you were able to solve the exercise, one of absolute reference.', 'start': 7758.667, 'duration': 5.502}, {'end': 7766.468, 'text': 'If not, let me do it again.', 'start': 7764.907, 'duration': 1.561}, {'end': 7777.312, 'text': "So here I'm going to use the same formula, equal to 240 and similar how we multiplied by 6.", 'start': 7769.029, 'duration': 8.283}, {'end': 7781.294, 'text': 'This time I know the cost per kilometer is 9.', 'start': 7777.312, 'duration': 3.982}], 'summary': 'Using absolute reference in excel formula, multiplying by 6, with a cost of 9 per kilometer.', 'duration': 42.815, 'max_score': 7738.479, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc7738479.jpg'}], 'start': 6818.972, 'title': 'Absolute reference in excel', 'summary': 'Demonstrates the use of relative and absolute references in excel formulas, showing how to calculate profit by using investment, total minus tax, and total plus tax, and then explains the concept of absolute reference with dollar sign in excel formulas. it also emphasizes on multiplying values and understanding the difference between relative and absolute referencing, ultimately leading to accurate results.', 'chapters': [{'end': 7136.912, 'start': 6818.972, 'title': 'Excel formulas and references', 'summary': 'Demonstrates the use of relative and absolute references in excel formulas, showing how to calculate profit by using investment, total minus tax, and total plus tax, and then explains the concept of absolute reference with dollar sign in excel formulas.', 'duration': 317.94, 'highlights': ['The chapter demonstrates how to calculate profit in Excel by using investment, total minus tax, and total plus tax, showing a negative value due to the difference between total minus tax and investment.', 'The speaker emphasizes the importance of relative reference in Excel formulas, showcasing its use to avoid repeating formulas and make Excel tasks easier.', 'The concept of absolute reference in Excel is explained, highlighting the use of a dollar sign to keep a particular cell reference constant when moving the formula up or down.']}, {'end': 7902.787, 'start': 7137.392, 'title': 'Absolute reference in excel', 'summary': 'Demonstrates the use of absolute reference in excel to perform calculations, with emphasis on multiplying values and understanding the difference between relative and absolute referencing, ultimately leading to accurate results.', 'duration': 765.395, 'highlights': ['Demonstrating the use of absolute reference to multiply values in Excel, ensuring the accuracy of calculations and avoiding errors caused by relative referencing.', 'Explaining the concept of absolute reference through the example of blocking both rows and columns, emphasizing the need to block the complete cell for accurate calculations.', 'Illustrating the application of absolute reference in multiplying values, such as the cost per kilometer by the kilometers traveled, and resolving errors by using absolute reference to obtain accurate results.']}], 'duration': 1083.815, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc6818972.jpg', 'highlights': ['The chapter demonstrates how to calculate profit in Excel by using investment, total minus tax, and total plus tax, showing a negative value due to the difference between total minus tax and investment.', 'Demonstrating the use of absolute reference to multiply values in Excel, ensuring the accuracy of calculations and avoiding errors caused by relative referencing.', 'The speaker emphasizes the importance of relative reference in Excel formulas, showcasing its use to avoid repeating formulas and make Excel tasks easier.', 'Explaining the concept of absolute reference through the example of blocking both rows and columns, emphasizing the need to block the complete cell for accurate calculations.', 'Illustrating the application of absolute reference in multiplying values, such as the cost per kilometer by the kilometers traveled, and resolving errors by using absolute reference to obtain accurate results.', 'The concept of absolute reference in Excel is explained, highlighting the use of a dollar sign to keep a particular cell reference constant when moving the formula up or down.']}, {'end': 9122.035, 'segs': [{'end': 7958.114, 'src': 'embed', 'start': 7903.807, 'weight': 0, 'content': [{'end': 7906.848, 'text': 'So giving a 10 second pause for you to work on.', 'start': 7903.807, 'duration': 3.041}, {'end': 7927.551, 'text': 'Okay, so hopefully you were able to solve this particular exercise too.', 'start': 7921.587, 'duration': 5.964}, {'end': 7932.776, 'text': 'Simple, similar to this, just that you need to do it four times.', 'start': 7928.873, 'duration': 3.903}, {'end': 7935.698, 'text': 'So cost of transport equal to 987.', 'start': 7933.416, 'duration': 2.282}, {'end': 7940.962, 'text': "What's the amount for the car? It's 11 rupees.", 'start': 7935.698, 'duration': 5.264}, {'end': 7943.865, 'text': 'So I do into 11, enter.', 'start': 7941.603, 'duration': 2.262}, {'end': 7950.313, 'text': 'And this time my G23 becomes absolute reference.', 'start': 7946.972, 'duration': 3.341}, {'end': 7952.053, 'text': 'I hit on F4.', 'start': 7951.013, 'duration': 1.04}, {'end': 7958.114, 'text': 'Enter And I do this just for the car.', 'start': 7953.193, 'duration': 4.921}], 'summary': 'Exercise solution: cost of transport is 987; car costs 11 rupees.', 'duration': 54.307, 'max_score': 7903.807, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc7903807.jpg'}, {'end': 8098.891, 'src': 'embed', 'start': 8070.935, 'weight': 1, 'content': [{'end': 8077.177, 'text': 'I gave an understanding about how dollar is used to block a column, block a row, etc.', 'start': 8070.935, 'duration': 6.242}, {'end': 8086.321, 'text': 'So here it is all about doing that together using relative and absolute and using mixed reference to solve your problems.', 'start': 8077.577, 'duration': 8.744}, {'end': 8092.907, 'text': 'so, in terms of definition, a mixed cell reference is either an absolute column or a relative row.', 'start': 8087.003, 'duration': 5.904}, {'end': 8098.891, 'text': 'or an absolute row or relative column understood the different different dollar sign which i was showing in the notepad right.', 'start': 8092.907, 'duration': 5.984}], 'summary': 'Explained usage of dollar sign to block columns and rows, and mixed cell reference in excel.', 'duration': 27.956, 'max_score': 8070.935, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc8070935.jpg'}, {'end': 8690.424, 'src': 'embed', 'start': 8655.596, 'weight': 3, 'content': [{'end': 8673.852, 'text': 'but just to see if the power function is working fine, 2 to the power of 2 is 4, equal to 2 to the power of 3 is 8.', 'start': 8655.596, 'duration': 18.256}, {'end': 8677.535, 'text': "It's 2 into 2 into 2..", 'start': 8673.852, 'duration': 3.683}, {'end': 8679.136, 'text': "OK, so let's get back.", 'start': 8677.535, 'duration': 1.601}, {'end': 8680.317, 'text': 'Now we have the formula.', 'start': 8679.256, 'duration': 1.061}, {'end': 8690.424, 'text': 'Now how do we use the referencing? Similar to how we used in this particular mixed reference exercise, we use the same logic, excuse me.', 'start': 8680.437, 'duration': 9.987}], 'summary': 'Testing power function: 2^2=4, 2^3=8. using referencing logic.', 'duration': 34.828, 'max_score': 8655.596, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc8655596.jpg'}], 'start': 7903.807, 'title': 'Excel referencing and power functions', 'summary': 'Covers absolute and mixed referencing in excel for calculating transport costs, troubleshooting errors, adjusting cell references, and using power functions with examples, including the application of references in formulas for solving practical problems.', 'chapters': [{'end': 8043.293, 'start': 7903.807, 'title': 'Absolute reference and transport cost calculation', 'summary': 'Demonstrates the application of absolute reference in calculating transport costs for car, bus, jeep, and tempo traveler, achieving correct results for each using specific formulas and values.', 'duration': 139.486, 'highlights': ['The exercise involves calculating the transport cost for a car, bus, jeep, and tempo traveler using absolute reference and specific formulas, providing practical insights into the application of absolute reference in Excel.', 'The cost of transport for the car is 987, and the amount for the car is 11 rupees, with the formula resulting in the value of 988.', "The correct calculation for the bus's transport cost is shown, with the formula yielding the accurate value when multiplied by 29.", 'The demonstration includes the use of absolute references, such as G23 and G25, for specific calculations, providing practical examples of applying absolute reference in Excel.', 'The exercise provides valuable insights into the use of absolute reference, particularly in the context of calculating transport costs, enhancing the understanding of absolute reference usage in Excel.']}, {'end': 8349.37, 'start': 8045.515, 'title': 'Mixed referencing in excel', 'summary': 'Explains mixed referencing in excel, which involves using a combination of relative and absolute referencing to solve problems. it includes a practical exercise to demonstrate the correct usage of mixed referencing and troubleshoots the errors encountered during the process.', 'duration': 303.855, 'highlights': ['The definition of mixed cell reference is explained as either an absolute column or a relative row, or an absolute row or relative column, which involves using the dollar symbol to create absolute referencing.', 'A practical exercise is conducted to demonstrate the use of mixed referencing in Excel to find and multiply values in specific cells using a single formula, showcasing the importance of using the correct referencing for accurate results.', 'The troubleshooting process involves identifying errors in the referencing as the position of the cells changes, leading to incorrect results, and explores solutions such as making specific cell references absolute to ensure accurate calculations.']}, {'end': 8552.122, 'start': 8350.351, 'title': 'Excel absolute and relative referencing', 'summary': 'Discusses the use of mixed referencing in excel to solve a problem, demonstrating the use of absolute and relative referencing to adjust cell references, resulting in successful formula application and dynamic referencing.', 'duration': 201.771, 'highlights': ['The use of mixed referencing in Excel to solve a problem by demonstrating the use of absolute and relative referencing to adjust cell references, resulting in a successful formula application and dynamic referencing.', 'The demonstration of using mixed referencing to block either rows or columns to achieve the necessary referencing, allowing for dynamic changes in the cell references.', 'The successful application of mixed referencing in Excel, exemplified by the formula adjusting the column and row references dynamically, ensuring the accurate calculation of values.', 'The explanation of how mixed referencing works, with examples showing the blocking of columns and the freedom of rows in cell references to achieve dynamic referencing.', 'The detailed explanation of using mixed referencing in Excel, showcasing the application of absolute referencing for blocking columns and relative referencing for dynamic row changes, leading to the accurate calculation of values.']}, {'end': 8723.703, 'start': 8552.122, 'title': 'Power functions in excel', 'summary': 'Discusses how to use power functions in excel and explains the concept of referencing in formulas, including the use of the f4 key to lock specific cell references, with examples of 2 to the power of 2 being 4 and 2 to the power of 3 being 8.', 'duration': 171.581, 'highlights': ['Explaining the concept of using power functions in Excel, such as 2 to the power of 2 being 4 and 2 to the power of 3 being 8.', 'Demonstrating the use of referencing in formulas, including locking specific cell references using the F4 key.', 'Encouraging practice by providing an exercise involving power functions and mixed referencing in Excel.']}, {'end': 9122.035, 'start': 8723.703, 'title': 'Using references in formulas', 'summary': 'Explains how to use relative, absolute, and mixed references in formulas to solve problems, with a demonstration of finding units used, unit charge, and amount payable in an electricity bill scenario.', 'duration': 398.332, 'highlights': ['Demonstration of using relative, absolute, and mixed references in formulas', 'Explanation of finding units used in an electricity bill scenario', 'Illustration of using absolute referencing in formulas']}], 'duration': 1218.228, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc7903807.jpg', 'highlights': ['The exercise involves calculating transport cost for car, bus, jeep, and tempo traveler using absolute reference and specific formulas, providing practical insights into the application of absolute reference in Excel.', 'The definition of mixed cell reference is explained as either an absolute column or a relative row, or an absolute row or relative column, which involves using the dollar symbol to create absolute referencing.', 'The use of mixed referencing in Excel to solve a problem by demonstrating the use of absolute and relative referencing to adjust cell references, resulting in a successful formula application and dynamic referencing.', 'Explaining the concept of using power functions in Excel, such as 2 to the power of 2 being 4 and 2 to the power of 3 being 8.', 'Demonstration of using relative, absolute, and mixed references in formulas']}, {'end': 10940.746, 'segs': [{'end': 9224.554, 'src': 'embed', 'start': 9186.887, 'weight': 0, 'content': [{'end': 9190.77, 'text': 'and then I will teach you all different types of data validations.', 'start': 9186.887, 'duration': 3.883}, {'end': 9194.273, 'text': 'So what is data validation?', 'start': 9192.531, 'duration': 1.742}, {'end': 9197.515, 'text': 'If I go by the definition,', 'start': 9195.974, 'duration': 1.541}, {'end': 9207.844, 'text': 'it is basically a feature in Excel you could use to restrict certain values or allow users to pick up those values which you restrict.', 'start': 9197.515, 'duration': 10.329}, {'end': 9212.761, 'text': 'You could configure data validation to prevent users from entering data.', 'start': 9208.837, 'duration': 3.924}, {'end': 9224.554, 'text': "Like for example, you want users to just enter numbers and you don't want them to enter numbers which are greater than say 100.", 'start': 9213.302, 'duration': 11.252}], 'summary': 'Teaching various types of data validations in excel, including restrictions and configuration.', 'duration': 37.667, 'max_score': 9186.887, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc9186887.jpg'}, {'end': 9918.816, 'src': 'embed', 'start': 9887.341, 'weight': 3, 'content': [{'end': 9897.312, 'text': 'what I want to do is I want to accept only whole numbers between 1 to 10, and I want proper input and error message to be displayed.', 'start': 9887.341, 'duration': 9.971}, {'end': 9899.052, 'text': "Okay, I'll repeat that.", 'start': 9897.732, 'duration': 1.32}, {'end': 9905.793, 'text': 'So I just can enter numbers between 1 to 10.', 'start': 9899.492, 'duration': 6.301}, {'end': 9910.854, 'text': "If I enter any numbers, let's say 36, it should throw up an error.", 'start': 9905.793, 'duration': 5.061}, {'end': 9917.696, 'text': 'And another thing is if I do a mistake, it should throw up an error.', 'start': 9912.615, 'duration': 5.081}, {'end': 9918.816, 'text': 'That is a requirement.', 'start': 9917.936, 'duration': 0.88}], 'summary': 'Accept only whole numbers between 1 to 10 with proper input and error messages.', 'duration': 31.475, 'max_score': 9887.341, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc9887341.jpg'}, {'end': 10243.961, 'src': 'embed', 'start': 10205.252, 'weight': 2, 'content': [{'end': 10213.034, 'text': 'Under that, I again go to data, data validations, and I click data validations.', 'start': 10205.252, 'duration': 7.782}, {'end': 10219.575, 'text': "Now what I'm going to do is I'm not going to select whole numbers because, of course, it's not whole numbers, right, male or female.", 'start': 10213.674, 'duration': 5.901}, {'end': 10221.355, 'text': 'So I go click list.', 'start': 10220.055, 'duration': 1.3}, {'end': 10228.757, 'text': 'In the source, I type in male and comma females.', 'start': 10222.336, 'duration': 6.421}, {'end': 10232.696, 'text': 'OK, there you go again.', 'start': 10230.675, 'duration': 2.021}, {'end': 10237.758, 'text': "I'm going to use input message data validation.", 'start': 10232.956, 'duration': 4.802}, {'end': 10240.58, 'text': 'Please select.', 'start': 10239.419, 'duration': 1.161}, {'end': 10243.961, 'text': 'From the drop down.', 'start': 10242.14, 'duration': 1.821}], 'summary': 'Setting up data validation for gender selection, specifying male and female as options.', 'duration': 38.709, 'max_score': 10205.252, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc10205252.jpg'}], 'start': 9122.155, 'title': 'Data validation in excel', 'summary': 'Covers data validation in excel, explaining techniques such as restricting data to predefined items, numbers within a specific range, time frames, text character limits, and formula-based validation. it also discusses error handling, creating drop-down lists, and utilizing data validation for database storage.', 'chapters': [{'end': 9678.996, 'start': 9122.155, 'title': 'Excel chapter 5: data validation', 'summary': 'Covers data validation in excel, including its definition, benefits, and practical examples, and demonstrates how to use data validation to restrict entries and create drop-down lists, enhancing user input accuracy and data management.', 'duration': 556.841, 'highlights': ['Data validation in Excel allows users to restrict certain values or allow users to pick up those values which are restricted, preventing errors and ensuring accurate data input.', 'Data validation can be used to create input messages and error alerts, enhancing user experience and preventing incorrect data entry.', 'The demonstration illustrates how data validation can be used to create drop-down lists for fields such as arrival time, ensuring accurate and restricted data input.']}, {'end': 9887.341, 'start': 9678.996, 'title': 'Data validation techniques', 'summary': 'Explains the various types of data validation, including restricting data to predefined items, numbers within a specific range, time frames, text character limits, and formula-based validation, and provides a practical exercise for understanding these concepts.', 'duration': 208.345, 'highlights': ['The chapter explains the various types of data validation, including restricting data to predefined items, numbers within a specific range, time frames, text character limits, and formula-based validation.', "For example, the speaker demonstrates restricting data to predefined items by using 'veg' or 'non-veg' options, and restricting numbers to a specific range from 1 to 20.", 'The presentation also mentions the ability to restrict text character limits, such as limiting the text input in a birthday form, and validating data based on formulas or values in other cells.', 'Additionally, the speaker highlights the practical exercise provided for understanding different types of data validation and how they can be applied in action.']}, {'end': 10306.723, 'start': 9887.341, 'title': 'Data validation and error handling', 'summary': 'Discusses setting up data validation for accepting whole numbers between 1 to 10 and creating proper input and error messages, demonstrating the process and its functionality with examples.', 'duration': 419.382, 'highlights': ['Demonstrating setting up data validation for accepting numbers between 1 to 10 and creating proper input and error messages.', 'Explanation of the input message and error alert for data validation, emphasizing the importance of proper user guidance and error handling.', "Illustration of setting up data validation for selecting 'male' or 'female' options, and creating appropriate input message and error alert."]}, {'end': 10509.552, 'start': 10306.783, 'title': 'Data validation using list', 'summary': 'Explains how to use data validation for creating drop-down lists, referencing source data from another column, and the benefits of leaving empty cells for future modifications.', 'duration': 202.769, 'highlights': ['The chapter shows how to use data validation to create a drop-down list for selecting values, such as male and female, and how to reference source data from another column, such as colors, simplifying the process.', 'The speaker emphasizes the benefits of leaving empty cells when referencing source data, enabling easier modifications in the future without redoing all the validations.', 'The explanation includes the process of using data validation settings, selecting source data from another column, and the use of absolute referencing with the $ symbol for data validation.', 'The chapter also highlights the immediate impact of modifications in the source data on the drop-down list, demonstrating the dynamic nature of the data validation process.']}, {'end': 10940.746, 'start': 10509.552, 'title': 'Data validation in excel', 'summary': 'Covers the concept of data validation in excel, including restricting text length, using inbuilt functions for validation, creating custom formulas, and utilizing data validation for database storage. it also includes a practical exercise on creating data validation for different fields.', 'duration': 431.194, 'highlights': ['The chapter covers the concept of data validation in Excel, including restricting text length, using inbuilt functions for validation, creating custom formulas, and utilizing data validation for database storage.', 'It also includes a practical exercise on creating data validation for different fields.']}], 'duration': 1818.591, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc9122155.jpg', 'highlights': ['Data validation in Excel allows users to restrict certain values or allow users to pick up those values which are restricted, preventing errors and ensuring accurate data input.', 'The chapter explains the various types of data validation, including restricting data to predefined items, numbers within a specific range, time frames, text character limits, and formula-based validation.', 'The chapter shows how to use data validation to create a drop-down list for selecting values, such as male and female, and how to reference source data from another column, such as colors, simplifying the process.', 'Demonstrating setting up data validation for accepting numbers between 1 to 10 and creating proper input and error messages.', 'The chapter covers the concept of data validation in Excel, including restricting text length, using inbuilt functions for validation, creating custom formulas, and utilizing data validation for database storage.']}, {'end': 13350.214, 'segs': [{'end': 11029.987, 'src': 'embed', 'start': 11001.737, 'weight': 2, 'content': [{'end': 11011.664, 'text': 'If you recollect from your previous chapters of Excel, you would have understood that formatting is extremely important to make Excel look good,', 'start': 11001.737, 'duration': 9.927}, {'end': 11020.351, 'text': 'right?. A simple example, which I was asking you to do earlier in text function was how do you highlight a particular cell,', 'start': 11011.664, 'duration': 8.687}, {'end': 11024.644, 'text': 'or how do you ensure that this particular cell is bold and so on.', 'start': 11020.351, 'duration': 4.293}, {'end': 11029.987, 'text': 'so imagine if you have a set of data just like what I have.', 'start': 11024.644, 'duration': 5.343}], 'summary': 'Formatting is crucial in excel to enhance appearance and readability of data.', 'duration': 28.25, 'max_score': 11001.737, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc11001737.jpg'}, {'end': 11303.437, 'src': 'embed', 'start': 11275.678, 'weight': 0, 'content': [{'end': 11280.579, 'text': 'Under the Home tab, I have in the styles, I have something called conditional formatting.', 'start': 11275.678, 'duration': 4.901}, {'end': 11290.021, 'text': "Again, if you recollect your initial chapters, I said this ribbon's similar formats of tabs are grouped together.", 'start': 11281.139, 'duration': 8.882}, {'end': 11297.623, 'text': 'So in this styles, you can see that in this particular ribbon, you have conditional formatting, format as table, and cell styles.', 'start': 11290.141, 'duration': 7.482}, {'end': 11300.023, 'text': 'For now, we are going to use conditional formatting.', 'start': 11298.003, 'duration': 2.02}, {'end': 11303.437, 'text': 'So I click on condition formatting, selecting the cells.', 'start': 11300.615, 'duration': 2.822}], 'summary': 'Using conditional formatting under home tab for cell selection.', 'duration': 27.759, 'max_score': 11275.678, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc11275678.jpg'}, {'end': 12383.176, 'src': 'embed', 'start': 12337.84, 'weight': 3, 'content': [{'end': 12339.54, 'text': 'It would be the same exercise.', 'start': 12337.84, 'duration': 1.7}, {'end': 12341.381, 'text': 'Conditional formatting.', 'start': 12340.341, 'duration': 1.04}, {'end': 12343.382, 'text': 'Highlight cell rules.', 'start': 12342.182, 'duration': 1.2}, {'end': 12345.343, 'text': 'Less than.', 'start': 12344.703, 'duration': 0.64}, {'end': 12354.695, 'text': 'First Me.', 'start': 12349.571, 'duration': 5.124}, {'end': 12359.098, 'text': 'And Custom format.', 'start': 12356.536, 'duration': 2.562}, {'end': 12361.16, 'text': 'Make it right.', 'start': 12360.399, 'duration': 0.761}, {'end': 12363.642, 'text': 'Click OK.', 'start': 12362.461, 'duration': 1.181}, {'end': 12368.305, 'text': 'And Click OK.', 'start': 12365.903, 'duration': 2.402}, {'end': 12369.686, 'text': 'So here you go.', 'start': 12369.066, 'duration': 0.62}, {'end': 12375.611, 'text': 'I have all the three conditional formatting applied to this particular cell ranges.', 'start': 12370.247, 'duration': 5.364}, {'end': 12383.176, 'text': 'And the beauty of conditional formatting is According to the dates, the conditional formatting would apply.', 'start': 12376.291, 'duration': 6.885}], 'summary': 'Three conditional formatting rules applied to cell ranges based on dates', 'duration': 45.336, 'max_score': 12337.84, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc12337840.jpg'}, {'end': 12755.954, 'src': 'embed', 'start': 12726.04, 'weight': 1, 'content': [{'end': 12733.002, 'text': 'I have data bars basically showcasing this particular numbers in data bars.', 'start': 12726.04, 'duration': 6.962}, {'end': 12737.681, 'text': 'So 74660 is represented by data bar.', 'start': 12733.698, 'duration': 3.983}, {'end': 12746.547, 'text': "Now still maybe it doesn't make any sense, but I just want to go ahead and modify this formula by going to conditional formatting, manage rules.", 'start': 12738.301, 'duration': 8.246}, {'end': 12755.954, 'text': 'So in this data bar, I double click and I open up the edit formatting rule and I let the color, etc.', 'start': 12747.328, 'duration': 8.626}], 'summary': 'Data bars represent 74660, modified through conditional formatting.', 'duration': 29.914, 'max_score': 12726.04, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc12726040.jpg'}, {'end': 12910.709, 'src': 'embed', 'start': 12882.742, 'weight': 4, 'content': [{'end': 12889.284, 'text': "So I'm selecting the data again, going to condition formatting, clicking on color scales.", 'start': 12882.742, 'duration': 6.542}, {'end': 12891.905, 'text': 'I have different types of color scales.', 'start': 12890.204, 'duration': 1.701}, {'end': 12900.327, 'text': 'Here I have color scales overlapped into those data bars, different set of color scales.', 'start': 12892.865, 'duration': 7.462}, {'end': 12906.649, 'text': "So using this, I'm using two condition formatting out here.", 'start': 12903.588, 'duration': 3.061}, {'end': 12910.709, 'text': 'So again, this is more like a graphical representation.', 'start': 12907.287, 'duration': 3.422}], 'summary': 'Using two condition formatting for graphical representation.', 'duration': 27.967, 'max_score': 12882.742, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc12882742.jpg'}, {'end': 13079.126, 'src': 'embed', 'start': 13048.251, 'weight': 5, 'content': [{'end': 13057.613, 'text': 'Moving on to another way by which I use condition formatting, and one of the most effective ways is what I would say is this particular exercise.', 'start': 13048.251, 'duration': 9.362}, {'end': 13065.054, 'text': 'To explain this, let me actually do a particular table so that it helps you.', 'start': 13059.213, 'duration': 5.841}, {'end': 13079.126, 'text': 'Now here I have randomly picked up 10 team members of my team who work, and then I have the data of their productivity.', 'start': 13066.835, 'duration': 12.291}], 'summary': "Using conditional formatting for a table of 10 team members' productivity data.", 'duration': 30.875, 'max_score': 13048.251, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc13048251.jpg'}], 'start': 10940.746, 'title': 'Conditional formatting in excel', 'summary': 'Covers conditional formatting basics, modifying and clearing rules, data analysis, and productivity tracking in excel. it includes predefined functions, custom color schemes, between rule, date ranges, and practical examples.', 'chapters': [{'end': 11564.629, 'start': 10940.746, 'title': 'Conditional formatting exercise', 'summary': 'Explains conditional formatting in excel, including the use of predefined functions to format cell ranges and the ability to create custom color schemes. it also demonstrates the process of referencing start and end values for conditional formatting.', 'duration': 623.883, 'highlights': ['The chapter explains conditional formatting in Excel, including the use of predefined functions to format cell ranges.', 'The ability to create custom color schemes is demonstrated.', 'The process of referencing start and end values for conditional formatting is explained.']}, {'end': 11806.349, 'start': 11566.31, 'title': 'Excel conditional formatting basics', 'summary': "Explains the basics of conditional formatting in excel, including how to use the 'between' rule, creating custom formats, and practical applications with examples of managing store items and team tasks.", 'duration': 240.039, 'highlights': ["Using the 'between' rule for conditional formatting", 'Practical applications of conditional formatting', 'Utilizing referencing and absolute function for modifying conditional formatting']}, {'end': 12036.683, 'start': 11806.349, 'title': 'Conditional formatting: modify and clear rules', 'summary': 'Explains how to use conditional formatting to highlight cells with data less than the expected productivity, then demonstrates how to modify and clear the formatting rules, providing step-by-step instructions for both processes.', 'duration': 230.334, 'highlights': ['The chapter highlights the process of using conditional formatting to highlight cells with data less than the expected productivity, such as entering only 6 tasks instead of the expected 10 reports.', 'It provides a detailed explanation of how to modify the conditional formatting rules, including changing the format of specific cells by double-clicking on the rule and selecting the desired changes.', 'Furthermore, the chapter explains the process of clearing the conditional formatting rules, offering the option to clear rules from selected cells or the entire sheet, providing a comprehensive guide to managing the formatting rules effectively.']}, {'end': 12819.121, 'start': 12038.024, 'title': 'Conditional formatting in excel', 'summary': 'Discusses how to apply conditional formatting in excel to highlight cells based on specific conditions, such as date ranges and numerical values, showcasing the impact of conditional formatting through practical examples and data visualization using data bars.', 'duration': 781.097, 'highlights': ['The chapter explains how to define and apply conditional formatting in Excel to highlight cells based on specific date conditions, such as greater than 1st September 07, between 1st May 07 to 1st September 07, and less than 1st May 07, showcasing the practical application of conditional formatting with clear examples.', 'The chapter demonstrates the use of data bars in conditional formatting to visually represent numerical data, allowing for enhanced data visualization and inference-making, as showcased through the example of representing sales data for different items on specific days of the week.', 'The chapter showcases how conditional formatting dynamically updates cell formatting based on changes in data, exemplified by the use of RANDBETWEEN function to play with numbers and observe the color changes in the highlighted cells, demonstrating the dynamic nature of conditional formatting.']}, {'end': 13047.632, 'start': 12819.585, 'title': 'Using conditional formatting for data analysis', 'summary': 'Explains how to use conditional formatting for data analysis, showcasing the use of color scales and data bars to represent climate data, with the ability to customize color ranges and values, providing a graphical representation for easier analysis of large data sets.', 'duration': 228.047, 'highlights': ['Using color scales and data bars to represent climate data', 'Customizing color ranges and values', 'Importance of conditional formatting in analyzing large data sets']}, {'end': 13350.214, 'start': 13048.251, 'title': 'Using conditional formatting for productivity tracking', 'summary': 'Discusses using conditional formatting to track team productivity, showcasing a table with productivity data and explaining how to use icon sets in conditional formatting to identify team members meeting or not meeting productivity targets.', 'duration': 301.963, 'highlights': ['Explaining the table showcasing productivity data and the use of conditional formatting.', 'Demonstrating the use of icon sets in conditional formatting to identify productivity targets.', 'Discussing the shared data entry and exclusive access to the weekly report.']}], 'duration': 2409.468, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc10940746.jpg', 'highlights': ['The chapter explains conditional formatting in Excel, including the use of predefined functions to format cell ranges.', 'The chapter demonstrates the use of data bars in conditional formatting to visually represent numerical data, allowing for enhanced data visualization and inference-making.', 'The chapter explains how to define and apply conditional formatting in Excel to highlight cells based on specific date conditions, showcasing the practical application of conditional formatting with clear examples.', 'The chapter highlights the process of using conditional formatting to highlight cells with data less than the expected productivity, providing a comprehensive guide to managing the formatting rules effectively.', 'Using color scales and data bars to represent climate data, and customizing color ranges and values.', 'Explaining the table showcasing productivity data and the use of conditional formatting, and demonstrating the use of icon sets in conditional formatting to identify productivity targets.']}, {'end': 14732.145, 'segs': [{'end': 13423.73, 'src': 'embed', 'start': 13377.029, 'weight': 1, 'content': [{'end': 13382.572, 'text': 'see there, as soon as he started achieving the targets, it moves into green.', 'start': 13377.029, 'duration': 5.543}, {'end': 13388.182, 'text': 'so this is another usage of conditional formatting doing reports.', 'start': 13382.572, 'duration': 5.61}, {'end': 13392.625, 'text': 'so here what i did is i used multiple things which i learned previously.', 'start': 13388.182, 'duration': 4.443}, {'end': 13394.767, 'text': 'i created a table,', 'start': 13392.625, 'duration': 2.142}, {'end': 13412.379, 'text': 'i used simple arithmetic functions to get this particular formula and then i used conditional formatting to showcase the way i want the result to be displayed so that i can inference out of it and hold people accountable basis for their doing work.', 'start': 13394.767, 'duration': 17.612}, {'end': 13415.681, 'text': 'okay, so this is how conditional formatting can be used.', 'start': 13412.379, 'duration': 3.302}, {'end': 13423.73, 'text': 'so recollecting simple conditions where the cells are highlighted based on the criteria.', 'start': 13415.681, 'duration': 8.049}], 'summary': 'Using conditional formatting, achieved targets moved report to green, showcasing accountability.', 'duration': 46.701, 'max_score': 13377.029, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc13377029.jpg'}, {'end': 13736.141, 'src': 'embed', 'start': 13704.437, 'weight': 0, 'content': [{'end': 13709.4, 'text': 'Under your data tab, you have this sort and filter ribbon.', 'start': 13704.437, 'duration': 4.963}, {'end': 13717.59, 'text': 'So here itself you can see it has sort ascending to descending, descending to ascending or largest to smallest.', 'start': 13710.185, 'duration': 7.405}, {'end': 13719.811, 'text': 'So it has already inbuilt function.', 'start': 13717.89, 'duration': 1.921}, {'end': 13727.135, 'text': 'Just reminding you that Excel is all about inbuilt functions which we need to know to use.', 'start': 13720.431, 'duration': 6.704}, {'end': 13736.141, 'text': 'So here we have an inbuilt function already in terms of sorting it either by largest to smallest or ascending to descending which I want to use.', 'start': 13727.596, 'duration': 8.545}], 'summary': 'Excel has inbuilt functions for sorting data by largest to smallest or ascending to descending.', 'duration': 31.704, 'max_score': 13704.437, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc13704437.jpg'}, {'end': 14437.26, 'src': 'embed', 'start': 14411.646, 'weight': 3, 'content': [{'end': 14418.649, 'text': "Notice, in this case, we will not have expand to sort or continue because Excel will understand that you're sorting the complete data.", 'start': 14411.646, 'duration': 7.003}, {'end': 14425.811, 'text': 'OK, so the beauty of using this advanced sort is it has sorted out the dates.', 'start': 14420.109, 'duration': 5.702}, {'end': 14427.812, 'text': "If you notice, it's Jan 11, 12, and so on.", 'start': 14426.051, 'duration': 1.761}, {'end': 14437.26, 'text': 'And then also if you see it is sorted out with respect to this, what is the total sales? So Jan 11th, the maximum sales.', 'start': 14429.175, 'duration': 8.085}], 'summary': "Excel's advanced sort sorted dates, showing max sales on jan 11th.", 'duration': 25.614, 'max_score': 14411.646, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc14411646.jpg'}, {'end': 14654.458, 'src': 'embed', 'start': 14615.152, 'weight': 5, 'content': [{'end': 14622.796, 'text': 'Select the data, click on sort for advanced sort, which is using multiple criterias, if not sort by a particular column.', 'start': 14615.152, 'duration': 7.644}, {'end': 14629.12, 'text': 'So the last thing in sorting which I want to showcase to you is, let me first delete all these levels.', 'start': 14623.637, 'duration': 5.483}, {'end': 14642.549, 'text': 'Okay, Select all the columns again, click on sort.', 'start': 14631.301, 'duration': 11.248}, {'end': 14654.458, 'text': 'so in sort, I just want to see how many sales of laptop is done but and the total quantity of laptops sold.', 'start': 14642.549, 'duration': 11.909}], 'summary': 'Demonstrating advanced sorting with multiple criteria and sales of laptops and total quantity sold.', 'duration': 39.306, 'max_score': 14615.152, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc14615152.jpg'}], 'start': 13350.214, 'title': 'Advanced sorting in spreadsheets', 'summary': 'Covers conditional formatting, sorting, and advanced sorting in spreadsheets. it explains using arithmetic functions and sorting data by date, quantity, items, and shop name, showcasing real-time changes and achieving maximum sales.', 'chapters': [{'end': 13524.241, 'start': 13350.214, 'title': 'Conditional formatting and sorting in spreadsheets', 'summary': 'Discusses the usage of conditional formatting to showcase real-time changes in achieving targets, using arithmetic functions and conditional formatting for accountability, and explains sorting and filtering in spreadsheets.', 'duration': 174.027, 'highlights': ['Conditional formatting used to showcase real-time changes in achieving targets', 'Usage of arithmetic functions and conditional formatting for accountability', 'Explanation of sorting and filtering in spreadsheets']}, {'end': 14154.669, 'start': 13524.661, 'title': 'Sorting data for effective analysis', 'summary': "Explains the concept of sorting data for effective analysis, showcasing how to sort data by quantity, date, items, shop name, and total sales, using excel's inbuilt functions, with a detailed demonstration of the process and its implications.", 'duration': 630.008, 'highlights': ["Sorting data by quantity, date, items, shop name, and total sales using Excel's inbuilt functions", 'Detailed demonstration of sorting process and implications', 'Importance of selecting data and giving instructions to Excel for sorting']}, {'end': 14476.98, 'start': 14154.669, 'title': 'Advanced sorting in excel', 'summary': 'Discusses using advanced sorting in excel to sort data based on multiple criteria, such as sorting by date and quantity to find the maximum quantity sold on a specific date, demonstrating the use of an advanced sort functionality to achieve this.', 'duration': 322.311, 'highlights': ['Excel has a wizard that provides multiple options for sorting data based on different criteria, making it easy to understand and implement complex sorting operations.', 'Excel automatically understands the data being sorted and applies appropriate sorting orders, such as sorting dates from oldest to newest and total sales from largest to smallest, based on the selected criteria.', 'The advanced sort functionality in Excel efficiently sorts data based on multiple criteria, enabling users to analyze and compare data sets effectively.']}, {'end': 14732.145, 'start': 14477, 'title': 'Advanced sorting for analysis', 'summary': 'Demonstrates using advanced sorting in excel to analyze data by sorting based on quantity, shop names, and items to identify the highest-selling shop and items.', 'duration': 255.145, 'highlights': ['The process of advanced sorting in Excel involves selecting the complete list of data and using multiple criteria for sorting.', 'The user intends to analyze which shop is selling the highest quantity of goods by sorting the data based on quantity and shop names.', 'The user showcases the process of sorting to identify the highest-selling items, such as laptops, by using multiple levels of sorting based on item and quantity.']}], 'duration': 1381.931, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc13350214.jpg', 'highlights': ["Sorting data by quantity, date, items, shop name, and total sales using Excel's inbuilt functions", 'Conditional formatting used to showcase real-time changes in achieving targets', 'Excel has a wizard that provides multiple options for sorting data based on different criteria', 'The advanced sort functionality in Excel efficiently sorts data based on multiple criteria', 'Usage of arithmetic functions and conditional formatting for accountability', 'The process of advanced sorting in Excel involves selecting the complete list of data and using multiple criteria for sorting']}, {'end': 16606.383, 'segs': [{'end': 14762.539, 'src': 'embed', 'start': 14733.607, 'weight': 4, 'content': [{'end': 14736.01, 'text': 'Easy? Yep.', 'start': 14733.607, 'duration': 2.403}, {'end': 14748.188, 'text': "So all that which I've been telling is pretty easy, provided you practice telling something that you need to keep practicing for you to remember.", 'start': 14736.29, 'duration': 11.898}, {'end': 14758.156, 'text': "if not, today it looks very easy, but then, when you come back and try to revisit this because you haven't practiced, you'll completely forget that.", 'start': 14748.188, 'duration': 9.968}, {'end': 14758.836, 'text': 'all right.', 'start': 14758.156, 'duration': 0.68}, {'end': 14762.539, 'text': 'so that completes our sorting exercise.', 'start': 14758.836, 'duration': 3.703}], 'summary': 'Practice makes retention easy; consistent practice is crucial for memory retention.', 'duration': 28.932, 'max_score': 14733.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc14733607.jpg'}, {'end': 14853.742, 'src': 'embed', 'start': 14819.062, 'weight': 0, 'content': [{'end': 14832.428, 'text': "Okay, let's imagine that you are in a census department, basically taking care of all data for our country.", 'start': 14819.062, 'duration': 13.366}, {'end': 14842.08, 'text': 'And you want to understand how much population is there in a particular locality.', 'start': 14833.778, 'duration': 8.302}, {'end': 14846.48, 'text': "OK, so whatever locality you're staying, we can take that as an example.", 'start': 14842.76, 'duration': 3.72}, {'end': 14851.181, 'text': 'Alright, so I will just keep in mind a particular place.', 'start': 14846.5, 'duration': 4.681}, {'end': 14853.742, 'text': "Let's take Pune an example and explain this to you.", 'start': 14851.321, 'duration': 2.421}], 'summary': 'Explanation of gathering population data for a locality, using pune as an example.', 'duration': 34.68, 'max_score': 14819.062, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc14819062.jpg'}, {'end': 15603.536, 'src': 'embed', 'start': 15568.032, 'weight': 1, 'content': [{'end': 15571.754, 'text': "Yeah, so I'm just ensuring that I'm applying the filter for the first time.", 'start': 15568.032, 'duration': 3.722}, {'end': 15578.057, 'text': 'So the first criteria, what is my first criteria? Show sales of Mega Mart.', 'start': 15572.834, 'duration': 5.223}, {'end': 15578.818, 'text': 'Very simple.', 'start': 15578.237, 'duration': 0.581}, {'end': 15580.599, 'text': 'This should not be as much of an issue.', 'start': 15578.838, 'duration': 1.761}, {'end': 15583.22, 'text': 'So show sales of Mega Mart.', 'start': 15581.199, 'duration': 2.021}, {'end': 15585.501, 'text': 'So Mega Mart is my customer.', 'start': 15584.101, 'duration': 1.4}, {'end': 15587.703, 'text': 'So I go drop down to customer.', 'start': 15585.541, 'duration': 2.162}, {'end': 15591.825, 'text': 'Under that customer, I only select Mega Mart.', 'start': 15588.403, 'duration': 3.422}, {'end': 15593.85, 'text': 'Okay And click.', 'start': 15593.029, 'duration': 0.821}, {'end': 15603.536, 'text': 'Okay So you can see that I have the complete sale of Mega Mart for all the products, the total amount and the respective dates.', 'start': 15593.97, 'duration': 9.566}], 'summary': 'Setting filter for mega mart sales, ensuring accuracy and completeness.', 'duration': 35.504, 'max_score': 15568.032, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc15568032.jpg'}, {'end': 16013.171, 'src': 'embed', 'start': 15979.827, 'weight': 2, 'content': [{'end': 15988.59, 'text': "So that's not what I wanted, right? I wanted exclusively Mega Mart sales for cookies and Mini Mart sales for milk.", 'start': 15979.827, 'duration': 8.763}, {'end': 16001.774, 'text': 'So how do I do this? Any thoughts? So should be something, right? Yes, there is, of course, an advanced sort.', 'start': 15989.35, 'duration': 12.424}, {'end': 16004.034, 'text': 'That is what we are going to learn.', 'start': 16002.434, 'duration': 1.6}, {'end': 16013.171, 'text': 'So how do I do advanced sort? So to get this, I first have to build my criteria or my condition.', 'start': 16004.595, 'duration': 8.576}], 'summary': 'Learn advanced sorting for exclusive mega mart cookie sales and mini mart milk sales.', 'duration': 33.344, 'max_score': 15979.827, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc15979827.jpg'}, {'end': 16524.082, 'src': 'embed', 'start': 16496.714, 'weight': 3, 'content': [{'end': 16505.096, 'text': 'Okay, so unique records only are copied to another location is the filter data will be copied to another location.', 'start': 16496.714, 'duration': 8.382}, {'end': 16511.456, 'text': 'So for example, the filter data of this particular criteria would be copied out here.', 'start': 16505.116, 'duration': 6.34}, {'end': 16513.437, 'text': 'Just check this.', 'start': 16512.738, 'duration': 0.699}, {'end': 16524.082, 'text': "So it's basically exporting the data of all that you filtered and moving that to this particular place, wherever you want to move.", 'start': 16516.158, 'duration': 7.924}], 'summary': 'Export unique filtered records to another location.', 'duration': 27.368, 'max_score': 16496.714, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc16496714.jpg'}], 'start': 14733.607, 'title': 'Data filtering in excel', 'summary': 'Covers the ease of data sorting and filtering, the process of filtering data for population analysis, an excel data filtering tutorial, and applying advanced filter in excel, offering practical examples and emphasizing the importance of practice for retention.', 'chapters': [{'end': 14816.321, 'start': 14733.607, 'title': 'Easy data sorting and filtering', 'summary': 'Discusses the ease of data sorting and filtering, emphasizing the importance of practice for retention, introducing the concept of filtering data akin to using control-f, and hinting at the advanced nature of the topic.', 'duration': 82.714, 'highlights': ["The importance of practice for retaining knowledge is emphasized, highlighting the need for consistent practice to prevent forgetting (e.g., 'provided you practice telling something that you need to keep practicing for you to remember').", "The concept of filtering data is introduced and likened to using Control-F, providing a simple terminology explanation (e.g., 'Filtering data is nothing but something similar to Control-F').", "The upcoming topic of filtering data is hinted to be more advanced, indicating a deeper exploration in the upcoming exercises (e.g., 'This filtering data is a little advanced')."]}, {'end': 15509.076, 'start': 14819.062, 'title': 'Filtering data for population analysis', 'summary': 'Explains the process of filtering data in a census department to analyze population, using the example of finding the number of people in a locality like pune, and then demonstrates the application of filtering in a spreadsheet to analyze sales data for specific products and conditions.', 'duration': 690.014, 'highlights': ['Filtering data in a census department involves breaking down the complete population data at the country level into states, cities, localities, and further layers to efficiently analyze specific population details.', 'The demonstration of filtering data in a spreadsheet showcases the application of filtering to analyze sales data for specific products and conditions, such as finding the sales of cookies for Mega Mart in a specific date range and for a specific price range.', 'The speaker emphasizes the ability to efficiently apply multiple layers of filtering in the census department, as well as in the spreadsheet, to analyze complex data and obtain specific details like the number of street lamps in a street or sales of specific products under different conditions.']}, {'end': 16103.943, 'start': 15509.076, 'title': 'Excel data filtering tutorial', 'summary': 'Demonstrates how to apply various data filters in excel, including filtering by specific criteria such as customer, product, and sales amount, with practical examples and step-by-step instructions.', 'duration': 594.867, 'highlights': ['The tutorial explains how to filter sales data by specific criteria, such as customer and product, using practical examples and step-by-step instructions.', 'The demonstration includes filtering sales data between $1,000 to $1,500, showcasing the process of applying number filters and specifying the desired sales range.', 'The tutorial provides guidance on filtering sales data above a specific amount, exemplifying the process of applying number filters for values greater than $1,000.', 'The chapter explains the advanced sorting technique for filtering exclusive sales data, using customer and product criteria with meticulous step-by-step instructions.']}, {'end': 16606.383, 'start': 16104.764, 'title': 'Applying advanced filter in excel', 'summary': 'Demonstrates how to apply advanced filters in excel to extract specific data based on criteria ranges, and provides a step-by-step solution to a filtering problem, ensuring the criteria is correctly applied.', 'duration': 501.619, 'highlights': ['The chapter explains how to correctly select and apply criteria ranges for advanced filtering in Excel, ensuring data is copied and criteria is accurately defined, preventing errors and ensuring the filter works effectively.', 'The instructor provides a step-by-step solution to a filtering problem, emphasizing the need to accurately define criteria for customer, product, and total, and demonstrates the process of applying advanced filters to extract specific data based on the defined criteria.', 'The chapter demonstrates the feature of copying filtered data to another location, explaining how the advanced filter can export the filtered data to a specified location and remove duplicates to display unique records only.']}], 'duration': 1872.776, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc14733607.jpg', 'highlights': ['Filtering data in a census department involves breaking down the complete population data at the country level into states, cities, localities, and further layers to efficiently analyze specific population details.', 'The tutorial explains how to filter sales data by specific criteria, such as customer and product, using practical examples and step-by-step instructions.', 'The chapter explains the advanced sorting technique for filtering exclusive sales data, using customer and product criteria with meticulous step-by-step instructions.', 'The chapter demonstrates the feature of copying filtered data to another location, explaining how the advanced filter can export the filtered data to a specified location and remove duplicates to display unique records only.', "The importance of practice for retaining knowledge is emphasized, highlighting the need for consistent practice to prevent forgetting (e.g., 'provided you practice telling something that you need to keep practicing for you to remember')."]}, {'end': 20674.223, 'segs': [{'end': 16668.241, 'src': 'embed', 'start': 16635.045, 'weight': 0, 'content': [{'end': 16641.907, 'text': "Let's say you have sales of two particular products month on month.", 'start': 16635.045, 'duration': 6.862}, {'end': 16652.651, 'text': 'so you have one sales of cars and you also have sales of bikes, and you have this data for, say, 12 months.', 'start': 16641.907, 'duration': 10.744}, {'end': 16661.717, 'text': 'so you basically want to see in a chart how a car sale is and a bike sale is every month for the 12 months.', 'start': 16652.651, 'duration': 9.066}, {'end': 16664.378, 'text': "that's when you use a column chart.", 'start': 16662.315, 'duration': 2.063}, {'end': 16668.241, 'text': 'it effectively shows a comparison of one or more series.', 'start': 16664.378, 'duration': 3.863}], 'summary': 'Analyze 12 months of car and bike sales using a column chart for comparison.', 'duration': 33.196, 'max_score': 16635.045, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc16635044.jpg'}, {'end': 17364.717, 'src': 'embed', 'start': 17338.189, 'weight': 1, 'content': [{'end': 17343.95, 'text': 'So basically anything to do with the design is the reflecting in the first particular tab.', 'start': 17338.189, 'duration': 5.761}, {'end': 17349.531, 'text': "Now if I go to the layout, it's basically to do with all the layouting of that particular chart.", 'start': 17344.43, 'duration': 5.101}, {'end': 17355.233, 'text': 'Which means I can insert shapes, format my selection.', 'start': 17350.832, 'duration': 4.401}, {'end': 17364.717, 'text': 'I can change the selection if I want to insert a text box and make it something unique chart name and all that, and Anything to do with labels.', 'start': 17355.393, 'duration': 9.324}], 'summary': 'The design tab reflects design elements, while the layout tab is for chart layout and labeling.', 'duration': 26.528, 'max_score': 17338.189, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc17338189.jpg'}, {'end': 18283.44, 'src': 'embed', 'start': 18249.694, 'weight': 3, 'content': [{'end': 18254.598, 'text': "I've just tried to put some colors and play around with it to get it in this particular format.", 'start': 18249.694, 'duration': 4.904}, {'end': 18263.413, 'text': 'Not just that, I can also format the way the The chart look and feel is so I could right click on this format chart area.', 'start': 18255.139, 'duration': 8.274}, {'end': 18266.354, 'text': 'I can make this a 3D format.', 'start': 18264.733, 'duration': 1.621}, {'end': 18274.717, 'text': 'Yeah, so this also becomes 3D format.', 'start': 18272.536, 'duration': 2.181}, {'end': 18280.719, 'text': "And when I actually copy this and I'll open a new PowerPoint presentation.", 'start': 18275.878, 'duration': 4.841}, {'end': 18283.44, 'text': 'I can paste this.', 'start': 18282.58, 'duration': 0.86}], 'summary': 'The speaker experimented with formatting a chart, making it 3d, and pasting it into a powerpoint presentation.', 'duration': 33.746, 'max_score': 18249.694, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc18249694.jpg'}, {'end': 19231.003, 'src': 'embed', 'start': 19190.875, 'weight': 5, 'content': [{'end': 19198.196, 'text': 'So I will use some light colors, solid fill and I will use some light colors such as this.', 'start': 19190.875, 'duration': 7.321}, {'end': 19201.748, 'text': 'make it a little bit even more lighter around.', 'start': 19199.406, 'duration': 2.342}, {'end': 19207.793, 'text': 'bluish okay, and let the trend line be the way it is.', 'start': 19201.748, 'duration': 6.045}, {'end': 19211.296, 'text': 'and here the fonts I will format.', 'start': 19207.793, 'duration': 3.503}, {'end': 19218.902, 'text': 'select the font and I want to make it bold.', 'start': 19211.296, 'duration': 7.606}, {'end': 19225.508, 'text': 'okay, and here as well I want to select the font and make it bold.', 'start': 19218.902, 'duration': 6.606}, {'end': 19231.003, 'text': 'And why just make the fonts bold?', 'start': 19228.903, 'duration': 2.1}], 'summary': 'Using light colors, solid fill, and bold fonts in the presentation.', 'duration': 40.128, 'max_score': 19190.875, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc19190875.jpg'}, {'end': 19348.183, 'src': 'embed', 'start': 19318.912, 'weight': 2, 'content': [{'end': 19321.053, 'text': 'we understood how to do a column chart.', 'start': 19318.912, 'duration': 2.141}, {'end': 19324.116, 'text': 'we understood the difference between column and line chart.', 'start': 19321.053, 'duration': 3.063}, {'end': 19329.621, 'text': "now let's move on to the third type of chart, which is a pie chart.", 'start': 19324.116, 'duration': 5.505}, {'end': 19335.667, 'text': "again, before I move on, I'll give a 10 second pause for you to work on this particular exercise.", 'start': 19329.621, 'duration': 6.046}, {'end': 19343.754, 'text': 'you have the data in front of you and you also have the details in the spreadsheet.', 'start': 19335.667, 'duration': 8.087}, {'end': 19348.183, 'text': 'So a 10 second pause for you to work on this particular exercise.', 'start': 19344.301, 'duration': 3.882}], 'summary': 'Learned about column and line charts, now moving on to pie chart. a 10-second pause given for exercise.', 'duration': 29.271, 'max_score': 19318.912, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc19318912.jpg'}, {'end': 19414.028, 'src': 'embed', 'start': 19385.406, 'weight': 4, 'content': [{'end': 19396.96, 'text': 'It basically is just taking one series of data and showcasing In that series the proportionality of how much a particular aspect of that series is.', 'start': 19385.406, 'duration': 11.554}, {'end': 19399.521, 'text': 'So, if I have to go by definition,', 'start': 19397.42, 'duration': 2.101}, {'end': 19406.804, 'text': 'it uses the series identifier in the chart title and displays the value of the series as proportional slices of a pie.', 'start': 19399.521, 'duration': 7.283}, {'end': 19414.028, 'text': 'The easiest way of me to explain this is through imagining a pizza.', 'start': 19408.125, 'duration': 5.903}], 'summary': 'Data visualization tool displays series values as proportional pie slices.', 'duration': 28.622, 'max_score': 19385.406, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc19385406.jpg'}], 'start': 16606.444, 'title': 'Excel chart creation and customization', 'summary': 'Covers creating and customizing column, line, and pie charts in excel, showcasing sales data, team productivity, and performance analysis. it emphasizes the use of chart wizard, toolbar, and various design options, and demonstrates protection and shared drives for data entry and calculation.', 'chapters': [{'end': 16797.263, 'start': 16606.444, 'title': 'Chapter 8: column charts', 'summary': 'Discusses column charts, which effectively compare multiple data series, showcasing sales of cars and bikes month on month for 12 months through a chart with numerical values on the y-axis and time or categories on the x-axis.', 'duration': 190.819, 'highlights': ['Column charts effectively compare multiple data series, showcasing sales of cars and bikes month on month for 12 months through a chart.', 'The vertical axis (y-axis) displays the numerical values, and the horizontal axis (x-axis) displays time or other categories.', 'The data for car and bike sales is added to a spreadsheet, with random values between 50 to 150 for cars and 40 to 100 for bikes for each month from January to December.']}, {'end': 17338.169, 'start': 16797.263, 'title': 'Creating simple column chart in excel', 'summary': "Discusses creating a simple column chart in excel using random data, and then moves on to creating a chart based on team members' productivity and quality, emphasizing the use of chart wizard, chart toolbar, and various design options.", 'duration': 540.906, 'highlights': ['The chapter discusses creating a simple column chart in Excel using random data', 'Emphasizes the use of chart wizard, chart toolbar, and various design options', "Moves on to creating a chart based on team members' productivity and quality"]}, {'end': 18249.134, 'start': 17338.189, 'title': 'Excel chart formatting', 'summary': 'Discusses excel chart formatting, including layout options, data labeling, plot area and series color customization, with a focus on creating an attractive and informative chart for team performance analysis.', 'duration': 910.945, 'highlights': ['The chapter covers layout options for design, layouting, labels, axis grid lines, plot area, and additional options, offering a comprehensive understanding of chart formatting.', 'The transcript provides step-by-step instructions for creating a column graph using the chart wizard, displaying productivity and quality data for team members, and customizing the chart title, grid lines, legend placement, and data labels.', 'Detailed guidance is given on changing the plot area and series colors, including the use of gradient fill, transparency adjustments, and 3D formatting to enhance the visual appeal of the chart.', 'The speaker demonstrates the flexibility of formatting options by showcasing the customization of individual data points, such as highlighting a specific column to emphasize low productivity for a team member.', "Instructions are provided on utilizing Excel's formatting tools, including design, layout, and format options, and the step-by-step process of customizing font colors, data series colors, and individual data points is thoroughly explained."]}, {'end': 19170.093, 'start': 18249.694, 'title': 'Excel chart formatting', 'summary': 'Demonstrates how to format and customize column and line charts in excel, highlighting the process of creating a 3d format chart, changing plot area and series colors, inserting images, and creating a line chart to display trends in sales data.', 'duration': 920.399, 'highlights': ['The chapter demonstrates the process of creating a 3D format chart and pasting it into a new PowerPoint presentation, emphasizing the ability to customize chart appearance.', 'The process of changing plot area and series colors is highlighted, showcasing the steps to use different chart tools to format the look and feel of the chart.', 'The demonstration includes the process of inserting images into the chart area and adjusting their positioning, emphasizing the ability to enhance visual appeal by incorporating images.', 'The chapter provides a detailed walkthrough of creating a line chart to display trends in sales data, illustrating the steps to select, format, and customize the appearance of the chart.']}, {'end': 19983.595, 'start': 19170.093, 'title': 'Line and pie chart presentation', 'summary': 'Covers the creation and customization of line and pie charts for presentation, including using light colors and bold fonts for a line chart and demonstrating the proportionality of data using 3d pie chart with manual color and segment selection.', 'duration': 813.502, 'highlights': ['Demonstrating the proportionality of data using 3D pie chart with manual color and segment selection', 'Using light colors and bold fonts for a line chart']}, {'end': 20674.223, 'start': 19983.595, 'title': 'Charts and spreadsheet demonstration', 'summary': 'Explains the creation of different types of charts including column, line, and pie charts, and also demonstrates the creation and referencing of a spreadsheet for team productivity tracking, with an emphasis on using protection and shared drives for data entry and calculation.', 'duration': 690.628, 'highlights': ['The chapter explains the creation of different types of charts including column, line, and pie charts, and also demonstrates the creation and referencing of a spreadsheet for team productivity tracking, with an emphasis on using protection and shared drives for data entry and calculation.', 'The demonstration includes creating and modifying a pie chart, converting data to percentages, modifying colors and gradients, and using chart tools.', 'Different types of charts such as bar chart, area chart, scattered chart, and surface chart are briefly reviewed and demonstrated, emphasizing the representation of data in various formats.', 'The chapter showcases the creation and referencing of a spreadsheet for team productivity tracking, with an emphasis on using protection and shared drives for data entry and calculation.', "The spreadsheet demonstration includes the creation of a table for team members' productivity report, the addition of task details, referencing data across sheets, and using conditional formatting.", 'The use of protection and shared drives for data entry and calculation in the spreadsheet is highlighted, with the addition of conditional formatting for better visualization of data.']}], 'duration': 4067.779, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc16606444.jpg', 'highlights': ['Column charts effectively compare multiple data series, showcasing sales of cars and bikes month on month for 12 months through a chart.', 'The chapter covers layout options for design, layouting, labels, axis grid lines, plot area, and additional options, offering a comprehensive understanding of chart formatting.', 'The chapter explains the creation of different types of charts including column, line, and pie charts, and also demonstrates the creation and referencing of a spreadsheet for team productivity tracking, with an emphasis on using protection and shared drives for data entry and calculation.', 'The chapter demonstrates the process of creating a 3D format chart and pasting it into a new PowerPoint presentation, emphasizing the ability to customize chart appearance.', 'Demonstrating the proportionality of data using 3D pie chart with manual color and segment selection', 'Using light colors and bold fonts for a line chart']}, {'end': 23002.031, 'segs': [{'end': 20696.514, 'src': 'embed', 'start': 20674.223, 'weight': 0, 'content': [{'end': 20684.087, 'text': "though I could use conditional formatting over here to give it conditions in such a way that it highlights the cell value if it doesn't matches or it goes below a particular number.", 'start': 20674.223, 'duration': 9.864}, {'end': 20685.328, 'text': "so I've created this.", 'start': 20684.087, 'duration': 1.241}, {'end': 20692.093, 'text': "so now what I'm going to do is I'm going to hide these particular sheets total, so they can't see.", 'start': 20685.328, 'duration': 6.765}, {'end': 20696.514, 'text': 'So how I do that is I go select that particular column which I need to hide.', 'start': 20692.493, 'duration': 4.021}], 'summary': 'Created conditional formatting to highlight cells not matching or going below a specific value, and hid specific sheets.', 'duration': 22.291, 'max_score': 20674.223, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc20674223.jpg'}, {'end': 20794.52, 'src': 'embed', 'start': 20762.12, 'weight': 13, 'content': [{'end': 20768.565, 'text': "And when you're saving the spreadsheet, you can see there is a tools option out here, right? So click on the tools option.", 'start': 20762.12, 'duration': 6.445}, {'end': 20774.829, 'text': 'So under the tools option, you have web option, map network drive, compress pictures, or general option.', 'start': 20769.085, 'duration': 5.744}, {'end': 20777.028, 'text': 'You get to click on general options.', 'start': 20775.387, 'duration': 1.641}, {'end': 20780.731, 'text': 'So as soon as you do click on general option, you have two things.', 'start': 20777.529, 'duration': 3.202}, {'end': 20785.494, 'text': 'One is to password to open this file, password to modify this file.', 'start': 20781.391, 'duration': 4.103}, {'end': 20794.52, 'text': 'So what this basically means is password to open means that every time you want to open this particular spreadsheet,', 'start': 20785.894, 'duration': 8.626}], 'summary': 'Explains how to set passwords in excel for opening and modifying files.', 'duration': 32.4, 'max_score': 20762.12, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc20762120.jpg'}, {'end': 20841.693, 'src': 'embed', 'start': 20799.904, 'weight': 5, 'content': [{'end': 20807.991, 'text': 'password to modify means that every time you want to modify the spreadsheet there needs to be a password so you could just make it read only.', 'start': 20799.904, 'duration': 8.087}, {'end': 20811.974, 'text': 'that means it can just open up but nothing.', 'start': 20807.991, 'duration': 3.983}, {'end': 20817.517, 'text': 'unless you put in the password to modify, it will not allow you to modify any content.', 'start': 20811.974, 'duration': 5.543}, {'end': 20820.087, 'text': "So for now, I'm going to use both options.", 'start': 20818.106, 'duration': 1.981}, {'end': 20822.407, 'text': 'So and read only recommendations.', 'start': 20820.567, 'duration': 1.84}, {'end': 20825.048, 'text': 'So you could see how everything comes into picture.', 'start': 20822.507, 'duration': 2.541}, {'end': 20828.369, 'text': 'Another small thing out here is always create backup.', 'start': 20825.588, 'duration': 2.781}, {'end': 20833.69, 'text': 'So this option means that Excel will keep saving the file periodically.', 'start': 20828.869, 'duration': 4.821}, {'end': 20838.992, 'text': 'So whatever file you have created, it will always have a backup in an Excel directory.', 'start': 20834.171, 'duration': 4.821}, {'end': 20841.693, 'text': 'So it will create backup, keep the backup.', 'start': 20839.392, 'duration': 2.301}], 'summary': 'Enabling password protection and creating periodic backups in excel to secure data and prevent unauthorized modifications.', 'duration': 41.789, 'max_score': 20799.904, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc20799904.jpg'}, {'end': 20894.054, 'src': 'embed', 'start': 20867.206, 'weight': 9, 'content': [{'end': 20871.409, 'text': 'But if it is a very critical data, I would suggest you create a backup.', 'start': 20867.206, 'duration': 4.203}, {'end': 20882.602, 'text': 'So another important thing out here is that Password to open and modify can be different, but if you forget that particular password,', 'start': 20871.929, 'duration': 10.673}, {'end': 20886.466, 'text': 'there is no way that Excel you can recover that particular password.', 'start': 20882.602, 'duration': 3.864}, {'end': 20894.054, 'text': 'So enter the password, which you remember, because if you lose that password, you will not be able to open this file.', 'start': 20886.966, 'duration': 7.088}], 'summary': 'Create a backup for critical data to avoid password loss in excel.', 'duration': 26.848, 'max_score': 20867.206, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc20867206.jpg'}, {'end': 21086.462, 'src': 'embed', 'start': 21051.519, 'weight': 10, 'content': [{'end': 21053.159, 'text': 'so I want to modify.', 'start': 21051.519, 'duration': 1.64}, {'end': 21055.78, 'text': 'so I will use the password again 111..', 'start': 21053.159, 'duration': 2.621}, {'end': 21066.184, 'text': "Okay so there's a small wizard telling that do you want to use this read only or do you want to edit it.", 'start': 21055.78, 'duration': 10.404}, {'end': 21071.518, 'text': 'So it is telling it should be open as read only unless you need to save changes open as read-only.', 'start': 21066.584, 'duration': 4.934}, {'end': 21077.72, 'text': "Yes, that means it will open again as read-only, but now I don't want, so I click on no, which means it is in an editable mode.", 'start': 21071.738, 'duration': 5.982}, {'end': 21086.462, 'text': "So that's how I've created a spreadsheet, password protected it, and only I can open it.", 'start': 21078.12, 'duration': 8.342}], 'summary': 'Created a password-protected spreadsheet, open as read-only, edited it, and only the creator can access it.', 'duration': 34.943, 'max_score': 21051.519, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc21051519.jpg'}, {'end': 21558.795, 'src': 'embed', 'start': 21533.437, 'weight': 1, 'content': [{'end': 21539.361, 'text': "Okay So that's basically what main things in protections are.", 'start': 21533.437, 'duration': 5.924}, {'end': 21541.623, 'text': 'One is to protect the worksheet.', 'start': 21539.882, 'duration': 1.741}, {'end': 21546.686, 'text': 'We saw by putting modify password and open password.', 'start': 21542.604, 'duration': 4.082}, {'end': 21551.59, 'text': 'Then we saw how to protect the workbook, that is protect structures and windows.', 'start': 21547.127, 'duration': 4.463}, {'end': 21558.795, 'text': 'And then we saw how do I protect the particular cells using the protect function.', 'start': 21552.15, 'duration': 6.645}], 'summary': 'Protection methods include worksheet, workbook, and cell protection using passwords and function.', 'duration': 25.358, 'max_score': 21533.437, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc21533437.jpg'}, {'end': 21678.439, 'src': 'embed', 'start': 21652.215, 'weight': 6, 'content': [{'end': 21660.941, 'text': 'All of users of this worksheet to format cells, format columns, insert columns, insert rows and so much else, which we can do by using protection.', 'start': 21652.215, 'duration': 8.726}, {'end': 21665.004, 'text': 'But mostly the protection is used only for these things.', 'start': 21661.381, 'duration': 3.623}, {'end': 21671.929, 'text': 'You could also use it to, I mean, you could protect users from inserting columns, rows, deleting columns, etc.', 'start': 21665.444, 'duration': 6.485}, {'end': 21674.911, 'text': "And that's how the protection comes in handy.", 'start': 21672.329, 'duration': 2.582}, {'end': 21678.439, 'text': 'allow users.', 'start': 21677.458, 'duration': 0.981}], 'summary': 'Users can format cells, columns, and rows; protection prevents unwanted changes.', 'duration': 26.224, 'max_score': 21652.215, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc21652215.jpg'}, {'end': 21847.642, 'src': 'embed', 'start': 21820.025, 'weight': 3, 'content': [{'end': 21823.446, 'text': "So when I've shared it in a particular shared drive, I could click on share workbook.", 'start': 21820.025, 'duration': 3.421}, {'end': 21828.229, 'text': 'So what happens is this particular workbook can be accessed by multiple people.', 'start': 21823.807, 'duration': 4.422}, {'end': 21831.81, 'text': "So here I have just put who's opened this.", 'start': 21828.749, 'duration': 3.061}, {'end': 21832.991, 'text': 'It is showing my number.', 'start': 21831.85, 'duration': 1.141}, {'end': 21835.76, 'text': "But that's how my number is reflecting on this computer.", 'start': 21833.479, 'duration': 2.281}, {'end': 21842.261, 'text': 'Otherwise I can go to advance before that allow changes by more than one user at the same time.', 'start': 21836.22, 'duration': 6.041}, {'end': 21843.581, 'text': 'This allows for group merging.', 'start': 21842.301, 'duration': 1.28}, {'end': 21845.142, 'text': "So I'll click on OK.", 'start': 21843.941, 'duration': 1.201}, {'end': 21847.642, 'text': 'So that means multiple people can access this.', 'start': 21845.522, 'duration': 2.12}], 'summary': 'Workbook shared with multiple users, enabling collaborative access and group merging.', 'duration': 27.617, 'max_score': 21820.025, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc21820025.jpg'}, {'end': 21909.922, 'src': 'embed', 'start': 21883.985, 'weight': 11, 'content': [{'end': 21890.167, 'text': 'Sharebook is effective just in case you want people to read and use this spreadsheet as read only.', 'start': 21883.985, 'duration': 6.182}, {'end': 21896.548, 'text': 'But then it generally lands up in more problem when you allow editing option and a share.', 'start': 21890.547, 'duration': 6.001}, {'end': 21905.019, 'text': 'So I would suggest not to give a share option to more people, because you have different ways of sending this data.', 'start': 21896.668, 'duration': 8.351}, {'end': 21909.922, 'text': 'you could send it through email and let them use the spreadsheet and work out of that,', 'start': 21905.019, 'duration': 4.903}], 'summary': "Sharebook is effective for read-only access, but issues arise with editing and sharing. it's better to limit sharing options and use email for collaboration.", 'duration': 25.937, 'max_score': 21883.985, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc21883985.jpg'}, {'end': 22161.419, 'src': 'embed', 'start': 22135.622, 'weight': 7, 'content': [{'end': 22140.005, 'text': 'Yeah So they can understand and click here if they need help.', 'start': 22135.622, 'duration': 4.383}, {'end': 22141.447, 'text': "So that's how you insert object.", 'start': 22140.025, 'duration': 1.422}, {'end': 22144.129, 'text': 'And in insert object you have multiple options.', 'start': 22141.967, 'duration': 2.162}, {'end': 22155.118, 'text': 'You could insert different types of objects such as Adobe, Office, graphs, some bitmap images and then some Word documents etc.', 'start': 22144.629, 'duration': 10.489}, {'end': 22157.478, 'text': "Yep, so that's how you insert object.", 'start': 22155.717, 'duration': 1.761}, {'end': 22161.419, 'text': 'The other interesting things out here is hyperlink.', 'start': 22158.158, 'duration': 3.261}], 'summary': 'Insert multiple types of objects including adobe, office, graphs, bitmap images, and word documents. also, explore hyperlinks.', 'duration': 25.797, 'max_score': 22135.622, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc22135622.jpg'}, {'end': 22305.326, 'src': 'embed', 'start': 22278.002, 'weight': 8, 'content': [{'end': 22286.124, 'text': "so that's easy hyperlink for you and more beneficial if you want to use it when there are multiple sheet,", 'start': 22278.002, 'duration': 8.122}, {'end': 22289.905, 'text': 'I can show you an example using my spreadsheet.', 'start': 22286.124, 'duration': 3.781}, {'end': 22297.803, 'text': 'so In this particular spreadsheet I have this topics which have to cover or which have been covering.', 'start': 22289.905, 'duration': 7.898}, {'end': 22299.184, 'text': 'so I have multiple sheet.', 'start': 22297.803, 'duration': 1.381}, {'end': 22305.326, 'text': 'right. so if you see that I have multiple sheets and what I can do is I can simply go.', 'start': 22299.184, 'duration': 6.142}], 'summary': 'Demonstrating the use of hyperlinks in a spreadsheet with multiple sheets.', 'duration': 27.324, 'max_score': 22278.002, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc22278002.jpg'}, {'end': 22476.178, 'src': 'embed', 'start': 22450.312, 'weight': 4, 'content': [{'end': 22457.817, 'text': "It's all about protecting workbook, creating a customized table by protecting cells or sheets,", 'start': 22450.312, 'duration': 7.505}, {'end': 22464.482, 'text': 'and also a few things in terms of how you could add hyperlinks and objects to a particular spreadsheet.', 'start': 22457.817, 'duration': 6.665}, {'end': 22468.794, 'text': "To start with, let's understand what pivot table is all about.", 'start': 22465.592, 'duration': 3.202}, {'end': 22476.178, 'text': 'So from the definition you can read through which tells that it is a most powerful feature.', 'start': 22469.554, 'duration': 6.624}], 'summary': 'Protect workbook, customize table, add hyperlinks, and understand pivot tables.', 'duration': 25.866, 'max_score': 22450.312, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc22450312.jpg'}, {'end': 22563.7, 'src': 'embed', 'start': 22536.285, 'weight': 2, 'content': [{'end': 22545.872, 'text': 'Sorry, certain features you learned in Excel, such as simple formulas, using sorting and then calculating in Pivot and so on and so forth.', 'start': 22536.285, 'duration': 9.587}, {'end': 22549.234, 'text': "So that's what we will see in detail in the coming slides.", 'start': 22545.892, 'duration': 3.342}, {'end': 22562.179, 'text': 'So in summary, it is basically an automatic sort where you could use a huge amount of data sorted automatically using pivot tables.', 'start': 22550.954, 'duration': 11.225}, {'end': 22563.7, 'text': 'And not just that.', 'start': 22562.699, 'duration': 1.001}], 'summary': 'Learn excel features: formulas, sorting, pivot tables for automatic sorting of large data.', 'duration': 27.415, 'max_score': 22536.285, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc22536285.jpg'}], 'start': 20674.223, 'title': 'Excel data security and analysis', 'summary': 'Covers excel features like conditional formatting, workbook security, file management, spreadsheet cell protection, protecting workbook structure, sharing features, and pivot table data analysis to ensure data integrity and security, with insights into specific excel options and actions for effective data analysis and protection.', 'chapters': [{'end': 20822.407, 'start': 20674.223, 'title': 'Excel conditional formatting and workbook security', 'summary': 'Explains how to use conditional formatting in excel to highlight cells based on certain conditions, and also demonstrates how to secure a workbook by setting passwords for opening and modifying it, providing insights into specific excel options and actions.', 'duration': 148.184, 'highlights': ['The chapter demonstrates how to use conditional formatting in Excel to highlight cell values based on specific conditions, enhancing data visualization and analysis.', 'It provides a step-by-step guide on securing an Excel workbook by setting passwords for opening and modifying it, ensuring data privacy and integrity.', 'The chapter also explains specific Excel options such as save as, tools, and general options, offering practical insights into workbook management and security measures.']}, {'end': 21077.72, 'start': 20822.507, 'title': 'Excel file management', 'summary': 'Illustrates the importance of creating backups in excel, highlights the significance of using passwords for data security, and demonstrates the functionality of read-only mode, emphasizing the inability to modify the file and the necessity of creating backups.', 'duration': 255.213, 'highlights': ['The importance of creating backups in Excel', 'Significance of using passwords for data security', 'Demonstration of the functionality of read-only mode']}, {'end': 21413.986, 'start': 21078.12, 'title': 'Spreadsheet cell protection', 'summary': 'Explains how to password protect and lock specific cells in a spreadsheet to prevent unauthorized modifications, ensuring data integrity and security, and shares the process of using cell protection in excel to restrict access and modifications, with a step-by-step guide on locking and unlocking cells and ensuring data integrity.', 'duration': 335.866, 'highlights': ['The chapter explains how to password protect and lock specific cells in a spreadsheet.', 'The process of using cell protection in Excel to restrict access and modifications, with a step-by-step guide on locking and unlocking cells.', 'Ensuring data integrity and security by preventing unauthorized modifications.']}, {'end': 21859.244, 'start': 21414.747, 'title': 'Protecting workbook and sharing features', 'summary': 'Explains how to protect workbook structure and windows, protect specific cells with formulas, and the concept of hidden cells. it also demonstrates the process of sharing a workbook in a shared drive, allowing multiple users to access and make changes simultaneously.', 'duration': 444.497, 'highlights': ['The chapter explains how to protect workbook structure and windows, protect specific cells with formulas, and the concept of hidden cells.', 'The process of sharing a workbook in a shared drive, allowing multiple users to access and make changes simultaneously, is demonstrated.']}, {'end': 22476.178, 'start': 21859.905, 'title': 'Excel workbook tips and tricks', 'summary': 'Discusses effective use of sharebook, inserting objects and hyperlinks, with tips on managing data access and creating hyperlinks to external sources, and also covers protecting workbooks and using pivot tables for data analysis.', 'duration': 616.273, 'highlights': ['Sharebook allows for selective data retention and is best suited for read-only access, caution is advised when allowing editing and sharing options.', 'Inserting objects allows for embedding various file types into the workbook, with options for linking and customizing display, facilitating easy access to referenced materials.', 'Creating hyperlinks within the workbook enables easy navigation between sheets, and also facilitates linking to external sources such as websites or documents.', 'The chapter also covers tips on protecting workbooks and using pivot tables for data analysis.']}, {'end': 23002.031, 'start': 22476.959, 'title': 'Extracting insights with pivot tables', 'summary': 'Explains the use of pivot tables to automatically sort and analyze large data sets, enabling tasks like creating graphs, calculating averages, and filtering sales data effectively.', 'duration': 525.072, 'highlights': ['Pivot tables allow for automatic sorting and analysis of large data sets, enabling tasks like creating graphs, calculating averages, and filtering sales data effectively.', 'Demonstration of using filters and sorting to analyze data before utilizing pivot tables to break down sales data, such as finding the count and sum of sales for specific products and types.', 'Example of breaking down sales data, such as finding the count and sum of sales for specific products and types, demonstrating the use of pivot tables for efficient data analysis.']}], 'duration': 2327.808, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc20674223.jpg', 'highlights': ['The chapter demonstrates how to use conditional formatting in Excel to highlight cell values based on specific conditions, enhancing data visualization and analysis.', 'The chapter explains how to password protect and lock specific cells in a spreadsheet.', 'Pivot tables allow for automatic sorting and analysis of large data sets, enabling tasks like creating graphs, calculating averages, and filtering sales data effectively.', 'The process of sharing a workbook in a shared drive, allowing multiple users to access and make changes simultaneously, is demonstrated.', 'The chapter also covers tips on protecting workbooks and using pivot tables for data analysis.', 'The importance of creating backups in Excel', 'The process of using cell protection in Excel to restrict access and modifications, with a step-by-step guide on locking and unlocking cells.', 'Inserting objects allows for embedding various file types into the workbook, with options for linking and customizing display, facilitating easy access to referenced materials.', 'Creating hyperlinks within the workbook enables easy navigation between sheets, and also facilitates linking to external sources such as websites or documents.', 'Significance of using passwords for data security', 'Demonstration of the functionality of read-only mode', 'Sharebook allows for selective data retention and is best suited for read-only access, caution is advised when allowing editing and sharing options.', 'The chapter explains how to protect workbook structure and windows, protect specific cells with formulas, and the concept of hidden cells.', 'The chapter also explains specific Excel options such as save as, tools, and general options, offering practical insights into workbook management and security measures.', 'Ensuring data integrity and security by preventing unauthorized modifications.']}, {'end': 25116.333, 'segs': [{'end': 23081.939, 'src': 'embed', 'start': 23056.281, 'weight': 5, 'content': [{'end': 23061.932, 'text': 'So how Does Excel rescue us? It does through pivot tables.', 'start': 23056.281, 'duration': 5.651}, {'end': 23071.615, 'text': "Okay, so all that which I did using filter, I'm going to insert a pivot table and I'll show you how quickly and easily I can do that.", 'start': 23062.993, 'duration': 8.622}, {'end': 23078.298, 'text': 'So for doing pivot tables, what I need to do is first select the data, something which we saw in the filtering as well.', 'start': 23072.096, 'duration': 6.202}, {'end': 23081.939, 'text': 'So first select the data, then I go to insert.', 'start': 23078.738, 'duration': 3.201}], 'summary': 'Excel rescues by quickly creating pivot tables from selected data.', 'duration': 25.658, 'max_score': 23056.281, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc23056281.jpg'}, {'end': 23505.845, 'src': 'embed', 'start': 23450.965, 'weight': 2, 'content': [{'end': 23459.114, 'text': 'So let me just quickly do something and then I will show you the different options which are available.', 'start': 23450.965, 'duration': 8.149}, {'end': 23501.842, 'text': "Okay, so to help you understand the pivot table, I've just randomly selected the different items and dropped into these four fields.", 'start': 23493.897, 'duration': 7.945}, {'end': 23505.845, 'text': 'So report filter, column labels, row labels, and values.', 'start': 23502.302, 'duration': 3.543}], 'summary': 'Demonstrating pivot table with report filter, column labels, row labels, and values.', 'duration': 54.88, 'max_score': 23450.965, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc23450965.jpg'}, {'end': 23636.88, 'src': 'embed', 'start': 23610.303, 'weight': 8, 'content': [{'end': 23615.085, 'text': "So here I'm using simple Pivot tables to get the data or get the numbers I want.", 'start': 23610.303, 'duration': 4.782}, {'end': 23618.967, 'text': 'The summation value is nothing but a mathematical function.', 'start': 23615.825, 'duration': 3.142}, {'end': 23622.29, 'text': 'So here in the summation, we have a lot of options.', 'start': 23619.368, 'duration': 2.922}, {'end': 23625.752, 'text': 'So here it is basically showing the count of sales.', 'start': 23622.67, 'duration': 3.082}, {'end': 23629.015, 'text': 'I could actually go pull this drop down.', 'start': 23626.293, 'duration': 2.722}, {'end': 23635.159, 'text': "I can go to value field settings and I don't want count because it doesn't make the actual count of sales.", 'start': 23629.035, 'duration': 6.124}, {'end': 23636.88, 'text': 'But let me do some of the sales.', 'start': 23635.199, 'duration': 1.681}], 'summary': 'Using pivot tables for sales data, modifying value field settings for accurate sales count.', 'duration': 26.577, 'max_score': 23610.303, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc23610303.jpg'}, {'end': 23926.322, 'src': 'embed', 'start': 23903.076, 'weight': 1, 'content': [{'end': 23912.369, 'text': 'Now for the same set of data, the same table, I have a filter and using that filter I can exactly find out how was the feedback for cookies.', 'start': 23903.076, 'duration': 9.293}, {'end': 23921.499, 'text': 'I could create a graph for this And then I can also do the same for some other product and compare why the feedback is good for cookies,', 'start': 23913.29, 'duration': 8.209}, {'end': 23925.022, 'text': 'why the feedback is good for some other product, and so on.', 'start': 23921.499, 'duration': 3.523}, {'end': 23926.322, 'text': "So that's the beauty of Pivot.", 'start': 23925.082, 'duration': 1.24}], 'summary': 'Using pivot, filter data for feedback on cookies and compare with other products.', 'duration': 23.246, 'max_score': 23903.076, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc23903076.jpg'}, {'end': 24313.855, 'src': 'embed', 'start': 24279.564, 'weight': 3, 'content': [{'end': 24281.365, 'text': 'Show sales one country at a time.', 'start': 24279.564, 'duration': 1.801}, {'end': 24284.407, 'text': 'Find the top five salesperson.', 'start': 24281.965, 'duration': 2.442}, {'end': 24287.828, 'text': 'Compare salesperson performance quarter by quarter.', 'start': 24284.927, 'duration': 2.901}, {'end': 24290.81, 'text': 'View the details behind a summarized amount.', 'start': 24288.529, 'duration': 2.281}, {'end': 24294.132, 'text': 'Show summarized amount as a percentage of the total.', 'start': 24291.53, 'duration': 2.602}, {'end': 24296.613, 'text': 'Calculate a bonus or commission.', 'start': 24294.772, 'duration': 1.841}, {'end': 24298.349, 'text': 'So all this is what I want to do.', 'start': 24297.008, 'duration': 1.341}, {'end': 24304.631, 'text': "I'm sure most of them you would be thinking that I need to do this and then I'll get it, but then I'll show you step by step.", 'start': 24298.829, 'duration': 5.802}, {'end': 24305.472, 'text': 'How do you do it?', 'start': 24304.791, 'duration': 0.681}, {'end': 24307.613, 'text': 'How do you actually replicate this?', 'start': 24305.652, 'duration': 1.961}, {'end': 24313.855, 'text': "And then, if you're able to do this, then I think you're familiar with pivot and has you practice more.", 'start': 24308.133, 'duration': 5.722}], 'summary': 'Analyze sales data by country, top 5 salespeople, quarterly performance, detailed view, percentage of total, and calculate bonus/commission.', 'duration': 34.291, 'max_score': 24279.564, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc24279564.jpg'}, {'end': 24879.612, 'src': 'embed', 'start': 24850.783, 'weight': 6, 'content': [{'end': 24858.228, 'text': 'So for example, I was talking about quarters, right? So when I put quarters, so I want to see why the quarter three sale is at 166.', 'start': 24850.783, 'duration': 7.445}, {'end': 24866.635, 'text': 'So here also I could double click on this data to get the complete list of data and then understand what exactly went wrong in quarter three.', 'start': 24858.228, 'duration': 8.407}, {'end': 24869.897, 'text': "So that's another way of doing that particular analysis.", 'start': 24867.055, 'duration': 2.842}, {'end': 24873.19, 'text': "So that's what happens when you use pivot.", 'start': 24870.629, 'duration': 2.561}, {'end': 24879.612, 'text': 'It gives you so many options and really helps you to actually analyze data in different different ways.', 'start': 24873.45, 'duration': 6.162}], 'summary': 'Using pivot allows for deeper analysis of sales data, such as examining the 166 sales in quarter three.', 'duration': 28.829, 'max_score': 24850.783, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc24850783.jpg'}, {'end': 25047.417, 'src': 'embed', 'start': 25020.686, 'weight': 0, 'content': [{'end': 25024.968, 'text': "So let's take, I want to give these guys a 10% bonus for their particular sales.", 'start': 25020.686, 'duration': 4.282}, {'end': 25030.73, 'text': 'And either I could take this particular graph, get all the total amount and then calculate a bonus.', 'start': 25025.708, 'duration': 5.022}, {'end': 25034.392, 'text': 'But is there an option in pivot? Yes, there is.', 'start': 25031.19, 'duration': 3.202}, {'end': 25039.174, 'text': "Okay So for that, first, let me get the total amount of sales they've done.", 'start': 25034.412, 'duration': 4.762}, {'end': 25042.535, 'text': 'So I have the salesperson instead of the count of order.', 'start': 25039.674, 'duration': 2.861}, {'end': 25045.156, 'text': 'I will remove the count of order.', 'start': 25043.916, 'duration': 1.24}, {'end': 25047.417, 'text': 'I will go ahead and put order amount.', 'start': 25045.716, 'duration': 1.701}], 'summary': 'Planning to give a 10% bonus based on sales, exploring pivot option for calculation.', 'duration': 26.731, 'max_score': 25020.686, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc25020686.jpg'}], 'start': 23002.691, 'title': 'Using pivot tables for data analysis', 'summary': 'Discusses the effective use of pivot tables in excel for data analysis, covering topics such as creating and utilizing pivot tables, filtering and summarizing data, customizing layout and calculations, data visualization through pivot fields, and extensive capabilities in sales data analysis, with practical applications, common errors to avoid, and various business needs addressed.', 'chapters': [{'end': 23554.951, 'start': 23002.691, 'title': 'Pivot tables in excel', 'summary': 'Discusses the use of pivot tables in excel, demonstrating how to create and utilize them effectively, emphasizing the importance of proper data organization and common errors to avoid, showcasing their practical applications and functionalities.', 'duration': 552.26, 'highlights': ['The chapter discusses the use of pivot tables in Excel, demonstrating how to create and utilize them effectively', 'Emphasizing the importance of proper data organization and common errors to avoid', 'Showcasing practical applications and functionalities of pivot tables']}, {'end': 23853.915, 'start': 23555.151, 'title': 'Pivot table for data analysis', 'summary': 'Explains how to use pivot tables in excel to filter and summarize data based on different row and column labels, with options to show counts, sums, and other functions, and demonstrates the flexibility in customizing the layout and calculations for different business needs.', 'duration': 298.764, 'highlights': ['Pivot tables can be used to filter and display data based on different row and column labels, allowing for easy calculation and summation of values.', 'Different mathematical functions such as count, sum, average, max, min, and product can be applied to the data, providing flexibility in customizing the calculations based on specific requirements.', 'The chapter emphasizes the adaptability of pivot tables, allowing users to manipulate the fields and labels to generate customized data views for different business analysis purposes.']}, {'end': 24057.184, 'start': 23853.915, 'title': 'Data visualization using pivot fields', 'summary': 'Demonstrates how to use pivot fields to create charts, filter data by product and customer, compare feedback for different products, and analyze sales data to determine profit and sales count.', 'duration': 203.269, 'highlights': ['By using pivot fields, one can create charts to compare feedback for different products and analyze sales data, providing valuable insights for decision-making.', 'Pivot fields allow for filtering data by product and customer, enabling the user to review feedback for specific products and compare feedback for different products.', 'The chapter showcases the flexibility of pivot fields, demonstrating the interchangeability of fields and the ability to analyze sales data to determine profit and sales count.']}, {'end': 24494.533, 'start': 24057.184, 'title': 'Pivot table: sales data analysis', 'summary': 'Explores the extensive capabilities of pivot tables, including creating reports, graphs, and charts based on sales data, with the potential to compare salesperson performance, view details behind summarized amounts, and calculate bonuses or commissions.', 'duration': 437.349, 'highlights': ['Pivot tables enable creating reports that provide total orders for each salesperson by utilizing the count function, allowing for detailed graph representation of salesperson performance.', 'The capability to compare salesperson performance quarter by quarter using pivot tables, enabling in-depth analysis and assessment of sales performance over different time periods.', 'The potential to view the details behind summarized amounts and present them as a percentage of the total, facilitating a deeper understanding of sales data and its relative contribution.', 'The ability to calculate bonuses or commissions based on sales data analysis using pivot tables, enabling the implementation of incentive programs for sales teams.']}, {'end': 25116.333, 'start': 24494.533, 'title': 'Pivot table data analysis', 'summary': 'Covers the use of pivot tables for data analysis, including showcasing sales data by country, sorting and filtering top salespersons, comparing sales performance quarter by quarter, viewing detailed data behind summarized amounts, calculating percentages of total sales, and creating a bonus or commission based on sales data.', 'duration': 621.8, 'highlights': ['The chapter covers the use of pivot tables for data analysis, including showcasing sales data by country, sorting and filtering top salespersons, comparing sales performance quarter by quarter, viewing detailed data behind summarized amounts, calculating percentages of total sales, and creating a bonus or commission based on sales data.', 'Showcasing sales data by country using pivot tables and filter options to display sales for one country at a time or in different arrangements such as column label or row label.', 'Sorting and filtering top salespersons using pivot tables, including sorting data in descending order to identify the top five salespersons and utilizing filtering and sorting options available in PMAT.', 'Comparing sales performance quarter by quarter by arranging and grouping data by dates, and utilizing pivot tables to group data by quarter and month for easy comparison.', 'Viewing detailed data behind summarized amounts by double-clicking on specific cells to access complete data, enabling in-depth analysis without needing to refer to the original data source.', 'Calculating percentages of total sales using pivot tables and value field settings to show values as percentages of rows or columns, providing an easy method for analyzing sales data in relation to the total.', 'Creating a bonus or commission based on sales data using pivot tables and calculated fields to define and apply formulas for calculating bonuses or commissions based on sales amounts.']}], 'duration': 2113.642, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc23002691.jpg', 'highlights': ['Pivot tables enable creating reports for total orders by salesperson, facilitating detailed graph representation of salesperson performance.', 'Pivot fields allow for filtering data by product and customer, enabling the review of feedback for specific products and comparison of feedback for different products.', 'Pivot tables can be used to filter and display data based on different row and column labels, allowing for easy calculation and summation of values.', 'The chapter covers the use of pivot tables for data analysis, including showcasing sales data by country, sorting and filtering top salespersons, comparing sales performance quarter by quarter, viewing detailed data behind summarized amounts, calculating percentages of total sales, and creating a bonus or commission based on sales data.', 'By using pivot fields, one can create charts to compare feedback for different products and analyze sales data, providing valuable insights for decision-making.', 'The chapter discusses the use of pivot tables in Excel, demonstrating how to create and utilize them effectively, emphasizing the importance of proper data organization and common errors to avoid, and showcasing practical applications and functionalities of pivot tables.', 'Pivot tables enable the comparison of salesperson performance quarter by quarter, facilitating in-depth analysis and assessment of sales performance over different time periods.', 'Pivot tables enable the calculation of bonuses or commissions based on sales data analysis, enabling the implementation of incentive programs for sales teams.', 'Pivot tables allow for the calculation of percentages of total sales using value field settings to show values as percentages of rows or columns, providing an easy method for analyzing sales data in relation to the total.', 'Pivot tables emphasize the adaptability, allowing users to manipulate the fields and labels to generate customized data views for different business analysis purposes.']}, {'end': 27235.53, 'segs': [{'end': 25170.074, 'src': 'embed', 'start': 25145.5, 'weight': 0, 'content': [{'end': 25152.661, 'text': 'You had a demo and then we also saw different options of how pivot could be used to create simple report of salesperson.', 'start': 25145.5, 'duration': 7.161}, {'end': 25155.931, 'text': 'filter it using a particular country.', 'start': 25153.43, 'duration': 2.501}, {'end': 25163.213, 'text': 'Then we saw how we could use sorting in the pivot, compare sales per percent performance by using grouping.', 'start': 25156.531, 'duration': 6.682}, {'end': 25170.074, 'text': 'Then we wanted to analyze the data and how we could use that particular cell to open up the data and see that.', 'start': 25163.793, 'duration': 6.281}], 'summary': 'Demo showcased pivot functionalities for sales report analysis.', 'duration': 24.574, 'max_score': 25145.5, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc25145500.jpg'}, {'end': 25318.127, 'src': 'embed', 'start': 25288.561, 'weight': 1, 'content': [{'end': 25290.302, 'text': 'and then pick up the respective object.', 'start': 25288.561, 'duration': 1.741}, {'end': 25293.966, 'text': 'VLOOKUP also similarly works on the same concept.', 'start': 25291.002, 'duration': 2.964}, {'end': 25297.792, 'text': 'In VLOOKUP, you have a certain syntax which you need to follow,', 'start': 25294.527, 'duration': 3.265}, {'end': 25307.245, 'text': 'and the most important things in the syntax is that there has to be a common lookup value between the two places where you want to pick up stuff.', 'start': 25297.792, 'duration': 9.453}, {'end': 25314.465, 'text': 'now putting this in excel terms, imagine you have two databases.', 'start': 25308.922, 'duration': 5.543}, {'end': 25318.127, 'text': 'one database you have an employee list.', 'start': 25314.465, 'duration': 3.662}], 'summary': 'Vlookup requires a common lookup value between two databases in excel.', 'duration': 29.566, 'max_score': 25288.561, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc25288561.jpg'}, {'end': 25430.469, 'src': 'embed', 'start': 25396.676, 'weight': 5, 'content': [{'end': 25399.339, 'text': 'So that is how a VLOOKUP function is used.', 'start': 25396.676, 'duration': 2.663}, {'end': 25408.563, 'text': 'Lookup function in summary is nothing but you use a certain syntax to pick up the values you want from a particular database.', 'start': 25399.939, 'duration': 8.624}, {'end': 25410.606, 'text': "So that's how VLOOKUP works.", 'start': 25408.583, 'duration': 2.023}, {'end': 25414.672, 'text': "So let's get to understand in detail.", 'start': 25411.708, 'duration': 2.964}, {'end': 25420.38, 'text': 'So let me go to the demo now and teach you how to use VLOOKUP.', 'start': 25415.132, 'duration': 5.248}, {'end': 25430.469, 'text': 'Alright, I have some exercise prepared for you for you to do, but even before we go ahead and practice this exercise,', 'start': 25422.386, 'duration': 8.083}], 'summary': 'Vlookup function is used to pick values from a database. a demo and exercise are provided for learning.', 'duration': 33.793, 'max_score': 25396.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc25396676.jpg'}, {'end': 25898.657, 'src': 'embed', 'start': 25868.807, 'weight': 2, 'content': [{'end': 25877.83, 'text': 'so once again, my syntax vlookup lookup value which i selected, table array, the complete table array, column index number, the column,', 'start': 25868.807, 'duration': 9.023}, {'end': 25882.975, 'text': "the data from which the column, the column data which needs to be picked out that i'm entering.", 'start': 25877.83, 'duration': 5.145}, {'end': 25886.699, 'text': 'the range lookup is going to be 0 and click.', 'start': 25882.975, 'duration': 3.724}, {'end': 25888.841, 'text': 'ok. so what it did?', 'start': 25886.699, 'duration': 2.142}, {'end': 25898.657, 'text': 'if you see the result, it is basically searched e005 in this particular table And then, on searching E005, it found Deepa in the second column.', 'start': 25888.841, 'duration': 9.816}], 'summary': "Using vlookup with syntax to find 'deepa' for 'e005'.", 'duration': 29.85, 'max_score': 25868.807, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc25868807.jpg'}, {'end': 26150.948, 'src': 'embed', 'start': 26124.439, 'weight': 3, 'content': [{'end': 26134.603, 'text': 'That is why the last value, when I come down to here and I click F2, though my E001 is relative, look at where my table has come.', 'start': 26124.439, 'duration': 10.164}, {'end': 26139.885, 'text': "It's come down because it has also moved down basis relative reference over here.", 'start': 26135.383, 'duration': 4.502}, {'end': 26144.387, 'text': 'So, recollecting what you learned in your relative reference chapter.', 'start': 26140.446, 'duration': 3.941}, {'end': 26145.468, 'text': 'what is that you need to do here?', 'start': 26144.387, 'duration': 1.081}, {'end': 26150.948, 'text': 'you need to make this particular table an absolute reference.', 'start': 26147.725, 'duration': 3.223}], 'summary': 'Relative reference moved table down, requiring absolute reference.', 'duration': 26.509, 'max_score': 26124.439, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc26124439.jpg'}, {'end': 26492.361, 'src': 'embed', 'start': 26467.3, 'weight': 4, 'content': [{'end': 26476.751, 'text': "For example, if your employee number is somewhere here, which I'll show you, your VLOOKUP will not work.", 'start': 26467.3, 'duration': 9.451}, {'end': 26478.052, 'text': "that's the drawback.", 'start': 26476.751, 'duration': 1.301}, {'end': 26487.018, 'text': 'it has to be in the first column, which means your lookup value and your table array should has your unique value at the first column.', 'start': 26478.052, 'duration': 8.966}, {'end': 26488.258, 'text': "so let's understand that better.", 'start': 26487.018, 'duration': 1.24}, {'end': 26492.361, 'text': 'so the first one was that you need to select the table array correctly.', 'start': 26488.258, 'duration': 4.103}], 'summary': 'Vlookup requires unique value in first column for proper functionality.', 'duration': 25.061, 'max_score': 26467.3, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc26467300.jpg'}], 'start': 25116.814, 'title': 'Vlookup function in excel', 'summary': 'Covers the use of pivot functions to manipulate and analyze sales data, including sorting, filtering, and grouping, as well as the definition and use of the vlookup function. it explains the concept of vlookup function, highlighting its use in excel to retrieve data from two databases based on a common unique reference, and delves into the detailed syntax and parameters of the vlookup function. additionally, it explains how to use vlookup in excel to search for values in a table array, specifying column index numbers and range lookup, with examples demonstrating the process and results. the chapter also covers the vlookup function, including the concepts of exact match and approximate match, as well as the importance of using absolute referencing to avoid errors when using relative references, with a demonstration of using the vlookup function and absolute referencing to retrieve specific data from a table. it also highlights common mistakes and drawbacks of vlookup, emphasizing the correct syntax and usage, with practical examples and recommendations for practice, to ensure successful application.', 'chapters': [{'end': 25198.546, 'start': 25116.814, 'title': 'Pivot and vlookup functions', 'summary': 'Covers the use of pivot functions to manipulate and analyze sales data, including sorting, filtering, and grouping, as well as the definition and use of the vlookup function.', 'duration': 81.732, 'highlights': ['The chapter covers the use of pivot functions to manipulate and analyze sales data, including sorting, filtering, and grouping, as well as the definition and use of the VLOOKUP function.', 'Pivot functions demonstrated include creating simple reports of salespersons, filtering by country, sorting, comparing sales performance, analyzing data, adding manual calculation fields, and using value fields within pivots.', 'The VLOOKUP function is defined as looking for a value in the leftmost column and returning a value in the same row from another column.']}, {'end': 25800.452, 'start': 25198.926, 'title': 'Understanding vlookup function', 'summary': 'Explains the concept of vlookup function, highlighting its use in excel to retrieve data from two databases based on a common unique reference, and then delves into the detailed syntax and parameters of the vlookup function.', 'duration': 601.526, 'highlights': ["VLOOKUP function is explained using an analogy of picking up items from a friend's house, emphasizing the need for a common reference value and the concept of lookup value and common value in databases.", 'The VLOOKUP function syntax parameters, including lookup value, table array, column index number, and range lookup, are detailed, with an emphasis on the importance of a unique lookup value for effective data retrieval.', 'The demonstration of the VLOOKUP function involves using employee IDs as unique reference values to retrieve corresponding information like name, address, and phone numbers from two tables, highlighting the importance of unique values for lookup in VLOOKUP function.', 'The chapter also covers the usage of the insert function to access the VLOOKUP syntax, providing a practical approach to understanding and implementing VLOOKUP in Excel.', 'The concept of range lookup in VLOOKUP is explained using an integer concept, detailing the significance of using 0 for an exact match, -1 for values lesser than the lookup value, and +1 for values higher than the lookup value, providing clarity on the practical application of range lookup in VLOOKUP function.']}, {'end': 26005.445, 'start': 25801.293, 'title': 'Using vlookup in excel', 'summary': 'Explains how to use vlookup in excel to search for values in a table array, specifying column index numbers and range lookup, with examples demonstrating the process and results.', 'duration': 204.152, 'highlights': ['The chapter explains using VLOOKUP in Excel to search for values in a table array.', 'The process involves specifying column index numbers and range lookup.', 'Examples demonstrate the process and results of using VLOOKUP in Excel.']}, {'end': 26319.418, 'start': 26007.375, 'title': 'Vlookup function and absolute referencing', 'summary': 'Covers the vlookup function, including the concepts of exact match and approximate match, as well as the importance of using absolute referencing to avoid errors when using relative references, with a demonstration of using the vlookup function and absolute referencing to retrieve specific data from a table.', 'duration': 312.043, 'highlights': ['The importance of using absolute referencing to avoid errors when using relative references', 'Explanation and demonstration of using the VLOOKUP function to retrieve specific data from a table', 'Concepts of exact match and approximate match in the context of the VLOOKUP function']}, {'end': 26655.176, 'start': 26319.418, 'title': 'Vlookup drawbacks and common mistakes', 'summary': 'Highlights common mistakes and drawbacks of vlookup, including the importance of selecting the table array correctly and the necessity for the unique value to start in the first column, which can cause errors and hinder the accuracy of the formula.', 'duration': 335.758, 'highlights': ['The importance of selecting the table array correctly to avoid incorrect results, which can cause serious problems when working with large amounts of data.', 'The necessity for the unique value to start in the first column for VLOOKUP to work effectively, as shown through examples of errors and manual changes required.', 'The drawbacks of VLOOKUP, including errors and inaccuracies, and the reference to the alternative method of overcoming these drawbacks with index and match, which will be covered in the next chapter.']}, {'end': 27235.53, 'start': 26655.176, 'title': 'Vlookup syntax and usage', 'summary': 'Discusses the vlookup function, emphasizing the correct syntax and usage, with practical examples and recommendations for practice, to ensure successful application.', 'duration': 580.354, 'highlights': ['The importance of using the VLOOKUP syntax correctly', 'Practical recommendation for practicing VLOOKUP function', 'Guidance on using VLOOKUP to find prices and calculate total income', 'Exercise on finding payments for each instructor based on their activity']}], 'duration': 2118.716, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc25116814.jpg', 'highlights': ['Pivot functions cover sorting, filtering, and grouping sales data', 'VLOOKUP function retrieves data based on a common unique reference', 'Demonstrates using VLOOKUP to search for values in a table array', 'Importance of using absolute referencing to avoid errors', 'Selecting the table array correctly is crucial for VLOOKUP effectiveness', 'Practical recommendation for practicing VLOOKUP function']}, {'end': 28933.566, 'segs': [{'end': 27764.905, 'src': 'embed', 'start': 27732.942, 'weight': 3, 'content': [{'end': 27746.287, 'text': 'but now, with just doing that blocking a and using absolute reference for the table, let me see if it fix.', 'start': 27732.942, 'duration': 13.345}, {'end': 27755.035, 'text': "okay, looks like it's working, but it is always going to throw me the reference of 2, which is nothing but the column 2 name.", 'start': 27746.287, 'duration': 8.748}, {'end': 27758.158, 'text': 'so how do i solve that problem?', 'start': 27755.035, 'duration': 3.123}, {'end': 27764.905, 'text': 'for that, The clever solution, which should have got a hint, is to use some numbers over here.', 'start': 27758.158, 'duration': 6.747}], 'summary': 'Using absolute reference fixed the issue with column 2 names.', 'duration': 31.963, 'max_score': 27732.942, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc27732942.jpg'}, {'end': 28310.519, 'src': 'embed', 'start': 28282.701, 'weight': 2, 'content': [{'end': 28289.325, 'text': 'so, for example, i would say select a table, i would give row number one, column number one.', 'start': 28282.701, 'duration': 6.624}, {'end': 28297.831, 'text': 'so whatever is the value on that row number and column number, that would be returned back when i use index function.', 'start': 28289.325, 'duration': 8.506}, {'end': 28299.071, 'text': "so that's index function.", 'start': 28297.831, 'duration': 1.24}, {'end': 28303.975, 'text': "we will anyway demo that, but i'm just giving you the theoretical details about index.", 'start': 28299.071, 'duration': 4.904}, {'end': 28310.519, 'text': 'Okay, so now that we understand index returns a value, what exactly does a row match function do??', 'start': 28305.074, 'duration': 5.445}], 'summary': 'Index function returns value at specified row and column.', 'duration': 27.818, 'max_score': 28282.701, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc28282701.jpg'}, {'end': 28565.037, 'src': 'embed', 'start': 28521.411, 'weight': 0, 'content': [{'end': 28530.339, 'text': "if I'm working with a small table but then using a lookup function, you're basically working with a huge amount of data and keeping,", 'start': 28521.411, 'duration': 8.928}, {'end': 28537.246, 'text': 'sorting the data according to your requirement and spoiling the table format for your requirement might be cumbersome.', 'start': 28530.339, 'duration': 6.907}, {'end': 28540.909, 'text': "So that's where index and match comes to a rescue.", 'start': 28537.706, 'duration': 3.203}, {'end': 28549.563, 'text': "Okay, so now I'm getting into index and match and let's understand how to use index and match function as a lookup.", 'start': 28541.803, 'duration': 7.76}, {'end': 28556.852, 'text': "I'm using the same exercise, which we did in VLOOKUP so that you can familiarize and you already worked on it.", 'start': 28551.129, 'duration': 5.723}, {'end': 28560.174, 'text': "So it's easier for you to associate and learn out of it.", 'start': 28556.912, 'duration': 3.262}, {'end': 28565.037, 'text': 'So, but even before we go ahead and start applying the index and match.', 'start': 28560.734, 'duration': 4.303}], 'summary': 'Using index and match for efficient lookup in large datasets.', 'duration': 43.626, 'max_score': 28521.411, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc28521411.jpg'}], 'start': 27235.53, 'title': 'Excel vlookup and index functions', 'summary': 'Covers the use of vlookup and index functions in excel, emphasizing correct formula application, addressing reference problems, and demonstrating practical examples for improved data retrieval and manipulation, and efficient lookup.', 'chapters': [{'end': 27372.854, 'start': 27235.53, 'title': 'Vlookup formula for comparing football activity', 'summary': 'Illustrates the use of the vlookup formula to compare activity to activity, emphasizing the correct use of the formula and the resulting total payments for football and swimming activities.', 'duration': 137.324, 'highlights': ['The Vlookup formula is used to compare activities and their corresponding weekly salaries, with football having a weekly salary of 165 and swimming having a weekly salary of 220.', 'The column index number for the Vlookup formula is determined by the number of columns in the table array, which in this case is 2, ensuring the correct calculation of total payments.', 'The process of using the Vlookup formula and autofill handler results in the accurate determination of the total payment for each instructor, exemplified by the specific payments for football and swimming activities.']}, {'end': 27675.027, 'start': 27373.274, 'title': 'Vlookup exercise and reference problem', 'summary': 'Covers the vlookup exercise, including the use of lookup value, table array, and column index number, and addresses the challenge of fixing reference problems while aiming to create a simple formula that works perfectly across different cells.', 'duration': 301.753, 'highlights': ['Using VLOOKUP to find employee details', 'Challenges with creating a single formula for multiple cells', 'Addressing reference problems in VLOOKUP']}, {'end': 28384.911, 'start': 27675.047, 'title': 'Excel vlookup, hlookup, index, and match functions', 'summary': 'Discusses the application of vlookup, hlookup, index, and match functions in excel, emphasizing the use of referencing and absolute reference, alongside practical examples, for improved data retrieval and manipulation.', 'duration': 709.864, 'highlights': ['The chapter discusses the application of VLOOKUP, HLOOKUP, Index, and Match functions in Excel.', 'Emphasizing the use of referencing and absolute reference, alongside practical examples, for improved data retrieval and manipulation.', 'Practical examples include blocking columns and rows, using cell references, and employing mixed references for efficient data manipulation and retrieval.', 'The chapter explores the use of VLOOKUP and HLOOKUP functions for vertical and horizontal data retrieval, respectively, with practical demonstrations.', 'Explanation of the Index and Match functions, emphasizing their roles in returning values and positions, supported by theoretical and practical insights.']}, {'end': 28933.566, 'start': 28385.431, 'title': 'Chapter 12: index and match function', 'summary': 'Demonstrates the use of index and match functions as a lookup, showcasing the limitations of vlookup and the efficient and flexible nature of index and match, with practical examples and clear explanations.', 'duration': 548.135, 'highlights': ['The index function is explained with practical examples, demonstrating its capability to return values based on the specified row and column numbers, showcasing its efficient and flexible nature.', 'The match function is demonstrated with clear explanations of its three arguments - lookup value, lookup array, and match type, showcasing its ability to return the position of the specified value in the lookup array.', 'The limitations of VLOOKUP are highlighted, emphasizing the need for index and match functions, especially when dealing with large datasets and avoiding the need to rearrange the data, providing a practical insight into the advantages of using index and match over VLOOKUP.']}], 'duration': 1698.036, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc27235530.jpg', 'highlights': ['The limitations of VLOOKUP are highlighted, emphasizing the need for index and match functions, especially when dealing with large datasets and avoiding the need to rearrange the data, providing a practical insight into the advantages of using index and match over VLOOKUP.', 'The chapter discusses the application of VLOOKUP, HLOOKUP, Index, and Match functions in Excel.', 'The index function is explained with practical examples, demonstrating its capability to return values based on the specified row and column numbers, showcasing its efficient and flexible nature.', 'Emphasizing the use of referencing and absolute reference, alongside practical examples, for improved data retrieval and manipulation.']}, {'end': 30136.618, 'segs': [{'end': 28967.685, 'src': 'embed', 'start': 28933.566, 'weight': 3, 'content': [{'end': 28944.433, 'text': "so index returns the value basis, the row and column, and match basically gives the position of that particular value which you're looking for now.", 'start': 28933.566, 'duration': 10.867}, {'end': 28948.376, 'text': 'how do I put both of this to use as a lookup function?', 'start': 28944.433, 'duration': 3.943}, {'end': 28952.498, 'text': "that's what we are going to see in my next exercise.", 'start': 28948.376, 'duration': 4.122}, {'end': 28967.685, 'text': 'just give me once again, let me copy this to down so that you can refer to it.', 'start': 28952.498, 'duration': 15.187}], 'summary': 'Using index and match functions for lookup in excel.', 'duration': 34.119, 'max_score': 28933.566, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc28933566.jpg'}, {'end': 29102.287, 'src': 'embed', 'start': 29082.359, 'weight': 2, 'content': [{'end': 29093.965, 'text': "So I'm searching E005 to get this relative position and I have to only select the positions or the particular column where the value is there and then match type is 0.", 'start': 29082.359, 'duration': 11.606}, {'end': 29096.906, 'text': 'I almost completed my match function, so I close that.', 'start': 29093.965, 'duration': 2.941}, {'end': 29102.287, 'text': 'Now I go back to my index, so you can see the formula giving you hints that index is done, array is done.', 'start': 29097.386, 'duration': 4.901}], 'summary': 'Searching e005 for relative position, completed match function, index and array done.', 'duration': 19.928, 'max_score': 29082.359, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc29082359.jpg'}, {'end': 29279.016, 'src': 'embed', 'start': 29252.616, 'weight': 4, 'content': [{'end': 29256.338, 'text': 'we have to use all that we learned in the previous chapters.', 'start': 29252.616, 'duration': 3.722}, {'end': 29258.279, 'text': 'we have to use the referencing.', 'start': 29256.338, 'duration': 1.941}, {'end': 29260.54, 'text': "we're using multiple formulas over here.", 'start': 29258.279, 'duration': 2.261}, {'end': 29262.781, 'text': 'so what is the right reference over here?', 'start': 29260.54, 'duration': 2.241}, {'end': 29275.753, 'text': 'so if you notice, your employee number stays, your table stays, but when you come down, Your employee number stays, but your table is moved down.', 'start': 29262.781, 'duration': 12.972}, {'end': 29279.016, 'text': 'There are two tables or two values which are moved on.', 'start': 29276.213, 'duration': 2.803}], 'summary': 'Applying referencing and multiple formulas with two tables being moved.', 'duration': 26.4, 'max_score': 29252.616, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc29252616.jpg'}, {'end': 29552.567, 'src': 'embed', 'start': 29520.297, 'weight': 0, 'content': [{'end': 29524.28, 'text': 'So for that, you have to modify your spreadsheet, your tables and get that done.', 'start': 29520.297, 'duration': 3.983}, {'end': 29527.243, 'text': "But in index, you really don't have to do that.", 'start': 29525.001, 'duration': 2.242}, {'end': 29534.613, 'text': 'important thing out here is you need to ensure you give the right syntax,', 'start': 29528.809, 'duration': 5.804}, {'end': 29544.061, 'text': 'you reference the right values and by doing so your index and match can be used to look, to look up values exactly.', 'start': 29534.613, 'duration': 9.448}, {'end': 29547.643, 'text': "so let's look at how i do that.", 'start': 29544.061, 'duration': 3.582}, {'end': 29549.665, 'text': 'so again, same thing.', 'start': 29547.643, 'duration': 2.022}, {'end': 29552.567, 'text': 'index open brackets.', 'start': 29549.665, 'duration': 2.902}], 'summary': 'Modify spreadsheet and tables to ensure correct syntax for using index and match to look up values.', 'duration': 32.27, 'max_score': 29520.297, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc29520297.jpg'}], 'start': 28933.566, 'title': 'Using index and match for data lookup', 'summary': 'Discusses the usage of index and match functions to perform a lookup for employee details, emphasizing flexibility and advantages over vlookup. it also explains the syntax accuracy and absolute referencing for complex formulas in excel.', 'chapters': [{'end': 29018.5, 'start': 28933.566, 'title': 'Using index and match for lookup', 'summary': 'Discusses the usage of index and match functions to perform a lookup, similar to vlookup, to find employee details such as name, address, and phone number from a given set of employee numbers.', 'duration': 84.934, 'highlights': ['The chapter discusses the usage of index and match functions to perform a lookup', 'Finding employee details such as name, address, and phone number from a given set of employee numbers', 'Explaining the usage of index function to start with']}, {'end': 29310.949, 'start': 29018.96, 'title': 'Understanding index and match functions', 'summary': 'Explains how to use the match function to retrieve the position of a specific value, and then use the index function to return the corresponding value in a selected column, emphasizing the importance of syntax accuracy and introducing the concept of absolute referencing for complex formulas.', 'duration': 291.989, 'highlights': ['The match function is used to retrieve the position of a specific value, such as E005, within a designated array, eliminating the need for manual entry.', 'The index function is employed to return the desired value, such as a name, from a specified column using the position obtained from the match function, demonstrating the practical application of the match function in conjunction with the index function.', 'The importance of syntax accuracy is emphasized, highlighting the necessity of correctly placing commas, opening and closing brackets, for the formula to function effectively.', 'Introduction of the concept of absolute referencing is made for complex formulas, showcasing the need to anchor specific cell references when dealing with multiple tables or values that shift positions.', 'Guidance is provided on using mixed referencing to address complex scenarios involving multiple tables or values, laying the foundation for more advanced applications of the index and match functions.']}, {'end': 30136.618, 'start': 29310.949, 'title': 'Index and match for data lookup', 'summary': 'Covers the usage of index and match functions for data lookup, emphasizing the flexibility and advantages over vlookup. it provides step-by-step instructions and examples for using index and match in excel, showcasing its ability to handle non-standard data arrangements and providing precise data retrieval.', 'duration': 825.669, 'highlights': ['The chapter explains the step-by-step process of using index and match functions for data lookup, showcasing the flexibility and advantages over VLOOKUP, with an emphasis on handling non-standard data arrangements.', 'It provides detailed examples and instructions for using index and match in Excel, highlighting its ability to handle unique data arrangements and precisely retrieve data.', 'Demonstrates the process of using index and match for data lookup, emphasizing its capability to handle non-standard data arrangements and provide precise data retrieval.', 'The chapter provides a detailed walkthrough of using index and match in Excel, demonstrating its ability to handle unique data arrangements and accurately retrieve data.']}], 'duration': 1203.052, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc28933566.jpg', 'highlights': ['The chapter explains the step-by-step process of using index and match functions for data lookup, showcasing the flexibility and advantages over VLOOKUP, with an emphasis on handling non-standard data arrangements.', 'It provides detailed examples and instructions for using index and match in Excel, highlighting its ability to handle unique data arrangements and precisely retrieve data.', 'The match function is used to retrieve the position of a specific value, such as E005, within a designated array, eliminating the need for manual entry.', 'The index function is employed to return the desired value, such as a name, from a specified column using the position obtained from the match function, demonstrating the practical application of the match function in conjunction with the index function.', 'Introduction of the concept of absolute referencing is made for complex formulas, showcasing the need to anchor specific cell references when dealing with multiple tables or values that shift positions.']}, {'end': 31440.311, 'segs': [{'end': 30484.144, 'src': 'embed', 'start': 30452.265, 'weight': 0, 'content': [{'end': 30453.765, 'text': 'So the row is blocked.', 'start': 30452.265, 'duration': 1.5}, {'end': 30459.529, 'text': 'And there you go.', 'start': 30458.688, 'duration': 0.841}, {'end': 30461.01, 'text': 'I got my complete answer.', 'start': 30459.889, 'duration': 1.121}, {'end': 30469.674, 'text': 'So I used index, I used match and I used reference and a small little trick of adding some numbers and referencing those numbers.', 'start': 30461.09, 'duration': 8.584}, {'end': 30475.738, 'text': 'By using this, I was able to use one formula and get the desired results.', 'start': 30471.115, 'duration': 4.623}, {'end': 30484.144, 'text': 'OK, the last thing, could you just try the same for this particular exercise, please, which we did here.', 'start': 30477.239, 'duration': 6.905}], 'summary': 'Used index, match, and reference to create one formula, achieving desired results.', 'duration': 31.879, 'max_score': 30452.265, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc30452265.jpg'}, {'end': 30672.241, 'src': 'embed', 'start': 30644.09, 'weight': 3, 'content': [{'end': 30651.112, 'text': 'Now, COUNTIF function is used when you have a criteria in a table and you want to measure that particular criteria,', 'start': 30644.09, 'duration': 7.022}, {'end': 30653.773, 'text': 'how many times it is repeated in a table?', 'start': 30651.112, 'duration': 2.661}, {'end': 30664.376, 'text': 'So for example you have a table maintained for your inventory and the inventory basically has the list of all the products in your stores.', 'start': 30654.293, 'duration': 10.083}, {'end': 30672.241, 'text': 'So you want to see how many products or how many number of products are there for say a TV or a fridge.', 'start': 30665.114, 'duration': 7.127}], 'summary': 'The countif function measures how many times a specific criteria is repeated in a table, such as counting the number of products in an inventory for a tv or a fridge.', 'duration': 28.151, 'max_score': 30644.09, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc30644090.jpg'}, {'end': 30718.621, 'src': 'embed', 'start': 30689.728, 'weight': 2, 'content': [{'end': 30693.07, 'text': 'Today we are going to learn AND, OR, NOR operators.', 'start': 30689.728, 'duration': 3.342}, {'end': 30697.553, 'text': "Most importantly, I'll be taking you all through AND, AND, OR operators.", 'start': 30693.47, 'duration': 4.083}, {'end': 30709.478, 'text': "If functions, so it is basically using a condition, how you'll be able to use that condition to give inputs when the condition is true or false.", 'start': 30698.155, 'duration': 11.323}, {'end': 30718.621, 'text': 'Nested if function is more about using multiple if functions, and how do you use multiple if functions to validate true or false,', 'start': 30709.998, 'duration': 8.623}], 'summary': 'Learning and, or, nor operators and nested if functions for validation.', 'duration': 28.893, 'max_score': 30689.728, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc30689728.jpg'}], 'start': 30136.618, 'title': 'Excel functions and troubleshooting', 'summary': 'Covers using a single formula for vlookup, troubleshooting issues with index and match functions, and applying logical operators and if functions in excel. it also demonstrates the application of absolute and mixed references, as well as the countif function for measuring criteria in a table.', 'chapters': [{'end': 30310.481, 'start': 30136.618, 'title': 'Vlookup using one single formula', 'summary': 'Discusses using a single formula for vlookup, applying index and match functions to drag the formula across, and how to properly use match and index functions to achieve the desired results.', 'duration': 173.863, 'highlights': ['The chapter discusses using a single formula for vlookup, applying index and match functions to drag the formula across, and how to properly use match and index functions to achieve the desired results.', 'Applying one index and match function to drag the formula across and display results, instead of using multiple rows and columns, is the key focus.', 'Using match and index functions with proper parameters, such as exact match and column number, is crucial for the formula to work perfectly.']}, {'end': 30689.648, 'start': 30310.981, 'title': 'Troubleshooting index and match functions', 'summary': 'Discusses troubleshooting issues with the index and match functions, demonstrating the use of absolute and mixed references, as well as the countif function for measuring criteria in a table.', 'duration': 378.667, 'highlights': ['The chapter discusses troubleshooting issues with the Index and Match functions, demonstrating the use of absolute and mixed references.', 'The chapter demonstrates the use of the COUNTIF function for measuring criteria in a table.']}, {'end': 30930.21, 'start': 30689.728, 'title': 'Logical operators and if functions in excel', 'summary': 'Covers the use of and, or, nor operators, as well as the application of if functions in excel to validate conditions, with examples of true and false values being generated based on logical tests.', 'duration': 240.482, 'highlights': ['The chapter covers the use of AND, OR, NOR operators, as well as the application of IF functions in Excel to validate conditions, with examples of true and false values being generated based on logical tests.', "The syntax of the IF function is explained as 'If logical test, what will happen if the value is true and what will happen if the value is false', providing a clear understanding of the function's application.", 'The demonstration includes a comparison of two cell values in Excel, with a clear representation of the true and false values generated based on the logical test.', 'Nested IF functions are discussed, emphasizing the usage of multiple IF functions to validate true or false conditions in Excel.', 'The demonstration provides a practical understanding of using logical operators and IF functions in Excel through a small table, enhancing comprehension and application of the functions.']}, {'end': 31440.311, 'start': 30932.091, 'title': 'Using if function in excel', 'summary': 'Explains how to use the if function in excel to automate decision-making based on logical tests, with emphasis on syntax accuracy and avoiding errors. it includes examples of applying the function to determine student grades and demonstrates handling multiple criteria with a simplified approach.', 'duration': 508.22, 'highlights': ['Explaining logical tests and outcomes for student grades', 'Demonstrating the syntax and arguments of the IF function', 'Handling errors and missing arguments in the IF function', 'Applying IF function with multiple criteria']}], 'duration': 1303.693, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc30136618.jpg', 'highlights': ['The chapter covers using a single formula for vlookup, applying index and match functions to drag the formula across, and how to properly use match and index functions to achieve the desired results.', 'The chapter discusses troubleshooting issues with the Index and Match functions, demonstrating the use of absolute and mixed references.', 'The chapter covers the use of AND, OR, NOR operators, as well as the application of IF functions in Excel to validate conditions, with examples of true and false values being generated based on logical tests.', 'The chapter demonstrates the use of the COUNTIF function for measuring criteria in a table.']}, {'end': 33105.574, 'segs': [{'end': 31525.654, 'src': 'embed', 'start': 31440.692, 'weight': 0, 'content': [{'end': 31444.934, 'text': 'We spoke about something in the agenda today which is using operators.', 'start': 31440.692, 'duration': 4.242}, {'end': 31447.936, 'text': "So I'm going to introduce you to operators.", 'start': 31445.454, 'duration': 2.482}, {'end': 31452.539, 'text': 'There are two operators or and are two operators.', 'start': 31447.976, 'duration': 4.563}, {'end': 31456.061, 'text': "Now let's understand what these operators are first.", 'start': 31453.419, 'duration': 2.642}, {'end': 31475.341, 'text': "Let's just check if it is built in.", 'start': 31472.699, 'duration': 2.642}, {'end': 31477.023, 'text': 'Okay, there you go, I have an OR.', 'start': 31475.481, 'duration': 1.542}, {'end': 31484.49, 'text': "Okay, so there is this operator OR which I'm looking at.", 'start': 31481.247, 'duration': 3.243}, {'end': 31492.574, 'text': "So it is basically OR open bracket and you can enter multiple logical values, right? That's what it's showing here.", 'start': 31484.93, 'duration': 7.644}, {'end': 31494.474, 'text': "That's the syntax which I was trying to show you.", 'start': 31492.594, 'duration': 1.88}, {'end': 31499.495, 'text': 'So I have OR, open brackets, I have logical 1, logical 2 and so on.', 'start': 31495.015, 'duration': 4.48}, {'end': 31503.076, 'text': 'So what it does is it checks whether any of the arguments are true.', 'start': 31499.936, 'duration': 3.14}, {'end': 31511.378, 'text': "So all the logical values it's going to check whether it is true and returns true or false accordingly.", 'start': 31503.556, 'duration': 7.822}, {'end': 31520.931, 'text': 'It returns false only if all arguments are false, okay? Which means If one of the logical value meets, it will return a true.', 'start': 31511.398, 'duration': 9.533}, {'end': 31525.654, 'text': 'And only if both the logical test, it will return as false.', 'start': 31521.471, 'duration': 4.183}], 'summary': 'Introduction to logical operators: or checks if any argument is true, returns true or false accordingly.', 'duration': 84.962, 'max_score': 31440.692, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc31440692.jpg'}, {'end': 32129.487, 'src': 'embed', 'start': 32100.85, 'weight': 1, 'content': [{'end': 32102.492, 'text': 'Not simple, right? A little complicated.', 'start': 32100.85, 'duration': 1.642}, {'end': 32107.537, 'text': "And as I've been telling you all the other chapters, you need to practice to get a hang of it.", 'start': 32103.012, 'duration': 4.525}, {'end': 32110.953, 'text': 'Do not worry if you do not get it in the first instance.', 'start': 32108.171, 'duration': 2.782}, {'end': 32114.656, 'text': 'Everyone gets that only after some time of practice.', 'start': 32111.474, 'duration': 3.182}, {'end': 32117.138, 'text': "But what's important is to understand the logic.", 'start': 32115.056, 'duration': 2.082}, {'end': 32119.019, 'text': 'What does an AND operator do??', 'start': 32117.558, 'duration': 1.461}, {'end': 32120.54, 'text': 'What does an OR operator do?', 'start': 32119.219, 'duration': 1.321}, {'end': 32124.123, 'text': 'And when do you use AND or OR operator to get the results out?', 'start': 32121.061, 'duration': 3.062}, {'end': 32129.487, 'text': 'Okay, so here again, SMNode is my input.', 'start': 32125.604, 'duration': 3.883}], 'summary': 'Learning logic gates: practice to understand and and or operators.', 'duration': 28.637, 'max_score': 32100.85, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc32100850.jpg'}, {'end': 32246.665, 'src': 'embed', 'start': 32217.657, 'weight': 4, 'content': [{'end': 32225.56, 'text': "So what I have here is, let's take these guys are no longer in the school, they work for a company.", 'start': 32217.657, 'duration': 7.903}, {'end': 32235.122, 'text': "And, as much as you are aware, or, if you're not aware, even similar to how you're being graded in the school or in colleges.", 'start': 32226.42, 'duration': 8.702}, {'end': 32237.803, 'text': 'even in companies, you are graded at the end of the year.', 'start': 32235.122, 'duration': 2.681}, {'end': 32239.403, 'text': 'So, basis your grade.', 'start': 32238.283, 'duration': 1.12}, {'end': 32243.744, 'text': 'whether you get an A or B or C, you get bonus, okay?', 'start': 32239.403, 'duration': 4.341}, {'end': 32246.665, 'text': 'So I have the list of employees.', 'start': 32244.204, 'duration': 2.461}], 'summary': 'Employees are graded at the end of the year and receive bonuses based on their performance.', 'duration': 29.008, 'max_score': 32217.657, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc32217657.jpg'}, {'end': 32560.72, 'src': 'embed', 'start': 32533.173, 'weight': 2, 'content': [{'end': 32542.178, 'text': "Then instead of me giving value if true, I've used a formula and if without that, when the value is not true, I've given no bonus.", 'start': 32533.173, 'duration': 9.005}, {'end': 32545.58, 'text': "So that's how I've solved this particular problem.", 'start': 32542.698, 'duration': 2.882}, {'end': 32549.842, 'text': 'Okay, I think you should be getting comfortable now with if functions.', 'start': 32546.2, 'duration': 3.642}, {'end': 32552.956, 'text': 'So let me just complicate it a little bit more for you.', 'start': 32550.515, 'duration': 2.441}, {'end': 32560.72, 'text': 'Okay, so here I have a sales statistics for you.', 'start': 32554.737, 'duration': 5.983}], 'summary': 'Solved problem using formula, introduced sales statistics.', 'duration': 27.547, 'max_score': 32533.173, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc32533173.jpg'}, {'end': 32632.375, 'src': 'embed', 'start': 32589.236, 'weight': 5, 'content': [{'end': 32601.76, 'text': "Anyone who's done a sales of less greater than 500 falls into good category and someone who's between 2500 and 5000 is an average sales.", 'start': 32589.236, 'duration': 12.524}, {'end': 32604.021, 'text': 'So what shall we do??', 'start': 32601.94, 'duration': 2.081}, {'end': 32606.942, 'text': 'How do you use if function and get this done??', 'start': 32604.201, 'duration': 2.741}, {'end': 32616.128, 'text': 'Okay, did you try??', 'start': 32614.787, 'duration': 1.341}, {'end': 32617.548, 'text': 'Very difficult?', 'start': 32616.628, 'duration': 0.92}, {'end': 32621.37, 'text': 'Thought through? No?', 'start': 32618.369, 'duration': 3.001}, {'end': 32628.874, 'text': "I'm sure it would have been a little difficult for you.", 'start': 32626.713, 'duration': 2.161}, {'end': 32632.375, 'text': 'Let me explain and help you out over here.', 'start': 32629.674, 'duration': 2.701}], 'summary': 'Sales between 500-2500 categorized as good or average. discussion on using if function.', 'duration': 43.139, 'max_score': 32589.236, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc32589236.jpg'}, {'end': 32942.934, 'src': 'embed', 'start': 32907.182, 'weight': 8, 'content': [{'end': 32911.104, 'text': 'Now in this table I want like the way I was explaining what COUNTIF is.', 'start': 32907.182, 'duration': 3.922}, {'end': 32913.286, 'text': 'I have a table just with numbers.', 'start': 32911.584, 'duration': 1.702}, {'end': 32920.309, 'text': 'So what I want you to do or what I am trying to get out here is I want to see how many times 3 is repeated in this.', 'start': 32913.746, 'duration': 6.563}, {'end': 32927.195, 'text': 'So in this case I just want to say equal to COUNTIF open brackets.', 'start': 32921.071, 'duration': 6.124}, {'end': 32930.436, 'text': 'Select the table.', 'start': 32929.676, 'duration': 0.76}, {'end': 32942.934, 'text': "That's how COUNTIF is, it doesn't start with if, it is COUNTIF, give the range, and the criteria is 3.", 'start': 32931.589, 'duration': 11.345}], 'summary': 'Explaining countif with an example of counting the number 3 in a table of numbers.', 'duration': 35.752, 'max_score': 32907.182, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc32907182.jpg'}], 'start': 31440.692, 'title': 'Logical operations in excel', 'summary': "Covers the introduction to the logical operator 'or', its syntax and functionality, as well as the usage of logical operators like or and and in excel formulas. it includes examples of age, nationality, voting eligibility, and bonus calculation for employees based on their grades and salaries. additionally, it explains if functions for calculating bonuses, implementing logical tests for different conditions, utilizing nested if functions for categorizing sales statistics, and employing countif to count occurrences of specific numbers in a table.", 'chapters': [{'end': 31525.654, 'start': 31440.692, 'title': 'Introduction to logical operators', 'summary': "Introduces the logical operator 'or' and explains its syntax and functionality, highlighting that it returns true if any argument is true and false only if all arguments are false.", 'duration': 84.962, 'highlights': ["The 'OR' operator allows entering multiple logical values and returns true if any of the arguments are true.", 'It returns false only if all arguments are false, indicating that it checks whether any of the arguments are true.', "The syntax of the 'OR' operator is demonstrated as 'OR(open brackets, logical 1, logical 2, and so on).'"]}, {'end': 32376.977, 'start': 31526.454, 'title': 'Logical operators and excel formulas', 'summary': 'Explains the usage of logical operators like or and and in excel formulas, using examples of age, nationality, voting eligibility, and bonus calculation for employees based on their grades and salaries.', 'duration': 850.523, 'highlights': ['The chapter explains the usage of logical operators like OR and AND in Excel formulas', 'Examples of age, nationality, voting eligibility, and bonus calculation for employees based on their grades and salaries are provided', 'Demonstration of the OR and AND operators in Excel formulas']}, {'end': 33105.574, 'start': 32377.478, 'title': 'Understanding if functions and nested if functions', 'summary': 'Covers the usage of if functions for calculating bonuses, implementing logical tests for different conditions, and utilizing nested if functions for categorizing sales statistics and employing countif to count occurrences of specific numbers in a table.', 'duration': 728.096, 'highlights': ['The chapter covers the usage of if functions for calculating bonuses', 'Utilizing nested if functions for categorizing sales statistics', 'Implementing COUNTIF to count occurrences of specific numbers in a table']}], 'duration': 1664.882, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc31440692.jpg', 'highlights': ["The 'OR' operator allows entering multiple logical values and returns true if any of the arguments are true.", 'The chapter explains the usage of logical operators like OR and AND in Excel formulas', 'The chapter covers the usage of if functions for calculating bonuses', 'It returns false only if all arguments are false, indicating that it checks whether any of the arguments are true.', 'Examples of age, nationality, voting eligibility, and bonus calculation for employees based on their grades and salaries are provided', 'Utilizing nested if functions for categorizing sales statistics', 'Demonstration of the OR and AND operators in Excel formulas', "The syntax of the 'OR' operator is demonstrated as 'OR(open brackets, logical 1, logical 2, and so on).'", 'Implementing COUNTIF to count occurrences of specific numbers in a table']}, {'end': 35455.701, 'segs': [{'end': 33267.374, 'src': 'embed', 'start': 33240.112, 'weight': 9, 'content': [{'end': 33247.555, 'text': 'and what happens because of doing the automation is the time taken for doing the reports will reduce a lot.', 'start': 33240.112, 'duration': 7.443}, {'end': 33254.739, 'text': 'and again the accuracy will be more, because when you start doing a report which take hours together,', 'start': 33247.555, 'duration': 7.184}, {'end': 33258.841, 'text': 'there is always a chance that you might commit a error.', 'start': 33254.739, 'duration': 4.102}, {'end': 33267.374, 'text': 'so when you automate tasks, if you run the macro 100 times also, it will run the same set of steps without any monotony.', 'start': 33258.841, 'duration': 8.533}], 'summary': 'Automation reduces report time, improves accuracy, and ensures consistent performance.', 'duration': 27.262, 'max_score': 33240.112, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc33240112.jpg'}, {'end': 33429.622, 'src': 'embed', 'start': 33402.938, 'weight': 3, 'content': [{'end': 33414.246, 'text': 'if you click on the visual basic icon, it will again take you to visual basic editor, same as alt f11, and then you have macros.', 'start': 33402.938, 'duration': 11.308}, {'end': 33417.669, 'text': 'this gives you the dialog box with the existing macros.', 'start': 33414.246, 'duration': 3.423}, {'end': 33420.75, 'text': "currently i didn't have any macros in this workbook, so it's not showing up anything.", 'start': 33417.669, 'duration': 3.081}, {'end': 33426.661, 'text': 'and then you have all these record macro, all these macro related functionalities.', 'start': 33422.559, 'duration': 4.102}, {'end': 33429.622, 'text': 'so this developer tab is very useful.', 'start': 33426.661, 'duration': 2.961}], 'summary': 'The developer tab in visual basic editor provides macro-related functionalities, including record macro.', 'duration': 26.684, 'max_score': 33402.938, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc33402938.jpg'}, {'end': 33510.667, 'src': 'embed', 'start': 33455.22, 'weight': 2, 'content': [{'end': 33459.263, 'text': 'all you need to do is to run turn on the macro recorder,', 'start': 33455.22, 'duration': 4.043}, {'end': 33466.729, 'text': 'perform the actions that comprise the task you want to automate and then turn off the macro recorder when your work is finished.', 'start': 33459.263, 'duration': 7.466}, {'end': 33474.53, 'text': 'while a macro recorder is on, every action you do like selecting a cell, selecting a number, formatting,', 'start': 33466.729, 'duration': 7.801}, {'end': 33480.892, 'text': 'everything gets recorded and it is represented in the form of a VBA code.', 'start': 33474.53, 'duration': 6.362}, {'end': 33485.316, 'text': 'so if you see here you have an option called record macro.', 'start': 33480.892, 'duration': 4.424}, {'end': 33491.038, 'text': "so this is a really powerful tool because even if you don't know the VBA coding,", 'start': 33485.316, 'duration': 5.722}, {'end': 33498.023, 'text': 'you can record the set of actions and that will be shown in the form of a VBA code.', 'start': 33491.038, 'duration': 6.985}, {'end': 33506.905, 'text': 'so most of the times, around 25 to 30 percent of the automation work can be done using the record macro option.', 'start': 33498.678, 'duration': 8.227}, {'end': 33510.667, 'text': 'however, record macro habits, fallbacks, okay.', 'start': 33506.905, 'duration': 3.762}], 'summary': 'Using the macro recorder, 25-30% of automation work can be done without vba coding.', 'duration': 55.447, 'max_score': 33455.22, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc33455220.jpg'}, {'end': 34387.468, 'src': 'heatmap', 'start': 33998.727, 'weight': 1, 'content': [{'end': 34002.909, 'text': 'and from visual basic editor you can select the macro and you can run it.', 'start': 33998.727, 'duration': 4.182}, {'end': 34006.191, 'text': 'and the third one is we have assigned a shortcut key.', 'start': 34002.909, 'duration': 3.282}, {'end': 34009.752, 'text': 'So what is the shortcut key that we assigned for this?', 'start': 34007.731, 'duration': 2.021}, {'end': 34012.694, 'text': "It's CTRL-SHIFT-L.", 'start': 34010.853, 'duration': 1.841}, {'end': 34018.038, 'text': 'So let me press CTRL-SHIFT-L on my laptop.', 'start': 34013.215, 'duration': 4.823}, {'end': 34021.581, 'text': 'So CTRL-SHIFT and then I press CTRL-SHIFT-L.', 'start': 34018.438, 'duration': 3.143}, {'end': 34029.906, 'text': 'See, as soon as I just press CTRL-SHIFT-L, it has done the task for me.', 'start': 34023.262, 'duration': 6.644}, {'end': 34033.949, 'text': "So whatever macro is recorded it's working.", 'start': 34030.847, 'duration': 3.102}, {'end': 34036.211, 'text': 'So let me just run it again.', 'start': 34034.71, 'duration': 1.501}, {'end': 34040.759, 'text': 'let me just clear off the entire data.', 'start': 34037.938, 'duration': 2.821}, {'end': 34047.761, 'text': 'okay, let me copy the base data again.', 'start': 34040.759, 'duration': 7.002}, {'end': 34052.242, 'text': 'okay, so now let me run the macro using the other option.', 'start': 34047.761, 'duration': 4.481}, {'end': 34056.844, 'text': 'okay, so now you see, you have all the data in a1.', 'start': 34052.242, 'duration': 4.602}, {'end': 34058.364, 'text': "you don't have any data in other things.", 'start': 34056.844, 'duration': 1.52}, {'end': 34058.944, 'text': 'you are all.', 'start': 34058.364, 'duration': 0.58}, {'end': 34062.766, 'text': 'you have all the data in a1, so macros.', 'start': 34058.944, 'duration': 3.822}, {'end': 34068.346, 'text': 'so macro one is the macro which i created just now and then click on Run.', 'start': 34062.766, 'duration': 5.58}, {'end': 34072.187, 'text': 'See now the texture columns has worked.', 'start': 34069.506, 'duration': 2.681}, {'end': 34079.708, 'text': 'Now we have done this action with using a macro and we have not written any code manually.', 'start': 34072.827, 'duration': 6.881}, {'end': 34081.349, 'text': 'We just use the record option.', 'start': 34080.008, 'duration': 1.341}, {'end': 34087.01, 'text': 'And it we got the VBA code and we are using the macro.', 'start': 34082.389, 'duration': 4.621}, {'end': 34088.49, 'text': 'So very simple.', 'start': 34087.73, 'duration': 0.76}, {'end': 34095.212, 'text': 'This is just recording the macro and most of the time you can use the record macro option for 20 to 30% of your work.', 'start': 34088.53, 'duration': 6.682}, {'end': 34102.617, 'text': 'what are the disadvantages of of using the macro recorder?', 'start': 34098.436, 'duration': 4.181}, {'end': 34116.08, 'text': "I'll just show you first thing, when you use a record option, the code will be too long because it will exactly convert the action into the VBA code.", 'start': 34102.617, 'duration': 13.463}, {'end': 34125.141, 'text': 'and second, and the most important thing, when you record a macro, it will work only for that specific range.', 'start': 34116.08, 'duration': 9.061}, {'end': 34132.808, 'text': 'so what I mean to say is, for example, if you have 10 rows and 10 columns and you recorded a macro for that,', 'start': 34125.141, 'duration': 7.667}, {'end': 34135.989, 'text': 'it will work only for that 10 rows and 10 columns.', 'start': 34132.808, 'duration': 3.181}, {'end': 34142.931, 'text': 'for example, tomorrow you have 11th row and 12th row and you run the macro and you will not get the desired output.', 'start': 34135.989, 'duration': 6.942}, {'end': 34148.353, 'text': 'so even if you have additional columns, if you have additional rows, you will not get the desired output.', 'start': 34142.931, 'duration': 5.422}, {'end': 34152.394, 'text': 'and if there is a difference in the format also, you will not get the desired result.', 'start': 34148.353, 'duration': 4.041}, {'end': 34161.172, 'text': 'So in a real world, in a real time scenario, this will not be possible, because you cannot say the report should have only 10 lines every time,', 'start': 34153.129, 'duration': 8.043}, {'end': 34165.053, 'text': 'because sometimes you might get a report with 100 lines of data.', 'start': 34161.172, 'duration': 3.881}, {'end': 34167.574, 'text': 'so your macro should be dynamic.', 'start': 34165.053, 'duration': 2.521}, {'end': 34173.296, 'text': 'so whatever the range, whatever the number of rows, whatever the count and what are the fields you have,', 'start': 34167.574, 'duration': 5.722}, {'end': 34183.991, 'text': 'the macro should be able to dynamically count the range and it should do the action irrespective of the number of rows and the kind of data.', 'start': 34173.296, 'duration': 10.695}, {'end': 34195.28, 'text': 'so for that we need to learn the further concepts of VBA and we need to learn how we need to write the code in VBA so that the macro will be dynamic.', 'start': 34183.991, 'duration': 11.289}, {'end': 34202.345, 'text': 'okay. so these are the main things to keep in mind.', 'start': 34195.28, 'duration': 7.065}, {'end': 34214.41, 'text': "I'll show you few other programs as well using macro recorder and then assignment of shortcut key to a macro, which I already showed,", 'start': 34202.345, 'duration': 12.065}, {'end': 34217.953, 'text': 'and then assigning a macro to a command button.', 'start': 34214.41, 'duration': 3.543}, {'end': 34219.214, 'text': 'so this is the same macro.', 'start': 34217.953, 'duration': 1.261}, {'end': 34222.276, 'text': "now we have let's see how we can assign this to a command button.", 'start': 34219.214, 'duration': 3.062}, {'end': 34227.541, 'text': "command button is nothing, but it's a button which you can click.", 'start': 34222.276, 'duration': 5.265}, {'end': 34236.147, 'text': 'so you, instead of running it using a shortcut key or directly from the macros tab, you want to click on a button and you want this macro to run.', 'start': 34227.541, 'duration': 8.606}, {'end': 34238.349, 'text': "so let's see how you can do that.", 'start': 34236.147, 'duration': 2.202}, {'end': 34239.57, 'text': 'let me just clear this data.', 'start': 34238.349, 'duration': 1.221}, {'end': 34256.434, 'text': "I'll again copy the data, okay, so now I want a button here like, say, some run macro.", 'start': 34241.263, 'duration': 15.171}, {'end': 34260.057, 'text': 'so if I click on the trend macro, I want the output.', 'start': 34256.434, 'duration': 3.623}, {'end': 34265.14, 'text': 'so in that case, here on the developer tab, you have something called insert.', 'start': 34260.057, 'duration': 5.083}, {'end': 34268.503, 'text': 'if you click on insert, you have form controls.', 'start': 34265.14, 'duration': 3.363}, {'end': 34271.469, 'text': 'so you can use these controls now.', 'start': 34269.488, 'duration': 1.981}, {'end': 34275.252, 'text': 'in the first one, you have button form control.', 'start': 34271.469, 'duration': 3.783}, {'end': 34281.215, 'text': 'click on the button form control and then you are getting this plus sign.', 'start': 34275.252, 'duration': 5.963}, {'end': 34284.097, 'text': 'you can draw this command button wherever you want.', 'start': 34281.215, 'duration': 2.882}, {'end': 34290.901, 'text': "so I'll just draw the button.", 'start': 34284.097, 'duration': 6.804}, {'end': 34293.442, 'text': 'just cancel this.', 'start': 34290.901, 'duration': 2.541}, {'end': 34295.844, 'text': 'okay, if I want to change the name, I can just click.', 'start': 34293.442, 'duration': 2.402}, {'end': 34304.175, 'text': "so I'll say run macro.", 'start': 34299.773, 'duration': 4.402}, {'end': 34306.797, 'text': 'so this is the command button which I have created now.', 'start': 34304.175, 'duration': 2.622}, {'end': 34311.059, 'text': 'so I want to assign the macro to this command button.', 'start': 34306.797, 'duration': 4.262}, {'end': 34316.922, 'text': "so I'll just click on it and then right click.", 'start': 34311.059, 'duration': 5.863}, {'end': 34319.984, 'text': 'you know something here called assign macro.', 'start': 34316.922, 'duration': 3.062}, {'end': 34328.749, 'text': 'okay, and the macro you want to assign is macro one.', 'start': 34319.984, 'duration': 8.765}, {'end': 34334.477, 'text': 'okay, so now we have the macro assigned to this command button.', 'start': 34328.749, 'duration': 5.728}, {'end': 34341.599, 'text': 'okay, so when i click on this command button, it runs the macro one macro.', 'start': 34334.477, 'duration': 7.122}, {'end': 34344.7, 'text': 'so now this is the data which we have.', 'start': 34341.599, 'duration': 3.101}, {'end': 34346.641, 'text': "so let's see whether it works or not.", 'start': 34344.7, 'duration': 1.941}, {'end': 34347.641, 'text': 'run macro.', 'start': 34346.641, 'duration': 1}, {'end': 34351.283, 'text': 'so as soon as i click on run macro, it did the same action.', 'start': 34347.641, 'duration': 3.642}, {'end': 34357.445, 'text': 'it just run the macro one, which is for text to columns.', 'start': 34351.283, 'duration': 6.162}, {'end': 34369.801, 'text': "So now I'll show you one more program how to record the macros.", 'start': 34364.059, 'duration': 5.742}, {'end': 34375.703, 'text': "I'll just delete this data.", 'start': 34374.443, 'duration': 1.26}, {'end': 34383.647, 'text': "I'll just delete this for the time being.", 'start': 34379.885, 'duration': 3.762}, {'end': 34387.468, 'text': "So let's see one more program.", 'start': 34384.167, 'duration': 3.301}], 'summary': 'Using macro recorder to run tasks and assigning shortcut keys and command buttons for macros in vba.', 'duration': 388.741, 'max_score': 33998.727, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc33998727.jpg'}, {'end': 34227.541, 'src': 'embed', 'start': 34202.345, 'weight': 7, 'content': [{'end': 34214.41, 'text': "I'll show you few other programs as well using macro recorder and then assignment of shortcut key to a macro, which I already showed,", 'start': 34202.345, 'duration': 12.065}, {'end': 34217.953, 'text': 'and then assigning a macro to a command button.', 'start': 34214.41, 'duration': 3.543}, {'end': 34219.214, 'text': 'so this is the same macro.', 'start': 34217.953, 'duration': 1.261}, {'end': 34222.276, 'text': "now we have let's see how we can assign this to a command button.", 'start': 34219.214, 'duration': 3.062}, {'end': 34227.541, 'text': "command button is nothing, but it's a button which you can click.", 'start': 34222.276, 'duration': 5.265}], 'summary': 'Demonstrating the use of macro recorder and assigning macro to a command button.', 'duration': 25.196, 'max_score': 34202.345, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc34202345.jpg'}, {'end': 34800.887, 'src': 'embed', 'start': 34745.91, 'weight': 0, 'content': [{'end': 34752.734, 'text': 'let me just take you through a quick recap on what we have learned today.', 'start': 34745.91, 'duration': 6.824}, {'end': 34754.855, 'text': 'so what is vba?', 'start': 34752.734, 'duration': 2.121}, {'end': 34764.454, 'text': 'so, vba is a programming language developed by Microsoft to automate the task that you do in the application supported by Microsoft.', 'start': 34754.855, 'duration': 9.599}, {'end': 34773.457, 'text': 'So not only in Excel, even if you can use VBA in MS Access, Outlook and MS Word as well.', 'start': 34764.994, 'duration': 8.463}, {'end': 34785.642, 'text': 'So what is a macro? So a macro is nothing but a code or a set of steps written in the VBA programming language in order to automate the task.', 'start': 34774.438, 'duration': 11.204}, {'end': 34789.88, 'text': 'What macros can do for you? We have already discussed.', 'start': 34786.678, 'duration': 3.202}, {'end': 34795.023, 'text': 'We can automate the repetitive task using the macros.', 'start': 34790.56, 'duration': 4.463}, {'end': 34797.264, 'text': 'Exploring visual basic editor.', 'start': 34795.824, 'duration': 1.44}, {'end': 34800.887, 'text': 'I want to show few more things in visual basic editor.', 'start': 34798.185, 'duration': 2.702}], 'summary': 'Vba is a microsoft programming language to automate tasks in excel, access, outlook, and word using macros for repetitive tasks.', 'duration': 54.977, 'max_score': 34745.91, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc34745910.jpg'}, {'end': 35426.653, 'src': 'embed', 'start': 35371.505, 'weight': 10, 'content': [{'end': 35377.608, 'text': 'so this is the major drawback which we have if we use only record macro option.', 'start': 35371.505, 'duration': 6.103}, {'end': 35384.671, 'text': 'so this is why record macro option can do only 20 to 30 percent of your automation work.', 'start': 35377.608, 'duration': 7.063}, {'end': 35386.772, 'text': 'again, you need to edit the code.', 'start': 35384.671, 'duration': 2.101}, {'end': 35397.297, 'text': 'you need to write the code in your own way so that the code is dynamic and it works for any number of rows and any number of columns.', 'start': 35387.39, 'duration': 9.907}, {'end': 35401.7, 'text': 'so this is one thing we need to always keep in mind.', 'start': 35397.297, 'duration': 4.403}, {'end': 35408.785, 'text': "and one more thing whenever you are writing a macro, it's important that which version of excel you are using.", 'start': 35401.7, 'duration': 7.085}, {'end': 35414.789, 'text': 'for example, if you are using an excel 2007 version and you have done a macro,', 'start': 35408.785, 'duration': 6.004}, {'end': 35423.51, 'text': "and if the other user to whom you have given the macro might have excel 2003, then there is a chance that the macro didn't run in his,", 'start': 35414.789, 'duration': 8.721}, {'end': 35425.752, 'text': "don't run in his system.", 'start': 35423.51, 'duration': 2.242}, {'end': 35426.653, 'text': 'okay, why?', 'start': 35425.752, 'duration': 0.901}], 'summary': 'Using the record macro option can automate 20-30% of the work, but further code editing is necessary for dynamic functionality. compatibility issues may arise between different versions of excel.', 'duration': 55.148, 'max_score': 35371.505, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc35371505.jpg'}], 'start': 33105.574, 'title': 'Vba for microsoft applications and excel macros', 'summary': 'Introduces vba as a powerful tool for automating operations in microsoft office applications, emphasizing its ability to reduce time for report automation and improve accuracy. it covers using visual basic editor in excel for creating and running macros, including the advantages and disadvantages of using the macro recorder, which can automate around 25 to 30 percent of tasks. additionally, it discusses recording and running vba macros, using macro recorder for program automation, and provides an overview of vba programming and its limitations.', 'chapters': [{'end': 33267.374, 'start': 33105.574, 'title': 'Introduction to vba for microsoft applications', 'summary': 'Introduces vba as a powerful programming language by microsoft office to automate operations in applications like excel, ms access, word, and highlights its ability to reduce time for report automation and improve accuracy.', 'duration': 161.8, 'highlights': ['VBA is a powerful language that enables you to control Excel in countless ways, and it is an object-oriented programming language, allowing automation of tasks in Microsoft applications like Access, Word, PowerPoint, and Outlook.', 'A macro is a sequence of VBA commands used to automate tasks, reducing the time taken for reports and improving accuracy by eliminating manual repetition.']}, {'end': 33810.887, 'start': 33267.374, 'title': 'Using visual basic editor for excel macros', 'summary': "Covers the use of visual basic editor (vbe) in excel for creating and running macros, including how to access the vbe, add the developer tab, record macros, and the advantages and disadvantages of using the macro recorder, which can automate around 25 to 30 percent of tasks, and how to record a macro to automate the 'text to columns' task.", 'duration': 543.513, 'highlights': ['The chapter covers the use of Visual Basic Editor (VBE) in Excel for creating and running macros, including how to access the VBE, add the developer tab, record macros, and the advantages and disadvantages of using the macro recorder, which can automate around 25 to 30 percent of tasks.', 'To access the Visual Basic Editor in Excel, you need to press Alt F11, which opens the VBE, an embedded development environment within the Excel workbook that allows writing and editing code.', 'The developer tab is essential for macro-related functionalities in Excel, providing access to the Visual Basic icon and the option to record macros, which can automate tasks without requiring VBA coding and can automate around 25 to 30 percent of tasks.', 'The macro recorder is a valuable tool that allows users to record their actions in Excel, such as selecting cells, formatting, and other actions, and then converts them into VBA code, automating tasks and reducing the need for manual coding.', "It's important to be careful when using the macro recorder, as it records every action performed, including mouse movements, and it's vital to ensure that only the required steps are being recorded to avoid unnecessary code being generated."]}, {'end': 34202.345, 'start': 33811.843, 'title': 'Recording and running vba macros', 'summary': 'Covers the process of recording and running vba macros in excel, demonstrating how to record a macro, view the generated vba code, and run the macro using different methods. it also highlights the limitations of using the macro recorder and the need for dynamic macros in real-world scenarios.', 'duration': 390.502, 'highlights': ['The chapter covers the process of recording and running VBA macros in Excel, demonstrating how to record a macro, view the generated VBA code, and run the macro using different methods.', 'It also highlights the limitations of using the macro recorder and the need for dynamic macros in real-world scenarios.']}, {'end': 34720.284, 'start': 34202.345, 'title': 'Using macro recorder for program automation', 'summary': 'Covers using macro recorder to assign shortcuts, commands, and buttons to run macros, demonstrating how to assign a macro to a command button and how to record a macro for sorting data, showcasing the efficiency of using macros for task automation.', 'duration': 517.939, 'highlights': ['Demonstrates assigning a macro to a command button to run it by clicking, showcasing the practical application of macros for task automation.', 'Illustrates the process of recording a macro for sorting data, emphasizing the efficiency and time-saving benefits of using macros for repetitive tasks.', 'Explains the limitations of the record macro option for real-time report automation, highlighting the need for dynamic macros in complex automation tasks.']}, {'end': 35455.701, 'start': 34720.845, 'title': 'Vba programming overview and limitations', 'summary': 'Covered an overview of vba programming, including its use in automating tasks in microsoft applications, the concept of macros and their limitations, and the drawbacks of using the record macro option for automation. it also emphasized the importance of writing dynamic code and considering excel version compatibility.', 'duration': 734.856, 'highlights': ['The importance of understanding VBA programming for automating tasks in Microsoft applications, including Excel, MS Access, Outlook, and MS Word, was emphasized. This can significantly improve efficiency in handling repetitive tasks and data manipulation.', 'The concept of macros as a set of steps written in VBA programming language to automate tasks and the ability to assign shortcut keys for quick execution were explained. It was emphasized that macros can automate repetitive tasks and save time.', 'The drawbacks of using the record macro option were highlighted, particularly the limitation of not dynamically adjusting to changes in the number of rows and columns. This was illustrated through an example of sorting data, where the recorded macro did not adapt to the increased number of rows, emphasizing the need for writing dynamic code.', 'The importance of considering Excel version compatibility when writing VBA code was stressed, as certain features and options may not be available across different versions, potentially leading to the failure of macros in different Excel versions.']}], 'duration': 2350.127, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc33105574.jpg', 'highlights': ['VBA is a powerful language for automating tasks in Microsoft applications.', 'A macro is a sequence of VBA commands used to automate tasks.', 'The macro recorder can automate around 25 to 30 percent of tasks.', 'The Visual Basic Editor (VBE) in Excel allows creating and running macros.', 'The developer tab provides access to the Visual Basic icon and macro recording.', 'The macro recorder converts actions into VBA code, automating tasks.', 'Recording and running VBA macros in Excel is covered in the chapter.', 'Assigning a macro to a command button showcases practical application.', 'Understanding VBA programming can significantly improve efficiency.', 'Macros can automate repetitive tasks and save time.', 'The limitations of the record macro option were highlighted.', 'Excel version compatibility is important when writing VBA code.']}, {'end': 38195.105, 'segs': [{'end': 35545.591, 'src': 'embed', 'start': 35517.349, 'weight': 0, 'content': [{'end': 35523.194, 'text': 'variables hold values of different data types that are specified when the variable is declared.', 'start': 35517.349, 'duration': 5.845}, {'end': 35528.498, 'text': "so data types I'll explain you in a bit so you will know what is a data type.", 'start': 35523.194, 'duration': 5.304}, {'end': 35534.382, 'text': 'so variable can hold different data types, meaning you have variables which can hold numbers.', 'start': 35528.498, 'duration': 5.884}, {'end': 35536.624, 'text': 'you have variables which can hold strings.', 'start': 35534.382, 'duration': 2.242}, {'end': 35545.591, 'text': 'so there are different data types and we will see in this session how we are going to define variables with different data types,', 'start': 35536.624, 'duration': 8.967}], 'summary': 'Variables hold different data types like numbers and strings.', 'duration': 28.242, 'max_score': 35517.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc35517349.jpg'}, {'end': 35658.617, 'src': 'embed', 'start': 35629.693, 'weight': 7, 'content': [{'end': 35635.375, 'text': 'so variables must begin with an alphabet, so always it should begin with a alphabet.', 'start': 35629.693, 'duration': 5.682}, {'end': 35640.484, 'text': 'They must contain only alphabets, numbers or the underscore symbol.', 'start': 35636.201, 'duration': 4.283}, {'end': 35641.785, 'text': 'No spaces.', 'start': 35641.185, 'duration': 0.6}, {'end': 35646.148, 'text': 'So a variable name can contain alphabets, numbers and underscore.', 'start': 35642.525, 'duration': 3.623}, {'end': 35650.711, 'text': 'But no other spaces and all special characters.', 'start': 35647.209, 'duration': 3.502}, {'end': 35653.933, 'text': 'They must not exceed 40 characters.', 'start': 35651.732, 'duration': 2.201}, {'end': 35658.617, 'text': "A variable name shouldn't be more than 40 characters.", 'start': 35655.214, 'duration': 3.403}], 'summary': 'Variable names must start with an alphabet, contain alphanumerics and underscore, and not exceed 40 characters.', 'duration': 28.924, 'max_score': 35629.693, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc35629693.jpg'}, {'end': 35943.94, 'src': 'embed', 'start': 35916.23, 'weight': 4, 'content': [{'end': 35918.952, 'text': 'to avoid the problems of misnaming variables,', 'start': 35916.23, 'duration': 2.722}, {'end': 35928.136, 'text': 'you can stipulate vba to generate error message while compilation whenever it encounters an undeclared variable.', 'start': 35918.952, 'duration': 9.184}, {'end': 35931.417, 'text': 'okay, so what explicit declaration means?', 'start': 35928.136, 'duration': 3.281}, {'end': 35936.175, 'text': 'we declare each and every variable before using it.', 'start': 35931.417, 'duration': 4.758}, {'end': 35943.94, 'text': 'so by doing this, when we, when we create any variable, or when we misspell any variable,', 'start': 35936.175, 'duration': 7.765}], 'summary': 'Explicitly declaring variables in vba prevents misnaming and generates error messages during compilation.', 'duration': 27.71, 'max_score': 35916.23, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc35916230.jpg'}, {'end': 36316.774, 'src': 'embed', 'start': 36279.615, 'weight': 5, 'content': [{'end': 36287.606, 'text': 'Local variables remain in existence only as long as the procedure is executing.', 'start': 36279.615, 'duration': 7.991}, {'end': 36289.568, 'text': 'so what is a local variable?', 'start': 36287.606, 'duration': 1.962}, {'end': 36294.311, 'text': 'a local variable is a variable that is declared within the procedure.', 'start': 36289.568, 'duration': 4.743}, {'end': 36312.126, 'text': 'so in this case the procedure here is the subroutine and here we are declaring test1 as string inside the.', 'start': 36294.311, 'duration': 17.815}, {'end': 36316.774, 'text': 'So here test1 is a local variable.', 'start': 36313.593, 'duration': 3.181}], 'summary': 'Local variables exist only during procedure execution.', 'duration': 37.159, 'max_score': 36279.615, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc36279615.jpg'}, {'end': 36377.727, 'src': 'embed', 'start': 36347.475, 'weight': 1, 'content': [{'end': 36354.119, 'text': 'A module level variable is declared in the declaration section of the module instead of within the procedure.', 'start': 36347.475, 'duration': 6.644}, {'end': 36362.945, 'text': 'Module level variables are available to all procedures within that module but not to the rest of the application.', 'start': 36355.12, 'duration': 7.825}, {'end': 36370.611, 'text': 'Module level variables remain in existence for the lifetime of the application and preserve their value.', 'start': 36364.066, 'duration': 6.545}, {'end': 36377.727, 'text': 'So what are module level variables? So in this case test 1 we said is a local variable.', 'start': 36371.551, 'duration': 6.176}], 'summary': "Module level variables are accessible to all procedures within a module and persist throughout the application's lifetime.", 'duration': 30.252, 'max_score': 36347.475, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc36347475.jpg'}, {'end': 37051.106, 'src': 'embed', 'start': 37019.756, 'weight': 6, 'content': [{'end': 37025.398, 'text': 'also, variant data type occupies more memory compared to the other data types.', 'start': 37019.756, 'duration': 5.642}, {'end': 37026.759, 'text': 'so variant.', 'start': 37025.398, 'duration': 1.361}, {'end': 37037.955, 'text': 'when we use a variant data type, it reduces the speed of the macro and also the variant data type consumes more memory space in the system.', 'start': 37026.759, 'duration': 11.196}, {'end': 37044.52, 'text': 'this is why we need to define the exact data type.', 'start': 37037.955, 'duration': 6.565}, {'end': 37051.106, 'text': 'this is the table which explains you with the type of data types we have and what is the,', 'start': 37044.52, 'duration': 6.586}], 'summary': 'Using variant data type reduces macro speed and consumes more memory space; precise data type definition is important.', 'duration': 31.35, 'max_score': 37019.756, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc37019756.jpg'}, {'end': 37304.083, 'src': 'embed', 'start': 37276.227, 'weight': 2, 'content': [{'end': 37283.493, 'text': 'however, a constant is a value in the macro that does not change during the execution of the macro.', 'start': 37276.227, 'duration': 7.266}, {'end': 37287.977, 'text': 'essentially, constants are variables that do not change, so variables.', 'start': 37283.493, 'duration': 4.484}, {'end': 37294.981, 'text': "in the execution of a macro, when you're doing the calculations, the values of the variables keep on changing.", 'start': 37289.02, 'duration': 5.961}, {'end': 37299.862, 'text': "however, a constant is a variable that doesn't change.", 'start': 37294.981, 'duration': 4.881}, {'end': 37302.662, 'text': 'the value is constant.', 'start': 37299.862, 'duration': 2.8}, {'end': 37304.083, 'text': 'so a constant is declared.', 'start': 37302.662, 'duration': 1.421}], 'summary': 'Constants in macros are variables with unchanging values.', 'duration': 27.856, 'max_score': 37276.227, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc37276227.jpg'}, {'end': 37732.582, 'src': 'embed', 'start': 37687.463, 'weight': 3, 'content': [{'end': 37710.953, 'text': "so let's say we have radius and then we want to calculate area of circle and then we want to calculate circumference of circle.", 'start': 37687.463, 'duration': 23.49}, {'end': 37718.274, 'text': 'okay, so whenever we have the radius we can calculate area and circumference.', 'start': 37710.953, 'duration': 7.321}, {'end': 37724.857, 'text': "so for area of circle, formula is pi r square and for circumference it's 2 pi r.", 'start': 37718.274, 'duration': 6.583}, {'end': 37730.46, 'text': 'where pi is a constant value, the value of pi is 3.141.', 'start': 37724.857, 'duration': 5.603}, {'end': 37732.582, 'text': "so let's see how we can write a program for this.", 'start': 37730.46, 'duration': 2.122}], 'summary': 'Calculate area and circumference of a circle using radius and pi value 3.141.', 'duration': 45.119, 'max_score': 37687.463, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc37687463.jpg'}], 'start': 35455.701, 'title': 'Vba variables and declarations', 'summary': "Covers vba variables, including their purpose, data types, naming conventions, variable declaration rules, variable scopes, data types' importance, handling constants, and practical programming examples.", 'chapters': [{'end': 35732.053, 'start': 35455.701, 'title': 'Vba variables and declarations', 'summary': 'Explains the importance of variables in vba, including their purpose, data types, and naming conventions, emphasizing the need for user-specific macro creation and compatibility across different versions, preparing the audience for practical application.', 'duration': 276.352, 'highlights': ['VBA stores data in memory using a variable.', 'Variables hold values of different data types.', 'Guidelines for naming variables: must begin with an alphabet, contain only alphabets, numbers, or underscore, not exceed 40 characters, and not be a reserved word.']}, {'end': 36248.632, 'start': 35732.053, 'title': 'Vba variable declaration', 'summary': 'Explains the rules for declaring vba variables, including the use of implicit and explicit declaration, emphasizing the importance of explicit declaration to avoid errors and the impact of using option explicit.', 'duration': 516.579, 'highlights': ['The importance of explicit declaration in VBA', 'Difference between implicit and explicit declaration in VBA', 'Impact of using option explicit in VBA']}, {'end': 36880.675, 'start': 36248.632, 'title': 'Understanding variable scopes in vba', 'summary': 'Explains the scope of variables in vba, distinguishing between local, module level, and global variables, and their respective accessibility within procedures and modules.', 'duration': 632.043, 'highlights': ["Local variables are accessible only within the procedure where they are declared, remaining in existence only during the procedure's execution.", 'Module level variables are available to all procedures within the module, retaining their value for the lifetime of the application.', 'Global variables, declared with the global statement, can be accessed by any code within the application, existing and retaining their value for the lifetime of the application.']}, {'end': 37245.945, 'start': 36880.675, 'title': 'Vba data types and variables', 'summary': 'Explains the importance of data types in vba, highlighting how not specifying a data type defaults to variant, which can affect program efficiency and memory consumption, and provides a detailed breakdown of key data types, their memory consumption, and typical use cases.', 'duration': 365.27, 'highlights': ['Variant data type occupies more memory compared to other data types', 'Specifying data types can impact the efficiency of the code', 'Explanation of major data types and their memory consumption']}, {'end': 37657.591, 'start': 37245.945, 'title': 'Constants and variables in macros', 'summary': 'Discusses the drawbacks of using a variant in macros, the concept of constants as unchanging values, and demonstrates using variables and constants to output dynamic results in a macro program.', 'duration': 411.646, 'highlights': ["Constants are variables that do not change during the execution of a macro, and their value is declared using the keyword 'const', for example, 'const pi as double = 3.14'.", 'Variables in macros can be used to store dynamic values, and their values may change during the execution of the macro.', 'Demonstrates using variables and constants to output dynamic results in a macro program by assigning values to variables, concatenating strings, and displaying the desired output.']}, {'end': 38195.105, 'start': 37657.591, 'title': 'Using constants and variables in programming', 'summary': 'Demonstrates the use of constants, variables, and data types in programming through examples of calculating the area and circumference of a circle with a given radius, using the constant value of pi as 3.141, and running a macro to display the results.', 'duration': 537.514, 'highlights': ['Using constants, variables, and data types in programming to calculate the area and circumference of a circle with a given radius, such as using the constant value of pi as 3.141 and running a macro to display the results.', 'Explaining the process of assigning values to variables, defining constants, and calculating the area and circumference of a circle with given radius values, exemplifying the use of double data type for decimal values and constant value of pi as 3.141.', 'Demonstrating the process of defining variables for radius, area, and circumference, including the use of the double data type for decimal values and the constant value of pi as 3.141, and then performing calculations to obtain the area and circumference of the circle.']}], 'duration': 2739.404, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/27dxBp0EgCc/pics/27dxBp0EgCc35455701.jpg', 'highlights': ['Variables hold values of different data types.', 'Module level variables are available to all procedures within the module.', 'Constants are variables that do not change during the execution of a macro.', 'Using constants, variables, and data types in programming to calculate the area and circumference of a circle.', 'The importance of explicit declaration in VBA', 'Local variables are accessible only within the procedure where they are declared.', 'Variant data type occupies more memory compared to other data types', 'Guidelines for naming variables: must begin with an alphabet, contain only alphabets, numbers, or underscore, not exceed 40 characters.']}], 'highlights': ['Covers major concepts in Microsoft Excel, including basic concepts, important formulas, data sorting and filtering techniques, data and file security, pivot tables, VBA macros, principles of charting, and creating dashboards for data analysis using Microsoft Excel.', 'Organizing employee details using a row to column or in lines system can streamline the process and improve accessibility.', "The sum total of all the students' weight is 475, calculated by adding individual weights manually and using the autosum function.", "Excel's find and replace feature saves time by efficiently replacing multiple instances of a specific text or data, as demonstrated by replacing 'M&A' with 'math' in a dataset of 120 rows, emphasizing the impracticality of manual replacement for larger datasets of 15,000 rows.", 'Demonstration of how the appearance of data in different cells can be deceiving, emphasizing the need to use the formula bar for accurate verification.', 'The chapter demonstrates how to calculate profit in Excel by using investment, total minus tax, and total plus tax, showing a negative value due to the difference between total minus tax and investment.', 'Data validation in Excel allows users to restrict certain values or allow users to pick up those values which are restricted, preventing errors and ensuring accurate data input.', 'The chapter explains conditional formatting in Excel, including the use of predefined functions to format cell ranges.', "Sorting data by quantity, date, items, shop name, and total sales using Excel's inbuilt functions", 'Filtering data in a census department involves breaking down the complete population data at the country level into states, cities, localities, and further layers to efficiently analyze specific population details.', 'Column charts effectively compare multiple data series, showcasing sales of cars and bikes month on month for 12 months through a chart.', 'Pivot tables enable creating reports for total orders by salesperson, facilitating detailed graph representation of salesperson performance.', 'VLOOKUP function retrieves data based on a common unique reference', 'The limitations of VLOOKUP are highlighted, emphasizing the need for index and match functions, especially when dealing with large datasets and avoiding the need to rearrange the data, providing a practical insight into the advantages of using index and match over VLOOKUP.', 'VBA is a powerful language for automating tasks in Microsoft applications.', 'Variables hold values of different data types.', 'Module level variables are available to all procedures within the module.', 'Constants are variables that do not change during the execution of a macro.', 'Using constants, variables, and data types in programming to calculate the area and circumference of a circle.']}