title
Data Analytics With Excel Full Course | Data Analytics Full Course | Data Analytics | Simplilearn

description
🔥Post Graduate Program In Data Analytics: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=DataAnalytics-xhFDYKqWlqw&utm_medium=Descriptionff&utm_source=youtube 🔥IIT Kanpur Professional Certificate Course In Data Science (India Only): https://www.simplilearn.com/iitk-professional-certificate-course-data-science?utm_campaign=DataAnalytics-xhFDYKqWlqw&utm_medium=Descriptionff&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=DataAnalytics-xhFDYKqWlqw&utm_medium=Descriptionff&utm_source=youtube 🔥Data Scientist Masters Program (Discount Code - YTBE15): https://www.simplilearn.com/big-data-and-analytics/senior-data-scientist-masters-program-training?utm_campaign=DataAnalytics-xhFDYKqWlqw&utm_medium=Descriptionff&utm_source=youtube 🟡 Caltech AI & Machine Learning Bootcamp (For US Learners Only) - https://www.simplilearn.com/ai-machine-learning-bootcamp?utm_campaign=DataAnalytics-xhFDYKqWlqw&utm_medium=Descriptionff&utm_source=youtube In this Data Analytics with Excel full course, you will learn what data analytics is, why data analytics is necessary, the types of data analytics, and the various data analytics applications. Dataset Link - https://drive.google.com/drive/folders/1U8CF0Xx3NivXdfrQvXJ_462tEb26oPuX 00:00:00 Functions and Formulas 03:39:13 Conditional Formatting 04:19:27 Data Validation 06:55:57 Pivot chart & Pivot Table ⏩ Check out the Data Analytics Playlist: link: https://www.youtube.com/playlist?list=PLEiEAq2VkUUKgEFXH1tBbHwq38oWYDScU ✅Subscribe to our Channel to learn more about the top Technologies: https://bit.ly/2VT4WtH #DataAnalyticsWithExcel #DataAnalyticsUsingExcel #DataAnalyticsFullCourse #DataAnalyticsForBeginners #LearnDataAnalytics #DataAnalysis #DataAnalytics #Simplilearn 🔥Free Data Analytics Course: https://www.simplilearn.com/learn-data-analytics-for-beginners-skillup?utm_campaign=DataAnalyticswithExcelLive&utm_medium=Description&utm_source=youtube ➡️ 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: https://www.simplilearn.com/pgp-data-analytics-certification-training-course?utm_campaign=DataAnalytics-xhFDYKqWlqw&utm_medium=Description&utm_source=youtube 🔥Caltech Data Analytics Bootcamp(US Only): https://www.simplilearn.com/data-analytics-bootcamp?utm_campaign=DataAnalytics-xhFDYKqWlqw&utm_medium=Description&utm_source=youtube 🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688

detail
{'title': 'Data Analytics With Excel Full Course | Data Analytics Full Course | Data Analytics | Simplilearn', 'heatmap': [{'end': 16951.435, 'start': 16656.098, 'weight': 1}], 'summary': 'This full course on data analytics with excel includes live sessions covering basics, top functions, pivot tables, power query, statistical analysis, automation using macros and vba, data manipulation techniques, data management, sorting, filtering, functions, pivot table analysis, power query editor, dashboard creation, revenue analysis, data analysis techniques, regression analysis, and vba for automation, providing practical examples and insights for data analysis and reporting.', 'chapters': [{'end': 387.053, 'segs': [{'end': 53.76, 'src': 'embed', 'start': 14.687, 'weight': 0, 'content': [{'end': 15.387, 'text': 'Hello everyone.', 'start': 14.687, 'duration': 0.7}, {'end': 19.189, 'text': 'Welcome to this live session on Data Analysis with Excel by Simply Learn.', 'start': 15.908, 'duration': 3.281}, {'end': 25.252, 'text': "In today's session, we will learn about Microsoft Excel and how it can be used to perform data analysis.", 'start': 20.029, 'duration': 5.223}, {'end': 30.094, 'text': 'Our instructors with good industry experience will help you learn everything from scratch.', 'start': 26.172, 'duration': 3.922}, {'end': 35.316, 'text': 'But before we begin, make sure to subscribe to our channel and hit the bell icon to never miss an update.', 'start': 30.974, 'duration': 4.342}, {'end': 43.06, 'text': 'So we will start by understanding the basics of Microsoft Excel, followed by learning the top Excel functions and formulas.', 'start': 37.377, 'duration': 5.683}, {'end': 46.535, 'text': 'We will then see how to validate and format data in Excel.', 'start': 43.973, 'duration': 2.562}, {'end': 51.458, 'text': 'Moving further, we will learn two important features of Excel that help in analysis of data.', 'start': 47.135, 'duration': 4.323}, {'end': 53.76, 'text': "So we'll look at pivot tables and power query.", 'start': 51.878, 'duration': 1.882}], 'summary': 'Live session on data analysis with excel covering basics, functions, formulas, validation, formatting, pivot tables, and power query.', 'duration': 39.073, 'max_score': 14.687, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14687.jpg'}, {'end': 203.589, 'src': 'embed', 'start': 157.67, 'weight': 2, 'content': [{'end': 163.815, 'text': 'now, before we move to excel, one of the question is why should we use excel?', 'start': 157.67, 'duration': 6.145}, {'end': 166.622, 'text': "So let's look at some of the points here.", 'start': 164.861, 'duration': 1.761}, {'end': 175.024, 'text': 'So Excel proves to be a great platform to perform various mathematical calculation on large data sets,', 'start': 166.642, 'duration': 8.382}, {'end': 179.446, 'text': 'which is one of the biggest requirements of various organizations these days.', 'start': 175.024, 'duration': 4.422}, {'end': 188.554, 'text': 'various features in excel, like searching, sorting, filtering, makes it easier for you to play with the data,', 'start': 180.126, 'duration': 8.428}, {'end': 196.842, 'text': 'and excel also allows you to beautify your data and present it in the form of charts, tables and data bars.', 'start': 188.554, 'duration': 8.288}, {'end': 203.589, 'text': 'now, when it comes to reporting, reporting, accounting and analysis can be performed with the help of excel.', 'start': 196.842, 'duration': 6.747}], 'summary': 'Excel is useful for mathematical calculations on large datasets, with features for searching, sorting, filtering and data visualization, supporting reporting, accounting, and analysis.', 'duration': 45.919, 'max_score': 157.67, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw157670.jpg'}], 'start': 14.687, 'title': 'Excel in data analysis', 'summary': 'Covers a live session on data analysis with excel, including understanding excel basics, top functions, pivot tables, power query, statistical analysis, and automating tasks using macros and vba, highlighting excel benefits such as mathematical calculations, data organization, security features, and versatile functions for data analysis and reporting.', 'chapters': [{'end': 157.67, 'start': 14.687, 'title': 'Data analysis with excel', 'summary': 'Covers a live session on data analysis with excel by simply learn, including topics such as understanding the basics of microsoft excel, top excel functions and formulas, data validation and formatting, pivot tables, power query, statistical analysis, and automating tasks using macros and vba.', 'duration': 142.983, 'highlights': ['The chapter covers a live session on Data Analysis with Excel by Simply Learn. The live session focuses on teaching data analysis with Excel by Simply Learn.', 'Topics include understanding the basics of Microsoft Excel, top Excel functions and formulas, data validation and formatting, pivot tables, power query, statistical analysis, and automating tasks using macros and VBA. The session covers various topics including understanding basics of Excel, top functions and formulas, data validation, pivot tables, power query, statistical analysis, and automating tasks using macros and VBA.', 'Learning the top Excel functions and formulas, followed by understanding the basics of Microsoft Excel. The session starts with learning the top Excel functions and formulas, followed by understanding the basics of Microsoft Excel.', 'Pivot tables and power query are important features of Excel that help in data analysis. The session includes learning about pivot tables and power query as important features of Excel for data analysis.', 'Using the data analysis tool pack for performing statistical analysis, such as descriptive statistics, regression analysis, and sampling. The session covers using the data analysis tool pack in Excel for performing statistical analysis, including descriptive statistics, regression analysis, and sampling.']}, {'end': 387.053, 'start': 157.67, 'title': 'Excel: features and functions', 'summary': 'Highlights the benefits of using excel, such as performing mathematical calculations on large datasets, data organization and visualization, security features, and various functions and formulas, making it a versatile tool for data analysis and reporting.', 'duration': 229.383, 'highlights': ['Excel is a great platform for performing mathematical calculations on large datasets, catering to the needs of various organizations. Excel is ideal for handling large datasets and performing mathematical calculations, meeting the needs of various organizations.', 'Excel offers features like searching, sorting, filtering, and data visualization, making it easier to work with data. Excel provides features like searching, sorting, filtering, and data visualization, simplifying data manipulation and analysis.', 'Excel is useful for reporting, accounting, analysis, task lists, calendars, and goal planning, and also provides good security for data through password protection. Excel supports reporting, accounting, analysis, task lists, calendars, goal planning, and offers data security through password protection.', 'Microsoft Excel is used for recording, analyzing, and visualizing data in the form of a spreadsheet, with various functions and formulas. Microsoft Excel is utilized for recording, analyzing, and visualizing data in a spreadsheet format, featuring numerous functions and formulas.', 'The chapter also covers a brief overview of Excel functions, formulas, and working with the application. The chapter provides a brief overview of Excel functions, formulas, and working with the application.']}], 'duration': 372.366, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14687.jpg', 'highlights': ['The live session focuses on teaching data analysis with Excel by Simply Learn.', 'The session covers various topics including understanding basics of Excel, top functions and formulas, data validation, pivot tables, power query, statistical analysis, and automating tasks using macros and VBA.', 'Excel is ideal for handling large datasets and performing mathematical calculations, meeting the needs of various organizations.', 'Excel provides features like searching, sorting, filtering, and data visualization, simplifying data manipulation and analysis.', 'Excel supports reporting, accounting, analysis, task lists, calendars, goal planning, and offers data security through password protection.']}, {'end': 3578.592, 'segs': [{'end': 1015.996, 'src': 'embed', 'start': 990.938, 'weight': 1, 'content': [{'end': 1001.746, 'text': 'so in this way you can work within your spreadsheet and you can be filling up the values where a delimiter by default is understood and we can split the data now.', 'start': 990.938, 'duration': 10.808}, {'end': 1010.513, 'text': 'however, sometimes you might have some data which has a different kind of delimiter and there is again a smarter way of splitting your data.', 'start': 1001.746, 'duration': 8.767}, {'end': 1015.996, 'text': 'so you can always scroll down here and that says splitting a column based on delimiters.', 'start': 1010.513, 'duration': 5.483}], 'summary': 'Split data in a spreadsheet using delimiters for efficient organization.', 'duration': 25.058, 'max_score': 990.938, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw990938.jpg'}, {'end': 1406.954, 'src': 'embed', 'start': 1381.255, 'weight': 0, 'content': [{'end': 1389.023, 'text': "Now that can be very handy when you're working on huge data and you would want to sort it in a particular order, say ascending or descending,", 'start': 1381.255, 'duration': 7.768}, {'end': 1397.827, 'text': 'or might be based on a particular field, or if that field was or if the cell was highlighted with a particular color, sorting the data.', 'start': 1389.023, 'duration': 8.804}, {'end': 1402.691, 'text': "so let's look at how excel can be used for sorting and filtering.", 'start': 1397.827, 'duration': 4.864}, {'end': 1406.954, 'text': "examples are pretty simple here, so let's check that.", 'start': 1402.691, 'duration': 4.263}], 'summary': 'Excel can sort and filter huge data based on specific criteria, facilitating efficient data management.', 'duration': 25.699, 'max_score': 1381.255, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw1381255.jpg'}, {'end': 1856.83, 'src': 'embed', 'start': 1829.781, 'weight': 2, 'content': [{'end': 1839.998, 'text': 'it will also be good to learn how to work with tables or basically converting your data into a tabular format and then doing some easy computations.', 'start': 1829.781, 'duration': 10.217}, {'end': 1842.399, 'text': 'so click on this tables option here.', 'start': 1839.998, 'duration': 2.401}, {'end': 1849.925, 'text': 'now, here we see there is some data which is in five columns and n number of rows.', 'start': 1842.399, 'duration': 7.526}, {'end': 1856.83, 'text': 'so i can basically select this data and then what i can do is i can insert,', 'start': 1849.925, 'duration': 6.905}], 'summary': 'Learn to work with tables, convert data into tabular format, and perform computations on data in 5 columns and n rows.', 'duration': 27.049, 'max_score': 1829.781, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw1829781.jpg'}, {'end': 2300.577, 'src': 'embed', 'start': 2274.967, 'weight': 3, 'content': [{'end': 2282.694, 'text': 'A text file or a CSV file and you would want to import that data into your Excel sheet that can be easily done.', 'start': 2274.967, 'duration': 7.727}, {'end': 2286.238, 'text': "So right now I've opened an Excel sheet.", 'start': 2283.295, 'duration': 2.943}, {'end': 2287.519, 'text': 'Now I can click on data.', 'start': 2286.318, 'duration': 1.201}, {'end': 2293.554, 'text': 'and here I have an option which says existing connections from other data sources.', 'start': 2288.092, 'duration': 5.462}, {'end': 2300.577, 'text': 'so or you can click on connections if you have already created some, so we can click on from other sources.', 'start': 2293.554, 'duration': 7.023}], 'summary': 'Import data from text or csv file into excel easily.', 'duration': 25.61, 'max_score': 2274.967, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw2274967.jpg'}, {'end': 2859.934, 'src': 'embed', 'start': 2834.164, 'weight': 4, 'content': [{'end': 2841.806, 'text': 'So, for example, I would want to sort the data in ascending order of area and descending order of agent name.', 'start': 2834.164, 'duration': 7.642}, {'end': 2844.467, 'text': "How do we do that? So let's look into this.", 'start': 2842.086, 'duration': 2.381}, {'end': 2845.927, 'text': 'So this is here.', 'start': 2844.887, 'duration': 1.04}, {'end': 2848.728, 'text': 'I already have the result here.', 'start': 2846.527, 'duration': 2.201}, {'end': 2855.15, 'text': "And how did I get this? So I'm looking for ascending order of area and descending order of agent name.", 'start': 2849.048, 'duration': 6.102}, {'end': 2858.553, 'text': 'so we can start with any particular column.', 'start': 2855.71, 'duration': 2.843}, {'end': 2859.934, 'text': 'that does not matter.', 'start': 2858.553, 'duration': 1.381}], 'summary': 'Sort data by ascending area and descending agent name.', 'duration': 25.77, 'max_score': 2834.164, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw2834164.jpg'}], 'start': 387.053, 'title': 'Excel data manipulation techniques', 'summary': 'Covers data manipulation techniques in excel, such as splitting data, transposing rows into columns, sorting, filtering, and adding/removing filters, providing efficient data organization and manipulation options.', 'chapters': [{'end': 990.938, 'start': 387.053, 'title': 'Excel: adding, filling, and splitting data', 'summary': 'Covers adding numbers using functions like sum, sumif, and auto sum, as well as filling down and right, and using flash fill to split data in excel.', 'duration': 603.885, 'highlights': ['The chapter demonstrates adding numbers using functions like SUM, SUMIF, and auto sum, as well as providing shortcuts like alt+equals for quick sum calculation. The chapter covers adding numbers using functions like SUM, SUMIF, and auto sum, as well as providing shortcuts like alt+equals for quick sum calculation.', 'It explains filling down and right by dragging and dropping the cursor, as well as using shortcuts like control+r and control+d for quick total calculation. It explains filling down and right by dragging and dropping the cursor, as well as using shortcuts like control+r and control+d for quick total calculation.', 'The chapter also demonstrates using flash fill to split data by deriving values from patterns or delimiters in a column. The chapter also demonstrates using flash fill to split data by deriving values from patterns or delimiters in a column.']}, {'end': 1779.397, 'start': 990.938, 'title': 'Excel data manipulation techniques', 'summary': 'Covers techniques such as splitting data based on delimiters, transposing rows into columns, sorting and filtering data, including sorting by date and color, and adding and removing filters, thus demonstrating the use of excel for data manipulation and organization.', 'duration': 788.459, 'highlights': ['The chapter demonstrates the technique of splitting data based on delimiters, allowing for better organization and manipulation of data. The speaker explains the process of splitting data based on delimiters in Excel, allowing for easier organization and manipulation of data.', 'The chapter illustrates the transposing of data, showcasing how to convert rows into columns and vice versa, using both direct actions and formulas. The transposing of data in Excel is demonstrated, showing how to convert rows into columns and vice versa using direct actions such as paste special and formulas.', 'The chapter provides insights into sorting and filtering data, including sorting by date and color, and adding and removing filters in Excel. Insights are given into sorting and filtering data in Excel, including sorting by date and color, and adding and removing filters.']}, {'end': 2245.15, 'start': 1779.637, 'title': 'Working with excel data', 'summary': 'Covers how to sort and filter data, convert data into a table format with easy computations, and create drop downs for efficient data entry.', 'duration': 465.513, 'highlights': ['Creating tables for data with the ability to add rows, columns, and perform calculations. The chapter explains how to convert data into a tabular format in Excel, allowing the addition of rows, columns, and conducting calculations within the table.', 'Using drop downs to efficiently select specific values for data entry. The process of creating drop downs in Excel is demonstrated, allowing users to select predefined values for efficient data entry.', 'Understanding how to sort and filter data without losing any information. The chapter emphasizes the ability to sort and filter data in Excel without losing any information, as it is only hidden based on the filter.']}, {'end': 2700.143, 'start': 2245.65, 'title': 'Excel data import and connection', 'summary': 'Demonstrates how to import data from local machine and web sources into excel using existing connections and create connections with an existing database, providing various options for importing data, including from web, local machine, and databases.', 'duration': 454.493, 'highlights': ['The chapter demonstrates how to import data from local machine and web sources into Excel using existing connections The tutorial provides step-by-step guidance on importing data from local machine and web sources into Excel using existing connections, showcasing the process of importing a CSV file and data from a web source.', 'Create connections with an existing database, providing various options for importing data The tutorial explains the process of creating connections with an existing database, offering various options for importing data such as from web, local machine, and databases, including the use of new query and power query editor.', 'Demonstrates the use of Excel features for importing and working with data The tutorial emphasizes on using Excel features to easily work with data, including importing text files or CSV files into Excel, verifying file properties, selecting delimiters, and importing data from web sources.']}, {'end': 3578.592, 'start': 2700.788, 'title': 'Excel data sorting and filtering', 'summary': 'Discusses sorting and filtering data in excel, including sorting data based on date, area, and agent name, custom sorting based on specific orders, applying filters to find specific data, and using advanced filtering with multiple conditions to obtain desired results.', 'duration': 877.804, 'highlights': ['Sorting data based on date, area, and agent name The chapter explains how to sort data in Excel based on date, area, and agent name, enabling users to arrange data in ascending or descending order as per their requirements.', 'Custom sorting based on specific orders The transcript provides details on custom sorting in Excel, allowing users to arrange data based on specific orders, such as sorting data according to a custom list of values in a particular column.', 'Applying filters to find specific data It explains how to apply filters in Excel to find specific data, such as filtering houses in the central area, enabling users to view only the relevant data based on specific criteria.', 'Using advanced filtering with multiple conditions The transcript describes the process of using advanced filtering with multiple conditions in Excel, allowing users to filter data based on complex criteria involving multiple columns and conditions.']}], 'duration': 3191.539, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw387053.jpg', 'highlights': ['The chapter provides insights into sorting and filtering data, including sorting by date and color, and adding and removing filters in Excel.', 'The chapter demonstrates the technique of splitting data based on delimiters, allowing for better organization and manipulation of data.', 'The chapter explains how to convert data into a tabular format in Excel, allowing the addition of rows, columns, and conducting calculations within the table.', 'The tutorial provides step-by-step guidance on importing data from local machine and web sources into Excel using existing connections.', 'The chapter explains how to sort data in Excel based on date, area, and agent name, enabling users to arrange data in ascending or descending order as per their requirements.']}, {'end': 5123.993, 'segs': [{'end': 3641.93, 'src': 'embed', 'start': 3579.09, 'weight': 4, 'content': [{'end': 3588.532, 'text': 'which basically means if I select this, this one tells me that this is M one row onwards till V two.', 'start': 3579.09, 'duration': 9.442}, {'end': 3592.833, 'text': 'So this is what we have and we would want to filter based on this.', 'start': 3589.172, 'duration': 3.661}, {'end': 3602.394, 'text': "So let's go ahead and then go for data filter advanced and here in advanced it says filter the list in place.", 'start': 3593.413, 'duration': 8.981}, {'end': 3604.115, 'text': "Now that's not what we would want to do.", 'start': 3602.474, 'duration': 1.641}, {'end': 3605.955, 'text': "So I'll say copy to another location.", 'start': 3604.235, 'duration': 1.72}, {'end': 3618.396, 'text': "this basically selects the list range, so which is telling me A1 to J126, so that's the columns and rows.", 'start': 3606.809, 'duration': 11.587}, {'end': 3619.857, 'text': 'selected criteria.', 'start': 3618.396, 'duration': 1.461}, {'end': 3632.063, 'text': 'range is based on M1, V2, which we have given here and copy to, I would say, for example, from M7 to p7.', 'start': 3619.857, 'duration': 12.206}, {'end': 3633.824, 'text': 'now this is the area where i would.', 'start': 3632.063, 'duration': 1.761}, {'end': 3635.926, 'text': 'this is the place where i want the result.', 'start': 3633.824, 'duration': 2.102}, {'end': 3641.93, 'text': "let's say okay, and now i get my data, which is based on the question which has been.", 'start': 3635.926, 'duration': 6.004}], 'summary': 'Filter data from a1 to j126 based on m1 to v2 criteria and copy to m7 to p7.', 'duration': 62.84, 'max_score': 3579.09, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw3579090.jpg'}, {'end': 3979.287, 'src': 'embed', 'start': 3954.787, 'weight': 1, 'content': [{'end': 3962.429, 'text': 'Various features in Excel like searching, sorting, filtering makes it easier for you to play with the data.', 'start': 3954.787, 'duration': 7.642}, {'end': 3971.478, 'text': 'and excel also allows you to beautify your data and present it in the form of charts, tables and data bars.', 'start': 3963.189, 'duration': 8.289}, {'end': 3979.287, 'text': 'now when it comes to reporting, reporting, accounting and analysis can be performed with the help of excel.', 'start': 3971.478, 'duration': 7.809}], 'summary': "Excel's features simplify data manipulation and visualization for reporting, accounting, and analysis.", 'duration': 24.5, 'max_score': 3954.787, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw3954787.jpg'}, {'end': 4032.618, 'src': 'embed', 'start': 4001.666, 'weight': 3, 'content': [{'end': 4014.177, 'text': 'so Excel or you might have heard a spreadsheet can be basically used for a lot of different tasks than just storing the information in so-called tabular format.', 'start': 4001.666, 'duration': 12.511}, {'end': 4022.935, 'text': 'Now Microsoft Excel is an application that is used for recording, analyzing, and visualizing data.', 'start': 4015.751, 'duration': 7.184}, {'end': 4025.016, 'text': 'It is in the form of a spreadsheet.', 'start': 4023.215, 'duration': 1.801}, {'end': 4032.618, 'text': "let's have a look at few of the functions and formulas used in excel, and before we do that,", 'start': 4026.656, 'duration': 5.962}], 'summary': 'Excel is used for recording, analyzing, and visualizing data in a spreadsheet format.', 'duration': 30.952, 'max_score': 4001.666, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw4001666.jpg'}, {'end': 4137.585, 'src': 'embed', 'start': 4111.413, 'weight': 0, 'content': [{'end': 4122.542, 'text': 'now you might be provided data which we can upload by loading a file from our machine or getting data from a web source or even connecting to a database.', 'start': 4111.413, 'duration': 11.129}, {'end': 4126.203, 'text': 'so there are various options which we will see in some time.', 'start': 4122.542, 'duration': 3.661}, {'end': 4130.207, 'text': 'so here we have an option which is called data.', 'start': 4126.203, 'duration': 4.004}, {'end': 4137.585, 'text': 'you can click on this one and this basically has options where you can use existing connections.', 'start': 4130.207, 'duration': 7.378}], 'summary': 'Options for uploading data include loading a file, getting data from a web source, or connecting to a database.', 'duration': 26.172, 'max_score': 4111.413, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw4111412.jpg'}, {'end': 4494.605, 'src': 'embed', 'start': 4437.079, 'weight': 7, 'content': [{'end': 4439.401, 'text': "So that's how you can simply add numbers.", 'start': 4437.079, 'duration': 2.322}, {'end': 4441.282, 'text': 'You can use auto sum.', 'start': 4439.821, 'duration': 1.461}, {'end': 4443.003, 'text': 'You can type in the formula.', 'start': 4441.482, 'duration': 1.521}, {'end': 4449.827, 'text': 'You can select the fields or you can just place your cursor where you would be looking for a sum.', 'start': 4443.483, 'duration': 6.344}, {'end': 4455.471, 'text': 'And then you can just do a alt equals and that basically populates the sum.', 'start': 4450.328, 'duration': 5.143}, {'end': 4471.377, 'text': "now let's look at some easy options of filling your cells or automatically populating the values in your cells within your excel sheet.", 'start': 4459.753, 'duration': 11.624}, {'end': 4484.062, 'text': 'now here we have an option which says 100 now we can click on this and that basically says it is making a sum of column C4 to D4..', 'start': 4471.377, 'duration': 12.685}, {'end': 4490.884, 'text': 'So if I click on this one, I can check that this is row number four, which shows up here.', 'start': 4484.602, 'duration': 6.282}, {'end': 4494.605, 'text': 'And I also know this is column C.', 'start': 4491.644, 'duration': 2.961}], 'summary': 'Learn how to add numbers and auto-sum in excel, with easy options for populating values in cells.', 'duration': 57.526, 'max_score': 4437.079, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw4437079.jpg'}, {'end': 4860.134, 'src': 'embed', 'start': 4821.963, 'weight': 9, 'content': [{'end': 4824.325, 'text': 'now i can basically select this one.', 'start': 4821.963, 'duration': 2.362}, {'end': 4835.029, 'text': 'I can say finish and say okay, and now, if you see, our data has been placed in in the columns appropriately,', 'start': 4825.063, 'duration': 9.966}, {'end': 4838.311, 'text': 'so this is how you can split your data based on a delimiter.', 'start': 4835.029, 'duration': 3.282}, {'end': 4841.633, 'text': 'And then organize your data in a better way.', 'start': 4838.851, 'duration': 2.782}, {'end': 4847.676, 'text': 'now there are some advanced options which we can learn later, but this basically tells about using a formula.', 'start': 4841.633, 'duration': 6.043}, {'end': 4849.367, 'text': 'So this is something.', 'start': 4848.086, 'duration': 1.281}, {'end': 4860.134, 'text': 'if say, if we have some name in one cell and if you would want to split it into first name, your helper column, your middle name, last name,', 'start': 4849.367, 'duration': 10.767}], 'summary': 'Learn how to split data based on a delimiter and organize it efficiently.', 'duration': 38.171, 'max_score': 4821.963, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw4821963.jpg'}, {'end': 4933.082, 'src': 'embed', 'start': 4908.47, 'weight': 10, 'content': [{'end': 4917.495, 'text': 'Now, you might have heard of situations where you would want to switch or turn your rows into columns and your columns into rows,', 'start': 4908.47, 'duration': 9.025}, {'end': 4920.016, 'text': "and that's where transposing comes into picture.", 'start': 4917.495, 'duration': 2.521}, {'end': 4927.98, 'text': 'It might be useful when you have your data in your X and Y axis, or, as I would say, in rows and columns,', 'start': 4920.496, 'duration': 7.484}, {'end': 4933.082, 'text': 'and you would want to switch your rows to become the columns and columns to become your rows.', 'start': 4927.98, 'duration': 5.102}], 'summary': 'Transposing allows switching rows to columns and columns to rows for better data representation.', 'duration': 24.612, 'max_score': 4908.47, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw4908470.jpg'}], 'start': 3579.09, 'title': 'Excel data management', 'summary': 'Covers data filtering and copying from specific criteria, data analysis with excel including functions, formulas, features, and benefits, and excel data management options including connecting to databases, using functions like sum and sumif, and transposing rows and columns.', 'chapters': [{'end': 3641.93, 'start': 3579.09, 'title': 'Data filtering and copying', 'summary': 'Demonstrates how to filter data based on specific criteria and copy the results to another location, with the example of selecting criteria range from m1 to v2 and copying the data from m7 to p7.', 'duration': 62.84, 'highlights': ['The process involves selecting the list range A1 to J126 and the criteria range M1 to V2 for filtering the data.', 'Copying the filtered data to another location, for instance, from M7 to P7, allows for obtaining the desired results.', 'Using the data filter advanced option, the chapter explains the process of filtering data based on specific criteria.']}, {'end': 4111.413, 'start': 3789.315, 'title': 'Data analysis with excel', 'summary': 'Covers an introduction to microsoft excel for data analysis, including functions, formulas, features, and benefits, with a scenario illustrating its application in a startup and its relevance for various organizational requirements.', 'duration': 322.098, 'highlights': ['Microsoft Excel is a great platform to perform various mathematical calculations on large data sets, with features like searching, sorting, filtering, and data visualization, which is essential for various organizations. Excel proves to be a great platform to perform various mathematical calculation on large data sets, which is one of the biggest requirements of various organizations these days.', 'Excel allows beautifying data and presenting it in the form of charts, tables, and data bars, facilitating reporting, accounting, and analysis tasks. Excel also allows you to beautify your data and present it in the form of charts, tables and data bars. Excel also provides good security for your data.', "The scenario illustrates a startup's need for efficient data handling and analysis, emphasizing the application of Microsoft Excel to cater to business needs in a cost-efficient manner, leading to the decision to train employees in Excel. The scenario illustrates a startup's need for efficient data handling and analysis, emphasizing the application of Microsoft Excel to cater to business needs in a cost-efficient manner, leading to the decision to train employees in Excel.", 'Excel is used for recording, analyzing, and visualizing data, offering a wide range of functions and formulas for various tasks beyond tabular data storage. Excel or you might have heard a spreadsheet can be basically used for a lot of different tasks than just storing the information in so-called tabular format. Now Microsoft Excel is an application that is used for recording, analyzing, and visualizing data.']}, {'end': 5123.993, 'start': 4111.413, 'title': 'Excel data management', 'summary': 'Covers various options for uploading data, such as connecting to a database, using existing connections, adding up numbers using functions like sum and sumif, automatic population of cells, and splitting data using delimiters or formulas. it also explores transposing rows into columns and columns into rows using shortcuts and formulas.', 'duration': 1012.58, 'highlights': ['Various options for uploading data The chapter explains options for uploading data, including loading a file from the machine, getting data from a web source, connecting to a database, and using existing connections like SQL server, analysis services, OData data feed, XML, data connection wizard, and Microsoft query.', "Adding up numbers using functions The transcript demonstrates adding up numbers using functions like SUM and SUMIF, allowing users to calculate totals based on specific criteria, and provides examples of using shortcuts like 'alt equals' and 'auto sum'.", "Automatic population of cells It covers automatic population of cells by dragging and dropping, using shortcuts like 'ctrl r' and 'ctrl d', as well as using the 'fill' option in the home menu, and 'flash fill' to fill up values with default delimiters.", 'Splitting data using delimiters or formulas The chapter explains how to split data using delimiters like comma, and advanced options like using formulas to split names into first, middle, and last names, allowing users to organize data and update split data when the original data is updated.', "Transposing rows into columns and columns into rows It provides instructions on transposing rows into columns and columns into rows using shortcuts like 'control alt V' and 'copy-paste special', as well as transposing with formulas to change the orientation of data from horizontal to vertical."]}], 'duration': 1544.903, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw3579090.jpg', 'highlights': ['The chapter explains options for uploading data, including loading a file from the machine, getting data from a web source, connecting to a database, and using existing connections like SQL server, analysis services, OData data feed, XML, data connection wizard, and Microsoft query.', 'Excel allows beautifying data and presenting it in the form of charts, tables, and data bars, facilitating reporting, accounting, and analysis tasks. Excel also allows you to beautify your data and present it in the form of charts, tables and data bars. Excel also provides good security for your data.', 'Various options for uploading data The chapter explains options for uploading data, including loading a file from the machine, getting data from a web source, connecting to a database, and using existing connections like SQL server, analysis services, OData data feed, XML, data connection wizard, and Microsoft query.', 'Excel is used for recording, analyzing, and visualizing data, offering a wide range of functions and formulas for various tasks beyond tabular data storage. Excel or you might have heard a spreadsheet can be basically used for a lot of different tasks than just storing the information in so-called tabular format. Now Microsoft Excel is an application that is used for recording, analyzing, and visualizing data.', 'The process involves selecting the list range A1 to J126 and the criteria range M1 to V2 for filtering the data.', 'Copying the filtered data to another location, for instance, from M7 to P7, allows for obtaining the desired results.', 'Using the data filter advanced option, the chapter explains the process of filtering data based on specific criteria.', "Adding up numbers using functions The transcript demonstrates adding up numbers using functions like SUM and SUMIF, allowing users to calculate totals based on specific criteria, and provides examples of using shortcuts like 'alt equals' and 'auto sum'.", "Automatic population of cells It covers automatic population of cells by dragging and dropping, using shortcuts like 'ctrl r' and 'ctrl d', as well as using the 'fill' option in the home menu, and 'flash fill' to fill up values with default delimiters.", 'Splitting data using delimiters or formulas The chapter explains how to split data using delimiters like comma, and advanced options like using formulas to split names into first, middle, and last names, allowing users to organize data and update split data when the original data is updated.', "Transposing rows into columns and columns into rows It provides instructions on transposing rows into columns and columns into rows using shortcuts like 'control alt V' and 'copy-paste special', as well as transposing with formulas to change the orientation of data from horizontal to vertical."]}, {'end': 6480.604, 'segs': [{'end': 5207.406, 'src': 'embed', 'start': 5124.434, 'weight': 0, 'content': [{'end': 5136.64, 'text': "So this is a very simple way in which you can basically use the Excel's capability to transpose your data and convert your rows into columns and columns into rows.", 'start': 5124.434, 'duration': 12.206}, {'end': 5151.267, 'text': 'Apart from working on additions, subtractions, filling up your data, sorting the data or basically splitting your data, transposing your data,', 'start': 5137.28, 'duration': 13.987}, {'end': 5155.847, 'text': 'one of the other requirements is sorting and filtering your data.', 'start': 5151.267, 'duration': 4.58}, {'end': 5163.636, 'text': "now that can be very handy when you're working on huge data and you would want to sort it in a particular order, say ascending or descending,", 'start': 5155.847, 'duration': 7.789}, {'end': 5172.436, 'text': 'or might be based on a particular field, or if that field was or if the cell was highlighted with a particular color, sorting the data.', 'start': 5163.636, 'duration': 8.8}, {'end': 5177.303, 'text': "so let's look at how excel can be used for sorting and filtering.", 'start': 5172.436, 'duration': 4.867}, {'end': 5181.549, 'text': "examples are pretty simple here, so let's check that.", 'start': 5177.303, 'duration': 4.246}, {'end': 5186.346, 'text': "so if we're going to sort and filter and say this is the data i have, Say, for example,", 'start': 5181.549, 'duration': 4.797}, {'end': 5191.171, 'text': 'I would want to sort the values in the department column alphabetically.', 'start': 5186.346, 'duration': 4.825}, {'end': 5197.517, 'text': "So what I can do is I can select department column and I'm already in the home tab.", 'start': 5191.551, 'duration': 5.966}, {'end': 5200.92, 'text': 'I can straight away go here with a sort and filter.', 'start': 5197.817, 'duration': 3.103}, {'end': 5207.406, 'text': "I can then say sort A to Z and that's basically alphabetically sorting your department column.", 'start': 5201.52, 'duration': 5.886}], 'summary': 'Learn how to use excel to transpose, sort, and filter data efficiently.', 'duration': 82.972, 'max_score': 5124.434, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw5124434.jpg'}, {'end': 5668.793, 'src': 'embed', 'start': 5642.683, 'weight': 4, 'content': [{'end': 5647.167, 'text': 'It basically has different filters, which we have learned earlier how to use.', 'start': 5642.683, 'duration': 4.484}, {'end': 5653.131, 'text': 'And this is basically my table, which is a collection of cells, which has some special features.', 'start': 5647.747, 'duration': 5.384}, {'end': 5656.474, 'text': 'So we can easily add rows to this table.', 'start': 5653.752, 'duration': 2.722}, {'end': 5658.736, 'text': 'We can add columns to this table.', 'start': 5656.934, 'duration': 1.802}, {'end': 5668.793, 'text': 'And we can even do some calculations so, for example, here I can click on this one, I can basically enter some field.', 'start': 5659.324, 'duration': 9.469}], 'summary': 'The table has filters, can add rows/columns, and perform calculations.', 'duration': 26.11, 'max_score': 5642.683, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw5642683.jpg'}, {'end': 5898.861, 'src': 'embed', 'start': 5868.536, 'weight': 6, 'content': [{'end': 5875.12, 'text': "Let's learn about one more efficient way of working with the data and that's using your dropdowns.", 'start': 5868.536, 'duration': 6.584}, {'end': 5878.161, 'text': "So let's see how dropdowns work here.", 'start': 5875.62, 'duration': 2.541}, {'end': 5887.919, 'text': 'Now Say, for example, you have this data which has the values in the food column and department is empty, and say, for example,', 'start': 5878.922, 'duration': 8.997}, {'end': 5891.26, 'text': 'you would want to enter the values in department.', 'start': 5887.919, 'duration': 3.341}, {'end': 5898.861, 'text': 'However, you would want to select the department should either have produce or meat and bakery.', 'start': 5891.34, 'duration': 7.521}], 'summary': 'Using dropdowns to efficiently work with data, selecting department values: produce, meat, and bakery.', 'duration': 30.325, 'max_score': 5868.536, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw5868536.jpg'}, {'end': 6068.16, 'src': 'embed', 'start': 6042.306, 'weight': 7, 'content': [{'end': 6048.851, 'text': 'so what we can do is we can open up a blank excel sheet and say, for example, you have been provided.', 'start': 6042.306, 'duration': 6.545}, {'end': 6056.056, 'text': 'a text file or a csv file and you would want to import that data into your excel sheet.', 'start': 6049.594, 'duration': 6.462}, {'end': 6057.897, 'text': 'that can be easily done.', 'start': 6056.056, 'duration': 1.841}, {'end': 6060.918, 'text': "so right now i've opened an excel sheet.", 'start': 6057.897, 'duration': 3.021}, {'end': 6068.16, 'text': 'now i can click on data and here i have an option which says existing connections from other data sources.', 'start': 6060.918, 'duration': 7.242}], 'summary': 'Import data from text or csv file into excel using existing connections.', 'duration': 25.854, 'max_score': 6042.306, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6042306.jpg'}, {'end': 6440.799, 'src': 'embed', 'start': 6406.46, 'weight': 8, 'content': [{'end': 6409.963, 'text': 'You could basically choose what kind of connection would you want.', 'start': 6406.46, 'duration': 3.503}, {'end': 6413.506, 'text': 'So these are all the different options which we can go for.', 'start': 6410.443, 'duration': 3.063}, {'end': 6417.509, 'text': 'and we can basically connect to a database.', 'start': 6414.267, 'duration': 3.242}, {'end': 6425.072, 'text': 'for example, if i have some database and say, for example, access database,', 'start': 6417.509, 'duration': 7.563}, {'end': 6430.915, 'text': 'i can see if there are some files with that particular database and i can import it.', 'start': 6425.072, 'duration': 5.843}, {'end': 6440.799, 'text': 'so similarly we can also, uh, click in here, which says new query, and that also gives you an option of getting the data from your files,', 'start': 6430.915, 'duration': 9.884}], 'summary': 'Options for database connection, including importing files and running new queries, are available.', 'duration': 34.339, 'max_score': 6406.46, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6406460.jpg'}, {'end': 6480.604, 'src': 'embed', 'start': 6455.647, 'weight': 9, 'content': [{'end': 6465.733, 'text': 'you can go from cloud, you can get it from online services, you can get it from other sources, which says from web, from your hadoop file system,', 'start': 6455.647, 'duration': 10.086}, {'end': 6475.38, 'text': 'from active directory, from a blank query, and you can even combine queries wherein you can run a Power Query editor.', 'start': 6465.733, 'duration': 9.647}, {'end': 6480.604, 'text': 'you can get the data from different sources and then you can bring it into your Excel.', 'start': 6475.38, 'duration': 5.224}], 'summary': 'Data can be retrieved from multiple sources and imported into excel using power query editor.', 'duration': 24.957, 'max_score': 6455.647, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6455647.jpg'}], 'start': 5124.434, 'title': 'Excel data operations in excel', 'summary': 'Covers transposing and sorting data, custom sorting, filtering, using tables and dropdowns, and importing data into excel, with practical examples and techniques for organizing and manipulating data effectively.', 'chapters': [{'end': 5337.983, 'start': 5124.434, 'title': 'Excel data transposition and sorting', 'summary': 'Demonstrates how to transpose data in excel and sort it alphabetically and numerically, showcasing the process with practical examples.', 'duration': 213.549, 'highlights': ['Excel can be used for sorting and filtering data, demonstrated with practical examples. The chapter emphasizes the utility of Excel in sorting and filtering data, showcasing practical examples to illustrate the process.', 'Transposing data in Excel is showcased as a simple way to convert rows into columns and vice versa. The process of transposing data in Excel is highlighted as a simple method to convert rows into columns and vice versa, offering a practical demonstration of the concept.', "Sorting the department column alphabetically using Excel's sort and filter function is demonstrated. A practical demonstration of using Excel's sort and filter function to alphabetically sort the department column is showcased, providing a clear example of the sorting process."]}, {'end': 5566.671, 'start': 5339.048, 'title': 'Data sorting and filtering', 'summary': 'Explains how to custom sort, sort by date or color, and add filters in excel, including selecting the column for sorting and applying filters based on value thresholds and date ranges.', 'duration': 227.623, 'highlights': ['You can custom sort data in Excel by choosing the column for sorting and the order, providing flexibility in organizing the data. The speaker explains the process of custom sorting in Excel, allowing users to choose the column and the order for sorting, providing flexibility in organizing the data.', 'Sorting data by date or color in Excel involves selecting the specific field, right-clicking, and choosing the desired sorting order, such as oldest to newest or based on color. The transcript details the process of sorting data by date or color in Excel by selecting the specific field and choosing the desired sorting order, providing a method to organize data based on date or color.', 'The use of filters in Excel allows for the selection of specific data based on criteria, such as date ranges or color, providing a way to focus on relevant information. The speaker explains how filters in Excel enable the selection of specific data based on criteria like date ranges or color, allowing users to focus on relevant information within the dataset.']}, {'end': 6009.791, 'start': 5567.431, 'title': 'Using tables and dropdowns in excel', 'summary': 'Discusses using tables to organize data in excel, including adding rows and columns, performing calculations, and creating dropdown lists for data validation.', 'duration': 442.36, 'highlights': ['Creating a table in Excel allows for easy addition of rows and columns, and performing calculations on the data. The process involves selecting the data, choosing the table option, and then being able to add rows, columns, and perform calculations on the data.', 'The use of tables in Excel enables the user to easily add rows and columns to the data, allowing for efficient organization and manipulation of information. Tables in Excel facilitate the addition of rows and columns, providing a structured format for efficient data organization and manipulation.', "Creating dropdown lists in Excel allows for easy data validation, limiting the user's input to predefined options. By using the data validation feature, dropdown lists can be created to restrict user input to specific predefined options, ensuring data accuracy and consistency."]}, {'end': 6480.604, 'start': 6010.529, 'title': 'Importing data into excel', 'summary': 'Covers importing data into excel from local machine or web, including importing csv files and creating connections with databases, using features such as text import wizard and power query editor.', 'duration': 470.075, 'highlights': ['Importing data from a local machine Demonstrates importing a CSV file from a local machine into Excel, using the text import wizard and choosing the delimiter, showcasing the ability to easily import data from different sources.', 'Importing data from a web source Illustrates importing data from a web source, specifying the path, and using the text import wizard to bring in the data, providing the ability to import data directly from the web into Excel.', 'Creating connections with databases Explains the process of creating connections with databases, including options to import data from SQL server, MySQL database, and other sources, demonstrating the versatility of Excel in connecting with various data repositories.', 'Utilizing Power Query editor Mentions the ability to use the Power Query editor to combine queries and fetch data from different sources, showcasing the advanced capabilities of Excel in importing and manipulating data.']}], 'duration': 1356.17, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw5124434.jpg', 'highlights': ['Excel can be used for sorting and filtering data, demonstrated with practical examples.', 'Transposing data in Excel is showcased as a simple way to convert rows into columns and vice versa.', "Sorting the department column alphabetically using Excel's sort and filter function is demonstrated.", 'The use of filters in Excel allows for the selection of specific data based on criteria, such as date ranges or color.', 'Creating a table in Excel allows for easy addition of rows and columns, and performing calculations on the data.', 'The use of tables in Excel enables the user to easily add rows and columns to the data, allowing for efficient organization and manipulation of information.', "Creating dropdown lists in Excel allows for easy data validation, limiting the user's input to predefined options.", 'Importing data from a local machine Demonstrates importing a CSV file from a local machine into Excel, using the text import wizard and choosing the delimiter, showcasing the ability to easily import data from different sources.', 'Creating connections with databases Explains the process of creating connections with databases, including options to import data from SQL server, MySQL database, and other sources, demonstrating the versatility of Excel in connecting with various data repositories.', 'Utilizing Power Query editor Mentions the ability to use the Power Query editor to combine queries and fetch data from different sources, showcasing the advanced capabilities of Excel in importing and manipulating data.']}, {'end': 7831.12, 'segs': [{'end': 6592.596, 'src': 'embed', 'start': 6561.607, 'weight': 0, 'content': [{'end': 6565.469, 'text': 'Now I can say let it sort based on cell values.', 'start': 6561.607, 'duration': 3.862}, {'end': 6568.248, 'text': 'And the order, what we have here.', 'start': 6566.067, 'duration': 2.181}, {'end': 6571.85, 'text': "so we have newest, oldest, so let's select this.", 'start': 6568.248, 'duration': 3.602}, {'end': 6577.033, 'text': 'I can say Okay, and now, if you see, the date has been sorted, so we have your.', 'start': 6571.85, 'duration': 5.183}, {'end': 6581.21, 'text': '10 18 2007 on the top.', 'start': 6578.568, 'duration': 2.642}, {'end': 6592.596, 'text': 'so that seems to be the latest date, and as we go down we will see an earlier month hour and earlier month than that in this date listed.', 'start': 6581.21, 'duration': 11.386}], 'summary': 'Cell values sorted in descending order, with 10/18/2007 on top.', 'duration': 30.989, 'max_score': 6561.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6561607.jpg'}, {'end': 6677.588, 'src': 'embed', 'start': 6649.801, 'weight': 1, 'content': [{'end': 6656.026, 'text': 'we could also use the filter option here on the top right and we could do it.', 'start': 6649.801, 'duration': 6.225}, {'end': 6666.013, 'text': 'or i could just say sort z to a and then it has arranged the data based on the agent column being in descending order.', 'start': 6656.026, 'duration': 9.987}, {'end': 6670.483, 'text': 'now i can go into area And then I can again do a sort.', 'start': 6666.013, 'duration': 4.47}, {'end': 6677.588, 'text': 'And I wanted my area column to be used for sorting the data and ascending to descending.', 'start': 6670.763, 'duration': 6.825}], 'summary': 'Using filter and sort options, data was arranged based on the agent and area columns.', 'duration': 27.787, 'max_score': 6649.801, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6649801.jpg'}, {'end': 6788.74, 'src': 'embed', 'start': 6759.19, 'weight': 2, 'content': [{'end': 6768.774, 'text': 'So what we can do is we can basically have area field selected and I would want to sort this particular data.', 'start': 6759.19, 'duration': 9.584}, {'end': 6773.476, 'text': 'So I have South County, Central and North County.', 'start': 6769.314, 'duration': 4.162}, {'end': 6776.657, 'text': 'So I can basically go for custom sort.', 'start': 6773.916, 'duration': 2.741}, {'end': 6782.437, 'text': "and then i can choose which is the value or column which i'm interested in.", 'start': 6777.955, 'duration': 4.482}, {'end': 6784.398, 'text': "let's go for area.", 'start': 6782.437, 'duration': 1.961}, {'end': 6788.74, 'text': 'we will go for something like cell values.', 'start': 6784.398, 'duration': 4.342}], 'summary': 'The data can be sorted by area field, with options for south county, central, and north county.', 'duration': 29.55, 'max_score': 6759.19, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6759190.jpg'}, {'end': 6929.718, 'src': 'embed', 'start': 6903.191, 'weight': 3, 'content': [{'end': 6908.78, 'text': 'So the problem statement is we would want to find all the houses in central area.', 'start': 6903.191, 'duration': 5.589}, {'end': 6917.052, 'text': 'Now how do we do that? We can do a sorting but we would want to use the filter which you see here is implemented.', 'start': 6909.381, 'duration': 7.671}, {'end': 6917.873, 'text': 'So how do you do it?', 'start': 6917.092, 'duration': 0.781}, {'end': 6924.556, 'text': 'so you can select this area and say, for example, i would want to apply filter.', 'start': 6918.354, 'duration': 6.202}, {'end': 6929.718, 'text': "i can just go in here and i can say let's get a filter on my first row.", 'start': 6924.556, 'duration': 5.162}], 'summary': 'Finding all houses in central area using filters and sorting.', 'duration': 26.527, 'max_score': 6903.191, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6903191.jpg'}, {'end': 7009.612, 'src': 'embed', 'start': 6980.112, 'weight': 12, 'content': [{'end': 6985.616, 'text': 'So I could do this or you can clear the filters from area and you get your data back.', 'start': 6980.112, 'duration': 5.504}, {'end': 6990.158, 'text': "So that's in one way you can filter out your data.", 'start': 6986.296, 'duration': 3.862}, {'end': 6993.701, 'text': "So let's look at an example of sort and filter,", 'start': 6990.639, 'duration': 3.062}, {'end': 7003.407, 'text': 'where we might have to filter the data based on two columns or multiple columns with different kind of values, where it could be and and or condition.', 'start': 6993.701, 'duration': 9.706}, {'end': 7009.612, 'text': 'Now say, for example, this is the data I have and this is the question which we need to answer,', 'start': 7004.008, 'duration': 5.604}], 'summary': 'The transcript discusses filtering data based on multiple conditions and demonstrates the process for sorting and filtering.', 'duration': 29.5, 'max_score': 6980.112, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6980112.jpg'}, {'end': 7245.859, 'src': 'embed', 'start': 7218.779, 'weight': 4, 'content': [{'end': 7227.601, 'text': 'which can be either replacing the existing content or in the same sheet in different set of columns and rows.', 'start': 7218.779, 'duration': 8.822}, {'end': 7229.302, 'text': 'you can have your result.', 'start': 7227.601, 'duration': 1.701}, {'end': 7232.195, 'text': "Let's look at one more example of filtering,", 'start': 7229.614, 'duration': 2.581}, {'end': 7240.037, 'text': 'where you are trying to filter the data based on an AND condition condition being met in two different columns,', 'start': 7232.195, 'duration': 7.842}, {'end': 7245.859, 'text': 'and then you would want to filter out the data for only specific columns.', 'start': 7240.037, 'duration': 5.822}], 'summary': 'The transcript discusses data filtering based on and condition with specific columns.', 'duration': 27.08, 'max_score': 7218.779, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw7218779.jpg'}, {'end': 7472.448, 'src': 'embed', 'start': 7417.304, 'weight': 5, 'content': [{'end': 7424.191, 'text': 'that you would want the agents with a house in north county area having two bedrooms and single type family.', 'start': 7417.304, 'duration': 6.887}, {'end': 7434.06, 'text': 'so in this way you can basically do advanced filtering, get your data and get it stored in the sheet anywhere at a different location.', 'start': 7424.191, 'duration': 9.869}, {'end': 7442.364, 'text': 'well, i could have also done filtering in place and that would have replaced the data which we have.', 'start': 7434.06, 'duration': 8.304}, {'end': 7443.684, 'text': "but that's not what we want.", 'start': 7442.364, 'duration': 1.32}, {'end': 7447.445, 'text': 'we would want the filtered result in a different place.', 'start': 7443.684, 'duration': 3.761}, {'end': 7450.946, 'text': 'so this is how you can do some advanced filtering.', 'start': 7447.445, 'duration': 3.501}, {'end': 7461.669, 'text': 'we can also use excel to filter out the data in one particular column, which might be conditional, or, say, using some numerical filters.', 'start': 7450.946, 'duration': 10.723}, {'end': 7472.448, 'text': 'now here say, for example, the problem statement is that you would want to display all the houses whose list price is between 45,000 to 600,000..', 'start': 7461.669, 'duration': 10.779}], 'summary': 'Using advanced filtering in excel, agents can find 2-bedroom single-family homes in north county area and store the filtered data in a different location.', 'duration': 55.144, 'max_score': 7417.304, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw7417304.jpg'}, {'end': 7742.02, 'src': 'embed', 'start': 7656.584, 'weight': 6, 'content': [{'end': 7661.828, 'text': 'I can go into advanced and then I can say copy to another location.', 'start': 7656.584, 'duration': 5.244}, {'end': 7665.01, 'text': 'So it is selecting my A1 to J126.', 'start': 7662.148, 'duration': 2.862}, {'end': 7669.913, 'text': 'Criteria range is based on M1 to W2.', 'start': 7666.05, 'duration': 3.863}, {'end': 7674.616, 'text': 'And then I would want my result from this particular column.', 'start': 7671.474, 'duration': 3.142}, {'end': 7676.367, 'text': "So let's say OK.", 'start': 7675.327, 'duration': 1.04}, {'end': 7684.851, 'text': 'And now you have your data filtered out in a different location in the sheet, which has been filtered based on your AND condition.', 'start': 7676.788, 'duration': 8.063}, {'end': 7692.354, 'text': 'So you can filter out the data in this way, or you could just apply a filter on a column and give the conditions.', 'start': 7685.231, 'duration': 7.123}, {'end': 7695.995, 'text': "Now let's solve one more interesting problem.", 'start': 7693.514, 'duration': 2.481}, {'end': 7702.898, 'text': 'And here we would want to use Excel where we would have an AND, AND, and OR condition.', 'start': 7696.515, 'duration': 6.383}, {'end': 7705.801, 'text': 'So say, for example, this is the data given to you.', 'start': 7703.359, 'duration': 2.442}, {'end': 7711.385, 'text': 'And the question is that you would want to find all the houses in North County.', 'start': 7706.181, 'duration': 5.204}, {'end': 7723.093, 'text': "Again, that's a spelling mistake, but then they are North County area with a list price greater than 300,000 and having three or four bedrooms.", 'start': 7711.745, 'duration': 11.348}, {'end': 7725.915, 'text': 'So the bedroom has conditional.', 'start': 7723.213, 'duration': 2.702}, {'end': 7728.136, 'text': 'So it has all three and four.', 'start': 7726.155, 'duration': 1.981}, {'end': 7732.898, 'text': 'And then basically you have list price which is greater than 300 000.', 'start': 7728.837, 'duration': 4.061}, {'end': 7742.02, 'text': 'now i could have obviously selected the columns and then basically gone for a filter.', 'start': 7732.898, 'duration': 9.122}], 'summary': 'Using excel, data is filtered based on and and or conditions for houses in north county with a list price > $300,000 and 3 or 4 bedrooms.', 'duration': 85.436, 'max_score': 7656.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw7656584.jpg'}], 'start': 6480.844, 'title': 'Data sorting and filtering in excel and real estate', 'summary': 'Covers sorting data in excel by various columns and advanced filtering techniques, as well as filtering real estate data based on specific criteria to extract customized results.', 'chapters': [{'end': 6876.195, 'start': 6480.844, 'title': 'Sorting data in excel', 'summary': 'Explains how to sort data in excel based on different columns, such as sorting dates from newest to oldest, arranging data in ascending and descending order based on specific columns, and custom sorting based on a specific order of values in a column.', 'duration': 395.351, 'highlights': ['Sorting dates from newest to oldest The chapter demonstrates sorting dates in descending order, with the latest date (10 18 2007) at the top and earlier dates following below.', "Arranging data in ascending and descending order based on specific columns The explanation includes sorting data in ascending order of area and descending order of agent name, utilizing the 'sort z to a' for descending order and custom sorting based on specific columns.", "Custom sorting based on a specific order of values in a column The chapter illustrates custom sorting based on a specific order of values in the area column, such as sorting data according to the order of 'South County, Central, and North County'."]}, {'end': 7353.148, 'start': 6876.195, 'title': 'Filtering data in real estate', 'summary': 'Discusses how to filter real estate data by area and specific criteria, demonstrating the use of sorting and filtering techniques to extract customized results based on multiple conditions and columns.', 'duration': 476.953, 'highlights': ['The chapter discusses how to apply a filter to find all the houses in the central area, demonstrating the process of selecting the area and applying a filter to obtain the desired data. Demonstrates the process of applying a filter to find houses in the central area.', 'It explains the process of filtering data based on two columns and multiple conditions to extract specific results, using examples of filtering houses based on different criteria in the central and South County areas. Demonstrates the process of filtering data based on multiple conditions and columns to extract specific results.', 'The chapter illustrates the application of advanced filtering techniques, including the use of an advanced filter to extract results based on two columns and specific criteria, showcasing the process of copying data to another location to obtain the filtered result. Illustrates the application of advanced filtering techniques, including the use of an advanced filter to extract results based on multiple columns and criteria.', 'It describes the process of filtering data based on an AND condition by populating the data based on specific criteria, such as filtering agents with properties in North County, having two bedrooms, and a single type family, and displaying the results in a specified set of columns. Describes the process of filtering data based on an AND condition and populating the data based on specific criteria.']}, {'end': 7565.993, 'start': 7353.646, 'title': 'Advanced filtering in excel', 'summary': 'Demonstrates advanced filtering in excel, including copying filtered data to another location, filtering based on specific criteria, and using numerical filters to display data within certain price ranges.', 'duration': 212.347, 'highlights': ['Demonstrating advanced filtering by copying filtered data to another location The speaker demonstrates how to copy filtered data to another location in Excel, showcasing the process of selecting a list range, specifying criteria, and copying the filtered result to a different place.', 'Filtering data based on specific criteria The demonstration includes filtering data based on specific criteria, such as filtering agents with a house in a specific area and specific features, showcasing the capability to extract targeted data using advanced filtering techniques.', 'Using numerical filters to display data within certain price ranges The chapter explains how to use Excel to filter data based on numerical criteria, such as displaying houses within specific price ranges, providing practical examples of filtering data based on numerical values.']}, {'end': 7831.12, 'start': 7565.993, 'title': 'Filtering data in excel', 'summary': 'Explains how to filter data in excel using and and or conditions, with a specific example of filtering houses with a list price greater than 300,000 and three or four bedrooms, and provides guidance on applying filters and selecting criteria for data manipulation.', 'duration': 265.127, 'highlights': ['The chapter explains how to filter data in Excel using AND and OR conditions It describes the process of applying multiple conditions to filter data, which enhances data manipulation capabilities.', 'Specific example of filtering houses with a list price greater than 300,000 and three or four bedrooms It illustrates a practical example of filtering data based on specific criteria, such as list price and number of bedrooms, demonstrating the application of filtering techniques.', 'Guidance on applying filters and selecting criteria for data manipulation It provides instructions on how to apply filters and select criteria to manipulate data effectively, enhancing the understanding of data filtering processes.']}], 'duration': 1350.276, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw6480844.jpg', 'highlights': ['Sorting dates from newest to oldest, showcasing the latest date (10 18 2007) at the top.', "Arranging data in ascending and descending order based on specific columns, utilizing 'sort z to a' for descending order.", "Custom sorting based on a specific order of values in the area column, such as 'South County, Central, and North County'.", 'Applying a filter to find houses in the central area, demonstrating the process of selecting the area and applying a filter.', 'Filtering data based on two columns and multiple conditions to extract specific results.', 'Application of advanced filtering techniques, including the use of an advanced filter to extract results based on multiple columns and criteria.', 'Filtering data based on an AND condition by populating the data based on specific criteria.', 'Demonstrating advanced filtering by copying filtered data to another location in Excel.', 'Filtering data based on specific criteria, such as filtering agents with a house in a specific area and specific features.', 'Using numerical filters to display data within certain price ranges.', 'Explaining how to filter data in Excel using AND and OR conditions, applying multiple conditions to enhance data manipulation capabilities.', 'Specific example of filtering houses with a list price greater than 300,000 and three or four bedrooms.', 'Guidance on applying filters and selecting criteria for data manipulation, providing instructions on how to manipulate data effectively.']}, {'end': 9316.709, 'segs': [{'end': 7888.561, 'src': 'embed', 'start': 7831.12, 'weight': 0, 'content': [{'end': 7834.942, 'text': 'and then i can say greater than three hundred thousand,', 'start': 7831.12, 'duration': 3.822}, {'end': 7847.637, 'text': 'And then I can say for so this one basically gives me a situation where your list price has to be greater than 300,000 and bedroom should be either three or four.', 'start': 7836.573, 'duration': 11.064}, {'end': 7849.838, 'text': 'So we have given our filtering criteria.', 'start': 7847.677, 'duration': 2.161}, {'end': 7851.899, 'text': 'Now to get the result.', 'start': 7850.399, 'duration': 1.5}, {'end': 7859.417, 'text': 'What we can do is we can go into data, we can go into advanced and we can say copy to another location.', 'start': 7852.299, 'duration': 7.118}, {'end': 7867.099, 'text': 'so our list range is selected, which is columns a to j, row, number 1 to 126.', 'start': 7859.417, 'duration': 7.682}, {'end': 7876.362, 'text': 'your criteria range is given in m1 to v3, where we have specified and we are saying the result would be in m7 to v7.', 'start': 7867.099, 'duration': 9.263}, {'end': 7880.383, 'text': 'so if i do this now, i have got the same data which we were seeing earlier,', 'start': 7876.362, 'duration': 4.021}, {'end': 7888.561, 'text': 'And here the bedroom values are 3 or 4 and basically the list price is greater than 300,000..', 'start': 7880.979, 'duration': 7.582}], 'summary': 'Using filtering criteria, a list with prices greater than $300,000 and 3-4 bedrooms is obtained, resulting in 126 rows of data.', 'duration': 57.441, 'max_score': 7831.12, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw7831120.jpg'}, {'end': 8027.999, 'src': 'embed', 'start': 7999.969, 'weight': 4, 'content': [{'end': 8005.997, 'text': "when you're working in excel, you will often hear the terms formula and function used frequently and sometimes interchangeably.", 'start': 7999.969, 'duration': 6.028}, {'end': 8009.43, 'text': 'They are closely related, but not exactly the same.', 'start': 8006.909, 'duration': 2.521}, {'end': 8013.532, 'text': 'Technically, a formula is an expression that begins with an equal sign.', 'start': 8010.091, 'duration': 3.441}, {'end': 8017.554, 'text': 'Functions are used to perform complex calculations that cannot be done manually.', 'start': 8013.932, 'duration': 3.622}, {'end': 8020.656, 'text': 'Functions have names that reflect their intended use.', 'start': 8018.275, 'duration': 2.381}, {'end': 8027.999, 'text': "Now, let me take you back to the Excel sheet, and I'll show you what a function is.", 'start': 8022.096, 'duration': 5.903}], 'summary': 'Excel uses formulas and functions for complex calculations.', 'duration': 28.03, 'max_score': 7999.969, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw7999969.jpg'}, {'end': 8073.482, 'src': 'embed', 'start': 8048.466, 'weight': 5, 'content': [{'end': 8054.051, 'text': 'so excel provides a function called sum that allows you to select a range of cells.', 'start': 8048.466, 'duration': 5.585}, {'end': 8065.319, 'text': "so i'll select a1 to a4, i'll close the bracket and if i hit enter, It will return me the result of 20 plus 30 plus 40 plus 90, which is 180..", 'start': 8054.051, 'duration': 11.268}, {'end': 8066.98, 'text': 'Similarly, there are other functions.', 'start': 8065.319, 'duration': 1.661}, {'end': 8070.701, 'text': "Let's say I want to find the average of a range of cells.", 'start': 8067.4, 'duration': 3.301}, {'end': 8073.482, 'text': 'So you can use the average function to do that.', 'start': 8071.361, 'duration': 2.121}], 'summary': "Excel's sum function adds a1 to a4, resulting in 180. it also offers other functions like average.", 'duration': 25.016, 'max_score': 8048.466, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8048466.jpg'}, {'end': 8118.383, 'src': 'embed', 'start': 8090.384, 'weight': 3, 'content': [{'end': 8096.265, 'text': "let's dig deeper into learning the different types of functions available in Excel and use relevant formulas when needed along the journey.", 'start': 8090.384, 'duration': 5.881}, {'end': 8104.347, 'text': 'On your screens, you can see we have a mathematical functions worksheet where we will learn the different mathematical functions present in Excel.', 'start': 8097.005, 'duration': 7.342}, {'end': 8107.688, 'text': "Let's begin at looking at this simple table.", 'start': 8105.527, 'duration': 2.161}, {'end': 8113.461, 'text': 'that has three columns the quantity column, price per unit and total sales.', 'start': 8108.84, 'duration': 4.621}, {'end': 8118.383, 'text': "now let's find the product of quantity and price per unit to get the total sales value.", 'start': 8113.461, 'duration': 4.922}], 'summary': 'Learn excel mathematical functions for data analysis.', 'duration': 27.999, 'max_score': 8090.384, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8090384.jpg'}, {'end': 8230.678, 'src': 'embed', 'start': 8202.27, 'weight': 6, 'content': [{'end': 8207.474, 'text': 'The first array, we will have the quantity values while the second array will have the price per unit.', 'start': 8202.27, 'duration': 5.204}, {'end': 8212.378, 'text': "So, I'll select my SUMPRODUCT function.", 'start': 8208.834, 'duration': 3.544}, {'end': 8216.642, 'text': "My first array, I'll give the quantity values.", 'start': 8213.759, 'duration': 2.883}, {'end': 8221.925, 'text': "I'll give a comma and I'll select my second array that is price per unit.", 'start': 8217.322, 'duration': 4.603}, {'end': 8223.287, 'text': "I'll close the bracket.", 'start': 8221.946, 'duration': 1.341}, {'end': 8230.678, 'text': 'I hit enter, you can see we have the total sales value, that is 1165.', 'start': 8224.332, 'duration': 6.346}], 'summary': 'Using sumproduct function, the total sales value is 1165.', 'duration': 28.408, 'max_score': 8202.27, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8202270.jpg'}, {'end': 8306.232, 'src': 'embed', 'start': 8251.69, 'weight': 7, 'content': [{'end': 8258.575, 'text': "The isEven function returns true if the given number is an even value, and it returns false if it's not an even number.", 'start': 8251.69, 'duration': 6.885}, {'end': 8262.759, 'text': 'So we have here two numbers, 4 and 13.', 'start': 8258.596, 'duration': 4.163}, {'end': 8268.503, 'text': 'Now, if I want to check if 4 is an even number or not, I can use the isEven function.', 'start': 8262.759, 'duration': 5.744}, {'end': 8277.591, 'text': "So I'll give equal to sign and type isEven and I'll select this number or the cell, close the bracket.", 'start': 8268.564, 'duration': 9.027}, {'end': 8282.09, 'text': 'If I hit enter, it will return true because 4 is an even number.', 'start': 8277.611, 'duration': 4.479}, {'end': 8294.619, 'text': "Similarly, if I have to check for 13,, I'll write is even and select this cell, which is A3, close the bracket, I'll hit enter.", 'start': 8283.071, 'duration': 11.548}, {'end': 8297.161, 'text': 'it will return false, because 13 is not an even number.', 'start': 8294.619, 'duration': 2.542}, {'end': 8306.232, 'text': "Similarly, The isOdd function returns true if the given number is odd and it returns false if it's not an odd number.", 'start': 8298.101, 'duration': 8.131}], 'summary': 'The iseven function checks if 4 is even, returning true, and if 13 is even, returning false. the isodd function checks if a given number is odd.', 'duration': 54.542, 'max_score': 8251.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8251690.jpg'}, {'end': 8376.085, 'src': 'embed', 'start': 8343.244, 'weight': 9, 'content': [{'end': 8350.068, 'text': "The isLogical function returns true if the given value is a logical value and it returns false if it's not a logical value.", 'start': 8343.244, 'duration': 6.824}, {'end': 8353.471, 'text': 'When I say logical value, I mean true or false.', 'start': 8350.849, 'duration': 2.622}, {'end': 8357.454, 'text': "Now let's check if 30 is a logical value or not.", 'start': 8353.911, 'duration': 3.543}, {'end': 8361.577, 'text': "so i'll write equal to is logical.", 'start': 8358.476, 'duration': 3.101}, {'end': 8363.459, 'text': "i'll select 30.", 'start': 8361.577, 'duration': 1.882}, {'end': 8369.782, 'text': 'if i hit enter, it returns false, because 30 is not a logical number.', 'start': 8363.459, 'duration': 6.323}, {'end': 8376.085, 'text': "similarly, let's check for the next two values.", 'start': 8369.782, 'duration': 6.303}], 'summary': 'The islogical function checks if a value is logical (true/false). 30 is not a logical value.', 'duration': 32.841, 'max_score': 8343.244, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8343244.jpg'}, {'end': 8535.468, 'src': 'embed', 'start': 8495.831, 'weight': 11, 'content': [{'end': 8506.29, 'text': "so let's see I'll give the round down function and I'll provide the same number that is 35.316.", 'start': 8495.831, 'duration': 10.459}, {'end': 8510.995, 'text': "let's say I want to round down to two decimal places.", 'start': 8506.29, 'duration': 4.705}, {'end': 8515.62, 'text': 'if I hit enter, the result is 35.31.', 'start': 8510.995, 'duration': 4.625}, {'end': 8520.065, 'text': 'similarly, the roundup function rounds a number up away from zero.', 'start': 8515.62, 'duration': 4.445}, {'end': 8523.108, 'text': "so I'll write round up.", 'start': 8520.065, 'duration': 3.043}, {'end': 8526.005, 'text': "I'll give my number 35.316.", 'start': 8524.144, 'duration': 1.861}, {'end': 8535.468, 'text': 'If I give the number of digits, say 2, It will return 35.32.', 'start': 8526.005, 'duration': 9.463}], 'summary': 'Demonstrating round down to 2 decimal places gives 35.31, round up gives 35.32.', 'duration': 39.637, 'max_score': 8495.831, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8495831.jpg'}, {'end': 8915.441, 'src': 'embed', 'start': 8885.564, 'weight': 13, 'content': [{'end': 8889.246, 'text': 'So if you want to generate random numbers, you can use 5 square.', 'start': 8885.564, 'duration': 3.682}, {'end': 8890.926, 'text': 'The result should be 25.', 'start': 8889.266, 'duration': 1.66}, {'end': 8892.106, 'text': 'You can see it here.', 'start': 8890.926, 'duration': 1.18}, {'end': 8898.528, 'text': 'Now I want to find the power of 4 raised to 4.', 'start': 8893.347, 'duration': 5.181}, {'end': 8900.97, 'text': 'The result is 256.', 'start': 8898.528, 'duration': 2.442}, {'end': 8907.175, 'text': 'moving ahead, the minus function or the mod function returns the remainder when a number is divided by a divisor.', 'start': 8900.97, 'duration': 6.205}, {'end': 8908.616, 'text': "so let's see how to use it.", 'start': 8907.175, 'duration': 1.441}, {'end': 8913.62, 'text': "i'll use the mod function and give my number 10.", 'start': 8908.616, 'duration': 5.004}, {'end': 8915.441, 'text': "let's divide 10 by 3.", 'start': 8913.62, 'duration': 1.821}], 'summary': 'Generating random numbers using 5 square, 4 raised to 4 is 256, and demonstrating the mod function with 10 divided by 3.', 'duration': 29.877, 'max_score': 8885.564, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8885564.jpg'}, {'end': 8976.077, 'src': 'embed', 'start': 8946.584, 'weight': 15, 'content': [{'end': 8951.566, 'text': "hence it's skipped the count of total sales and it counted the remaining cells.", 'start': 8946.584, 'duration': 4.982}, {'end': 8955.107, 'text': 'now next, moving on to countA function.', 'start': 8951.946, 'duration': 3.161}, {'end': 8959.329, 'text': 'the countA function counts the number of cells in a range that are not empty.', 'start': 8955.107, 'duration': 4.222}, {'end': 8965.192, 'text': "so, for example, if I use the countA function now, let's say I'll select from L1 to L4.", 'start': 8959.329, 'duration': 5.863}, {'end': 8968.632, 'text': 'close the bracket.', 'start': 8968.032, 'duration': 0.6}, {'end': 8971.594, 'text': 'it has returned me 3 because we had an empty cell.', 'start': 8968.632, 'duration': 2.962}, {'end': 8976.077, 'text': 'so it skipped that count and it counted the remaining cells that were non-empty.', 'start': 8971.594, 'duration': 4.483}], 'summary': 'Counta function counts non-empty cells in range, returning 3 for l1 to l4.', 'duration': 29.493, 'max_score': 8946.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8946584.jpg'}, {'end': 9031.457, 'src': 'embed', 'start': 8997.97, 'weight': 12, 'content': [{'end': 9003.715, 'text': 'now, as the function suggests, it counts the number of blank or empty cells in a specified range of.', 'start': 8997.97, 'duration': 5.745}, {'end': 9013.144, 'text': "so if i use count blank and let's say i'll select this range from l1 to l4, close the bracket,", 'start': 9003.715, 'duration': 9.429}, {'end': 9021.29, 'text': 'you can see it has returned one because we had one empty cell in this range of cells.', 'start': 9014.185, 'duration': 7.105}, {'end': 9025.493, 'text': "now let's do one more count blank.", 'start': 9021.29, 'duration': 4.203}, {'end': 9031.457, 'text': "now i'll select from l2 to n4, close the bracket.", 'start': 9025.493, 'duration': 5.964}], 'summary': 'The countblank function identifies empty cells, returning the count. for example, selecting l1 to l4 yields one empty cell.', 'duration': 33.487, 'max_score': 8997.97, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw8997970.jpg'}, {'end': 9150.419, 'src': 'embed', 'start': 9116.208, 'weight': 16, 'content': [{'end': 9121.329, 'text': 'now, since one of the conditions is false, so our and function returns false.', 'start': 9116.208, 'duration': 5.121}, {'end': 9128.791, 'text': 'moving on to or function, the or function checks whether any of the arguments are true and returns true.', 'start': 9121.329, 'duration': 7.462}, {'end': 9132.792, 'text': 'if all the arguments do not meet the condition, it will return false.', 'start': 9128.791, 'duration': 4.001}, {'end': 9137.453, 'text': 'so you have to satisfy all the conditions in order to return a true value.', 'start': 9132.792, 'duration': 4.661}, {'end': 9138.814, 'text': "I'll use the OR function.", 'start': 9137.453, 'duration': 1.361}, {'end': 9150.419, 'text': "now let's say I'll give my condition from E2 to E4 greater than 15 comma from F2 to, let's say, F4 less than 25.", 'start': 9138.814, 'duration': 11.605}], 'summary': 'And function returns false, or function returns true based on conditions met.', 'duration': 34.211, 'max_score': 9116.208, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9116208.jpg'}, {'end': 9243.848, 'src': 'embed', 'start': 9216.973, 'weight': 10, 'content': [{'end': 9222.575, 'text': 'so the is text function checks if the given value is a text value and returns true or false.', 'start': 9216.973, 'duration': 5.602}, {'end': 9229.198, 'text': 'so if i were to check if this value is a text or not, so i can use the is text function.', 'start': 9222.575, 'duration': 6.623}, {'end': 9231.359, 'text': "i'll give reference to this cell.", 'start': 9229.198, 'duration': 2.161}, {'end': 9232.58, 'text': 'close the bracket.', 'start': 9231.359, 'duration': 1.221}, {'end': 9236.482, 'text': "it returns false because 10 is not a text, it's a number.", 'start': 9232.58, 'duration': 3.902}, {'end': 9238.603, 'text': "similarly, let's check the next one.", 'start': 9236.482, 'duration': 2.121}, {'end': 9240.224, 'text': "i'll select cell e3.", 'start': 9238.603, 'duration': 1.621}, {'end': 9243.848, 'text': 'If I hit enter, you can see it has written.', 'start': 9241.465, 'duration': 2.383}], 'summary': "The 'is text' function checks if a value is text, returning true or false. it returns false for the value '10' since it's a number.", 'duration': 26.875, 'max_score': 9216.973, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9216973.jpg'}], 'start': 7831.12, 'title': 'Excel data filtering and functions', 'summary': 'Explains how to filter data using criteria such as list price greater than $300,000 and bedrooms equal to either three or four, resulting in a dataset containing 3 or 4 bedrooms and list prices over $300,000. it also covers the basics of formulas and functions in excel, including examples of addition, division, sum, average, and sumproduct, with an emphasis on practical application. additionally, it covers logical functions like iseven, isodd, islogical, number manipulation functions such as round, int, truncate, ceil, floor, subtotal, fact, max, min, and randbetween, and various excel functions and operations such as square, power, mod, count, counta, count blank, and, or, is text, and length functions.', 'chapters': [{'end': 7888.561, 'start': 7831.12, 'title': 'Filtering data with multiple criteria', 'summary': 'Explains how to filter data using criteria such as list price greater than $300,000 and bedrooms equal to either three or four, resulting in a dataset containing 3 or 4 bedrooms and list prices over $300,000.', 'duration': 57.441, 'highlights': ['The process involves specifying filtering criteria for list price and bedrooms, resulting in a dataset with specific requirements. 300,000, 3 bedrooms, 4 bedrooms', "The method involves navigating to the data tab, selecting 'advanced', and then copying the filtered data to another location for further analysis. 126 rows, columns A to J, range M1 to V3", 'Upon execution, the result matches the specified criteria, displaying data where bedrooms are 3 or 4 and the list price exceeds $300,000. Matching dataset']}, {'end': 8230.678, 'start': 7888.562, 'title': 'Excel formulas and functions', 'summary': 'Covers the basics of formulas and functions in excel, including examples of addition, division, sum, average, and sumproduct, demonstrating the ability to perform basic calculations and complex operations, with an emphasis on practical application.', 'duration': 342.116, 'highlights': ['The chapter covers the basics of formulas and functions in Excel, including examples of addition, division, sum, average, and SUMPRODUCT, demonstrating the ability to perform basic calculations and complex operations, with an emphasis on practical application. It includes examples of addition, division, sum, average, and SUMPRODUCT, demonstrating the ability to perform basic calculations and complex operations, with an emphasis on practical application.', "In Microsoft Excel, a formula is an expression that operates on values in a range of cells. All formulas in Excel return a result, even when the result is an error. A formula in Excel is an expression that operates on values in a range of cells, always returning a result, even if it's an error.", 'The chapter explains the distinction between formulas and functions, stating that technically, a formula is an expression that begins with an equal sign, while functions are used to perform complex calculations that cannot be done manually. It distinguishes between formulas and functions, stating that a formula begins with an equal sign, while functions are used for complex calculations.', 'The transcript provides practical demonstrations of using functions like sum and average to calculate totals and averages of values in a range of cells, exemplifying their application in real-world scenarios. It provides practical demonstrations of using functions like sum and average to calculate totals and averages of values in a range of cells, exemplifying their real-world application.', 'The speaker demonstrates the application of the SUMPRODUCT function to find the total sales value by multiplying corresponding quantity and price per unit arrays, providing a practical illustration of complex calculation using a built-in Excel function. It demonstrates the application of the SUMPRODUCT function to find the total sales value by multiplying corresponding quantity and price per unit arrays, providing a practical illustration of complex calculation using a built-in Excel function.']}, {'end': 8885.224, 'start': 8230.678, 'title': 'Excel logical functions & number manipulation', 'summary': 'Covers the usage of logical functions like iseven, isodd, islogical along with number manipulation functions such as round, int, truncate, ceil, floor, subtotal, fact, max, min, and randbetween in excel, demonstrating their application and results.', 'duration': 654.546, 'highlights': ["The isEven function returns true if the given number is an even value, and false if it's not, with 4 returning true and 13 returning false, showing the application and results of the function. isEven function, 4 returning true, 13 returning false", "The isOdd function returns true if the given number is odd and false if it's not, with 4 returning false and 13 returning true, showcasing the application and results of the function. isOdd function, 4 returning false, 13 returning true", "The isLogical function returns true if the given value is a logical value, demonstrated by identifying true and false as logical values, providing practical examples of the function's usage. isLogical function, identifying true and false as logical values", 'The isNumber function identifies if the given value is a number, returning true for numerical values and false for text values, exemplifying its application with numeric and text values. isNumber function, returning true for numbers, false for text values', 'Demonstrates the application of round, round down, and round up functions to manipulate decimal numbers, providing examples of rounding to specific decimal places and the resulting rounded values. Application of round, round down, and round up functions, rounding to specific decimal places']}, {'end': 9316.709, 'start': 8885.564, 'title': 'Excel functions and operations', 'summary': 'Covers various excel functions and operations such as square, power, mod, count, counta, count blank, and, or, is text, and length functions, providing examples and explanations for each function and its usage.', 'duration': 431.145, 'highlights': ['The chapter covers various Excel functions and operations such as square, power, mod, count, countA, count blank, and, or, is text, and length functions The chapter delves into a variety of Excel functions and operations, including square, power, mod, count, countA, count blank, and, or, is text, and length functions.', 'The result of 4 raised to the power of 4 is 256 The result of 4 raised to the power of 4 is 256, demonstrating the power function in Excel.', 'The mod function returns the remainder when a number is divided by a divisor The mod function returns the remainder when a number is divided by a divisor, demonstrated through examples of dividing numbers and obtaining the remainder.', 'The countA function counts the number of cells in a range that are not empty The countA function counts the number of cells in a range that are not empty, showcasing how it handles empty cells and returns the count of non-empty cells within the specified range.', 'The and function checks if all the conditions or arguments are true and returns true if they are The and function checks if all the conditions or arguments are true and returns true if they are, illustrated through logical conditions and their evaluation.']}], 'duration': 1485.589, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw7831120.jpg', 'highlights': ['The process involves specifying filtering criteria for list price and bedrooms, resulting in a dataset with specific requirements. 300,000, 3 bedrooms, 4 bedrooms', "The method involves navigating to the data tab, selecting 'advanced', and then copying the filtered data to another location for further analysis. 126 rows, columns A to J, range M1 to V3", 'Upon execution, the result matches the specified criteria, displaying data where bedrooms are 3 or 4 and the list price exceeds $300,000. Matching dataset', 'The chapter covers the basics of formulas and functions in Excel, including examples of addition, division, sum, average, and SUMPRODUCT, demonstrating the ability to perform basic calculations and complex operations, with an emphasis on practical application.', 'The chapter explains the distinction between formulas and functions, stating that technically, a formula is an expression that begins with an equal sign, while functions are used to perform complex calculations that cannot be done manually. It distinguishes between formulas and functions, stating that a formula begins with an equal sign, while functions are used for complex calculations.', 'The transcript provides practical demonstrations of using functions like sum and average to calculate totals and averages of values in a range of cells, exemplifying their application in real-world scenarios.', 'The speaker demonstrates the application of the SUMPRODUCT function to find the total sales value by multiplying corresponding quantity and price per unit arrays, providing a practical illustration of complex calculation using a built-in Excel function.', "The isEven function returns true if the given number is an even value, and false if it's not, with 4 returning true and 13 returning false, showing the application and results of the function. isEven function, 4 returning true, 13 returning false", "The isOdd function returns true if the given number is odd and false if it's not, with 4 returning false and 13 returning true, showcasing the application and results of the function. isOdd function, 4 returning false, 13 returning true", "The isLogical function returns true if the given value is a logical value, demonstrated by identifying true and false as logical values, providing practical examples of the function's usage. isLogical function, identifying true and false as logical values", 'The isNumber function identifies if the given value is a number, returning true for numerical values and false for text values, exemplifying its application with numeric and text values. isNumber function, returning true for numbers, false for text values', 'Demonstrates the application of round, round down, and round up functions to manipulate decimal numbers, providing examples of rounding to specific decimal places and the resulting rounded values. Application of round, round down, and round up functions, rounding to specific decimal places', 'The chapter covers various Excel functions and operations such as square, power, mod, count, countA, count blank, and, or, is text, and length functions The chapter delves into a variety of Excel functions and operations, including square, power, mod, count, countA, count blank, and, or, is text, and length functions.', 'The result of 4 raised to the power of 4 is 256 The result of 4 raised to the power of 4 is 256, demonstrating the power function in Excel.', 'The mod function returns the remainder when a number is divided by a divisor The mod function returns the remainder when a number is divided by a divisor, demonstrated through examples of dividing numbers and obtaining the remainder.', 'The countA function counts the number of cells in a range that are not empty The countA function counts the number of cells in a range that are not empty, showcasing how it handles empty cells and returns the count of non-empty cells within the specified range.', 'The and function checks if all the conditions or arguments are true and returns true if they are The and function checks if all the conditions or arguments are true and returns true if they are, illustrated through logical conditions and their evaluation.']}, {'end': 10647.393, 'segs': [{'end': 9512.308, 'src': 'embed', 'start': 9485.289, 'weight': 0, 'content': [{'end': 9488.871, 'text': 'now, moving ahead, the next function we have is SUBSTITUTE.', 'start': 9485.289, 'duration': 3.582}, {'end': 9494.515, 'text': 'The substitute function replaces existing text with new text in a text string.', 'start': 9489.631, 'duration': 4.884}, {'end': 9502.541, 'text': "For example, if I use the substitute function, I'll give my old text, which has I work at Simply Learn, Give a comma.", 'start': 9494.515, 'duration': 8.026}, {'end': 9509.466, 'text': "Now I'll select, let's say I work which is from my old text and I want to replace it with.", 'start': 9502.541, 'duration': 6.925}, {'end': 9511.547, 'text': 'He works.', 'start': 9509.466, 'duration': 2.081}, {'end': 9512.308, 'text': 'Close the bracket.', 'start': 9511.547, 'duration': 0.761}], 'summary': "The substitute function replaces text in a string. example: 'i work at simply learn' becomes 'he works'.", 'duration': 27.019, 'max_score': 9485.289, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9485289.jpg'}, {'end': 9577.035, 'src': 'embed', 'start': 9544.173, 'weight': 1, 'content': [{'end': 9544.893, 'text': 'you can see it here.', 'start': 9544.173, 'duration': 0.72}, {'end': 9547.714, 'text': 'concatenate means combining strings.', 'start': 9544.893, 'duration': 2.821}, {'end': 9552.915, 'text': 'so a concatenate function joins or merges several text strings into one text string.', 'start': 9547.714, 'duration': 5.201}, {'end': 9558.79, 'text': 'here I have two strings hello and in another cell i have world.', 'start': 9552.915, 'duration': 5.875}, {'end': 9562.431, 'text': 'i want to concatenate these two strings.', 'start': 9558.79, 'duration': 3.641}, {'end': 9565.972, 'text': "so i'll use the concatenate function.", 'start': 9562.431, 'duration': 3.541}, {'end': 9570.813, 'text': "i'll select cell a25, give a comma.", 'start': 9565.972, 'duration': 4.841}, {'end': 9577.035, 'text': "now i'll give two inverted commas with a space, give another comma and select cell b25 that has world.", 'start': 9570.813, 'duration': 6.222}], 'summary': "Concatenate function combines 'hello' and 'world' into 'hello world'.", 'duration': 32.862, 'max_score': 9544.173, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9544173.jpg'}, {'end': 9646.226, 'src': 'embed', 'start': 9620.649, 'weight': 2, 'content': [{'end': 9625.672, 'text': 'now repeat function or rept function repeats text a given number of times.', 'start': 9620.649, 'duration': 5.023}, {'end': 9628.735, 'text': 'you can fill a cell with a number of instances of a text string.', 'start': 9625.672, 'duration': 3.063}, {'end': 9630.596, 'text': 'now we have some data here.', 'start': 9628.735, 'duration': 1.861}, {'end': 9636.56, 'text': "so here let's say these are the age groups of women and there was a survey.", 'start': 9630.596, 'duration': 5.964}, {'end': 9640.542, 'text': 'these are the number of respondents we got from each age group.', 'start': 9636.56, 'duration': 3.982}, {'end': 9642.424, 'text': 'i want to create an histogram.', 'start': 9640.542, 'duration': 1.882}, {'end': 9646.226, 'text': 'i can use the repeat function or rept function.', 'start': 9642.424, 'duration': 3.802}], 'summary': 'The repeat function can be used to create a histogram based on the number of respondents in different age groups.', 'duration': 25.577, 'max_score': 9620.649, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9620649.jpg'}, {'end': 9722.888, 'src': 'embed', 'start': 9696.894, 'weight': 3, 'content': [{'end': 9701.076, 'text': 'Now the type function returns an integer representing the data type of a value.', 'start': 9696.894, 'duration': 4.182}, {'end': 9709.28, 'text': "Now here on the right you can see, suppose the type function returns 1, it means the value is a number or it's a blank.", 'start': 9701.316, 'duration': 7.964}, {'end': 9713.862, 'text': "Let's say the return type is 2, it means the data type is a text.", 'start': 9709.58, 'duration': 4.282}, {'end': 9718.825, 'text': "If it's 4, it means the data type is a logical value that is either true or false.", 'start': 9714.463, 'duration': 4.362}, {'end': 9722.888, 'text': "Now if it's an error, it will return the value 16.", 'start': 9719.565, 'duration': 3.323}], 'summary': 'The type function returns integer codes for different data types: 1 for number/blank, 2 for text, 4 for logical value, and 16 for error.', 'duration': 25.994, 'max_score': 9696.894, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9696894.jpg'}, {'end': 9874.086, 'src': 'embed', 'start': 9820.214, 'weight': 4, 'content': [{'end': 9825.775, 'text': 'Now, there is a function called isBlank, which checks if a cell is empty or blank.', 'start': 9820.214, 'duration': 5.561}, {'end': 9829.696, 'text': 'If it is empty, it will return true, else it will return false.', 'start': 9826.555, 'duration': 3.141}, {'end': 9835.517, 'text': 'Now, if you were to check if this cell is empty or not.', 'start': 9830.996, 'duration': 4.521}, {'end': 9838.518, 'text': 'if I hit enter, it returns me.', 'start': 9835.517, 'duration': 3.001}, {'end': 9844.459, 'text': 'true, because you can see it here cell A47 is empty and there is no value in it.', 'start': 9838.518, 'duration': 5.941}, {'end': 9855.289, 'text': 'Now, if I want to find out if cell A48 is blank or not, if I hit enter, it returns me false because there is a value 40 in it.', 'start': 9845.663, 'duration': 9.626}, {'end': 9860.271, 'text': "now we'll see how to use trim function.", 'start': 9856.667, 'duration': 3.604}, {'end': 9865.717, 'text': "so the trim function removes all the unnecessary spaces from a string, but it won't remove the sink,", 'start': 9860.271, 'duration': 5.446}, {'end': 9870.923, 'text': 'all the unnecessary spaces except the space between angela and stephens.', 'start': 9865.717, 'duration': 5.206}, {'end': 9874.086, 'text': "i'll copy paste this value to the remaining cells.", 'start': 9870.923, 'duration': 3.163}], 'summary': 'Isblank function checks cell emptiness. trim removes spaces, preserves name space.', 'duration': 53.872, 'max_score': 9820.214, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9820214.jpg'}, {'end': 9929.068, 'src': 'embed', 'start': 9897.913, 'weight': 6, 'content': [{'end': 9900.035, 'text': 'next we have the find function.', 'start': 9897.913, 'duration': 2.122}, {'end': 9904.378, 'text': 'so the find function returns the starting position of one string within another string.', 'start': 9900.035, 'duration': 4.343}, {'end': 9908.962, 'text': 'now you should notice that find function is case sensitive.', 'start': 9904.378, 'duration': 4.584}, {'end': 9912.644, 'text': "so let's use the find function here.", 'start': 9908.962, 'duration': 3.682}, {'end': 9914.366, 'text': "i'll write find.", 'start': 9912.644, 'duration': 1.722}, {'end': 9921.931, 'text': 'I want to find where capital S is located in the cell A52 that has Angela Stephens.', 'start': 9915.146, 'duration': 6.785}, {'end': 9925.033, 'text': "I'll give a comma, select A52, hit enter.", 'start': 9922.171, 'duration': 2.862}, {'end': 9929.068, 'text': 'It returns me the starting position that is 11.', 'start': 9925.353, 'duration': 3.715}], 'summary': "The find function returns the starting position of 's' in 'angela stephens' as 11.", 'duration': 31.155, 'max_score': 9897.913, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9897913.jpg'}, {'end': 10037.226, 'src': 'embed', 'start': 10011.448, 'weight': 8, 'content': [{'end': 10020.354, 'text': 'So here also, since we searched for capital A, but it looked for any A that was present in Antonio Maino, be it small or capital.', 'start': 10011.448, 'duration': 8.906}, {'end': 10023.777, 'text': 'Finally, we have a clean function.', 'start': 10021.075, 'duration': 2.702}, {'end': 10028.18, 'text': 'So the clean function returns all non-printable characters from the text.', 'start': 10023.797, 'duration': 4.383}, {'end': 10031.942, 'text': 'Now you can see here, this cell has three lines of text.', 'start': 10028.82, 'duration': 3.122}, {'end': 10037.226, 'text': 'If you want to print this cell in one line, I can use the clean function.', 'start': 10032.663, 'duration': 4.563}], 'summary': 'The clean function removes all non-printable characters from the text.', 'duration': 25.778, 'max_score': 10011.448, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw10011448.jpg'}, {'end': 10094.285, 'src': 'embed', 'start': 10063.923, 'weight': 9, 'content': [{'end': 10065.163, 'text': "let's see them one by one.", 'start': 10063.923, 'duration': 1.24}, {'end': 10066.824, 'text': 'we have a function called upper.', 'start': 10065.163, 'duration': 1.661}, {'end': 10070.787, 'text': 'so the upper function converts any text string into upper case.', 'start': 10066.824, 'duration': 3.963}, {'end': 10073.108, 'text': 'so we have a string called india.', 'start': 10070.787, 'duration': 2.321}, {'end': 10077.631, 'text': "now, if i want to convert india into upper case, i'll use the upper function.", 'start': 10073.108, 'duration': 4.523}, {'end': 10078.831, 'text': 'select that cell.', 'start': 10077.631, 'duration': 1.2}, {'end': 10079.772, 'text': 'you can see it here.', 'start': 10078.831, 'duration': 0.941}, {'end': 10083.374, 'text': 'it has converted india into uppercase characters.', 'start': 10079.772, 'duration': 3.602}, {'end': 10087.757, 'text': 'similarly, you can use the lower function to convert any text string into lower case.', 'start': 10083.374, 'duration': 4.383}, {'end': 10091.264, 'text': "I'll use the lower function.", 'start': 10088.843, 'duration': 2.421}, {'end': 10092.604, 'text': 'select that string.', 'start': 10091.264, 'duration': 1.34}, {'end': 10094.285, 'text': 'enter. there you go.', 'start': 10092.604, 'duration': 1.681}], 'summary': "Demonstrating upper and lower functions; converting 'india' to uppercase and lowercase.", 'duration': 30.362, 'max_score': 10063.923, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw10063923.jpg'}, {'end': 10138.339, 'src': 'embed', 'start': 10107.99, 'weight': 10, 'content': [{'end': 10110.871, 'text': "I'll give equal to, search for VLOOKUP.", 'start': 10107.99, 'duration': 2.881}, {'end': 10115.933, 'text': 'Now, my lookup value will be Stuart, comma.', 'start': 10111.911, 'duration': 4.022}, {'end': 10119.043, 'text': "Then I'll select my table array.", 'start': 10117.162, 'duration': 1.881}, {'end': 10124.828, 'text': "So I'll take my table array from A2 to C7.", 'start': 10119.984, 'duration': 4.844}, {'end': 10127.87, 'text': 'If you want, you can choose the entire table also.', 'start': 10125.869, 'duration': 2.001}, {'end': 10129.452, 'text': 'Give a comma.', 'start': 10128.831, 'duration': 0.621}, {'end': 10134.796, 'text': 'My column index number is this department column, which is the third column.', 'start': 10130.092, 'duration': 4.704}, {'end': 10138.339, 'text': 'And range lookup is either true or false.', 'start': 10136.037, 'duration': 2.302}], 'summary': "Using vlookup to search for 'stuart' in a table from a2 to c7, with a range lookup of either true or false.", 'duration': 30.349, 'max_score': 10107.99, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw10107990.jpg'}, {'end': 10381.432, 'src': 'embed', 'start': 10351.134, 'weight': 11, 'content': [{'end': 10352.735, 'text': 'hit enter.', 'start': 10351.134, 'duration': 1.601}, {'end': 10354.015, 'text': 'it has returned sales.', 'start': 10352.735, 'duration': 1.28}, {'end': 10357.976, 'text': 'so davis is from sales department, moving ahead.', 'start': 10354.015, 'duration': 3.961}, {'end': 10361.697, 'text': "now let's explore the if function.", 'start': 10357.976, 'duration': 3.721}, {'end': 10366.259, 'text': 'so the if function in excel checks whether a condition is satisfied or met.', 'start': 10361.697, 'duration': 4.562}, {'end': 10372.02, 'text': 'it returns a value, if true, else it will return another value if the condition is not satisfied.', 'start': 10366.259, 'duration': 5.761}, {'end': 10374.641, 'text': 'so let me use an if function here.', 'start': 10372.02, 'duration': 2.621}, {'end': 10381.432, 'text': "I'll give my logical test, as say the value in cell O11.", 'start': 10375.99, 'duration': 5.442}], 'summary': 'Davis from sales department achieved sales, and an if function in excel was explored.', 'duration': 30.298, 'max_score': 10351.134, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw10351134.jpg'}], 'start': 9318.13, 'title': 'Excel text and functions', 'summary': 'Covers various excel functions including text, replace, concatenate, and logical operations, with examples and outcomes.', 'chapters': [{'end': 9485.289, 'start': 9318.13, 'title': 'Excel text and replace functions', 'summary': 'Covers the usage of text and replace functions in excel, including the length function returning 28 for counting spaces, the text function converting date values into text in various formats, and the replace function replacing part of a text string within a different text string.', 'duration': 167.159, 'highlights': ['The length function returns 28 for counting spaces. The length function returns a count of 28 for a cell, including spaces.', 'The text function converts date values into text in various formats. The text function converts date values into text in specified number formats, such as DDMM yy and dd, triple m, then 4y.', "The replace function replaces part of a text string within a different text string. The replace function is demonstrated by replacing 'a' with 'b' in A101, replacing 'A' with 'A2' in A102, and replacing 'Ad' with 'SA' in 'Adam'."]}, {'end': 9819.754, 'start': 9485.289, 'title': 'Excel text and function functions', 'summary': 'Covers the substitute, concatenate, rept, and type functions in excel. the substitute function replaces text, concatenate combines strings, rept repeats text, and type returns the data type of a value.', 'duration': 334.465, 'highlights': ["The SUBSTITUTE function replaces existing text with new text in a text string. The SUBSTITUTE function allows replacing specific text within a string with new text, demonstrated by replacing 'I work' with 'He works' and replacing '2010' with '2016' in the given examples.", "The CONCATENATE function joins or merges several text strings into one text string. The CONCATENATE function combines multiple strings into one, as shown by merging 'hello' and 'world' to form 'hello, world' and 'Excel' and 'is fun to learn' to form 'Excel is fun to learn'.", 'The REPT function repeats text a given number of times. The REPT function repeats a specific text multiple times, demonstrated by repeating a special character and creating a histogram-like appearance.', 'The TYPE function returns an integer representing the data type of a value. The TYPE function determines the data type of a value, returning different integers for numbers, text, errors, and arrays, as showcased with various examples.']}, {'end': 10063.923, 'start': 9820.214, 'title': 'Excel functions: isblank, trim, find, search, clean', 'summary': 'Covers the functions isblank, trim, find, search, and clean in excel, including examples and outcomes, with isblank returning true for empty cells and false for non-empty cells, trim removing unnecessary spaces, find returning the starting position of a string within another string, search being case insensitive, and clean removing non-printable characters from text.', 'duration': 243.709, 'highlights': ['The isBlank function checks if a cell is empty or blank, returning true for empty cells and false for non-empty cells. The isBlank function accurately identifies empty or non-empty cells, providing a clear distinction between the two types.', 'The trim function removes unnecessary spaces from a string, while retaining the space between specific words, exemplified by removing at least six spaces from the original string. An example demonstrates the effectiveness of the trim function in removing unnecessary spaces, showing a reduction of at least six spaces from the original string.', 'The find function returns the starting position of one string within another string and is case sensitive, as demonstrated by finding the starting position of specific characters within a given string. The find function accurately determines the starting position of a specified character within a string, illustrating its case sensitivity and functionality.', "The search function is case insensitive, as shown by the search for 'A' within a string returning the position of both capital and lowercase 'A'. The search function showcases its case insensitivity by returning the position of both capital and lowercase occurrences of the specified character within a given string.", 'The clean function removes all non-printable characters from text, demonstrated by condensing a cell with multiple lines of text into a single line. The clean function effectively condenses text by removing all non-printable characters, showcasing its ability to streamline and format text.']}, {'end': 10647.393, 'start': 10063.923, 'title': 'Excel functions for text and logical operations', 'summary': 'Covers functions like vlookup, hlookup, if, and, ifna, and iferror, demonstrating how to convert text cases, use lookup functions to find specific information, and apply logical operations to return specific values.', 'duration': 583.47, 'highlights': ["The upper function converts any text string into uppercase characters, as demonstrated by converting the string 'India' to uppercase. The upper function is used to convert text to uppercase, as shown by converting the string 'India' to uppercase.", "The VLOOKUP function is used to find a specific value in a table, demonstrated by finding the department of a person using VLOOKUP and returning 'marketing.' VLOOKUP is used to find a specific value in a table, as demonstrated by finding the department of a person and returning 'marketing.'", "The IF function is used to check whether a condition is satisfied, as demonstrated by returning 'yes' or 'no' based on whether a value is greater than 5. The IF function is used to check whether a condition is satisfied and return specific values based on the result."]}], 'duration': 1329.263, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw9318130.jpg', 'highlights': ['The SUBSTITUTE function replaces existing text with new text in a text string.', 'The CONCATENATE function joins or merges several text strings into one text string.', 'The REPT function repeats text a given number of times.', 'The TYPE function returns an integer representing the data type of a value.', 'The isBlank function checks if a cell is empty or blank, returning true for empty cells and false for non-empty cells.', 'The trim function removes unnecessary spaces from a string, while retaining the space between specific words.', 'The find function returns the starting position of one string within another string and is case sensitive.', "The search function is case insensitive, as shown by the search for 'A' within a string returning the position of both capital and lowercase 'A'.", 'The clean function removes all non-printable characters from text.', 'The upper function converts any text string into uppercase characters.', 'The VLOOKUP function is used to find a specific value in a table.', 'The IF function is used to check whether a condition is satisfied.']}, {'end': 12344.943, 'segs': [{'end': 10840.185, 'src': 'embed', 'start': 10813.143, 'weight': 0, 'content': [{'end': 10818.648, 'text': "Similarly, let's find the city to which the person with the last name as Evans belongs to.", 'start': 10813.143, 'duration': 5.505}, {'end': 10822.769, 'text': 'So evens, you can see from the table, belongs to Los Angeles.', 'start': 10819.426, 'duration': 3.343}, {'end': 10825.331, 'text': "Let's find out using our index match function.", 'start': 10822.969, 'duration': 2.362}, {'end': 10840.185, 'text': "I'll write index and then array I'll give as ac1 to ac7, my ronum I'll give my match function.", 'start': 10826.252, 'duration': 13.933}], 'summary': 'Using the index match function, it was found that the person with the last name evans belongs to los angeles.', 'duration': 27.042, 'max_score': 10813.143, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw10813143.jpg'}, {'end': 11049.724, 'src': 'embed', 'start': 11019.256, 'weight': 1, 'content': [{'end': 11023.92, 'text': 'So in this column, that is the G column, Afghanistan is present 146 times.', 'start': 11019.256, 'duration': 4.664}, {'end': 11029.771, 'text': "similarly, let's count the number of times india is present in the data set.", 'start': 11025.623, 'duration': 4.148}, {'end': 11031.774, 'text': "so i'll use the county function again.", 'start': 11029.771, 'duration': 2.003}, {'end': 11034.499, 'text': "i'll select the same range.", 'start': 11031.774, 'duration': 2.725}, {'end': 11035.822, 'text': 'we give a comma now.', 'start': 11034.499, 'duration': 1.323}, {'end': 11037.605, 'text': 'my criteria will be india.', 'start': 11035.822, 'duration': 1.783}, {'end': 11040.842, 'text': 'You can see here India is present 155 times.', 'start': 11037.981, 'duration': 2.861}, {'end': 11044.563, 'text': "Moving on, the next function we'll talk about is COUNTIFS.", 'start': 11041.282, 'duration': 3.281}, {'end': 11049.724, 'text': 'So COUNTIFS function counts the number of cells specified in a given set of conditions or criteria.', 'start': 11044.823, 'duration': 4.901}], 'summary': 'Afghanistan appears 146 times, india appears 155 times in the data set. countifs counts cells based on conditions or criteria.', 'duration': 30.468, 'max_score': 11019.256, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw11019256.jpg'}, {'end': 11222.26, 'src': 'embed', 'start': 11190.505, 'weight': 2, 'content': [{'end': 11191.785, 'text': 'so we have a question here.', 'start': 11190.505, 'duration': 1.28}, {'end': 11199.027, 'text': 'we want to find the total cases in france on those days where the deaths have been less than 100.', 'start': 11191.785, 'duration': 7.242}, {'end': 11203.568, 'text': 'so this should be those days instead of whose.', 'start': 11199.027, 'duration': 4.541}, {'end': 11208.11, 'text': "okay. so let's see our SUMIFS function now.", 'start': 11203.568, 'duration': 4.542}, {'end': 11213.811, 'text': 'first we need to give the sum range, since we want to find the total cases.', 'start': 11208.11, 'duration': 5.701}, {'end': 11218.539, 'text': "so we'll give the sum range as the column E comma.", 'start': 11213.811, 'duration': 4.728}, {'end': 11222.26, 'text': 'now the second criteria is the countries column.', 'start': 11218.539, 'duration': 3.721}], 'summary': 'Find total cases in france on days with less than 100 deaths.', 'duration': 31.755, 'max_score': 11190.505, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw11190505.jpg'}, {'end': 11438.933, 'src': 'embed', 'start': 11406.341, 'weight': 5, 'content': [{'end': 11419.355, 'text': 'So I will write DATEDIF and in that I will give my first date value as the date of birth, which is 30 December 1994, comma.', 'start': 11406.341, 'duration': 13.014}, {'end': 11424.2, 'text': "my second date value will be today's date, which is 28th of july.", 'start': 11419.355, 'duration': 4.845}, {'end': 11427.202, 'text': 'now i want to find the age in terms of years.', 'start': 11424.2, 'duration': 3.002}, {'end': 11429.765, 'text': "so within double quotes i'll give y.", 'start': 11427.202, 'duration': 2.563}, {'end': 11432.988, 'text': 'i hit enter, it gives me 25.', 'start': 11429.765, 'duration': 3.223}, {'end': 11438.933, 'text': 'so it has subtracted our first date value and the second date value to return the year.', 'start': 11432.988, 'duration': 5.945}], 'summary': 'Using datedif function to calculate age, result: 25 years.', 'duration': 32.592, 'max_score': 11406.341, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw11406341.jpg'}, {'end': 11498.624, 'src': 'embed', 'start': 11461.309, 'weight': 4, 'content': [{'end': 11467.193, 'text': "now the end date will be today's day comma basis.", 'start': 11461.309, 'duration': 5.884}, {'end': 11475.868, 'text': "i'll give one, which is actually it will divide the actual number of days by the actual days in a year.", 'start': 11467.193, 'duration': 8.675}, {'end': 11483.753, 'text': 'right enter, it gives me my year fraction, which is 25.5763.', 'start': 11475.868, 'duration': 7.885}, {'end': 11489.698, 'text': "now let's say I'll replace 1 with, let's say, 2.", 'start': 11483.753, 'duration': 5.945}, {'end': 11493.56, 'text': 'right enter, it gives me 25.95.', 'start': 11489.698, 'duration': 3.862}, {'end': 11494.621, 'text': 'you can see it here.', 'start': 11493.56, 'duration': 1.061}, {'end': 11498.624, 'text': 'if you give 2, it will divide the actual days by 360.', 'start': 11494.621, 'duration': 4.003}], 'summary': 'Calculating year fraction using actual days and 360 days.', 'duration': 37.315, 'max_score': 11461.309, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw11461309.jpg'}, {'end': 11840.39, 'src': 'embed', 'start': 11801.993, 'weight': 3, 'content': [{'end': 11810.075, 'text': "So first let's use our networkDays function to find the number of working days between two date values.", 'start': 11801.993, 'duration': 8.082}, {'end': 11824.313, 'text': "So I'll write networkDays and let's say I'll give my first date or the starting date as 1st of July, 2020.", 'start': 11810.895, 'duration': 13.418}, {'end': 11830.297, 'text': "and i'll give my second date value as 31st of july 2020.", 'start': 11824.313, 'duration': 5.984}, {'end': 11833.619, 'text': 'close the bracket hit.', 'start': 11830.297, 'duration': 3.322}, {'end': 11836.087, 'text': 'enter, it gives me 23.', 'start': 11833.619, 'duration': 2.468}, {'end': 11838.489, 'text': 'now let me show you the calendar now.', 'start': 11836.087, 'duration': 2.402}, {'end': 11840.39, 'text': 'this is the july calendar.', 'start': 11838.489, 'duration': 1.901}], 'summary': 'Using networkdays function, 23 working days between 1st-31st july 2020.', 'duration': 38.397, 'max_score': 11801.993, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw11801993.jpg'}, {'end': 12025.425, 'src': 'embed', 'start': 12000.595, 'weight': 6, 'content': [{'end': 12007.517, 'text': 'the PMT function can be used to figure out the future payments for a loan, assuming constant payments and a constant interest rate.', 'start': 12000.595, 'duration': 6.922}, {'end': 12011.599, 'text': "so I'll just show you the arguments the PMT function takes.", 'start': 12007.517, 'duration': 4.082}, {'end': 12016.921, 'text': 'you can see it takes rate, NPR or NPER, PV, FP and a type.', 'start': 12011.599, 'duration': 5.322}, {'end': 12019.082, 'text': 'so rate is the interest rate for the loan.', 'start': 12016.921, 'duration': 2.161}, {'end': 12021.723, 'text': 'NPER is the total number of payments for the loan.', 'start': 12019.082, 'duration': 2.641}, {'end': 12025.425, 'text': 'PV is the future value or the total value of all loan payments.', 'start': 12021.723, 'duration': 3.702}], 'summary': 'The pmt function calculates future loan payments using rate, nper, pv, fp, and type as arguments.', 'duration': 24.83, 'max_score': 12000.595, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12000595.jpg'}, {'end': 12208.728, 'src': 'embed', 'start': 12140.787, 'weight': 7, 'content': [{'end': 12147.168, 'text': 'So the IRR function in Excel returns the internal rate of a series of cash flows.', 'start': 12140.787, 'duration': 6.381}, {'end': 12160.053, 'text': "so if I want to find the internal rate of return, I'll use the IRR function and I'll select my values from B16 to B21.", 'start': 12147.168, 'duration': 12.885}, {'end': 12163.534, 'text': 'close the bracket.', 'start': 12160.053, 'duration': 3.481}, {'end': 12167.168, 'text': 'so this is my IRR value, that is, 22%.', 'start': 12163.534, 'duration': 3.634}, {'end': 12169.449, 'text': 'If I want to find the XIRR value,', 'start': 12167.168, 'duration': 2.281}, {'end': 12176.213, 'text': "I'll use the XIRR function that returns the internal rate for a schedule of cash flows that occur in irregular intervals.", 'start': 12169.449, 'duration': 6.764}, {'end': 12181.016, 'text': "Now for using the XIRR function, I'll use this table.", 'start': 12176.754, 'duration': 4.262}, {'end': 12185.819, 'text': 'Now you can see it has irregular cash flows over a period of time.', 'start': 12181.456, 'duration': 4.363}, {'end': 12189.041, 'text': 'So let me use my XIRR function.', 'start': 12187.02, 'duration': 2.021}, {'end': 12201.603, 'text': "I'll give my values ranging from F22 to F27 comma.", 'start': 12191.075, 'duration': 10.528}, {'end': 12206.627, 'text': "the dates I'll give from E22 to E27.", 'start': 12201.603, 'duration': 5.024}, {'end': 12207.487, 'text': 'close the bracket.', 'start': 12206.627, 'duration': 0.86}, {'end': 12208.728, 'text': 'hit enter.', 'start': 12207.487, 'duration': 1.241}], 'summary': 'Irr function in excel calculates 22% rate of return, xirr handles irregular cash flows.', 'duration': 67.941, 'max_score': 12140.787, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12140787.jpg'}, {'end': 12269.915, 'src': 'embed', 'start': 12235.203, 'weight': 9, 'content': [{'end': 12238.565, 'text': 'so we have an annual interest rate of 10 percent years.', 'start': 12235.203, 'duration': 3.362}, {'end': 12242.007, 'text': 'we have 8 and yearly payment we have 1 lakh.', 'start': 12238.565, 'duration': 3.442}, {'end': 12247.03, 'text': "now, to find the future value, i'll use equal to fv rate.", 'start': 12242.007, 'duration': 5.023}, {'end': 12254.854, 'text': "i'll give g4, which is 10 percent comma nper.", 'start': 12247.03, 'duration': 7.824}, {'end': 12264.591, 'text': "i'll give g5, which is the number of years, that is 8, and yearly payment i'll give 1 lakh, which is g6 comma.", 'start': 12254.854, 'duration': 9.737}, {'end': 12265.291, 'text': 'present value.', 'start': 12264.591, 'duration': 0.7}, {'end': 12269.915, 'text': '0 type is also 0, right enter.', 'start': 12265.291, 'duration': 4.624}], 'summary': 'Annual interest rate of 10%, 8 years, 1 lakh yearly payment to find future value.', 'duration': 34.712, 'max_score': 12235.203, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12235203.jpg'}, {'end': 12344.943, 'src': 'embed', 'start': 12320.646, 'weight': 10, 'content': [{'end': 12331.774, 'text': 'So the NPV formula or the function calculates the net present value of an investment based on a discount rate and a series of future payments having negative values and income having positive values.', 'start': 12320.646, 'duration': 11.128}, {'end': 12341.401, 'text': 'So you can see in the table here Our investment has a negative value and our incomes have positive values and we have a discount rate of 10%.', 'start': 12331.774, 'duration': 9.627}, {'end': 12344.943, 'text': "Now let's find the net present value.", 'start': 12341.401, 'duration': 3.542}], 'summary': 'Npv formula calculates net present value of an investment using discount rate and future payments.', 'duration': 24.297, 'max_score': 12320.646, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12320646.jpg'}], 'start': 10647.393, 'title': 'Excel functions and usage', 'summary': 'Covers the usage of index match functions for table lookup, excel functions for data analysis including countif, countifs, sumifs, and averageif with examples from a coronavirus dataset, and excel date and time functions as well as financial functions including pmt, irr, xirr, fv, pv, and npv with practical examples and results.', 'chapters': [{'end': 10943.827, 'start': 10647.393, 'title': 'Using index match functions', 'summary': "Demonstrates the use of index match functions to find specific values in a table, such as finding the city corresponding to a person's name, with examples showing results like 'davis' in the third row and second column, and 'boston' in the fifth row and fourth column.", 'duration': 296.434, 'highlights': ["The index match function is used to find specific values in a table, such as finding 'Davis' in the third row and second column, and 'Boston' in the fifth row and fourth column.", "The match function returns the relative position of an item in an array that matches a specified value, for example, finding 'events' in the fifth row and 'Boston' in the sixth row.", "The index match function can be used to return a value in a column to the left, with an example returning 'Jensen' for the first name and 'Kansas' for the city.", "The demonstration showcases using the index match function to find the city corresponding to a person's last name, returning 'Los Angeles' for 'Evens' and 'Sales' for 'Zampa'."]}, {'end': 11299.392, 'start': 10944.087, 'title': 'Excel functions for data analysis', 'summary': 'Demonstrates the usage of countif, countifs, sumifs, and averageif functions with a coronavirus dataset, revealing 155 instances of india in the dataset, 68 days with more than 100 cases in india, and 20,638 cases in france with deaths less than 100.', 'duration': 355.305, 'highlights': ["India appears 155 times in the dataset The COUNTIF function identifies India's occurrence 155 times in the dataset, providing a quantitative insight into its frequency.", '68 days of India with more than 100 cases The COUNTIFS function accurately determines the number of days where India experienced more than 100 cases, offering a clear quantitative analysis.', '20,638 cases in France with deaths less than 100 The SUMIFS function efficiently calculates the total cases in France on days where the deaths have been less than 100, providing a specific quantitative result.']}, {'end': 11978.81, 'start': 11299.392, 'title': 'Excel date and time functions', 'summary': 'Covers various excel date and time functions including today, now, time, datedif, yearfrac, weekday, day, month, year, networkdays, and workday, highlighting their usage and examples.', 'duration': 679.418, 'highlights': ['The NETWORKDAYS function returned 23 working days in July 2020, excluding weekends, from the 1st to the 31st. The NETWORKDAYS function calculated 23 working days in July 2020, excluding 8 weekends, from the 1st to the 31st.', 'The WORKDAY function determined the next working day after 5 days, excluding weekends, as the 8th of July 2020. The WORKDAY function identified the next working day after 5 days, excluding weekends, as the 8th of July 2020.', 'The YEARFRAC function computed the year fraction between two dates as 25.5763, and as 25.95 when adjusted with a basis of 2. The YEARFRAC function calculated the year fraction as 25.5763 and 25.95 when adjusted with a basis of 2.', 'The DATEDIF function calculated the age as 25 years between the date of birth and the current date. The DATEDIF function determined the age as 25 years between the date of birth and the current date.', 'The NOW function returned the current date and time, and the WEEKDAY function identified the day of the week for a given date. The NOW function provided the current date and time, and the WEEKDAY function determined the day of the week for a given date.']}, {'end': 12344.943, 'start': 11979.31, 'title': 'Financial functions tutorial', 'summary': 'Covers financial functions including pmt, irr, xirr, fv, pv, and npv in excel, demonstrating calculations for future payments, internal rate of return, future value, present value, and net present value, with examples and formulas.', 'duration': 365.633, 'highlights': ['Demonstrates the use of PMT function to calculate future payments for a loan, with an example of finding the PMT amount for a loan of two lakhs at an interest rate of 5% per annum. Loan amount: 2 lakhs, Interest rate: 5%', 'Illustrates the use of IRR function to find the internal rate of return for a series of cash flows, showing the example of calculating IRR value as 22%. IRR value: 22%', 'Shows the application of XIRR function to determine the internal rate for a schedule of cash flows occurring at irregular intervals, with an example of finding the XIRR value. XIRR value', 'Explains the usage of FV function to calculate the future value of an investment with periodic constant payments and a constant interest rate, providing an example with an annual interest rate of 10%, 8 years, and yearly payment of 1 lakh. Interest rate: 10%, Years: 8, Yearly payment: 1 lakh, Future value: 11 lakhs, 43,588', 'Details the application of PV function to determine the present value of an investment, showcasing an example with an interest rate of 8%, 5 years, and a payment per period of 10,000. Interest rate: 8%, Number of periods: 5 years, Payment per period: 10,000', 'Describes the NPV function to calculate the net present value of an investment based on a discount rate and a series of future payments, with an example illustrating the calculation of the net present value. Discount rate: 10%']}], 'duration': 1697.55, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw10647393.jpg', 'highlights': ["The demonstration showcases using the index match function to find the city corresponding to a person's last name, returning 'Los Angeles' for 'Evens' and 'Sales' for 'Zampa'.", "The COUNTIF function identifies India's occurrence 155 times in the dataset, providing a quantitative insight into its frequency.", 'The SUMIFS function efficiently calculates the total cases in France on days where the deaths have been less than 100, providing a specific quantitative result.', 'The NETWORKDAYS function calculated 23 working days in July 2020, excluding 8 weekends, from the 1st to the 31st.', 'The YEARFRAC function calculated the year fraction as 25.5763 and 25.95 when adjusted with a basis of 2.', 'The DATEDIF function determined the age as 25 years between the date of birth and the current date.', 'Demonstrates the use of PMT function to calculate future payments for a loan, with an example of finding the PMT amount for a loan of two lakhs at an interest rate of 5% per annum.', 'Illustrates the use of IRR function to find the internal rate of return for a series of cash flows, showing the example of calculating IRR value as 22%.', 'Shows the application of XIRR function to determine the internal rate for a schedule of cash flows occurring at irregular intervals, with an example of finding the XIRR value.', 'Explains the usage of FV function to calculate the future value of an investment with periodic constant payments and a constant interest rate, providing an example with an annual interest rate of 10%, 8 years, and yearly payment of 1 lakh.', 'Describes the NPV function to calculate the net present value of an investment based on a discount rate and a series of future payments, with an example illustrating the calculation of the net present value.']}, {'end': 14994.075, 'segs': [{'end': 12441.115, 'src': 'embed', 'start': 12409.391, 'weight': 0, 'content': [{'end': 12413.372, 'text': 'So I can just do a control Z to see the previous result.', 'start': 12409.391, 'duration': 3.981}, {'end': 12414.732, 'text': 'Now here.', 'start': 12413.452, 'duration': 1.28}, {'end': 12425.863, 'text': 'I had the values which were greater than 10,000, and the one which we did just now basically highlighted the values which are greater than 12,000..', 'start': 12414.732, 'duration': 11.131}, {'end': 12427.565, 'text': 'So this is one simple example.', 'start': 12425.864, 'duration': 1.701}, {'end': 12429.547, 'text': 'Now we can look at some other examples.', 'start': 12427.645, 'duration': 1.902}, {'end': 12433.77, 'text': 'Say, for example, you want to format cells using three color scale.', 'start': 12430.107, 'duration': 3.663}, {'end': 12441.115, 'text': 'So if you look at the values here, I have a three color scale, mainly in green, yellow and red.', 'start': 12434.29, 'duration': 6.825}], 'summary': 'Demonstrating conditional formatting for values greater than 12,000 and three-color scaling.', 'duration': 31.724, 'max_score': 12409.391, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12409391.jpg'}, {'end': 12647.01, 'src': 'embed', 'start': 12615.474, 'weight': 2, 'content': [{'end': 12625.122, 'text': 'so what we are seeing here is if the revenue generated is greater than average, then that shows in green,', 'start': 12615.474, 'duration': 9.648}, {'end': 12631.276, 'text': "And if the revenue generated is lesser than average, that's shows in orange.", 'start': 12625.891, 'duration': 5.385}, {'end': 12632.957, 'text': 'Now how do we do that.', 'start': 12631.876, 'duration': 1.081}, {'end': 12636.981, 'text': 'So we can basically again manage some rules.', 'start': 12633.518, 'duration': 3.463}, {'end': 12639.123, 'text': 'So I can basically create a new rule.', 'start': 12637.081, 'duration': 2.042}, {'end': 12647.01, 'text': 'Now here I can select one of the options which says format only values that are above or below average.', 'start': 12639.763, 'duration': 7.247}], 'summary': 'Revenue above average in green, below in orange.', 'duration': 31.536, 'max_score': 12615.474, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12615474.jpg'}, {'end': 12878.069, 'src': 'embed', 'start': 12845.554, 'weight': 3, 'content': [{'end': 12846.775, 'text': "and that's it.", 'start': 12845.554, 'duration': 1.221}, {'end': 12852.141, 'text': 'so now you have your values, which are top or bottom 10 values.', 'start': 12846.775, 'duration': 5.366}, {'end': 12860.083, 'text': 'so you are using conditional formatting, where you are basically highlighting your cell values based on different colors,', 'start': 12852.141, 'duration': 7.942}, {'end': 12866.724, 'text': 'and here easy conditional formatting based on different rules helps us to do that now.', 'start': 12860.083, 'duration': 6.641}, {'end': 12878.069, 'text': 'similarly, you can also have the values, which is basically showing you how the values are increasing.', 'start': 12866.724, 'duration': 11.345}], 'summary': 'Using conditional formatting to highlight top or bottom 10 values and visualize increasing values.', 'duration': 32.515, 'max_score': 12845.554, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12845554.jpg'}, {'end': 13166.956, 'src': 'embed', 'start': 13118.683, 'weight': 1, 'content': [{'end': 13121.125, 'text': "And that's how I use conditional formatting.", 'start': 13118.683, 'duration': 2.442}, {'end': 13129.49, 'text': 'So conditional formatting can be very useful if you would want to use icon set, if you want to use your data bars,', 'start': 13121.145, 'duration': 8.345}, {'end': 13138.416, 'text': 'if you would want to highlight particular values, if you would want to color code based on some calculation,', 'start': 13129.49, 'duration': 8.926}, {'end': 13148.563, 'text': 'if you would want to use a three color or a two color scale, or if you would want to just find out values based on some simple calculation.', 'start': 13138.416, 'duration': 10.147}, {'end': 13162.654, 'text': 'So conditional formatting is used extensively by data analysts or people who are working business intelligence teams or people who would want to use Excel to easily identify the data.', 'start': 13149.147, 'duration': 13.507}, {'end': 13166.956, 'text': 'easily identify the cells which contain particular value.', 'start': 13163.894, 'duration': 3.062}], 'summary': 'Conditional formatting in excel is extensively used by data analysts and business intelligence teams to easily identify and highlight specific values and data.', 'duration': 48.273, 'max_score': 13118.683, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw13118683.jpg'}, {'end': 13249.42, 'src': 'embed', 'start': 13194.384, 'weight': 6, 'content': [{'end': 13199.528, 'text': 'so you could limit it to basically a number between a particular value.', 'start': 13194.384, 'duration': 5.144}, {'end': 13211.297, 'text': 'you could also add some messages to it if you would want, or you could even circle invalid data or clear validation circles.', 'start': 13199.528, 'duration': 11.769}, {'end': 13218.142, 'text': 'so data validation really helps us in validating the data which is being fed in two particular fields.', 'start': 13211.297, 'duration': 6.845}, {'end': 13226.083, 'text': "Now it's a feature in Excel which is mainly used to control what a user can fill in a cell.", 'start': 13219.696, 'duration': 6.387}, {'end': 13229.247, 'text': 'You can decide what type of values must be entered.', 'start': 13226.463, 'duration': 2.784}, {'end': 13239.598, 'text': 'You can also restrict user to enter only valid data and if any invalid data is entered, an error message will be displayed.', 'start': 13229.947, 'duration': 9.651}, {'end': 13242.817, 'text': "Now that's where you can use your data validations.", 'start': 13240.236, 'duration': 2.581}, {'end': 13245.478, 'text': "So let's see how that can be done.", 'start': 13243.117, 'duration': 2.361}, {'end': 13249.42, 'text': "So for data validation, let's see some exercises here.", 'start': 13245.758, 'duration': 3.662}], 'summary': 'Data validation in excel limits input values, provides error messages, and controls user input for accurate data entry.', 'duration': 55.036, 'max_score': 13194.384, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw13194384.jpg'}, {'end': 14024.489, 'src': 'embed', 'start': 13979.417, 'weight': 7, 'content': [{'end': 13987.624, 'text': 'A pivot table is a tool that summarizes and reorganizes selected columns and rows of data in its spreadsheet to obtain a desired report.', 'start': 13979.417, 'duration': 8.207}, {'end': 13991.103, 'text': 'It does not actually change the spreadsheet data.', 'start': 13988.962, 'duration': 2.141}, {'end': 13995.285, 'text': 'It simply pivots or turns the data to view it in different perspectives.', 'start': 13991.643, 'duration': 3.642}, {'end': 14001.207, 'text': 'Pivot tables are especially useful with large amounts of data that would be time consuming to calculate manually.', 'start': 13995.785, 'duration': 5.422}, {'end': 14006.089, 'text': "Now, let's understand the different components of a pivot table.", 'start': 14002.468, 'duration': 3.621}, {'end': 14008.39, 'text': 'So there are four main components.', 'start': 14006.909, 'duration': 1.481}, {'end': 14009.931, 'text': 'First, we have rows.', 'start': 14009.03, 'duration': 0.901}, {'end': 14015.053, 'text': 'When a field is chosen for the row area, it populates as the first column in the pivot table.', 'start': 14010.271, 'duration': 4.782}, {'end': 14020.985, 'text': 'Similar to the columns, all row labels are unique values and duplicates are removed.', 'start': 14016.3, 'duration': 4.685}, {'end': 14024.489, 'text': 'Columns is the second component.', 'start': 14022.166, 'duration': 2.323}], 'summary': 'Pivot tables summarize and reorganize data for different perspectives and are especially useful with large amounts of data.', 'duration': 45.072, 'max_score': 13979.417, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw13979417.jpg'}, {'end': 14187.205, 'src': 'embed', 'start': 14158.55, 'weight': 12, 'content': [{'end': 14162.491, 'text': "so using this table, we'll create several pivot tables.", 'start': 14158.55, 'duration': 3.941}, {'end': 14171.64, 'text': "First of all, let's create a pivot table to find the total population for each state and sort it in descending order.", 'start': 14165.029, 'duration': 6.611}, {'end': 14175.642, 'text': 'So you can see, here we have the problem statement.', 'start': 14171.881, 'duration': 3.761}, {'end': 14182.064, 'text': 'so our first pivot table will have the total population for each of the states in descending order.', 'start': 14175.642, 'duration': 6.422}, {'end': 14187.205, 'text': 'so to create a pivot table you can click any cell in your data.', 'start': 14182.064, 'duration': 5.141}], 'summary': 'Creating pivot tables to find total population for each state, sorted in descending order.', 'duration': 28.655, 'max_score': 14158.55, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14158550.jpg'}, {'end': 14294.119, 'src': 'embed', 'start': 14266.527, 'weight': 10, 'content': [{'end': 14273.412, 'text': 'So I right click, go to sort option and choose Z to A, that is largest to smallest.', 'start': 14266.527, 'duration': 6.885}, {'end': 14280.238, 'text': 'You can see here in 2011, Maharashtra had the highest number of population.', 'start': 14274.513, 'duration': 5.725}, {'end': 14288.856, 'text': 'or the total population in Maharashtra was the highest, then it was Uttar Pradesh, we had Andhra Pradesh and, if I come down,', 'start': 14281.132, 'duration': 7.724}, {'end': 14294.119, 'text': 'we have Nagaland and Andaman and Nicobar Islands towards the end.', 'start': 14288.856, 'duration': 5.263}], 'summary': 'In 2011, maharashtra had the highest population, followed by uttar pradesh and andhra pradesh.', 'duration': 27.592, 'max_score': 14266.527, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14266527.jpg'}, {'end': 14352.966, 'src': 'embed', 'start': 14320.686, 'weight': 13, 'content': [{'end': 14326.049, 'text': 'ok, now, here we want to find the total sum of literates.', 'start': 14320.686, 'duration': 5.363}, {'end': 14332.993, 'text': "so what I'll do is first let me drag total literates column to values.", 'start': 14326.049, 'duration': 6.944}, {'end': 14335.594, 'text': 'you have the total sum of literates from all the states.', 'start': 14332.993, 'duration': 2.601}, {'end': 14342.154, 'text': 'Next I want to see the sum of total literates based on states and cities.', 'start': 14337.529, 'duration': 4.625}, {'end': 14349.342, 'text': "So let me first drag state name onto rows and then we'll drag city onto rows.", 'start': 14342.234, 'duration': 7.108}, {'end': 14352.966, 'text': 'You can see here, we have our pivot table ready.', 'start': 14350.503, 'duration': 2.463}], 'summary': 'Total sum of literates from all states and cities found.', 'duration': 32.28, 'max_score': 14320.686, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14320686.jpg'}, {'end': 14418.389, 'src': 'embed', 'start': 14381.303, 'weight': 14, 'content': [{'end': 14383.883, 'text': 'moving on okay.', 'start': 14381.303, 'duration': 2.58}, {'end': 14391.445, 'text': 'so the next thing we want to see is what is the average sex ratio and the child sex ratio for each state?', 'start': 14383.883, 'duration': 7.562}, {'end': 14397.987, 'text': 'with that we also want to find the states that had the highest and lowest sex ratio in 2011.', 'start': 14391.445, 'duration': 6.542}, {'end': 14399.888, 'text': "so let's create a pivot table for this.", 'start': 14397.987, 'duration': 1.901}, {'end': 14405.769, 'text': "i'll click on any cell, go to insert, choose pivot table, click on existing.", 'start': 14399.888, 'duration': 5.881}, {'end': 14408.57, 'text': "i'll select cell q5 and click on.", 'start': 14405.769, 'duration': 2.801}, {'end': 14418.389, 'text': "ok now, since we want the average sex ratio and the child sex ratio, so first i'll drag those columns.", 'start': 14408.57, 'duration': 9.819}], 'summary': 'Analyzing average and child sex ratios by state in 2011.', 'duration': 37.086, 'max_score': 14381.303, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14381303.jpg'}, {'end': 14511.099, 'src': 'embed', 'start': 14479.384, 'weight': 15, 'content': [{'end': 14482.547, 'text': "Up next, let's explore one more feature of pivot table.", 'start': 14479.384, 'duration': 3.163}, {'end': 14487.471, 'text': 'So suppose you want to see the top or bottom few rows of a pivot table.', 'start': 14483.087, 'duration': 4.384}, {'end': 14489.959, 'text': 'you can do that as well.', 'start': 14488.137, 'duration': 1.822}, {'end': 14492.322, 'text': 'so here we have a question at hand.', 'start': 14489.959, 'duration': 2.363}, {'end': 14496.807, 'text': 'we want to find the top three cities with the highest number of female graduates.', 'start': 14492.322, 'duration': 4.485}, {'end': 14501.933, 'text': "so let's see from the entire pivot table how we can filter the top three cities.", 'start': 14496.807, 'duration': 5.126}, {'end': 14511.099, 'text': "so I'll go to insert click on the pivot table option, Go to Existing worksheet, click on Q5 and hit OK.", 'start': 14501.933, 'duration': 9.166}], 'summary': 'Exploring pivot table to find top 3 cities with highest female graduates.', 'duration': 31.715, 'max_score': 14479.384, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14479384.jpg'}, {'end': 14698.094, 'src': 'embed', 'start': 14660.443, 'weight': 16, 'content': [{'end': 14672.427, 'text': "and since we want to slice the table based on state, that is, Rajasthan and Karnataka, I'll choose state name as my slicer field.", 'start': 14660.443, 'duration': 11.984}, {'end': 14676.229, 'text': 'you can see, this is my slicer here.', 'start': 14672.427, 'duration': 3.802}, {'end': 14686.213, 'text': "now you only want the data for Rajasthan and Karnataka, so I'll search for these two.", 'start': 14676.229, 'duration': 9.984}, {'end': 14687.593, 'text': 'so here we have Karnataka.', 'start': 14686.213, 'duration': 1.38}, {'end': 14694.794, 'text': 'so let me select Karnataka first, And I also want for Rajasthan.', 'start': 14687.593, 'duration': 7.201}, {'end': 14698.094, 'text': 'So let me select Rajasthan also.', 'start': 14695.934, 'duration': 2.16}], 'summary': 'Slicing table by state: rajasthan and karnataka selected for data analysis.', 'duration': 37.651, 'max_score': 14660.443, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14660443.jpg'}, {'end': 14759.176, 'src': 'embed', 'start': 14729.439, 'weight': 17, 'content': [{'end': 14737.363, 'text': "for example, we have a question here what's the percentage contribution of male and female literates from each state?", 'start': 14729.439, 'duration': 7.924}, {'end': 14742.486, 'text': 'now we want to see in terms of percentage and not as sum or average.', 'start': 14737.363, 'duration': 5.123}, {'end': 14744.708, 'text': "let's do that.", 'start': 14742.486, 'duration': 2.222}, {'end': 14751.302, 'text': "i'll create my pivot table, click on existing and I'll select an empty cell.", 'start': 14744.708, 'duration': 6.594}, {'end': 14759.176, 'text': 'okay, now here, since we want to find the percentage contribution of male and female literates.', 'start': 14751.302, 'duration': 7.874}], 'summary': 'Analyzing the percentage contribution of male and female literates from each state using pivot table.', 'duration': 29.737, 'max_score': 14729.439, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14729439.jpg'}, {'end': 14940.552, 'src': 'embed', 'start': 14908.939, 'weight': 18, 'content': [{'end': 14920.724, 'text': 'now we have the list of states and their respective cities and to the right of the pivot table you can see the sum of female graduates from each city.', 'start': 14908.939, 'duration': 11.785}, {'end': 14925.726, 'text': "now, first i'll sort this column.", 'start': 14920.724, 'duration': 5.002}, {'end': 14932.528, 'text': "i'll right click, go to sort and click on shortest to largest.", 'start': 14925.726, 'duration': 6.802}, {'end': 14940.552, 'text': 'now we have sorted our female graduates from shortest or smallest to largest.', 'start': 14932.528, 'duration': 8.024}], 'summary': 'Sorted female graduates from shortest to largest in pivot table.', 'duration': 31.613, 'max_score': 14908.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14908939.jpg'}, {'end': 15001.357, 'src': 'embed', 'start': 14973.037, 'weight': 11, 'content': [{'end': 14976.64, 'text': 'so you can see andaman, and nicobar islands has only one city that is port player,', 'start': 14973.037, 'duration': 3.603}, {'end': 14982.792, 'text': 'While the remaining you can find the bottom three cities with the lowest number of female graduates.', 'start': 14977.33, 'duration': 5.462}, {'end': 14984.632, 'text': 'So, Andhra Pradesh had these three.', 'start': 14983.012, 'duration': 1.62}, {'end': 14988.513, 'text': 'In Assam, we had Nagaon, then there was Dibrugarh and Silchar.', 'start': 14984.712, 'duration': 3.801}, {'end': 14994.075, 'text': 'Similarly, if I come down in Haryana, we have Palwal, Kathal and Zind.', 'start': 14989.334, 'duration': 4.741}, {'end': 15001.357, 'text': "If I come further, here you can see for Karnataka, there's Gangavati, there's Ranibenur and there's Kolar.", 'start': 14995.416, 'duration': 5.941}], 'summary': 'Andaman and nicobar islands has only one city, port blair, while the bottom three cities in andhra pradesh, assam, haryana, and karnataka have the lowest number of female graduates.', 'duration': 28.32, 'max_score': 14973.037, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14973037.jpg'}], 'start': 12344.964, 'title': 'Excel data analysis', 'summary': "Covers conditional formatting for data analysis, excel data validation, pivot table analysis, and india's 2011 census data analysis, including visualizing data patterns, restricting cell input, and analyzing population and education data.", 'chapters': [{'end': 13020.893, 'start': 12344.964, 'title': 'Conditional formatting for data analysis', 'summary': 'Discusses the application of conditional formatting in excel for data analysis, including highlighting values based on specific conditions, creating color scales, and identifying top and bottom values, aiding in visualizing and understanding data patterns.', 'duration': 675.929, 'highlights': ['Conditional formatting can be used to highlight values based on specific conditions such as revenue greater than 12,000, aiding in quick identification and analysis. The speaker demonstrates using conditional formatting to highlight values greater than 12,000, allowing for quick identification and analysis of data.', 'Creating a three-color scale using conditional formatting allows for visualizing data patterns based on cell values, aiding in understanding data distribution and trends. The speaker explains the process of creating a three-color scale using conditional formatting, enabling visualization of data patterns based on cell values to understand data distribution and trends.', 'Conditional formatting can be applied to cells based on their values, such as highlighting revenue greater than average in green and lesser than average in orange, aiding in visualizing data distribution and trends. The speaker demonstrates applying conditional formatting to cells based on their values, highlighting revenue greater than average in green and lesser than average in orange, aiding in visualizing data distribution and trends.', 'Identifying top and bottom values using conditional formatting allows for quick visualization of key data points, aiding in understanding data patterns and outliers. The speaker explains how to use conditional formatting to identify top and bottom values, allowing for quick visualization of key data points and aiding in understanding data patterns and outliers.', 'Using conditional formatting to highlight revenue trends over time aids in visualizing and understanding data patterns, facilitating analysis and decision-making. The speaker discusses using conditional formatting to highlight revenue trends over time, facilitating visualization and understanding of data patterns, aiding in analysis and decision-making.']}, {'end': 13639.959, 'start': 13020.893, 'title': 'Excel: conditional formatting and data validation', 'summary': 'Explains the application of conditional formatting, including the use of icon sets and color scales, as well as data validation in excel, demonstrating how to restrict cell input, set message alerts, and define validation rules for specific data types and ranges.', 'duration': 619.066, 'highlights': ['Conditional formatting allows for the use of icon sets and color scales to easily identify and highlight specific cell values based on calculations, with the application being extensively used by data analysts and business intelligence teams. Conditional formatting enables the use of icon sets and color scales to highlight cell values, such as using green icons for values greater than or equal to 67% and red icons for values way below 33%, extensively used by data analysts and business intelligence teams.', 'Data validation in Excel helps control user input, allowing for specific validation rules to be set for different data types and ranges, with the capability to display input messages and error alerts for invalid data entries. Data validation in Excel controls user input, displaying input messages and error alerts for invalid data entries, and allows specific validation rules to be set for different data types and ranges.']}, {'end': 14046.922, 'start': 13639.959, 'title': 'Excel data validation and pivot tables', 'summary': 'Covers data validation in excel, demonstrating how to restrict values in a cell using whole numbers, decimals, and lists, and explains the components and functionality of pivot tables.', 'duration': 406.963, 'highlights': ['Excel pivot tables summarize and reorganize data, providing a tool for creating tabular reports. Pivot tables are a powerful tool for summarizing and reorganizing data in Excel for creating tabular reports.', 'Pivot tables consist of four main components: rows, columns, values, and filters. The four main components of pivot tables are rows, columns, values, and filters, each serving specific functions in organizing and summarizing data.', 'Data validation in Excel allows restricting values in a cell using whole numbers, decimals, and lists. The chapter demonstrates data validation methods in Excel, including restricting values in a cell using whole numbers, decimals, and lists.']}, {'end': 14294.119, 'start': 14047.643, 'title': "Analyzing india's 2011 census data in excel", 'summary': "Focuses on analyzing india's 2011 census data using microsoft excel, exploring pivot tables to find total population for each state, with maharashtra having the highest population and andaman and nicobar islands having the lowest.", 'duration': 246.476, 'highlights': ['Maharashtra had the highest total population in 2011 census data The total population in Maharashtra was the highest according to the 2011 census data.', 'Andaman and Nicobar Islands had the lowest total population in 2011 census data Andaman and Nicobar Islands had the lowest total population according to the 2011 census data.', 'Exploring pivot tables in Excel to find total population for each state The chapter demonstrates using pivot tables in Excel to find the total population for each state and sorting it in descending order.']}, {'end': 14994.075, 'start': 14294.119, 'title': 'Pivot table analysis and data visualization', 'summary': 'Explains how to create and utilize pivot tables to analyze data, including finding the total sum of literates, average sex ratio, top cities with the highest number of female graduates, using slicers to filter data, calculating percentage contribution of male and female literates for each state, and finding the bottom three cities with the lowest female graduates.', 'duration': 699.956, 'highlights': ['Creating Pivot Table for Total Sum of Literates The process of creating a pivot table to find the total sum of literates from all the states, followed by sorting the data based on states and cities.', 'Analyzing Average Sex Ratio and Child Sex Ratio Utilizing pivot tables to analyze the average sex ratio and child sex ratio for each state, along with identifying the states with the highest and lowest sex ratios in 2011.', 'Filtering Top Three Cities with Highest Female Graduates Demonstrating how to filter and display the top three cities with the highest number of female graduates using a pivot table.', 'Utilizing Slicer to Filter Data Explanation of how to use a slicer to filter the pivot table data for specific states, such as Rajasthan and Karnataka.', 'Calculating Percentage Contribution of Male and Female Literates Demonstrating the process of calculating the percentage contribution of male and female literates for each state using pivot tables.', 'Identifying Bottom Three Cities with Lowest Female Graduates The process of utilizing pivot tables to identify and display the bottom three cities with the lowest number of female graduates from each state.']}], 'duration': 2649.111, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw12344964.jpg', 'highlights': ['Conditional formatting aids in quick identification and analysis of values greater than 12,000.', 'Creating a three-color scale using conditional formatting enables visualization of data patterns.', 'Applying conditional formatting to highlight revenue greater than average in green aids in visualizing data distribution.', 'Using conditional formatting to identify top and bottom values allows for quick visualization of key data points.', 'Conditional formatting to highlight revenue trends over time facilitates analysis and decision-making.', 'Conditional formatting enables the use of icon sets and color scales to highlight specific cell values based on calculations.', 'Data validation in Excel controls user input, displaying input messages and error alerts for invalid data entries.', 'Pivot tables summarize and reorganize data, providing a tool for creating tabular reports.', 'Pivot tables consist of four main components: rows, columns, values, and filters.', 'Data validation in Excel allows restricting values in a cell using whole numbers, decimals, and lists.', 'Maharashtra had the highest total population in 2011 census data.', 'Andaman and Nicobar Islands had the lowest total population in 2011 census data.', 'Using pivot tables in Excel to find the total population for each state and sorting it in descending order.', 'Creating Pivot Table for Total Sum of Literates and sorting the data based on states and cities.', 'Utilizing pivot tables to analyze the average sex ratio and child sex ratio for each state.', 'Filtering and displaying the top three cities with the highest number of female graduates using a pivot table.', 'Using a slicer to filter the pivot table data for specific states, such as Rajasthan and Karnataka.', 'Calculating the percentage contribution of male and female literates for each state using pivot tables.', 'Utilizing pivot tables to identify and display the bottom three cities with the lowest number of female graduates from each state.']}, {'end': 17188.086, 'segs': [{'end': 15231.748, 'src': 'embed', 'start': 15204.228, 'weight': 0, 'content': [{'end': 15208.211, 'text': 'Excel gives me some default charts which you can use.', 'start': 15204.228, 'duration': 3.983}, {'end': 15211.373, 'text': "Let's say I'll select this.", 'start': 15209.332, 'duration': 2.041}, {'end': 15215.736, 'text': 'Let me drag it a bit to the right.', 'start': 15213.274, 'duration': 2.462}, {'end': 15219.518, 'text': "Here you can see I'll close this pivot field list.", 'start': 15216.857, 'duration': 2.661}, {'end': 15223.622, 'text': 'This is a nice bar chart that Excel has created.', 'start': 15220.56, 'duration': 3.062}, {'end': 15225.203, 'text': 'This is called a pivot chart.', 'start': 15223.782, 'duration': 1.421}, {'end': 15231.748, 'text': 'Now, here you can see the category fruits and vegetables and the different fruits and vegetables or the items.', 'start': 15225.804, 'duration': 5.944}], 'summary': 'Using excel to create pivot charts with default bar chart for fruits and vegetables.', 'duration': 27.52, 'max_score': 15204.228, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw15204228.jpg'}, {'end': 15630.962, 'src': 'embed', 'start': 15604.803, 'weight': 2, 'content': [{'end': 15610.225, 'text': 'It allows you to carry out the extract, transform, and load operations on the datasets from multiple sources.', 'start': 15604.803, 'duration': 5.422}, {'end': 15614.467, 'text': "Now, let's look at the challenges solved by Power Query.", 'start': 15611.165, 'duration': 3.302}, {'end': 15619.396, 'text': 'Earlier, in Excel, there was difficulty in data connections.', 'start': 15616.235, 'duration': 3.161}, {'end': 15628.58, 'text': 'Now, using Power Query, you can connect to a broad range of data sources, such as relational databases, web files, text, CSV,', 'start': 15620.217, 'duration': 8.363}, {'end': 15630.962, 'text': 'JSON files and even fetch data in the cloud.', 'start': 15628.58, 'duration': 2.382}], 'summary': 'Power query enables diverse data connections, solving previous excel data connection challenges.', 'duration': 26.159, 'max_score': 15604.803, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw15604803.jpg'}, {'end': 15678.374, 'src': 'embed', 'start': 15653.616, 'weight': 1, 'content': [{'end': 15659.202, 'text': 'Using Power Query, a repeatable process query is adopted to update the data in real-time and in the future.', 'start': 15653.616, 'duration': 5.586}, {'end': 15664.907, 'text': 'In Excel, it was not so easy to reshape, transform and manipulate data.', 'start': 15660.543, 'duration': 4.364}, {'end': 15670.833, 'text': 'But using Power Query provides a highly interactive experience and sophisticated tools to prepare your data.', 'start': 15665.588, 'duration': 5.245}, {'end': 15678.374, 'text': 'From Excel 2016 onwards, Power Query on Windows has been fully integrated into Excel.', 'start': 15673.305, 'duration': 5.069}], 'summary': 'Power query enables real-time data updates and interactive data preparation in excel.', 'duration': 24.758, 'max_score': 15653.616, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw15653616.jpg'}, {'end': 15764.14, 'src': 'embed', 'start': 15741.135, 'weight': 5, 'content': [{'end': 15749.406, 'text': "Up next, you'll see how to group your data, how to split a column into multiple columns, and then pivot and unpivot your table.", 'start': 15741.135, 'duration': 8.271}, {'end': 15753.211, 'text': 'Then date columns and make some transformations.', 'start': 15750.828, 'duration': 2.383}, {'end': 15759.178, 'text': 'understand how to append tables and merge tables vertically and horizontally.', 'start': 15754.437, 'duration': 4.741}, {'end': 15764.14, 'text': "Now let's open MS Excel and start with importing a simple text file.", 'start': 15759.939, 'duration': 4.201}], 'summary': 'Learn data grouping, splitting, pivoting, unpivoting, transforming, appending, and merging in ms excel.', 'duration': 23.005, 'max_score': 15741.135, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw15741135.jpg'}, {'end': 16187.566, 'src': 'embed', 'start': 16158.467, 'weight': 3, 'content': [{'end': 16165.47, 'text': "these are all repeated, which we actually don't need, And these are pretty similar to our column names.", 'start': 16158.467, 'duration': 7.003}, {'end': 16175.197, 'text': 'Similarly, if I scroll down, you see there are some rows which have null values.', 'start': 16166.491, 'duration': 8.706}, {'end': 16182.462, 'text': 'So actually these rows do not add up any value or do not add any value to our data.', 'start': 16176.218, 'duration': 6.244}, {'end': 16184.384, 'text': "So we'll clean this data first.", 'start': 16182.682, 'duration': 1.702}, {'end': 16187.566, 'text': "So let's see how to do it.", 'start': 16185.925, 'duration': 1.641}], 'summary': 'Identifying and removing redundant rows and null values from the data.', 'duration': 29.099, 'max_score': 16158.467, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw16158467.jpg'}, {'end': 16339.14, 'src': 'embed', 'start': 16309.892, 'weight': 4, 'content': [{'end': 16312.054, 'text': "Let's do some more manipulation to our data.", 'start': 16309.892, 'duration': 2.162}, {'end': 16319.44, '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': 16312.754, 'duration': 6.686}, {'end': 16322.95, 'text': 'so let me just show you the venue column.', 'start': 16320.649, 'duration': 2.301}, {'end': 16325.012, 'text': 'so this is our venue column.', 'start': 16322.95, 'duration': 2.062}, {'end': 16328.634, 'text': 'so the first value is the stadium name.', 'start': 16325.012, 'duration': 3.622}, {'end': 16336.338, 'text': 'then we have the city in which the stadium is there and finally we have the country name.', 'start': 16328.634, 'duration': 7.704}, {'end': 16339.14, 'text': 'so I want to extract only the stadium name.', 'start': 16336.338, 'duration': 2.802}], 'summary': 'Adding new column by extracting stadium names from venue column.', 'duration': 29.248, 'max_score': 16309.892, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw16309892.jpg'}, {'end': 16951.435, 'src': 'heatmap', 'start': 16656.098, 'weight': 1, 'content': [{'end': 16657.38, 'text': "I'll go to the transform tab.", 'start': 16656.098, 'duration': 1.282}, {'end': 16660.857, 'text': 'click on format and select uppercase.', 'start': 16658.015, 'duration': 2.842}, {'end': 16668.761, 'text': 'Now we saw how to do some simple manipulation of our data.', 'start': 16662.918, 'duration': 5.843}, {'end': 16671.844, 'text': 'So we created a few columns, split a few columns.', 'start': 16668.982, 'duration': 2.862}, {'end': 16679.089, 'text': 'Now to save all this, I have to go to the home tab and then click on close and load.', 'start': 16673.544, 'duration': 5.545}, {'end': 16685.133, 'text': 'This will take some time and load our data onto this Excel sheet.', 'start': 16681.369, 'duration': 3.764}, {'end': 16689.035, 'text': "You can see here it's loading the data.", 'start': 16686.874, 'duration': 2.161}, {'end': 16690.856, 'text': 'This will take a bit of time.', 'start': 16689.314, 'duration': 1.542}, {'end': 16692.037, 'text': 'There you go.', 'start': 16691.497, 'duration': 0.54}, {'end': 16701.433, 'text': 'Excel Power Query feature has successfully performed some manipulations on our data, some calculations on our data,', 'start': 16693.498, 'duration': 7.935}, {'end': 16706.915, 'text': 'and then it has saved the final version and loaded it onto Excel.', 'start': 16701.433, 'duration': 5.482}, {'end': 16711.436, 'text': 'Now using this clean data, we can do some analysis.', 'start': 16707.435, 'duration': 4.001}, {'end': 16716.457, 'text': "Let's say I want to find the seasons in which the winner's team scored more than three goals.", 'start': 16712.096, 'duration': 4.361}, {'end': 16724.04, 'text': 'So we have a problem statement at hand where we want to find the seasons in which the winner team had more than three goals scored.', 'start': 16716.957, 'duration': 7.083}, {'end': 16733.08, 'text': 'So what you can do is select any cell in this data and go to the insert tab and click on pivot table.', 'start': 16724.997, 'duration': 8.083}, {'end': 16738.601, 'text': "Here, I'll click on existing worksheet and then I'll give my location.", 'start': 16734.08, 'duration': 4.521}, {'end': 16743.403, 'text': "I'll place my pivot table somewhere here and click on OK.", 'start': 16739.463, 'duration': 3.94}, {'end': 16744.605, 'text': 'All right.', 'start': 16744.304, 'duration': 0.301}, {'end': 16750.567, 'text': "Now, since I want to know the seasons, I'll drag season on to row.", 'start': 16745.265, 'duration': 5.302}, {'end': 16756.259, 'text': "and I'll also drag the winners team column onto rows.", 'start': 16751.635, 'duration': 4.624}, {'end': 16761.024, 'text': "Then I'll choose my winner score under values.", 'start': 16757.881, 'duration': 3.143}, {'end': 16764.587, 'text': 'Here you can see we have the pivot table ready.', 'start': 16762.024, 'duration': 2.563}, {'end': 16771.732, 'text': 'Now we need to filter this table to see all the winning teams that scored more than three goals.', 'start': 16766.068, 'duration': 5.664}, {'end': 16777.237, 'text': "So what I'll do is I'll select this winner score column and place it under filters.", 'start': 16771.792, 'duration': 5.445}, {'end': 16781.586, 'text': 'And here you can see, I have my filter.', 'start': 16779.645, 'duration': 1.941}, {'end': 16789.41, 'text': "Let me click on this dropdown and I'll select multiple items from this multiple items.", 'start': 16782.467, 'duration': 6.943}, {'end': 16795.334, 'text': "I'll choose four, five, and seven because these values are greater than three and click on.", 'start': 16789.491, 'duration': 5.843}, {'end': 16800.497, 'text': 'Okay Here you can see, I have filtered my pivot table and left.', 'start': 16795.414, 'duration': 5.083}, {'end': 16806.58, 'text': 'You can see the season and the winner team that had scored more than three goals in the finals.', 'start': 16800.537, 'duration': 6.043}, {'end': 16808.804, 'text': 'All right.', 'start': 16808.464, 'duration': 0.34}, {'end': 16813.088, 'text': 'Similarly, you can perform some more analysis.', 'start': 16809.665, 'duration': 3.423}, {'end': 16818.091, 'text': 'Suppose I want to know how many times Real Madrid won the championship.', 'start': 16813.488, 'duration': 4.603}, {'end': 16819.392, 'text': "So let's see.", 'start': 16818.792, 'duration': 0.6}, {'end': 16826.578, 'text': "I'll click on one cell in the dataset, go to the insert tab and click on pivot table.", 'start': 16819.412, 'duration': 7.166}, {'end': 16831.021, 'text': "I'll choose existing worksheet and give my location here.", 'start': 16827.919, 'duration': 3.102}, {'end': 16834.404, 'text': "Let's say I want to place my pivot table here.", 'start': 16831.882, 'duration': 2.522}, {'end': 16835.805, 'text': "I'll click on.", 'start': 16834.424, 'duration': 1.381}, {'end': 16837.346, 'text': 'Okay All right.', 'start': 16835.905, 'duration': 1.441}, {'end': 16848.377, 'text': "So the question we have is how many times have Real Madrid won the championship? So I'll choose the winners team column and place it under rows.", 'start': 16837.952, 'duration': 10.425}, {'end': 16854.6, 'text': "And let's say we'll select the winner score as well.", 'start': 16849.738, 'duration': 4.862}, {'end': 16862.284, 'text': "And let's convert this winner score from sum to let's say count and click on.", 'start': 16856.561, 'duration': 5.723}, {'end': 16874.911, 'text': "Okay And since I want to check only for Real Madrid, so what I'll do is I'll go to the insert tab and I'll insert a slicer.", 'start': 16862.344, 'duration': 12.567}, {'end': 16880.592, 'text': "Here I'll choose winner teams as my slicer and click on OK.", 'start': 16875.931, 'duration': 4.661}, {'end': 16886.453, 'text': 'And out of this, I want to choose only Real Madrid.', 'start': 16881.592, 'duration': 4.861}, {'end': 16887.714, 'text': "So I'll select Real Madrid.", 'start': 16886.513, 'duration': 1.201}, {'end': 16889.294, 'text': 'You can see it here.', 'start': 16888.494, 'duration': 0.8}, {'end': 16892.155, 'text': 'Real Madrid have won the championship 13 times.', 'start': 16890.134, 'duration': 2.021}, {'end': 16902.648, 'text': "okay, let's say you want to compress your data and remove unnecessary columns without losing any information.", 'start': 16895.945, 'duration': 6.703}, {'end': 16907.23, 'text': 'you can do that using a feature in the power query editor called unpivot.', 'start': 16902.648, 'duration': 4.582}, {'end': 16911.571, 'text': "to perform this task, we'll use a census data of india from wikipedia.", 'start': 16907.23, 'duration': 4.341}, {'end': 16915.753, 'text': 'so let me first show you the wikipedia page.', 'start': 16911.571, 'duration': 4.182}, {'end': 16922.896, 'text': 'so this is my wikipedia article, which says list of states in india by past population, and if i scroll down,', 'start': 16915.753, 'duration': 7.143}, {'end': 16932.169, 'text': "you can see it here there's a table which says by past population from 1947 to 2011.", 'start': 16924.246, 'duration': 7.923}, {'end': 16940.171, 'text': 'so there are a few columns like rank this state or union territory, and we have population starting from 1951 till 2011, which was our last census.', 'start': 16932.169, 'duration': 8.002}, {'end': 16951.435, 'text': 'Here you can see if I scroll further there are nearly 29 states and we have 7 Union Territories.', 'start': 16943.908, 'duration': 7.527}], 'summary': "Excel power query feature manipulated and analyzed data, finding winning teams with more than 3 goals, real madrid's championship wins, and performing unpivot on census data.", 'duration': 295.337, 'max_score': 16656.098, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw16656098.jpg'}], 'start': 14995.416, 'title': 'Excel pivot tables and power query', 'summary': 'Covers creating calculated fields in excel pivot tables, demonstrating features such as pivot tables, filtering, slicer, power query, and data manipulation, with specific examples including sales analysis, census data, and european cup data.', 'chapters': [{'end': 15381.134, 'start': 14995.416, 'title': 'Creating calculated fields in excel pivot table', 'summary': 'Demonstrates how to create a calculated field in a pivot table in excel, showing the process of creating a calculated field for sales and generating various pivot charts based on the data, with guava being the highest selling fruit.', 'duration': 385.718, 'highlights': ['Creating a Calculated Field in Pivot Table The chapter explains the process of creating a calculated field in a pivot table in Excel, using the example of creating a calculated field for sales by multiplying price per kg and quantity.', 'Generating Pivot Charts It shows the process of generating pivot charts in Excel, including bar, pie, line, and area charts based on the sales data, with Guava being the highest selling fruit and Broccoli as the highest selling vegetable.', 'Highest Selling Item The transcript highlights that Guava was the highest selling fruit, and Broccoli was the highest selling vegetable, with specific sales values mentioned for each.']}, {'end': 15936.669, 'start': 15381.134, 'title': 'Excel power query demo', 'summary': 'Covered a demonstration of pivot tables in excel using 2011 census data from india, showcasing features such as creating pivot tables, calculating averages, filtering tables, using slicer, finding percentage contribution, creating calculated fields, and creating various charts and graphs. additionally, it delved into the important features and functionalities of power query in excel, including its challenges solved, characteristics of big data, features, and a demonstration of importing and visualizing data from different sources.', 'duration': 555.535, 'highlights': ['The chapter covered a demonstration of pivot tables in Excel using 2011 census data from India It showcased features such as creating pivot tables, calculating averages, filtering tables, using slicer, finding percentage contribution, creating calculated fields, and creating various charts and graphs.', 'Power Query enables you to connect to a broad range of data sources, such as relational databases, web files, text, CSV, JSON files, and even fetch data in the cloud It allows for a broader connection to data sources, facilitating easier data handling and analysis.', 'Power Query enables transforming data to an appropriate size and allows working on any shape of data from any source It facilitates data transformation to handle big data and work with data from diverse sources.', 'Using Power Query, a repeatable process query is adopted to update the data in real-time and in the future It allows for real-time data updates through a repeatable process query.', 'Power Query provides a highly interactive experience and sophisticated tools to prepare data It offers an interactive experience and tools for data preparation and manipulation.', 'Excel Power Query allows you to clean, transform, manipulate, and process data for analysis It supports data cleaning, transformation, manipulation, and processing for analysis.', 'Power Query helps automate repetitive tasks and prepares and shapes data for analysis It aids in automating repetitive tasks, preparing, and shaping data for analysis.', 'The chapter also demonstrated importing and visualizing data from different sources in Excel It provided a demonstration of importing and visualizing data from various sources, including text files.']}, {'end': 16306.97, 'start': 15937.569, 'title': 'Importing web data into excel using power query', 'summary': 'Demonstrates the process of using excel power query to import a table from a wikipedia page on european cup and uefa champions league finals, cleaning the data, and visualizing it in excel, with specific insights into data transformation and cleaning.', 'duration': 369.401, 'highlights': ['Excel Power Query allows importing data from the web and cleaning it for analysis. The Power Query feature in Excel enables the import of data from the web, as demonstrated by importing a table from a Wikipedia page on European Cup and UEFA Champions League Finals, showcasing the process of cleaning and transforming the data for analysis.', 'Data cleaning involves removing redundant rows and rows with null values. The demonstration includes steps for removing redundant rows and rows with null values from the imported table, enhancing the data quality and preparing it for analysis.', 'Specific steps for data reduction, including removing top and bottom rows, are illustrated. The process of reducing the data by removing top and bottom rows is elaborated, with specific steps outlined for removing redundant and irrelevant data from the table.']}, {'end': 16892.155, 'start': 16309.892, 'title': 'Data manipulation in excel', 'summary': "Demonstrates data manipulation in excel using power query, including adding new columns, splitting columns, and changing values. it also shows how to use pivot tables for analysis, such as finding seasons with winning teams scoring more than three goals and counting real madrid's championship wins.", 'duration': 582.263, 'highlights': ['The chapter demonstrates data manipulation in Excel using Power Query, including adding new columns, splitting columns, and changing values. The transcript provides a detailed demonstration of using Power Query in Excel to add new columns, split existing columns, and change the case of values, showcasing practical data manipulation techniques.', "It also shows how to use pivot tables for analysis, such as finding seasons with winning teams scoring more than three goals and counting Real Madrid's championship wins. The chapter illustrates the use of pivot tables for analysis, including filtering to find seasons with winning teams scoring more than three goals and counting the number of championship wins for Real Madrid."]}, {'end': 17188.086, 'start': 16895.945, 'title': 'Unpivoting census data in excel', 'summary': 'Demonstrates how to use the power query editor to unpivot a table of indian census data from wikipedia, removing unnecessary rows and compressing population columns from 1951 to 2011, ultimately preparing the data for loading into excel.', 'duration': 292.141, 'highlights': ['The chapter demonstrates how to use the Power Query Editor to unpivot a table of Indian census data from Wikipedia It explains the process of extracting a table from Wikipedia using the Power Query Editor and preparing it for further transformation.', 'Removing unnecessary rows and compressing population columns from 1951 to 2011 The tutorial explains the steps to remove unnecessary rows and compress population columns, demonstrating data cleaning and organization.', 'Preparing the data for loading into Excel The chapter covers the final steps of data preparation before loading the transformed data into Excel for further analysis and visualization.']}], 'duration': 2192.67, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw14995416.jpg', 'highlights': ['Generating Pivot Charts: Demonstrates generating pivot charts in Excel based on sales data.', 'Power Query: Facilitates data transformation, real-time updates, and data preparation.', 'Excel Power Query: Supports importing and visualizing data from various sources.', 'Data Cleaning: Involves removing redundant rows, null values, and irrelevant data.', 'Data Manipulation: Demonstrates adding new columns, splitting columns, and changing values.', 'Pivot Tables for Analysis: Illustrates using pivot tables for data analysis and filtering.']}, {'end': 18570.005, 'segs': [{'end': 17242.295, 'src': 'embed', 'start': 17212.717, 'weight': 0, 'content': [{'end': 17218.582, 'text': "So let me go to a new sheet and let's import the AdventureWorks customer dataset.", 'start': 17212.717, 'duration': 5.865}, {'end': 17220.964, 'text': "It's a CSV file onto Excel first.", 'start': 17219.083, 'duration': 1.881}, {'end': 17225.228, 'text': "So I'll go to my data tab and click on get data.", 'start': 17221.685, 'duration': 3.543}, {'end': 17232.485, 'text': "From here, I'll go to from file and choose from text slash CSV.", 'start': 17225.528, 'duration': 6.957}, {'end': 17236.849, 'text': 'You can see it here in my Power Query files folder.', 'start': 17233.706, 'duration': 3.143}, {'end': 17240.113, 'text': 'I have my AdventureWorks customer table.', 'start': 17236.909, 'duration': 3.204}, {'end': 17242.295, 'text': "I'll select this and click on import.", 'start': 17240.133, 'duration': 2.162}], 'summary': 'Imported adventureworks customer dataset csv file into excel using power query.', 'duration': 29.578, 'max_score': 17212.717, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw17212717.jpg'}, {'end': 17465.573, 'src': 'embed', 'start': 17433.596, 'weight': 2, 'content': [{'end': 17435.536, 'text': 'you can see we have removed the prefix column.', 'start': 17433.596, 'duration': 1.94}, {'end': 17438.317, 'text': "similarly, let's remove the other two columns.", 'start': 17435.536, 'duration': 2.781}, {'end': 17445.519, 'text': 'you can either right click and do or go to the home tab and then select remove columns.', 'start': 17438.317, 'duration': 7.202}, {'end': 17457.147, 'text': "alright, now Let's say we want to add a domain name column from AWS customers by extracting the characters between at the rate and com.", 'start': 17445.519, 'duration': 11.628}, {'end': 17460.349, 'text': "So actually I'm talking about the email address.", 'start': 17458.008, 'duration': 2.341}, {'end': 17465.573, 'text': 'From this email address, we want to create a new column called as domain name.', 'start': 17461.43, 'duration': 4.143}], 'summary': 'Removed prefix column and added domain name column from email addresses.', 'duration': 31.977, 'max_score': 17433.596, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw17433596.jpg'}, {'end': 17645.028, 'src': 'embed', 'start': 17621.755, 'weight': 3, 'content': [{'end': 17630.14, 'text': 'we have this product name column and say, if you want to find the total number of product names in the table, how to do it.', 'start': 17621.755, 'duration': 8.385}, {'end': 17636.503, 'text': 'so what you can do is click on this product name column, then go to the transform tab.', 'start': 17630.14, 'duration': 6.363}, {'end': 17640.646, 'text': 'now in the transform tab you have an option called statistics.', 'start': 17636.503, 'duration': 4.143}, {'end': 17645.028, 'text': 'click on this, drop down and select count values.', 'start': 17640.646, 'duration': 4.382}], 'summary': 'Find total number of product names in the table using statistics tab.', 'duration': 23.273, 'max_score': 17621.755, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw17621755.jpg'}, {'end': 17701.573, 'src': 'embed', 'start': 17674.011, 'weight': 4, 'content': [{'end': 17678.034, 'text': "So let's see what is the average product price from the product table.", 'start': 17674.011, 'duration': 4.023}, {'end': 17683.739, 'text': "So I'll select this product price column, go to the transform tab under statistics.", 'start': 17678.194, 'duration': 5.545}, {'end': 17685.66, 'text': "I'll select average.", 'start': 17684.419, 'duration': 1.241}, {'end': 17694.267, 'text': 'So this will give me the average product price, which is 714.4373.', 'start': 17686.381, 'duration': 7.886}, {'end': 17695.928, 'text': 'You can consider any unit you want.', 'start': 17694.267, 'duration': 1.661}, {'end': 17697.55, 'text': "Let's say this is in dollars.", 'start': 17696.108, 'duration': 1.442}, {'end': 17701.573, 'text': 'Now again, we have to cancel this step to move back.', 'start': 17698.711, 'duration': 2.862}], 'summary': 'The average product price is 714.4373 dollars.', 'duration': 27.562, 'max_score': 17674.011, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw17674011.jpg'}, {'end': 18094.149, 'src': 'embed', 'start': 18068.117, 'weight': 5, 'content': [{'end': 18076.664, 'text': 'Now you can see here on the Power Query Editor, all my data files have been combined vertically.', 'start': 18068.117, 'duration': 8.547}, {'end': 18079.627, 'text': 'Here you can see the month column.', 'start': 18077.485, 'duration': 2.142}, {'end': 18083.422, 'text': "On the extreme right, it's the amount column.", 'start': 18081.28, 'duration': 2.142}, {'end': 18090.807, 'text': 'And to the extreme left, we have the source name or the source file where the data came from.', 'start': 18084.322, 'duration': 6.485}, {'end': 18092.328, 'text': 'So first is project one.', 'start': 18090.927, 'duration': 1.401}, {'end': 18094.149, 'text': 'Then we have project two.', 'start': 18093.069, 'duration': 1.08}], 'summary': 'Data files combined vertically, displaying month and amount columns, with source names for project one and project two.', 'duration': 26.032, 'max_score': 18068.117, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw18068117.jpg'}, {'end': 18570.005, 'src': 'embed', 'start': 18533.683, 'weight': 6, 'content': [{'end': 18538.068, 'text': "So if you want to get the data and the dashboard file that we'll be creating in this demo,", 'start': 18533.683, 'duration': 4.385}, {'end': 18540.53, 'text': 'then please put your email IDs in the comment section of the video.', 'start': 18538.068, 'duration': 2.462}, {'end': 18543.053, 'text': 'Our team will share the files via email.', 'start': 18541.051, 'duration': 2.002}, {'end': 18548.098, 'text': "Now, let's begin by understanding what is a dashboard in Excel.", 'start': 18544.494, 'duration': 3.604}, {'end': 18556.227, 'text': 'A dashboard is a visual interface that provides an overview of key measures relevant to a particular objective with the help of charts and graphs.', 'start': 18549.379, 'duration': 6.848}, {'end': 18563.239, 'text': 'Dashboard reports allow managers to get a high-level overview of the business and help them make quick decisions.', 'start': 18557.233, 'duration': 6.006}, {'end': 18570.005, 'text': 'There are different types of dashboards such as strategic dashboards, analytical dashboards, and operational dashboards.', 'start': 18564.22, 'duration': 5.785}], 'summary': 'Learn about excel dashboards and their benefits. different types include strategic, analytical, and operational dashboards.', 'duration': 36.322, 'max_score': 18533.683, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw18533683.jpg'}], 'start': 17189.434, 'title': 'Power query editor and data manipulation in excel', 'summary': 'Covers importing and transforming adventureworks datasets using power query editor, including statistical analysis, data manipulation, and creating dashboards in excel, providing practical insights for power bi practitioners.', 'chapters': [{'end': 17242.295, 'start': 17189.434, 'title': 'Exploring power query editor with adventureworks customer table', 'summary': 'Covers importing adventureworks customer dataset into excel using power query editor, which is provided by microsoft for practitioners to learn power bi and similar technologies.', 'duration': 52.861, 'highlights': ['Importing AdventureWorks customer dataset into Excel using Power Query Editor, provided by Microsoft for practitioners to learn Power BI and similar technologies.', "Choosing 'from text slash CSV' option in the data tab to import the AdventureWorks customer table as a CSV file onto Excel.", 'Selecting the AdventureWorks customer table and clicking on import in the Power Query files folder.', 'Exploring features and functionalities of Power Query Editor with a demo.']}, {'end': 17521.04, 'start': 17245.967, 'title': 'Power query data transformation', 'summary': 'Covers the process of loading and transforming datasets using power query, including changing column cases, merging columns, removing unwanted columns, and adding new columns like domain name extracted from email addresses.', 'duration': 275.073, 'highlights': ['The process of transforming data includes changing column cases, merging columns, removing unwanted columns, and adding new columns like domain name extracted from email addresses The chapter covers the process of loading and transforming datasets using Power Query, including changing column cases, merging columns, removing unwanted columns, and adding new columns like domain name extracted from email addresses.', "Changing the columns' values to proper case by capitalizing each word The presenter demonstrates changing the prefix, first name, and last name columns to proper case by capitalizing each word.", 'Merging the prefix, first name, and last name columns into one full name column The process of merging the prefix, first name, and last name columns into one full name column is demonstrated.', "Removing unwanted columns by right-clicking and selecting 'remove' The process of removing unwanted columns is shown, allowing for the removal of specific columns by right-clicking and selecting 'remove'.", 'Adding a new column for domain name extracted from email addresses The presenter adds a new column for the domain name extracted from email addresses, demonstrating how to extract characters between specific delimiters to create a domain name column.']}, {'end': 17910.21, 'start': 17521.04, 'title': 'Statistical analysis and data transformation', 'summary': 'Covers the use of power query editor for data transformation, exploring statistical features, and performing calculations on the adventureworks product dataset, including finding total product names, calculating average product price, and adding a discount column.', 'duration': 389.17, 'highlights': ['The chapter covers the use of Power Query editor for data transformation and exploring statistical features. ', 'It demonstrates finding the total number of product names in the table, which is 293. Total number of product names: 293', 'Calculating the average product price, which is 714.4373 (considered in dollars). Average product price: 714.4373 dollars', 'Finding the minimum product price, which is 2.29. Minimum product price: 2.29', 'Finding the maximum product price in the product table. ', 'Rounding the product cost and product price columns to two decimal places for consistency. ', 'Adding a discount price column by multiplying the product price by 0.9 and rounding it to two decimal places. ']}, {'end': 18570.005, 'start': 17910.23, 'title': 'Data manipulation and dashboard creation in excel', 'summary': 'Demonstrates data manipulation in excel by formatting, combining, and merging multiple tables, and concludes with an overview of creating dashboards in excel, offering to share the files via email.', 'duration': 659.775, 'highlights': ['The chapter demonstrates how to combine and bring all data together from multiple files in Excel, using Power Query to vertically join five Excel files and to horizontally merge two tables. The chapter highlights the process of combining and bringing together data from multiple files in Excel, using Power Query to vertically join five Excel files and to horizontally merge two tables.', 'It provides a demonstration of creating two dashboards using a sample sales dataset and offers to share the files via email upon request in the comment section of the video. The chapter provides a demonstration of creating two dashboards using a sample sales dataset and offers to share the files via email upon request in the comment section of the video.', 'The chapter explains the concept of a dashboard in Excel, defining it as a visual interface that provides an overview of key measures relevant to a particular objective with the help of charts and graphs. The chapter explains the concept of a dashboard in Excel, defining it as a visual interface that provides an overview of key measures relevant to a particular objective with the help of charts and graphs.']}], 'duration': 1380.571, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw17189434.jpg', 'highlights': ['Importing AdventureWorks customer dataset into Excel using Power Query Editor.', "Choosing 'from text slash CSV' option in the data tab to import the AdventureWorks customer table as a CSV file onto Excel.", 'The process of transforming data includes changing column cases, merging columns, removing unwanted columns, and adding new columns like domain name extracted from email addresses.', 'Demonstrates finding the total number of product names in the table, which is 293.', 'Calculating the average product price, which is 714.4373 (considered in dollars).', 'The chapter demonstrates how to combine and bring all data together from multiple files in Excel, using Power Query to vertically join five Excel files and to horizontally merge two tables.', 'It provides a demonstration of creating two dashboards using a sample sales dataset and offers to share the files via email upon request in the comment section of the video.', 'The chapter explains the concept of a dashboard in Excel, defining it as a visual interface that provides an overview of key measures relevant to a particular objective with the help of charts and graphs.']}, {'end': 20057.147, 'segs': [{'end': 18623.96, 'src': 'embed', 'start': 18570.926, 'weight': 0, 'content': [{'end': 18574.73, 'text': 'An advantage of dashboards is the quick detection of outliers and correlations.', 'start': 18570.926, 'duration': 3.804}, {'end': 18580.802, 'text': 'With comprehensive data visualization, it is time-saving as compared to running multiple reports.', 'start': 18575.796, 'duration': 5.006}, {'end': 18584.907, 'text': "With this understanding, let's jump into our demo.", 'start': 18582.004, 'duration': 2.903}, {'end': 18589.213, 'text': "For creating our dashboards, we'll be using a sample sales dataset.", 'start': 18585.588, 'duration': 3.625}, {'end': 18591.375, 'text': 'Let me show you the dataset first.', 'start': 18590.013, 'duration': 1.362}, {'end': 18596.395, 'text': "so here is the sales data set that we'll be using for our demo.", 'start': 18592.614, 'duration': 3.781}, {'end': 18601.457, 'text': 'so this data set was actually generated using a simulator and is completely random.', 'start': 18596.395, 'duration': 5.062}, {'end': 18607.84, 'text': 'it was not validated, though we have applied certain transformations to the data using power query features.', 'start': 18601.457, 'duration': 6.383}, {'end': 18611.521, 'text': 'so this data, as you can see, has thousand rows.', 'start': 18607.84, 'duration': 3.681}, {'end': 18614.342, 'text': 'so using the simulator, we had generated thousand rows of data.', 'start': 18611.521, 'duration': 2.821}, {'end': 18623.96, 'text': 'similarly, if I go on top, you can see this data set has 17 columns.', 'start': 18616.593, 'duration': 7.367}], 'summary': 'Dashboards enable quick outlier detection and correlation, saving time with comprehensive data visualization using a sample sales dataset of 1000 rows and 17 columns.', 'duration': 53.034, 'max_score': 18570.926, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw18570926.jpg'}, {'end': 18765.839, 'src': 'embed', 'start': 18702.012, 'weight': 2, 'content': [{'end': 18707.374, 'text': 'then we have the unit cost column, which is basically the production cost for each of the items.', 'start': 18702.012, 'duration': 5.362}, {'end': 18709.675, 'text': 'next we have the total revenue.', 'start': 18707.374, 'duration': 2.301}, {'end': 18714.457, 'text': 'the total revenue is actually the product of units sold and unit price.', 'start': 18709.675, 'duration': 4.782}, {'end': 18717.037, 'text': 'then we have the total cost column.', 'start': 18714.457, 'duration': 2.58}, {'end': 18722.559, 'text': 'now the total cost column is actually the product of units sold and the unit cost.', 'start': 18717.037, 'duration': 5.522}, {'end': 18725.601, 'text': 'similarly we have the total profit column.', 'start': 18722.559, 'duration': 3.042}, {'end': 18729.322, 'text': 'so total profit is the difference between total revenue and the total cost.', 'start': 18725.601, 'duration': 3.721}, {'end': 18737.872, 'text': 'and finally we have created two more columns, that is order year and then we have order month.', 'start': 18730.509, 'duration': 7.363}, {'end': 18742.494, 'text': 'now these two columns were actually generated using the power query features.', 'start': 18737.872, 'duration': 4.622}, {'end': 18748.757, 'text': 'so we use the order date column, which is this column, and extracted order year and order month.', 'start': 18742.494, 'duration': 6.263}, {'end': 18757.276, 'text': 'so first we are going to create a revenue dashboard where we will focus on generating reports for revenue by order year, revenue by year and region,', 'start': 18748.757, 'duration': 8.519}, {'end': 18759.277, 'text': 'revenue by order, priority and much more.', 'start': 18757.276, 'duration': 2.001}, {'end': 18765.839, 'text': "We'll create separate pivot tables and pivot charts and format them to make them look more interesting and presentable.", 'start': 18760.057, 'duration': 5.782}], 'summary': 'Transcript covers unit cost, total revenue, total cost, and profit calculations. it also discusses creating a revenue dashboard with various reports and visualizations.', 'duration': 63.827, 'max_score': 18702.012, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw18702012.jpg'}, {'end': 19234.108, 'src': 'embed', 'start': 19199.489, 'weight': 5, 'content': [{'end': 19202.651, 'text': 'so we have a nice combo chart ready here.', 'start': 19199.489, 'duration': 3.162}, {'end': 19209.154, 'text': 'now the way to look at it is the bars represent the total revenue, which is this column.', 'start': 19202.651, 'duration': 6.503}, {'end': 19216.321, 'text': 'now the line represents the total cost.', 'start': 19210.518, 'duration': 5.803}, {'end': 19219.902, 'text': 'so let me go ahead and edit this chart a bit.', 'start': 19216.321, 'duration': 3.581}, {'end': 19228.086, 'text': "so first of all, let's delete the field buttons, all right, and let's also remove the legend from here.", 'start': 19219.902, 'duration': 8.184}, {'end': 19231.547, 'text': "next we'll add data labels.", 'start': 19228.086, 'duration': 3.461}, {'end': 19234.108, 'text': "so i'll click on data labels here.", 'start': 19231.547, 'duration': 2.561}], 'summary': 'A combo chart shows revenue and cost, with field buttons and legend removed, and data labels added.', 'duration': 34.619, 'max_score': 19199.489, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw19199489.jpg'}, {'end': 19605.153, 'src': 'embed', 'start': 19565.967, 'weight': 6, 'content': [{'end': 19571.371, 'text': "So I'm going to insert picture present on the device that is my computer.", 'start': 19565.967, 'duration': 5.404}, {'end': 19578.276, 'text': "I'll go to desktop and here I have a folder called Excel dashboard files.", 'start': 19572.732, 'duration': 5.544}, {'end': 19585.495, 'text': "and I'll select this dashboard background and hit insert.", 'start': 19580.891, 'duration': 4.604}, {'end': 19588.278, 'text': 'So this is going to insert an image.', 'start': 19586.596, 'duration': 1.682}, {'end': 19593.102, 'text': 'Now let me just drag this image.', 'start': 19589.139, 'duration': 3.963}, {'end': 19596.125, 'text': 'So it covers a fair enough portion.', 'start': 19593.502, 'duration': 2.623}, {'end': 19599.608, 'text': "So I'll hit shift and I'll drag it.", 'start': 19596.145, 'duration': 3.463}, {'end': 19605.153, 'text': 'All right.', 'start': 19604.853, 'duration': 0.3}], 'summary': 'Inserting and adjusting an image on a computer for an excel dashboard.', 'duration': 39.186, 'max_score': 19565.967, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw19565967.jpg'}, {'end': 19750.853, 'src': 'embed', 'start': 19682.904, 'weight': 7, 'content': [{'end': 19689.727, 'text': "so my text box is transparent now and I'll also remove the outline all right.", 'start': 19682.904, 'duration': 6.823}, {'end': 19699.081, 'text': "Now let me just double click on the title of my dashboard and I'm going to select a font.", 'start': 19690.708, 'duration': 8.373}, {'end': 19701.064, 'text': 'You can select whichever font you want.', 'start': 19699.261, 'duration': 1.803}, {'end': 19705.591, 'text': 'Let me stick to Britannica bold.', 'start': 19702.967, 'duration': 2.624}, {'end': 19710.44, 'text': "And I'll increase the size to let's say 30.", 'start': 19706.816, 'duration': 3.624}, {'end': 19714.924, 'text': "Alright, I'll just drag the text box.", 'start': 19710.44, 'duration': 4.484}, {'end': 19719.148, 'text': "I'll make the text as white instead of black.", 'start': 19716.426, 'duration': 2.722}, {'end': 19724.834, 'text': 'Alright, so we have our title of the dashboard ready.', 'start': 19720.169, 'duration': 4.665}, {'end': 19730.815, 'text': 'now, if you want, you can also insert some icons to this dashboard.', 'start': 19725.871, 'duration': 4.944}, {'end': 19739.463, 'text': "so i'll go to insert and i'll click on illustrations again and select pictures.", 'start': 19730.815, 'duration': 8.648}, {'end': 19750.853, 'text': "i'm going to add this to pictures, which is of a store and a cart, to make it look visually appealing.", 'start': 19739.463, 'duration': 11.39}], 'summary': 'Customized dashboard title with britannica bold font, size 30, and added store and cart pictures for visual appeal.', 'duration': 67.949, 'max_score': 19682.904, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw19682904.jpg'}, {'end': 19839.074, 'src': 'embed', 'start': 19806.114, 'weight': 9, 'content': [{'end': 19812.118, 'text': 'similarly, let me bring in all the other charts as well, alright.', 'start': 19806.114, 'duration': 6.004}, {'end': 19817.022, 'text': 'so now you can see I have added all my charts and graphs to this dashboard.', 'start': 19812.118, 'duration': 4.904}, {'end': 19827.865, 'text': 'So you can see here we have our line charts, our column charts, the combo charts, the spy chart and others.', 'start': 19818.157, 'duration': 9.708}, {'end': 19832.989, 'text': 'Now let me go ahead and format these charts a little more.', 'start': 19828.766, 'duration': 4.223}, {'end': 19835.571, 'text': 'So you can see this looks a bit cluttered.', 'start': 19833.329, 'duration': 2.242}, {'end': 19839.074, 'text': "So let's adjust the labels.", 'start': 19836.352, 'duration': 2.722}], 'summary': 'Dashboard includes line, column, combo charts & spy chart, needs label adjustment.', 'duration': 32.96, 'max_score': 19806.114, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw19806114.jpg'}], 'start': 18570.926, 'title': 'Creating excel revenue dashboards', 'summary': 'Explains the advantages of dashboards for quick outlier detection and correlation analysis, saving time compared to running multiple reports. the demo showcases the use of a sample sales dataset with 1000 rows and 17 columns. it covers the setup of a revenue dashboard, including the generation of pivot tables and charts to analyze total revenue by year, chart formatting, and data labeling. additionally, it demonstrates creating combo and bar charts in excel, including formatting access labels in millions, adding data labels, and visualizing revenue by region and items. it also covers the creation of an excel revenue dashboard with background image insertion, text box formatting, chart addition, and formatting, resulting in a visually appealing and transparently formatted dashboard with logos for each chart.', 'chapters': [{'end': 18623.96, 'start': 18570.926, 'title': 'Dashboard advantages and sales dataset demo', 'summary': 'Explains the advantages of dashboards for quick outlier detection and correlation analysis, saving time compared to running multiple reports. the demo showcases the use of a sample sales dataset with 1000 rows and 17 columns.', 'duration': 53.034, 'highlights': ['The dataset used for the demo consists of 1000 rows and 17 columns, generated using a simulator and containing completely random data.', 'Dashboards offer quick outlier detection and correlation analysis, saving time compared to running multiple reports.']}, {'end': 19084.542, 'start': 18623.96, 'title': 'Creating revenue dashboard', 'summary': 'Covers the setup of a revenue dashboard, including the generation of pivot tables and charts to analyze total revenue by year, chart formatting, and data labeling.', 'duration': 460.582, 'highlights': ['The chapter covers the setup of a revenue dashboard, including the generation of pivot tables and charts to analyze total revenue by year, chart formatting, and data labeling. Setup of a revenue dashboard, generation of pivot tables and charts, analysis of total revenue by year, chart formatting, data labeling', 'The total revenue is actually the product of units sold and unit price. Calculation of total revenue as the product of units sold and unit price', 'The total profit is the difference between total revenue and the total cost. Definition of total profit as the difference between total revenue and the total cost', 'The total cost column is actually the product of units sold and the unit cost. Calculation of total cost as the product of units sold and the unit cost']}, {'end': 19535.112, 'start': 19084.542, 'title': 'Creating combo and bar charts in excel', 'summary': 'Demonstrates creating combo and bar charts in excel, including formatting access labels in millions, adding data labels, and visualizing revenue by region and items.', 'duration': 450.57, 'highlights': ['The chapter demonstrates creating combo and bar charts in Excel. The chapter covers creating combo and bar charts in Excel for visualizing revenue data.', 'Formatting access labels in millions and adding data labels. The chapter explains formatting access labels in millions and adding data labels to the charts for better visualization.', 'Visualizing revenue by region and items. The chapter showcases visualizing revenue by region and items using pivot tables and bar charts.']}, {'end': 20057.147, 'start': 19535.112, 'title': 'Create excel revenue dashboard', 'summary': 'Covers the creation of an excel revenue dashboard with background image insertion, text box formatting, chart addition, and formatting, resulting in a visually appealing and transparently formatted dashboard with logos for each chart.', 'duration': 522.035, 'highlights': ['Inserted a background image by going to the insert tab, selecting pictures, and dragging it to cover a portion of the sheet.', "Formatted the text box for the dashboard title by centralizing it, making it transparent with no outline, changing the font to 'Britannica Bold' with white color, and increasing its size to 30.", 'Added icons to the dashboard by inserting pictures of a store and a cart to make it visually appealing.', 'Added various charts like line charts, column charts, combo charts, and spy chart to the dashboard, formatted each chart by removing the white background, grid lines, and outline, and placing each chart over a transparent rectangle with blue background and white font text for better readability and visibility.']}], 'duration': 1486.221, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw18570926.jpg', 'highlights': ['Dashboards offer quick outlier detection and correlation analysis, saving time compared to running multiple reports.', 'The dataset used for the demo consists of 1000 rows and 17 columns, generated using a simulator and containing completely random data.', 'The chapter covers the setup of a revenue dashboard, including the generation of pivot tables and charts to analyze total revenue by year, chart formatting, and data labeling.', 'The total revenue is actually the product of units sold and unit price. Calculation of total revenue as the product of units sold and unit price.', 'The total profit is the difference between total revenue and the total cost. Definition of total profit as the difference between total revenue and the total cost.', 'The chapter demonstrates creating combo and bar charts in Excel. The chapter covers creating combo and bar charts in Excel for visualizing revenue data.', 'Inserted a background image by going to the insert tab, selecting pictures, and dragging it to cover a portion of the sheet.', "Formatted the text box for the dashboard title by centralizing it, making it transparent with no outline, changing the font to 'Britannica Bold' with white color, and increasing its size to 30.", 'Added icons to the dashboard by inserting pictures of a store and a cart to make it visually appealing.', 'Added various charts like line charts, column charts, combo charts, and spy chart to the dashboard, formatted each chart by removing the white background, grid lines, and outline, and placing each chart over a transparent rectangle with blue background and white font text for better readability and visibility.']}, {'end': 21896.68, 'segs': [{'end': 20145.809, 'src': 'embed', 'start': 20085.827, 'weight': 0, 'content': [{'end': 20091.631, 'text': 'If you want this, you can also format the total year by revenue in terms of millions.', 'start': 20085.827, 'duration': 5.804}, {'end': 20094.893, 'text': 'So the way to do is, you can select this graph.', 'start': 20092.272, 'duration': 2.621}, {'end': 20099.618, 'text': 'right click and go to format access.', 'start': 20095.894, 'duration': 3.724}, {'end': 20108.128, 'text': "here if I scroll down you have numbers and under category I'll select custom.", 'start': 20099.618, 'duration': 8.51}, {'end': 20122.644, 'text': "then I'll select my type, as this format, which is in millions, and you see, here we have successfully formatted our y-axis labels, all right.", 'start': 20108.128, 'duration': 14.516}, {'end': 20127.269, 'text': 'so the next thing is to add slicers and timelines to our dashboard.', 'start': 20122.644, 'duration': 4.625}, {'end': 20132.454, 'text': 'now slicers are used to format your data based on a particular column.', 'start': 20127.91, 'duration': 4.544}, {'end': 20141.302, 'text': 'suppose, if you want to see revenue by certain items, you can add item as a slicer and you can view the entire dashboard.', 'start': 20132.454, 'duration': 8.848}, {'end': 20145.809, 'text': 'similarly, for timelines, you can add date columns.', 'start': 20141.302, 'duration': 4.507}], 'summary': 'Format year revenue in millions, add slicers and timelines to dashboard for data insights.', 'duration': 59.982, 'max_score': 20085.827, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw20085827.jpg'}, {'end': 20265.446, 'src': 'embed', 'start': 20238.658, 'weight': 1, 'content': [{'end': 20245.28, 'text': 'okay, so under report connections, you have all the pivot tables that we created.', 'start': 20238.658, 'duration': 6.622}, {'end': 20248.361, 'text': 'you currently see only one of the pivot table is selected.', 'start': 20245.28, 'duration': 3.081}, {'end': 20251.902, 'text': 'so we need to select all the pivot tables.', 'start': 20248.361, 'duration': 3.541}, {'end': 20256.864, 'text': 'so let me check all the pivot tables present in this workbook and click on.', 'start': 20251.902, 'duration': 4.962}, {'end': 20265.446, 'text': "ok, All right now that we have connected one of our slicers, we'll now connect the other remaining slicers.", 'start': 20256.864, 'duration': 8.582}], 'summary': 'Connecting all pivot tables and slicers in the workbook.', 'duration': 26.788, 'max_score': 20238.658, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw20238658.jpg'}, {'end': 20540.327, 'src': 'embed', 'start': 20511.28, 'weight': 3, 'content': [{'end': 20515.103, 'text': 'alright. so let me go ahead and create my first pivot table.', 'start': 20511.28, 'duration': 3.823}, {'end': 20520.408, 'text': "so I'll create a new worksheet.", 'start': 20515.103, 'duration': 5.305}, {'end': 20526.252, 'text': "this time I'm going to create a line chart to visualize the profit for each year.", 'start': 20520.408, 'duration': 5.844}, {'end': 20535.12, 'text': "so I'll drag my total profit column to values and my order year to rows.", 'start': 20526.252, 'duration': 8.868}, {'end': 20540.327, 'text': 'so here you can see we have our pivot table ready.', 'start': 20535.12, 'duration': 5.207}], 'summary': 'Creating pivot table and line chart to visualize profit by year.', 'duration': 29.047, 'max_score': 20511.28, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw20511280.jpg'}, {'end': 20918.423, 'src': 'embed', 'start': 20889.464, 'weight': 4, 'content': [{'end': 20892.147, 'text': 'Based on this pivot table, let me create my pivot chart.', 'start': 20889.464, 'duration': 2.683}, {'end': 20896.554, 'text': "So I'll go to recommended charts and I'm going to create a line chart.", 'start': 20892.388, 'duration': 4.166}, {'end': 20898.396, 'text': 'I close this.', 'start': 20897.836, 'duration': 0.56}, {'end': 20912.358, 'text': 'you see here, based on this chart, you can tell the profit generated with online sales were actually lower than that of offline.', 'start': 20900.168, 'duration': 12.19}, {'end': 20918.423, 'text': 'so here the blue line represents offline profit and the orange line represents online profit.', 'start': 20912.358, 'duration': 6.065}], 'summary': 'Online sales generated lower profit than offline sales.', 'duration': 28.959, 'max_score': 20889.464, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw20889464.jpg'}, {'end': 21026.702, 'src': 'embed', 'start': 20996.732, 'weight': 5, 'content': [{'end': 21001.334, 'text': "So I'll first go to insert, click on pivot table and click on OK.", 'start': 20996.732, 'duration': 4.602}, {'end': 21008.938, 'text': "So I'll drag my sales channel under rows and then we'll have the total profit column under values.", 'start': 21002.555, 'duration': 6.383}, {'end': 21014.281, 'text': 'so this is my simple pivot table.', 'start': 21011.016, 'duration': 3.265}, {'end': 21017.486, 'text': "now let's create our pivot chart, which is going to be a pie chart.", 'start': 21014.281, 'duration': 3.205}, {'end': 21022.981, 'text': 'let me explore the other types of pie charts we have.', 'start': 21018.22, 'duration': 4.761}, {'end': 21025.562, 'text': "okay, so i'm going to select a donut chart here.", 'start': 21022.981, 'duration': 2.581}, {'end': 21026.702, 'text': 'i click on.', 'start': 21025.562, 'duration': 1.14}], 'summary': 'Created a pivot table with sales channel and total profit, then explored donut chart types.', 'duration': 29.97, 'max_score': 20996.732, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw20996732.jpg'}, {'end': 21151.853, 'src': 'embed', 'start': 21119.488, 'weight': 6, 'content': [{'end': 21122.41, 'text': "so I'll put my revenue at the top.", 'start': 21119.488, 'duration': 2.922}, {'end': 21127.413, 'text': "then I'm going to select item type under rows.", 'start': 21122.41, 'duration': 5.003}, {'end': 21129.654, 'text': 'so here is my pivot table.', 'start': 21127.413, 'duration': 2.241}, {'end': 21136.919, 'text': 'based on this pivot table, let me now create a combo chart so you can see the preview of the chart.', 'start': 21129.654, 'duration': 7.265}, {'end': 21142.789, 'text': 'the blue bars represent the total revenue and the orange line represents the total profit.', 'start': 21138.047, 'duration': 4.742}, {'end': 21144.33, 'text': "I'll click on.", 'start': 21142.789, 'duration': 1.541}, {'end': 21148.051, 'text': 'OK, let me close this.', 'start': 21144.33, 'duration': 3.721}, {'end': 21151.853, 'text': "first let's remove the field buttons.", 'start': 21148.051, 'duration': 3.802}], 'summary': 'Demonstrating pivot table and combo chart creation with revenue and profit data.', 'duration': 32.365, 'max_score': 21119.488, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw21119488.jpg'}, {'end': 21367.54, 'src': 'embed', 'start': 21337.493, 'weight': 7, 'content': [{'end': 21347.856, 'text': 'okay, so here on my profit dashboard, I have added all the charts and have aligned and reshaped it so that it looks good.', 'start': 21337.493, 'duration': 10.363}, {'end': 21351.057, 'text': "I've also made some formatting.", 'start': 21347.856, 'duration': 3.201}, {'end': 21356.378, 'text': "for example, I've reduced the size of the chart title.", 'start': 21351.057, 'duration': 5.321}, {'end': 21363.779, 'text': 'now let me go ahead and show you a few more formatting that we also did for the revenue dashboard.', 'start': 21356.378, 'duration': 7.401}, {'end': 21367.54, 'text': "first, let's remove the white background from all the charts.", 'start': 21363.779, 'duration': 3.761}], 'summary': 'Formatted profit dashboard with charts and resized chart title. removed white background from revenue dashboard.', 'duration': 30.047, 'max_score': 21337.493, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw21337493.jpg'}, {'end': 21575.483, 'src': 'embed', 'start': 21548.612, 'weight': 8, 'content': [{'end': 21552.494, 'text': 'Now you can filter this in terms of years, quarters, months or days.', 'start': 21548.612, 'duration': 3.882}, {'end': 21554.654, 'text': 'Let me just select year now.', 'start': 21553.114, 'duration': 1.54}, {'end': 21560.097, 'text': 'So I have years from 2010 till 2017.', 'start': 21555.355, 'duration': 4.742}, {'end': 21564.538, 'text': "Let me just squeeze this and I'll place it somewhere here on the right.", 'start': 21560.097, 'duration': 4.441}, {'end': 21570.541, 'text': 'Now let me go ahead and create a few slicers for my profit dashboard.', 'start': 21567.12, 'duration': 3.421}, {'end': 21572.622, 'text': 'So I have selected one of the charts.', 'start': 21571.061, 'duration': 1.561}, {'end': 21575.483, 'text': "Under insert, I'll click on slicer.", 'start': 21573.642, 'duration': 1.841}], 'summary': 'Data can be filtered by year, with 2010-2017 available. slicers are being created for the profit dashboard.', 'duration': 26.871, 'max_score': 21548.612, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw21548612.jpg'}, {'end': 21641.236, 'src': 'embed', 'start': 21612.186, 'weight': 9, 'content': [{'end': 21618.768, 'text': "so I'm going to click on the multiple select option and go to report connections.", 'start': 21612.186, 'duration': 6.582}, {'end': 21625.77, 'text': "here I'm going to select all the pivot tables that are related to profit.", 'start': 21618.768, 'duration': 7.002}, {'end': 21631.912, 'text': 'so here I have selected four and I need one more, which is pivot table number 10.', 'start': 21625.77, 'duration': 6.142}, {'end': 21633.353, 'text': 'I click on OK.', 'start': 21631.912, 'duration': 1.441}, {'end': 21637.754, 'text': "similarly, let's create or connect my region filter to all the pivot tables.", 'start': 21633.353, 'duration': 4.401}, {'end': 21641.236, 'text': 'so I right click, go to report connections.', 'start': 21637.754, 'duration': 3.482}], 'summary': 'Select 5 pivot tables related to profit and connect region filter to all pivot tables.', 'duration': 29.05, 'max_score': 21612.186, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw21612186.jpg'}, {'end': 21782.486, 'src': 'embed', 'start': 21747.754, 'weight': 10, 'content': [{'end': 21756.537, 'text': "now, if i select another year, let's say 2013, i can just drag this to the right.", 'start': 21747.754, 'duration': 8.783}, {'end': 21762.808, 'text': 'and now you can see our profit by year and sales channel.', 'start': 21756.537, 'duration': 6.271}, {'end': 21769.754, 'text': 'for offline and online, you can see the map or the line chart for total profit by year.', 'start': 21762.808, 'duration': 6.946}, {'end': 21776.881, 'text': 'so in 2012 it was 51 million and then it went up to 54 million in 2013.', 'start': 21769.754, 'duration': 7.127}, {'end': 21779.963, 'text': 'similarly, our map has also changed.', 'start': 21776.881, 'duration': 3.082}, {'end': 21782.486, 'text': 'now this is a sort of an information that we have.', 'start': 21779.963, 'duration': 2.523}], 'summary': 'The total profit increased from 51 million in 2012 to 54 million in 2013, as shown in the map and line chart.', 'duration': 34.732, 'max_score': 21747.754, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw21747754.jpg'}], 'start': 20057.408, 'title': 'Excel dashboard creation', 'summary': 'Covers revenue data formatting, adding slicers and timelines to a dashboard, creating revenue and profit dashboards in excel, and demonstrates the creation of several reports and charts, including timelines and slicers for data analysis, with the highest profit generated in 2013 amounting to 54 million.', 'chapters': [{'end': 20122.644, 'start': 20057.408, 'title': 'Revenue formatting for graphs', 'summary': 'Demonstrates how to format revenue data in a graph, including the color of bars, labels, and axis values, all in terms of millions.', 'duration': 65.236, 'highlights': ['The chapter explains how to format revenue data in a graph, including the color of bars, labels, and axis values, all in terms of millions.', 'Demonstrates the process of formatting the y-axis labels to display revenue in millions by selecting the graph, right-clicking, and accessing the format axis option.']}, {'end': 20348.642, 'start': 20122.644, 'title': 'Adding slicers and timelines to dashboard', 'summary': 'Explains how to add slicers and timelines to a dashboard, allowing users to filter data based on specific columns, and connect pivot tables to the slicers, with a demonstration of organizing and arranging the slicers for effective dashboard usage.', 'duration': 225.998, 'highlights': ['Demonstrates adding slicers and timelines to a dashboard, allowing users to filter data based on specific columns. Slicers are used to format data based on a particular column, such as revenue by items. Timelines allow filtering by date columns for specific time periods.', 'Connecting pivot tables to the slicers for filtering the dashboard data. Demonstrates connecting all the pivot tables to the slicers, enabling filtering across the entire dashboard.', 'Organizing and arranging the slicers for effective dashboard usage. Shows the process of arranging and organizing slicers to make the dashboard user-friendly and visually appealing.']}, {'end': 20766.914, 'start': 20348.642, 'title': 'Excel revenue and profit dashboards', 'summary': 'Covers the process of creating revenue and profit dashboards in excel, demonstrating the filtering and visualization of revenue by item type, country, region, and sales channel, as well as the creation of pivot tables and pivot charts to showcase profit by year and country.', 'duration': 418.272, 'highlights': ['The chapter demonstrates filtering and visualization of revenue by item type, country, region, and sales channel in Excel. The transcript illustrates the process of filtering and visualizing revenue by item type (beverages), country (India), region, and sales channel (offline) in Excel, showcasing the ability to dynamically display revenue data based on various criteria.', 'Creation of pivot tables and pivot charts to showcase profit by year and country. The chapter outlines the creation of pivot tables and pivot charts to visualize profit by year and country, including sorting the data to identify the years with the highest and lowest profit, demonstrating the use of charts and formatting options to present the total profit by year.']}, {'end': 21493.623, 'start': 20766.914, 'title': 'Excel profit dashboard creation', 'summary': 'Demonstrates the creation of several reports and charts in excel, including a profit map, pivot tables, pivot charts, and a profit dashboard, with a focus on profit analysis by country, year, sales channel, and items, concluding with the addition of background images and formatting for the profit dashboard.', 'duration': 726.709, 'highlights': ['The chapter demonstrates the creation of several reports and charts in Excel, including a profit map, pivot tables, pivot charts, and a profit dashboard. The speaker explains the process of creating a profit map with color-coded shading to represent varying profit levels, connecting the map to the original data source, creating pivot tables and charts to analyze profit by year, sales channel, and items, and finally, building a profit dashboard.', 'The pivot table and pivot chart analysis reveal insights such as the comparison of online and offline profits by year, with a notable exception in 2012. The pivot table and pivot chart analysis show the comparison of profits generated with online and offline sales, highlighting a higher online profit than offline profit in 2012, providing insights into the performance of different sales channels over time.', 'The creation of a pie chart based on profit by sales channel is demonstrated, utilizing a donut chart type and formatting for enhanced visualization. The speaker demonstrates the creation of a pie chart based on profit by sales channel, utilizing a donut chart type, removing field buttons and legends, adding data labels, and formatting the labels to display profit in millions for enhanced visualization.', 'The process of creating a combo chart depicting revenue and profit by items is explained, including the formatting of chart elements and color adjustments. The chapter explains the process of creating a combo chart to visualize revenue and profit by items, including the formatting of chart elements, removal of field buttons and legends, addition of a chart title, and color adjustments for improved visualization.', 'The addition of background images, title, and formatting for the profit dashboard is demonstrated, including the alignment and reshaping of charts for a cohesive presentation. The speaker demonstrates the addition of background images, title, and formatting for the profit dashboard, including the alignment and reshaping of charts, reduction of chart title size, removal of white backgrounds, formatting of chart elements, and the addition of a blue background for improved aesthetics.']}, {'end': 21896.68, 'start': 21494.404, 'title': 'Creating timelines and slicers in excel', 'summary': 'Explains how to create timelines and slicers in excel to analyze data, allowing users to filter and visualize information by year, sales channel, representatives, and countries, and understand profit trends over the years, with the highest profit generated in 2013 amounting to 54 million.', 'duration': 402.276, 'highlights': ['Users can create timelines in Excel based on date columns, such as order date, to visualize data by year, allowing filtering by years, quarters, months, or days. Date-based visualization, filtering by years, quarters, months, or days.', 'Slicers can be used to filter and connect pivot tables based on different categories such as regions, countries, sales channels, and representatives, allowing for detailed analysis and visualization of profit data. Filtering and connecting pivot tables based on different categories for detailed profit analysis.', 'The demonstration showcases the ability to analyze profit trends over the years, with the highest profit generated in 2013 amounting to 54 million. Highlighting the highest profit generated in 2013 amounting to 54 million.']}], 'duration': 1839.272, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw20057408.jpg', 'highlights': ['Demonstrates the process of formatting the y-axis labels to display revenue in millions by selecting the graph, right-clicking, and accessing the format axis option.', 'Connecting pivot tables to the slicers for filtering the dashboard data. Demonstrates connecting all the pivot tables to the slicers, enabling filtering across the entire dashboard.', 'Organizing and arranging the slicers for effective dashboard usage. Shows the process of arranging and organizing slicers to make the dashboard user-friendly and visually appealing.', 'The chapter outlines the creation of pivot tables and pivot charts to visualize profit by year and country, including sorting the data to identify the years with the highest and lowest profit, demonstrating the use of charts and formatting options to present the total profit by year.', 'The pivot table and pivot chart analysis show the comparison of profits generated with online and offline sales, highlighting a higher online profit than offline profit in 2012, providing insights into the performance of different sales channels over time.', 'The speaker demonstrates the creation of a pie chart based on profit by sales channel, utilizing a donut chart type, removing field buttons and legends, adding data labels, and formatting the labels to display profit in millions for enhanced visualization.', 'The chapter explains the process of creating a combo chart to visualize revenue and profit by items, including the formatting of chart elements, removal of field buttons and legends, addition of a chart title, and color adjustments for improved visualization.', 'The speaker demonstrates the addition of background images, title, and formatting for the profit dashboard, including the alignment and reshaping of charts, reduction of chart title size, removal of white backgrounds, formatting of chart elements, and the addition of a blue background for improved aesthetics.', 'Date-based visualization, filtering by years, quarters, months, or days.', 'Filtering and connecting pivot tables based on different categories for detailed profit analysis.', 'Highlighting the highest profit generated in 2013 amounting to 54 million.']}, {'end': 23799.524, 'segs': [{'end': 21962.848, 'src': 'embed', 'start': 21896.68, 'weight': 0, 'content': [{'end': 21900.344, 'text': 'now you can also select multiple countries.', 'start': 21896.68, 'duration': 3.664}, {'end': 21904.348, 'text': "let's say I want to know for France as well.", 'start': 21900.344, 'duration': 4.004}, {'end': 21908.901, 'text': 'so my map will change accordingly.', 'start': 21904.348, 'duration': 4.553}, {'end': 21918.106, 'text': 'so now I have United Kingdom and France selected and the other charts present in my dashboard change accordingly.', 'start': 21908.901, 'duration': 9.205}, {'end': 21921.768, 'text': 'now I have my country selected as India.', 'start': 21918.106, 'duration': 3.662}, {'end': 21929.449, 'text': 'you can see the map of India here and these were the respective profit values.', 'start': 21921.768, 'duration': 7.681}, {'end': 21933.793, 'text': 'now, one thing to note here is this is actually not millions.', 'start': 21929.449, 'duration': 4.344}, {'end': 21936.595, 'text': 'that should be in k, that is thousands.', 'start': 21933.793, 'duration': 2.802}, {'end': 21942.06, 'text': 'so please mark this as thousand and not in millions.', 'start': 21936.595, 'duration': 5.465}, {'end': 21945.123, 'text': 'even for this, this is actually k and not million.', 'start': 21942.06, 'duration': 3.063}, {'end': 21947.524, 'text': 'all right.', 'start': 21946.263, 'duration': 1.261}, {'end': 21956.046, 'text': 'so we have successfully created our second dashboard that is on profit.', 'start': 21947.524, 'duration': 8.522}, {'end': 21962.848, 'text': 'let me just resize this a bit and we place it where it was earlier.', 'start': 21956.046, 'duration': 6.802}], 'summary': 'Dashboard updated with multiple countries selected, profit values corrected to thousands.', 'duration': 66.168, 'max_score': 21896.68, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw21896680.jpg'}, {'end': 22129.086, 'src': 'embed', 'start': 22064.227, 'weight': 3, 'content': [{'end': 22069.593, 'text': 'You can always go ahead and play with different themes, colors, fonts and effects.', 'start': 22064.227, 'duration': 5.366}, {'end': 22070.754, 'text': 'All right.', 'start': 22070.434, 'duration': 0.32}, {'end': 22076.52, 'text': 'so in this demo we saw how to create a revenue dashboard.', 'start': 22071.596, 'duration': 4.924}, {'end': 22085.127, 'text': 'so we created line charts, this combo chart, pie chart, horizontal and vertical bar charts,', 'start': 22076.52, 'duration': 8.607}, {'end': 22096.937, 'text': 'and then we learned how to add slicers and connect it to different pivot tables, and we filtered our data to see revenue as well as profit by items,', 'start': 22085.127, 'duration': 11.81}, {'end': 22106.926, 'text': 'by countries, by different regions, sales channel learned how to create a map and lots more.', 'start': 22096.937, 'duration': 9.989}, {'end': 22114.313, 'text': "let's quickly see some more examples of doing data analysis using excel,", 'start': 22106.926, 'duration': 7.387}, {'end': 22121.38, 'text': 'and for that we can use some inbuilt add-ins which can be added to our excel sheet.', 'start': 22114.313, 'duration': 7.067}, {'end': 22129.086, 'text': 'so, for example, if you would want to do a descriptive analytics or descriptive analysis on your data, say, for example,', 'start': 22121.38, 'duration': 7.706}], 'summary': 'Demo on creating a revenue dashboard with line, combo, pie, bar charts, slicers, pivot tables, and map in excel.', 'duration': 64.859, 'max_score': 22064.227, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw22064227.jpg'}, {'end': 22667.128, 'src': 'embed', 'start': 22639.451, 'weight': 6, 'content': [{'end': 22647.314, 'text': 'look at the frequency of values might be, also look at the defects, and for which we can use something like Pareto chart,', 'start': 22639.451, 'duration': 7.863}, {'end': 22653.256, 'text': 'so we can go for histogram and that basically gives us the frequency of values.', 'start': 22647.314, 'duration': 5.942}, {'end': 22654.777, 'text': 'now, how do we do that?', 'start': 22653.256, 'duration': 1.521}, {'end': 22662.96, 'text': 'so we have already added the add-in, which is data analysis earlier, so we can just use the same thing again.', 'start': 22654.777, 'duration': 8.183}, {'end': 22666.148, 'text': 'Here we would want to create a histogram.', 'start': 22663.587, 'duration': 2.561}, {'end': 22667.128, 'text': "So let's say OK.", 'start': 22666.188, 'duration': 0.94}], 'summary': 'Analyze frequency of values using pareto chart and histogram for data analysis.', 'duration': 27.677, 'max_score': 22639.451, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw22639451.jpg'}, {'end': 22922.124, 'src': 'embed', 'start': 22894.511, 'weight': 7, 'content': [{'end': 22899.113, 'text': "And then I'm interested in finding out the correlation of temperature and units sold.", 'start': 22894.511, 'duration': 4.602}, {'end': 22900.574, 'text': "So let's select this.", 'start': 22899.433, 'duration': 1.141}, {'end': 22903.895, 'text': 'And that basically gives me a range of values.', 'start': 22901.254, 'duration': 2.641}, {'end': 22908.558, 'text': 'It gives me the correlation value, which is 0.2859.', 'start': 22904.156, 'duration': 4.402}, {'end': 22909.378, 'text': 'Say OK.', 'start': 22908.558, 'duration': 0.82}, {'end': 22910.619, 'text': "And that's your value.", 'start': 22909.678, 'duration': 0.941}, {'end': 22915.401, 'text': 'So similarly, we can do it for temperature and price ice cream.', 'start': 22911.459, 'duration': 3.942}, {'end': 22919.603, 'text': "So let's go for correlation.", 'start': 22915.861, 'duration': 3.742}, {'end': 22922.124, 'text': "So that's the function we are interested in.", 'start': 22920.183, 'duration': 1.941}], 'summary': 'Correlation of temperature and units sold is 0.2859, further analysis needed.', 'duration': 27.613, 'max_score': 22894.511, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw22894511.jpg'}, {'end': 23049.549, 'src': 'embed', 'start': 23015.994, 'weight': 8, 'content': [{'end': 23026.557, 'text': 'that basically gives you a correlation of your different variables and what are the values, and we can check these values based on the values,', 'start': 23015.994, 'duration': 10.563}, {'end': 23027.599, 'text': 'what we have here.', 'start': 23026.557, 'duration': 1.042}, {'end': 23038.601, 'text': 'so we have basically temperature and price of ice cream, and that basically shows me 0.96149.', 'start': 23027.599, 'duration': 11.002}, {'end': 23041.083, 'text': 'you have temperature and units sold.', 'start': 23038.601, 'duration': 2.482}, {'end': 23043.645, 'text': 'so you have 0.2859.', 'start': 23041.083, 'duration': 2.562}, {'end': 23049.549, 'text': 'now you can also look at units sold and say, for example, price of ice cream.', 'start': 23043.645, 'duration': 5.904}], 'summary': 'Correlation analysis reveals strong positive relationship between temperature and ice cream price (0.96149) and weaker relationship between temperature and units sold (0.2859).', 'duration': 33.555, 'max_score': 23015.994, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw23015994.jpg'}, {'end': 23379.964, 'src': 'embed', 'start': 23327.435, 'weight': 9, 'content': [{'end': 23336.561, 'text': 'So we can use Excel to do a simple sampling and we can choose whether we would want to go for periodic sampling or random sampling.', 'start': 23327.435, 'duration': 9.126}, {'end': 23338.062, 'text': "Let's continue learning.", 'start': 23337.001, 'duration': 1.061}, {'end': 23343.417, 'text': 'Excel and how Excel can be used for data analysis.', 'start': 23338.934, 'duration': 4.483}, {'end': 23352.084, 'text': "Now we have seen sampling, histogram, correlation, descriptive stats, and now also let's look at regression.", 'start': 23343.537, 'duration': 8.547}, {'end': 23353.345, 'text': 'Now, what is regression?', 'start': 23352.324, 'duration': 1.021}, {'end': 23363.372, 'text': 'So when you talk about statistical modeling, regression analysis is used to estimate the relationship between two or more variables.', 'start': 23353.885, 'duration': 9.487}, {'end': 23376.141, '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': 23363.932, 'duration': 12.209}, {'end': 23379.964, 'text': 'so, for example, if we look at our data here, we have temperature,', 'start': 23376.141, 'duration': 3.823}], 'summary': 'Using excel for data analysis including sampling, histogram, correlation, descriptive stats, and regression.', 'duration': 52.529, 'max_score': 23327.435, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw23327435.jpg'}, {'end': 23486.679, 'src': 'embed', 'start': 23432.697, 'weight': 11, 'content': [{'end': 23439.739, 'text': "So when you talk about your x, That's your independent variable or we can say predictor or explanatory.", 'start': 23432.697, 'duration': 7.042}, {'end': 23448.322, 'text': 'So, when you talk about your independent variables or, as we say, predictors or explanatory variables,', 'start': 23440.4, 'duration': 7.922}, {'end': 23454.584, 'text': 'these are the factors that might influence the dependent variable.', 'start': 23448.322, 'duration': 6.262}, {'end': 23458.885, 'text': "And when you talk about dependent variable, that's your response or target.", 'start': 23455.184, 'duration': 3.701}, {'end': 23462.366, 'text': "That's the main factor you're trying to understand or predict.", 'start': 23459.385, 'duration': 2.981}, {'end': 23473.833, 'text': 'so we can plot a scatter plot to see relationship between variables, if they are linearly related, are they positively related, negatively related,', 'start': 23463.007, 'duration': 10.826}, {'end': 23475.013, 'text': 'and so on.', 'start': 23473.833, 'duration': 1.18}, {'end': 23486.679, 'text': 'we can also get values such as r square, which is coefficient of determination, regression line, and then analyze the relationship between variables.', 'start': 23475.013, 'duration': 11.666}], 'summary': 'Independent variables influence dependent variable; analyze with scatter plots and r square.', 'duration': 53.982, 'max_score': 23432.697, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw23432697.jpg'}, {'end': 23649.834, 'src': 'embed', 'start': 23622.564, 'weight': 13, 'content': [{'end': 23627.548, 'text': 'If there are residuals, that is points which do not fall on the regression line.', 'start': 23622.564, 'duration': 4.984}, {'end': 23629.71, 'text': 'And in terms of statistics.', 'start': 23628.188, 'duration': 1.522}, {'end': 23637.447, 'text': 'when we talk about statistics, we differentiate between a simple and multiple linear regression.', 'start': 23630.743, 'duration': 6.704}, {'end': 23642.05, 'text': 'so you have something called a simple linear regression models,', 'start': 23637.447, 'duration': 4.603}, {'end': 23649.834, 'text': 'which basically gives you the relationship between dependent variable and one independent variable using a linear function,', 'start': 23642.05, 'duration': 7.784}], 'summary': 'Explains residuals and differentiates between simple and multiple linear regression.', 'duration': 27.27, 'max_score': 23622.564, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw23622564.jpg'}], 'start': 21896.68, 'title': 'Excel data analysis techniques', 'summary': 'Covers creating interactive dashboards, pivot tables, changing dashboard themes, data analysis techniques including histograms, correlation analysis, pareto charts, and regression analysis in excel, with examples and quantitative insights.', 'chapters': [{'end': 21962.848, 'start': 21896.68, 'title': 'Interactive dashboard creation', 'summary': 'Illustrates the creation of an interactive dashboard with the ability to select multiple countries, displaying profit values in thousands and not millions.', 'duration': 66.168, 'highlights': ['The dashboard allows the selection of multiple countries, enabling dynamic changes in the displayed data.', 'The profit values displayed are in thousands (k) and not in millions, providing accurate representation of the data.', 'Demonstrates the creation of a second dashboard focusing on profit, offering a user-friendly resizing feature for enhanced visualization.']}, {'end': 22639.451, 'start': 21962.848, 'title': 'Creating pivot tables and changing dashboard themes', 'summary': 'Demonstrates creating pivot tables and pivot charts, formatting them, and changing the look and feel of the dashboard by selecting different themes, impacting the revenue dashboard. it also covers creating different charts, adding slicers, connecting them to pivot tables, and using inbuilt add-ins for data analysis in excel.', 'duration': 676.603, 'highlights': ['The chapter demonstrates creating pivot tables and pivot charts and formatting them, impacting the revenue dashboard. The demonstration includes creating different pivot tables and pivot charts and formatting them based on the requirement. Changing the theme also impacts the revenue dashboard.', 'The chapter covers creating different charts, adding slicers, connecting them to pivot tables, and using inbuilt add-ins for data analysis in Excel. The chapter covers creating line charts, combo chart, pie chart, horizontal and vertical bar charts, adding slicers, connecting them to different pivot tables, and using inbuilt add-ins for data analysis.', 'The chapter also demonstrates using inbuilt add-ins for data analysis in Excel, such as descriptive statistics and frequency analysis. The demonstration includes using inbuilt add-ins like analysis tool pack and solver add-in for descriptive statistics and frequency analysis on the data.']}, {'end': 23015.994, 'start': 22639.451, 'title': 'Data analysis techniques', 'summary': 'Provides guidance on creating histograms and performing correlation analysis using data analysis add-in. it emphasizes the use of pareto charts and correlation functions to analyze frequency of values and relationships between variables.', 'duration': 376.543, 'highlights': ['The chapter demonstrates the creation of histograms using data analysis add-in, emphasizing the use of Pareto charts to analyze the frequency of values within different ranges, with a maximum frequency of 5. The demonstration includes selecting input and bin ranges, providing output range, and obtaining visual histograms to analyze frequency of values, with a maximum frequency of 5 within a particular range.', 'It explains the process of using correlation functions to determine the relationship between variables, such as temperature and units sold, with a correlation value of 0.2859, and temperature and price of ice cream, to analyze linear relationships. The explanation covers the use of correlation functions to calculate the correlation value between variables, providing a correlation value of 0.2859 for temperature and units sold, and highlighting the process for determining linear relationships between variables.']}, {'end': 23326.915, 'start': 23015.994, 'title': 'Data analysis techniques: correlation and sampling', 'summary': 'Discusses the use of correlation to determine relationships between variables with examples of correlation values such as 0.96149 for temperature and price of ice cream, and explains periodic and random sampling techniques using excel.', 'duration': 310.921, 'highlights': ['The correlation values are 0.96149 for temperature and price of ice cream, and 0.2859 for temperature and units sold, showcasing the strong relationship between temperature and the price of ice cream.', 'Explanation of using Excel to perform periodic sampling, such as obtaining every second value from a set of data, and random sampling, with a demonstration of selecting three random temperature values from a dataset of seven values.', 'Demonstration of using Excel for periodic sampling by selecting a frequency of two values and obtaining the corresponding sampled values, exemplifying the practical application of periodic sampling techniques.']}, {'end': 23799.524, 'start': 23327.435, 'title': 'Using excel for regression analysis', 'summary': 'Covers the use of excel for regression analysis, explaining the concept of regression, the role of independent and dependent variables, and the generation of scatter plots for visualizing relationships between variables.', 'duration': 472.089, 'highlights': ['Regression analysis is used to estimate the relationship between two or more variables. Regression analysis estimates the relationship between variables.', 'Understanding the distinction between independent and dependent variables is crucial in regression analysis. The distinction between independent and dependent variables is crucial in regression analysis.', 'The process of creating scatter plots in Excel for visualizing the relationships between variables is explained. The process of creating scatter plots in Excel for visualizing relationships between variables is explained.', 'Differentiating between simple and multiple linear regression is essential in statistical analysis. Understanding the difference between simple and multiple linear regression is essential in statistical analysis.']}], 'duration': 1902.844, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw21896680.jpg', 'highlights': ['The dashboard allows the selection of multiple countries, enabling dynamic changes in the displayed data.', 'The profit values displayed are in thousands (k) and not in millions, providing accurate representation of the data.', 'Demonstrates the creation of a second dashboard focusing on profit, offering a user-friendly resizing feature for enhanced visualization.', 'The chapter demonstrates creating pivot tables and pivot charts and formatting them, impacting the revenue dashboard.', 'The chapter covers creating different charts, adding slicers, connecting them to pivot tables, and using inbuilt add-ins for data analysis in Excel.', 'The chapter also demonstrates using inbuilt add-ins for data analysis in Excel, such as descriptive statistics and frequency analysis.', 'The chapter demonstrates the creation of histograms using data analysis add-in, emphasizing the use of Pareto charts to analyze the frequency of values within different ranges, with a maximum frequency of 5.', 'It explains the process of using correlation functions to determine the relationship between variables, such as temperature and units sold, with a correlation value of 0.2859, and temperature and price of ice cream, to analyze linear relationships.', 'The correlation values are 0.96149 for temperature and price of ice cream, and 0.2859 for temperature and units sold, showcasing the strong relationship between temperature and the price of ice cream.', 'Explanation of using Excel to perform periodic sampling, such as obtaining every second value from a set of data, and random sampling, with a demonstration of selecting three random temperature values from a dataset of seven values.', 'Regression analysis is used to estimate the relationship between two or more variables.', 'Understanding the distinction between independent and dependent variables is crucial in regression analysis.', 'The process of creating scatter plots in Excel for visualizing the relationships between variables is explained.', 'Differentiating between simple and multiple linear regression is essential in statistical analysis.']}, {'end': 24898.322, 'segs': [{'end': 23957.17, 'src': 'embed', 'start': 23918.676, 'weight': 0, 'content': [{'end': 23930.599, 'text': 'So it is basically y, when you say y equals, you can say bx plus a plus c.', 'start': 23918.676, 'duration': 11.923}, {'end': 23937.169, 'text': 'And this would be normally the mathematical equation for a linear regression.', 'start': 23930.599, 'duration': 6.57}, {'end': 23946.976, 'text': 'where x is your independent variable, y is your dependent variable, your,', 'start': 23937.169, 'duration': 9.807}, {'end': 23957.17, 'text': 'a is the y-intercept which is expected mean value of y when all x variables are equal to zero.', 'start': 23946.976, 'duration': 10.194}], 'summary': 'Linear regression equation: y=bx+a+c. a is y-intercept, expected mean value of y at x=0.', 'duration': 38.494, 'max_score': 23918.676, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw23918676.jpg'}, {'end': 24152.181, 'src': 'embed', 'start': 24123.516, 'weight': 1, 'content': [{'end': 24128.301, 'text': 'then we have chosen temperature and price of ice cream both.', 'start': 24123.516, 'duration': 4.785}, {'end': 24131.363, 'text': 'Now we can choose the labels, which we have done.', 'start': 24129.161, 'duration': 2.202}, {'end': 24138.069, 'text': 'We can choose your output range which is basically on this sheet and then we can select the residuals.', 'start': 24131.903, 'duration': 6.166}, {'end': 24141.952, 'text': 'So that basically gives you your residuals.', 'start': 24138.889, 'duration': 3.063}, {'end': 24146.076, 'text': 'Now we can also get your line fit plots which you were seeing earlier.', 'start': 24142.412, 'duration': 3.664}, {'end': 24152.181, 'text': 'You can also go for your normal probability plots and then basically just say okay.', 'start': 24146.836, 'duration': 5.345}], 'summary': 'Selected temperature and price of ice cream, chose labels, output range, and residuals, and generated line fit and normal probability plots.', 'duration': 28.665, 'max_score': 24123.516, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw24123516.jpg'}, {'end': 24465.269, 'src': 'embed', 'start': 24415.866, 'weight': 2, 'content': [{'end': 24422.169, 'text': 'So it basically means 45% of our values fit the regression analysis model.', 'start': 24415.866, 'duration': 6.303}, {'end': 24432.154, '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': 24422.77, 'duration': 9.384}, {'end': 24442.576, 'text': 'so normally if you have r squared value higher then that is considered as a good fit And here we have considered multiple variables.', 'start': 24433.59, 'duration': 8.986}, {'end': 24451.801, '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': 24443.297, 'duration': 8.504}, {'end': 24460.006, 'text': 'So you would want to use this value instead of R square for shows the precision of your regression analysis.', 'start': 24452.402, 'duration': 7.604}, {'end': 24465.269, 'text': 'So the smaller the value, the more certain you can be about your regression equation.', 'start': 24460.526, 'duration': 4.743}], 'summary': '45% of values fit regression model, adjusted r square shows precision.', 'duration': 49.403, 'max_score': 24415.866, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw24415866.jpg'}, {'end': 24708.12, 'src': 'embed', 'start': 24679.955, 'weight': 4, 'content': [{'end': 24684.077, 'text': 'now when we look at the residual output which we are seeing here.', 'start': 24679.955, 'duration': 4.122}, {'end': 24688.859, 'text': 'so that gives you your observations, how many observations we have.', 'start': 24684.077, 'duration': 4.782}, {'end': 24695.102, 'text': 'it shows what is the predicted unit sold and then it also gives you your residuals.', 'start': 24688.859, 'duration': 6.243}, {'end': 24702.065, 'text': 'so we can compare the estimated and actual number of sold umbrellas based on the observation.', 'start': 24695.102, 'duration': 6.963}, {'end': 24708.12, 'text': "so we have 11.209 here and if you compare, that's basically 12.", 'start': 24702.065, 'duration': 6.055}], 'summary': 'Analyzing residual output, we have 11.209 observations and 12 predicted units sold, enabling comparison with actual sales.', 'duration': 28.165, 'max_score': 24679.955, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw24679955.jpg'}], 'start': 23799.684, 'title': 'Regression analysis fundamentals', 'summary': 'Covers regression analysis basics, excel implementation, understanding multiple r, r square, adjusted r square, and anova values, and simple regression analysis overview for data analysis and prediction.', 'chapters': [{'end': 24014.135, 'start': 23799.684, 'title': 'Regression analysis basics', 'summary': 'Explains the basics of regression analysis, including the regression equation y = bx + a + c, the meaning of coefficients like intercept and slope, and the importance of the error term. it also demonstrates the creation and customization of a scatter plot for data analysis.', 'duration': 214.451, 'highlights': ['The chapter explains the regression equation y = bx + a + c, where x is the independent variable, y is the dependent variable, a is the y-intercept, b is the slope of the regression line, and c is the error term.', 'It demonstrates the creation and customization of a scatter plot for data analysis, allowing for the labeling of x-axis, y-axis, and the chart title, providing a clear reference for the relationship between dependent and independent variables.', 'The importance of the error term is highlighted, emphasizing its role as the difference between the actual and predicted values of the dependent variable, reflecting the imprecision of predictors in real-life scenarios.', "The chapter mentions the coefficient 'b' as the slope of the regression line, representing the rate of change for y as x changes, providing a quantitative understanding of the relationship between dependent and independent variables.", "The concept of a y-intercept 'a' is explained as the expected mean value of y when all x variables are equal to zero, providing a foundational understanding of the regression equation and its graphical representation."]}, {'end': 24306.464, 'start': 24014.135, 'title': 'Regression analysis in excel', 'summary': 'Explains how to perform regression analysis in excel to predict units sold based on temperature and price of ice cream, including selecting input and output ranges and interpreting the summary output.', 'duration': 292.329, 'highlights': ['Performing regression analysis in Excel to predict units sold based on temperature and price of ice cream, including selecting input and output ranges and interpreting the summary output.', 'Selecting the dependent and independent variables for regression analysis in Excel, such as temperature and price of ice cream, to predict units sold.', 'Choosing the input and output ranges for regression analysis in Excel, along with selecting residuals, line fit plots, and normal probability plots to interpret the results.', 'Understanding that Excel automatically performs all the necessary calculations for regression analysis, providing summary output, ANOVA, residual output, and regression statistics.']}, {'end': 24679.955, 'start': 24306.464, 'title': 'Understanding regression analysis', 'summary': 'Explains the concept of multiple r, r square, adjusted r square, and anova values in regression analysis, with an emphasis on interpreting their values and significance in determining the goodness of fit and reliability of the model.', 'duration': 373.491, 'highlights': ['The R square value of 0.450788 indicates that 45% of the dependent variables are explained by the independent variables in the regression model, signifying a moderate fit. The R square value of 0.450788 quantifies the percentage of dependent variables explained by independent variables, demonstrating a moderate fit of the regression model.', 'The concept of adjusted R square is introduced as a metric adjusted for the number of independent variables in the model, providing a measure of the precision of the regression analysis. Adjusted R square is presented as a metric adjusted for the number of independent variables, offering a measure of the precision of the regression analysis.', 'The significance F value is discussed as an indicator of the overall statistical significance of the model, with a threshold of 0.05 for determining the reliability of the results. The significance F value is highlighted as a measure of the statistical significance of the model, with a threshold of 0.05 to determine the reliability of the results.', 'An explanation of coefficients in regression analysis is provided, emphasizing their role in building the linear regression equation and enhancing the understanding of the regression analysis output. The explanation of coefficients in regression analysis underscores their significance in constructing the linear regression equation and improving the comprehension of the regression analysis output.', 'The ANOVA values, including DF, SS, MS, F, and significance F, are detailed to elucidate their role in assessing the variability within the regression model and the overall significance of the model. The ANOVA values, such as DF, SS, MS, F, and significance F, are explained as components that assess the variability within the regression model and the general significance of the model.']}, {'end': 24898.322, 'start': 24679.955, 'title': 'Simple regression analysis overview', 'summary': 'Provides an overview of performing a simple regression analysis, discussing the residual output, comparing predicted and actual values, understanding the impact of independent variables on dependent variables, and utilizing statistical functions for linear regression analysis.', 'duration': 218.367, 'highlights': ['The residual output allows for comparison between the estimated and actual number of sold umbrellas based on the observations, revealing the difference between predicted and actual values. 11.209 and 12, 15 and 13.5', 'The residuals help in understanding how far the actual values are from the predicted values, as independent variables are never perfect predictors of dependent variables, demonstrating the importance of the residual section in the analysis. ', 'The chapter emphasizes the importance of selecting the right variables and comparing their impact on the response or target, while also highlighting the possibility of adding more details to scatter plots and utilizing statistical functions for linear regression analysis. r square values']}], 'duration': 1098.638, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw23799684.jpg', 'highlights': ['The chapter explains the regression equation y = bx + a + c, providing a foundational understanding of the regression equation and its graphical representation.', 'Performing regression analysis in Excel to predict units sold based on temperature and price of ice cream, including selecting input and output ranges and interpreting the summary output.', 'The R square value of 0.450788 quantifies the percentage of dependent variables explained by independent variables, demonstrating a moderate fit of the regression model.', 'The concept of adjusted R square is introduced as a metric adjusted for the number of independent variables, offering a measure of the precision of the regression analysis.', 'The residual output allows for comparison between the estimated and actual number of sold umbrellas based on the observations, revealing the difference between predicted and actual values.']}, {'end': 26141.822, 'segs': [{'end': 25004.004, 'src': 'embed', 'start': 24972.353, 'weight': 0, 'content': [{'end': 24983.197, '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': 24972.353, 'duration': 10.844}, {'end': 24989.978, 'text': 'and this can all be done by simple automated wave, which is macros.', 'start': 24983.197, 'duration': 6.781}, {'end': 24997.741, 'text': 'so what it does is it is a set of tasks that you can execute as many number of times as you want.', 'start': 24989.978, 'duration': 7.763}, {'end': 25004.004, 'text': 'when you create a macro, basically what it does is it will record all your mouse clicks and keystrokes.', 'start': 24997.741, 'duration': 6.263}], 'summary': 'Automated macros in excel record mouse clicks and keystrokes for repetitive tasks.', 'duration': 31.651, 'max_score': 24972.353, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw24972353.jpg'}, {'end': 25085.105, 'src': 'embed', 'start': 25057.72, 'weight': 2, 'content': [{'end': 25066.265, 'text': 'you just run that macros, and this will do everything for you and you do not have to do that same tedious task every day.', 'start': 25057.72, 'duration': 8.545}, {'end': 25074.209, 'text': 'so this is basically a set of code or a task that you can execute and automate, and it can be done n number of times.', 'start': 25066.265, 'duration': 7.944}, {'end': 25079.023, 'text': 'so it makes your life easy now.', 'start': 25074.881, 'duration': 4.142}, {'end': 25085.105, 'text': 'so next thing is how you actually activate the macros in your Excel.', 'start': 25079.023, 'duration': 6.082}], 'summary': 'Macros automate tasks, saving time and effort in excel, making life easier.', 'duration': 27.385, 'max_score': 25057.72, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw25057720.jpg'}, {'end': 25146.08, 'src': 'embed', 'start': 25118.379, 'weight': 1, 'content': [{'end': 25123.984, 'text': 'so this will add a developer tab here and this will enable the macro.', 'start': 25118.379, 'duration': 5.605}, {'end': 25130.81, 'text': 'so now you have your data and you can run macros, or record a macro and run it all on your Excel.', 'start': 25123.984, 'duration': 6.826}, {'end': 25136.413, 'text': 'There is also an option to do it a different way.', 'start': 25133.311, 'duration': 3.102}, {'end': 25146.08, 'text': 'Right click on any of the tabs on the ribbon, customize ribbon and you can add or remove your macros from here.', 'start': 25136.533, 'duration': 9.547}], 'summary': 'Excel now allows running macros and customizing ribbon for easy access to macros.', 'duration': 27.701, 'max_score': 25118.379, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw25118379.jpg'}, {'end': 25426.089, 'src': 'embed', 'start': 25397.554, 'weight': 4, 'content': [{'end': 25404.675, 'text': 'And I simple with a single click I will be able to do these daily tasks and execute in a flash.', 'start': 25397.554, 'duration': 7.121}, {'end': 25408.316, 'text': 'So I just click run and it does the rest for me.', 'start': 25405.195, 'duration': 3.121}, {'end': 25414.417, 'text': 'I have my report ready and is in a proper format that is required.', 'start': 25408.936, 'duration': 5.481}, {'end': 25416.521, 'text': 'and it can be sent across.', 'start': 25415.06, 'duration': 1.461}, {'end': 25426.089, 'text': 'so here, as we see, it is done in a single click, by running a simple macro, continuing our work on macro.', 'start': 25416.521, 'duration': 9.568}], 'summary': 'Automate daily tasks with a single click using a macro, saving time and ensuring proper report format and delivery.', 'duration': 28.535, 'max_score': 25397.554, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw25397554.jpg'}, {'end': 25675.368, 'src': 'embed', 'start': 25639.254, 'weight': 5, 'content': [{'end': 25648.093, 'text': 'so what it basically does visual basic for application is a programming language of excel that helps you automate tasks by writing macros.', 'start': 25639.254, 'duration': 8.839}, {'end': 25656.52, 'text': 'We have seen how we use macros, how it simplifies our tedious daily tasks by running a simple macro.', 'start': 25648.554, 'duration': 7.966}, {'end': 25672.947, 'text': 'Now to help us write these macros and help us get the best use of our abilities to ease our work and automate our tasks, we use VBA.', 'start': 25657.181, 'duration': 15.766}, {'end': 25675.368, 'text': 'so why VBA?', 'start': 25672.947, 'duration': 2.421}], 'summary': 'Vba automates excel tasks, simplifying and easing work.', 'duration': 36.114, 'max_score': 25639.254, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw25639254.jpg'}, {'end': 25725.903, 'src': 'embed', 'start': 25696.368, 'weight': 6, 'content': [{'end': 25708.155, 'text': 'how we uh, you can simplify your daily task by just running a simple macro, and this vba helps us in performing those tasks in a very simple manner.', 'start': 25696.368, 'duration': 11.787}, {'end': 25712.097, 'text': 'another thing is it supports english like statements.', 'start': 25708.155, 'duration': 3.942}, {'end': 25720.903, 'text': 'there are multiple statements that you can use and which are very basic english language statements, and this can help us.', 'start': 25712.097, 'duration': 8.806}, {'end': 25725.903, 'text': 'or it is very easy to program or code or edit these commands.', 'start': 25720.903, 'duration': 5}], 'summary': 'Vba simplifies daily tasks with basic english statements, making programming and editing easy.', 'duration': 29.535, 'max_score': 25696.368, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw25696368.jpg'}, {'end': 25878.287, 'src': 'embed', 'start': 25851.245, 'weight': 7, 'content': [{'end': 25857.569, 'text': 'and this is basically the VBA code window where you edit your or write your codes.', 'start': 25851.245, 'duration': 6.324}, {'end': 25859.982, 'text': 'The next thing is the Project Explorer.', 'start': 25858.462, 'duration': 1.52}, {'end': 25867.684, 'text': 'Project Explorer is simply like a Windows Explorer, but this, in particular, is specific to your VBA,', 'start': 25860.042, 'duration': 7.642}, {'end': 25877.627, 'text': "wherein it shows the different options that are there the Excel sheets or the worksheets that you're working on, or the modules that you have saved,", 'start': 25867.684, 'duration': 9.943}, {'end': 25878.287, 'text': 'and so on.', 'start': 25877.627, 'duration': 0.66}], 'summary': 'Introduction to vba code window and project explorer for excel.', 'duration': 27.042, 'max_score': 25851.245, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw25851245.jpg'}, {'end': 25979.567, 'src': 'embed', 'start': 25929.292, 'weight': 9, 'content': [{'end': 25935.439, 'text': 'of course, individual excel code that you can run it will allow you to test individual lines,', 'start': 25929.292, 'duration': 6.147}, {'end': 25939.223, 'text': "which is very useful and handy when you're writing and debugging codes.", 'start': 25935.439, 'duration': 3.784}, {'end': 25947.106, 'text': 'The output if the lines that you execute can be seen here in the immediate window, whereas watch window.', 'start': 25939.984, 'duration': 7.122}, {'end': 25958.169, 'text': 'watch window is similar to it, but it displays the value of the watch expression in its current current state, so this can be extremely useful.', 'start': 25947.106, 'duration': 11.063}, {'end': 25966.392, 'text': 'again, while we are debugging the VBA code, adding a watch is a great utility and we will see the use,', 'start': 25958.169, 'duration': 8.223}, {'end': 25972.876, 'text': 'the advantages of having a watch window in your VBA interface.', 'start': 25966.392, 'duration': 6.484}, {'end': 25979.567, 'text': 'So these are the things that we learn and look into it in detail in the coming section.', 'start': 25973.958, 'duration': 5.609}], 'summary': 'Individual excel code allows testing and debugging, with the output visible in the immediate and watch windows. adding a watch is a great utility for debugging vba code.', 'duration': 50.275, 'max_score': 25929.292, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw25929292.jpg'}], 'start': 24898.902, 'title': 'Excel macros, automation, and vba in excel', 'summary': 'Covers the use of excel macros for automation, enabling users to record and execute repetitive tasks, automating daily tasks in excel, and the benefits of using vba in excel macros, emphasizing its ability to simplify and automate tedious tasks, along with introducing the basics of vba for excel and its key components.', 'chapters': [{'end': 25335.492, 'start': 24898.902, 'title': 'Excel macros and automation', 'summary': 'Discusses the use of excel macros for automation, enabling users to record and execute repetitive tasks, enhancing efficiency and reducing manual effort. it also explains the process of activating and recording macros in excel.', 'duration': 436.59, 'highlights': ['Excel macros enable users to automate repetitive tasks, reducing manual effort and enhancing efficiency. The use of Excel macros allows users to automate repetitive tasks, reducing manual effort and enhancing efficiency.', 'The process of activating and recording macros in Excel is explained, including adding the developer tab, customizing the ribbon, and recording a macro for repetitive tasks. The process of activating and recording macros in Excel is explained, including adding the developer tab, customizing the ribbon, and recording a macro for repetitive tasks.', 'The importance of macros in automating daily tasks and reports in Excel is emphasized, showcasing how macros can streamline tasks such as data extraction, column removal, and data formatting. The importance of macros in automating daily tasks and reports in Excel is emphasized, showcasing how macros can streamline tasks such as data extraction, column removal, and data formatting.']}, {'end': 25604.836, 'start': 25335.492, 'title': 'Automating tasks with macros', 'summary': 'Demonstrates how to automate daily tasks in excel using macros, allowing users to execute multiple steps in a single click and create a button to run the macro, along with alternative methods of accessing and editing macros.', 'duration': 269.344, 'highlights': ['The chapter demonstrates how to automate daily tasks in Excel using macros, allowing users to execute multiple steps in a single click. The user shows how they can perform everyday tasks by automating all the steps with a single click using macros.', 'The chapter explains how to create a button to run the macro and execute daily tasks with a single click. The user explains the process of adding a button in the developer tab to run the macro and execute daily tasks with a single click.', 'The chapter outlines alternative methods of accessing and editing macros, such as using the view button and the VBA visual basic application. The user explains alternative methods of accessing and editing macros, including using the view button and the VBA visual basic application.']}, {'end': 25725.903, 'start': 25605.776, 'title': 'Vba for excel macros', 'summary': 'Discusses the benefits of using vba in excel macros, emphasizing its ability to simplify and automate tedious tasks, saving time and effort for everyday operations.', 'duration': 120.127, 'highlights': ['VBA is a visual basic application language used for writing macros in Excel, helping automate tasks and simplify daily operations.', 'Using VBA in macros for Excel saves time in performing tedious tasks and supports English-like statements, making it easy to program or edit commands.', 'The rush column in the sheet represents the priority of orders and can be edited with VBA to automate tasks and simplify daily operations.']}, {'end': 26141.822, 'start': 25725.903, 'title': 'Vba for excel: basics and functions', 'summary': 'Introduces the basics of vba for excel, detailing the vba editor interface along with its key components such as the toolbar, menu bar, code window, project explorer, properties window, immediate window, and watch window, highlighting the capabilities and advantages of each.', 'duration': 415.919, 'highlights': ['The VBA code window allows the user to edit and write codes, facilitating the execution of a range of functions within Excel, enhancing productivity and efficiency.', "The Project Explorer serves as an explorer for VBA, displaying various options such as Excel sheets, worksheets, and modules, providing a comprehensive view of the user's work.", 'The Immediate Window in VBA allows users to run and execute simple lines of individual Excel code, providing a valuable tool for testing and debugging.', "The Watch Window in VBA displays the value of watch expressions in their current state, offering utility and advantages during the debugging process, enhancing the user's experience with VBA for Excel.", 'The chapter also illustrates the practical application of VBA for macros, demonstrating recording a macro to perform simple to complex tasks in Excel, showcasing the hands-on usage of VBA for Excel functionalities.']}], 'duration': 1242.92, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw24898902.jpg', 'highlights': ['Excel macros enable users to automate repetitive tasks, reducing manual effort and enhancing efficiency.', 'The process of activating and recording macros in Excel is explained, including adding the developer tab, customizing the ribbon, and recording a macro for repetitive tasks.', 'The importance of macros in automating daily tasks and reports in Excel is emphasized, showcasing how macros can streamline tasks such as data extraction, column removal, and data formatting.', 'The chapter demonstrates how to automate daily tasks in Excel using macros, allowing users to execute multiple steps in a single click.', 'The chapter explains how to create a button to run the macro and execute daily tasks with a single click.', 'VBA is a visual basic application language used for writing macros in Excel, helping automate tasks and simplify daily operations.', 'Using VBA in macros for Excel saves time in performing tedious tasks and supports English-like statements, making it easy to program or edit commands.', 'The VBA code window allows the user to edit and write codes, facilitating the execution of a range of functions within Excel, enhancing productivity and efficiency.', "The Project Explorer serves as an explorer for VBA, displaying various options such as Excel sheets, worksheets, and modules, providing a comprehensive view of the user's work.", 'The Immediate Window in VBA allows users to run and execute simple lines of individual Excel code, providing a valuable tool for testing and debugging.', "The Watch Window in VBA displays the value of watch expressions in their current state, offering utility and advantages during the debugging process, enhancing the user's experience with VBA for Excel.", 'The chapter also illustrates the practical application of VBA for macros, demonstrating recording a macro to perform simple to complex tasks in Excel, showcasing the hands-on usage of VBA for Excel functionalities.']}, {'end': 28688.515, 'segs': [{'end': 26293.218, 'src': 'embed', 'start': 26266.607, 'weight': 1, 'content': [{'end': 26271.789, 'text': "Now I'll explain it further about the dim statement why is it used and what does it use for.", 'start': 26266.607, 'duration': 5.182}, {'end': 26281.133, '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': 26272.549, 'duration': 8.584}, {'end': 26293.218, 'text': "So here I am trying to specify a variable called x and how I'm going to do it I will have it as a number or an integer or anything.", 'start': 26281.233, 'duration': 11.985}], 'summary': "The 'dim' statement in vba is used to declare variables, like specifying a variable 'x' as a number or an integer.", 'duration': 26.611, 'max_score': 26266.607, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw26266607.jpg'}, {'end': 27065.826, 'src': 'embed', 'start': 27024.101, 'weight': 2, 'content': [{'end': 27035.244, 'text': 'let me show you one more simple example where in a message box pops up and a welcome message that can be pop set when you open the excel or worksheet.', 'start': 27024.101, 'duration': 11.143}, {'end': 27050.048, 'text': "so so let's name it as auto open and then again message box i wanted to populate and what does it say?", 'start': 27035.244, 'duration': 14.804}, {'end': 27061.305, 'text': 'I put it in quotes welcome to our worksheet, our workshop.', 'start': 27052.003, 'duration': 9.302}, {'end': 27065.826, 'text': "It's very simple.", 'start': 27065.026, 'duration': 0.8}], 'summary': 'Demonstrated a simple example of a message box with a welcome message for excel/worksheets.', 'duration': 41.725, 'max_score': 27024.101, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw27024101.jpg'}, {'end': 27152.446, 'src': 'embed', 'start': 27105.117, 'weight': 0, 'content': [{'end': 27114.501, 'text': 'i mean where i am going to specify a cell, where the value will be given to the cell.', 'start': 27105.117, 'duration': 9.384}, {'end': 27118.104, 'text': "I'm going to give it as B5.", 'start': 27115.622, 'duration': 2.482}, {'end': 27122.268, 'text': 'Okay I put it in quotes.', 'start': 27118.124, 'duration': 4.144}, {'end': 27123.949, 'text': 'Close the parenthesis.', 'start': 27122.888, 'duration': 1.061}, {'end': 27130.194, 'text': "What is the value that I'm going to give? Value equals 250.", 'start': 27125.791, 'duration': 4.403}, {'end': 27138.801, 'text': "Okay So now with this code, I mean, I'm going to assign a value of 250 to the cell B5.", 'start': 27130.194, 'duration': 8.607}, {'end': 27139.502, 'text': "Let's see.", 'start': 27139.121, 'duration': 0.381}, {'end': 27141.443, 'text': 'Run it.', 'start': 27141.123, 'duration': 0.32}, {'end': 27152.446, 'text': 'See Again I do it to A10.', 'start': 27141.964, 'duration': 10.482}], 'summary': 'Assigning value 250 to cell b5 and a10 using code.', 'duration': 47.329, 'max_score': 27105.117, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw27105117.jpg'}, {'end': 27549.53, 'src': 'embed', 'start': 27509.18, 'weight': 3, 'content': [{'end': 27518.053, 'text': "and now i am going to specify the worksheet two and what is the range that i'm going to select?", 'start': 27509.18, 'duration': 8.873}, {'end': 27518.773, 'text': 'the same thing.', 'start': 27518.053, 'duration': 0.72}, {'end': 27529.656, 'text': 'okay, the range will be a1 to d5.', 'start': 27518.773, 'duration': 10.883}, {'end': 27533.697, 'text': "okay, what i'm going to do?", 'start': 27529.656, 'duration': 4.041}, {'end': 27537.037, 'text': "i'm going to select it.", 'start': 27533.697, 'duration': 3.34}, {'end': 27539.118, 'text': 'okay, and see what happens here.', 'start': 27537.037, 'duration': 2.081}, {'end': 27549.53, 'text': "Okay, so here I am selecting the worksheet to and I'm activating it and I'm going to assign range and I'm going to select it.", 'start': 27540.777, 'duration': 8.753}], 'summary': 'Select worksheet 2, assign range a1 to d5, and activate it.', 'duration': 40.35, 'max_score': 27509.18, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw27509180.jpg'}, {'end': 28281.826, 'src': 'embed', 'start': 28253.888, 'weight': 4, 'content': [{'end': 28256.329, 'text': "it's very basic condition that you are specifying.", 'start': 28253.888, 'duration': 2.441}, {'end': 28265.494, 'text': 'you are specifying two variables, the score and the result, and then you are giving a value for it and you are specifying the condition.', 'start': 28256.329, 'duration': 9.165}, {'end': 28268.856, 'text': 'if the score is greater than then, the result should be passed.', 'start': 28265.494, 'duration': 3.362}, {'end': 28279.265, 'text': 'Now, in the next section, we will simply tweak this and use an if, then else statement.', 'start': 28271.721, 'duration': 7.544}, {'end': 28281.826, 'text': 'We will attach an else statement with it and see.', 'start': 28279.285, 'duration': 2.541}], 'summary': 'Explaining basic conditional statements using score and result variables with an example of if-then-else statement.', 'duration': 27.938, 'max_score': 28253.888, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw28253888.jpg'}], 'start': 26141.822, 'title': 'Vba code and execution', 'summary': 'Covers vba code for formatting columns, including macro details, font selection, and variable declaration, demonstrating the execution resulting in cell b5 set to 500. it also discusses using dim keyword to assign values and message box usage, vba selection and manipulation, and if statement and for loop in excel vba.', 'chapters': [{'end': 26362.898, 'start': 26141.822, 'title': 'Vba code explanation and execution', 'summary': 'Explains a vba code for formatting columns, including the macro details, font selection, and variable declaration, and demonstrates the execution of the code resulting in the value of cell b5 being set to 500.', 'duration': 221.076, 'highlights': ['The VBA code demonstrates formatting columns, including macro details, font selection, and variable declaration, and executes to set the value of cell B5 to 500.', "The chapter explains the usage of the 'dim' statement in VBA, its function in declaring variables, and provides an example of declaring a variable 'x' as an integer with a value of 500.", "The VBA code is explained in detail, starting with the 'sub' and 'end sub' statements, specifying the module name, and demonstrating the execution of the code resulting in the value of cell B5 being set to 500."]}, {'end': 26808.622, 'start': 26362.898, 'title': 'Using dim keyword in vba', 'summary': 'Discusses the usage of dim keyword in vba to assign integer, string, double, date values, and user input to cells, and to display message boxes, with examples and code snippets.', 'duration': 445.724, 'highlights': ['Usage of DIM keyword for assigning integer, string, double, and date values in VBA The chapter covers the usage of DIM keyword to assign integer, string, double, and date values, demonstrating its versatility in VBA programming.', 'Assigning string value to a selected cell in VBA The transcript explains how to assign a string value to a selected cell in VBA, demonstrating practical application in tasks such as report generation.', 'Displaying message boxes with specified values in VBA It describes the process of displaying message boxes with specified values in VBA, showcasing its utility in providing user prompts and notifications.', 'Using user input to populate a cell in VBA The chapter demonstrates the use of user input to populate a cell in VBA, illustrating its effectiveness in streamlining data input processes.']}, {'end': 27478.049, 'start': 26808.622, 'title': 'Using message box and vba in excel', 'summary': 'Demonstrates the use of message box and vba in excel, including customizing welcome messages, displaying cell values, assigning values to cells, and selecting ranges of cells using vba.', 'duration': 669.427, 'highlights': ['The chapter demonstrates how to use message box to display custom welcome messages, cell values, and assign values to cells in Excel using VBA. It shows the versatility of message box in displaying custom welcome messages and cell values, as well as assigning values to cells using VBA.', 'The demonstration includes using VBA to select ranges of cells and assign values, providing a quick and efficient method for regular daily tasks in Excel. It showcases how VBA can be utilized to select ranges of cells and assign values, streamlining daily tasks in Excel for improved efficiency.', 'The example illustrates how to assign values to specific cells and ranges in Excel, showcasing the flexibility and ease of use of VBA for such tasks. It exemplifies the simplicity and flexibility of using VBA to assign values to specific cells and ranges, enhancing the ease of performing such tasks in Excel.']}, {'end': 27952.451, 'start': 27478.049, 'title': 'Vba selection and manipulation', 'summary': 'Demonstrates how to select and manipulate ranges in vba, including selecting cells, copying and pasting, and counting cells, with examples showing the process and results.', 'duration': 474.402, 'highlights': ['The chapter demonstrates selecting and activating specific worksheets and ranges in VBA, providing a practical example of selecting cells in a different sheet and activating them.', 'It shows a simple VBA code for copying a selected range and pasting it to a specified location on the active sheet, illustrating the process and outcome.', 'The transcript explains how to count the number of cells in a specified range using VBA, including examples of counting cells, columns, and rows, and showcasing the resulting counts.']}, {'end': 28688.515, 'start': 27952.451, 'title': 'Excel vba: if statement and for loop', 'summary': 'Introduces the if statement and for loop in excel vba, explaining their syntax and providing examples of their usage to evaluate conditions and execute tasks iteratively, showcasing the simplicity and versatility of these fundamental programming constructs.', 'duration': 736.064, 'highlights': ["The if statement in Excel VBA allows the specification of conditions and corresponding outputs, providing a simple and familiar syntax for evaluating conditions and defining results, as demonstrated by the example of selecting 'pass' or 'fail' based on a specified score. if statement, specifying conditions and outputs, example of selecting 'pass' or 'fail' based on a score", 'The for loop statement in Excel VBA enables the repetitive execution of tasks, as illustrated by the example of filling column A with values 1 to 24, showcasing the sequential execution and simplicity of the for loop. for loop statement, repetitive execution of tasks, example of filling column A with values 1 to 24', 'Color coding the B column based on the values using VBA showcases the versatility of for loop statements in Excel, demonstrating the ability to perform additional tasks such as formatting and customization. color coding the B column based on values, showcasing versatility of for loop statements, additional tasks such as formatting and customization']}], 'duration': 2546.693, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xhFDYKqWlqw/pics/xhFDYKqWlqw26141822.jpg', 'highlights': ['The VBA code demonstrates formatting columns, including macro details, font selection, and variable declaration, and executes to set the value of cell B5 to 500.', "The chapter explains the usage of the 'dim' statement in VBA, its function in declaring variables, and provides an example of declaring a variable 'x' as an integer with a value of 500.", 'The chapter demonstrates how to use message box to display custom welcome messages, cell values, and assign values to cells in Excel using VBA. It shows the versatility of message box in displaying custom welcome messages and cell values, as well as assigning values to cells using VBA.', 'The chapter demonstrates selecting and activating specific worksheets and ranges in VBA, providing a practical example of selecting cells in a different sheet and activating them.', "The if statement in Excel VBA allows the specification of conditions and corresponding outputs, providing a simple and familiar syntax for evaluating conditions and defining results, as demonstrated by the example of selecting 'pass' or 'fail' based on a specified score."]}], 'highlights': ['The live session focuses on teaching data analysis with Excel by Simply Learn.', 'Excel is ideal for handling large datasets and performing mathematical calculations, meeting the needs of various organizations.', 'Excel provides features like searching, sorting, filtering, and data visualization, simplifying data manipulation and analysis.', 'The chapter provides insights into sorting and filtering data, including sorting by date and color, and adding and removing filters in Excel.', 'The chapter explains options for uploading data, including loading a file from the machine, getting data from a web source, connecting to a database, and using existing connections like SQL server, analysis services, OData data feed, XML, data connection wizard, and Microsoft query.', 'Excel allows beautifying data and presenting it in the form of charts, tables, and data bars, facilitating reporting, accounting, and analysis tasks.', 'The chapter covers the basics of formulas and functions in Excel, including examples of addition, division, sum, average, and SUMPRODUCT, demonstrating the ability to perform basic calculations and complex operations, with an emphasis on practical application.', 'The SUBSTITUTE function replaces existing text with new text in a text string.', "The demonstration showcases using the index match function to find the city corresponding to a person's last name, returning 'Los Angeles' for 'Evens' and 'Sales' for 'Zampa'.", 'Conditional formatting aids in quick identification and analysis of values greater than 12,000.', 'Pivot tables summarize and reorganize data, providing a tool for creating tabular reports.', 'Excel macros enable users to automate repetitive tasks, reducing manual effort and enhancing efficiency.']}