title
Advance Excel Tutorial for 2022 | Advanced Excel Course | Excel Tutorial For Beginners |Simplilearn
description
🔥Post Graduate Program In Data Analytics: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=AdvanceExcel-h_UBLvhszko&utm_medium=DescriptionFF&utm_source=youtube
🔥IIT Kanpur Professional Certificate Course In Data Analytics (India Only): https://www.simplilearn.com/iitk-professional-certificate-course-data-analytics?utm_campaign=AdvanceExcel-h_UBLvhszko&utm_medium=DescriptionFF&utm_source=youtube
🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=AdvanceExcel-h_UBLvhszko&utm_medium=DescriptionFF&utm_source=youtube
🔥Data Analyst Masters Program (Discount Code - YTBE15): https://www.simplilearn.com/data-analyst-masters-certification-training-course?utm_campaign=AdvanceExcel-h_UBLvhszko&utm_medium=DescriptionFF&utm_source=youtube
This video is based on the Advanced Excel Tutorial for 2022. This Advanced Excel Course will cover the major fundamentals of Excel and its Advanced Excel concepts as well. This updated tutorial is dedicatedly designed and organized to help both beginners and experienced to update and enhance their advanced excel skill set.
The video covers the following topics
00:00 Introduction to Advanced Excel Tutorial for 2022
This part of the Advanced Excel Tutorial for 2022 covers a basic introduction to all the Advanced excel Topics Covered on the session
01:45 Excel Basic Knowledge
This part of the Advanced Excel Tutorial for 2022 will cover the Excel fundamentals for beginners
18:27 Goal Seek In Excel
This part of the Advanced Excel Tutorial for 2022 will explain the goal seek formulas and methods
23:03 Excel Lookup Tutorial
This part of the Advanced Excel Tutorial for 2022 will cover the VLookUp, HLookUp, and XLookUp in Excel
1:03:23 Slicers In Excel
This part of the Advanced Excel Tutorial for 2022 will cover the Slicers in Excel concepts and implementation
1:07:46 Excel Budget Template
This part of the Advanced Excel Tutorial for 2022 will help you create a Budget worksheet for your needs
1:14:50 Excel MIS Report
This part of the Advanced Excel Tutorial for 2022 will guide you to create an MIS Report
1:25:01 Excel Macros and VBA
This part of the Advanced Excel Tutorial for 2022 will teach you Excel Macros and VBA Fundamentals
2:27:15 Regression in Excel
This part of the Advanced Excel Tutorial for 2022 will help you learn Regression in Excel
2:53:51 Excel Power query
This part of the Advanced Excel Tutorial for 2022 will help you learn Excel Power Query
3:51:12 Pivot Tables in Excel
This part of the Advanced Excel Tutorial for 2022 will help you learn the Pivot tables in Excel
4:19:12 Excel Interview Questions
This part of the Advanced Excel Tutorial for 2022 will guide you through the most frequently asked Excel Interview Questions
🔥Free Business Analytics with Excel Course with completion certificate- https://www.simplilearn.com/learn-business-analytics-excel-fundamentals-skillup?utm_campaign=AdvanceExcel&utm_medium=Description&utm_source=youtube
âś…Subscribe to our Channel to learn more about the top Technologies: https://bit.ly/2VT4WtH
⏩ Check out the Excel tutorial videos: https://www.youtube.com/watch?v=nPkmWE4JCfE&list=PLEiEAq2VkUUKf8aLrspLg3zuyJ5S-5K5S
#AdvanceExcelTutorialFor2022 #AdvancedExcelCourse #AdvancedExcelTutorial #ExcelTutorialForBeginners #ExcelTutorial #ExcelForBeginners #Simplilearn
What is Microsoft Excel?
Excel is one of the best applications available on the market for creating spreadsheets to crunch numbers and dashboard reports as well as storing and administering data.
➡️ About Post Graduate Program In Data Analytics
This Data Analytics Program is ideal for all working professionals and prior programming knowledge is not required. It covers topics like data analysis, data visualization, regression techniques, and supervised learning in-depth via our applied learning model with live sessions by leading practitioners and industry projects.
âś… Key Features
- Post Graduate Program certificate and Alumni Association membership
- Exclusive hackathons and Ask me Anything sessions by IBM
- 8X higher live interaction in live online classes by industry experts
- Capstone from 3 domains and 14+ Data Analytics Projects with Industry datasets from Google PlayStore, Lyft, World Bank etc.
- Master Classes delivered by Purdue faculty and IBM experts
- Simplilearn's JobAssist helps you get noticed by top hiring companies
- Resume preparation and LinkedIn profile building
- 1:1 mock interview
- Career accelerator webinars
âś… Skills Covered
- Data Analytics
- Statistical Analysis using Excel
- Data Analysis Python and R
- Data Visualization Tableau and Power BI
- Linear and logistic regression modules
- Clustering using kmeans
- Supervised Learning
👉 Learn More at:
🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688
detail
{'title': 'Advance Excel Tutorial for 2022 | Advanced Excel Course | Excel Tutorial For Beginners |Simplilearn', 'heatmap': [{'end': 1609.945, 'start': 935.802, 'weight': 0.965}, {'end': 3913.15, 'start': 2753.916, 'weight': 0.718}], 'summary': 'This advanced excel tutorial for 2022 covers microsoft excel basics, data formatting, vba automation, regression analysis, power query features, pivot tables, functions, and advanced data manipulation techniques with practical examples and quantifiable data, including predicting units sold based on temperature and price, and combining 5 excel files successfully.', 'chapters': [{'end': 938.046, 'segs': [{'end': 223.339, 'src': 'embed', 'start': 179.967, 'weight': 2, 'content': [{'end': 182.348, 'text': 'We will see this in a better way through the practical session.', 'start': 179.967, 'duration': 2.381}, {'end': 186.231, 'text': 'So once you get started with the sheet, you will have some more options.', 'start': 182.889, 'duration': 3.342}, {'end': 189.793, 'text': 'So this particular option is called as the toolbar menu.', 'start': 186.631, 'duration': 3.162}, {'end': 195.297, 'text': 'You will have the file, home, insert, draw, page layout, formulas, data, review, view and help.', 'start': 190.153, 'duration': 5.144}, {'end': 200.102, 'text': 'So, these are the tools that you will be using to work on your data using Microsoft Excel.', 'start': 195.837, 'duration': 4.265}, {'end': 203.406, 'text': 'Furthermore, we have a toolbar ribbon.', 'start': 200.482, 'duration': 2.924}, {'end': 211.474, 'text': 'So when you select some or the other option from the file home insert, draw page, layout, formulas, data review,', 'start': 203.786, 'duration': 7.688}, {'end': 214.696, 'text': 'view and help buttons you will have a ribbon.', 'start': 211.474, 'duration': 3.222}, {'end': 218.837, 'text': 'so, for example, you can see that i have selected the home tool here.', 'start': 214.696, 'duration': 4.141}, {'end': 223.339, 'text': 'so when i press on the home tool, this is the ribbon which microsoft excel gives me.', 'start': 218.837, 'duration': 4.502}], 'summary': 'Practical session covers excel tools like file, home, insert, draw, page layout, formulas, data, review, view, and help.', 'duration': 43.372, 'max_score': 179.967, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko179967.jpg'}, {'end': 381.077, 'src': 'embed', 'start': 353.68, 'weight': 5, 'content': [{'end': 358.307, 'text': 'So we will be using Microsoft Excel to create a sheet of the employees in a company.', 'start': 353.68, 'duration': 4.627}, {'end': 365.798, 'text': 'So basically an employee in a company has employee ID, name and designation, salary, et cetera, et cetera.', 'start': 359.268, 'duration': 6.53}, {'end': 369.843, 'text': 'So we will be trying to create the same table using Microsoft Excel.', 'start': 366.158, 'duration': 3.685}, {'end': 374.849, 'text': 'But before that, let us understand the fundamentals of Microsoft Excel through the practical demo first.', 'start': 369.983, 'duration': 4.866}, {'end': 381.077, 'text': 'So I have started my Microsoft Excel and this is how the homepage of Microsoft Excel looks like.', 'start': 375.35, 'duration': 5.727}], 'summary': 'Using microsoft excel to create a sheet of employees with employee id, name, designation, and salary.', 'duration': 27.397, 'max_score': 353.68, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko353680.jpg'}, {'end': 584.204, 'src': 'embed', 'start': 529.298, 'weight': 0, 'content': [{'end': 532.301, 'text': 'And you can navigate through sheets just by pressing on the sheet name.', 'start': 529.298, 'duration': 3.003}, {'end': 541.468, 'text': 'And when you get back to the bottom right corner, you have an option of increasing and decreasing the cell size or the sheet size.', 'start': 534.045, 'duration': 7.423}, {'end': 544.83, 'text': "Now let's keep it default with 100%.", 'start': 541.968, 'duration': 2.862}, {'end': 549.171, 'text': 'Now, these are the few fundamentals that you need to keep in mind before getting started with Microsoft Excel.', 'start': 544.83, 'duration': 4.341}, {'end': 556.794, 'text': "Now that we know the fundamentals of Microsoft Excel, let's get started with a practical session, which is about the employees details in a company.", 'start': 549.671, 'duration': 7.123}, {'end': 562.957, 'text': "Now let's select this particular cell and let's type in employee details.", 'start': 557.735, 'duration': 5.222}, {'end': 576.34, 'text': 'Yeah, we have the cell.', 'start': 575.259, 'duration': 1.081}, {'end': 581.743, 'text': 'Now an employee details table will have the information related to employees.', 'start': 577.2, 'duration': 4.543}, {'end': 584.204, 'text': 'So the information will be about name.', 'start': 582.183, 'duration': 2.021}], 'summary': 'Learn excel fundamentals and practical session on employee details table.', 'duration': 54.906, 'max_score': 529.298, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko529298.jpg'}], 'start': 9.571, 'title': 'Microsoft excel basics', 'summary': 'Covers the fundamentals of microsoft excel, including navigating through sheets, addressing cells, changing font settings, merging cells, and creating a table for employee details with names, employee numbers, designations, salaries, and blood groups.', 'chapters': [{'end': 95.567, 'start': 9.571, 'title': 'Excel advanced tutorial', 'summary': 'Covers a comprehensive excel advanced tutorial, including topics such as goal seek, lookups, slices, macros, power query, pivot tables, and important interview questions.', 'duration': 85.996, 'highlights': ['Covering a wide range of Excel topics such as Goal Seek, lookups, Slices, macros, Power Query, pivot tables, and important interview questions.', 'Emphasizing the importance of subscribing to the YouTube channel for daily updates on multiple technologies.', 'Exploring fundamental Excel concepts and creating an Excel budget template.', 'Discussing regression and MIS report in Excel.', 'Stressing the continuous updates on multiple technologies available on the YouTube channel.']}, {'end': 287.943, 'start': 96.047, 'title': 'Fundamentals of microsoft excel', 'summary': 'Provides an overview of microsoft excel, including its purpose, features, and interface, and details the fundamental components such as the homepage, toolbar menu, ribbon, and toolbar groups.', 'duration': 191.896, 'highlights': ['Microsoft Excel is a software product designed and developed by Microsoft for storing data in an organized way, capable of manipulating data through mathematical operations, and used to extract insights and represent data in visually appealing graphs and charts. Microsoft Excel is designed for storing and manipulating data, and representing insights through graphs and charts.', 'The chapter provides an overview of the Microsoft Excel software product and its fundamental components, including the homepage, toolbar menu, ribbon, and toolbar groups. The chapter covers the fundamental components of Microsoft Excel, such as the homepage, toolbar menu, ribbon, and toolbar groups.', 'Microsoft Excel provides various tools such as file, home, insert, draw, page layout, formulas, data, review, view, and help for working on data. Microsoft Excel offers a range of tools, including file, home, insert, draw, page layout, formulas, data, review, view, and help.', 'The toolbar ribbon in Microsoft Excel contains options for performing various operations related to data manipulation and presentation. The toolbar ribbon in Microsoft Excel offers a range of options for data manipulation and presentation.', 'The toolbar ribbon is segmented into groups, each with separate functions, and includes a toolbar more options dialog box when necessary. The toolbar ribbon is segmented into groups, each serving separate functions, and provides a toolbar more options dialog box.']}, {'end': 488.773, 'start': 287.943, 'title': 'Microsoft excel fundamentals', 'summary': 'Introduces the fundamental concepts of microsoft excel, including the definition of cells and addresses, navigating through sheets, and selecting sheet types, providing insights into the practical demo and the toolbar functionalities.', 'duration': 200.83, 'highlights': ['The chapter introduces the fundamental concepts of Microsoft Excel, including the definition of cells and addresses, navigating through sheets, and selecting sheet types. The transcript covers the fundamental concepts of Microsoft Excel, such as defining cells and addresses, navigating through sheets, and selecting sheet types.', 'The tutorial provides insights into the practical demo of using Microsoft Excel to create a sheet for employees in a company. The tutorial discusses using Microsoft Excel to create a sheet for employees, emphasizing the practical application of the software.', 'The transcript explains the functionalities of the toolbar in Microsoft Excel, illustrating the different ribbons and options available under each menu. The transcript details the functionalities of the toolbar in Microsoft Excel, explaining the different ribbons and options available under each menu.']}, {'end': 938.046, 'start': 488.773, 'title': 'Microsoft excel basics', 'summary': 'Covers the fundamentals of microsoft excel, including navigating through sheets, addressing cells, changing font settings, merging cells, and creating a table for employee details with names, employee numbers, designations, salaries, and blood groups.', 'duration': 449.273, 'highlights': ['The chapter covers the fundamentals of Microsoft Excel, including navigating through sheets, addressing cells, changing font settings, merging cells, and creating a table for employee details with names, employee numbers, designations, salaries, and blood groups.', 'The practical session involves creating a table for employee details with information such as names, employee numbers, designations, salaries, and blood groups.', 'The discussion includes addressing cells in a sheet, navigating through sheets, and increasing/decreasing cell size or sheet size.', 'The process of merging cells and centering the data in the center part is explained, demonstrating how to organize the employee details table effectively.', 'The chapter discusses changing font settings, such as font type, size, and style, and demonstrates resolving visibility issues by manually adjusting cell/row size or double-clicking on cells.', 'The practical session involves allocating salaries to employees, such as $100,000 for the CEO, $20,000 for finance employees, and resizing cell width to accommodate the length of the text.']}], 'duration': 928.475, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko9571.jpg', 'highlights': ['Covers the fundamentals of Microsoft Excel, including navigating through sheets, addressing cells, changing font settings, merging cells, and creating a table for employee details with names, employee numbers, designations, salaries, and blood groups.', 'The chapter introduces the fundamental concepts of Microsoft Excel, including the definition of cells and addresses, navigating through sheets, and selecting sheet types.', 'The chapter provides an overview of the Microsoft Excel software product and its fundamental components, including the homepage, toolbar menu, ribbon, and toolbar groups.', 'Microsoft Excel provides various tools such as file, home, insert, draw, page layout, formulas, data, review, view, and help for working on data.', 'The toolbar ribbon in Microsoft Excel contains options for performing various operations related to data manipulation and presentation.', 'The practical session involves creating a table for employee details with information such as names, employee numbers, designations, salaries, and blood groups.']}, {'end': 4052.898, 'segs': [{'end': 1434.851, 'src': 'embed', 'start': 1409.722, 'weight': 1, 'content': [{'end': 1416.064, 'text': 'It allows you to search for a particular value in a column and returns another value from a different column but of the same row.', 'start': 1409.722, 'duration': 6.342}, {'end': 1423.027, 'text': 'VLOOKUP takes advantage of vertically aligned tables to quickly find data associated with the value the user enters.', 'start': 1417.205, 'duration': 5.822}, {'end': 1427.028, 'text': "Now, let's look at the syntax to write a VLOOKUP function.", 'start': 1423.647, 'duration': 3.381}, {'end': 1430.149, 'text': 'So VLOOKUP takes four parameters or arguments.', 'start': 1427.388, 'duration': 2.761}, {'end': 1431.27, 'text': 'You can see it here.', 'start': 1430.389, 'duration': 0.881}, {'end': 1434.851, 'text': 'There are four arguments in the VLOOKUP function.', 'start': 1431.47, 'duration': 3.381}], 'summary': 'Vlookup searches for data in a table and returns associated value, with 4 parameters.', 'duration': 25.129, 'max_score': 1409.722, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko1409722.jpg'}, {'end': 1528.776, 'src': 'embed', 'start': 1500.16, 'weight': 2, 'content': [{'end': 1501.681, 'text': 'You can see the data set on the left.', 'start': 1500.16, 'duration': 1.521}, {'end': 1507.824, 'text': "Using this data set, we'll understand how VLOOKUP performs an exact and approximate match.", 'start': 1502.802, 'duration': 5.022}, {'end': 1510.606, 'text': "We'll see how VLOOKUP is case insensitive.", 'start': 1508.345, 'duration': 2.261}, {'end': 1513.827, 'text': "Then we'll perform a wildcard character search.", 'start': 1511.386, 'duration': 2.441}, {'end': 1517.669, 'text': "We'll understand how to handle errors when a match is not found.", 'start': 1514.588, 'duration': 3.081}, {'end': 1524.993, 'text': 'And finally, you will look at two-way lookups.', 'start': 1518.23, 'duration': 6.763}, {'end': 1528.776, 'text': 'here is a small example of how VLOOKUP works.', 'start': 1524.993, 'duration': 3.783}], 'summary': "Exploring vlookup's performance, case insensitivity, wildcard search, error handling, and two-way lookups.", 'duration': 28.616, 'max_score': 1500.16, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko1500160.jpg'}, {'end': 2615.035, 'src': 'embed', 'start': 2583.601, 'weight': 0, 'content': [{'end': 2589.785, 'text': "I'll consider my table array from the representatives column till the end of total column or the sales column.", 'start': 2583.601, 'duration': 6.184}, {'end': 2595.086, 'text': 'Based on this selection, my column index number is 5.', 'start': 2591.246, 'duration': 3.84}, {'end': 2600.849, 'text': "comma. the range lookup is false, so I'll just double click on false.", 'start': 2595.086, 'duration': 5.763}, {'end': 2602.029, 'text': 'close the bracket.', 'start': 2600.849, 'duration': 1.18}, {'end': 2603.45, 'text': 'hit enter.', 'start': 2602.029, 'duration': 1.421}, {'end': 2615.035, 'text': 'you can see it here our vlookup function has returned the total as 539.73, which is actually the value for this row.', 'start': 2603.45, 'duration': 11.585}], 'summary': 'Using vlookup with range lookup false, column index 5, returned total as 539.73.', 'duration': 31.434, 'max_score': 2583.601, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko2583601.jpg'}, {'end': 3913.15, 'src': 'heatmap', 'start': 2753.916, 'weight': 0.718, 'content': [{'end': 2761.283, 'text': "VLOOKUP has returned me 999.5, which is actually Kivel's total.", 'start': 2753.916, 'duration': 7.367}, {'end': 2764.065, 'text': "now there's another way to do it.", 'start': 2761.283, 'duration': 2.782}, {'end': 2773.246, 'text': 'instead of selecting the cell in which we have the lookup value, I can directly write it in the VLOOKUP function.', 'start': 2764.065, 'duration': 9.181}, {'end': 2776.948, 'text': "so I'll write the VLOOKUP function something like this.", 'start': 2773.246, 'duration': 3.702}, {'end': 2786.253, 'text': "so my lookup value I'll pass Ki within double inverted commas, because Ki is a character value.", 'start': 2776.948, 'duration': 9.305}, {'end': 2798.832, 'text': "so within double inverted commas I'm writing Ki, followed by &, which is for concatenation, followed by a star within double inverted commas.", 'start': 2786.253, 'duration': 12.579}, {'end': 2801.553, 'text': 'so this is going to be my lookup values.', 'start': 2798.832, 'duration': 2.721}, {'end': 2808.195, 'text': 'this in total means anything that appears after Ki will be considered as the lookup value.', 'start': 2801.553, 'duration': 6.642}, {'end': 2814.216, 'text': "again, the table array I'll select as these rows and columns comma.", 'start': 2808.195, 'duration': 6.021}, {'end': 2816.957, 'text': 'my column index number would be 5 comma.', 'start': 2814.216, 'duration': 2.741}, {'end': 2817.777, 'text': 'the range lookup is 0.', 'start': 2816.957, 'duration': 0.82}, {'end': 2820.858, 'text': 'if I close the bracket, hit enter.', 'start': 2817.777, 'duration': 3.081}, {'end': 2822.918, 'text': 'you see, we have the same value.', 'start': 2820.858, 'duration': 2.06}, {'end': 2826.959, 'text': 'so this is another method or another way of using VLOOKUPS.', 'start': 2822.918, 'duration': 4.041}, {'end': 2830.955, 'text': 'Okay now moving ahead.', 'start': 2828.914, 'duration': 2.041}, {'end': 2832.675, 'text': "let's explore another feature of VLOOKUP.", 'start': 2830.955, 'duration': 1.72}, {'end': 2837.437, 'text': 'So if the VLOOKUP function cannot find a match, it returns a hasNA error.', 'start': 2833.255, 'duration': 4.182}, {'end': 2843.619, 'text': 'Now if you want, you can use the ifna function to replace the hasNA error with a friendly message.', 'start': 2837.797, 'duration': 5.822}, {'end': 2851.862, 'text': 'So suppose you want to find the region in which an order was placed on 10th of May 2018.', 'start': 2844.219, 'duration': 7.643}, {'end': 2857.064, 'text': 'Now if the order date is not found, I want to return a message that is invalid date.', 'start': 2851.862, 'duration': 5.202}, {'end': 2866.773, 'text': "So if you look at the order date column we don't have any value for 10th of May 2015.", 'start': 2858.184, 'duration': 8.589}, {'end': 2867.834, 'text': 'you can see these values.', 'start': 2866.773, 'duration': 1.061}, {'end': 2871.876, 'text': "we don't have any value corresponding to this date.", 'start': 2867.834, 'duration': 4.042}, {'end': 2877.98, 'text': "so if my VLOOKUP function cannot find this lookup value, I'll return invalid date.", 'start': 2871.876, 'duration': 6.104}, {'end': 2882.303, 'text': 'so let me show you how to write this.', 'start': 2877.98, 'duration': 4.323}, {'end': 2883.684, 'text': "I'll type my VLOOKUP function.", 'start': 2882.303, 'duration': 1.381}, {'end': 2894.369, 'text': "I'll give my order date as 10th of May 2015 as my lookup value and then I'll consider my table array.", 'start': 2884.721, 'duration': 9.648}, {'end': 2900.975, 'text': "starting from the order date column till the end of region column, I'll give another comma.", 'start': 2894.369, 'duration': 6.606}, {'end': 2902.636, 'text': 'my column index number would be 2.', 'start': 2900.975, 'duration': 1.661}, {'end': 2906.52, 'text': 'this time, based on the table array, we have selected comma.', 'start': 2902.636, 'duration': 3.884}, {'end': 2910.103, 'text': 'I want an exact match.', 'start': 2906.52, 'duration': 3.583}, {'end': 2912.305, 'text': "so I'll give 0, close the bracket and hit enter.", 'start': 2910.103, 'duration': 2.202}, {'end': 2914.06, 'text': 'there you go.', 'start': 2913.12, 'duration': 0.94}, {'end': 2915.761, 'text': 'it has returned me an error.', 'start': 2914.06, 'duration': 1.701}, {'end': 2920.643, 'text': 'now I want to convert this error to a friendly message saying invalid date.', 'start': 2915.761, 'duration': 4.882}, {'end': 2921.924, 'text': 'so let me show you how to do it.', 'start': 2920.643, 'duration': 1.281}, {'end': 2923.224, 'text': "I'll do it here.", 'start': 2921.924, 'duration': 1.3}, {'end': 2933.208, 'text': "so to return a valid message, I'll use the if any function and within if any function, I'll give my VLOOKUP function.", 'start': 2923.224, 'duration': 9.984}, {'end': 2940.171, 'text': "so let me type, if any, I'll hit tab Now for the value parameter.", 'start': 2933.208, 'duration': 6.963}, {'end': 2941.451, 'text': "I'll use my VLOOKUP function.", 'start': 2940.171, 'duration': 1.28}, {'end': 2946.694, 'text': 'So my lookup value is going to be the order date comma.', 'start': 2941.852, 'duration': 4.842}, {'end': 2951.997, 'text': "my table array I'll select from the order date column till the end of region column comma.", 'start': 2946.694, 'duration': 5.303}, {'end': 2955.838, 'text': 'my column index number is going to be 2, comma.', 'start': 2951.997, 'duration': 3.841}, {'end': 2958.274, 'text': 'my range lookup is 0,.', 'start': 2955.838, 'duration': 2.436}, {'end': 2960.596, 'text': "I'll close the bracket give another comma.", 'start': 2958.274, 'duration': 2.322}, {'end': 2966.822, 'text': 'Now, if this expression results in an error, I want to display a message that is invalid date.', 'start': 2961.257, 'duration': 5.565}, {'end': 2969.765, 'text': "So I'll put invalid date within double quotes.", 'start': 2967.523, 'duration': 2.242}, {'end': 2978.914, 'text': 'Since it is a text message, I close the bracket, hit enter.', 'start': 2970.586, 'duration': 8.328}, {'end': 2982.798, 'text': 'You can see the result, which is invalid date.', 'start': 2979.555, 'duration': 3.243}, {'end': 2989.742, 'text': 'Now moving on to the final section of the demo, we have two-way lookup.', 'start': 2985.239, 'duration': 4.503}, {'end': 2996.927, 'text': 'Now in the VLOOKUP function, the column index parameter is normally hard-coded as a static number.', 'start': 2991.563, 'duration': 5.364}, {'end': 3003.031, 'text': 'However, you can also create a dynamic column index by using the MATCH function to locate the right column.', 'start': 2997.487, 'duration': 5.544}, {'end': 3008.975, 'text': 'This technique allows you to create a dynamic two-way lookup matching on both rows and columns.', 'start': 3004.592, 'duration': 4.383}, {'end': 3014.227, 'text': 'now let me first show you how a match function works.', 'start': 3010.196, 'duration': 4.031}, {'end': 3021.336, 'text': 'so suppose I want to find the index where unit cost column is present.', 'start': 3014.227, 'duration': 7.109}, {'end': 3024.578, 'text': "so here I'll use the match function.", 'start': 3021.336, 'duration': 3.242}, {'end': 3030.201, 'text': "I'll give my lookup value as the unit cost column name comma.", 'start': 3024.578, 'duration': 5.623}, {'end': 3037.927, 'text': "suppose I'll choose my lookup array starting from representatives column till the unit cost column comma.", 'start': 3030.201, 'duration': 7.726}, {'end': 3041.329, 'text': "I'll give my match type as 0 for exact match.", 'start': 3037.927, 'duration': 3.402}, {'end': 3050.395, 'text': 'if I close the bracket, hit enter, it returns me 4 because My array was selected from the representatives column till the unit cost column.', 'start': 3041.329, 'duration': 9.066}, {'end': 3054.699, 'text': 'And in this array, unit cost is at the fourth index.', 'start': 3051.056, 'duration': 3.643}, {'end': 3059.402, 'text': "Alright Now using this idea, let's solve the following problem.", 'start': 3055.84, 'duration': 3.562}, {'end': 3063.826, 'text': 'I want to find the cost of each item sold by Morgan.', 'start': 3060.063, 'duration': 3.763}, {'end': 3067.288, 'text': "So let's write our VLOOKUP formula.", 'start': 3064.646, 'duration': 2.642}, {'end': 3069.13, 'text': "I'll write VLOOKUP.", 'start': 3067.308, 'duration': 1.822}, {'end': 3078.966, 'text': 'My lookup value is J5, which has the representatives name as Morgan comma.', 'start': 3071.111, 'duration': 7.855}, {'end': 3087.513, 'text': "my table array I'll select from C2 till F12.", 'start': 3078.966, 'duration': 8.547}, {'end': 3097.922, 'text': "I'll give a comma and this time I know that my column index number is at 4, but I'll be using the MATCH function to find the same.", 'start': 3087.513, 'duration': 10.409}, {'end': 3101.505, 'text': "so I'll write MATCH.", 'start': 3097.922, 'duration': 3.583}, {'end': 3106.6, 'text': 'my lookup value is unit cost comma.', 'start': 3101.505, 'duration': 5.095}, {'end': 3113.544, 'text': 'my lookup array is from C1 till F1 comma.', 'start': 3106.6, 'duration': 6.944}, {'end': 3115.565, 'text': 'my match type is 0.', 'start': 3113.544, 'duration': 2.021}, {'end': 3121.708, 'text': "I close this bracket, give another comma and I'll give my range lookup as 0.", 'start': 3115.565, 'duration': 6.143}, {'end': 3122.429, 'text': 'close the bracket.', 'start': 3121.708, 'duration': 0.721}, {'end': 3124.03, 'text': 'hit enter.', 'start': 3122.429, 'duration': 1.601}, {'end': 3127.732, 'text': 'you can see, for Morgan the unit cost is 19.99.', 'start': 3124.03, 'duration': 3.702}, {'end': 3129.433, 'text': 'you can verify from the table.', 'start': 3127.732, 'duration': 1.701}, {'end': 3133.038, 'text': 'for Morgan, the unit cost is 19.99.', 'start': 3129.433, 'duration': 3.605}, {'end': 3138.06, 'text': 'alright, now we have another question here.', 'start': 3133.038, 'duration': 5.022}, {'end': 3142.882, 'text': 'I want to find how much sales did Andrews make in the central region?', 'start': 3138.06, 'duration': 4.822}, {'end': 3146.483, 'text': 'so here I have two lookup values.', 'start': 3142.882, 'duration': 3.601}, {'end': 3147.864, 'text': 'one is central one.', 'start': 3146.483, 'duration': 1.381}, {'end': 3153.686, 'text': 'is Andrews now looking at these two values, I want to find the total.', 'start': 3147.864, 'duration': 5.822}, {'end': 3159.805, 'text': 'to solve such problems, let me first create another table.', 'start': 3153.686, 'duration': 6.119}, {'end': 3162.587, 'text': "I'll place the table somewhere here.", 'start': 3159.805, 'duration': 2.782}, {'end': 3165.669, 'text': 'okay, so let me just paste the table here.', 'start': 3162.587, 'duration': 3.082}, {'end': 3173.575, 'text': "alright, now I'll create another column where I'll merge the values of region and representatives.", 'start': 3165.669, 'duration': 7.906}, {'end': 3177.338, 'text': 'so here let me create another column.', 'start': 3173.575, 'duration': 3.763}, {'end': 3185.744, 'text': "okay, I'll just name this column as region rep.", 'start': 3177.338, 'duration': 8.406}, {'end': 3187.565, 'text': 'alright, now this column.', 'start': 3185.744, 'duration': 1.821}, {'end': 3192.012, 'text': "I'll use the concatenation operator.", 'start': 3189.15, 'duration': 2.862}, {'end': 3197.416, 'text': "so I'll select East, use Ambersand and then select Jones.", 'start': 3192.012, 'duration': 5.404}, {'end': 3199.337, 'text': "I'll hit enter.", 'start': 3197.416, 'duration': 1.921}, {'end': 3200.397, 'text': 'now the same thing.', 'start': 3199.337, 'duration': 1.06}, {'end': 3205.161, 'text': "I'll just duplicate it for the other rows as well.", 'start': 3200.397, 'duration': 4.764}, {'end': 3210.744, 'text': 'so I have my new column created now using this table, I can easily solve the problem.', 'start': 3205.161, 'duration': 5.583}, {'end': 3214.487, 'text': "now to find the total, I'll write my VLOOKUP formula.", 'start': 3210.744, 'duration': 3.743}, {'end': 3229.254, 'text': 'So my lookup value will be a combination of J11 & K11, while table array will start from the region, rep,', 'start': 3215.72, 'duration': 13.534}, {'end': 3236.541, 'text': 'which has the values of region and rep combined till the last column.', 'start': 3229.254, 'duration': 7.287}, {'end': 3243.649, 'text': 'And from this table array, my column index number is 5 and my range lookup is 0.', 'start': 3238.346, 'duration': 5.303}, {'end': 3259.662, 'text': "If I close the bracket, hit enter, you can see for central region, which is this one and for Andrew's representative, the total is 149.25.", 'start': 3243.649, 'duration': 16.013}, {'end': 3262.403, 'text': 'all right now we are done with our demo.', 'start': 3259.662, 'duration': 2.741}, {'end': 3266.803, 'text': "let's have a quick glance at what all we did in this demo.", 'start': 3262.403, 'duration': 4.4}, {'end': 3272.224, 'text': 'okay, so first of all we saw our data set, which was a sales data set that we used.', 'start': 3266.803, 'duration': 5.421}, {'end': 3277.905, 'text': 'then we started with our first example to see how vlookup performs an exact match.', 'start': 3272.224, 'duration': 5.681}, {'end': 3287.141, 'text': 'we also saw how, using the formulas tab and lookup and reference, you can write vlookup in another way,', 'start': 3277.905, 'duration': 9.236}, {'end': 3291.206, 'text': 'so you can use the function arguments to give your VLOOKUP parameters.', 'start': 3287.141, 'duration': 4.065}, {'end': 3295.031, 'text': 'next we saw how VLOOKUP performs an approximate match.', 'start': 3291.206, 'duration': 3.825}, {'end': 3300.197, 'text': 'so we had mat and raw, which were not present in the representatives column.', 'start': 3295.031, 'duration': 5.166}, {'end': 3310.944, 'text': 'after that we saw an important property of VLOOKUP, that is, VLOOKUP only looks to the right, followed by vlookup only returns the first match,', 'start': 3300.197, 'duration': 10.747}, {'end': 3315.025, 'text': 'following which we saw that vlookup is case insensitive.', 'start': 3310.944, 'duration': 4.081}, {'end': 3324.408, 'text': 'so here we had added a new row where gil was present in upper case, but still vlookup found the total revenue for gil,', 'start': 3315.025, 'duration': 9.383}, {'end': 3327.128, 'text': 'which was in sentence case or proper case.', 'start': 3324.408, 'duration': 2.72}, {'end': 3338.713, 'text': 'next we saw how we can use wildcard characters to use vlookups, And here we saw how to use an, if any, function along with VLOOKUP.', 'start': 3327.128, 'duration': 11.585}, {'end': 3342.695, 'text': 'And finally, we saw how to use two-way lookups.', 'start': 3339.453, 'duration': 3.242}, {'end': 3349.239, 'text': 'Choose from over 300 in-demand skills and get access to 1000 plus hours of video content for free.', 'start': 3342.915, 'duration': 6.324}, {'end': 3351.02, 'text': 'Visit SkillUp by Simply Learn.', 'start': 3349.399, 'duration': 1.621}, {'end': 3353.622, 'text': 'Click on the link in the description to know more.', 'start': 3351.34, 'duration': 2.282}, {'end': 3358.765, 'text': 'Now we are on the sheet of Microsoft Excel and here you can see some data on my sheet.', 'start': 3354.062, 'duration': 4.703}, {'end': 3362.707, 'text': 'That is employee name, employee ID, and salary.', 'start': 3360.266, 'duration': 2.441}, {'end': 3366.924, 'text': 'so here we are going to perform our horizontal lookup, but before that,', 'start': 3363.743, 'duration': 3.181}, {'end': 3374.485, 'text': 'let me show you that this particular drop down menu so this particular drop down menu has all the names of the employees in this particular table,', 'start': 3366.924, 'duration': 7.561}, {'end': 3378.206, 'text': 'and we have created this using data validation in excel.', 'start': 3374.485, 'duration': 3.721}, {'end': 3384.647, 'text': 'if you want to create the drop down menus just like these, then feel free to check out our data validation in excel tutorial,', 'start': 3378.206, 'duration': 6.441}, {'end': 3386.847, 'text': 'link to which is in the description box below.', 'start': 3384.647, 'duration': 2.2}, {'end': 3389.788, 'text': "now let's try to implement each lookup in excel.", 'start': 3386.847, 'duration': 2.941}, {'end': 3394.49, 'text': 'So here we have employee name, but we want to find out the employee ID.', 'start': 3390.228, 'duration': 4.262}, {'end': 3397.171, 'text': "So, for that let's create the HLOOKUP.", 'start': 3394.71, 'duration': 2.461}, {'end': 3400.213, 'text': 'Now, for that equals to HLOOKUP.', 'start': 3397.752, 'duration': 2.461}, {'end': 3404.595, 'text': 'Now, the first one is the lookup value.', 'start': 3402.954, 'duration': 1.641}, {'end': 3408.297, 'text': 'So, this one is the cell which is the employee name.', 'start': 3404.815, 'duration': 3.482}, {'end': 3412.939, 'text': 'Now, after that the table array, we have to select all the table arrays here.', 'start': 3408.817, 'duration': 4.122}, {'end': 3418.593, 'text': 'the row index number.', 'start': 3416.932, 'duration': 1.661}, {'end': 3423.135, 'text': 'so the employee id is in row index number 2, according to the table.', 'start': 3418.593, 'duration': 4.542}, {'end': 3426.697, 'text': 'so for that, provide 2 and close the bracket.', 'start': 3423.135, 'duration': 3.562}, {'end': 3431.24, 'text': 'and before closing brackets you might want to give the exact match.', 'start': 3426.697, 'duration': 4.543}, {'end': 3435.522, 'text': 'so for exact match, select false and close the bracket.', 'start': 3431.24, 'duration': 4.282}, {'end': 3436.963, 'text': 'press enter and there you go.', 'start': 3435.522, 'duration': 1.441}, {'end': 3441.607, 'text': "And if you select Jack, then you'll find the employee ID of Jack.", 'start': 3437.783, 'duration': 3.824}, {'end': 3446.091, 'text': "And if you select Jerry, you'll find the employee ID of Jerry reflecting here.", 'start': 3442.108, 'duration': 3.983}, {'end': 3450.576, 'text': "Now, similarly, let's find out the salary of all the employees using HLOOKUP.", 'start': 3446.732, 'duration': 3.844}, {'end': 3452.758, 'text': 'So, equals HLOOKUP.', 'start': 3451.337, 'duration': 1.421}, {'end': 3456.631, 'text': 'and lookup value.', 'start': 3455.189, 'duration': 1.442}, {'end': 3465.664, 'text': 'that happens to be the name of the employee, now the table array and next we have row index number.', 'start': 3456.631, 'duration': 9.033}, {'end': 3467.947, 'text': 'that is, salary happens to be the third row in the table.', 'start': 3465.664, 'duration': 2.283}, {'end': 3474.105, 'text': 'so three and exact match close the bracket and enter.', 'start': 3467.947, 'duration': 6.158}, {'end': 3480.712, 'text': "So now, if you change the values in this particular drop-down menu, you'll find the employee ID and their particular salary.", 'start': 3474.626, 'duration': 6.086}, {'end': 3486.198, 'text': 'So Jack happens to be carrying the employee ID as 1001 and his salary is 10,000.', 'start': 3480.993, 'duration': 5.205}, {'end': 3490.303, 'text': "So similarly, let's try to find out Peter's employee ID and salary.", 'start': 3486.199, 'duration': 4.104}, {'end': 3494.345, 'text': 'so there you go one, zero, zero, five and fifty thousand salary.', 'start': 3491.084, 'duration': 3.261}, {'end': 3497.066, 'text': "so that's how you use hlookup in excel.", 'start': 3494.345, 'duration': 2.721}, {'end': 3499.767, 'text': 'here you can see the first chart.', 'start': 3497.066, 'duration': 2.701}, {'end': 3506.509, 'text': "so this particular data is converted into a table format to avoid confusion while you're fixing the data.", 'start': 3499.767, 'duration': 6.742}, {'end': 3512.411, 'text': 'so here you can see the name of the employee, employee id, department, salary and date of joining,', 'start': 3506.509, 'duration': 5.902}, {'end': 3518.312, 'text': 'and here you can see the salary column and the bonus percentage of each and every employee based on their salaries.', 'start': 3512.411, 'duration': 5.901}, {'end': 3524.233, 'text': 'Now coming to the new data table and here on the table 2, which is an updated table.', 'start': 3519.052, 'duration': 5.181}, {'end': 3532.175, 'text': 'here you can find we are going to find out the department of the employee and the current department of employee and the bonus percentage based on his or her salary.', 'start': 3524.233, 'duration': 7.942}, {'end': 3538.736, 'text': 'So why are we finding current department again? So here you can see that we have an intern, Emily.', 'start': 3532.495, 'duration': 6.241}, {'end': 3543.58, 'text': 'who has joined in the year 2020 but recently she got promoted as a developer.', 'start': 3539.156, 'duration': 4.424}, {'end': 3550.286, 'text': 'So her department has been changed from department intern to department developer in 2021.', 'start': 3544, 'duration': 6.286}, {'end': 3553.769, 'text': "So that's what we're going to find out here in the current department using XLOOKUP.", 'start': 3550.286, 'duration': 3.483}, {'end': 3556.371, 'text': "Now let's start to implement XLOOKUP.", 'start': 3554.41, 'duration': 1.961}, {'end': 3561.336, 'text': 'So for that equals to XLOOKUP and for lookup.', 'start': 3556.752, 'duration': 4.584}, {'end': 3569.154, 'text': 'you can see if you need to find out the lookup value, lookup array, return array And all the other two are not important right now,', 'start': 3561.336, 'duration': 7.818}, {'end': 3572.176, 'text': 'but you can use them to get some exact matches in the future.', 'start': 3569.154, 'duration': 3.022}, {'end': 3573.476, 'text': "We'll also use that in the future.", 'start': 3572.196, 'duration': 1.28}, {'end': 3579.259, 'text': "For now, let's try to use the first three parameters that is lookup value, lookup array and written array.", 'start': 3573.816, 'duration': 5.443}, {'end': 3586.503, 'text': 'So the lookup value is the name of the employee, Mary, and lookup array is right here, which is this one.', 'start': 3579.699, 'duration': 6.804}, {'end': 3589.765, 'text': "Now we're going to select the lookup array.", 'start': 3587.744, 'duration': 2.021}, {'end': 3591.507, 'text': 'Then comma.', 'start': 3590.586, 'duration': 0.921}, {'end': 3594.693, 'text': 'And we are going to look up the department.', 'start': 3592.168, 'duration': 2.525}, {'end': 3597.638, 'text': 'So this is the department lookup array.', 'start': 3594.713, 'duration': 2.925}, {'end': 3599.641, 'text': "And that's all we need.", 'start': 3598.799, 'duration': 0.842}, {'end': 3603.774, 'text': 'Close the bracket and press enter.', 'start': 3601.311, 'duration': 2.463}, {'end': 3607.278, 'text': 'Okay, I think we missed out the lookup value.', 'start': 3604.775, 'duration': 2.503}, {'end': 3611.943, 'text': 'So the employee or the lookup value which we required was missed.', 'start': 3608.099, 'duration': 3.844}, {'end': 3613.105, 'text': "So I've added it here.", 'start': 3612.063, 'duration': 1.042}, {'end': 3616.569, 'text': 'So that is the lookup value, the first one, which is Mary.', 'start': 3613.125, 'duration': 3.444}, {'end': 3618.05, 'text': 'And there you go.', 'start': 3617.269, 'duration': 0.781}, {'end': 3621.074, 'text': 'Press enter and you have the department here.', 'start': 3619.192, 'duration': 1.882}, {'end': 3626.695, 'text': "Now, let's add it to all the rows and here you have all the rows and all the data related to all the employees.", 'start': 3621.574, 'duration': 5.121}, {'end': 3629.876, 'text': 'Now, we will find out the current department.', 'start': 3627.216, 'duration': 2.66}, {'end': 3634.417, 'text': 'As you can see, here you have Emily as intern, which is the older data.', 'start': 3630.016, 'duration': 4.401}, {'end': 3641.059, 'text': 'But what if you needed the latest data, that is, the developer, in the year 2021, right?', 'start': 3634.798, 'duration': 6.261}, {'end': 3644.94, 'text': 'For that you might want to work the XLOOKUP formula a little bit.', 'start': 3641.379, 'duration': 3.561}, {'end': 3646.621, 'text': "So, let's try that.", 'start': 3645.22, 'duration': 1.401}, {'end': 3659.771, 'text': 'XLOOKUP lookup value, which is the same, and comma now, where you have to look for the array that is lookup array, this one, and the return array,', 'start': 3647.281, 'duration': 12.49}, {'end': 3673.514, 'text': 'which happens to be this one, and next You can find the exact match for this one.', 'start': 3659.771, 'duration': 13.743}, {'end': 3675.536, 'text': "So that's all right, exact match.", 'start': 3673.554, 'duration': 1.982}, {'end': 3680.28, 'text': 'And here comes the important part where you have to search last to first.', 'start': 3676.157, 'duration': 4.123}, {'end': 3686.827, 'text': 'By default, XLOOKUP searches for first to last, that is like this in the starting place to the last place.', 'start': 3680.681, 'duration': 6.146}, {'end': 3689.809, 'text': "So that's the reason why we had Emily as intern.", 'start': 3687.187, 'duration': 2.622}, {'end': 3696.636, 'text': 'So now if we give last to first, then it retrieves the latest data where Emily will be shown as developer.', 'start': 3690.21, 'duration': 6.426}, {'end': 3701.282, 'text': 'Now select that and now you can close the brackets and press enter.', 'start': 3697.216, 'duration': 4.066}, {'end': 3704.086, 'text': "So let's add that to all the rows.", 'start': 3702.043, 'duration': 2.043}, {'end': 3705.168, 'text': 'There you go.', 'start': 3704.667, 'duration': 0.501}, {'end': 3712.419, 'text': 'Now you can see Emily, Clark, Peter and James, all they have got their updated job profiles as you can see in the original data.', 'start': 3705.629, 'duration': 6.79}, {'end': 3720.084, 'text': 'Emily is a developer now, so is Clark, who was originally a tester and he got promoted to development team in 2021.', 'start': 3712.802, 'duration': 7.282}, {'end': 3725.785, 'text': 'Similarly, Peter became a senior finance executive in 2021 and James, the manager of sales.', 'start': 3720.084, 'duration': 5.701}, {'end': 3730.946, 'text': "Now, let's try to find out the bonus for all these employees based on their salary.", 'start': 3726.325, 'duration': 4.621}, {'end': 3732.326, 'text': 'So, this one is quite simple.', 'start': 3731.146, 'duration': 1.18}, {'end': 3734.327, 'text': 'So, XLOOKUP.', 'start': 3733.326, 'duration': 1.001}, {'end': 3741.368, 'text': 'Now, the lookup value is their existing salary, comma, and the bonus part.', 'start': 3735.827, 'duration': 5.541}, {'end': 3744.01, 'text': 'So the lookup array is the salary range.', 'start': 3741.909, 'duration': 2.101}, {'end': 3747.772, 'text': 'Then here is another important point.', 'start': 3745.091, 'duration': 2.681}, {'end': 3753.996, 'text': 'So here you might want to find the exact match, but finding the exact match will not help you here.', 'start': 3748.413, 'duration': 5.583}, {'end': 3761.14, 'text': 'You might want to declare another type of value where you can get exact match or next smaller item.', 'start': 3754.376, 'duration': 6.764}, {'end': 3763.542, 'text': "So you'll understand it, why I'm selecting that.", 'start': 3761.561, 'duration': 1.981}, {'end': 3766.203, 'text': "So now let's close the bracket and enter.", 'start': 3764.202, 'duration': 2.001}, {'end': 3771.418, 'text': 'so now we have the bonus values.', 'start': 3769.557, 'duration': 1.861}, {'end': 3778.104, 'text': "now let's add the bonus value to all the factors, all the rows i mean, so you can see we have different variety of salaries, that is 38,000, 22,000,", 'start': 3771.418, 'duration': 6.686}, {'end': 3778.224, 'text': '39, etc.', 'start': 3778.104, 'duration': 0.12}, {'end': 3779.725, 'text': 'but here in the ranges we have provided 10, 20, 30, 40 and 50..', 'start': 3778.224, 'duration': 1.501}, {'end': 3781.346, 'text': 'so what if the value is between 30 and 20?', 'start': 3779.725, 'duration': 1.621}, {'end': 3791.515, 'text': "that's when you want the less number, right?", 'start': 3781.346, 'duration': 10.169}, {'end': 3796.243, 'text': "That's when you want the number which is equivalent to 30 or less than 30, which happens to be 15%.", 'start': 3791.615, 'duration': 4.628}, {'end': 3798.387, 'text': "So that's how it has worked here.", 'start': 3796.243, 'duration': 2.144}, {'end': 3801.453, 'text': 'That is 0.15% bonus to the actual salary.', 'start': 3798.447, 'duration': 3.006}, {'end': 3810.601, 'text': 'So, slices in Excel are software filters used along with Excel tables or Excel pivot tables over a large amount of data.', 'start': 3803.177, 'duration': 7.424}, {'end': 3818.205, 'text': 'Not just filtering out data, but slices also help you with an easy understanding of the information being extracted and displayed on the screen.', 'start': 3811.101, 'duration': 7.104}, {'end': 3823.407, 'text': 'Now, Microsoft Excel slices are compatible with Windows and Macintosh operating systems.', 'start': 3818.965, 'duration': 4.442}, {'end': 3827.709, 'text': 'Now, let us understand how to implement slices in Excel.', 'start': 3824.007, 'duration': 3.702}, {'end': 3832.892, 'text': 'So, for that, we might want to get back to the practical mode, that is, starting our Microsoft Excel.', 'start': 3828.009, 'duration': 4.883}, {'end': 3845.004, 'text': 'Now we are on our Excel sheet and, as you can see on my screen, there is some data available on the Excel spreadsheet,', 'start': 3837.978, 'duration': 7.026}, {'end': 3847.547, 'text': 'and this table is not just any table.', 'start': 3845.004, 'duration': 2.543}, {'end': 3852.551, 'text': 'This table from Microsoft Excel has been converted into an actual table.', 'start': 3848.187, 'duration': 4.364}, {'end': 3858.977, 'text': 'As you know, by default, Excel considers all the data which has been inserted into the spreadsheet as a database.', 'start': 3853.111, 'duration': 5.866}, {'end': 3866.622, 'text': 'And to implement the slices in Excel, we might want to create or convert the format of database into a normal table.', 'start': 3859.617, 'duration': 7.005}, {'end': 3875.808, 'text': 'For that, you can select all your data and just press Ctrl T and that will allow you an option called convert the data into table.', 'start': 3866.682, 'duration': 9.126}, {'end': 3884.333, 'text': 'Now, since this table is already converted as a table, we can directly start implementing or inserting slices into the spreadsheet.', 'start': 3876.748, 'duration': 7.585}, {'end': 3888.236, 'text': 'Now, for that, select all your data.', 'start': 3886.155, 'duration': 2.081}, {'end': 3899.978, 'text': 'Then go into the Insert option, and in the Insert ribbon, you can see the Filters group.', 'start': 3893.593, 'duration': 6.385}, {'end': 3903.862, 'text': 'And in the Filters group, select the option called Slicer.', 'start': 3900.479, 'duration': 3.383}, {'end': 3913.15, 'text': 'Now you will be provided with multiple options, since we have the columns Employee ID, Employee Name, Zone Designation, Department Salary,', 'start': 3904.302, 'duration': 8.848}], 'summary': 'The transcript covers excel functions like vlookup, ifna, match, hlookup, xlookup, and usage of slices in excel data visualization.', 'duration': 1159.234, 'max_score': 2753.916, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko2753916.jpg'}, {'end': 3014.227, 'src': 'embed', 'start': 2985.239, 'weight': 4, 'content': [{'end': 2989.742, 'text': 'Now moving on to the final section of the demo, we have two-way lookup.', 'start': 2985.239, 'duration': 4.503}, {'end': 2996.927, 'text': 'Now in the VLOOKUP function, the column index parameter is normally hard-coded as a static number.', 'start': 2991.563, 'duration': 5.364}, {'end': 3003.031, 'text': 'However, you can also create a dynamic column index by using the MATCH function to locate the right column.', 'start': 2997.487, 'duration': 5.544}, {'end': 3008.975, 'text': 'This technique allows you to create a dynamic two-way lookup matching on both rows and columns.', 'start': 3004.592, 'duration': 4.383}, {'end': 3014.227, 'text': 'now let me first show you how a match function works.', 'start': 3010.196, 'duration': 4.031}], 'summary': 'Vlookup function can have a dynamic column index using the match function, enabling dynamic two-way lookup matching on both rows and columns.', 'duration': 28.988, 'max_score': 2985.239, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko2985239.jpg'}, {'end': 3845.004, 'src': 'embed', 'start': 3811.101, 'weight': 3, 'content': [{'end': 3818.205, 'text': 'Not just filtering out data, but slices also help you with an easy understanding of the information being extracted and displayed on the screen.', 'start': 3811.101, 'duration': 7.104}, {'end': 3823.407, 'text': 'Now, Microsoft Excel slices are compatible with Windows and Macintosh operating systems.', 'start': 3818.965, 'duration': 4.442}, {'end': 3827.709, 'text': 'Now, let us understand how to implement slices in Excel.', 'start': 3824.007, 'duration': 3.702}, {'end': 3832.892, 'text': 'So, for that, we might want to get back to the practical mode, that is, starting our Microsoft Excel.', 'start': 3828.009, 'duration': 4.883}, {'end': 3845.004, 'text': 'Now we are on our Excel sheet and, as you can see on my screen, there is some data available on the Excel spreadsheet,', 'start': 3837.978, 'duration': 7.026}], 'summary': 'Excel slices aid in data understanding, compatible with windows and macintosh operating systems.', 'duration': 33.903, 'max_score': 3811.101, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko3811101.jpg'}], 'start': 938.166, 'title': 'Excel data, formatting, goal seek, vlookup, and lookup functions', 'summary': 'Covers adding data and formatting in excel, using goal seek to find required marks, understanding vlookup usage and properties, explaining vlookup function with ifna and match functions, and excel lookup functions and slicing tutorial.', 'chapters': [{'end': 1117.037, 'start': 938.166, 'title': 'Adding data and formatting in excel', 'summary': 'Demonstrates how to add data and format an excel table, including adding a new column and formatting options for font, size, and color, along with saving the file, preparing for goal setting in excel.', 'duration': 178.871, 'highlights': ['Adding a new column by right-clicking on the cell, selecting the insert option, and then typing in the new data, such as serial numbers or employee numbers, with the ability to copy and paste data and fill series, exemplifying the process with practical demonstrations.', "Explaining formatting options such as changing the font, bolding text, increasing the font size, and altering text color, as well as aligning cells to the center and adjusting the spacing between rows and columns, highlighting practical methods to enhance the table's appearance.", 'Concluding the tutorial by saving the Excel file in a local location, providing a comprehensive guide on adding data, formatting, and saving, preparing for the subsequent focus on goal setting in Excel.']}, {'end': 1886.133, 'start': 1117.777, 'title': 'Using goal seek in excel', 'summary': 'Demonstrates how to use goal seek in excel to find the required marks for a student to achieve a target percentage, with a specific example of a student needing to attain a 75% mark for an interview, and explains the vlookup function in excel including its syntax, parameters, and examples.', 'duration': 768.356, 'highlights': ['The chapter demonstrates how to use Goal Seek in Excel to find the required marks for a student to achieve a target percentage, with a specific example of a student needing to attain a 75% mark for an interview.', 'Explains the VLOOKUP function in Excel including its syntax, parameters, and examples.', 'Provides a detailed example of using VLOOKUP to find the items sold by a specific representative in a dataset, including explanations of the lookup value, table array, column index number, and range lookup.']}, {'end': 2816.957, 'start': 1886.133, 'title': 'Vlookup in excel: usage and properties', 'summary': 'Demonstrates the usage of vlookup in excel, including methods for exact and approximate match, property of looking to the right, returning only the first match, being case insensitive, and supporting wildcards. it also includes examples of finding values and understanding the reasons behind the returned results.', 'duration': 930.824, 'highlights': ['VLOOKUP can be used in Excel to find exact matches by specifying the lookup value, table array, column index number, and range lookup. The speaker demonstrates using VLOOKUP to find an exact match for a lookup value, showing how to specify the lookup value, table array, column index number, and range lookup, and achieving the exact match result.', 'VLOOKUP can be used to find approximate matches by ensuring the lookup value column is sorted in ascending order and specifying range lookup as true. The speaker explains how to use VLOOKUP to find approximate matches by ensuring the lookup value column is sorted in ascending order and specifying range lookup as true, providing an example with detailed steps.', 'VLOOKUP always looks to the right, requiring the lookup value to be on the left and the value to be found to be on the right of the table. The transcript explains the property of VLOOKUP always looking to the right, demonstrating the scenario where the lookup value is on the left and the value to be found is on the right of the table.', 'VLOOKUP returns only the first match, as demonstrated by the examples of finding the units sold by Jardine and the unit cost for Jones. The speaker illustrates the property of VLOOKUP returning only the first match through examples of finding the units sold by Jardine and the unit cost for Jones, showcasing the returned results and explaining the reasoning.', "VLOOKUP is case insensitive, as shown by finding the total revenue generated by 'Gil' in both proper and uppercase cases. The transcript highlights the case insensitivity of VLOOKUP, demonstrating the process of finding the total revenue generated by 'Gil' in both proper and uppercase cases and explaining the outcomes.", "VLOOKUP supports wildcards for performing partial matches on lookup values, allowing retrieval of values by typing part of the lookup value. The speaker explains how VLOOKUP supports wildcards for performing partial matches on lookup values, demonstrating the usage of wildcards to find the total sales for a representative whose name starts with 'KI' and illustrating an alternative method of using wildcards within the VLOOKUP function."]}, {'end': 3259.662, 'start': 2816.957, 'title': 'Vlookup function explained', 'summary': 'Explains the vlookup function, including handling errors using the ifna function, dynamic column index using the match function, and solving two-way lookup problems, with key highlights being replacing errors with friendly messages using ifna and using dynamic column index in vlookup with the match function.', 'duration': 442.705, 'highlights': ['Replacing errors with friendly messages using IFNA The chapter explains how to use the IFNA function to replace the #N/A error with a friendly message when VLOOKUP cannot find a match.', 'Using dynamic column index in VLOOKUP with the MATCH function The technique of creating a dynamic column index by using the MATCH function to locate the right column in the VLOOKUP function is demonstrated, allowing for dynamic two-way lookup matching on both rows and columns.', 'Finding the total sales for a specific region and representative using VLOOKUP Demonstrates using VLOOKUP to find the total sales for a specific region and representative by combining the lookup values, selecting the appropriate table array, and using a specific column index number.']}, {'end': 4052.898, 'start': 3259.662, 'title': 'Excel lookup and slicing tutorial', 'summary': 'Covers various excel lookup functions including vlookup, hlookup, and xlookup, as well as the implementation of slices in excel, offering practical demonstrations with specific examples and techniques.', 'duration': 793.236, 'highlights': ['The chapter covers various Excel lookup functions including VLOOKUP, HLOOKUP, and XLOOKUP The chapter demonstrates the usage of VLOOKUP for exact and approximate matches, case insensitivity, wildcard characters, and two-way lookups, as well as practical demonstrations of HLOOKUP and XLOOKUP.', 'Practical demonstrations with specific examples and techniques The transcript provides practical demonstrations of implementing VLOOKUP, HLOOKUP, and XLOOKUP with specific examples, showcasing how to find employee IDs, salaries, and current departments, and how to use slices in Excel for data analysis and presentation.', 'Implementation of slices in Excel The chapter demonstrates the implementation of slices in Excel, showcasing the process of converting data into a table, inserting and arranging slices, and utilizing slices for filtering and presenting specific employee data based on zones, departments, and designations.']}], 'duration': 3114.732, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko938166.jpg', 'highlights': ['Demonstrates using VLOOKUP to find the total sales for a specific region and representative by combining the lookup values, selecting the appropriate table array, and using a specific column index number.', 'Explains the VLOOKUP function in Excel including its syntax, parameters, and examples.', 'The chapter demonstrates the usage of VLOOKUP for exact and approximate matches, case insensitivity, wildcard characters, and two-way lookups, as well as practical demonstrations of HLOOKUP and XLOOKUP.', 'The chapter demonstrates the implementation of slices in Excel, showcasing the process of converting data into a table, inserting and arranging slices, and utilizing slices for filtering and presenting specific employee data based on zones, departments, and designations.', 'The technique of creating a dynamic column index by using the MATCH function to locate the right column in the VLOOKUP function is demonstrated, allowing for dynamic two-way lookup matching on both rows and columns.']}, {'end': 5507.006, 'segs': [{'end': 4120.426, 'src': 'embed', 'start': 4054.079, 'weight': 0, 'content': [{'end': 4062.788, 'text': "So that's how it's done and that's how you implement slicers in Excel to simplify the filtering options using slicers during a presentation.", 'start': 4054.079, 'duration': 8.709}, {'end': 4066.872, 'text': 'And with that, we have come to an end of this tutorial on slicers in Excel.', 'start': 4063.288, 'duration': 3.584}, {'end': 4069.555, 'text': 'How to create an Excel budget template.', 'start': 4066.992, 'duration': 2.563}, {'end': 4077.463, 'text': "For that, let's get back to practical mode where we have to start our Microsoft Excel and see how to implement Excel budget template.", 'start': 4070.576, 'duration': 6.887}, {'end': 4082.526, 'text': 'So on my screen you can see an example Excel budget template theme.', 'start': 4078.365, 'duration': 4.161}, {'end': 4087.107, 'text': 'So on my screen you can see a column where you have the debit amount.', 'start': 4083.246, 'duration': 3.861}, {'end': 4092.348, 'text': 'So in the i column we have all the debit amount, what we are spending for a month,', 'start': 4087.847, 'duration': 4.501}, {'end': 4096.149, 'text': 'and on the right column will be the balance amount or the running balance of that.', 'start': 4092.348, 'duration': 3.801}, {'end': 4107.611, 'text': "For example you can see that there is a row number 5 where the debit amount is 8000 for a jeans Levi's and it is done through UPI payment.", 'start': 4096.669, 'duration': 10.942}, {'end': 4116.462, 'text': 'Now, if I change this value of 8,000 to 4,000 something, then you can see that the running balance will automatically change.', 'start': 4108.234, 'duration': 8.228}, {'end': 4120.426, 'text': 'You can see that currently the running balance is 73,000.', 'start': 4116.903, 'duration': 3.523}], 'summary': 'Tutorial on implementing slicers in excel and creating an excel budget template with practical examples and running balance calculation.', 'duration': 66.347, 'max_score': 4054.079, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko4054079.jpg'}, {'end': 4170.841, 'src': 'embed', 'start': 4145.194, 'weight': 3, 'content': [{'end': 4151.877, 'text': 'you can see that we have also included what is the total debit amount in January and what is the running balance.', 'start': 4145.194, 'duration': 6.683}, {'end': 4159.339, 'text': 'that is, the total available balance after 1 lakh has been debited into the account in the month of February as February income.', 'start': 4151.877, 'duration': 7.462}, {'end': 4164.656, 'text': 'Now the current available balance is 1,13,200.', 'start': 4160.24, 'duration': 4.416}, {'end': 4169.501, 'text': 'and the total expense in February is one lakh four thousand five hundred.', 'start': 4164.657, 'duration': 4.844}, {'end': 4170.841, 'text': 'right, how am I doing?', 'start': 4169.501, 'duration': 1.34}], 'summary': 'In february, 1,00,000 was debited, leaving a balance of 1,13,200, with expenses totaling 1,04,500.', 'duration': 25.647, 'max_score': 4145.194, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko4145194.jpg'}, {'end': 4389.895, 'src': 'embed', 'start': 4333.137, 'weight': 5, 'content': [{'end': 4339.481, 'text': 'and done right, there you go.', 'start': 4333.137, 'duration': 6.344}, {'end': 4345.885, 'text': 'so the total expense of the month January is 93 800 rupees.', 'start': 4339.481, 'duration': 6.404}, {'end': 4357.193, 'text': "now let's calculate the total debit of February month, same formula, that is, calculating the sum of all the expenses made in the month of February.", 'start': 4345.885, 'duration': 11.308}, {'end': 4378.811, 'text': "Now, let's also calculate the sum of all the expenses in the month of March.", 'start': 4374.727, 'duration': 4.084}, {'end': 4389.895, 'text': 'done so.', 'start': 4388.633, 'duration': 1.262}], 'summary': 'Total expense in january: 93,800 rupees. calculating total debit in february and march.', 'duration': 56.758, 'max_score': 4333.137, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko4333137.jpg'}, {'end': 4549.776, 'src': 'embed', 'start': 4519.301, 'weight': 6, 'content': [{'end': 4525.562, 'text': 'So, MIS reports help management to access the performance of organization and allow faster decision making.', 'start': 4519.301, 'duration': 6.261}, {'end': 4531.344, 'text': 'So, if you know to create the MIS reports using various business intelligence tools such as Excel,', 'start': 4525.962, 'duration': 5.382}, {'end': 4535.604, 'text': 'then you might find a very good job opportunity in a well-established company.', 'start': 4531.344, 'duration': 4.26}, {'end': 4540.385, 'text': 'Now, let us try to learn how to create MIS report using Excel in a practical mode.', 'start': 4536.185, 'duration': 4.2}, {'end': 4542.846, 'text': "So, let's get back to the Microsoft Excel.", 'start': 4540.786, 'duration': 2.06}, {'end': 4549.776, 'text': 'If getting your learning started is half the battle, what if you could do that for free? Visit SkillUp by SimplyLearn.', 'start': 4543.433, 'duration': 6.343}], 'summary': 'Mis reports aid in faster decision-making, and proficiency in creating them using excel can lead to job opportunities in established companies.', 'duration': 30.475, 'max_score': 4519.301, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko4519301.jpg'}, {'end': 4616.779, 'src': 'embed', 'start': 4588.204, 'weight': 7, 'content': [{'end': 4592.266, 'text': "So how did I create this MIS report? Let's go through this in a step by step way.", 'start': 4588.204, 'duration': 4.062}, {'end': 4603.026, 'text': 'So on my screen you can see that we have created a completely new Excel workbook and we have some sample data with 5 rows and 20 columns.', 'start': 4594.238, 'duration': 8.788}, {'end': 4609.552, 'text': 'So this particular data is based on the manufacture year and the car manufacturing company,', 'start': 4604.007, 'duration': 5.545}, {'end': 4616.779, 'text': 'and the type of the car manufactured and number of cars manufactured in that particular year, and also the price of that particular car.', 'start': 4609.552, 'duration': 7.227}], 'summary': 'Mis report created using new excel workbook with 5 rows and 20 columns, containing data on car manufacturing company, type, number of cars, and prices.', 'duration': 28.575, 'max_score': 4588.204, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko4588204.jpg'}, {'end': 5072.895, 'src': 'embed', 'start': 5049.916, 'weight': 9, 'content': [{'end': 5058.763, 'text': 'you need to make sure that all the macros are disabled on your dataset and you convert the entire data into the regular table format of Excel.', 'start': 5049.916, 'duration': 8.847}, {'end': 5063.067, 'text': 'Right now, Excel is considering this particular data as a database.', 'start': 5059.203, 'duration': 3.864}, {'end': 5070.713, 'text': 'So for that, you need to select all the data, press control T and then convert the entire data in the form of tabular data.', 'start': 5063.507, 'duration': 7.206}, {'end': 5072.895, 'text': "And also don't forget to click this icon.", 'start': 5070.773, 'duration': 2.122}], 'summary': 'Disable macros and convert data to regular table format in excel.', 'duration': 22.979, 'max_score': 5049.916, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko5049916.jpg'}, {'end': 5155.565, 'src': 'embed', 'start': 5099.2, 'weight': 10, 'content': [{'end': 5101.541, 'text': "so that's how you work on MIS report in Excel.", 'start': 5099.2, 'duration': 2.341}, {'end': 5102.882, 'text': 'so what is Excel macro?', 'start': 5101.541, 'duration': 1.341}, {'end': 5121.935, 'text': 'you do certain daily tasks and daily reports or monthly reports that you generate and you use data from your excel and you scrub data and you basically have the same things performed or done on the excel sheets every time and every day,', 'start': 5102.882, 'duration': 19.053}, {'end': 5126.699, 'text': 'or monthly or bi-weekly, or any number of times that you open your excel.', 'start': 5121.935, 'duration': 4.764}, {'end': 5129.121, 'text': 'you remove certain columns,', 'start': 5126.699, 'duration': 2.422}, {'end': 5138.908, 'text': 'you add certain things and you change certain values and these are the things that you do and n number of times on an excel.', 'start': 5129.121, 'duration': 9.787}, {'end': 5146.739, 'text': 'this can all be done by simple, automated way, which is macros.', 'start': 5140.674, 'duration': 6.065}, {'end': 5155.565, 'text': 'so what it does is it is a set of tasks that you can execute as many number of times as you want when you create a macro.', 'start': 5146.739, 'duration': 8.826}], 'summary': 'Using excel macros automates repetitive tasks in mis reports, saving time and effort.', 'duration': 56.365, 'max_score': 5099.2, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko5099200.jpg'}], 'start': 4054.079, 'title': 'Excel budgeting and mis reports', 'summary': 'Covers implementing slicers and a budget template in excel, creating mis reports, and using macros. it includes examples of a running balance update, a final balance of 37,300 rupees for march, and the importance of mis reports for decision making.', 'chapters': [{'end': 4170.841, 'start': 4054.079, 'title': 'Implementing slicers in excel', 'summary': 'Explains how to implement slicers in excel to simplify filtering options, along with creating an excel budget template, demonstrating the automatic running balance update and providing specific examples of debit amounts and running balances.', 'duration': 116.762, 'highlights': ['The tutorial explains how to implement slicers in Excel to simplify filtering options during a presentation.', 'The transcript demonstrates the automatic update of running balances in an Excel budget template when changing debit amounts.', 'Specific examples of debit amounts and running balances are provided, such as changing a debit amount from 8,000 to 4,000 and observing the running balance automatically adjusting to 73,000.', 'The tutorial also includes details about the total debit amount and running balance in January and the available balance and expenses in February.']}, {'end': 4486.112, 'start': 4170.841, 'title': 'Excel budget template implementation', 'summary': 'Explains how to implement an excel budget template, including converting data into a table, typing formulas for running balance, calculating total expenses for each month, and determining final balance, resulting in a final balance of 37,300 rupees for march.', 'duration': 315.271, 'highlights': ['Implementing Excel budget template Describes the step-by-step process of implementing an Excel budget template, including converting data into a table and typing formulas for running balance.', 'Calculating final balance for March Details the calculation of the final balance for March, resulting in a final balance of 37,300 rupees.', 'Calculating total expenses for each month Explains the process of calculating total expenses for each month using a sum function.', 'Typing formulas for running balance Illustrates the process of typing formulas for running balance, providing a practical example of tracking income, expenses, and remaining balance.']}, {'end': 5049.916, 'start': 4486.733, 'title': 'Creating mis reports in excel', 'summary': 'Explains the process of creating mis reports using microsoft excel, highlighting the importance of mis reports for accurate decision making, the use of pivot tables and pivot charts, and the connection of slicers to multiple pivot tables and charts to enable interactive reporting.', 'duration': 563.183, 'highlights': ['MIS reports provide data on different categories for accurate decision making. Emphasizes the importance of MIS reports for accurate decision making, demonstrating their value in facilitating informed choices.', 'Creating a completely new Excel workbook and using sample data with 5 rows and 20 columns based on the manufacture year and car manufacturing company. Illustrates the process of creating MIS reports, emphasizing the use of sample data and the structure of the Excel workbook for report generation.', 'Demonstrates the use of pivot tables and pivot charts for creating interactive reports in Excel. Highlights the practical application of pivot tables and pivot charts in creating interactive reports, showcasing their usefulness in data visualization and analysis.', 'Connecting slicers to multiple pivot tables and charts to enable interactive reporting. Showcases the process of connecting slicers to multiple pivot tables and charts to enable interactive reporting, emphasizing the importance of data connectivity for comprehensive analysis.']}, {'end': 5507.006, 'start': 5049.916, 'title': 'Excel macros and mis reports', 'summary': 'Explains how to disable macros in excel, convert data into tabular format, activate and record macros, and automate repetitive tasks using macros to streamline mis report generation in excel.', 'duration': 457.09, 'highlights': ['The process to disable macros in Excel and convert data into tabular format is explained. The chapter details the process of disabling macros in Excel and converting data into a tabular format using the control T function, ensuring efficient data management.', 'The explanation of how to activate and record macros in Excel is provided, emphasizing the automation of repetitive tasks. The chapter provides a detailed explanation on how to activate and record macros in Excel, emphasizing the automation of repetitive tasks to streamline MIS report generation.', 'The detailed process of automating repetitive tasks in Excel using macros is demonstrated, showcasing the efficiency and time-saving benefits. The chapter demonstrates the detailed process of automating repetitive tasks in Excel using macros, showcasing the efficiency and time-saving benefits of this automation.']}], 'duration': 1452.927, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko4054079.jpg', 'highlights': ['The tutorial explains how to implement slicers in Excel to simplify filtering options during a presentation.', 'The transcript demonstrates the automatic update of running balances in an Excel budget template when changing debit amounts.', 'Specific examples of debit amounts and running balances are provided, such as changing a debit amount from 8,000 to 4,000 and observing the running balance automatically adjusting to 73,000.', 'The tutorial also includes details about the total debit amount and running balance in January and the available balance and expenses in February.', 'Implementing Excel budget template Describes the step-by-step process of implementing an Excel budget template, including converting data into a table and typing formulas for running balance.', 'Calculating final balance for March Details the calculation of the final balance for March, resulting in a final balance of 37,300 rupees.', 'MIS reports provide data on different categories for accurate decision making. Emphasizes the importance of MIS reports for accurate decision making, demonstrating their value in facilitating informed choices.', 'Creating a completely new Excel workbook and using sample data with 5 rows and 20 columns based on the manufacture year and car manufacturing company. Illustrates the process of creating MIS reports, emphasizing the use of sample data and the structure of the Excel workbook for report generation.', 'Demonstrates the use of pivot tables and pivot charts for creating interactive reports in Excel. Highlights the practical application of pivot tables and pivot charts in creating interactive reports, showcasing their usefulness in data visualization and analysis.', 'The process to disable macros in Excel and convert data into tabular format is explained. The chapter details the process of disabling macros in Excel and converting data into a tabular format using the control T function, ensuring efficient data management.', 'The explanation of how to activate and record macros in Excel is provided, emphasizing the automation of repetitive tasks. The chapter provides a detailed explanation on how to activate and record macros in Excel, emphasizing the automation of repetitive tasks to streamline MIS report generation.', 'The detailed process of automating repetitive tasks in Excel using macros is demonstrated, showcasing the efficiency and time-saving benefits.']}, {'end': 6296.233, 'segs': [{'end': 5536.104, 'src': 'embed', 'start': 5507.046, 'weight': 5, 'content': [{'end': 5510.588, 'text': 'This is the task that I perform everyday.', 'start': 5507.046, 'duration': 3.542}, {'end': 5512.85, 'text': "I still don't need it.", 'start': 5511.409, 'duration': 1.441}, {'end': 5517.752, 'text': 'And I go to the developer tab and click stop recording.', 'start': 5514.81, 'duration': 2.942}, {'end': 5521.294, 'text': 'So now I have the macro saved.', 'start': 5518.332, 'duration': 2.962}, {'end': 5528.339, 'text': 'And when I have it, I have automated all these steps in one simple click.', 'start': 5521.314, 'duration': 7.025}, {'end': 5531.601, 'text': 'And how I execute it in another sheet.', 'start': 5529.219, 'duration': 2.382}, {'end': 5534.483, 'text': 'So I will show it across to you.', 'start': 5532.121, 'duration': 2.362}, {'end': 5536.104, 'text': 'The next thing.', 'start': 5535.523, 'duration': 0.581}], 'summary': 'A daily task automated with a macro, saving time and effort.', 'duration': 29.058, 'max_score': 5507.046, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko5507046.jpg'}, {'end': 5689.032, 'src': 'embed', 'start': 5661.879, 'weight': 1, 'content': [{'end': 5668.241, 'text': 'at the end, you can view macros, you can record and you can edit your macro.', 'start': 5661.879, 'duration': 6.362}, {'end': 5674.584, 'text': 'so when I click view macro, that will show you what are the macros that I have and that I can run,', 'start': 5668.241, 'duration': 6.343}, {'end': 5682.527, 'text': 'and you can simply run it alternatively And you can record a macro as well instead of from the developer tab.', 'start': 5674.584, 'duration': 7.943}, {'end': 5689.032, 'text': 'If you just click on record macro, it will do the same function that we do from the developer tab.', 'start': 5683.207, 'duration': 5.825}], 'summary': 'View, record, and edit macros; run and record macros without the developer tab.', 'duration': 27.153, 'max_score': 5661.879, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko5661879.jpg'}, {'end': 5860.611, 'src': 'embed', 'start': 5834.011, 'weight': 0, 'content': [{'end': 5841.376, 'text': 'there are many advantages of using VBA and why we prefer VBA in macros for excel.', 'start': 5834.011, 'duration': 7.365}, {'end': 5845.219, 'text': 'so basically, it is a very simple language.', 'start': 5841.376, 'duration': 3.843}, {'end': 5849.102, 'text': 'it saves time in performing tedious tasks.', 'start': 5845.219, 'duration': 3.883}, {'end': 5852.065, 'text': 'you have number of tasks that you do every day,', 'start': 5849.102, 'duration': 2.963}, {'end': 5860.611, 'text': 'as we have seen in the previous section how we can simplify your daily task by just running a simple macro,', 'start': 5852.065, 'duration': 8.546}], 'summary': 'Vba simplifies daily tasks, saving time in excel macros.', 'duration': 26.6, 'max_score': 5834.011, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko5834011.jpg'}, {'end': 6025.288, 'src': 'embed', 'start': 5995.674, 'weight': 3, 'content': [{'end': 6001.338, 'text': 'VBA code window, as it says, is the code window where your actual code resides,', 'start': 5995.674, 'duration': 5.664}, {'end': 6009.862, 'text': 'wherein you can go into it and edit the code or write the codes and select the different range of functions that you can perform,', 'start': 6001.338, 'duration': 8.524}, {'end': 6017.105, 'text': 'and this is basically the VBA code window where you edit your or write your codes.', 'start': 6009.862, 'duration': 7.243}, {'end': 6018.685, 'text': 'the next thing is the project explorer.', 'start': 6017.105, 'duration': 1.58}, {'end': 6025.288, 'text': 'project explorer is simply like a windows explorer, but this is, in particular, is specific to your VBA,', 'start': 6018.685, 'duration': 6.603}], 'summary': 'The vba code window is where you edit or write code, and the project explorer is specific to vba.', 'duration': 29.614, 'max_score': 5995.674, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko5995674.jpg'}, {'end': 6180.893, 'src': 'embed', 'start': 6157.144, 'weight': 4, 'content': [{'end': 6166.728, 'text': 'so there are two ways to open the Visual Basic application, and the first thing is you can run Alt F11,', 'start': 6157.144, 'duration': 9.584}, {'end': 6175.991, 'text': 'click on the sheet and just press and it will open the um visual basic for applications window, or alternatively,', 'start': 6166.728, 'duration': 9.263}, {'end': 6180.893, 'text': 'you can just go and click on the visual basic and it will open the sheet for you.', 'start': 6175.991, 'duration': 4.902}], 'summary': 'Two ways to open visual basic: alt f11 or clicking directly on the sheet or application window.', 'duration': 23.749, 'max_score': 6157.144, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko6157144.jpg'}], 'start': 5507.046, 'title': 'Automating tasks with vba in excel', 'summary': 'Covers automating daily tasks in excel using vba macros, detailing the process of recording, running, and editing macros, and explaining the vba editor interface with its components, such as the toolbar, menu bar, vba code window, project explorer, properties window, immediate window, and watch window.', 'chapters': [{'end': 5912.306, 'start': 5507.046, 'title': 'Automating tasks with excel macros', 'summary': 'Explains how to automate daily tasks in excel using macros, detailing the process of recording, running, and editing macros, and the advantages of using vba, which saves time and improves excel functionality.', 'duration': 405.26, 'highlights': ['The VBA, Visual Basic Application, helps automate tasks in Excel by writing macros, simplifying daily tasks and improving work efficiency, ultimately saving time.', 'The process of recording, running, and editing macros is detailed, including adding a button to run a macro with a single click, and using VBA to edit macros for specific tasks, such as removing existing columns from a sheet.', "Advantages of VBA in macros for Excel are highlighted, including time-saving, support for English-like statements, and improving Excel functionality by allowing users to customize Excel's behavior."]}, {'end': 6296.233, 'start': 5913.143, 'title': 'Vba editor interface and functions', 'summary': 'Explains the vba editor interface in detail, covering the toolbar, menu bar, vba code window, project explorer, properties window, immediate window, and watch window, and demonstrates how to open the visual basic for applications window and record a macro to perform tasks using vba.', 'duration': 383.09, 'highlights': ['The VBA editor interface includes the toolbar, menu bar, VBA code window, project explorer, properties window, immediate window, and watch window. It provides an overview of the main components of the VBA editor interface, offering a comprehensive understanding of its functionality.', 'The process of opening the Visual Basic for Applications window is explained, demonstrating two methods: using Alt F11 or clicking on the Visual Basic option. It outlines the two ways to open the Visual Basic for Applications window, providing practical guidance for users.', 'Demonstration of recording a macro and performing tasks using VBA, including deleting columns, selecting data, changing font, and saving the macro. It showcases the practical application of VBA by recording a macro and executing tasks such as deleting columns, selecting data, changing font, and saving the macro.']}], 'duration': 789.187, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko5507046.jpg', 'highlights': ['VBA simplifies daily tasks in Excel, improving work efficiency and saving time.', 'Detailed process of recording, running, and editing macros, including adding a button for one-click execution.', "Advantages of VBA in macros: time-saving, support for English-like statements, and customization of Excel's behavior.", 'VBA editor interface components: toolbar, menu bar, VBA code window, project explorer, properties window, immediate window, and watch window.', 'Explanation of opening the Visual Basic for Applications window using Alt F11 or clicking on the Visual Basic option.', 'Demonstration of recording a macro and performing tasks using VBA, showcasing practical application.']}, {'end': 8821.862, 'segs': [{'end': 6389.263, 'src': 'embed', 'start': 6359.145, 'weight': 2, 'content': [{'end': 6370.951, 'text': "so this is the code or a format of the vba code that it looks like and we'll try to do some basic functions with the vba and discuss this in further details.", 'start': 6359.145, 'duration': 11.806}, {'end': 6373.798, 'text': 'So for ease of explanation,', 'start': 6371.897, 'duration': 1.901}, {'end': 6389.263, 'text': "I have picked up a blank spreadsheet and I'll try to explain some basic functions and the commands and the codes that we use to accomplish the task with VBA.", 'start': 6373.798, 'duration': 15.465}], 'summary': 'An introduction to vba code for basic functions in a blank spreadsheet', 'duration': 30.118, 'max_score': 6359.145, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko6359145.jpg'}, {'end': 6447.375, 'src': 'embed', 'start': 6420.409, 'weight': 4, 'content': [{'end': 6424.79, 'text': 'and whatever is there should be here, and this is the name of the out of the macro.', 'start': 6420.409, 'duration': 4.381}, {'end': 6430.411, 'text': "Now I'll explain it further about the dim statement why is it used and what does it use for.", 'start': 6425.25, 'duration': 5.161}, {'end': 6439.773, 'text': 'So dim is basically a keyword which is the short form of dimension and it is used to declare variables in the VBA.', 'start': 6431.192, 'duration': 8.581}, {'end': 6447.375, 'text': "So here I am trying to specify a variable called x and how I'm going to do it.", 'start': 6439.853, 'duration': 7.522}], 'summary': "Explanation of 'dim' statement for declaring variables in vba.", 'duration': 26.966, 'max_score': 6420.409, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko6420409.jpg'}, {'end': 6545.777, 'src': 'embed', 'start': 6514.943, 'weight': 0, 'content': [{'end': 6521.554, 'text': 'So you see, here in the excel sheet it executes the value of the cell B5 to be 500..', 'start': 6514.943, 'duration': 6.611}, {'end': 6528.606, 'text': 'So this is one simple function that you can do with the dimension keyboard or DIN keyboard.', 'start': 6521.554, 'duration': 7.052}, {'end': 6532.018, 'text': 'And you can assign the value as integers.', 'start': 6529.835, 'duration': 2.183}, {'end': 6545.777, 'text': 'Now we proceed further with some more examples and how we assign the string value as a text or something else.', 'start': 6532.579, 'duration': 13.198}], 'summary': 'Excel sheet executes b5 cell value as 500, demonstrating simple function assignment and integer values, then proceeds with examples and string value assignments.', 'duration': 30.834, 'max_score': 6514.943, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko6514943.jpg'}, {'end': 7350.968, 'src': 'embed', 'start': 7288.829, 'weight': 3, 'content': [{'end': 7297.456, 'text': "Okay So now with this code, I mean, I'm going to assign a value of 250 to the cell V5.", 'start': 7288.829, 'duration': 8.627}, {'end': 7298.137, 'text': "Let's see.", 'start': 7297.756, 'duration': 0.381}, {'end': 7300.078, 'text': 'Run it.', 'start': 7299.758, 'duration': 0.32}, {'end': 7309, 'text': 'See Again, I do it to A10.', 'start': 7300.599, 'duration': 8.401}, {'end': 7315.963, 'text': 'OK So this is how you can assign a value to a particular cell.', 'start': 7310.841, 'duration': 5.122}, {'end': 7322.225, 'text': 'Now I can run the same thing and give a range of cells.', 'start': 7317.863, 'duration': 4.362}, {'end': 7325.246, 'text': 'Let me make it very simple here.', 'start': 7323.386, 'duration': 1.86}, {'end': 7332.449, 'text': 'I give it as B1.', 'start': 7331.709, 'duration': 0.74}, {'end': 7346.887, 'text': 'through B5 as 450.', 'start': 7335.503, 'duration': 11.384}, {'end': 7350.968, 'text': "so let's see what it does see.", 'start': 7346.887, 'duration': 4.081}], 'summary': 'Assigning values to specific cells, e.g., v5: 250, a10: unspecified, b1-b5: 450.', 'duration': 62.139, 'max_score': 7288.829, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko7288829.jpg'}, {'end': 7522.179, 'src': 'embed', 'start': 7482.782, 'weight': 1, 'content': [{'end': 7490.887, 'text': 'Okay, in the next example, there will be a scenario where you need to select a range of sets in a sheet.', 'start': 7482.782, 'duration': 8.105}, {'end': 7492.928, 'text': 'So this can also be done.', 'start': 7490.987, 'duration': 1.941}, {'end': 7495.61, 'text': 'This task can also be performed using VBA.', 'start': 7492.948, 'duration': 2.662}, {'end': 7498.172, 'text': 'So I will show you how.', 'start': 7496.21, 'duration': 1.962}, {'end': 7504.415, 'text': "This is our, let's say select a range.", 'start': 7498.192, 'duration': 6.223}, {'end': 7516.156, 'text': "Okay, and And I'm going to set a dim variable as an example.", 'start': 7505.636, 'duration': 10.52}, {'end': 7518.937, 'text': "What I'm doing is I'm specifying a range.", 'start': 7516.736, 'duration': 2.201}, {'end': 7522.179, 'text': "I'm going to select a range of cells.", 'start': 7519.438, 'duration': 2.741}], 'summary': 'Demonstrating how to select a range of cells using vba in an example.', 'duration': 39.397, 'max_score': 7482.782, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko7482782.jpg'}], 'start': 6296.754, 'title': 'Vba fundamentals in excel', 'summary': 'Introduces vba basics, covering dim keyword usage, assigning values to cells, automating cell selection and counting, if statement, and for loop, with a total of 8 examples and quantifiable data such as 250, 450, and 230.', 'chapters': [{'end': 6513.139, 'start': 6296.754, 'title': 'Introduction to vba in excel', 'summary': "Introduces the basics of vba in excel, covering the structure of a vba code, the use of the 'dim' keyword to declare variables, and the execution of a simple vba code to assign a value to a cell, with a focus on using a blank spreadsheet as an example.", 'duration': 216.385, 'highlights': ["The chapter introduces the basics of VBA in Excel Covers the structure of a VBA code and the use of the 'dim' keyword to declare variables", "Execution of a simple VBA code to assign a value to a cell Demonstrates assigning a value of 500 to a cell using the 'dim' keyword and running the code, providing practical insight into VBA execution", 'Using a blank spreadsheet as an example for explanation Illustrates the explanation of VBA basics using a blank spreadsheet as a practical example, enhancing understanding for learners']}, {'end': 7240.283, 'start': 6514.943, 'title': 'Using dim keyword in vba', 'summary': 'Discusses the usage of the dim keyword in vba to set variables, including examples of assigning values as integers, strings, and doubles. it also demonstrates using vba to display message boxes and populate cells in excel, with a total of 8 examples covered.', 'duration': 725.34, 'highlights': ['The chapter covers the usage of the DIM keyword in VBA to set variables, including assigning values as integers, strings, and doubles. It includes examples of assigning the value of a cell as 500, using DIMM as a double, and storing date values and text using string.', 'Demonstrates using VBA to display message boxes and populate cells in Excel, including examples of displaying a message box with a specified value and using a message box for welcome messages. It shows using VBA to display message boxes with specified values and to create welcome messages on Excel, with examples of populating cells with user input and displaying customized welcome messages.', 'The chapter provides 8 examples of using DIM and VBA in Excel, including assigning values, displaying messages, and populating cells. It covers a total of 8 examples, including assigning values to cells, displaying messages with specified values, and populating cells with user input and welcome messages.']}, {'end': 7624.937, 'start': 7244.048, 'title': 'Assigning values using vba', 'summary': 'Demonstrates how to assign values to specific cells and ranges using vba, showcasing examples of assigning single values and ranges with quantifiable data such as 250, 450, and 230, as well as selecting and setting values for ranges of cells.', 'duration': 380.889, 'highlights': ['The chapter demonstrates how to assign values to specific cells and ranges using VBA, showcasing examples of assigning single values and ranges such as 250, 450, and 230.', 'The examples also include selecting and setting values for ranges of cells, providing practical applications for regular daily tasks.', 'The demonstration emphasizes the simplicity and efficiency of using VBA to assign values and select cell ranges, making tasks like copy, paste, and functions easier to perform.']}, {'end': 8111.084, 'start': 7624.937, 'title': 'Automating cell selection and counting', 'summary': 'Demonstrates how to automate the selection of cells in different worksheets, copy and paste cell contents, and count the number of cells, columns, and rows using vba, making it easier to perform these tasks for daily operations and providing examples of selecting, copying, and counting cells.', 'duration': 486.147, 'highlights': ['Automating selection of cells in different worksheets Demonstrates how to automate the selection of cells in different worksheets using VBA, simplifying the process of selecting the same range of cells in multiple sheets.', 'Automating copying and pasting cell contents Illustrates the automation of copying and pasting cell contents in VBA, making it easier to perform the task of copying cell contents and pasting them to another location.', 'Automating counting the number of cells, columns, and rows Shows how to automate the counting of the number of cells, columns, and rows in VBA, providing examples of counting cells, columns, and rows and displaying the counts through message boxes.']}, {'end': 8821.862, 'start': 8111.084, 'title': 'If statement and for loop', 'summary': 'Covers the basics of the if statement, including syntax and usage with a practical example, followed by a detailed explanation of the for loop statement and its application with examples, showcasing how to execute tasks multiple times and color code cells based on a specified pattern.', 'duration': 710.778, 'highlights': ['Explanation of If Statement Syntax and Usage The chapter explains the syntax and usage of the if statement, emphasizing its simplicity and application in programming, with a practical example of setting conditions and specifying the output based on the condition.', 'Practical Example of Using If Statement A practical example of using the if statement to specify conditions and outcomes based on a score, demonstrating how to set variables, specify conditions, and determine the output.', 'Implementation of For Loop Statement with Practical Example The chapter provides a detailed example of using the for loop statement, demonstrating how to execute a task multiple times sequentially, with the practical application of filling a column with values and color coding another column based on a specified pattern.']}], 'duration': 2525.108, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko6296754.jpg', 'highlights': ['The chapter provides 8 examples of using DIM and VBA in Excel, including assigning values, displaying messages, and populating cells.', 'Demonstrates how to automate the selection of cells in different worksheets using VBA, simplifying the process of selecting the same range of cells in multiple sheets.', "The chapter introduces the basics of VBA in Excel Covers the structure of a VBA code and the use of the 'dim' keyword to declare variables.", 'The chapter demonstrates how to assign values to specific cells and ranges using VBA, showcasing examples of assigning single values and ranges such as 250, 450, and 230.', 'The chapter covers the usage of the DIM keyword in VBA to set variables, including assigning values as integers, strings, and doubles.']}, {'end': 10410.192, 'segs': [{'end': 8858.582, 'src': 'embed', 'start': 8822.522, 'weight': 0, 'content': [{'end': 8828.466, 'text': 'So there are numerous examples and exercises that we can perform tasks we can perform with the BBA.', 'start': 8822.522, 'duration': 5.944}, {'end': 8834.749, 'text': 'And this can be found in detail in our next courses, advanced courses.', 'start': 8829.486, 'duration': 5.263}, {'end': 8836.24, 'text': 'Now, what is regression?', 'start': 8835.239, 'duration': 1.001}, {'end': 8846.29, 'text': 'So when you talk about statistical modeling, regression analysis is used to estimate the relationship between two or more variables.', 'start': 8836.781, 'duration': 9.509}, {'end': 8858.582, 'text': 'And basically we would want to look at two or more variables and basically a relationship of those variables with the response or the target right?', 'start': 8846.83, 'duration': 11.752}], 'summary': 'Regression analysis estimates relationship between variables in statistical modeling.', 'duration': 36.06, 'max_score': 8822.522, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko8822522.jpg'}, {'end': 8950.59, 'src': 'embed', 'start': 8923.318, 'weight': 1, 'content': [{'end': 8931.24, 'text': 'So, when you talk about your independent variables or, as we say, predictors or explanatory variables,', 'start': 8923.318, 'duration': 7.922}, {'end': 8937.461, 'text': 'these are the factors that might influence the dependent variable.', 'start': 8931.24, 'duration': 6.221}, {'end': 8941.784, 'text': "And when you talk about dependent variable, that's your response or target.", 'start': 8938.101, 'duration': 3.683}, {'end': 8945.326, 'text': "That's the main factor you are trying to understand or predict.", 'start': 8942.284, 'duration': 3.042}, {'end': 8950.59, 'text': 'So we can plot a scatter plot to see relationship between variables.', 'start': 8945.927, 'duration': 4.663}], 'summary': 'Independent variables influence the dependent variable; scatter plot shows their relationship.', 'duration': 27.272, 'max_score': 8923.318, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko8923318.jpg'}, {'end': 9420.087, 'src': 'embed', 'start': 9387.31, 'weight': 3, 'content': [{'end': 9395.616, 'text': 'Or we would want to do the regression analysis to demonstrate the relationship between dependent and independent variables.', 'start': 9387.31, 'duration': 8.306}, {'end': 9401.501, 'text': 'So normally when you talk about your linear regression equation, you might have known about it.', 'start': 9396.237, 'duration': 5.264}, {'end': 9413.53, 'text': 'So it is basically y, when you say y equals, you can say bx plus a plus c.', 'start': 9401.601, 'duration': 11.929}, {'end': 9420.087, 'text': 'And this would be normally the mathematical equation for a linear regression.', 'start': 9413.53, 'duration': 6.557}], 'summary': 'Regression analysis shows the relationship between dependent and independent variables, typically represented by the equation y=bx+a+c.', 'duration': 32.777, 'max_score': 9387.31, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko9387310.jpg'}, {'end': 9792.483, 'src': 'embed', 'start': 9705.121, 'weight': 2, 'content': [{'end': 9709.925, 'text': 'So we go get into data we get into data analysis we choose regression.', 'start': 9705.121, 'duration': 4.804}, {'end': 9715.359, 'text': 'We have already chosen our Y range, which is column C.', 'start': 9711.038, 'duration': 4.321}, {'end': 9717.099, 'text': 'X range is multiple variables.', 'start': 9715.359, 'duration': 1.74}, {'end': 9724.001, 'text': 'We have gone for labels, output range, which we would want to select a particular cell.', 'start': 9717.28, 'duration': 6.721}, {'end': 9725.942, 'text': 'So let me select this.', 'start': 9724.701, 'duration': 1.241}, {'end': 9733.043, 'text': "I'm going for residuals, line fit plots, and normal probability plots, and say OK.", 'start': 9725.962, 'duration': 7.081}, {'end': 9735.744, 'text': 'So now we have our plots.', 'start': 9733.063, 'duration': 2.681}, {'end': 9741.759, 'text': 'We can just separate them to have a better understanding.', 'start': 9738.677, 'duration': 3.082}, {'end': 9752.187, 'text': 'Yeah, and that basically shows you the variables.', 'start': 9741.779, 'duration': 10.408}, {'end': 9757.571, 'text': 'So here it shows me units sold and then what are the predicted units sold.', 'start': 9752.447, 'duration': 5.124}, {'end': 9760.63, 'text': 'And here the variable is temperature.', 'start': 9758.007, 'duration': 2.623}, {'end': 9762.792, 'text': 'here the variable is price of ice cream.', 'start': 9760.63, 'duration': 2.162}, {'end': 9768.979, 'text': "right, so that's your one variable and second variable, and then you also have your normal probability plot.", 'start': 9762.792, 'duration': 6.187}, {'end': 9774.785, 'text': 'now to interpret this or to understand what we have here, where we look at your', 'start': 9768.979, 'duration': 5.806}, {'end': 9777.497, 'text': 'summary output.', 'start': 9776.317, 'duration': 1.18}, {'end': 9781.939, 'text': 'so it is good to understand what excel is helping us out.', 'start': 9777.497, 'duration': 4.442}, {'end': 9789.382, 'text': 'so, basically, when we do a regression in excel, it is performing all the calculations automatically.', 'start': 9781.939, 'duration': 7.443}, {'end': 9792.483, 'text': 'the interpretation is something which we need to understand.', 'start': 9789.382, 'duration': 3.101}], 'summary': 'Regression analysis in excel with y range column c, multiple x variables, and interpretation of plots and summary output.', 'duration': 87.362, 'max_score': 9705.121, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko9705121.jpg'}, {'end': 9851.483, 'src': 'embed', 'start': 9823.742, 'weight': 7, 'content': [{'end': 9830.306, 'text': 'So this can be any value between your when you talk about your correlation coefficient.', 'start': 9823.742, 'duration': 6.564}, {'end': 9837.584, 'text': 'It can be any value between minus 1 and 1, and its absolute value indicates the relationship strength.', 'start': 9831.115, 'duration': 6.469}, {'end': 9842.94, 'text': 'So, the larger the absolute value, the stronger the relationship.', 'start': 9838.678, 'duration': 4.262}, {'end': 9851.483, 'text': 'So, 1 basically means a strong positive relationship, minus 1 means a strong negative relationship and 0 means no relationship at all.', 'start': 9843.04, 'duration': 8.443}], 'summary': 'Correlation coefficient ranges from -1 to 1, indicating relationship strength. 1=strong positive, -1=strong negative, 0=no relationship.', 'duration': 27.741, 'max_score': 9823.742, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko9823742.jpg'}, {'end': 9970.662, 'src': 'embed', 'start': 9881.533, 'weight': 8, 'content': [{'end': 9896.638, 'text': 'Now, when we talk about looking at the value of your R square, so it is basically we can be seeing that this is 0.450788.', 'start': 9881.533, 'duration': 15.105}, {'end': 9898.779, 'text': "that's the value which I'm talking about.", 'start': 9896.638, 'duration': 2.141}, {'end': 9905.103, 'text': 'so it basically means 45% of our values fit the regression analysis model.', 'start': 9898.779, 'duration': 6.324}, {'end': 9915.11, 'text': 'So in other words, 45% of dependent variables, that is your Y values, are explained by the independent variables, that is your X values.', 'start': 9905.683, 'duration': 9.427}, {'end': 9922.816, 'text': 'So normally if you have R squared value higher, then that is considered as a good fit.', 'start': 9916.511, 'duration': 6.305}, {'end': 9925.495, 'text': 'And here we have considered multiple variables.', 'start': 9923.553, 'duration': 1.942}, {'end': 9934.705, 'text': 'Now when you talk about your adjusted R square, it is the R square adjusted for the number of independent variables in the model.', 'start': 9926.216, 'duration': 8.489}, {'end': 9942.493, 'text': 'So you would want to use this value instead of R square for multiple regression analysis.', 'start': 9935.326, 'duration': 7.167}, {'end': 9944.656, 'text': 'Such as in our case.', 'start': 9943.634, 'duration': 1.022}, {'end': 9947.499, 'text': 'Now you also have standard error.', 'start': 9945.516, 'duration': 1.983}, {'end': 9955.367, 'text': "So that's basically goodness of fit measure that basically shows the precision of your regression analysis.", 'start': 9947.839, 'duration': 7.528}, {'end': 9960.633, 'text': 'So the smaller the value, the more certain you can be about your regression equation.', 'start': 9955.888, 'duration': 4.745}, {'end': 9970.662, 'text': 'So when we talk about your R square, that represents the percentage of dependent variables, variance, that is explained by the model.', 'start': 9961.411, 'duration': 9.251}], 'summary': 'R-squared value is 0.45, indicating 45% fit for regression analysis, with emphasis on adjusted r-square for multiple regression analysis.', 'duration': 89.129, 'max_score': 9881.533, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko9881533.jpg'}, {'end': 10115.474, 'src': 'embed', 'start': 10082.329, 'weight': 11, 'content': [{'end': 10094.567, 'text': "So when we usually talk about simple linear regression, the ANOVA part is not really used or it's rarely used.", 'start': 10082.329, 'duration': 12.238}, {'end': 10107.211, 'text': 'right, but when we look at the significance f value, that gives an idea of how reliably or statistically significant our results are.', 'start': 10095.228, 'duration': 11.983}, {'end': 10114.113, 'text': "so if significance f is less than 0.05, that's five percent.", 'start': 10107.211, 'duration': 6.902}, {'end': 10115.474, 'text': 'our model is okay.', 'start': 10114.113, 'duration': 1.361}], 'summary': 'Simple linear regression rarely uses anova. a significance f value less than 0.05 indicates a reliable model.', 'duration': 33.145, 'max_score': 10082.329, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10082329.jpg'}, {'end': 10168.831, 'src': 'embed', 'start': 10139.139, 'weight': 4, 'content': [{'end': 10145.781, 'text': 'So we know we are looking at our variables and then we are also look at looking at our intercept,', 'start': 10139.139, 'duration': 6.642}, {'end': 10150.807, 'text': 'which basically gives you more details on your coefficients.', 'start': 10145.781, 'duration': 5.026}, {'end': 10158.428, 'text': 'so the the main important thing when we talk about regression analysis is basically your coefficients.', 'start': 10150.807, 'duration': 7.621}, {'end': 10168.831, 'text': 'it basically enables you to build a linear regression equation uh, in the form of y equals bx plus a, which i explained earlier,', 'start': 10158.428, 'duration': 10.403}], 'summary': 'Regression analysis focuses on coefficients to build linear equations.', 'duration': 29.692, 'max_score': 10139.139, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10139139.jpg'}, {'end': 10257.02, 'src': 'embed', 'start': 10230.946, 'weight': 14, 'content': [{'end': 10238.411, 'text': 'because independent variables are never perfect predictors of your dependent variables and the residuals.', 'start': 10230.946, 'duration': 7.465}, {'end': 10247.357, 'text': 'Basically, help us in understanding how far the actual values are from the predicted values so.', 'start': 10239.412, 'duration': 7.945}, {'end': 10250.815, 'text': "That's what we see here in the residual section.", 'start': 10247.833, 'duration': 2.982}, {'end': 10257.02, 'text': 'So, for example, when we look at this one, which is 11.209 and the actual is 12,,', 'start': 10251.476, 'duration': 5.544}], 'summary': 'Residuals measure the difference between actual and predicted values.', 'duration': 26.074, 'max_score': 10230.946, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10230946.jpg'}, {'end': 10365.822, 'src': 'embed', 'start': 10339.531, 'weight': 15, 'content': [{'end': 10348.035, 'text': "so it's pretty simple here you can add more details to your scatter plot or the plots, what we have created,", 'start': 10339.531, 'duration': 8.504}, {'end': 10351.717, 'text': 'or some people prefer to do it using formulas.', 'start': 10348.035, 'duration': 3.682}, {'end': 10362.861, 'text': 'so basically, we have some statistical functions that can help you do linear regression analysis, such as You have linux, you have slope,', 'start': 10351.717, 'duration': 11.144}, {'end': 10365.822, 'text': 'you have intercept, you have correlation, and so on.', 'start': 10362.861, 'duration': 2.961}], 'summary': 'Learn to enhance scatter plots with statistical functions for linear regression analysis, including linux, slope, intercept, and correlation.', 'duration': 26.291, 'max_score': 10339.531, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10339531.jpg'}], 'start': 8822.522, 'title': 'Regression analysis in excel', 'summary': 'Covers understanding and performing regression analysis in excel, explaining the concept, process, and key metrics, with a focus on predicting units sold based on temperature and price of ice cream, understanding the relationship between variables, interpreting summary outputs, and emphasizing model accuracy through comparison of predicted and actual values.', 'chapters': [{'end': 9282.429, 'start': 8822.522, 'title': 'Understanding regression analysis', 'summary': 'Explains the concept of regression analysis, its use in estimating the relationship between variables, and how to perform regression analysis in excel to predict the units sold based on temperature and price of ice cream.', 'duration': 459.907, 'highlights': ['Regression analysis is used to estimate the relationship between two or more variables in statistical modeling. Regression analysis is used to estimate the relationship between two or more variables in statistical modeling.', 'Understanding the distinction between independent variables (predictors) and dependent variables (response or target) is crucial in regression analysis. Understanding the distinction between independent variables (predictors) and dependent variables (response or target) is crucial in regression analysis.', 'Performing regression analysis in Excel to predict units sold based on temperature and price of ice cream involves creating a scatter plot and selecting X and Y values. Performing regression analysis in Excel to predict units sold based on temperature and price of ice cream involves creating a scatter plot and selecting X and Y values.']}, {'end': 9503.88, 'start': 9282.61, 'title': 'Linear regression analysis', 'summary': 'Discusses the process of performing linear regression analysis in excel, including understanding the regression equation, coefficients, and the relationship between dependent and independent variables, aiming to demonstrate the relationship between them and explaining the mathematical representation of the linear equation.', 'duration': 221.27, 'highlights': ['The regression equation y = bx + a + c demonstrates the mathematical representation for linear regression, where x is the independent variable, y is the dependent variable, a is the y-intercept, and b is the slope of the regression line. The regression equation y = bx + a + c represents the mathematical model for linear regression, with x as the independent variable, y as the dependent variable, a as the y-intercept, and b as the slope of the regression line.', 'The process involves understanding the coefficients of the regression line, including the y-intercept, the slope of the regression line, and the error term, which represents the difference between the actual and predicted values of the dependent variable. The analysis includes understanding the coefficients of the regression line, such as the y-intercept, the slope (rate of change for y as x changes), and the error term, which signifies the variance between the actual and predicted values of the dependent variable.', 'Explaining the process of linear regression in Excel, including reducing the equation to y = bx + a and understanding the mathematical representation of the linear equation. The chapter explains the process of linear regression in Excel, including simplifying the equation to y = bx + a and comprehending the mathematical representation of the linear equation.']}, {'end': 9792.483, 'start': 9503.88, 'title': 'Regression analysis in excel', 'summary': 'Covers performing regression analysis in excel, including selecting dependent and independent variables, output range selection, and interpreting the summary output and plots.', 'duration': 288.603, 'highlights': ['Performing regression analysis in Excel involves selecting dependent and independent variables and output range, with the software performing automatic calculations.', 'The interpretation of the regression analysis output in Excel is crucial for understanding the results and making informed decisions.', 'The process includes selecting the input Y range as the dependent variable, choosing multiple variables for the X range, and selecting output ranges for residuals, line fit plots, and normal probability plots.', 'The chapter emphasizes the importance of understanding the interpretation of regression analysis results in Excel for making informed decisions.']}, {'end': 10115.474, 'start': 9792.483, 'title': 'Understanding regression analysis in excel', 'summary': 'Explains the key metrics in regression analysis using excel, including the correlation coefficient, coefficient of determination, adjusted r square, standard error, and anova, with a focus on interpreting their values and significance for a multiple regression model.', 'duration': 322.991, 'highlights': ['The larger the absolute value of the correlation coefficient, the stronger the relationship, with 1 indicating a strong positive relationship, -1 a strong negative relationship, and 0 no relationship at all. The correlation coefficient measures the strength of linear relationship between two variables, with the absolute value indicating the relationship strength.', 'The R square value of 0.450788 indicates that 45% of the dependent variables are explained by the independent variables in the multiple regression model. The coefficient of determination (R square) serves as an indicator of the goodness of fit, with a higher value considered a good fit.', 'The adjusted R square is used for multiple regression analysis, considering the number of independent variables in the model. Adjusted R square accounts for the number of independent variables in the model and is preferred for multiple regression analysis.', 'The standard error represents the precision of the regression analysis, with a smaller value indicating more certainty about the regression equation. Standard error is a measure of the precision of the regression analysis, showing the average distance that data points fall from the regression line.', 'The ANOVA values, including F statistic and significance F, are used to test the overall significance of the model and the reliability of the results. ANOVA values provide information about the levels of variability within the regression model and the overall significance and reliability of the results.']}, {'end': 10410.192, 'start': 10115.474, 'title': 'Performing regression analysis', 'summary': "Discusses the importance of coefficients, residual output, and percentile values in understanding regression analysis, and emphasizes the comparison between predicted and actual values to gauge the model's accuracy.", 'duration': 294.718, 'highlights': ['The importance of coefficients in building a linear regression equation is highlighted, enabling a better understanding of the regression analysis output.', 'The comparison between predicted and actual unit sold values is emphasized, with specific examples such as 11.209 vs. 12 and 15 vs. 13.5, to illustrate the impact of independent variables on the dependent variable.', 'The significance of residuals in understanding the disparity between predicted and actual values is explained, emphasizing that independent variables are never perfect predictors of the dependent variables.', 'The use of statistical functions such as linux, slope, intercept, and correlation for linear regression analysis is mentioned, showcasing the potential for further analysis and insights into the relationship between variables.']}], 'duration': 1587.67, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko8822522.jpg', 'highlights': ['Regression analysis estimates the relationship between variables in statistical modeling.', 'Understanding the distinction between independent and dependent variables is crucial in regression analysis.', 'Performing regression analysis in Excel involves creating a scatter plot and selecting X and Y values.', 'The regression equation y = bx + a + c represents the mathematical model for linear regression.', 'Understanding the coefficients of the regression line, including the y-intercept, the slope, and the error term, is crucial.', 'Performing regression analysis in Excel involves selecting dependent and independent variables and output range.', 'The interpretation of the regression analysis output in Excel is crucial for understanding the results and making informed decisions.', 'The larger the absolute value of the correlation coefficient, the stronger the relationship between variables.', 'The R square value of 0.450788 indicates that 45% of the dependent variables are explained by the independent variables.', 'The adjusted R square is used for multiple regression analysis, considering the number of independent variables in the model.', 'The standard error represents the precision of the regression analysis, with a smaller value indicating more certainty about the regression equation.', 'ANOVA values, including F statistic and significance F, are used to test the overall significance of the model and the reliability of the results.', 'The importance of coefficients in building a linear regression equation is highlighted.', 'The comparison between predicted and actual unit sold values is emphasized to illustrate the impact of independent variables on the dependent variable.', 'The significance of residuals in understanding the disparity between predicted and actual values is explained.', 'The use of statistical functions such as linest, slope, intercept, and correlation for linear regression analysis is mentioned.']}, {'end': 11201.054, 'segs': [{'end': 10574.818, 'src': 'embed', 'start': 10536.824, 'weight': 0, 'content': [{'end': 10539.285, 'text': 'Now coming to the features of Power Query.', 'start': 10536.824, 'duration': 2.461}, {'end': 10544.568, 'text': 'Excel Power Query allows you to clean, transform, manipulate and process your data for analysis.', 'start': 10540.106, 'duration': 4.462}, {'end': 10548.53, 'text': 'It helps you to automate repetitive tasks that you want to do it over and over again.', 'start': 10545.068, 'duration': 3.462}, {'end': 10551.851, 'text': 'You can search for data sources and make connections as and when you want.', 'start': 10548.79, 'duration': 3.061}, {'end': 10556.634, 'text': 'Power Query helps you to prepare and shape the data in the right format for performing analysis.', 'start': 10552.332, 'duration': 4.302}, {'end': 10564.552, 'text': 'And finally, once your data is ready, you can share your findings or use your query to create interactive reports and dashboards.', 'start': 10557.748, 'duration': 6.804}, {'end': 10570.535, 'text': "Now let's have a glance at the demo that we are going to work on in this video.", 'start': 10566.733, 'duration': 3.802}, {'end': 10574.818, 'text': "So we'll look at how to load data from different sources.", 'start': 10572.356, 'duration': 2.462}], 'summary': 'Excel power query enables data cleaning, transformation, and automation for analysis and reporting.', 'duration': 37.994, 'max_score': 10536.824, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10536824.jpg'}, {'end': 10707.225, 'src': 'embed', 'start': 10677.739, 'weight': 3, 'content': [{'end': 10681.5, 'text': 'Once I click on it, it will ask me to give the location where the file is there.', 'start': 10677.739, 'duration': 3.761}, {'end': 10684.16, 'text': 'So my file is on desktop.', 'start': 10682.34, 'duration': 1.82}, {'end': 10686.72, 'text': "I'll click on Power Query files.", 'start': 10684.44, 'duration': 2.28}, {'end': 10689.781, 'text': 'And here you can see, I have my employee TXT file.', 'start': 10687.101, 'duration': 2.68}, {'end': 10691.481, 'text': 'You can see the type here.', 'start': 10690.461, 'duration': 1.02}, {'end': 10696.742, 'text': 'It says text document, click on EMP and hit import.', 'start': 10691.541, 'duration': 5.201}, {'end': 10701.063, 'text': 'Now this will take some time to import the file onto Excel.', 'start': 10698.082, 'duration': 2.981}, {'end': 10704.784, 'text': "it's establishing a connection.", 'start': 10703.464, 'duration': 1.32}, {'end': 10707.225, 'text': 'you can see there you go.', 'start': 10704.784, 'duration': 2.441}], 'summary': 'Imported employee txt file onto excel from desktop using power query.', 'duration': 29.486, 'max_score': 10677.739, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10677739.jpg'}, {'end': 10819.556, 'src': 'embed', 'start': 10789.469, 'weight': 4, 'content': [{'end': 10795.814, 'text': "So I'll click on the second chart which says the count of name by city and I'll click on OK.", 'start': 10789.469, 'duration': 6.345}, {'end': 10799.677, 'text': 'Here you can see.', 'start': 10797.235, 'duration': 2.442}, {'end': 10803.52, 'text': 'we have a nice clustered bar chart and you can see.', 'start': 10799.677, 'duration': 3.843}, {'end': 10807.993, 'text': 'in Bangalore there were five employees.', 'start': 10804.252, 'duration': 3.741}, {'end': 10811.654, 'text': 'in Hyderabad we had two and in Nasik we had one.', 'start': 10807.993, 'duration': 3.661}, {'end': 10813.074, 'text': 'you can see the count here.', 'start': 10811.654, 'duration': 1.42}, {'end': 10815.895, 'text': 'this is a pivot table.', 'start': 10813.074, 'duration': 2.821}, {'end': 10819.556, 'text': 'if you go back to our actual data here you can see.', 'start': 10815.895, 'duration': 3.661}], 'summary': 'The chart displays the count of employees by city: bangalore (5), hyderabad (2), and nasik (1).', 'duration': 30.087, 'max_score': 10789.469, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10789469.jpg'}, {'end': 10958.813, 'src': 'embed', 'start': 10927.538, 'weight': 6, 'content': [{'end': 10939.32, 'text': "Okay, now it's navigating and okay, you can see, power query feature in excel has given us a list of tables which you can see here.", 'start': 10927.538, 'duration': 11.782}, {'end': 10942.282, 'text': 'we have something called as document.', 'start': 10939.32, 'duration': 2.962}, {'end': 10950.427, 'text': "there's a key table and here you can see there's one table which was extracted from the Wikipedia page,", 'start': 10942.282, 'duration': 8.145}, {'end': 10957.772, 'text': 'and the table which I am interested in is this table which has the list of European Cup finals.', 'start': 10950.427, 'duration': 7.345}, {'end': 10958.813, 'text': 'you can see it here.', 'start': 10957.772, 'duration': 1.041}], 'summary': 'Power query in excel provides a list of tables, including the european cup finals table extracted from wikipedia.', 'duration': 31.275, 'max_score': 10927.538, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10927538.jpg'}, {'end': 11077.688, 'src': 'embed', 'start': 11042.804, 'weight': 7, 'content': [{'end': 11050.237, 'text': 'now here, if you see the first two rows, the values are the same.', 'start': 11042.804, 'duration': 7.433}, {'end': 11051.758, 'text': 'you can see season season.', 'start': 11050.237, 'duration': 1.521}, {'end': 11059.803, 'text': "these are all repeated, which we actually don't need, and these are pretty similar to our column names.", 'start': 11051.758, 'duration': 8.045}, {'end': 11068.468, 'text': 'similarly, if I scroll down, you see there are some rows which have null values.', 'start': 11059.803, 'duration': 8.665}, {'end': 11075.747, 'text': 'So actually, these rows do not add up any value or do not add any value to our data.', 'start': 11069.502, 'duration': 6.245}, {'end': 11077.688, 'text': "So we'll clean this data first.", 'start': 11075.967, 'duration': 1.721}], 'summary': 'Data cleaning: remove repeated values and rows with null values.', 'duration': 34.884, 'max_score': 11042.804, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko11042804.jpg'}, {'end': 11201.054, 'src': 'embed', 'start': 11173.342, 'weight': 8, 'content': [{'end': 11180.425, 'text': 'So the last season of UEFA Champions League was held in 2020, which is this year,', 'start': 11173.342, 'duration': 7.083}, {'end': 11189.069, 'text': 'and Bayern Munich won the Champions League against a French team that was Paris Saint-Germain, by a score of 1-0..', 'start': 11180.425, 'duration': 8.644}, {'end': 11198.833, 'text': 'Now, if you see the last value, that is the attendance, you can see the value is zero, which means there were no spectators in the stadium.', 'start': 11190.469, 'duration': 8.364}, {'end': 11201.054, 'text': 'It was because of the COVID conditions.', 'start': 11199.213, 'duration': 1.841}], 'summary': 'In 2020, bayern munich won uefa champions league 1-0 against paris saint-germain, with zero attendance due to covid.', 'duration': 27.712, 'max_score': 11173.342, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko11173342.jpg'}], 'start': 10410.732, 'title': 'Using excel power query for data import and analysis', 'summary': 'Explores the benefits of excel power query, such as connecting to diverse data sources and handling big data. it also delves into importing and visualizing data, including importing from web files and analyzing data insights, such as covid-impacted attendance at the last uefa champions league in 2020.', 'chapters': [{'end': 10574.818, 'start': 10410.732, 'title': 'Excel power query benefits', 'summary': 'Explains the benefits of using excel power query, including its ability to connect to various data sources, handle big data, and provide tools for data preparation, transformation, and analysis.', 'duration': 164.086, 'highlights': ['Excel Power Query allows you to connect to a broad range of data sources, such as relational databases, web files, text, CSV, JSON files, and cloud data. This feature addresses the difficulty in data connections, providing flexibility and enhancing data accessibility.', 'Power Query enables handling of big data by transforming it into an appropriate size and working with any shape of data from any source. This addresses the challenges related to the volume, variety, and velocity of big data, improving data management and analysis.', 'Power Query provides sophisticated tools for data preparation, transformation, and manipulation, offering an interactive experience to automate repetitive tasks for analysis. This feature enhances data processing efficiency and supports the creation of interactive reports and dashboards.']}, {'end': 10848.134, 'start': 10575.999, 'title': 'Excel power query: import and visualize data', 'summary': 'Covers extracting and importing tables from web files, specifically demonstrating how to import a comma-separated text file into excel using power query, and then visualize the data with a clustered bar chart showing the count of employees by city.', 'duration': 272.135, 'highlights': ['Importing a comma-separated text file into Excel using Power Query Demonstrates how to import a comma-separated text file containing employee data into Excel using Power Query, with the file origin being a text document, and the automatic detection of column names and values.', 'Visualizing employee data with a clustered bar chart Shows how to use the imported employee data to create a clustered bar chart in Excel, depicting the count of employees by city, with specific counts for Bangalore, Hyderabad, and Nasik.', 'Explanation of Excel Power Query features for importing web data Mentions the capabilities of Excel Power Query features for importing data from the web or internet, showcasing its versatility beyond local file imports.']}, {'end': 11201.054, 'start': 10849.434, 'title': 'Importing table data from web to excel', 'summary': "Demonstrates how to import a table from a wikipedia page on the list of european cup and uefa champions league finals onto excel using the power query feature, clean the data by removing redundant rows and null values, and analyze the data to find insights, such as the last season's uefa champions league being held in 2020 with zero attendance due to covid conditions.", 'duration': 351.62, 'highlights': ["The chapter demonstrates how to import a table from a Wikipedia page onto Excel using the Power Query feature. It shows the process of copying the URL of the Wikipedia page, navigating to the 'From Web' option in the Data tab, and utilizing the Power Query feature to extract the desired table with details such as season, winners, scoreline, runner sub, venue, and attendance.", "It explains the process of cleaning the imported data by removing redundant rows and null values. It illustrates the steps to remove redundant rows using the 'Reduce Rows' option under the 'Home' tab and remove null value rows to ensure the data is prepared for analysis.", "The last season of UEFA Champions League was held in 2020 with zero attendance due to COVID conditions. It provides insights into the last season's UEFA Champions League, indicating that it was held in 2020 with zero attendance, attributing it to the COVID conditions, and also mentions the winner and scoreline (Bayern Munich won against Paris Saint-Germain with a score of 1-0)."]}], 'duration': 790.322, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko10410732.jpg', 'highlights': ['Excel Power Query connects to diverse data sources, enhancing data accessibility.', 'Power Query handles big data, improving data management and analysis.', 'Power Query offers tools for data preparation and manipulation, enhancing efficiency.', 'Importing a comma-separated text file into Excel using Power Query.', 'Visualizing employee data with a clustered bar chart in Excel.', 'Excel Power Query features for importing web data showcase its versatility.', 'Importing a table from a Wikipedia page onto Excel using Power Query.', 'Cleaning imported data by removing redundant rows and null values.', "Insights into the last season's UEFA Champions League, including COVID-impacted attendance."]}, {'end': 12083.563, 'segs': [{'end': 11241.822, 'src': 'embed', 'start': 11203.874, 'weight': 0, 'content': [{'end': 11206.116, 'text': "Now let's do some more manipulation to our data.", 'start': 11203.874, 'duration': 2.242}, {'end': 11213.521, 'text': "Suppose this time, I want to add a new column, let's say a stadium name by extracting values from the venue column.", 'start': 11206.836, 'duration': 6.685}, {'end': 11217.048, 'text': 'so let me just show you the venue column.', 'start': 11214.727, 'duration': 2.321}, {'end': 11219.108, 'text': 'so this is our venue column.', 'start': 11217.048, 'duration': 2.06}, {'end': 11222.729, 'text': 'so the first value is the stadium name.', 'start': 11219.108, 'duration': 3.621}, {'end': 11230.431, 'text': 'then we have the city in which the stadium is there and finally we have the country name.', 'start': 11222.729, 'duration': 7.702}, {'end': 11241.822, 'text': "so I want to extract only the stadium name so you can see we have some stadiums like Santiago Bernabeu, there's Wembley, San Siro and other stadium.", 'start': 11230.431, 'duration': 11.391}], 'summary': 'Extract stadium names from venue column for data manipulation.', 'duration': 37.948, 'max_score': 11203.874, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko11203874.jpg'}, {'end': 11388.73, 'src': 'embed', 'start': 11321.85, 'weight': 1, 'content': [{'end': 11330.533, 'text': 'so if you consider this venue column, whatever is there between the two commas is the stadium city.', 'start': 11321.85, 'duration': 8.683}, {'end': 11334.035, 'text': 'for example, Santiago Bernabeu is in Madrid.', 'start': 11330.533, 'duration': 3.502}, {'end': 11336.836, 'text': 'similarly, Wembley stadium is in London.', 'start': 11334.035, 'duration': 2.801}, {'end': 11338.863, 'text': 'San Siro is in Milan.', 'start': 11337.623, 'duration': 1.24}, {'end': 11345.866, 'text': 'So these medial values I want to extract into a new column called as stadium city.', 'start': 11339.644, 'duration': 6.222}, {'end': 11348.027, 'text': "So let's see how to do it.", 'start': 11346.786, 'duration': 1.241}, {'end': 11352.688, 'text': "So I'll click on the venue column and go to extract.", 'start': 11348.807, 'duration': 3.881}, {'end': 11357.51, 'text': "And again, I'll select the extract tab.", 'start': 11353.469, 'duration': 4.041}, {'end': 11362.792, 'text': "And here now I'll choose text between delimiters.", 'start': 11358.59, 'duration': 4.202}, {'end': 11370.854, 'text': "and my first delimiter I'll give is a comma and a space.", 'start': 11365.089, 'duration': 5.765}, {'end': 11381.984, 'text': 'you can see here all the values have a comma and a space, and my end delimiter will be another comma.', 'start': 11370.854, 'duration': 11.13}, {'end': 11382.845, 'text': "then I'll click on.", 'start': 11381.984, 'duration': 0.861}, {'end': 11388.73, 'text': 'OK, this will add a new column to the extreme right of the table.', 'start': 11382.845, 'duration': 5.885}], 'summary': 'Extract stadium cities from venue column using text between delimiters.', 'duration': 66.88, 'max_score': 11321.85, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko11321850.jpg'}, {'end': 12035.227, 'src': 'embed', 'start': 11996.805, 'weight': 4, 'content': [{'end': 12002.85, 'text': 'You can see here, the step has been applied and all the population column from 1951 till 2011 have been unpivoted.', 'start': 11996.805, 'duration': 6.045}, {'end': 12009.609, 'text': 'If you want, you can go ahead and rename these columns.', 'start': 12006.107, 'duration': 3.502}, {'end': 12020.937, 'text': "Let's say I'll write it as population column and let's say this is, I'll rename it to total population value.", 'start': 12009.709, 'duration': 11.228}, {'end': 12028.542, 'text': 'All right.', 'start': 12028.202, 'duration': 0.34}, {'end': 12032.205, 'text': 'Now we are done with our preparation of data.', 'start': 12029.343, 'duration': 2.862}, {'end': 12035.227, 'text': "Let's go to the home tab and click on close and load.", 'start': 12032.465, 'duration': 2.762}], 'summary': 'Data preparation completed, population unpivoted from 1951 to 2011, columns renamed, and closed and loaded.', 'duration': 38.422, 'max_score': 11996.805, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko11996805.jpg'}], 'start': 11203.874, 'title': 'Data manipulation in excel power query', 'summary': "Demonstrates data manipulation in excel power query, showcasing the process of adding new columns from venue data and demonstrating various functions such as 'add column' and 'text before delimiter', and data manipulation techniques including extracting text between delimiters, splitting columns, converting text to uppercase, filtering pivot tables, and unpivoting columns.", 'chapters': [{'end': 11352.688, 'start': 11203.874, 'title': 'Adding new columns from venue data', 'summary': "Demonstrates adding new columns for stadium name and stadium city by extracting values from the venue column, showcasing the process of using 'add column' and 'text before delimiter' functions in the tool, with examples of stadium names and cities extracted.", 'duration': 148.814, 'highlights': ["The process of adding a new column for stadium name by using the 'text before delimiter' function, with examples such as Santiago Bernabeu, Wembley, and San Siro being extracted from the venue column.", "Demonstration of adding a new column for stadium city by extracting values between the commas in the venue column, showcasing examples of cities such as Madrid, London, and Milan being extracted into a new column called 'stadium city'.", 'Explanation of the steps involved in the process, including renaming the columns and applying the changes, providing a comprehensive overview of the data manipulation.']}, {'end': 12083.563, 'start': 11353.469, 'title': 'Data manipulation in excel power query', 'summary': 'Demonstrates data manipulation in excel power query, including extracting text between delimiters, splitting columns, converting text to uppercase, filtering pivot tables, and unpivoting columns, to achieve desired analysis and data compression.', 'duration': 730.094, 'highlights': ['The chapter demonstrates data manipulation in Excel Power Query The chapter illustrates various data manipulation techniques using Excel Power Query.', 'Extracting text between delimiters and renaming columns Demonstrates extracting text between delimiters and renaming columns, such as extracting the stadium city and splitting the score column into winner score and loser score.', 'Converting text to uppercase and performing analysis using pivot tables Illustrates converting text to uppercase and performing analysis using pivot tables, including filtering to find winning teams that scored more than three goals and counting the number of championships won by Real Madrid.', 'Unpivoting columns for data compression and analysis Demonstrates the use of unpivoting columns for data compression and analysis, using a census data example from Wikipedia.']}], 'duration': 879.689, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko11203874.jpg', 'highlights': ["Demonstrates data manipulation in Excel Power Query, showcasing adding new columns from venue data and various functions like 'add column' and 'text before delimiter'.", 'Illustrates extracting text between delimiters, splitting columns, converting text to uppercase, filtering pivot tables, and unpivoting columns for data compression and analysis.', "Shows the process of adding a new column for stadium name using the 'text before delimiter' function, with examples like Santiago Bernabeu, Wembley, and San Siro being extracted from the venue column.", "Demonstrates adding a new column for stadium city by extracting values between the commas in the venue column, showcasing examples of cities such as Madrid, London, and Milan being extracted into a new column called 'stadium city'.", 'Explains the steps involved in the process, including renaming the columns and applying the changes, providing a comprehensive overview of the data manipulation.']}, {'end': 13700.123, 'segs': [{'end': 12114.08, 'src': 'embed', 'start': 12084.909, 'weight': 3, 'content': [{'end': 12090.933, 'text': "Let's continue with our demo and let's explore a few more features and functionalities of Power Query Editor.", 'start': 12084.909, 'duration': 6.024}, {'end': 12095.936, 'text': 'Now the next table we are going to use is an AdventureWorks customer table.', 'start': 12092.213, 'duration': 3.723}, {'end': 12101.65, 'text': 'Now this dataset is provided by Microsoft for practitioners who want to learn Power BI,', 'start': 12096.926, 'duration': 4.724}, {'end': 12107.254, 'text': 'Excel or similar technologies and want to do some manipulation, some calculation, some data analysis stuff.', 'start': 12101.65, 'duration': 5.604}, {'end': 12114.08, 'text': "So let me go to a new sheet and let's import the AdventureWorks customer dataset.", 'start': 12108.195, 'duration': 5.885}], 'summary': 'Exploring features of power query editor with adventureworks customer dataset for power bi and excel.', 'duration': 29.171, 'max_score': 12084.909, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko12084909.jpg'}, {'end': 12867.952, 'src': 'embed', 'start': 12840.292, 'weight': 2, 'content': [{'end': 12848.657, 'text': "so we'll use a table called adventureworks calendar table, which has basically a date column, and let's see how, using power query,", 'start': 12840.292, 'duration': 8.365}, {'end': 12853.139, 'text': 'you can prepare that data as well and make some manipulation, some calculations.', 'start': 12848.657, 'duration': 4.482}, {'end': 12857.403, 'text': 'So let me go ahead and import AdventureWorks calendars table.', 'start': 12854.4, 'duration': 3.003}, {'end': 12859.885, 'text': 'Again, this data set is provided by Microsoft.', 'start': 12857.423, 'duration': 2.462}, {'end': 12867.952, 'text': "So I'll go to my new sheet and I'll go to the data tab, click on get data.", 'start': 12860.745, 'duration': 7.207}], 'summary': 'Using power query to prepare and manipulate adventureworks calendar data, provided by microsoft.', 'duration': 27.66, 'max_score': 12840.292, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko12840292.jpg'}, {'end': 13491.973, 'src': 'embed', 'start': 13438.806, 'weight': 0, 'content': [{'end': 13443.947, 'text': 'If I scroll down, we have project three files, project four, and similarly we have project five.', 'start': 13438.806, 'duration': 5.141}, {'end': 13448.508, 'text': 'Now this is one way in which you can merge your tables vertically.', 'start': 13444.747, 'duration': 3.761}, {'end': 13450.249, 'text': 'So we are done with it.', 'start': 13449.268, 'duration': 0.981}, {'end': 13453.77, 'text': "Let's just go to the home tab and click on close and load.", 'start': 13450.609, 'duration': 3.161}, {'end': 13460.112, 'text': 'All the transformations that were applied, you can see it here.', 'start': 13457.651, 'duration': 2.461}, {'end': 13468.22, 'text': 'We have our final table and we have successfully combined 5 Excel files.', 'start': 13460.212, 'duration': 8.008}, {'end': 13473.363, 'text': 'Again, you can also join your data horizontally.', 'start': 13470.281, 'duration': 3.082}, {'end': 13477.805, 'text': 'This would be like an SQL join where the data is present in multiple files or sheets.', 'start': 13473.883, 'duration': 3.922}, {'end': 13481.107, 'text': 'Based on a common key column, you can join the tables.', 'start': 13478.486, 'duration': 2.621}, {'end': 13486.65, 'text': 'So you can perform a left join, a right join, an inner join based on the problem that you are trying to solve.', 'start': 13481.828, 'duration': 4.822}, {'end': 13489.472, 'text': "So let's merge two tables based on a column.", 'start': 13487.331, 'duration': 2.141}, {'end': 13491.973, 'text': "I'll show the dataset first.", 'start': 13490.613, 'duration': 1.36}], 'summary': 'Merged 5 excel files vertically and discussed joining data horizontally using sql joins.', 'duration': 53.167, 'max_score': 13438.806, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko13438806.jpg'}], 'start': 12084.909, 'title': 'Power query features and applications', 'summary': 'Explores the functionalities of power query editor for data transformation, statistical analysis, and date manipulation using adventureworks customer and calendar datasets. it covers tasks such as importing, transforming data, statistical analysis, date format manipulation, and data combining, including successfully combining 5 excel files and demonstrating a left outer join with a common key column.', 'chapters': [{'end': 12416.529, 'start': 12084.909, 'title': 'Power query editor features', 'summary': 'Explores the functionalities of power query editor using adventureworks customer dataset, covering tasks such as importing, transforming data, and creating new columns based on email addresses.', 'duration': 331.62, 'highlights': ['The dataset used is AdventureWorks customer table provided by Microsoft for practitioners who want to learn Power BI, Excel, or similar technologies. The dataset used is AdventureWorks customer table provided by Microsoft for practitioners who want to learn Power BI, Excel, or similar technologies.', 'The process of importing the AdventureWorks customer dataset into Excel using Power Query is demonstrated, with a focus on the steps and the preview of the data set. The process of importing the AdventureWorks customer dataset into Excel using Power Query is demonstrated, with a focus on the steps and the preview of the data set.', 'Demonstrating the use of Power Query features to transform data, including changing columns to proper case and merging multiple columns into a new column. Demonstrating the use of Power Query features to transform data, including changing columns to proper case and merging multiple columns into a new column.', 'The creation of a new column for domain names by extracting characters from email addresses using Power Query. The creation of a new column for domain names by extracting characters from email addresses using Power Query.']}, {'end': 12840.292, 'start': 12416.529, 'title': 'Power query for statistical analysis', 'summary': 'Covers using power query for statistical analysis on a dataset, including finding the total number of products, calculating average product price, finding minimum and maximum product prices, rounding values, adding a new custom column, and loading the modified dataset onto excel.', 'duration': 423.763, 'highlights': ["Finding Total Number of Products By using the 'Count Values' option in the transform tab, the total number of products in the table is determined, which is 293.", "Calculating Average Product Price The 'Average' option in the transform tab is used to calculate the average product price, which is found to be 714.4373 dollars.", "Finding Minimum and Maximum Product Prices The 'Minimum' and 'Maximum' options in the transform tab are used to find the minimum product price, which is 2.29, and the maximum product price in the dataset.", "Rounding Values The 'Rounding' option in the transform tab is used to round the product cost and product price columns to two decimal places, ensuring consistent decimal points across the dataset.", "Adding a New Custom Column A new column called 'discount price' is added by multiplying the product price column by 0.9 to give a 10% discount, and the new column is rounded to two decimal places."]}, {'end': 13255.803, 'start': 12840.292, 'title': 'Power query date manipulation', 'summary': 'Explains how to use power query to manipulate and prepare data from the adventureworks calendar table, including transforming date formats, finding earliest and latest dates, and adding new columns for day, week, month, and year values.', 'duration': 415.511, 'highlights': ['The chapter explains how to use Power Query to transform and prepare data from the AdventureWorks calendar table, including changing the regional settings to enable access to date fields and formatting the date column. ', 'Demonstrates how to find the earliest and latest dates from the AdventureWorks calendar table using Power Query, resulting in the earliest date of 1st January 2015 and the latest date of 30th June 2017. Earliest date: 1st January 2015, Latest date: 30th June 2017', 'Illustrates the process of adding new columns such as day name, start of the week, month, and year values, and demonstrates the method to change the week start day from Sunday to Monday in Power Query. ']}, {'end': 13700.123, 'start': 13255.803, 'title': 'Combining and joining data in excel', 'summary': 'Explains how to combine data from multiple csv files vertically and then merge tables horizontally using power query in excel, successfully combining 5 excel files and demonstrating a left outer join with a common key column.', 'duration': 444.32, 'highlights': ['The chapter explains how to combine data from multiple CSV files vertically using Power Query in Excel, successfully combining 5 Excel files. Demonstrates the process of loading multiple CSV files from a folder, combining them vertically using Power Query, and successfully merging 5 Excel files.', 'The chapter demonstrates a left outer join with a common key column to merge tables horizontally using Power Query in Excel. Illustrates the process of merging tables horizontally, specifically using a left outer join with a common key column, using Power Query in Excel.']}], 'duration': 1615.214, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko12084909.jpg', 'highlights': ['Demonstrates the process of loading multiple CSV files from a folder, combining them vertically using Power Query, and successfully merging 5 Excel files.', 'Illustrates the process of merging tables horizontally, specifically using a left outer join with a common key column, using Power Query in Excel.', 'Demonstrates how to find the earliest and latest dates from the AdventureWorks calendar table using Power Query, resulting in the earliest date of 1st January 2015 and the latest date of 30th June 2017.', 'The process of importing the AdventureWorks customer dataset into Excel using Power Query is demonstrated, with a focus on the steps and the preview of the data set.', 'The dataset used is AdventureWorks customer table provided by Microsoft for practitioners who want to learn Power BI, Excel, or similar technologies.']}, {'end': 14976.958, 'segs': [{'end': 13795.431, 'src': 'embed', 'start': 13700.363, 'weight': 0, 'content': [{'end': 13709.326, 'text': "So I'll select this and now you can see there's a tick mark, which means it has selected the rows and the column successfully.", 'start': 13700.363, 'duration': 8.963}, {'end': 13714.187, 'text': 'You can see it says the selection matches 175 or 175 rows from the first table.', 'start': 13709.366, 'duration': 4.821}, {'end': 13716.327, 'text': "And let's click on OK.", 'start': 13715.307, 'duration': 1.02}, {'end': 13721.429, 'text': "Let's just expand this.", 'start': 13720.089, 'duration': 1.34}, {'end': 13724.035, 'text': 'and click on.', 'start': 13723.434, 'duration': 0.601}, {'end': 13733.212, 'text': 'ok, if i scroll to the right, you can see i have successfully merged both the tables.', 'start': 13724.035, 'duration': 9.177}, {'end': 13739.42, 'text': 'now, if you want, you can remove unnecessary rows or columns.', 'start': 13733.212, 'duration': 6.208}, {'end': 13748.684, 'text': 'suppose, if you see, i have the course column from the year 11 table and here also i have the course column.', 'start': 13739.42, 'duration': 9.264}, {'end': 13751.285, 'text': 'now this is redundant.', 'start': 13748.684, 'duration': 2.601}, {'end': 13753.526, 'text': "let's just remove one of the columns.", 'start': 13751.285, 'duration': 2.241}, {'end': 13758.989, 'text': "i'll just select this column, i'll go to remove columns and i'll select remove columns.", 'start': 13753.526, 'duration': 5.463}, {'end': 13763.367, 'text': 'okay, the rest looks fine.', 'start': 13760.825, 'duration': 2.542}, {'end': 13769.732, 'text': 'we have successfully merged both the tables by using a left outer join.', 'start': 13763.367, 'duration': 6.365}, {'end': 13776.617, 'text': "let's just click close and load.", 'start': 13769.732, 'duration': 6.885}, {'end': 13779.699, 'text': 'now. this will take some time to load our data onto excel.', 'start': 13776.617, 'duration': 3.082}, {'end': 13781.18, 'text': 'you can see it here.', 'start': 13779.699, 'duration': 1.481}, {'end': 13785.344, 'text': 'we have successfully loaded it onto excel.', 'start': 13781.18, 'duration': 4.164}, {'end': 13786.865, 'text': 'now we are done with our demo part.', 'start': 13785.344, 'duration': 1.521}, {'end': 13791.568, 'text': "Now let's just see what all we have done in our demo.", 'start': 13788.305, 'duration': 3.263}, {'end': 13795.431, 'text': "So I'll go to my first sheet.", 'start': 13792.088, 'duration': 3.343}], 'summary': 'Successfully merged 175 rows, removed redundant columns, and loaded data onto excel.', 'duration': 95.068, 'max_score': 13700.363, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko13700363.jpg'}, {'end': 13853.028, 'src': 'embed', 'start': 13825.373, 'weight': 5, 'content': [{'end': 13835.46, 'text': 'we made some analysis using pivot tables and then we imported another web file which was based on a population data,', 'start': 13825.373, 'duration': 10.087}, {'end': 13843.298, 'text': 'and we used the unpivot option or the unpivot feature in the Power Query to reduce the number of columns.', 'start': 13835.46, 'duration': 7.838}, {'end': 13849.305, 'text': 'then we made some calculations to our customer table, which was from AdventureWorks.', 'start': 13843.298, 'duration': 6.007}, {'end': 13853.028, 'text': 'then we used another table called the products table.', 'start': 13849.305, 'duration': 3.723}], 'summary': 'Analyzed data using pivot tables, imported web file on population data, used unpivot feature in power query, performed calculations on customer table from adventureworks, and utilized products table.', 'duration': 27.655, 'max_score': 13825.373, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko13825373.jpg'}, {'end': 13915.215, 'src': 'embed', 'start': 13883.194, 'weight': 4, 'content': [{'end': 13885.335, 'text': 'It does not actually change the spreadsheet data.', 'start': 13883.194, 'duration': 2.141}, {'end': 13889.517, 'text': 'It simply pivots or turns the data to view it in different perspectives.', 'start': 13885.875, 'duration': 3.642}, {'end': 13895.441, 'text': 'Pivot tables are especially useful with large amounts of data that would be time consuming to calculate manually.', 'start': 13890.018, 'duration': 5.423}, {'end': 13900.323, 'text': "Now, let's understand the different components of a pivot table.", 'start': 13896.701, 'duration': 3.622}, {'end': 13902.624, 'text': 'So there are four main components.', 'start': 13901.144, 'duration': 1.48}, {'end': 13904.165, 'text': 'First, we have rows.', 'start': 13903.265, 'duration': 0.9}, {'end': 13909.288, 'text': 'When a field is chosen for the row area, it populates as the first column in the pivot table.', 'start': 13904.505, 'duration': 4.783}, {'end': 13915.215, 'text': 'Similar to the columns, all row labels are unique values and duplicates are removed.', 'start': 13910.529, 'duration': 4.686}], 'summary': 'Pivot tables efficiently analyze large datasets, with rows and columns displaying unique values.', 'duration': 32.021, 'max_score': 13883.194, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko13883194.jpg'}, {'end': 14000.731, 'src': 'embed', 'start': 13971.573, 'weight': 6, 'content': [{'end': 13974.356, 'text': "First, I'll show you one of the features that Excel offers us.", 'start': 13971.573, 'duration': 2.783}, {'end': 13979.461, 'text': 'So suppose I click on any cell and hit Ctrl plus Q,', 'start': 13974.917, 'duration': 4.544}, {'end': 13986.247, 'text': "you can see our entire table is selected and at the right bottom there's an option of quick analysis.", 'start': 13979.461, 'duration': 6.786}, {'end': 13992.969, 'text': 'now you can see, by default, excel has prompted certain features such as formatting.', 'start': 13987.128, 'duration': 5.841}, {'end': 13997.29, 'text': "we have charts, totals and there's one more called tables.", 'start': 13992.969, 'duration': 4.321}, {'end': 14000.731, 'text': 'now excel, by default, has created some pivot tables for us.', 'start': 13997.29, 'duration': 3.441}], 'summary': 'Excel offers quick analysis for data, including formatting, charts, totals, and pivot tables.', 'duration': 29.158, 'max_score': 13971.573, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko13971573.jpg'}, {'end': 14098.376, 'src': 'embed', 'start': 14071.035, 'weight': 7, 'content': [{'end': 14077.44, 'text': 'so our first pivot table will have the total population for each of the states in descending order.', 'start': 14071.035, 'duration': 6.405}, {'end': 14082.604, 'text': 'so to create a pivot table you can click any cell in your data.', 'start': 14077.44, 'duration': 5.164}, {'end': 14089.41, 'text': 'go to the insert tab and here left you can see we have the option to create a pivot table.', 'start': 14082.604, 'duration': 6.806}, {'end': 14093.273, 'text': 'so let me select pivot table now.', 'start': 14089.41, 'duration': 3.863}, {'end': 14098.376, 'text': "my range is already selected the entire table and here i'll choose existing worksheet.", 'start': 14093.273, 'duration': 5.103}], 'summary': 'Creating a pivot table to display total population of states in descending order.', 'duration': 27.341, 'max_score': 14071.035, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14071035.jpg'}, {'end': 14262.283, 'src': 'embed', 'start': 14233.86, 'weight': 9, 'content': [{'end': 14238.483, 'text': 'Next, I want to see the sum of total literates based on states and cities.', 'start': 14233.86, 'duration': 4.623}, {'end': 14245.668, 'text': "So let me first drag state name onto rows and then we'll drag city onto rows.", 'start': 14238.563, 'duration': 7.105}, {'end': 14249.291, 'text': 'You can see here, we have our pivot table ready.', 'start': 14246.849, 'duration': 2.442}, {'end': 14258.022, 'text': 'To the left of the pivot table, you can see we have the state names and the cities per state.', 'start': 14251.22, 'duration': 6.802}, {'end': 14262.283, 'text': 'And on the right, you can see the total number of literates from each city.', 'start': 14258.722, 'duration': 3.561}], 'summary': 'Pivot table shows total literates by state and city.', 'duration': 28.423, 'max_score': 14233.86, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14233860.jpg'}, {'end': 14317.26, 'src': 'embed', 'start': 14289.551, 'weight': 10, 'content': [{'end': 14296.073, 'text': 'with that we also want to find the states that had the highest and lowest sex ratio in 2011.', 'start': 14289.551, 'duration': 6.522}, {'end': 14297.314, 'text': "so let's create a pivot table.", 'start': 14296.073, 'duration': 1.241}, {'end': 14297.974, 'text': 'for this.', 'start': 14297.314, 'duration': 0.66}, {'end': 14304.636, 'text': "I'll click on any cell, go to insert, choose pivot table, click on existing worksheet.", 'start': 14297.974, 'duration': 6.662}, {'end': 14314.439, 'text': "I'll select cell q5 and click on ok, now, since we want the average sex ratio and the child sex ratio.", 'start': 14304.636, 'duration': 9.803}, {'end': 14317.26, 'text': "so first I'll drag those columns.", 'start': 14314.439, 'duration': 2.821}], 'summary': 'Analyzing states with highest and lowest sex ratio in 2011 using pivot table.', 'duration': 27.709, 'max_score': 14289.551, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14289551.jpg'}, {'end': 14495.146, 'src': 'embed', 'start': 14463.551, 'weight': 11, 'content': [{'end': 14465.912, 'text': 'so here we have a question at hand.', 'start': 14463.551, 'duration': 2.361}, {'end': 14470.393, 'text': 'we want to find the top three cities with the highest number of female graduates.', 'start': 14465.912, 'duration': 4.481}, {'end': 14475.515, 'text': "so let's see from the entire pivot table how we can filter the top three cities.", 'start': 14470.393, 'duration': 5.122}, {'end': 14484.68, 'text': "so i'll go to insert click on the pivot table option, Go to existing worksheet, click on Q5 and hit OK.", 'start': 14475.515, 'duration': 9.165}, {'end': 14495.146, 'text': "Now, since we want to find the top three cities, I'll drag city column onto rows and then we want the female graduates.", 'start': 14486.041, 'duration': 9.105}], 'summary': 'Identifying top 3 cities with highest female graduates using pivot table and existing data.', 'duration': 31.595, 'max_score': 14463.551, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14463551.jpg'}, {'end': 14638.097, 'src': 'embed', 'start': 14583.626, 'weight': 12, 'content': [{'end': 14588.63, 'text': "so let's create a pivot table for this and see how you can use a slicer to filter the table.", 'start': 14583.626, 'duration': 5.004}, {'end': 14590.851, 'text': 'click on existing.', 'start': 14589.97, 'duration': 0.881}, {'end': 14595.672, 'text': "I'll click on a location, this time q6 click.", 'start': 14590.851, 'duration': 4.821}, {'end': 14599.633, 'text': 'OK now, since I want the total population.', 'start': 14595.672, 'duration': 3.961}, {'end': 14611.477, 'text': "so I'll drag total population on to values and then I'll select the city on to rows and then the state name.", 'start': 14599.633, 'duration': 11.844}, {'end': 14615.638, 'text': "also, I'll place it on top of city.", 'start': 14611.477, 'duration': 4.161}, {'end': 14626.435, 'text': 'so you have in the pivot table all the states and their cities and on the right you can see the total population for each of these cities.', 'start': 14615.638, 'duration': 10.797}, {'end': 14631.432, 'text': 'but our question is we want to find only for Rajasthan and Karnataka now?', 'start': 14626.435, 'duration': 4.997}, {'end': 14638.097, 'text': 'for that, what you can do is go to insert and create a slicer.', 'start': 14631.432, 'duration': 6.665}], 'summary': 'Create pivot table, use slicer to filter data by location and population, insert slicer for rajasthan and karnataka.', 'duration': 54.471, 'max_score': 14583.626, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14583626.jpg'}, {'end': 14741.307, 'src': 'embed', 'start': 14717.881, 'weight': 15, 'content': [{'end': 14725.823, 'text': "for example, we have a question here what's the percentage contribution of male and female literates from each state?", 'start': 14717.881, 'duration': 7.942}, {'end': 14730.924, 'text': 'now we want to see in terms of percentage and not as sum or average.', 'start': 14725.823, 'duration': 5.101}, {'end': 14733.145, 'text': "let's do that.", 'start': 14730.924, 'duration': 2.221}, {'end': 14741.307, 'text': "i'll create my pivot table, click on existing and I'll select an empty cell.", 'start': 14733.145, 'duration': 8.162}], 'summary': 'Analyzing percentage contributions of male and female literates from each state using pivot table.', 'duration': 23.426, 'max_score': 14717.881, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14717881.jpg'}, {'end': 14886.489, 'src': 'embed', 'start': 14856.618, 'weight': 16, 'content': [{'end': 14863.886, 'text': "Now moving ahead, let's say we want to find the bottom three cities from each state that had the lowest female graduates.", 'start': 14856.618, 'duration': 7.268}, {'end': 14866.008, 'text': 'We can do that as well.', 'start': 14864.867, 'duration': 1.141}, {'end': 14872.896, 'text': "I'll go to Insert, click on Pivot, go to Existing Worksheet, select an empty worksheet and click on OK.", 'start': 14866.549, 'duration': 6.347}, {'end': 14886.489, 'text': "Now, since I want to see based on states as well as cities, so let me drag the state name first onto rows and let's drag the city column onto rows.", 'start': 14874.682, 'duration': 11.807}], 'summary': 'Demonstrating how to find bottom three cities with lowest female graduates by using pivot table.', 'duration': 29.871, 'max_score': 14856.618, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14856618.jpg'}], 'start': 13700.363, 'title': 'Using pivot tables in excel', 'summary': 'Explains using pivot tables in excel to find total sum of literates in each city, average sex ratio and child sex ratio for each state, and filtering the top three cities with highest number of female graduates.', 'chapters': [{'end': 13763.367, 'start': 13700.363, 'title': 'Merge and remove columns in table', 'summary': 'Discusses merging two tables, successfully matching 175 rows, and removing redundant columns to improve data organization and efficiency.', 'duration': 63.004, 'highlights': ['Successfully merged both the tables, combining the data for improved analysis and insights.', 'Selected rows and columns match 175 from the first table, demonstrating the successful selection process.', 'Removed redundant column, enhancing data organization and efficiency.']}, {'end': 13970.793, 'start': 13763.367, 'title': 'Excel pivot table demo', 'summary': 'Demonstrates how to merge tables using a left outer join, load data onto excel, import various data sources, create pivot charts, manipulate data using power query, perform statistical calculations, and explains the components and usefulness of pivot tables in excel.', 'duration': 207.426, 'highlights': ['The chapter demonstrates how to merge tables using a left outer join, load data onto Excel, and import various data sources. The process includes successfully merging tables, loading data onto Excel, and importing data from text files and web sources such as UEFA Champions League table and population data, and demonstrating the use of pivot charts.', 'The chapter explains the components and usefulness of pivot tables in Excel, including rows, columns, values, and filters. It explains the four main components of pivot tables - rows, columns, values, and filters, and their respective functions in summarizing and reorganizing data in Excel, especially with large datasets.', 'The chapter demonstrates manipulating data using Power Query, performing statistical calculations, and creating pivot charts. It includes using Power Query to manipulate data, performing statistical calculations, creating pivot charts, and explaining the process of appending and merging multiple tables in Excel.']}, {'end': 14179.518, 'start': 13971.573, 'title': 'Excel pivot table demo', 'summary': "Demonstrates the use of excel's quick analysis feature to create pivot tables for analyzing population data, including creating a pivot table to find the total population for each state and sorting it in descending order, with maharashtra having the highest total population in 2011.", 'duration': 207.945, 'highlights': ["Excel's quick analysis feature prompts formatting, charts, totals, and tables by default, including predefined pivot tables for sum of district code, sex ratio, child sex ratio, male graduates, and female graduates by state name. Excel's quick analysis feature prompts predefined pivot tables for various data summaries, such as sum of district code, sex ratio, child sex ratio, male graduates, and female graduates by state name.", 'The chapter demonstrates the creation of a pivot table to find the total population for each state and sort it in descending order, with Maharashtra having the highest total population in 2011. The chapter guides through the creation of a pivot table to find the total population for each state and sort it in descending order, where Maharashtra had the highest total population in 2011.', 'The process of creating a pivot table involves selecting the entire table, choosing an existing worksheet to place the pivot table, and dragging the state name onto rows while dragging the total population under values to obtain the sum of total population for each state. The process of creating a pivot table involves selecting the entire table, choosing an existing worksheet to place the pivot table, and dragging the state name onto rows while dragging the total population under values to obtain the sum of total population for each state.']}, {'end': 14562.993, 'start': 14179.518, 'title': 'Using pivot tables in excel', 'summary': 'Explains how to use pivot tables in excel to find the total sum of literates in each city belonging to a certain state, the average sex ratio and child sex ratio for each state, and how to filter the top three cities with the highest number of female graduates.', 'duration': 383.475, 'highlights': ["Finding Total Sum of Literates The pivot table is used to find the total sum of literates in each city belonging to a certain state by dragging the 'total literates' column to values and then dragging 'state name' and 'city' onto rows, resulting in a pivot table showing the total number of literates from each city.", 'Average Sex Ratio and Child Sex Ratio Another pivot table is created to find the average sex ratio and child sex ratio for each state by summarizing the values as an average, and then sorting the column to find the states with the highest and lowest sex ratio in 2011.', 'Filtering Top Three Cities with Highest Female Graduates The process of using a pivot table to filter the top three cities with the highest number of female graduates is explained, involving sorting the column in descending order and then filtering to display only the top three cities.']}, {'end': 14976.958, 'start': 14562.993, 'title': 'Using slicers and percentage contribution in pivot tables', 'summary': 'Demonstrates how to use slicers in a pivot table to filter data for specific states, create percentage contribution of male and female literates per state, and find the bottom three cities with the lowest number of female graduates for each state.', 'duration': 413.965, 'highlights': ['highlight The chapter demonstrates how to use slicers in a pivot table to filter data for specific states, create percentage contribution of male and female literates per state, and find the bottom three cities with the lowest number of female graduates for each state.', 'highlight By using a slicer in the pivot table, the presenter filters the data to display only the total population for the states of Rajasthan and Karnataka.', 'highlight The presenter creates a pivot table to show the total population for each city in Rajasthan and Karnataka, then uses a slicer to filter the table to display data for only these two states.', 'highlight The presenter explains how to create a percentage contribution of male and female literates per state in a pivot table, demonstrating the process of converting the data to be represented in terms of percentage and identifying the states with the highest and lowest percentage contributions.', 'highlight The presenter uses the pivot table to find the bottom three cities from each state with the lowest number of female graduates, demonstrating how to filter and sort the data to identify the cities with the lowest number of female graduates.']}], 'duration': 1276.595, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko13700363.jpg', 'highlights': ['Successfully merged both tables for improved analysis and insights.', 'Selected rows and columns match 175 from the first table, demonstrating successful selection process.', 'Removed redundant column, enhancing data organization and efficiency.', 'Demonstrated merging tables using left outer join, loading data onto Excel, and importing various data sources.', 'Explained components and usefulness of pivot tables in Excel, including rows, columns, values, and filters.', 'Demonstrated manipulating data using Power Query, performing statistical calculations, and creating pivot charts.', "Excel's quick analysis feature prompts predefined pivot tables for various data summaries.", 'Guided through the creation of a pivot table to find the total population for each state and sort it in descending order.', 'Demonstrated the process of creating a pivot table to find the total population for each state and sort it in descending order.', 'Used pivot table to find the total sum of literates in each city belonging to a certain state.', 'Created pivot table to find the average sex ratio and child sex ratio for each state.', 'Explained the process of using a pivot table to filter the top three cities with the highest number of female graduates.', 'Demonstrated how to use slicers in a pivot table to filter data for specific states.', 'Filtered the data to display only the total population for the states of Rajasthan and Karnataka using a slicer in the pivot table.', 'Created a pivot table to show the total population for each city in Rajasthan and Karnataka, then used a slicer to filter the table to display data for only these two states.', 'Explained how to create a percentage contribution of male and female literates per state in a pivot table.', 'Used the pivot table to find the bottom three cities from each state with the lowest number of female graduates.']}, {'end': 16208.664, 'segs': [{'end': 15034.475, 'src': 'embed', 'start': 15005.115, 'weight': 0, 'content': [{'end': 15010.979, 'text': 'So in a pivot table, you can create or use custom formulas to create calculated fields or items.', 'start': 15005.115, 'duration': 5.864}, {'end': 15015.263, 'text': 'Calculated fields are formulas that can refer to other fields in the pivot table.', 'start': 15011.7, 'duration': 3.563}, {'end': 15019.486, 'text': 'Calculated fields appear with other value fields in the pivot table.', 'start': 15016.064, 'duration': 3.422}, {'end': 15025.65, 'text': "Like other value fields, a calculated field's name may proceed with sum of followed by the field name.", 'start': 15020.066, 'duration': 5.584}, {'end': 15034.475, 'text': 'So here we have a sales table that has columns like the items, which has different fruits and vegetables,', 'start': 15026.57, 'duration': 7.905}], 'summary': 'Pivot tables allow creation of calculated fields, referring to other fields, appearing with other value fields.', 'duration': 29.36, 'max_score': 15005.115, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko15005115.jpg'}, {'end': 15410.378, 'src': 'embed', 'start': 15384.749, 'weight': 1, 'content': [{'end': 15391.852, 'text': "now let's go to our first sheet and summarize what we have done in this demo for pivot tables in excel.", 'start': 15384.749, 'duration': 7.103}, {'end': 15392.752, 'text': 'so we had our data.', 'start': 15391.852, 'duration': 0.9}, {'end': 15397.849, 'text': 'This is 2011 census data from India.', 'start': 15394.386, 'duration': 3.463}, {'end': 15406.535, 'text': 'We had the different cities, the state names and we had the total population total literates, female literates, male literates.', 'start': 15398.169, 'duration': 8.366}, {'end': 15410.378, 'text': 'we had the sex ratio, total graduates and other information.', 'start': 15406.535, 'duration': 3.843}], 'summary': 'Summary of 2011 indian census data for pivot tables in excel.', 'duration': 25.629, 'max_score': 15384.749, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko15384749.jpg'}, {'end': 15499.259, 'src': 'embed', 'start': 15465.516, 'weight': 2, 'content': [{'end': 15469.699, 'text': 'We saw how to find the top three cities with the highest number of female graduates.', 'start': 15465.516, 'duration': 4.183}, {'end': 15475.143, 'text': 'We found out that Delhi, Greater Mumbai and Bangalore were the top cities with highest number of female graduates.', 'start': 15469.939, 'duration': 5.204}, {'end': 15480.206, 'text': 'Next, we saw how to use slicer in a pivot table.', 'start': 15476.003, 'duration': 4.203}, {'end': 15488.171, 'text': 'So we sliced our table based on Rajasthan and Karnataka state and saw the total population for all the cities in Rajasthan and Karnataka.', 'start': 15480.626, 'duration': 7.545}, {'end': 15499.259, 'text': 'In the next sheet, we explored another feature that was to find the percentage contribution of male and female literates from each state.', 'start': 15490.376, 'duration': 8.883}], 'summary': 'Identified top 3 cities for female graduates; demonstrated pivot table slicing and explored state-wise literacy rates.', 'duration': 33.743, 'max_score': 15465.516, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko15465516.jpg'}, {'end': 15846.065, 'src': 'embed', 'start': 15813.769, 'weight': 3, 'content': [{'end': 15818.592, 'text': 'so what happens is, first, two rows will be locked.', 'start': 15813.769, 'duration': 4.823}, {'end': 15823.496, 'text': 'okay, these two rows will be locked similarly.', 'start': 15818.592, 'duration': 4.904}, {'end': 15828.459, 'text': "for if you want to freeze the columns, okay, it's the same thing.", 'start': 15823.496, 'duration': 4.963}, {'end': 15835.639, 'text': 'i am going to select this particular column, then click on view and then click freeze paint.', 'start': 15828.459, 'duration': 7.18}, {'end': 15838.801, 'text': 'so what happens is the first two columns are locked.', 'start': 15835.639, 'duration': 3.162}, {'end': 15846.065, 'text': 'a and b will be locked and if i scroll right, you will always see the customer name and category column.', 'start': 15838.801, 'duration': 7.264}], 'summary': 'Locks two rows and columns to freeze them; ensures a fixed view while scrolling.', 'duration': 32.296, 'max_score': 15813.769, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko15813769.jpg'}, {'end': 15890.21, 'src': 'embed', 'start': 15866.077, 'weight': 4, 'content': [{'end': 15874.201, 'text': 'you want to protect the data For from anyone or from someone cop from copying the cell for your worksheet.', 'start': 15866.077, 'duration': 8.124}, {'end': 15875.422, 'text': 'So how do you do that?', 'start': 15874.201, 'duration': 1.221}, {'end': 15885.848, 'text': 'You need to select that particular data that you want to protect and then click ctrl shift F and then you will see the Protection tab.', 'start': 15875.962, 'duration': 9.886}, {'end': 15890.21, 'text': 'Okay, you just need to go to the protection tab and select lock.', 'start': 15886.208, 'duration': 4.002}], 'summary': 'Protect data in worksheet by selecting and locking specific content.', 'duration': 24.133, 'max_score': 15866.077, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko15866077.jpg'}], 'start': 14977.758, 'title': 'Excel pivot table demo', 'summary': 'Demonstrates creating and analyzing pivot tables in excel using 2011 census data from india, including finding top and bottom cities for various parameters, using slicers, and creating calculated fields and various charts.', 'chapters': [{'end': 15384.749, 'start': 14977.758, 'title': 'Creating calculated fields in excel pivot table', 'summary': 'Demonstrates the process of creating calculated fields in an excel pivot table, showcasing examples of creating a calculated field for sales and utilizing pivot charts to visualize the sales data for different items and categories.', 'duration': 406.991, 'highlights': ['The process of creating a calculated field in an Excel pivot table is explained, demonstrating the use of custom formulas to calculate fields or items, where the calculated fields can refer to other fields in the pivot table. This process allows the user to create calculated fields or items in a pivot table using custom formulas, enabling the calculated fields to refer to other fields in the pivot table.', 'A detailed example of creating a calculated field for sales in a pivot table is provided, illustrating the formula for calculating sales as the product of price per kg and quantity. The example showcases the creation of a calculated field for sales in a pivot table, with the formula being the product of price per kg and quantity, demonstrating the practical application of calculated fields in analyzing sales data.', 'The utilization of pivot charts to visualize sales data for different items and categories is demonstrated, showcasing the creation of bar charts, pie charts, line charts, and area charts to represent sales information effectively. The chapter illustrates the use of pivot charts to visualize sales data for different items and categories, including the creation of bar charts, pie charts, line charts, and area charts, providing a comprehensive visualization of the sales information.']}, {'end': 15754.241, 'start': 15384.749, 'title': 'Excel pivot table demo', 'summary': 'Demonstrates creating and analyzing pivot tables in excel using 2011 census data from india, including finding top and bottom cities for various parameters, using slicers, and creating calculated fields and various charts.', 'duration': 369.492, 'highlights': ['The chapter demonstrates creating and analyzing pivot tables in Excel using 2011 census data from India. The demo focuses on using census data from India to create pivot tables in Excel for analysis.', 'Finding the top three cities with the highest number of female graduates, including Delhi, Greater Mumbai, and Bangalore. The top cities with the highest number of female graduates are identified as Delhi, Greater Mumbai, and Bangalore.', 'Exploring the feature to find the percentage contribution of male and female literates from each state. The process of finding the percentage contribution of male and female literates from each state is explored.', 'Creating a calculated field in a pivot table and exploring the creation of different charts and graphs. The demo covers creating a calculated field in a pivot table and explores the creation of various charts and graphs, including area chart, column chart, horizontal bar chart, and pie chart.', 'Explaining the cell address and relative/absolute cell referencing in Excel. The explanation of cell address and relative/absolute cell referencing in Excel is provided, including the identification of a cell on a worksheet and the differences between relative and absolute referencing.']}, {'end': 16208.664, 'start': 15754.241, 'title': 'Excel tips and functions', 'summary': 'Provides tips for freezing rows and columns, protecting data, distinguishing between formulas and functions, and understanding the order of operations in excel, along with an example of writing a formula.', 'duration': 454.423, 'highlights': ["Freezing rows and columns in Excel can be done by selecting the desired row or column, going to the 'View' tab, and clicking 'Freeze Panes', which locks the selected rows or columns for easier navigation.", "Protecting data in Excel involves selecting the data, pressing 'Ctrl+Shift+F' to access the Protection tab, setting a password, and then clicking 'Protect Sheet' to prevent unauthorized copying of the content.", 'The difference between a formula and a function in Excel lies in the fact that a formula is manually typed by the user, while a function is a predefined calculation within Excel, offering efficiency and quick usage.', "Understanding the order of operations in Excel, represented by the acronym 'PEMDAS' (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction), is crucial for correctly evaluating formulas and preventing errors in calculations.", 'Writing a formula to multiply the value in cell A1 by 10, adding the result by 5, and then dividing it by 2 involves following the order of operations, starting with parentheses, then multiplication, addition, and division.']}], 'duration': 1230.906, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko14977758.jpg', 'highlights': ['The process of creating a calculated field in an Excel pivot table is explained, demonstrating the use of custom formulas to calculate fields or items, where the calculated fields can refer to other fields in the pivot table.', 'The chapter demonstrates creating and analyzing pivot tables in Excel using 2011 census data from India. The demo focuses on using census data from India to create pivot tables in Excel for analysis.', 'Finding the top three cities with the highest number of female graduates, including Delhi, Greater Mumbai, and Bangalore. The top cities with the highest number of female graduates are identified as Delhi, Greater Mumbai, and Bangalore.', "Freezing rows and columns in Excel can be done by selecting the desired row or column, going to the 'View' tab, and clicking 'Freeze Panes', which locks the selected rows or columns for easier navigation.", "Protecting data in Excel involves selecting the data, pressing 'Ctrl+Shift+F' to access the Protection tab, setting a password, and then clicking 'Protect Sheet' to prevent unauthorized copying of the content."]}, {'end': 17330.925, 'segs': [{'end': 16270.464, 'src': 'embed', 'start': 16235.299, 'weight': 8, 'content': [{'end': 16237.74, 'text': 'OK, so first we will see what is count.', 'start': 16235.299, 'duration': 2.441}, {'end': 16242.864, 'text': 'Count is basically it counts the number of cells which have numeric values.', 'start': 16238.241, 'duration': 4.623}, {'end': 16245.241, 'text': 'the name suggests it is count.', 'start': 16243.779, 'duration': 1.462}, {'end': 16248.985, 'text': 'it will count the number of cells which has numeric volumes.', 'start': 16245.241, 'duration': 3.744}, {'end': 16259.356, 'text': 'okay, if you see for this column and where, at a11, we are putting it as count, equals to a10, to a2, to a10.', 'start': 16248.985, 'duration': 10.371}, {'end': 16270.464, 'text': "so you're taking this range and you're finding out what are the number of cells which have numeric value if you see this 1,, 2,, 3, 4, 5..", 'start': 16259.356, 'duration': 11.108}], 'summary': 'Count function identifies cells with numeric values, providing count of such cells in specified range.', 'duration': 35.165, 'max_score': 16235.299, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko16235299.jpg'}, {'end': 16391.233, 'src': 'embed', 'start': 16285.878, 'weight': 4, 'content': [{'end': 16289.48, 'text': 'It can be a number, it can be an alphabet, it can be anything.', 'start': 16285.878, 'duration': 3.602}, {'end': 16294.563, 'text': 'It will see what are the number of cells which has any form of content.', 'start': 16289.54, 'duration': 5.023}, {'end': 16300.847, 'text': 'For example, this is the same example we have taken here and we have put the function as counter a2 to a10.', 'start': 16295.243, 'duration': 5.604}, {'end': 16306.885, 'text': 'Now see, 1, 2, 3, 4, 5, 6, 7.', 'start': 16302.588, 'duration': 4.297}, {'end': 16312.252, 'text': 'So, it sees that there are 7 cells have some content in it.', 'start': 16306.89, 'duration': 5.362}, {'end': 16314.634, 'text': 'It is numbers and letters.', 'start': 16312.472, 'duration': 2.162}, {'end': 16317.035, 'text': 'It can be anything but it has some content.', 'start': 16314.694, 'duration': 2.341}, {'end': 16318.716, 'text': 'So, COUNTA is the function for this.', 'start': 16317.055, 'duration': 1.661}, {'end': 16321.917, 'text': 'Now, the next one is COUNT BLANK.', 'start': 16319.116, 'duration': 2.801}, {'end': 16327.02, 'text': 'As the name suggests, it will count the number of blank cells only.', 'start': 16322.357, 'duration': 4.663}, {'end': 16331.844, 'text': 'So again the same examples, count blank A2 to A10.', 'start': 16327.762, 'duration': 4.082}, {'end': 16334.085, 'text': 'It will see what are the number of blanks.', 'start': 16332.304, 'duration': 1.781}, {'end': 16336.907, 'text': 'If you see this is blank and this is blank.', 'start': 16334.426, 'duration': 2.481}, {'end': 16338.928, 'text': 'So two cells are blank.', 'start': 16336.927, 'duration': 2.001}, {'end': 16342.65, 'text': 'So it will count the number of blank cells only.', 'start': 16338.948, 'duration': 3.702}, {'end': 16348.353, 'text': 'Now, the next one is what is the shortcut to add a filter to a table?', 'start': 16344.211, 'duration': 4.142}, {'end': 16350.094, 'text': 'Why do we use filter?', 'start': 16348.993, 'duration': 1.101}, {'end': 16356.237, 'text': 'And it is one of the basics function in the Excel, where you need to do some sorting.', 'start': 16350.534, 'duration': 5.703}, {'end': 16358.604, 'text': 'and it can be done by using a filter.', 'start': 16356.661, 'duration': 1.943}, {'end': 16360.688, 'text': "What is the shortcut key? It's very simple.", 'start': 16358.965, 'duration': 1.723}, {'end': 16363.553, 'text': 'Ctrl Shift and L.', 'start': 16360.768, 'duration': 2.785}, {'end': 16366.658, 'text': 'This is the key that is used to put up a filter.', 'start': 16363.553, 'duration': 3.105}, {'end': 16370.745, 'text': 'You just press this Ctrl Shift L all together.', 'start': 16366.678, 'duration': 4.067}, {'end': 16375.061, 'text': 'and you will find the filter and you can sort it now.', 'start': 16371.258, 'duration': 3.803}, {'end': 16381.806, 'text': 'the next question that you will come across is how do you create a hyperlink in excel?', 'start': 16375.061, 'duration': 6.745}, {'end': 16382.766, 'text': "so it's quite simple.", 'start': 16381.806, 'duration': 0.96}, {'end': 16386.149, 'text': 'there is a simple shortcut ctrl k.', 'start': 16382.766, 'duration': 3.383}, {'end': 16389.972, 'text': 'you press these two together and you will get the option.', 'start': 16386.149, 'duration': 3.823}, {'end': 16391.233, 'text': 'you will select the.', 'start': 16389.972, 'duration': 1.261}], 'summary': 'Excel functions: counta counts content, countblank counts blanks, filter shortcut ctrl shift l, hyperlink shortcut ctrl k.', 'duration': 105.355, 'max_score': 16285.878, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko16285878.jpg'}, {'end': 16441.015, 'src': 'embed', 'start': 16416.429, 'weight': 6, 'content': [{'end': 16423.291, 'text': 'so how do we merge the text strings in multiple cells into one cell?', 'start': 16416.429, 'duration': 6.862}, {'end': 16429.073, 'text': 'there is one particular uh simple function that you can use, which is called called as concatenate.', 'start': 16423.291, 'duration': 5.782}, {'end': 16434.095, 'text': 'concatenate function is used to merge text strings present in multiple cells.', 'start': 16429.073, 'duration': 5.022}, {'end': 16437.573, 'text': "okay, and it's a very simple thing.", 'start': 16435.071, 'duration': 2.502}, {'end': 16441.015, 'text': 'you just need to put this equals to concatenate.', 'start': 16437.573, 'duration': 3.442}], 'summary': 'Use the concatenate function to merge text strings in multiple cells.', 'duration': 24.586, 'max_score': 16416.429, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko16416429.jpg'}, {'end': 16571.375, 'src': 'embed', 'start': 16544.09, 'weight': 3, 'content': [{'end': 16551.213, 'text': 'okay, the next question uh, it is very, very important one again, and very basic as well.', 'start': 16544.09, 'duration': 7.123}, {'end': 16555.125, 'text': 'So how can you split a column into two or more columns??', 'start': 16551.843, 'duration': 3.282}, {'end': 16562.63, 'text': 'You have some particular text or a sentence written, or data written in your cell or a column,', 'start': 16555.165, 'duration': 7.465}, {'end': 16567.012, 'text': 'and you want to put them across in two or more or many number of columns.', 'start': 16562.63, 'duration': 4.382}, {'end': 16570.654, 'text': 'so how do you perform this function?', 'start': 16567.433, 'duration': 3.221}, {'end': 16571.375, 'text': "it's very simple.", 'start': 16570.654, 'duration': 0.721}], 'summary': 'Explaining how to split a column into two or more columns for data organization.', 'duration': 27.285, 'max_score': 16544.09, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko16544090.jpg'}, {'end': 16731.139, 'src': 'embed', 'start': 16703.767, 'weight': 0, 'content': [{'end': 16706.589, 'text': 'so what is this particular thing?', 'start': 16703.767, 'duration': 2.822}, {'end': 16717.053, 'text': 'in excel, vlookup function is used for looking up a piece of information in a table and extracting some corresponding data or information.', 'start': 16706.589, 'duration': 10.464}, {'end': 16731.139, 'text': 'so you have some set of values in one particular column and you want to retrieve or extract the data corresponding to that particular column or a cell in the whole sheet.', 'start': 16717.053, 'duration': 14.086}], 'summary': 'Vlookup function in excel extracts corresponding data from a table.', 'duration': 27.372, 'max_score': 16703.767, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko16703767.jpg'}, {'end': 17092.764, 'src': 'embed', 'start': 17060.074, 'weight': 1, 'content': [{'end': 17064.236, 'text': 'and we are specifying the condition to be greater than 40 000..', 'start': 17060.074, 'duration': 4.162}, {'end': 17068.197, 'text': 'so, based on this two things it will check and perform.', 'start': 17064.236, 'duration': 3.961}, {'end': 17074.259, 'text': 'if these conditions are met, then it will give the desired result.', 'start': 17068.197, 'duration': 6.062}, {'end': 17080.982, 'text': 'okay, so this is the function that is used in excel if function.', 'start': 17074.259, 'duration': 6.723}, {'end': 17085.263, 'text': 'okay, the next thing is sum if function.', 'start': 17080.982, 'duration': 4.281}, {'end': 17092.764, 'text': 'okay, again, the sum function acts, the cell value specified by a given condition or a criteria.', 'start': 17085.263, 'duration': 7.501}], 'summary': 'The function checks for conditions greater than 40,000 and performs an action based on the result. it also discusses the usage of the if and sumif functions in excel.', 'duration': 32.69, 'max_score': 17060.074, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17060074.jpg'}, {'end': 17205.352, 'src': 'embed', 'start': 17174.952, 'weight': 2, 'content': [{'end': 17179.553, 'text': 'so we will use the COUNTIF function, and COUNTIF function is again simple COUNTIF.', 'start': 17174.952, 'duration': 4.601}, {'end': 17188.622, 'text': "you are specifying the column that is the uh source that you're looking for, that is g2, the column g, and it is 2.", 'start': 17179.553, 'duration': 9.069}, {'end': 17195.186, 'text': 'it starts from 2 row 2, g2 till g35777, which is the end of the column.', 'start': 17188.622, 'duration': 6.564}, {'end': 17197.287, 'text': "and what you're looking for is italy.", 'start': 17195.186, 'duration': 2.101}, {'end': 17205.352, 'text': 'you are specifying it in quotes, italy, and then the next column that it will look for is the e column, the number of cases.', 'start': 17197.287, 'duration': 8.065}], 'summary': "Using countif function to find 'italy' in column g2:g35777 and counting the number of cases in column e.", 'duration': 30.4, 'max_score': 17174.952, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17174952.jpg'}], 'start': 16208.664, 'title': 'Excel functions and tips', 'summary': 'Covers differences between count, counter, and count blank functions, excel shortcuts, vlookup function, and other related functions, with practical demonstrations and step-by-step instructions.', 'chapters': [{'end': 16338.928, 'start': 16208.664, 'title': 'Excel count functions explained', 'summary': 'Explains the differences between count, counter, and count blank functions in excel, highlighting their respective functionalities and examples of their usage.', 'duration': 130.264, 'highlights': ['The count function in Excel counts the number of cells with numeric values, as demonstrated by the example where 5 cells have numeric values in the specified range a2 to a10.', 'The counter function in Excel counts the number of cells with any form of content, including numbers and letters, as shown by the example where 7 cells have some form of content in the specified range a2 to a10.', 'The count blank function in Excel counts the number of blank cells, as illustrated by the example where 2 cells are identified as blank in the specified range a2 to a10.']}, {'end': 16674.25, 'start': 16338.948, 'title': 'Excel tips and functions', 'summary': "Covers excel shortcuts for adding filters and creating hyperlinks, using the concatenate function to merge text strings, and splitting a column into multiple columns using the 'text to columns' feature, providing practical demonstrations and step-by-step instructions.", 'duration': 335.302, 'highlights': ['The shortcut to add a filter to a table in Excel is Ctrl Shift L, which is one of the basic functions used for sorting. The shortcut key to add a filter in Excel is Ctrl Shift L, a basic function for sorting.', 'The shortcut to create a hyperlink in Excel is Ctrl K, allowing redirection to other documents or web pages. In Excel, the shortcut to create a hyperlink is Ctrl K, enabling redirection to other documents or web pages.', "The 'concatenate' function in Excel is used to merge text strings present in multiple cells into one cell, simplifying the process of combining cell values. The 'concatenate' function in Excel simplifies merging text strings from multiple cells into one cell, streamlining the process of combining cell values.", "The 'Text to Columns' feature in Excel allows for the splitting of a column into multiple columns based on specified delimiters, providing step-by-step instructions for the process. In Excel, the 'Text to Columns' feature enables the splitting of a column into multiple columns based on specified delimiters, with provided step-by-step instructions."]}, {'end': 17330.925, 'start': 16674.81, 'title': 'Vlookup in excel and other functions', 'summary': 'Explains the vlookup function in excel, its syntax and usage, along with examples. it also covers the differences between vlookup and lookup functions, report formats in excel, the if function, sum if function, and the countif function, providing practical examples and insights.', 'duration': 656.115, 'highlights': ['The VLOOKUP function in Excel is used for looking up a piece of information in a table and extracting corresponding data. It involves specifying the value to look for, the table to search in, the column index, and optional range lookup. The VLOOKUP function in Excel allows users to search for specific information in a table and retrieve corresponding data, based on the value, table, column index, and optional range lookup parameters.', 'The if function in Excel performs a logical test and returns a value based on the test result, using specified conditions. It is exemplified by a scenario where the record is considered valid if the age is over 20 and the salary is over 40,000. The if function in Excel conducts logical tests and yields results based on specified conditions, as demonstrated by the example where the record is deemed valid if the age is over 20 and the salary surpasses 40,000.', 'The COUNTIF function in Excel is utilized to count the occurrences of a specific criterion in a range of cells, demonstrated by finding the number of days with more than 200 deaths in Italy. The COUNTIF function in Excel is used to count instances of a particular criterion within a range of cells, as shown by the example of determining the days with over 200 deaths in Italy.']}], 'duration': 1122.261, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko16208664.jpg', 'highlights': ['The VLOOKUP function in Excel allows users to search for specific information in a table and retrieve corresponding data, based on the value, table, column index, and optional range lookup parameters.', 'The if function in Excel conducts logical tests and yields results based on specified conditions, as demonstrated by the example where the record is deemed valid if the age is over 20 and the salary surpasses 40,000.', 'The COUNTIF function in Excel is used to count instances of a particular criterion within a range of cells, as shown by the example of determining the days with over 200 deaths in Italy.', "The 'Text to Columns' feature in Excel enables the splitting of a column into multiple columns based on specified delimiters, with provided step-by-step instructions.", 'The shortcut to create a hyperlink in Excel is Ctrl K, enabling redirection to other documents or web pages.', 'The shortcut to add a filter to a table in Excel is Ctrl Shift L, a basic function for sorting.', "The 'concatenate' function in Excel simplifies merging text strings from multiple cells into one cell, streamlining the process of combining cell values.", 'The counter function in Excel counts the number of cells with any form of content, including numbers and letters, as shown by the example where 7 cells have some form of content in the specified range a2 to a10.', 'The count function in Excel counts the number of cells with numeric values, as demonstrated by the example where 5 cells have numeric values in the specified range a2 to a10.', 'The count blank function in Excel counts the number of blank cells, as illustrated by the example where 2 cells are identified as blank in the specified range a2 to a10.']}, {'end': 18523.243, 'segs': [{'end': 17392.482, 'src': 'embed', 'start': 17366.349, 'weight': 0, 'content': [{'end': 17373.198, 'text': 'i mean the data that is there that can be know, multiple number of rows and columns, which has different.', 'start': 17366.349, 'duration': 6.849}, {'end': 17374.459, 'text': 'uh, data.', 'start': 17373.198, 'duration': 1.261}, {'end': 17375.799, 'text': 'that has to be.', 'start': 17374.459, 'duration': 1.34}, {'end': 17385.081, 'text': 'that is there in the reports and you need to analyze those trends and you need to create a report and basically present it to the management or in a presentation.', 'start': 17375.799, 'duration': 9.282}, {'end': 17387.262, 'text': 'so pivot table is a summary of table.', 'start': 17385.081, 'duration': 2.181}, {'end': 17392.482, 'text': 'you know whatever data is there, you can put it in a summarized format, are useful.', 'start': 17387.262, 'duration': 5.22}], 'summary': 'Pivot tables summarize data for analysis and reporting to management.', 'duration': 26.133, 'max_score': 17366.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17366349.jpg'}, {'end': 17635.426, 'src': 'embed', 'start': 17609.714, 'weight': 1, 'content': [{'end': 17614.217, 'text': 'So you need to select what data you need to sort and put a drop down.', 'start': 17609.714, 'duration': 4.503}, {'end': 17624.105, 'text': 'So you need to select this and click data validation and you select the list option, which shows you that there is a different option.', 'start': 17614.597, 'duration': 9.508}, {'end': 17627.161, 'text': "I'll show it across in the example.", 'start': 17625.04, 'duration': 2.121}, {'end': 17632.625, 'text': 'For example, what we have here is some particular data and we have different variables.', 'start': 17627.181, 'duration': 5.444}, {'end': 17635.426, 'text': 'We need to sort or put a drop down list.', 'start': 17632.725, 'duration': 2.701}], 'summary': 'Demonstrates how to use data validation to create a drop down list for sorting data.', 'duration': 25.712, 'max_score': 17609.714, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17609714.jpg'}, {'end': 17726.935, 'src': 'embed', 'start': 17696.568, 'weight': 2, 'content': [{'end': 17700.79, 'text': 'we use the advanced filter option present in the data tab.', 'start': 17696.568, 'duration': 4.222}, {'end': 17710.755, 'text': "so this is the scenario wherein you have multiple set of datas and you need to sort it, and it's actually huge data.", 'start': 17700.79, 'duration': 9.965}, {'end': 17722.133, 'text': 'you need to sort a particular column or a field with this criteria and you can select it and specify the criteria and specify a range,', 'start': 17711.588, 'duration': 10.545}, {'end': 17726.935, 'text': 'what you want to select, or based on what criteria.', 'start': 17722.133, 'duration': 4.802}], 'summary': 'Using advanced filter to sort huge data with specified criteria and range.', 'duration': 30.367, 'max_score': 17696.568, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17696568.jpg'}, {'end': 17887.2, 'src': 'embed', 'start': 17859.081, 'weight': 3, 'content': [{'end': 17866.985, 'text': 'the next question is you know you need to highlight some particular cells using a particular criteria?', 'start': 17859.081, 'duration': 7.904}, {'end': 17874.989, 'text': 'in this case we are trying to analyze or highlight those cells where the total sales is more than five thousand dollars.', 'start': 17866.985, 'duration': 8.004}, {'end': 17881.118, 'text': 'So what we do is we use conditional formatting to highlight the cells based on the criteria.', 'start': 17875.455, 'duration': 5.663}, {'end': 17887.2, 'text': "It's a very useful tool in analyzing data and visually it helps.", 'start': 17881.818, 'duration': 5.382}], 'summary': 'Use conditional formatting to highlight cells with sales over $5000, aiding data analysis.', 'duration': 28.119, 'max_score': 17859.081, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17859081.jpg'}, {'end': 17993.695, 'src': 'embed', 'start': 17965.445, 'weight': 4, 'content': [{'end': 17972.367, 'text': 'basically, index function is a very powerful tool wherein you know you.', 'start': 17965.445, 'duration': 6.922}, {'end': 17980.93, 'text': 'for example, you have a table of planets in our solar system and you want to get the name of the fourth planet, mars.', 'start': 17972.367, 'duration': 8.563}, {'end': 17988.671, 'text': 'so you can do it with a formula using the index function and at the same time you can use the match function.', 'start': 17980.93, 'duration': 7.741}, {'end': 17993.695, 'text': 'okay, with the match function together, you can play wonders with it.', 'start': 17988.671, 'duration': 5.024}], 'summary': 'The index function is a powerful tool for accessing data from a table, such as retrieving the name of the fourth planet, mars, using a formula and the match function.', 'duration': 28.25, 'max_score': 17965.445, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17965445.jpg'}, {'end': 18279.362, 'src': 'embed', 'start': 18225.402, 'weight': 5, 'content': [{'end': 18234.249, 'text': 'How we do that is again you have to put another column and put up a COUNTF criteria, COUNTF function,', 'start': 18225.402, 'duration': 8.847}, {'end': 18244.176, 'text': 'wherein you see that you are specifying the range and the value, which is being twice, or enter duplicate.', 'start': 18234.249, 'duration': 9.927}, {'end': 18247.018, 'text': 'And you have to put up a condition.', 'start': 18244.856, 'duration': 2.162}, {'end': 18248.859, 'text': "I'll show it to you in the next.", 'start': 18247.178, 'duration': 1.681}, {'end': 18253.625, 'text': 'So here we have the data and I need to find the duplicate values here.', 'start': 18249.642, 'duration': 3.983}, {'end': 18257.727, 'text': "So I just select this and as I said, it's quite simple.", 'start': 18254.085, 'duration': 3.642}, {'end': 18267.794, 'text': 'Go to conditional formatting form and then go to conditional formatting and select highlight cell rules and simply go down and select the duplicate values.', 'start': 18257.747, 'duration': 10.047}, {'end': 18270.536, 'text': 'Okay, it will give you which color you want.', 'start': 18268.555, 'duration': 1.981}, {'end': 18274.038, 'text': 'You can simply select red or anything.', 'start': 18271.016, 'duration': 3.022}, {'end': 18279.362, 'text': 'As you see, the moment you select, you will see the duplicate values highlighted.', 'start': 18274.098, 'duration': 5.264}], 'summary': 'Using countif function to find duplicate values in data.', 'duration': 53.96, 'max_score': 18225.402, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko18225402.jpg'}, {'end': 18443.563, 'src': 'embed', 'start': 18416.333, 'weight': 7, 'content': [{'end': 18420.354, 'text': 'there is also a problem to remove duplicate entries.', 'start': 18416.333, 'duration': 4.021}, {'end': 18424.77, 'text': 'so How can you remove duplicate values in a range of cells?', 'start': 18420.354, 'duration': 4.416}, {'end': 18432.816, 'text': 'To simply do that, you can delete the duplicate values in a column by simply selecting the highlighted cells and press the delete button.', 'start': 18425.131, 'duration': 7.685}, {'end': 18440.821, 'text': 'So you go in each column and you see the duplicate values, select it and delete it.', 'start': 18433.316, 'duration': 7.505}, {'end': 18443.563, 'text': 'And this is one tedious way of doing it.', 'start': 18441.502, 'duration': 2.061}], 'summary': 'Duplicate values in a range of cells can be removed by selecting and deleting them individually, which is a tedious process.', 'duration': 27.23, 'max_score': 18416.333, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko18416333.jpg'}], 'start': 17330.925, 'title': 'Excel data manipulation techniques', 'summary': 'Covers creating pivot tables and advanced filters, using conditional formatting and index-match functions, and utilizing index and match functions for finding and removing duplicate values in excel data.', 'chapters': [{'end': 17859.081, 'start': 17330.925, 'title': 'Using pivot tables and advanced filters in excel', 'summary': 'Explains how to create a pivot table in excel to summarize and present data, and also demonstrates the application of advanced filters for sorting and filtering data based on specific criteria.', 'duration': 528.156, 'highlights': ['Pivot tables are used to summarize and present data in a concise and informative manner for analysis and reporting. Pivot tables allow for the summarization and presentation of data, making it simple and informative for analysis and reporting purposes.', 'Creating a drop-down list in Excel using data validation allows for sorting and selecting specific data based on different variables. The process of creating a drop-down list using data validation in Excel enables the sorting and selection of specific data based on different variables.', 'The application of advanced filters in Excel simplifies the sorting and filtering of large datasets based on specific criteria. Advanced filters in Excel simplify the sorting and filtering of large datasets based on specific criteria, making the process more efficient.']}, {'end': 18178.143, 'start': 17859.081, 'title': 'Conditional formatting and index-match function', 'summary': 'Discusses the usage of conditional formatting to highlight cells with total sales exceeding $5000 and the application of the index and match functions to retrieve specific data from a table in excel.', 'duration': 319.062, 'highlights': ['Conditional formatting is used to highlight cells with total sales exceeding $5000, providing visual aid for quick data analysis. By using conditional formatting to highlight cells with total sales greater than $5000, it allows for quick and visual data analysis.', 'The index and match functions are utilized to retrieve specific data from a table in Excel, enabling efficient data lookup and matching. The index and match functions are employed to efficiently retrieve specific data from a table in Excel, facilitating data lookup and matching.']}, {'end': 18523.243, 'start': 18178.403, 'title': 'Index and match functions and removing duplicate values', 'summary': 'Covers using index and match functions to find duplicate values in a column, including using conditional formatting and countif function, as well as methods to remove duplicate values in a range of cells, such as using the delete button and the remove duplicates tool in excel.', 'duration': 344.84, 'highlights': ['Using conditional formatting to highlight duplicate values in a column The speaker explains how to use conditional formatting to easily identify duplicate values in a column, allowing users to select the color for highlighting the duplicates.', 'Utilizing the countif function to identify and label duplicate entries The speaker demonstrates using the countif function to create a new column that identifies and labels duplicate entries in the data, providing a step-by-step guide on setting up the formula and dragging it to cover all entries.', "Methods for removing duplicate values in a range of cells The transcript outlines two methods for removing duplicate values in a range of cells, including manually selecting and deleting highlighted cells and using the 'remove duplicates' tool in the data tab of Excel, showcasing a more efficient approach."]}], 'duration': 1192.318, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko17330925.jpg', 'highlights': ['Pivot tables summarize and present data for analysis and reporting.', 'Creating a drop-down list using data validation enables sorting and selection of specific data.', 'Advanced filters in Excel simplify sorting and filtering of large datasets based on specific criteria.', 'Conditional formatting highlights cells with total sales exceeding $5000 for quick data analysis.', 'Index and match functions efficiently retrieve specific data from a table in Excel.', 'Using conditional formatting to highlight duplicate values in a column for easy identification.', 'Utilizing the countif function to identify and label duplicate entries in the data.', "Methods for removing duplicate values in a range of cells, including using the 'remove duplicates' tool in Excel."]}, {'end': 19503.917, 'segs': [{'end': 18999.743, 'src': 'embed', 'start': 18963.701, 'weight': 1, 'content': [{'end': 18966.383, 'text': 'a formula wherein you will see the result.', 'start': 18963.701, 'duration': 2.682}, {'end': 18973.448, 'text': 'okay, based on the past student data you have to put a pass fail results in the column based on the following criteria.', 'start': 18966.383, 'duration': 7.065}, {'end': 18975.129, 'text': 'what is the criteria?', 'start': 18973.448, 'duration': 1.681}, {'end': 18977.73, 'text': 'and, for example, here in this example,', 'start': 18975.129, 'duration': 2.601}, {'end': 18989.136, 'text': "what they're saying is you have to say this the student is passed if his marks is More than 60 and the attendance is more than 75%.", 'start': 18977.73, 'duration': 11.406}, {'end': 18999.743, 'text': 'OK, so how we perform this function as it says you have to use the if function and check with the and condition to fill the results column.', 'start': 18989.136, 'duration': 10.607}], 'summary': 'Use an if function to assign pass/fail results based on marks and attendance criteria.', 'duration': 36.042, 'max_score': 18963.701, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko18963701.jpg'}, {'end': 19175.914, 'src': 'embed', 'start': 19151.77, 'weight': 0, 'content': [{'end': 19159.069, 'text': 'use the year frac or date did if function to return the number of whole days between start date and the end date.', 'start': 19151.77, 'duration': 7.299}, {'end': 19169.072, 'text': "so you specify a start date, which will be today's date and the i mean the birthday, and then the end day will be today's day, for example.", 'start': 19159.069, 'duration': 10.003}, {'end': 19175.914, 'text': 'okay, and you will get to know the age using the ear frac function and dated f function.', 'start': 19169.072, 'duration': 6.842}], 'summary': 'Use the yearfrac or datedif function to calculate the age in days between start and end date.', 'duration': 24.144, 'max_score': 19151.77, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko19151770.jpg'}, {'end': 19299.68, 'src': 'embed', 'start': 19272.714, 'weight': 2, 'content': [{'end': 19277.016, 'text': 'And similarly, you can use the dated if function shown in the example.', 'start': 19272.714, 'duration': 4.302}, {'end': 19277.817, 'text': 'All right.', 'start': 19277.477, 'duration': 0.34}, {'end': 19280.198, 'text': 'Now moving on to the next question.', 'start': 19278.257, 'duration': 1.941}, {'end': 19283.56, 'text': 'You have nested if statements.', 'start': 19280.959, 'duration': 2.601}, {'end': 19284.601, 'text': 'This is very important.', 'start': 19283.58, 'duration': 1.021}, {'end': 19287.922, 'text': 'And you will definitely be coming across this question.', 'start': 19284.821, 'duration': 3.101}, {'end': 19294.718, 'text': 'How are nested if statements used in Excel? What are nested if statements? The if function can be nested.', 'start': 19288.103, 'duration': 6.615}, {'end': 19298.199, 'text': 'I mean, it can be when we have multiple conditions to meet.', 'start': 19294.778, 'duration': 3.421}, {'end': 19299.68, 'text': 'It can be nested.', 'start': 19298.78, 'duration': 0.9}], 'summary': 'Excel nested if statements allow multiple conditions to be met.', 'duration': 26.966, 'max_score': 19272.714, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko19272714.jpg'}], 'start': 18523.563, 'title': 'Excel intermediate to advanced techniques', 'summary': 'Covers intermediate to advanced excel techniques, including using data validation to control user input, creating formulas to categorize data, and utilizing nested if statements to specify multiple conditions with examples and scenarios.', 'chapters': [{'end': 18911.884, 'start': 18523.563, 'title': 'Excel intermediate level techniques', 'summary': 'Covers intermediate level excel techniques including finding the day of the week using the weekday function, understanding wildcards in excel, and setting up data validation to control user input, with examples.', 'duration': 388.321, 'highlights': ['The weekday function in Excel is used to find the day of the week for a particular date, denoted by numbers from 1 to 7, representing Sunday to Saturday, respectively.', 'Excel supports three types of wildcards: asterisk (*), representing any number of characters; question mark (?), representing one single character; and tilde (~), used to identify a wildcard character, with the function of these to be seen in later sessions.', "Data validation in Excel is a feature used to control user input into a cell, allowing restrictions such as entering only numbers or text, and can be set up from the 'Data' tab under 'Data Tools', with the ability to customize restrictions and error messages based on the user's requirements."]}, {'end': 19272.454, 'start': 18911.884, 'title': 'Data validation and formulas in excel', 'summary': "Explains how to use data validation to restrict cell values and how to create formulas in excel to add 'pass' or 'fail' based on specific criteria, as well as to calculate age using the yearfrac and datedif functions.", 'duration': 360.57, 'highlights': ['Explanation of using if and and functions to create a pass or fail formula based on specific criteria. The chapter provides a detailed example of using if and and functions in Excel to specify conditions for a pass or fail result based on marks and attendance, with a specific example of a student failing due to low marks and attendance.', 'Demonstration of using yearfrac and datedif functions to calculate age based on start and end dates. The chapter demonstrates the usage of yearfrac and datedif functions in Excel to calculate age based on the start and end dates, providing a practical example with the current date and date of birth.', 'Explanation of data validation to restrict cell values. The chapter explains the concept of data validation in Excel to restrict the values that can be entered into a cell, providing an overview of how data validation works.']}, {'end': 19503.917, 'start': 19272.714, 'title': 'Excel nested if statements', 'summary': "Discusses the usage of nested if statements in excel, which allows for specifying multiple conditions and replacing false values with another if function, exemplified through a scenario where marks above 80 are labeled 'excellent' and those below 60 are 'bad or average', using two nested if conditions.", 'duration': 231.203, 'highlights': ["The if function in Excel can be nested when there are multiple conditions to be met, allowing for the replacement of false values with another if function, as exemplified by the scenario where marks above 80 are labeled as 'excellent' and those below 60 are 'bad or average'.", "The example provided demonstrates the nesting of two if conditions to determine the result of marks based on specified conditions, such as labeling marks above 80 as 'excellent' and those below 60 as 'bad or average'.", "The explanation includes a demonstration of how the nested if statements work in Excel, checking for conditions such as marks above 80 being labeled 'excellent' and those below 60 being labeled 'bad or average'."]}], 'duration': 980.354, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko18523563.jpg', 'highlights': ['Demonstration of using yearfrac and datedif functions to calculate age based on start and end dates.', 'Explanation of using if and and functions to create a pass or fail formula based on specific criteria.', 'The if function in Excel can be nested when there are multiple conditions to be met.']}, {'end': 22986.243, 'segs': [{'end': 20060.304, 'src': 'embed', 'start': 20031.345, 'weight': 1, 'content': [{'end': 20036.158, 'text': 'yes, Yes, you can create a pivot table from multiple worksheets.', 'start': 20031.345, 'duration': 4.813}, {'end': 20038.619, 'text': 'Sometimes you will have different data in different sheets.', 'start': 20036.198, 'duration': 2.421}, {'end': 20045.52, 'text': 'Can you create a pivot table from all the worksheets in a single pivot table? Yes, you can.', 'start': 20039.099, 'duration': 6.421}, {'end': 20046.921, 'text': 'But there is a condition.', 'start': 20046.04, 'duration': 0.881}, {'end': 20049.401, 'text': 'There should be a common row in both the tables.', 'start': 20046.961, 'duration': 2.44}, {'end': 20051.862, 'text': 'You cannot have different types of data.', 'start': 20049.861, 'duration': 2.001}, {'end': 20053.242, 'text': 'There should be a common row.', 'start': 20052.082, 'duration': 1.16}, {'end': 20060.304, 'text': 'So then only it will act as a primary key for the first table and the foreign key for the second table.', 'start': 20054.523, 'duration': 5.781}], 'summary': 'You can create a pivot table from multiple worksheets if there is a common row in both tables.', 'duration': 28.959, 'max_score': 20031.345, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko20031345.jpg'}, {'end': 20128.214, 'src': 'embed', 'start': 20099.903, 'weight': 0, 'content': [{'end': 20102.504, 'text': 'so here we have with the covid data example.', 'start': 20099.903, 'duration': 2.601}, {'end': 20111.326, 'text': 'again you will be asked to create a pivot table to find the top three countries from each continent, based on the total cases, using the COVID data.', 'start': 20102.504, 'duration': 8.822}, {'end': 20113.507, 'text': 'Again, we have done this before.', 'start': 20111.587, 'duration': 1.92}, {'end': 20120.25, 'text': 'We have created a pivot table for this and we need to further refine it showing the top three countries with the number of cases.', 'start': 20113.607, 'duration': 6.643}, {'end': 20123.812, 'text': 'And I will show that same thing in the Excel.', 'start': 20121.091, 'duration': 2.721}, {'end': 20128.214, 'text': "And we already have created a table and we'll refine it further,", 'start': 20124.492, 'duration': 3.722}], 'summary': 'Create pivot table to find top 3 countries from each continent based on total covid cases.', 'duration': 28.311, 'max_score': 20099.903, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko20099903.jpg'}, {'end': 20523.868, 'src': 'embed', 'start': 20497.158, 'weight': 2, 'content': [{'end': 20502.262, 'text': 'basically, slicers are used to further filter data in the pivot table.', 'start': 20497.158, 'duration': 5.104}, {'end': 20510.605, 'text': "suppose you already have some data, and it's for ease that you can do it by just adding a slicer.", 'start': 20502.262, 'duration': 8.343}, {'end': 20522.208, 'text': 'you can select particular data in or a field and you can see the output for that particular field that you have chosen in the slicer.', 'start': 20510.605, 'duration': 11.603}, {'end': 20523.868, 'text': "okay, so it's very simple.", 'start': 20522.208, 'duration': 1.66}], 'summary': 'Slicers filter pivot table data, enabling easy selection and visualization of specific fields.', 'duration': 26.71, 'max_score': 20497.158, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko20497158.jpg'}, {'end': 21065.086, 'src': 'embed', 'start': 21022.688, 'weight': 3, 'content': [{'end': 21025.19, 'text': 'and what are macros in excel?', 'start': 21022.688, 'duration': 2.502}, {'end': 21028.592, 'text': 'create a macro to automate a task.', 'start': 21025.19, 'duration': 3.402}, {'end': 21033.296, 'text': 'basically, you have some daily tasks that you perform in excel.', 'start': 21028.592, 'duration': 4.704}, {'end': 21039.902, 'text': 'okay, and you can do this with quite ease using the macros.', 'start': 21033.296, 'duration': 6.606}, {'end': 21048.407, 'text': 'okay, it basically is a program that resides within the excel file and it is used to automate tasks in excel,', 'start': 21039.902, 'duration': 8.505}, {'end': 21065.086, 'text': 'some daily tasks that you run like running when you come across in any data And you have to scrub that data and you do some daily routines and by removing some columns or adding some formulas and you know,', 'start': 21048.407, 'duration': 16.679}], 'summary': 'Macros in excel automate daily tasks with ease.', 'duration': 42.398, 'max_score': 21022.688, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko21022688.jpg'}, {'end': 21946.891, 'src': 'embed', 'start': 21915, 'weight': 4, 'content': [{'end': 21926.111, 'text': 'okay, so, to find a last row, sometimes you have long list of data and it has like n number of rows and n number of columns.', 'start': 21915, 'duration': 11.111}, {'end': 21935.12, 'text': 'so instead of scrolling down all together to find the last row, you can use this VBA code.', 'start': 21926.111, 'duration': 9.009}, {'end': 21936.021, 'text': 'you can write this code.', 'start': 21935.12, 'duration': 0.901}, {'end': 21940.805, 'text': "it's a very simple one and it will give you the last row.", 'start': 21936.021, 'duration': 4.784}, {'end': 21944.228, 'text': 'you will find the last row with a single click.', 'start': 21940.805, 'duration': 3.423}, {'end': 21946.891, 'text': "okay, let's see it in the vba.", 'start': 21944.228, 'duration': 2.663}], 'summary': 'Vba code helps find last row in long data list with a single click.', 'duration': 31.891, 'max_score': 21915, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko21915000.jpg'}, {'end': 22931.589, 'src': 'embed', 'start': 22895.672, 'weight': 5, 'content': [{'end': 22905.256, 'text': 'okay, and it will ask you this is the module 2, because i defined it as module 2 and so the message box opens up and it asks you to enter a value.', 'start': 22895.672, 'duration': 9.584}, {'end': 22911.059, 'text': "now let's see 2 is a prime number.", 'start': 22905.256, 'duration': 5.803}, {'end': 22916.782, 'text': 'okay, i run it again by pressing the fake key.', 'start': 22911.059, 'duration': 5.723}, {'end': 22921.605, 'text': 'okay, 12 is not a prime number.', 'start': 22916.782, 'duration': 4.823}, {'end': 22923.986, 'text': 'so this is how we have been.', 'start': 22921.605, 'duration': 2.381}, {'end': 22931.589, 'text': 'we have coded a VBA code function to check whether the number is prime or not.', 'start': 22924.887, 'duration': 6.702}], 'summary': 'Vba code function checks if 2 and 12 are prime numbers.', 'duration': 35.917, 'max_score': 22895.672, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko22895672.jpg'}], 'start': 19503.917, 'title': 'Excel and vba data analysis', 'summary': 'Covers using descriptive statistics and pivot tables in excel for data analysis, advanced pivot table techniques, pivot table tools and techniques, excel macros, advanced analysis, vba code for data manipulation, and vba chart and function tutorials.', 'chapters': [{'end': 19958.362, 'start': 19503.917, 'title': 'Excel data analysis tools', 'summary': 'Explains how to use descriptive statistics and pivot tables in excel for data analysis, including adding the analysis tool pack, selecting input range, and generating summary statistics with a data analysis tool, as well as creating a pivot table to display the number of cases in each country by their respective continents.', 'duration': 454.445, 'highlights': ['Descriptive statistics in Excel for data analysis, including adding the analysis tool pack, selecting input range, and generating summary statistics The chapter explains how to use descriptive statistics in Excel for data analysis, including adding the analysis tool pack, selecting input range, and generating summary statistics with a data analysis tool.', 'Creating a pivot table in Excel to display the number of cases in each country by their respective continents The chapter demonstrates creating a pivot table in Excel to display the number of cases in each country by their respective continents, including selecting input range and fields to display.', 'Process of adding the analysis tool pack in Excel for data analysis The process of adding the analysis tool pack in Excel for data analysis is explained, including accessing the add-ins, selecting the analysis tool pack, and enabling the data analysis option.']}, {'end': 20407.635, 'start': 19958.642, 'title': 'Advanced pivot table techniques', 'summary': 'Covers advanced pivot table techniques including creating dynamic ranges, using multiple data sources, refining data search, and adding calculated fields, with examples and quantitative data, such as top 3 countries based on total cases and defining bonus calculation.', 'duration': 448.993, 'highlights': ['Creating a pivot table to find the top three countries from each continent based on the total cases using the COVID data The example demonstrates creating a pivot table to find top three countries from each continent based on total cases using COVID data, showcasing the practical application of pivot table with quantitative data.', 'Creating a calculated field from pivot table analyze option to define bonus calculation based on sales and unit sold Explains the process of creating a calculated field to define bonus calculation based on specific conditions like sales and unit sold, showcasing an advanced feature of pivot table analysis.', 'Selecting a dynamic range in data source of a pivot table by creating a name table Demonstrates the process of providing a dynamic range in the data source of a pivot table by creating a name table, showcasing an advanced technique for data manipulation in pivot tables.', 'Creating a pivot table from multiple worksheets and establishing a relationship between the tables Explains the process of creating a pivot table from multiple worksheets and establishing a relationship between the tables, highlighting the capability of pivot tables to handle data from different sources.', 'Adding a calculated field to the pivot table to show the bonus of the sales based on a particular formula Illustrates the process of adding a calculated field to the pivot table to display the bonus of sales based on a specific formula, demonstrating the flexibility and customization options available in pivot table analysis.']}, {'end': 21022.688, 'start': 20407.635, 'title': 'Pivot table tools and techniques', 'summary': 'Covers adding columns to existing data, using slicers to filter data in pivot tables, displaying the percentage contribution of each country and continent to the total cases in a pivot table, and creating pivot charts to visually represent data in different chart forms.', 'duration': 615.053, 'highlights': ['Adding columns to existing data and doing calculations The chapter explains adding a bonus column based on sales percentage and demonstrates the process of adding a new field to an existing data table to perform calculations.', 'Using slicers to filter pivot table data The tutorial illustrates the use of slicers to filter data in a pivot table based on specific fields such as month and country, enabling simplified data representation and analysis.', 'Displaying percentage contribution in pivot tables The chapter details the process of creating a pivot table to show the percentage contribution of each country and continent to the total cases, facilitating a clear visual representation of data.', 'Creating pivot charts to visually represent data The tutorial demonstrates the creation of pivot charts in various forms such as column, pie, and bar charts, providing options to visually represent data for effective presentations and analysis.']}, {'end': 21893.686, 'start': 21022.688, 'title': 'Excel macros and advanced analysis', 'summary': "Explains the use of macros in excel to automate daily tasks, providing details on how to record and run macros, followed by an in-depth explanation of what-if analysis tools including goal seek, data table, and scenario manager, and concludes with a comparison of functions and subroutines in vba as well as the difference between 'this workbook' and 'active workbook'.", 'duration': 870.998, 'highlights': ['Macros in Excel can automate daily tasks by recording and running steps, allowing multiple actions to be performed with a single click. Recording and running macros in Excel automates repetitive tasks, saving time and effort by allowing multiple actions to be performed with a single click.', 'What-if analysis in Excel involves tools like goal seek, data table, and scenario manager, allowing for different scenarios and calculations. What-if analysis in Excel includes tools like goal seek, data table, and scenario manager, enabling the analysis of various scenarios and calculations through different mathematical calculations and formulas.', 'Functions in VBA return a value and are called by a variable, while subroutines do not return a value and can be recalled from multiple locations within the program. Functions in VBA return a value and are called by a variable, whereas subroutines do not return a value and can be recalled from multiple locations within the program.', "The difference between 'this workbook' and 'active workbook' in VBA lies in their reference to the workbook where the code is running and the currently active workbook, respectively. The difference between 'this workbook' and 'active workbook' in VBA is that the former refers to the workbook where the code is running, while the latter refers to the currently active workbook.", "Arguments can be passed to a VBA function as a value or as a reference, with 'by reference' and 'by value' being the respective keywords for each method. Arguments can be passed to a VBA function as a value or as a reference, with 'by reference' and 'by value' being the respective keywords for each method."]}, {'end': 22359.374, 'start': 21893.686, 'title': 'Vba code for last row, column, file existence, and debugging', 'summary': 'Discusses vba code to find the last row and column in a long list of data, check file existence in a specified location, and use debug tools such as step-by-step execution and creating breakpoints.', 'duration': 465.688, 'highlights': ['The VBA code to find the last row and column allows users to quickly identify the end of a long list of data, improving efficiency and saving time.', 'The VBA code to check file existence provides a simple utility to verify whether a file exists in a specified location, enhancing file management and error handling.', 'The chapter demonstrates the use of debug tools in VBA, such as step-by-step execution and creating breakpoints, to analyze and troubleshoot complex code, improving code quality and reliability.']}, {'end': 22986.243, 'start': 22360.862, 'title': 'Vba chart and function tutorial', 'summary': 'Covers creating a bar chart using vba with given data, followed by defining a vba function to calculate the area of a rectangle and a vba code to check if a number is prime.', 'duration': 625.381, 'highlights': ['Creating a bar chart using VBA The tutorial demonstrates how to use VBA to create a bar chart with given data, providing a practical example and emphasizing its simplicity and usefulness.', 'Defining a VBA function to calculate the area of a rectangle The tutorial explains the process of defining a VBA function to calculate the area of a rectangle, including the steps to define the function, variables, and formula, followed by a demonstration in Excel.', 'Writing a VBA code to check if a number is prime The chapter provides a detailed explanation of writing a VBA code to check if a number is prime, including the use of loops, the mod operator, and a practical demonstration with an input box.']}], 'duration': 3482.326, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/h_UBLvhszko/pics/h_UBLvhszko19503917.jpg', 'highlights': ['Creating a pivot table to find the top three countries from each continent based on the total cases using the COVID data', 'Creating a pivot table from multiple worksheets and establishing a relationship between the tables', 'Using slicers to filter pivot table data', 'Macros in Excel can automate daily tasks by recording and running steps, allowing multiple actions to be performed with a single click', 'The VBA code to find the last row and column allows users to quickly identify the end of a long list of data, improving efficiency and saving time', 'Writing a VBA code to check if a number is prime']}], 'highlights': ['The chapter introduces the fundamental concepts of Microsoft Excel, including the definition of cells and addresses, navigating through sheets, and selecting sheet types.', 'Demonstrates using VLOOKUP to find the total sales for a specific region and representative by combining the lookup values, selecting the appropriate table array, and using a specific column index number.', 'The tutorial explains how to implement slicers in Excel to simplify filtering options during a presentation.', 'VBA simplifies daily tasks in Excel, improving work efficiency and saving time.', 'Demonstrates the process of loading multiple CSV files from a folder, combining them vertically using Power Query, and successfully merging 5 Excel files.', 'Excel Power Query connects to diverse data sources, enhancing data accessibility.', 'Demonstrates the process of creating a calculated field in an Excel pivot table, demonstrating the use of custom formulas to calculate fields or items, where the calculated fields can refer to other fields in the pivot table.', 'The VLOOKUP function in Excel allows users to search for specific information in a table and retrieve corresponding data, based on the value, table, column index, and optional range lookup parameters.', 'Pivot tables summarize and present data for analysis and reporting.', 'Demonstration of using yearfrac and datedif functions to calculate age based on start and end dates.', 'Creating a pivot table to find the top three countries from each continent based on the total cases using the COVID data', 'Macros in Excel can automate daily tasks by recording and running steps, allowing multiple actions to be performed with a single click']}