title
🔥Data Analytics In Excel Full Course | Data Analytics Tutorial | Data Analytics Project |Simplilearn

description
🔥Data Analytics Course for 3-8 Yrs Work Exp: https://l.linklyhq.com/l/1tx7T 🔥Data Analytics Course for 0-3 Yrs Work Exp: https://l.linklyhq.com/l/1ugCA 🔥Data Analytics Course for 8+ Yrs Work Exp: https://l.linklyhq.com/l/1ugCG This video on Data Analytics In Excel Full Course by Simplilearn is curated with the help of leading data analytics giants and real-time industry experts covering the major and critical data analytical skills, techniques, tips, and tricks to prepare you and make sure you become an expert data analyst. This video on Data Analytics In Excel Full Course will help you will understand the various crucial functions available in Excel, such as lookup, hlookup, sumif/s, counif/s, iferror and others. Finally, you will see how to use the Data Analysis Toolpak to perform various data analysis operations. So, let's begin. 🔥Free Data Analytics Course: https://www.simplilearn.com/learn-data-analytics-for-beginners-skillup?utm_campaign=DataAnalyticsNew&utm_medium=DescriptionFirstFold&utm_source=youtube Dataset Link: https://drive.google.com/drive/folders/1U8CF0Xx3NivXdfrQvXJ_462tEb26oPuX ✅Subscribe to our Channel to learn more about the top Technologies: https://bit.ly/2VT4WtH ⏩ Check out the Business Analytics tutorial videos: https://bit.ly/3hQFfDP #DataAnalyticsInExcel #dataanalyticscourse #dataAnalyticsProject #DataAnalyticsInExcelFullCourse #DataAnalyticsWithExcel #DataAnalyticsUsingExcel #DataAnalyticsCourse #DataAnalyticsCourseForBeginners #ExcelForDataAnalysis #ExcelForBeginners #ExcelBasicsForBeginners #ExcelTutorial #ExcelTutorialForBeginners #BusinessAnalystCourse #BusinessAnalyst #BusinessAnalytics #Simplilearn What is Microsoft Excel? Excel is one of the best applications available on the market for creating spreadsheets to crunch numbers and dashboard reports as well as storing and administering data. This software first appeared on the scene back in 1987, and since then it has grown to become one of the most popular pieces of software for home or business. Excel can be used for data entry to store data in the form of a table such as recording your daily expenses or business expenditure. It can also be used for inventory management. ➡️ 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=DataAnalyticsNew11Mar2023&utm_medium=DescriptionFirstFold&utm_source=youtube 🔥🔥 Interested in Attending Live Classes? Call Us: IN - 18002127688 / US - +18445327688

detail
{'title': '🔥Data Analytics In Excel Full Course | Data Analytics Tutorial | Data Analytics Project |Simplilearn', 'heatmap': [{'end': 1494.941, 'start': 994.038, 'weight': 0.908}, {'end': 2735.971, 'start': 2231.489, 'weight': 0.77}, {'end': 5473.93, 'start': 5222.502, 'weight': 0.946}, {'end': 6717.997, 'start': 6463.565, 'weight': 0.779}, {'end': 7710.729, 'start': 7458.006, 'weight': 0.7}, {'end': 13928.227, 'start': 13424.9, 'weight': 1}], 'summary': 'This excel data analytics full course covers functions, formulas, filtering techniques, data manipulation, analysis, visualization, vba macros, and shortcut keys, offering practical insights and examples to improve data management efficiency and productivity.', 'chapters': [{'end': 59.339, 'segs': [{'end': 59.339, 'src': 'embed', 'start': 19.244, 'weight': 0, 'content': [{'end': 21.968, 'text': "Hey everyone, welcome to Simply Learn's YouTube channel.", 'start': 19.244, 'duration': 2.724}, {'end': 26.474, 'text': 'In this session, we will be learning about data analytics with Excel.', 'start': 22.388, 'duration': 4.086}, {'end': 31.26, 'text': 'But before we begin, let me tell you guys that we have daily updates on multiple technologies.', 'start': 26.894, 'duration': 4.366}, {'end': 35.526, 'text': "So if you're a tech geek in a continuous hunt for latest technological trends,", 'start': 31.621, 'duration': 3.905}, {'end': 42.384, 'text': "then consider getting subscribed to our YouTube channel and don't forget to hit that bell icon to never miss an update from Simply Learn.", 'start': 35.882, 'duration': 6.502}, {'end': 49.226, 'text': "Now, before we begin, I would like to take a quick confirmation from your end if I'm audible and everything is clearly visible on the screen.", 'start': 42.944, 'duration': 6.282}, {'end': 52.947, 'text': 'If yes, please type yes in the chat section.', 'start': 50.246, 'duration': 2.701}, {'end': 59.339, 'text': 'Great I see some positive responses, so I think we are good to go.', 'start': 55.468, 'duration': 3.871}], 'summary': "Simply learn's youtube channel offers daily updates on multiple technologies. consider subscribing for latest tech trends.", 'duration': 40.095, 'max_score': 19.244, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI19244.jpg'}], 'start': 19.244, 'title': 'Data analytics with excel', 'summary': 'Introduces a session on data analytics with excel, emphasizing daily updates on multiple technologies, and encouraging subscriptions to the youtube channel.', 'chapters': [{'end': 59.339, 'start': 19.244, 'title': 'Data analytics with excel', 'summary': 'Introduces a session on data analytics with excel, emphasizing daily updates on multiple technologies and encouraging subscriptions to the youtube channel, with a confirmation from the audience before beginning.', 'duration': 40.095, 'highlights': ['The session covers data analytics with Excel.', "Simply Learn's YouTube channel provides daily updates on multiple technologies.", 'Encourages subscriptions and hitting the bell icon for updates.', 'Confirmation from the audience before beginning the session.']}], 'duration': 40.095, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI19244.jpg', 'highlights': ['The session covers data analytics with Excel.', "Simply Learn's YouTube channel provides daily updates on multiple technologies.", 'Encourages subscriptions and hitting the bell icon for updates.', 'Confirmation from the audience before beginning the session.']}, {'end': 2917.858, 'segs': [{'end': 108.511, 'src': 'embed', 'start': 79.48, 'weight': 6, 'content': [{'end': 86.206, 'text': 'to the state where we include data validation on the data in Excel spreadsheet, conditional formatting and much more.', 'start': 79.48, 'duration': 6.726}, {'end': 88.388, 'text': 'Followed by the fundamentals.', 'start': 86.627, 'duration': 1.761}, {'end': 97.036, 'text': 'we will start with data analytics from the basics, where we try to compare two columns, and also we will try to cover freezing columns, rows, etc.', 'start': 88.388, 'duration': 8.648}, {'end': 98.638, 'text': 'for the reporting purposes.', 'start': 97.216, 'duration': 1.422}, {'end': 108.511, 'text': 'Later we will try to incorporate security to our Excel data, which might include dashboards, charts and confidential organization data.', 'start': 99.138, 'duration': 9.373}], 'summary': 'Cover data validation, conditional formatting, data analytics, freezing columns, and incorporating security in excel.', 'duration': 29.031, 'max_score': 79.48, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI79480.jpg'}, {'end': 311.43, 'src': 'embed', 'start': 263.637, 'weight': 0, 'content': [{'end': 268.882, 'text': 'and finally, to make things interesting, we will walk you through some day-to-day shortcuts used in excel.', 'start': 263.637, 'duration': 5.245}, {'end': 276.989, 'text': 'so, with that discussed, we can get started with our first topic and i hope i made myself clear with the agenda over to our experts.', 'start': 268.882, 'duration': 8.107}, {'end': 284.212, 'text': "hi everyone, let's continue learning excel, so We will learn about functions and formulas.", 'start': 276.989, 'duration': 7.223}, {'end': 289.736, 'text': 'We will learn about conditional formatting, data validation, pivot chart, and pivot table.', 'start': 284.453, 'duration': 5.283}, {'end': 293.078, 'text': "Now let's look at a scenario here.", 'start': 290.937, 'duration': 2.141}, {'end': 305.046, 'text': 'So one day in a startup, one professional speaks that their business is growing and they would need an efficient way to work with the data.', 'start': 293.418, 'duration': 11.628}, {'end': 311.43, 'text': 'They would have to find a way to work faster with storing and analyzing data.', 'start': 306.127, 'duration': 5.303}], 'summary': 'Learn day-to-day excel shortcuts, functions, formulas, and data analysis for efficient work.', 'duration': 47.793, 'max_score': 263.637, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI263637.jpg'}, {'end': 434.072, 'src': 'embed', 'start': 364.725, 'weight': 1, 'content': [{'end': 373.695, 'text': "Now, Before we move to Excel, one of the question is why should we use Excel? So let's look at some of the points here.", 'start': 364.725, 'duration': 8.97}, {'end': 382.082, 'text': 'So Excel proves to be a great platform to perform various mathematical calculation on large data sets,', 'start': 373.715, 'duration': 8.367}, {'end': 386.525, 'text': 'which is one of the biggest requirements of various organizations these days.', 'start': 382.082, 'duration': 4.443}, {'end': 394.852, 'text': 'Various features in Excel like searching, sorting, filtering makes it easier for you to play with the data.', 'start': 387.186, 'duration': 7.666}, {'end': 402.874, 'text': 'And Excel also allows you to beautify your data and present it in the form of charts, tables and data bars.', 'start': 395.592, 'duration': 7.282}, {'end': 410.737, 'text': 'Now, when it comes to reporting, reporting, accounting and analysis can be performed with the help of Excel.', 'start': 403.875, 'duration': 6.862}, {'end': 417.439, 'text': 'It can help you with your task lists, your calendars and goal planning worksheets.', 'start': 411.677, 'duration': 5.762}, {'end': 421.241, 'text': 'Excel also provides good security for your data.', 'start': 418.419, 'duration': 2.822}, {'end': 424.304, 'text': 'Excel files have the feature of password protection.', 'start': 421.682, 'duration': 2.622}, {'end': 427.827, 'text': 'This way, your information can be safe.', 'start': 424.945, 'duration': 2.882}, {'end': 434.072, 'text': 'Now when we talk about what is Excel and how it can be used.', 'start': 429.208, 'duration': 4.864}], 'summary': 'Excel is useful for mathematical calculations, data manipulation, reporting, and security.', 'duration': 69.347, 'max_score': 364.725, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI364725.jpg'}, {'end': 1494.941, 'src': 'heatmap', 'start': 994.038, 'weight': 0.908, 'content': [{'end': 995.939, 'text': 'And this basically gives me the total.', 'start': 994.038, 'duration': 1.901}, {'end': 999.26, 'text': 'There is one more quick way to check if this is right.', 'start': 996.379, 'duration': 2.881}, {'end': 1003.761, 'text': 'So the easiest option would be to select this cell.', 'start': 1000.04, 'duration': 3.721}, {'end': 1012.844, 'text': 'Now, what I can also do is I can just select all of these fields by just highlighting and selecting all the fields once it is selected.', 'start': 1004.181, 'duration': 8.663}, {'end': 1017.426, 'text': 'press on control r and that gives you the total.', 'start': 1013.484, 'duration': 3.942}, {'end': 1026.731, 'text': 'now, if we would be doing this top down, then i could select all these rows for this particular column and then i could do a control d.', 'start': 1017.426, 'duration': 9.305}, {'end': 1031.454, 'text': "so that's your filling down and this one was filling right.", 'start': 1026.731, 'duration': 4.723}, {'end': 1039.877, 'text': 'so this is an easier option of doing a fill when you would want to have the formula applied to every row,', 'start': 1031.454, 'duration': 8.423}, {'end': 1044.56, 'text': 'as it occurs in the first row or the last row.', 'start': 1039.877, 'duration': 4.683}, {'end': 1048.344, 'text': 'we could test this by, for example, selecting these fields.', 'start': 1044.56, 'duration': 3.784}, {'end': 1052.847, 'text': 'i could delete them, and i have here which says 130.', 'start': 1048.344, 'duration': 4.503}, {'end': 1063.115, 'text': 'i could just place my cursor here and i could drag it all the way up, and that should also do the same magic which we were seeing from top down.', 'start': 1052.847, 'duration': 10.268}, {'end': 1075.238, 'text': 'so this is a simple way wherein you can fill up your cells and you can also automatically propagate or move your computation to all the cells.', 'start': 1063.115, 'duration': 12.123}, {'end': 1078.922, 'text': "Let's look at the split option,", 'start': 1076.32, 'duration': 2.602}, {'end': 1089.146, 'text': 'which basically helps us in splitting the data when we have some kind of pattern or when we have some kind of delimiters in our data in, say,', 'start': 1078.922, 'duration': 10.224}, {'end': 1096.651, 'text': 'one particular column, and we would want to derive the values out of it, so we can always use the splitting option.', 'start': 1089.146, 'duration': 7.505}, {'end': 1106.955, 'text': 'now the easiest option would be so for example, we have our email column which has the email ids and which We can clearly see has a first name, dot,', 'start': 1096.651, 'duration': 10.304}, {'end': 1107.475, 'text': 'last name.', 'start': 1106.955, 'duration': 0.52}, {'end': 1111.316, 'text': 'Now I see that there is a last name Smith filled up here.', 'start': 1107.895, 'duration': 3.421}, {'end': 1112.517, 'text': 'First name is empty.', 'start': 1111.436, 'duration': 1.081}, {'end': 1118.478, 'text': 'So what I can also do is I can just type in say Nancy here.', 'start': 1112.937, 'duration': 5.541}, {'end': 1120.759, 'text': "Now that's the first name.", 'start': 1118.498, 'duration': 2.261}, {'end': 1130.597, 'text': "I can again start typing the second name and as soon as you do that you would see a faded list of numbers and that's your clue to hit enter.", 'start': 1121.289, 'duration': 9.308}, {'end': 1135.661, 'text': 'And once you do that you would see all the first names have been filled in here.', 'start': 1131.077, 'duration': 4.584}, {'end': 1141.706, 'text': 'If you would want to maintain the case sensitiveness you can just go ahead and delete these.', 'start': 1136.161, 'duration': 5.545}, {'end': 1144.03, 'text': "and let's type in as it occurs.", 'start': 1142.29, 'duration': 1.74}, {'end': 1152.473, 'text': "so let's say Nancy, as the first name, go down to the next cell and just type in Andy and there is your grade list.", 'start': 1144.03, 'duration': 8.443}, {'end': 1157.114, 'text': 'so just hit on enter and that basically fills up your first name.', 'start': 1152.473, 'duration': 4.641}, {'end': 1168.577, 'text': 'what we can also do is we can just select this particular field and either we can type in control E, which basically fills up all the options.', 'start': 1157.114, 'duration': 11.463}, {'end': 1174.889, 'text': "Now I can just do a undo by typing in or clicking control Z and that's basically gone.", 'start': 1169.058, 'duration': 5.831}, {'end': 1187.845, 'text': 'what I can also do is I can select a particular field and then I can go into home option and under home you have an option here which is fill.', 'start': 1175.714, 'duration': 12.131}, {'end': 1193.63, 'text': 'so you can select this and then you can do a flash fill, which is what we are doing here.', 'start': 1187.845, 'duration': 5.785}, {'end': 1197.954, 'text': 'so click on flash fill and that automatically fills up the values.', 'start': 1193.63, 'duration': 4.324}, {'end': 1208.763, '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': 1197.954, 'duration': 10.809}, {'end': 1217.509, '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': 1208.763, 'duration': 8.746}, {'end': 1222.993, 'text': 'so you can always scroll down here and that says splitting a column based on delimiters.', 'start': 1217.509, 'duration': 5.484}, {'end': 1230.599, 'text': 'so we have some values in the data column and these values in each row are separated by comma.', 'start': 1222.993, 'duration': 7.606}, {'end': 1233.527, 'text': 'so select this.', 'start': 1230.599, 'duration': 2.928}, {'end': 1241.849, 'text': 'your data is already selected, text to columns, delimited comma is selected and now click on next.', 'start': 1233.527, 'duration': 8.322}, {'end': 1245.25, 'text': 'so it basically says what is the destination?', 'start': 1241.849, 'duration': 3.401}, {'end': 1251.391, 'text': "let's select this one and i can choose what would you want to have.", 'start': 1245.25, 'duration': 6.141}, {'end': 1254.352, 'text': 'so that shows me this would be my data preview.', 'start': 1251.391, 'duration': 2.961}, {'end': 1256.712, 'text': 'now i can basically select this one.', 'start': 1254.352, 'duration': 2.36}, {'end': 1262.952, 'text': 'I can say finish and say okay, and now, if you see.', 'start': 1257.469, 'duration': 5.483}, {'end': 1267.235, 'text': 'our data has been placed in in the columns appropriately.', 'start': 1262.952, 'duration': 4.283}, {'end': 1273.558, 'text': 'So this is how you can split your data based on a delimiter and then organize your data in a better way.', 'start': 1267.435, 'duration': 6.123}, {'end': 1280.082, 'text': 'Now there are some advanced options which we can learn later but this basically tells about using a formula.', 'start': 1274.039, 'duration': 6.043}, {'end': 1281.773, 'text': 'So this is something.', 'start': 1280.492, 'duration': 1.281}, {'end': 1292.54, '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': 1281.773, 'duration': 10.767}, {'end': 1295.862, 'text': 'so that can also be done using formulas,', 'start': 1292.54, 'duration': 3.322}, {'end': 1304.407, 'text': 'and this basically tells how would you extract characters from your left cell and how would you place them in your right cell,', 'start': 1295.862, 'duration': 8.545}, {'end': 1308.649, 'text': 'so you can try this activity, which is a little more of advanced option.', 'start': 1304.407, 'duration': 4.242}, {'end': 1321.281, 'text': 'the benefit is that you can always use this, wherein, if you do some kind of transformation using your formulas, if your original data gets updated,', 'start': 1308.649, 'duration': 12.632}, {'end': 1324.603, 'text': 'then the split data will also get updated.', 'start': 1321.281, 'duration': 3.322}, {'end': 1336.888, 'text': "And that's the benefit of using formulas where you can place values from one cell into multiple cells based on execution of your details in the formulas.", 'start': 1325.163, 'duration': 11.725}, {'end': 1340.497, 'text': 'How about using the transpose option?', 'start': 1337.975, 'duration': 2.522}, {'end': 1349.902, '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': 1340.877, 'duration': 9.025}, {'end': 1352.423, 'text': "and that's where transposing comes into picture.", 'start': 1349.902, 'duration': 2.521}, {'end': 1360.388, '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': 1352.904, 'duration': 7.484}, {'end': 1365.491, 'text': 'and you would want to switch your rows to become the columns and columns to become your rows.', 'start': 1360.388, 'duration': 5.103}, {'end': 1372.869, 'text': 'So what we can do is the simplest way is, you can select all your values.', 'start': 1366.058, 'duration': 6.811}, {'end': 1377.671, 'text': 'so here we basically have six columns and i would say, two rows.', 'start': 1372.869, 'duration': 4.802}, {'end': 1384.856, 'text': 'now i can select all of these and then i can select an empty field, for example the one which is highlighted here.', 'start': 1377.671, 'duration': 7.185}, {'end': 1387.777, 'text': 'well, you can always do a control alt v.', 'start': 1384.856, 'duration': 2.921}, {'end': 1389.158, 'text': "that's a shortcut.", 'start': 1387.777, 'duration': 1.381}, {'end': 1395.782, 'text': 'what you can also do is, once you have selected all your fields, you can just copy them.', 'start': 1389.158, 'duration': 6.624}, {'end': 1399.765, 'text': 'so just do a control c and then click on an empty cell.', 'start': 1395.782, 'duration': 3.983}, {'end': 1404.669, 'text': 'and then what you can do is you can do a special paste or paste special.', 'start': 1400.405, 'duration': 4.264}, {'end': 1410.955, 'text': 'so under your home you have the paste option and here you can go for paste special.', 'start': 1404.669, 'duration': 6.286}, {'end': 1417.319, 'text': 'and once you do that, You need to select the transpose option over here and click on OK.', 'start': 1410.955, 'duration': 6.364}, {'end': 1423.145, 'text': 'And now you will see that the columns and the rows have been transposed.', 'start': 1417.88, 'duration': 5.265}, {'end': 1427.529, 'text': 'So your row name was item and that has become the column heading.', 'start': 1423.465, 'duration': 4.064}, {'end': 1434.476, 'text': 'You had row name as amount and that has become the column heading and all your values have been transposed.', 'start': 1428.03, 'duration': 6.446}, {'end': 1437.498, 'text': 'in this particular format.', 'start': 1435.237, 'duration': 2.261}, {'end': 1442.22, 'text': "now there is another way of doing that, and again that's using your formulas.", 'start': 1437.498, 'duration': 4.722}, {'end': 1452.623, 'text': 'so what you can do is you can transpose with a formula also, and that basically works when you have similar kind of data.', 'start': 1442.22, 'duration': 10.403}, {'end': 1456.124, 'text': 'so this has six columns and basically two rows.', 'start': 1452.623, 'duration': 3.501}, {'end': 1459.125, 'text': 'so you can basically do this.', 'start': 1456.124, 'duration': 3.001}, {'end': 1463.227, 'text': 'so you can select this, and earlier we were doing a copy.', 'start': 1459.125, 'duration': 4.102}, {'end': 1471.985, 'text': 'But now what we would want to do is we would want to just look at the row numbers, which tells me it is C33, C34,', 'start': 1463.72, 'duration': 8.265}, {'end': 1475.887, 'text': 'and it starts with C and ends in with your H column.', 'start': 1471.985, 'duration': 3.902}, {'end': 1481.01, 'text': 'So what we can do is we know that we have six columns and two rows.', 'start': 1476.268, 'duration': 4.742}, {'end': 1486.654, 'text': 'So transposing that would actually give me two columns and six rows.', 'start': 1481.571, 'duration': 5.083}, {'end': 1494.941, 'text': 'So what we can do is we can select two columns and six rows in our excel sheet.', 'start': 1487.114, 'duration': 7.827}], 'summary': 'The transcript covers various excel techniques like filling, splitting, and transposing data.', 'duration': 500.903, 'max_score': 994.038, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI994038.jpg'}, {'end': 1063.115, 'src': 'embed', 'start': 1031.454, 'weight': 7, 'content': [{'end': 1039.877, 'text': 'so this is an easier option of doing a fill when you would want to have the formula applied to every row,', 'start': 1031.454, 'duration': 8.423}, {'end': 1044.56, 'text': 'as it occurs in the first row or the last row.', 'start': 1039.877, 'duration': 4.683}, {'end': 1048.344, 'text': 'we could test this by, for example, selecting these fields.', 'start': 1044.56, 'duration': 3.784}, {'end': 1052.847, 'text': 'i could delete them, and i have here which says 130.', 'start': 1048.344, 'duration': 4.503}, {'end': 1063.115, 'text': 'i could just place my cursor here and i could drag it all the way up, and that should also do the same magic which we were seeing from top down.', 'start': 1052.847, 'duration': 10.268}], 'summary': 'Demonstrating a quicker method to apply formulas to every row, with practical example and result of 130.', 'duration': 31.661, 'max_score': 1031.454, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI1031454.jpg'}, {'end': 1106.955, 'src': 'embed', 'start': 1078.922, 'weight': 8, 'content': [{'end': 1089.146, 'text': 'which basically helps us in splitting the data when we have some kind of pattern or when we have some kind of delimiters in our data in, say,', 'start': 1078.922, 'duration': 10.224}, {'end': 1096.651, 'text': 'one particular column, and we would want to derive the values out of it, so we can always use the splitting option.', 'start': 1089.146, 'duration': 7.505}, {'end': 1106.955, 'text': 'now the easiest option would be so for example, we have our email column which has the email ids and which We can clearly see has a first name, dot,', 'start': 1096.651, 'duration': 10.304}], 'summary': 'Data splitting is useful for extracting values from columns, like email ids with first names.', 'duration': 28.033, 'max_score': 1078.922, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI1078922.jpg'}, {'end': 1340.497, 'src': 'embed', 'start': 1308.649, 'weight': 9, 'content': [{'end': 1321.281, 'text': 'the benefit is that you can always use this, wherein, if you do some kind of transformation using your formulas, if your original data gets updated,', 'start': 1308.649, 'duration': 12.632}, {'end': 1324.603, 'text': 'then the split data will also get updated.', 'start': 1321.281, 'duration': 3.322}, {'end': 1336.888, 'text': "And that's the benefit of using formulas where you can place values from one cell into multiple cells based on execution of your details in the formulas.", 'start': 1325.163, 'duration': 11.725}, {'end': 1340.497, 'text': 'How about using the transpose option?', 'start': 1337.975, 'duration': 2.522}], 'summary': 'Benefit of using formulas: updated split data, values from one cell into multiple cells, using transpose option.', 'duration': 31.848, 'max_score': 1308.649, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI1308649.jpg'}, {'end': 1588.253, 'src': 'embed', 'start': 1556.84, 'weight': 10, 'content': [{'end': 1569.045, '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': 1556.84, 'duration': 12.205}, {'end': 1583.671, 'text': 'Apart from working on additions, subtractions, filling up your data, sorting the data or basically splitting your data, transposing your data,', 'start': 1569.685, 'duration': 13.986}, {'end': 1588.253, 'text': 'one of the other requirements is sorting and filtering your data.', 'start': 1583.671, 'duration': 4.582}], 'summary': 'Excel can transpose data, sort, filter, and perform arithmetic operations.', 'duration': 31.413, 'max_score': 1556.84, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI1556840.jpg'}, {'end': 2071.592, 'src': 'embed', 'start': 2040.569, 'weight': 11, 'content': [{'end': 2046.414, 'text': 'or basically converting your data into a tabular format and then doing some easy computations.', 'start': 2040.569, 'duration': 5.845}, {'end': 2049.096, 'text': 'So click on this tables option here.', 'start': 2047.014, 'duration': 2.082}, {'end': 2056.442, 'text': 'Now here we see there is some data which is in five columns and n number of rows.', 'start': 2049.396, 'duration': 7.046}, {'end': 2063.848, 'text': 'So I can basically select this data and then what I can do is I can insert,', 'start': 2056.922, 'duration': 6.926}, {'end': 2070.013, 'text': "choose the table option and then it says my table has headers and we'll be okay with that.", 'start': 2063.848, 'duration': 6.165}, {'end': 2071.592, 'text': "I'll say okay.", 'start': 2070.532, 'duration': 1.06}], 'summary': 'Converting data into a tabular format, with 5 columns and n rows, and performing computations.', 'duration': 31.023, 'max_score': 2040.569, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI2040569.jpg'}, {'end': 2231.489, 'src': 'embed', 'start': 2207.194, 'weight': 12, 'content': [{'end': 2214.078, 'text': "so it says it is calculating the sum of the last three months and if that's what you would want to do,", 'start': 2207.194, 'duration': 6.884}, {'end': 2224.065, 'text': 'just hit on enter and it has automatically calculated the totals for all your rows for these three columns.', 'start': 2214.078, 'duration': 9.987}, {'end': 2227.247, 'text': 'so the sum formula is getting filled up.', 'start': 2224.065, 'duration': 3.182}, {'end': 2231.489, 'text': 'now i can select any particular cell and i can look in my address bar.', 'start': 2227.247, 'duration': 4.242}], 'summary': 'Automatically calculates the sum of the last three months for all rows and columns.', 'duration': 24.295, 'max_score': 2207.194, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI2207194.jpg'}, {'end': 2735.971, 'src': 'heatmap', 'start': 2231.489, 'weight': 0.77, 'content': [{'end': 2245.94, 'text': 'so it has already given me the formula where it has started calculating the sum from the October column till the December column and has given me the calculated values of the columns.', 'start': 2231.489, 'duration': 14.451}, {'end': 2251.204, 'text': 'what we can also do is we can get total rows in the table now.', 'start': 2245.94, 'duration': 5.264}, {'end': 2253.386, 'text': "that's a simpler option.", 'start': 2251.204, 'duration': 2.182}, {'end': 2264.442, 'text': 'so what we can do is we can select any cell in this particular table and then we see that there is a table tools design option showing up here.', 'start': 2253.386, 'duration': 11.056}, {'end': 2269.364, 'text': "now I can select this and then it says well, let's get a total row.", 'start': 2264.442, 'duration': 4.922}, {'end': 2276.968, 'text': "so let's select this and it automatically populates the total here, and if you would want the average,", 'start': 2269.364, 'duration': 7.604}, {'end': 2282.53, 'text': "then we could select this and from the drop down I can select what I'm interested in.", 'start': 2276.968, 'duration': 5.562}, {'end': 2287.113, 'text': 'so, for example, I would want the average values and not the total.', 'start': 2283.17, 'duration': 3.943}, {'end': 2292.558, 'text': 'I could just select this and that gives me the average of these values.', 'start': 2287.113, 'duration': 5.445}, {'end': 2300.964, 'text': 'so we can always do simpler computations here by converting our data into table format.', 'start': 2292.558, 'duration': 8.406}, {'end': 2308.03, 'text': "let's learn about one more efficient way of working with the data, and that's using your drop downs.", 'start': 2300.964, 'duration': 7.066}, {'end': 2311.332, 'text': "so let's see how drop downs work here.", 'start': 2308.03, 'duration': 3.302}, {'end': 2320.33, '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': 2311.332, 'duration': 8.998}, {'end': 2323.753, 'text': 'you would want to enter the values in department.', 'start': 2320.33, 'duration': 3.423}, {'end': 2331.519, 'text': 'however, you would want to select the department should either have produce or meat and bakery,', 'start': 2323.753, 'duration': 7.766}, {'end': 2338.084, 'text': 'and these are the only three options which should be available for any user to fill in the values.', 'start': 2331.519, 'duration': 6.565}, {'end': 2339.265, 'text': 'how do we do that?', 'start': 2338.084, 'duration': 1.181}, {'end': 2345.051, 'text': 'so we can basically create a table by pressing control D.', 'start': 2339.265, 'duration': 5.786}, {'end': 2356.815, 'text': 'so what I can do is, under my department here I can select one of the cells and then I can do a control T that basically converts this into a table.', 'start': 2345.051, 'duration': 11.764}, {'end': 2359.956, 'text': 'I can say okay, and my table is created.', 'start': 2356.815, 'duration': 3.141}, {'end': 2371.575, 'text': 'now what I can do is, once this part is done, We can select all the blank fields here where we would want this drop down to be applicable.', 'start': 2359.956, 'duration': 11.619}, {'end': 2379.498, 'text': 'now, under your data tab, you can go in and select data validation and this has an option called data validation.', 'start': 2371.575, 'duration': 7.923}, {'end': 2380.178, 'text': 'click on this.', 'start': 2379.498, 'duration': 0.68}, {'end': 2383.205, 'text': 'which basically says allow any value.', 'start': 2381.004, 'duration': 2.201}, {'end': 2384.905, 'text': 'So here I will select.', 'start': 2383.445, 'duration': 1.46}, {'end': 2397.17, 'text': 'I would want to give a list of values and then I can type in my values here, which I can say produce, say for example meat, and then say bakery.', 'start': 2384.905, 'duration': 12.265}, {'end': 2398.831, 'text': 'Now these are the values.', 'start': 2397.67, 'duration': 1.161}, {'end': 2401.232, 'text': 'So we can click on OK.', 'start': 2399.531, 'duration': 1.701}, {'end': 2408.898, 'text': 'And once we have done that, We basically have a drop down here next to apples,', 'start': 2402.892, 'duration': 6.006}, {'end': 2414.6, 'text': 'which will only show us the values which we can feed in under the department column.', 'start': 2408.898, 'duration': 5.702}, {'end': 2422.123, 'text': 'So I can go into every cell and then I can basically choose what is the department which handles this.', 'start': 2415.08, 'duration': 7.043}, {'end': 2431.206, 'text': 'And then basically I can select one of these from the drop down, so this is an easier option of creating your drop down.', 'start': 2422.842, 'duration': 8.364}, {'end': 2442.192, 'text': 'And then feeding in the values from the set of values which you have defined here on the right, so this is a simple example of using your drop downs.', 'start': 2431.807, 'duration': 10.385}, {'end': 2453.568, 'text': 'working with your tables, working with your sort and filter, transpose, split, filling up your data, adding in some data here, and similarly,', 'start': 2442.938, 'duration': 10.63}, {'end': 2462.357, 'text': 'you can use excel for more than one use case, using its inbuilt features to easily work with your data.', 'start': 2453.568, 'duration': 8.789}, {'end': 2473.871, 'text': "Let's see how we can import data or bring in data into our Excel from your local machine or from an external web source.", 'start': 2463.064, 'duration': 10.807}, {'end': 2480.975, 'text': 'If getting your learning started is half the battle, what if you could do that for free? Visit SkillUp by SimplyLearn.', 'start': 2474.631, 'duration': 6.344}, {'end': 2483.597, 'text': 'Click on the link in the description to know more.', 'start': 2481.316, 'duration': 2.281}, {'end': 2490.101, 'text': 'So what we can do is we can open up a blank Excel sheet and say, for example,', 'start': 2484.798, 'duration': 5.303}, {'end': 2498.532, 'text': 'you have been provided a text file or a csv file and you would want to import that data into your excel sheet.', 'start': 2490.101, 'duration': 8.431}, {'end': 2500.374, 'text': 'that can be easily done.', 'start': 2498.532, 'duration': 1.842}, {'end': 2503.417, 'text': "so right now i've opened an excel sheet.", 'start': 2500.374, 'duration': 3.043}, {'end': 2509.895, 'text': 'now i can click on data And here I have an option which says existing connections from other data sources.', 'start': 2503.417, 'duration': 6.478}, {'end': 2514.038, 'text': 'So or you can click on connections if you have already created some.', 'start': 2510.635, 'duration': 3.403}, {'end': 2517, 'text': 'So we can click on from other sources.', 'start': 2514.458, 'duration': 2.542}, {'end': 2525.807, 'text': 'So this is one option where you can connect to your different data sources and you can get the data from one of these.', 'start': 2517.661, 'duration': 8.146}, {'end': 2529.63, 'text': 'What we can also do is I can click on connections.', 'start': 2526.367, 'duration': 3.263}, {'end': 2531.251, 'text': 'Now it says there is none.', 'start': 2529.87, 'duration': 1.381}, {'end': 2533.032, 'text': 'I can click on add.', 'start': 2531.271, 'duration': 1.761}, {'end': 2541.385, 'text': 'It says well, show the connections where connection files on network, connection files on this computer.', 'start': 2533.392, 'duration': 7.993}, {'end': 2545.346, 'text': "So I can say, let's get some files from this computer.", 'start': 2541.885, 'duration': 3.461}, {'end': 2553.39, 'text': 'Now, if that does not show up something, so say browse for more, and that basically shows you different options.', 'start': 2546.047, 'duration': 7.343}, {'end': 2558.976, 'text': "So let's basically select a folder where I have some data sets.", 'start': 2553.47, 'duration': 5.506}, {'end': 2565.338, 'text': "i'll click in here and this is basically a folder where i have some data sets.", 'start': 2558.976, 'duration': 6.362}, {'end': 2574.121, 'text': "now let me select this particular file, and i know it is a csv file, so let's click on open now.", 'start': 2565.338, 'duration': 8.783}, {'end': 2581.604, 'text': 'if you would want to verify this, you could have gone and looked into the properties of the file and it says it is a dot csv file,', 'start': 2574.121, 'duration': 7.483}, {'end': 2583.64, 'text': 'which is what we are interested in.', 'start': 2582.179, 'duration': 1.461}, {'end': 2584.961, 'text': "So I'll take this file.", 'start': 2583.78, 'duration': 1.181}, {'end': 2586.282, 'text': "I'll say open.", 'start': 2585.541, 'duration': 0.741}, {'end': 2593.987, 'text': "Now this basically shows me the text import wizard option, which says, is the file delimited? I'll say yes.", 'start': 2586.662, 'duration': 7.325}, {'end': 2595.588, 'text': 'Click on next.', 'start': 2594.827, 'duration': 0.761}, {'end': 2599.37, 'text': 'So I will select comma as my delimiter.', 'start': 2596.108, 'duration': 3.262}, {'end': 2602.272, 'text': 'I can say text qualifier is none.', 'start': 2599.951, 'duration': 2.321}, {'end': 2604.894, 'text': 'Now this is my data.', 'start': 2603.073, 'duration': 1.821}, {'end': 2611.038, 'text': 'So my data preview is already showing me the data is what is the data in the CSV file.', 'start': 2605.154, 'duration': 5.884}, {'end': 2616.744, 'text': 'You can click on next and then you have an option that says data format is general.', 'start': 2611.618, 'duration': 5.126}, {'end': 2619.046, 'text': 'You can go for date format.', 'start': 2617.264, 'duration': 1.782}, {'end': 2620.788, 'text': 'You can go for advanced options.', 'start': 2619.086, 'duration': 1.702}, {'end': 2622.61, 'text': "So I'll just say finish.", 'start': 2621.249, 'duration': 1.361}, {'end': 2627.035, 'text': 'And basically now this has been created here.', 'start': 2623.691, 'duration': 3.344}, {'end': 2634.333, 'text': 'So we basically have this, and now I can click on close.', 'start': 2627.796, 'duration': 6.537}, {'end': 2638.014, 'text': 'now, once you have done that, you can click on existing connections.', 'start': 2634.333, 'duration': 3.681}, {'end': 2644.156, 'text': 'it shows me the data which we have here, the connection which we have created, say open,', 'start': 2638.014, 'duration': 6.142}, {'end': 2651.459, 'text': 'and then it says do you want to import this data or bring this data into existing worksheet?', 'start': 2644.156, 'duration': 7.303}, {'end': 2655.78, 'text': 'you can also say add this to a data model if you are doing some data modeling.', 'start': 2651.459, 'duration': 4.321}, {'end': 2662.522, 'text': 'so click on ok, and now this data is automatically inserted in my excel file.', 'start': 2655.78, 'duration': 6.742}, {'end': 2669.016, 'text': 'I can basically save it into this particular sheet.', 'start': 2663.132, 'duration': 5.884}, {'end': 2677.241, 'text': 'Now what we can also do is we can also start a new sheet and that does not have a data and we can get some other data from web.', 'start': 2669.276, 'duration': 7.965}, {'end': 2685.366, 'text': "So what I can do is I can go into my GitHub and let's say I would be interested in this CSV file.", 'start': 2677.801, 'duration': 7.565}, {'end': 2690.349, 'text': 'So I can select this and this is my GitHub path, a path on web.', 'start': 2685.926, 'duration': 4.423}, {'end': 2697.733, 'text': 'So I can click on draw and that basically gives me the raw path where this particular file is.', 'start': 2690.909, 'duration': 6.824}, {'end': 2705.037, 'text': 'Now you can select this, copy this particular path and here you can come back to your Excel sheet.', 'start': 2697.913, 'duration': 7.124}, {'end': 2707.179, 'text': 'We would be interested in getting the data from web.', 'start': 2705.137, 'duration': 2.042}, {'end': 2712.182, 'text': 'might be from a text file where we will have to specify the delimiters.', 'start': 2708.019, 'duration': 4.163}, {'end': 2720.068, 'text': "or let's go to web, and here i can given the web path from where i would be interested in getting the file.", 'start': 2712.182, 'duration': 7.886}, {'end': 2725.091, 'text': "let's give the github path, which is publicly available, and then click on import.", 'start': 2720.068, 'duration': 5.023}, {'end': 2730.916, 'text': 'now, once you click on import, it tells me there are two fields data and value.', 'start': 2725.091, 'duration': 5.825}, {'end': 2735.971, 'text': 'these are within double quotes, separated by comma.', 'start': 2730.916, 'duration': 5.055}], 'summary': 'Excel allows for easy calculations, data tables, and importing data from various sources.', 'duration': 504.482, 'max_score': 2231.489, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI2231489.jpg'}, {'end': 2431.206, 'src': 'embed', 'start': 2402.892, 'weight': 13, 'content': [{'end': 2408.898, 'text': 'And once we have done that, We basically have a drop down here next to apples,', 'start': 2402.892, 'duration': 6.006}, {'end': 2414.6, 'text': 'which will only show us the values which we can feed in under the department column.', 'start': 2408.898, 'duration': 5.702}, {'end': 2422.123, 'text': 'So I can go into every cell and then I can basically choose what is the department which handles this.', 'start': 2415.08, 'duration': 7.043}, {'end': 2431.206, 'text': 'And then basically I can select one of these from the drop down, so this is an easier option of creating your drop down.', 'start': 2422.842, 'duration': 8.364}], 'summary': 'Creating a drop down menu for department selection, streamlining data entry.', 'duration': 28.314, 'max_score': 2402.892, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI2402892.jpg'}, {'end': 2517, 'src': 'embed', 'start': 2463.064, 'weight': 14, 'content': [{'end': 2473.871, 'text': "Let's see how we can import data or bring in data into our Excel from your local machine or from an external web source.", 'start': 2463.064, 'duration': 10.807}, {'end': 2480.975, 'text': 'If getting your learning started is half the battle, what if you could do that for free? Visit SkillUp by SimplyLearn.', 'start': 2474.631, 'duration': 6.344}, {'end': 2483.597, 'text': 'Click on the link in the description to know more.', 'start': 2481.316, 'duration': 2.281}, {'end': 2490.101, 'text': 'So what we can do is we can open up a blank Excel sheet and say, for example,', 'start': 2484.798, 'duration': 5.303}, {'end': 2498.532, 'text': 'you have been provided a text file or a csv file and you would want to import that data into your excel sheet.', 'start': 2490.101, 'duration': 8.431}, {'end': 2500.374, 'text': 'that can be easily done.', 'start': 2498.532, 'duration': 1.842}, {'end': 2503.417, 'text': "so right now i've opened an excel sheet.", 'start': 2500.374, 'duration': 3.043}, {'end': 2509.895, 'text': 'now i can click on data And here I have an option which says existing connections from other data sources.', 'start': 2503.417, 'duration': 6.478}, {'end': 2514.038, 'text': 'So or you can click on connections if you have already created some.', 'start': 2510.635, 'duration': 3.403}, {'end': 2517, 'text': 'So we can click on from other sources.', 'start': 2514.458, 'duration': 2.542}], 'summary': 'Learn how to import data into excel from local or web sources, easily and for free with skillup by simplylearn.', 'duration': 53.936, 'max_score': 2463.064, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI2463064.jpg'}], 'start': 59.919, 'title': 'Excel data analytics training and techniques', 'summary': 'Covers a comprehensive agenda for excel data analytics training, including functions and formulas, conditional formatting, pivot tables, and data recovery, aimed at improving data management efficiency and providing insights and visualizations for reporting and analysis. it also includes techniques for data manipulation such as splitting, transposing, sorting, and filtering, along with working with tables, importing data from local machine and web sources, demonstrating efficient data handling.', 'chapters': [{'end': 596.442, 'start': 59.919, 'title': 'Excel data analytics training agenda', 'summary': 'Covers a comprehensive agenda for excel data analytics training, including functions and formulas, conditional formatting, pivot tables, and data recovery, aimed at improving data management efficiency and providing insights and visualizations for reporting and analysis.', 'duration': 536.523, 'highlights': ['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 is highlighted as a platform for performing mathematical calculations on large datasets, meeting the key requirement of organizations.', "Excel also allows you to beautify your data and present it in the form of charts, tables and data bars. Excel's capability to beautify and present data in visual forms such as charts and tables is emphasized.", 'Excel provides good security for your data. Excel files have the feature of password protection. The data security feature of Excel, including password protection for files, is emphasized.', 'The chapter covers a comprehensive agenda for Excel data analytics training, including functions and formulas, conditional formatting, pivot tables, and data recovery, aimed at improving data management efficiency and providing insights and visualizations for reporting and analysis. The agenda for Excel data analytics training, covering functions, formulas, conditional formatting, pivot tables, and data recovery, is highlighted as a comprehensive approach for improving data management efficiency and providing insights for reporting and analysis.']}, {'end': 1197.954, 'start': 597.302, 'title': 'Excel formulas and data manipulation', 'summary': 'Covers various excel functions including sum, sumif, autosum, filling down and right to manipulate and analyze data, with examples and shortcuts provided, demonstrating how to quickly and efficiently perform data calculations and manipulations in excel.', 'duration': 600.652, 'highlights': ['The chapter covers various Excel functions including SUM, SUMIF, AutoSum, Filling Down and Right to manipulate and analyze data Demonstrates the range of functions available for manipulating and analyzing data in Excel.', 'Examples and shortcuts provided, demonstrating how to quickly and efficiently perform data calculations and manipulations in Excel Illustrates the practical application of Excel functions and shortcuts to streamline data calculations and manipulations.', 'Provides step-by-step instructions for utilizing functions such as SUM, SUMIF, and AutoSum, including examples of selecting specific data ranges and applying conditions Offers detailed guidance on utilizing specific Excel functions and applying conditions for data analysis.', 'Demonstrates the process of filling down and right to efficiently apply formulas across rows and columns in Excel Illustrates the efficient application of formulas across rows and columns in Excel using the filling down and right techniques.', 'Explains the usage of splitting option to derive values from data with delimiters, using the example of splitting email addresses into first and last names Provides a practical example of using the splitting option to derive values from data with delimiters, such as splitting email addresses into first and last names.']}, {'end': 1990.215, 'start': 1197.954, 'title': 'Excel data manipulation techniques', 'summary': 'Covers techniques such as splitting data based on delimiters, transposing rows into columns, sorting and filtering data in excel, with examples of sorting alphabetically, by values, by date, and by color, and how to add, delete, and modify filters.', 'duration': 792.261, 'highlights': ['The chapter covers techniques such as splitting data based on delimiters, transposing rows into columns, and sorting and filtering data in Excel, with examples of sorting alphabetically, by values, by date, and by color, and how to add, delete, and modify filters.', 'The benefit of using formulas for splitting data is the ability to update the split data when the original data is updated.', "Transposing data in Excel can be done by selecting all values and using the 'paste special' option with the 'transpose' feature, or by using a formula, and it involves changing the horizontal orientation to the vertical orientation.", 'The chapter explains how to sort data alphabetically and by values, and how to sort by date or color in Excel, with examples of sorting alphabetically and by values, and also sorting by date and color, and the ability to add, delete, and modify filters.', 'The chapter provides examples of how to add, delete, and modify filters in Excel, including using number filters to sort data based on specific criteria, such as above average, below average, and greater than a specified value.']}, {'end': 2462.357, 'start': 1990.215, 'title': 'Working with excel data', 'summary': 'Explains how to sort, filter, and work with tables in excel, including converting data into tabular format, adding rows and columns, performing calculations, using drop downs, and leveraging inbuilt features for efficient data handling.', 'duration': 472.142, 'highlights': ["The chapter explains how to convert data into tabular format and add rows and columns, demonstrating the process of inserting a table with headers and adding and filling rows and columns with values like 'chocolates' and 25,000. Converting data into tabular format, adding and filling rows and columns, inserting a table with headers, adding values like 'chocolates' and 25,000.", "The chapter demonstrates the use of Excel for performing calculations, such as calculating the sum of the last three months using the 'sum' formula and obtaining total rows and averages in the table. Performing calculations in Excel, calculating the sum of the last three months, obtaining total rows and averages in the table.", "The chapter explains the process of creating drop downs in Excel to facilitate the selection of specific values, such as 'produce,' 'meat,' and 'bakery,' for the department column, enhancing data input efficiency. Creating drop downs in Excel, facilitating selection of specific values like 'produce,' 'meat,' and 'bakery,' for the department column."]}, {'end': 2917.858, 'start': 2463.064, 'title': 'Importing data into excel', 'summary': 'Demonstrates how to import data into excel from local machine and web sources, including steps for importing csv files, getting data from web, and connecting to an existing database.', 'duration': 454.794, 'highlights': ['Importing Data from Local Machine The process of importing data from a local machine into Excel is explained, including steps for importing CSV files, verifying file properties, using the text import wizard, and inserting the data into the Excel file.', 'Importing Data from Web The steps for importing data from the web into Excel, including specifying the web path, importing the data, and manipulating the imported data using text to columns and filtering, are detailed.', 'Connecting to Existing Database The process of connecting to an existing database from Excel, including options for connecting to different data sources, creating SQL server connections, and importing data from various sources, is explained.']}], 'duration': 2857.939, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI59919.jpg', 'highlights': ['The chapter covers a comprehensive agenda for Excel data analytics training, including functions and formulas, conditional formatting, pivot tables, and data recovery, aimed at improving data management efficiency and providing insights and visualizations for reporting and analysis.', 'Excel proves to be a great platform to perform various mathematical calculation on large data sets, meeting the key requirement of organizations.', "Excel's capability to beautify and present data in visual forms such as charts and tables is emphasized.", 'Excel provides good security for your data, including password protection for files.', 'Demonstrates the range of functions available for manipulating and analyzing data in Excel.', 'Illustrates the practical application of Excel functions and shortcuts to streamline data calculations and manipulations.', 'Offers detailed guidance on utilizing specific Excel functions and applying conditions for data analysis.', 'Illustrates the efficient application of formulas across rows and columns in Excel using the filling down and right techniques.', 'Provides a practical example of using the splitting option to derive values from data with delimiters, such as splitting email addresses into first and last names.', 'The benefit of using formulas for splitting data is the ability to update the split data when the original data is updated.', 'Explains the process of transposing data in Excel and the ability to sort and filter data with practical examples.', "Converting data into tabular format, adding and filling rows and columns, inserting a table with headers, adding values like 'chocolates' and 25,000.", 'Performing calculations in Excel, calculating the sum of the last three months, obtaining total rows and averages in the table.', "Creating drop downs in Excel, facilitating selection of specific values like 'produce,' 'meat,' and 'bakery,' for the department column.", 'The process of importing data from a local machine into Excel is explained, including steps for importing CSV files, verifying file properties, using the text import wizard, and inserting the data into the Excel file.', 'The steps for importing data from the web into Excel, including specifying the web path, importing the data, and manipulating the imported data using text to columns and filtering, are detailed.', 'The process of connecting to an existing database from Excel, including options for connecting to different data sources, creating SQL server connections, and importing data from various sources, is explained.']}, {'end': 4334.796, 'segs': [{'end': 3021.111, 'src': 'embed', 'start': 2994.36, 'weight': 1, 'content': [{'end': 3000.883, 'text': 'Or you could say the oldest date or the earliest month will be towards the lower side of your sheet.', 'start': 2994.36, 'duration': 6.523}, {'end': 3003.585, 'text': 'So here we can select date listed.', 'start': 3001.404, 'duration': 2.181}, {'end': 3005.746, 'text': 'Now I can say let it sort.', 'start': 3004.105, 'duration': 1.641}, {'end': 3010.744, 'text': 'based on cell values and the order, what we have here.', 'start': 3006.601, 'duration': 4.143}, {'end': 3014.327, 'text': "so we have newest to oldest, so let's select this.", 'start': 3010.744, 'duration': 3.583}, {'end': 3015.647, 'text': 'i can say okay.', 'start': 3014.327, 'duration': 1.32}, {'end': 3021.111, 'text': 'and now, if you see, the date has been sorted, so we have your 10, 18 2007 on the top.', 'start': 3015.647, 'duration': 5.464}], 'summary': 'The date has been sorted with 10/18/2007 on top.', 'duration': 26.751, 'max_score': 2994.36, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI2994360.jpg'}, {'end': 3076.581, 'src': 'embed', 'start': 3051.244, 'weight': 0, 'content': [{'end': 3058.887, 'text': 'So, for example, I would want to sort the data in ascending order of area and descending order of agent name.', 'start': 3051.244, 'duration': 7.643}, {'end': 3061.548, 'text': "How do we do that? So let's look into this.", 'start': 3059.167, 'duration': 2.381}, {'end': 3063.008, 'text': 'So this is here.', 'start': 3061.968, 'duration': 1.04}, {'end': 3065.81, 'text': 'I already have the result here.', 'start': 3063.609, 'duration': 2.201}, {'end': 3072.232, 'text': "And how did I get this? So I'm looking for ascending order of area and descending order of agent name.", 'start': 3066.13, 'duration': 6.102}, {'end': 3076.581, 'text': 'So we can start with any particular column that does not matter.', 'start': 3072.793, 'duration': 3.788}], 'summary': 'Sorting data by ascending area and descending agent name', 'duration': 25.337, 'max_score': 3051.244, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI3051244.jpg'}, {'end': 3325.425, 'src': 'embed', 'start': 3294.238, 'weight': 2, 'content': [{'end': 3298.239, 'text': "You have in the 12th row something like Lang, and that's what we are doing.", 'start': 3294.238, 'duration': 4.001}, {'end': 3307.121, 'text': 'So we can basically arrange the data in a particular order by choosing a custom list and then sorting your data.', 'start': 3298.72, 'duration': 8.401}, {'end': 3318.683, 'text': "So that's one more simpler task of what we have done, where we have sorted the data in the order where, under our area column,", 'start': 3307.822, 'duration': 10.861}, {'end': 3325.425, 'text': 'we first wanted South County, then we wanted Central Data and then we wanted North County.', 'start': 3318.683, 'duration': 6.742}], 'summary': 'The data was sorted by a custom list, arranging south county, central data, and north county in a specific order.', 'duration': 31.187, 'max_score': 3294.238, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI3294238.jpg'}, {'end': 3372.21, 'src': 'embed', 'start': 3345.683, 'weight': 3, 'content': [{'end': 3351.251, 'text': 'So the problem statement is we would want to find all the houses in central area.', 'start': 3345.683, 'duration': 5.568}, {'end': 3359.522, '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': 3351.872, 'duration': 7.65}, {'end': 3362.747, 'text': 'So how do you do it? So you can select this area.', 'start': 3359.562, 'duration': 3.185}, {'end': 3367.028, 'text': 'and say, for example, I would want to apply filter.', 'start': 3363.447, 'duration': 3.581}, {'end': 3372.21, 'text': "I can just go in here and I can say let's get a filter on my first row.", 'start': 3367.028, 'duration': 5.182}], 'summary': 'Goal: find all houses in central area using filtering and sorting.', 'duration': 26.527, 'max_score': 3345.683, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI3345683.jpg'}, {'end': 3625.949, 'src': 'embed', 'start': 3570.39, 'weight': 8, 'content': [{'end': 3580.294, 'text': 'And then in filter you have an option called advanced, and here what I can do is I can say I would want to filter the list in the place,', 'start': 3570.39, 'duration': 9.904}, {'end': 3581.914, 'text': "but that's not what I would want to do.", 'start': 3580.294, 'duration': 1.62}, {'end': 3584.115, 'text': "So I'll say copy to another location.", 'start': 3582.314, 'duration': 1.801}, {'end': 3589.159, 'text': 'And here if you see the list range will tell me that this is the data.', 'start': 3584.736, 'duration': 4.423}, {'end': 3591.62, 'text': 'So A1 to J126.', 'start': 3589.359, 'duration': 2.261}, {'end': 3594.862, 'text': 'So A to J column selected and all the rows.', 'start': 3591.66, 'duration': 3.202}, {'end': 3600.245, 'text': "Criteria range is basically based on what I've given here.", 'start': 3595.703, 'duration': 4.542}, {'end': 3607.99, 'text': 'So that is M from 1 to V which is 3.', 'start': 3600.325, 'duration': 7.665}, {'end': 3618.801, 'text': "So I'm selecting these columns and then I'm saying all the way, whatever criteria I've given and copy to, I'm saying M eight to V eight.", 'start': 3607.99, 'duration': 10.811}, {'end': 3623.006, 'text': 'So that basically will give me my filtered result.', 'start': 3619.262, 'duration': 3.744}, {'end': 3625.949, 'text': 'So you could basically just say, okay.', 'start': 3623.406, 'duration': 2.543}], 'summary': 'Using advanced filter, columns a to j and rows 1 to 126 were filtered based on criteria m1 to v3 and copied to m8 to v8.', 'duration': 55.559, 'max_score': 3570.39, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI3570390.jpg'}, {'end': 3682.516, 'src': 'embed', 'start': 3653.291, 'weight': 4, 'content': [{'end': 3661.257, 'text': 'So in this way, you can have your customized filter applied on two different columns and get your data,', 'start': 3653.291, 'duration': 7.966}, {'end': 3670.086, 'text': 'which can be either replacing the existing content or in the same sheet in different set of columns and rows.', 'start': 3661.257, 'duration': 8.829}, {'end': 3671.827, 'text': 'you can have your result.', 'start': 3670.086, 'duration': 1.741}, {'end': 3674.69, 'text': "Let's look at one more example of filtering,", 'start': 3672.088, 'duration': 2.602}, {'end': 3682.516, 'text': 'where you are trying to filter the data based on an AND condition condition being met in two different columns,', 'start': 3674.69, 'duration': 7.826}], 'summary': 'Customized filter applied on two columns for data retrieval.', 'duration': 29.225, 'max_score': 3653.291, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI3653291.jpg'}, {'end': 3876.562, 'src': 'embed', 'start': 3852.972, 'weight': 6, 'content': [{'end': 3859.795, 'text': "let's say okay, and now i get my data, which is based on the question which has been asked,", 'start': 3852.972, 'duration': 6.823}, {'end': 3866.658, 'text': 'that you would want the agents with a house in north county area having two bedrooms and single type family.', 'start': 3859.795, 'duration': 6.863}, {'end': 3876.562, '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': 3866.658, 'duration': 9.904}], 'summary': 'Advanced filtering allows storing relevant data in different locations.', 'duration': 23.59, 'max_score': 3852.972, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI3852972.jpg'}, {'end': 4176.133, 'src': 'embed', 'start': 4145.85, 'weight': 7, 'content': [{'end': 4148.292, 'text': 'So say for example, this is the data given to you.', 'start': 4145.85, 'duration': 2.442}, {'end': 4154.738, 'text': 'And the question is that you would want to find all the houses in North County again.', 'start': 4148.673, 'duration': 6.065}, {'end': 4165.587, 'text': "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': 4154.738, 'duration': 10.849}, {'end': 4176.133, 'text': 'So the bedroom has conditional, so it has all three and four, and then basically you have list price which is greater than 300 000.', 'start': 4165.707, 'duration': 10.426}], 'summary': 'Find houses in north county area with list price > $300,000 and 3-4 bedrooms.', 'duration': 30.283, 'max_score': 4145.85, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI4145850.jpg'}], 'start': 2917.858, 'title': 'Advanced data filtering techniques', 'summary': 'Covers advanced data filtering techniques in excel, including sorting in specific orders, filtering real estate data with multiple conditions, and applying advanced filtering with specific criteria, such as list prices and property features.', 'chapters': [{'end': 3318.683, 'start': 2917.858, 'title': 'Sorting data in excel', 'summary': 'Explains how to sort data in excel, including sorting in newest to oldest order, sorting in ascending order of area and descending order of agent name, and sorting according to a specific order of area.', 'duration': 400.825, 'highlights': ['The chapter explains how to sort data in Excel in newest to oldest order based on date listed column. The process of sorting data in newest to oldest order based on the date listed column is demonstrated, allowing users to arrange data in descending order of dates.', 'The chapter provides a demonstration of sorting data in ascending order of area and descending order of agent name in Excel. A demonstration is provided on sorting data in ascending order of area and descending order of agent name, showcasing the process of arranging data based on specific column orders.', 'The explanation includes sorting data according to a specific order of area, such as South County, Central, and North County. The chapter details the process of sorting data according to a specific order of area, allowing users to arrange the data based on a custom list and specific sorting requirements.']}, {'end': 3625.949, 'start': 3318.683, 'title': 'Filtering and sorting data in real estate', 'summary': 'Demonstrates how to filter and sort real estate data to find houses in the central area, and how to apply multiple conditions for filtering data based on different values and conditions, using practical examples and step-by-step instructions.', 'duration': 307.266, 'highlights': ['The chapter demonstrates how to filter and sort real estate data to find houses in the central area It shows the process of filtering and sorting data to find houses in the central area using practical examples and step-by-step instructions.', 'Applying multiple conditions for filtering data based on different values and conditions It explains how to apply multiple conditions for filtering data, using the example of finding houses in the central region with pool and South County without pool.', 'Step-by-step instructions on using advanced filtering options to copy filtered results to another location It provides step-by-step instructions on using advanced filtering options to copy filtered results to another location, demonstrating the practical application of the technique.']}, {'end': 3794.138, 'start': 3626.607, 'title': 'Advanced data filtering techniques', 'summary': 'Demonstrates advanced data filtering techniques using multiple criteria on different columns to obtain customized results, such as filtering data based on an and condition in two different columns and populating specific columns with filtered data.', 'duration': 167.531, 'highlights': ['The chapter illustrates the application of an advanced filter to obtain customized results by filtering data based on two columns and then displaying the result (quantifiable data).', 'The demonstration includes filtering data based on an AND condition in two different columns and populating specific columns with filtered data, such as filtering agents with a house in North County, having two bedrooms, and a single type family (quantifiable data).', 'The explanation covers the process of applying advanced criteria to filter data, including selecting values for area, specifying the number of bedrooms, and defining the type of family, exemplifying the use of AND conditions for filtering (quantifiable data).']}, {'end': 4145.386, 'start': 3794.138, 'title': 'Advanced filtering in excel', 'summary': 'Explains how to perform advanced filtering in excel, including filtering based on specific criteria, such as selecting agents with a house in the north county area, having two bedrooms and a single-family type; and filtering data based on list prices between 45,000 to 600,000 or 300,000 to 400,000. it also covers the use of and conditions for filtering data based on multiple criteria and demonstrates two methods for achieving the same result.', 'duration': 351.248, 'highlights': ['Performing advanced filtering in Excel to select agents with specific house criteria, such as location, bedrooms, and type, showcasing the ability to filter data based on multiple conditions. Filtering based on specific criteria, such as location, bedrooms, and type of house.', 'Demonstrating the process of filtering data based on list prices within specified ranges, such as between 45,000 to 600,000 or 300,000 to 400,000, showcasing the versatility of Excel for filtering numerical data. Filtering data based on list prices within specified ranges.', 'Explaining the use of AND conditions for filtering data based on multiple criteria, highlighting the method of adding conditions in the same line for the same column and across different lines for different columns. Illustrating the use of AND conditions for filtering data based on multiple criteria.']}, {'end': 4334.796, 'start': 4145.85, 'title': 'Data filtering in excel', 'summary': 'Explains how to filter data in excel to find houses in north county with a list price greater than $300,000 and having three or four bedrooms, using examples and the step-by-step process of applying filters in excel.', 'duration': 188.946, 'highlights': ['Explaining the process of filtering data in Excel to find houses in North County with a list price greater than $300,000 and having three or four bedrooms. Step-by-step explanation of filtering process, specifying conditions for list price and bedrooms.', 'Demonstrating the use of criteria range to filter data and obtain the desired result. Illustration of using criteria range and obtaining the filtered result.', 'Showing the method of copying filtered data to another location for further analysis or use. Explanation of copying filtered data for further analysis or use.']}], 'duration': 1416.938, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI2917858.jpg', 'highlights': ['The chapter provides a demonstration of sorting data in ascending order of area and descending order of agent name in Excel.', 'The chapter explains how to sort data in Excel in newest to oldest order based on date listed column.', 'The chapter details the process of sorting data according to a specific order of area, allowing users to arrange the data based on a custom list and specific sorting requirements.', 'The chapter demonstrates how to filter and sort real estate data to find houses in the central area using practical examples and step-by-step instructions.', 'Applying multiple conditions for filtering data based on different values and conditions.', 'The chapter illustrates the application of an advanced filter to obtain customized results by filtering data based on two columns and then displaying the result.', 'Performing advanced filtering in Excel to select agents with specific house criteria, such as location, bedrooms, and type, showcasing the ability to filter data based on multiple conditions.', 'Explaining the process of filtering data in Excel to find houses in North County with a list price greater than $300,000 and having three or four bedrooms.', 'Demonstrating the use of criteria range to filter data and obtain the desired result.', 'Showing the method of copying filtered data to another location for further analysis or use.']}, {'end': 5619.41, 'segs': [{'end': 4640.02, 'src': 'embed', 'start': 4611.974, 'weight': 0, 'content': [{'end': 4617.801, 'text': 'Similarly, if you want to find the average of total sales, you can use the average function.', 'start': 4611.974, 'duration': 5.827}, {'end': 4621.526, 'text': "So I'll type average and hit tab, it will auto complete.", 'start': 4617.841, 'duration': 3.685}, {'end': 4625.754, 'text': "I'll select my sales values, close the bracket.", 'start': 4622.492, 'duration': 3.262}, {'end': 4629.715, 'text': 'If I hit enter, it will give me the average total sales.', 'start': 4625.934, 'duration': 3.781}, {'end': 4634.698, 'text': "Now there's another function called SUMPRODUCT that is available in Excel.", 'start': 4630.316, 'duration': 4.382}, {'end': 4640.02, 'text': 'You can use the SUMPRODUCT function to return the sum of the product of corresponding ranges or arrays.', 'start': 4635.338, 'duration': 4.682}], 'summary': 'Excel allows users to easily calculate average and sum using functions like average and sumproduct.', 'duration': 28.046, 'max_score': 4611.974, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI4611974.jpg'}, {'end': 4710.978, 'src': 'embed', 'start': 4684.911, 'weight': 1, 'content': [{'end': 4689.916, 'text': 'so you have is even function, is odd, function is logical and so on.', 'start': 4684.911, 'duration': 5.005}, {'end': 4694.18, 'text': "now let's use them one by one and see how it works.", 'start': 4689.916, 'duration': 4.264}, {'end': 4701.407, 'text': "the is even function returns true if the given number is an even value, and it returns false if it's not an even number.", 'start': 4694.18, 'duration': 7.227}, {'end': 4703.549, 'text': 'so we have here two numbers 4 and 13.', 'start': 4701.407, 'duration': 2.142}, {'end': 4710.978, 'text': 'now, If I want to check if 4 is an even number or not, I can use the isEven function.', 'start': 4703.549, 'duration': 7.429}], 'summary': 'Demonstrating iseven function, identifying even numbers, using 4 and 13.', 'duration': 26.067, 'max_score': 4684.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI4684911.jpg'}, {'end': 4926.261, 'src': 'embed', 'start': 4894.913, 'weight': 3, 'content': [{'end': 4898.215, 'text': "Now let's explore a few other functions that you can see here.", 'start': 4894.913, 'duration': 3.302}, {'end': 4902.598, 'text': 'So the round function rounds a number to a specified number of digits.', 'start': 4898.235, 'duration': 4.363}, {'end': 4909.155, 'text': 'so suppose I want to round up 35.316 to two decimal places.', 'start': 4903.473, 'duration': 5.682}, {'end': 4915.297, 'text': "so I'll write equal to and I'll give the round function.", 'start': 4909.155, 'duration': 6.142}, {'end': 4920.539, 'text': "I'll provide my number 35.316 comma.", 'start': 4915.297, 'duration': 5.242}, {'end': 4925.3, 'text': "since I want to round up to two decimal places, I'll give two.", 'start': 4920.539, 'duration': 4.761}, {'end': 4926.261, 'text': "I'll close the bracket.", 'start': 4925.3, 'duration': 0.961}], 'summary': 'The round function can round 35.316 to two decimal places.', 'duration': 31.348, 'max_score': 4894.913, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI4894913.jpg'}, {'end': 5473.93, 'src': 'heatmap', 'start': 5222.502, 'weight': 0.946, 'content': [{'end': 5229.287, 'text': "So, I'll give 9, let's sum up the price per unit column.", 'start': 5222.502, 'duration': 6.785}, {'end': 5232.229, 'text': 'So, F2 to F4, close the bracket.', 'start': 5229.667, 'duration': 2.562}, {'end': 5233.549, 'text': 'Hit enter.', 'start': 5232.949, 'duration': 0.6}, {'end': 5238.671, 'text': 'You can see the sum of price per unit is 105.', 'start': 5233.969, 'duration': 4.702}, {'end': 5239.811, 'text': "Now let's do one more.", 'start': 5238.671, 'duration': 1.14}, {'end': 5242.852, 'text': 'Use the same subtotal function.', 'start': 5241.352, 'duration': 1.5}, {'end': 5249.074, 'text': "Now let's say I want to find the maximum value from the sales column.", 'start': 5243.793, 'duration': 5.281}, {'end': 5251.035, 'text': 'Select my total sales column.', 'start': 5249.615, 'duration': 1.42}, {'end': 5253.296, 'text': 'Close the bracket.', 'start': 5252.495, 'duration': 0.801}, {'end': 5254.796, 'text': 'Hit enter.', 'start': 5253.316, 'duration': 1.48}, {'end': 5259.778, 'text': "Returns me 480 because that's the highest or the maximum value of total sales.", 'start': 5255.516, 'duration': 4.262}, {'end': 5264.828, 'text': 'now excel provides a factorial function called fact.', 'start': 5260.686, 'duration': 4.142}, {'end': 5268.869, 'text': 'so if you want to find the factorial of a number, you can use this function.', 'start': 5264.828, 'duration': 4.041}, {'end': 5271.89, 'text': 'so find out the factorial.', 'start': 5268.869, 'duration': 3.021}, {'end': 5274.611, 'text': "so i'll use the fact function.", 'start': 5271.89, 'duration': 2.721}, {'end': 5276.832, 'text': "let's say i want to find the factorial of number 5.", 'start': 5274.611, 'duration': 2.221}, {'end': 5278.413, 'text': "i'll close it.", 'start': 5276.832, 'duration': 1.581}, {'end': 5283.174, 'text': 'you can see the factorial of 5 is 120.', 'start': 5278.413, 'duration': 4.761}, {'end': 5293.702, 'text': "similarly, if you want to find the factorial of, let's say, the number 7, it returns me 1040.", 'start': 5283.174, 'duration': 10.528}, {'end': 5299.965, 'text': 'moving on, now you can use the max and min function to find the maximum and minimum values in a range of cells.', 'start': 5293.702, 'duration': 6.263}, {'end': 5303.126, 'text': "so i'll use the max function.", 'start': 5299.965, 'duration': 3.161}, {'end': 5306.628, 'text': "let's say i want to find the maximum from price per unit.", 'start': 5303.126, 'duration': 3.502}, {'end': 5310.15, 'text': 'you can see, it has returned me the macro, that is 40..', 'start': 5306.628, 'duration': 3.522}, {'end': 5321.314, 'text': "similarly you can use the min function i'll use the same column price per unit Now you can see the minimum value from price per unit column is 30.", 'start': 5310.15, 'duration': 11.164}, {'end': 5327.716, 'text': 'Excel also has a function called randBetween that returns a random number between the numbers you specify.', 'start': 5321.314, 'duration': 6.402}, {'end': 5331.737, 'text': 'So if you want to generate random numbers, you can use 5 square.', 'start': 5328.056, 'duration': 3.681}, {'end': 5333.417, 'text': 'The result should be 25.', 'start': 5332.357, 'duration': 1.06}, {'end': 5334.578, 'text': 'You can see it here.', 'start': 5333.417, 'duration': 1.161}, {'end': 5340.999, 'text': 'Now I want to find the power of 4 raised to 4.', 'start': 5335.838, 'duration': 5.161}, {'end': 5343.462, 'text': 'The result is 256.', 'start': 5340.999, 'duration': 2.463}, {'end': 5349.447, 'text': 'Moving ahead, the minus function or the mod function returns the remainder when a number is divided by a divisor.', 'start': 5343.462, 'duration': 5.985}, {'end': 5350.808, 'text': "So let's see how to use it.", 'start': 5349.647, 'duration': 1.161}, {'end': 5356.113, 'text': "I'll use the mod function and give my number 10.", 'start': 5350.828, 'duration': 5.285}, {'end': 5357.514, 'text': "Let's divide 10 by 3.", 'start': 5356.113, 'duration': 1.401}, {'end': 5359.395, 'text': 'The result is 1.', 'start': 5357.514, 'duration': 1.881}, {'end': 5361.477, 'text': 'Now we have a few other numbers.', 'start': 5359.395, 'duration': 2.082}, {'end': 5362.858, 'text': "Let's try them also.", 'start': 5361.557, 'duration': 1.301}, {'end': 5365.42, 'text': 'Use mod 12.', 'start': 5363.879, 'duration': 1.541}, {'end': 5369.85, 'text': "Let's say I want to divide 12 by 4.", 'start': 5365.42, 'duration': 4.43}, {'end': 5371.551, 'text': 'If the result is 0, 9.', 'start': 5369.85, 'duration': 1.701}, {'end': 5372.992, 'text': "Similarly, let's see one more.", 'start': 5371.551, 'duration': 1.441}, {'end': 5375.373, 'text': "I'll use the count function.", 'start': 5373.992, 'duration': 1.381}, {'end': 5381.736, 'text': "Now let's see, I'll select from G1 to G4, close the bracket, hit enter.", 'start': 5375.653, 'duration': 6.083}, {'end': 5387.74, 'text': 'It returns me 3 because total sales, the first cell value is not a number.', 'start': 5381.936, 'duration': 5.804}, {'end': 5389.04, 'text': "It's a character value.", 'start': 5388.08, 'duration': 0.96}, {'end': 5394.063, 'text': "Hence, it's skipped the count of total sales and it counted the remaining cells.", 'start': 5389.08, 'duration': 4.983}, {'end': 5397.584, 'text': 'now. next, moving on to countA function.', 'start': 5394.423, 'duration': 3.161}, {'end': 5401.806, 'text': 'the countA function counts the number of cells in a range that are not empty.', 'start': 5397.584, 'duration': 4.222}, {'end': 5411.106, 'text': "so, for example, if I use the countA function now, let's say I'll select from L1 to L4, close the bracket,", 'start': 5401.806, 'duration': 9.3}, {'end': 5414.087, 'text': 'it has returned me 3 because we had an empty cell.', 'start': 5411.106, 'duration': 2.981}, {'end': 5418.569, 'text': 'so it skipped that count and it counted the remaining cells that were non-empty.', 'start': 5414.087, 'duration': 4.482}, {'end': 5420.79, 'text': "similarly, let's use the function once more.", 'start': 5418.569, 'duration': 2.221}, {'end': 5422.671, 'text': 'type count a.', 'start': 5420.79, 'duration': 1.881}, {'end': 5429.294, 'text': "now let's say i'll select from l2 to n4, close the bracket.", 'start': 5422.671, 'duration': 6.623}, {'end': 5435.616, 'text': 'hit enter, it gives me 5 because we have only 5 values that are cells are empty.', 'start': 5429.294, 'duration': 6.322}, {'end': 5437.157, 'text': 'so it skipped that count.', 'start': 5435.616, 'duration': 1.541}, {'end': 5440.438, 'text': 'moving ahead, we have the count blank function.', 'start': 5437.777, 'duration': 2.661}, {'end': 5446.22, 'text': 'now, as the function suggests, it counts the number of blank or empty cells in a specified range of.', 'start': 5440.438, 'duration': 5.782}, {'end': 5455.682, 'text': "so if i use count blank and let's say i'll select this range from l1 to l4, close the bracket,", 'start': 5446.22, 'duration': 9.462}, {'end': 5463.785, 'text': 'you can see it has returned one because we had one empty cell in this range of cells.', 'start': 5455.682, 'duration': 8.103}, {'end': 5466.986, 'text': "now let's do one more count blank.", 'start': 5463.785, 'duration': 3.201}, {'end': 5473.93, 'text': "I'll select from L2 to N4, close the bracket.", 'start': 5468.146, 'duration': 5.784}], 'summary': 'Demonstration of excel functions including sum, max, min, randbetween, factorial, power, mod, count, counta, and countblank with quantifiable examples provided.', 'duration': 251.428, 'max_score': 5222.502, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI5222502.jpg'}, {'end': 5321.314, 'src': 'embed', 'start': 5293.702, 'weight': 2, 'content': [{'end': 5299.965, 'text': 'moving on, now you can use the max and min function to find the maximum and minimum values in a range of cells.', 'start': 5293.702, 'duration': 6.263}, {'end': 5303.126, 'text': "so i'll use the max function.", 'start': 5299.965, 'duration': 3.161}, {'end': 5306.628, 'text': "let's say i want to find the maximum from price per unit.", 'start': 5303.126, 'duration': 3.502}, {'end': 5310.15, 'text': 'you can see, it has returned me the macro, that is 40..', 'start': 5306.628, 'duration': 3.522}, {'end': 5321.314, 'text': "similarly you can use the min function i'll use the same column price per unit Now you can see the minimum value from price per unit column is 30.", 'start': 5310.15, 'duration': 11.164}], 'summary': 'Using max and min functions to find max/min values: max price per unit is 40, min price per unit is 30.', 'duration': 27.612, 'max_score': 5293.702, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI5293702.jpg'}, {'end': 5422.671, 'src': 'embed', 'start': 5394.423, 'weight': 4, 'content': [{'end': 5397.584, 'text': 'now. next, moving on to countA function.', 'start': 5394.423, 'duration': 3.161}, {'end': 5401.806, 'text': 'the countA function counts the number of cells in a range that are not empty.', 'start': 5397.584, 'duration': 4.222}, {'end': 5411.106, 'text': "so, for example, if I use the countA function now, let's say I'll select from L1 to L4, close the bracket,", 'start': 5401.806, 'duration': 9.3}, {'end': 5414.087, 'text': 'it has returned me 3 because we had an empty cell.', 'start': 5411.106, 'duration': 2.981}, {'end': 5418.569, 'text': 'so it skipped that count and it counted the remaining cells that were non-empty.', 'start': 5414.087, 'duration': 4.482}, {'end': 5420.79, 'text': "similarly, let's use the function once more.", 'start': 5418.569, 'duration': 2.221}, {'end': 5422.671, 'text': 'type count a.', 'start': 5420.79, 'duration': 1.881}], 'summary': 'The counta function counts non-empty cells in a range, skipping empty cells. for example, selecting l1 to l4 returns 3 because it skips the empty cell and counts the remaining non-empty cells.', 'duration': 28.248, 'max_score': 5394.423, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI5394423.jpg'}], 'start': 4335.456, 'title': 'Excel formulas and functions overview', 'summary': 'Explains excel formulas and functions, including sum, average, and sumproduct functions to calculate total sales and average sales with results as 1165 and 45, and also covers iseven, isodd, islogical, and isnumber functions, and various other functions like round, int, truncate, ceiling, floor, subtotal, fact, max, min, randbetween, power, mod, count, counta, countblank, and, and or with examples and quantifiable data.', 'chapters': [{'end': 4470.496, 'start': 4335.456, 'title': 'Excel formulas and functions', 'summary': 'Explains the concept of formulas and functions in excel, highlighting the ability to perform basic calculations such as addition and division, and the distinction between formulas and functions, emphasizing the use of functions for complex calculations.', 'duration': 135.04, 'highlights': ['Formulas in Excel allow performing basic calculations such as addition, subtraction, multiplication, and division, as well as finding percentages and averages for a range of cells. This emphasizes the diverse functions of formulas in Excel, including basic calculations and statistical operations.', 'A formula in Excel is an expression that operates on values in a range of cells, always returning a result even when the result is an error. This highlights the fundamental definition of a formula in Excel, ensuring a result for all operations.', 'Functions in Excel are used to perform complex calculations that cannot be done manually, with names reflecting their intended use. This emphasizes the purpose of functions in Excel for complex calculations and the significance of their specific names.']}, {'end': 4684.911, 'start': 4471.237, 'title': 'Excel formulas and functions', 'summary': 'Explains how to use sum, average, and sumproduct functions in excel to calculate total sales and average sales, providing examples and results as 1165 and 45, and also delves into logical functions.', 'duration': 213.674, 'highlights': ['The chapter explains how to use SUM, AVERAGE, and SUMPRODUCT functions in Excel to calculate total sales and average sales, providing examples and results as 1165 and 45. Explanation of using SUM, AVERAGE, and SUMPRODUCT functions; Example results of 1165 and 45.', 'Excel provides a function called SUM that allows you to select a range of cells and return the result of 20 plus 30 plus 40 plus 90, which is 180. Explanation of using the SUM function; Example result of 180.', 'You can use the average function to find the average of a range of cells, returning the average of the selected cells, which is 45. Explanation of using the AVERAGE function; Example result of 45.', 'The chapter also delves into logical functions in Excel. Introduction to logical functions in Excel.']}, {'end': 4894.18, 'start': 4684.911, 'title': 'Excel functions: iseven, isodd, islogical, and isnumber', 'summary': 'Demonstrates the usage of iseven, isodd, islogical, and isnumber functions in excel, showcasing their ability to determine if a given number is even or odd, if a value is logical, and if a value is a number, with examples of their output such as 4 returning true for iseven and false for isodd, and 30 returning false for islogical and true for isnumber.', 'duration': 209.269, 'highlights': ["The isEven function returns true if the given number is an even value and false if it's not even, such as 4 returning true and 13 returning false. The isEven function is demonstrated with examples, showing that 4 returns true and 13 returns false, thereby showcasing its utility in determining if a given number is even.", "The isOdd function returns true if the given number is odd and false if it's not odd, such as 4 returning false and 13 returning true. The isOdd function is utilized to determine if a number is odd, with examples showing that 4 returns false and 13 returns true, thereby exemplifying its ability to identify odd numbers.", "The isLogical function returns true if the given value is a logical value and false if it's not, such as 30 returning false and the logical values true and false returning true. The isLogical function is illustrated by examples, demonstrating its capability to identify logical values, with 30 returning false and true and false returning true, showcasing its functionality in recognizing logical values.", "The isNumber function returns true if the given value is a number, such as 40.5 and 30 both returning true, while a text value like 'cnn' returns false. The isNumber function is used to determine if a value is a number, with examples showcasing that 40.5 and 30 both return true, while 'cnn' returns false, thus demonstrating its effectiveness in identifying numerical values."]}, {'end': 5619.41, 'start': 4894.913, 'title': 'Excel functions overview', 'summary': 'Covers various excel functions including round, int, truncate, ceiling, floor, subtotal, fact, max, min, randbetween, power, mod, count, counta, countblank, and, and or functions with examples and quantifiable data, demonstrating their usage and results.', 'duration': 724.497, 'highlights': ['The round function rounds a number to a specified number of digits, for example rounding up 35.316 to two decimal places gives 35.32, demonstrating its usage and result.', 'The subtotal function returns the subtotal in a list or database, with examples of finding average, count, sum, and maximum value, providing quantifiable data such as average of 9 selected cells, 9 filled cells, sum of price per unit as 105, and the maximum value of total sales as 480.', 'The fact function calculates the factorial of a number, such as the factorial of 5 being 120 and the factorial of 7 being 1040, demonstrating the usage and results.', 'The max and min functions find the maximum and minimum values in a range of cells, for instance, the maximum value from the price per unit column being 40 and the minimum value being 30, providing quantifiable data for usage and results.', 'The mod function returns the remainder when a number is divided by a divisor, for example, the remainder of 10 divided by 3 being 1, showcasing its usage and result.', 'The count, countA, and countBlank functions count the number of cells in a range, empty cells, and blank cells respectively, with examples demonstrating their usage and quantifiable data for the results.', 'The AND function checks if all conditions are true and returns true if all conditions are met, while the OR function checks whether any of the arguments are true and returns true if any condition is satisfied, with examples illustrating their usage and results.', 'The randBetween function generates a random number between specified numbers, showcasing its usage and result with the example of generating a random number between 5 and 5 resulting in 25.', 'The power function calculates the result of a number raised to a power, for instance, 4 raised to 4 giving the result of 256, demonstrating its usage and result.']}], 'duration': 1283.954, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI4335456.jpg', 'highlights': ['The chapter explains how to use SUM, AVERAGE, and SUMPRODUCT functions in Excel to calculate total sales and average sales, providing examples and results as 1165 and 45.', "The isEven function returns true if the given number is an even value and false if it's not even, such as 4 returning true and 13 returning false.", 'The max and min functions find the maximum and minimum values in a range of cells, for instance, the maximum value from the price per unit column being 40 and the minimum value being 30, providing quantifiable data for usage and results.', 'The round function rounds a number to a specified number of digits, for example rounding up 35.316 to two decimal places gives 35.32, demonstrating its usage and result.', 'The count, countA, and countBlank functions count the number of cells in a range, empty cells, and blank cells respectively, with examples demonstrating their usage and quantifiable data for the results.']}, {'end': 7097.509, 'segs': [{'end': 5865.856, 'src': 'embed', 'start': 5833.772, 'weight': 7, 'content': [{'end': 5836.254, 'text': 'Moving ahead, next we have the replace function.', 'start': 5833.772, 'duration': 2.482}, {'end': 5841.477, 'text': 'The replace function replaces the part of a text string within a different text string.', 'start': 5836.454, 'duration': 5.023}, {'end': 5847.781, 'text': 'So, suppose I want to replace cell A15 value which is A101.', 'start': 5841.497, 'duration': 6.284}, {'end': 5851.004, 'text': "I'll give my start num as 1.", 'start': 5848.262, 'duration': 2.742}, {'end': 5854.387, 'text': "the number of characters, let's say one and my new text.", 'start': 5851.004, 'duration': 3.383}, {'end': 5857.689, 'text': 'i want to replace the a value with b.', 'start': 5854.387, 'duration': 3.302}, {'end': 5858.45, 'text': 'close the bracket.', 'start': 5857.689, 'duration': 0.761}, {'end': 5865.856, 'text': 'if i hit enter, you can see here a101 has become b101 because we replace the first a value to b.', 'start': 5858.45, 'duration': 7.406}], 'summary': "The 'replace' function replaces text in a string, demonstrated by replacing 'a' with 'b' in 'a101' to become 'b101'.", 'duration': 32.084, 'max_score': 5833.772, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI5833772.jpg'}, {'end': 5967.871, 'src': 'embed', 'start': 5937.007, 'weight': 5, 'content': [{'end': 5943.032, 'text': "for example, if i use the substitute function, i'll give my old text, which has i work at.", 'start': 5937.007, 'duration': 6.025}, {'end': 5945.034, 'text': 'simply learn, give a comma.', 'start': 5943.032, 'duration': 2.002}, {'end': 5951.96, 'text': "now i'll select, let's say i work, which is from my old text and i want to replace it with.", 'start': 5945.034, 'duration': 6.926}, {'end': 5954.042, 'text': 'he works.', 'start': 5951.96, 'duration': 2.082}, {'end': 5954.803, 'text': 'close the bracket.', 'start': 5954.042, 'duration': 0.761}, {'end': 5964.586, 'text': 'You can see the substitute function has replaced the second 2010, from our old string to 2016..', 'start': 5956.052, 'duration': 8.534}, {'end': 5967.871, 'text': "Next, let's use the substitute function once again.", 'start': 5964.586, 'duration': 3.285}], 'summary': "Demonstrates use of substitute function to replace 'i work' with 'he works' and '2010' with '2016'", 'duration': 30.864, 'max_score': 5937.007, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI5937007.jpg'}, {'end': 6028.045, 'src': 'embed', 'start': 5995.417, 'weight': 6, 'content': [{'end': 6001.284, 'text': 'here I have two strings hello and in another cell i have world.', 'start': 5995.417, 'duration': 5.867}, {'end': 6004.931, 'text': 'i want to concatenate these two strings.', 'start': 6001.284, 'duration': 3.647}, {'end': 6008.477, 'text': "so i'll use the concatenate function.", 'start': 6004.931, 'duration': 3.546}, {'end': 6013.294, 'text': "i'll select cell 825, give a comma.", 'start': 6008.477, 'duration': 4.817}, {'end': 6021.34, 'text': "now I'll give two inverted commas with a space, give another comma and select cell B25 that has world.", 'start': 6013.294, 'duration': 8.046}, {'end': 6022.301, 'text': 'close the bracket.', 'start': 6021.34, 'duration': 0.961}, {'end': 6023.001, 'text': 'there you go.', 'start': 6022.301, 'duration': 0.7}, {'end': 6025.863, 'text': 'now it has concatenated both hello and world.', 'start': 6023.001, 'duration': 2.862}, {'end': 6028.045, 'text': "now it's one word hello, world.", 'start': 6025.863, 'duration': 2.182}], 'summary': "Concatenated 'hello' and 'world' using the concatenate function, resulting in 'hello, world'.", 'duration': 32.628, 'max_score': 5995.417, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI5995417.jpg'}, {'end': 6131.217, 'src': 'embed', 'start': 6104.886, 'weight': 8, 'content': [{'end': 6108.567, 'text': 'similarly, you can do it for the next few cells.', 'start': 6104.886, 'duration': 3.681}, {'end': 6112.668, 'text': 'also, if i just copy paste this formula, it will give me the result.', 'start': 6108.567, 'duration': 4.101}, {'end': 6114.649, 'text': 'so you can see it looks like a histogram.', 'start': 6112.668, 'duration': 1.981}, {'end': 6119.49, 'text': "now let's say i want to repeat hashtag and add the rate symbol for 10 times.", 'start': 6114.649, 'duration': 4.841}, {'end': 6122.591, 'text': 'so i can write the repeat function.', 'start': 6119.49, 'duration': 3.101}, {'end': 6129.935, 'text': 'give my text as hashtag and add the rate and the number of times i want is 10.', 'start': 6122.591, 'duration': 7.344}, {'end': 6131.217, 'text': 'close the bracket.', 'start': 6129.935, 'duration': 1.282}], 'summary': 'Demonstrates using formulas to create histograms and repeat text 10 times.', 'duration': 26.331, 'max_score': 6104.886, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI6104886.jpg'}, {'end': 6184.514, 'src': 'embed', 'start': 6156.932, 'weight': 9, 'content': [{'end': 6161.314, 'text': "If it's 4, it means the data type is a logical value that is either true or false.", 'start': 6156.932, 'duration': 4.382}, {'end': 6165.357, 'text': "Now if it's an error, it will return the value 16.", 'start': 6162.054, 'duration': 3.303}, {'end': 6168.039, 'text': "And if it's an array, it will return 64.", 'start': 6165.357, 'duration': 2.682}, {'end': 6169.361, 'text': "Now let's try these examples.", 'start': 6168.039, 'duration': 1.322}, {'end': 6174.105, 'text': 'Here we have Excel, which is a text value.', 'start': 6170.922, 'duration': 3.183}, {'end': 6183.833, 'text': 'So if I use my type function and select Excel, hit enter, it returns me true because Excel is a text value.', 'start': 6175.046, 'duration': 8.787}, {'end': 6184.514, 'text': 'You can see it here.', 'start': 6183.853, 'duration': 0.661}], 'summary': 'Using the type function in excel returns specific values for different data types, such as 4 for logical values, 16 for errors, and 64 for arrays.', 'duration': 27.582, 'max_score': 6156.932, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI6156932.jpg'}, {'end': 6316.546, 'src': 'embed', 'start': 6258.175, 'weight': 10, 'content': [{'end': 6262.297, 'text': "If it's an array, it will return the data type as 64, which is an array.", 'start': 6258.175, 'duration': 4.122}, {'end': 6268.261, 'text': "Now, there's a function called isBlank, which checks if a cell is empty or blank.", 'start': 6262.717, 'duration': 5.544}, {'end': 6272.163, 'text': 'If it is empty, it will return true, else it will return false.', 'start': 6269.041, 'duration': 3.122}, {'end': 6278.006, 'text': 'Now, if you were to check if this cell is empty or not.', 'start': 6273.482, 'duration': 4.524}, {'end': 6281.008, 'text': 'if I hit enter, it returns me.', 'start': 6278.006, 'duration': 3.002}, {'end': 6286.972, 'text': 'true, because you can see it here cell A47 is empty and there is no value in it.', 'start': 6281.008, 'duration': 5.964}, {'end': 6297.481, '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': 6288.153, 'duration': 9.328}, {'end': 6302.76, 'text': "now we'll see how to use trim function.", 'start': 6299.139, 'duration': 3.621}, {'end': 6308.202, 'text': "so the trim function removes all the unnecessary spaces from a string, but it won't remove the sync,", 'start': 6302.76, 'duration': 5.442}, {'end': 6313.404, 'text': 'all the unnecessary spaces except the space between angela and stephens.', 'start': 6308.202, 'duration': 5.202}, {'end': 6316.546, 'text': "i'll copy paste this value to the remaining cells.", 'start': 6313.404, 'duration': 3.142}], 'summary': 'The function isblank checks if a cell is empty or blank, returning true or false accordingly. the trim function removes unnecessary spaces from a string.', 'duration': 58.371, 'max_score': 6258.175, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI6258175.jpg'}, {'end': 6369.334, 'src': 'embed', 'start': 6340.386, 'weight': 12, 'content': [{'end': 6342.528, 'text': 'next we have the find function.', 'start': 6340.386, 'duration': 2.142}, {'end': 6346.873, 'text': 'so the find function returns the starting position of one string within another string.', 'start': 6342.528, 'duration': 4.345}, {'end': 6351.437, 'text': 'now you should notice that find function is case sensitive.', 'start': 6346.873, 'duration': 4.564}, {'end': 6355.141, 'text': "so let's use the find function here.", 'start': 6351.437, 'duration': 3.704}, {'end': 6356.862, 'text': "i'll write find.", 'start': 6355.141, 'duration': 1.721}, {'end': 6364.789, 'text': 'i want to find where capital s is located in the cell a52 that has angela stephens.', 'start': 6357.643, 'duration': 7.146}, {'end': 6365.59, 'text': 'you give a comma.', 'start': 6364.789, 'duration': 0.801}, {'end': 6367.011, 'text': 'select a52.', 'start': 6365.59, 'duration': 1.421}, {'end': 6369.334, 'text': 'hit enter, it returns me the starting position.', 'start': 6367.011, 'duration': 2.323}], 'summary': "The find function returns the starting position of 's' in 'angela stephens' within cell a52.", 'duration': 28.948, 'max_score': 6340.386, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI6340386.jpg'}, {'end': 6717.997, 'src': 'heatmap', 'start': 6453.942, 'weight': 13, 'content': [{'end': 6462.825, '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': 6453.942, 'duration': 8.883}, {'end': 6466.266, 'text': 'Finally, we have a clean function.', 'start': 6463.565, 'duration': 2.701}, {'end': 6470.668, 'text': 'So the clean function returns all non-printable characters from the text.', 'start': 6466.286, 'duration': 4.382}, {'end': 6474.409, 'text': 'Now you can see here, this cell has three lines of text.', 'start': 6471.308, 'duration': 3.101}, {'end': 6479.711, 'text': 'If you want to print this cell in one line, I can use the clean function.', 'start': 6475.149, 'duration': 4.562}, {'end': 6482.711, 'text': 'It will remove all the non-printable characters.', 'start': 6480.649, 'duration': 2.062}, {'end': 6486.676, 'text': "I'll select cell A67, close the bracket, hit enter.", 'start': 6483.032, 'duration': 3.644}, {'end': 6487.737, 'text': 'There you go.', 'start': 6487.297, 'duration': 0.44}, {'end': 6490.741, 'text': 'It has printed the address in one line.', 'start': 6488.298, 'duration': 2.443}, {'end': 6495.987, 'text': 'Now, we have one more text which says, I hope you are doing well during the pandemic.', 'start': 6491.401, 'duration': 4.586}, {'end': 6500.452, 'text': 'Now, if I want to clean that cell, close the bracket.', 'start': 6497.028, 'duration': 3.424}, {'end': 6503.89, 'text': 'It has printed the same sentence in one line.', 'start': 6500.95, 'duration': 2.94}, {'end': 6506.171, 'text': 'Now, there are a few more functions.', 'start': 6504.391, 'duration': 1.78}, {'end': 6507.491, 'text': "Let's see them one by one.", 'start': 6506.411, 'duration': 1.08}, {'end': 6508.991, 'text': 'We have a function called upper.', 'start': 6507.651, 'duration': 1.34}, {'end': 6512.512, 'text': 'So the upper function converts any text string into upper case.', 'start': 6509.311, 'duration': 3.201}, {'end': 6515.572, 'text': 'So we have a string called India.', 'start': 6513.252, 'duration': 2.32}, {'end': 6519.793, 'text': "Now, if I want to convert India into upper case, I'll use the upper function.", 'start': 6515.913, 'duration': 3.88}, {'end': 6520.953, 'text': 'Select that cell.', 'start': 6520.113, 'duration': 0.84}, {'end': 6522.074, 'text': 'You can see it here.', 'start': 6521.293, 'duration': 0.781}, {'end': 6524.834, 'text': 'It has converted India into upper case characters.', 'start': 6522.254, 'duration': 2.58}, {'end': 6530.235, 'text': 'Similarly, you can use the lower function to convert any text string into lower case.', 'start': 6525.854, 'duration': 4.381}, {'end': 6532.532, 'text': "I'll use the lower function.", 'start': 6531.311, 'duration': 1.221}, {'end': 6534.593, 'text': 'Select that string.', 'start': 6533.753, 'duration': 0.84}, {'end': 6536.775, 'text': 'Enter There you go.', 'start': 6535.094, 'duration': 1.681}, {'end': 6541.438, 'text': 'So, all the letters in the word India has been converted into lower case.', 'start': 6537.615, 'duration': 3.823}, {'end': 6546.842, 'text': "Now, there's another function called proper which is used to convert any string into proper case.", 'start': 6541.978, 'duration': 4.864}, {'end': 6550.484, 'text': 'So, I can write a VLOOKUP function something like this.', 'start': 6548.103, 'duration': 2.381}, {'end': 6553.366, 'text': "I'll give equal to search for VLOOKUP.", 'start': 6550.504, 'duration': 2.862}, {'end': 6561.534, 'text': "Now, my lookup value will be Stuart comma Then I'll select my table array.", 'start': 6554.407, 'duration': 7.127}, {'end': 6567.317, 'text': "So I'll take my table array from A2 to C7.", 'start': 6562.474, 'duration': 4.843}, {'end': 6570.318, 'text': 'If you want, you can choose the entire table also.', 'start': 6568.357, 'duration': 1.961}, {'end': 6571.919, 'text': 'Give a comma.', 'start': 6570.338, 'duration': 1.581}, {'end': 6577.262, 'text': 'My column index number is this department column, which is the third column.', 'start': 6572.58, 'duration': 4.682}, {'end': 6580.824, 'text': 'And range lookup is either true or false.', 'start': 6578.523, 'duration': 2.301}, {'end': 6584.566, 'text': "I'll select false, which is zero for exact match.", 'start': 6581.164, 'duration': 3.402}, {'end': 6589.048, 'text': 'If I hit enter, you can see it has returned marketing.', 'start': 6585.666, 'duration': 3.382}, {'end': 6594.379, 'text': 'And in the table also, if you see, Stuart is from the marketing department.', 'start': 6589.955, 'duration': 4.424}, {'end': 6597.501, 'text': 'This is a simple example how you can use Blookup.', 'start': 6594.839, 'duration': 2.662}, {'end': 6602.185, 'text': 'Now, if you want, you can choose the entire table.', 'start': 6598.722, 'duration': 3.463}, {'end': 6606.908, 'text': 'So, I can select from A2 to E7.', 'start': 6602.205, 'duration': 4.703}, {'end': 6607.949, 'text': 'Hit enter.', 'start': 6606.928, 'duration': 1.021}, {'end': 6609.15, 'text': 'It will give the same result.', 'start': 6607.969, 'duration': 1.181}, {'end': 6612.613, 'text': 'Now, suppose I want to find from the marketing department.', 'start': 6609.591, 'duration': 3.022}, {'end': 6617.222, 'text': 'so this is the person who is from the marketing department.', 'start': 6613.82, 'duration': 3.402}, {'end': 6618.603, 'text': 'i want to find the city.', 'start': 6617.222, 'duration': 1.381}, {'end': 6620.584, 'text': "i'll use the vlookup function again.", 'start': 6618.603, 'duration': 1.981}, {'end': 6622.425, 'text': 'vlookup. now.', 'start': 6620.584, 'duration': 1.841}, {'end': 6625.627, 'text': 'my lookup value is marketing element.', 'start': 6622.425, 'duration': 3.202}, {'end': 6629.87, 'text': 'column is the leftmost lookup column.', 'start': 6625.627, 'duration': 4.243}, {'end': 6631.411, 'text': 'select the remaining table.', 'start': 6629.87, 'duration': 1.541}, {'end': 6633.152, 'text': 'give a comma.', 'start': 6631.411, 'duration': 1.741}, {'end': 6641.198, 'text': "now my column index number is 2, which is the city column, And I'll give my range lookup as zero because I want the exact match.", 'start': 6633.152, 'duration': 8.046}, {'end': 6642.078, 'text': "I'll hit enter.", 'start': 6641.498, 'duration': 0.58}, {'end': 6648.721, 'text': 'You can see it has returned me the city to which this person from the marketing department belongs to, that is Kansas.', 'start': 6642.118, 'duration': 6.603}, {'end': 6650.682, 'text': "Let's see another example.", 'start': 6649.221, 'duration': 1.461}, {'end': 6657.596, 'text': 'Suppose I want to find the city to which the person with the last name Davis belongs to.', 'start': 6651.872, 'duration': 5.724}, {'end': 6660.218, 'text': "So I'll write my VLOOKUP function.", 'start': 6658.036, 'duration': 2.182}, {'end': 6663.3, 'text': "I'll take my lookup value as Davis.", 'start': 6660.618, 'duration': 2.682}, {'end': 6664.441, 'text': "I'll give a comma.", 'start': 6663.7, 'duration': 0.741}, {'end': 6668.784, 'text': 'Now my table array will start from the last name column.', 'start': 6665.222, 'duration': 3.562}, {'end': 6672.787, 'text': "And let's select the remaining columns as well.", 'start': 6670.065, 'duration': 2.722}, {'end': 6680.136, 'text': 'Now my column index number will be 3 as per our selection.', 'start': 6674.352, 'duration': 5.784}, {'end': 6686.14, 'text': "Another example, let's say we want to find the first name of the person who is from Los Angeles city.", 'start': 6680.156, 'duration': 5.984}, {'end': 6690.423, 'text': 'So we have our lookup value as Los Angeles and now we want to find the first name.', 'start': 6686.3, 'duration': 4.123}, {'end': 6692.945, 'text': "So I'll again use the lookup function.", 'start': 6690.683, 'duration': 2.262}, {'end': 6695.387, 'text': "I'll give my lookup value as Los Angeles.", 'start': 6693.545, 'duration': 1.842}, {'end': 6701.058, 'text': 'comma. now, my lookup vector is the city column comma.', 'start': 6696.175, 'duration': 4.883}, {'end': 6704.18, 'text': 'my resultant vector is the first name column.', 'start': 6701.058, 'duration': 3.122}, {'end': 6705.301, 'text': 'close the bracket.', 'start': 6704.18, 'duration': 1.121}, {'end': 6710.825, 'text': 'hit enter, you can see it has returned me stuart, which is actually wrong.', 'start': 6705.301, 'duration': 5.524}, {'end': 6717.997, 'text': "If you see our original table, the person who is from Los Angeles City is not Stuart, but it's Lucy.", 'start': 6711.834, 'duration': 6.163}], 'summary': 'The transcript covers text cleaning and vlookup functions in excel, including examples and results.', 'duration': 25.769, 'max_score': 6453.942, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI6453942.jpg'}, {'end': 6553.366, 'src': 'embed', 'start': 6525.854, 'weight': 1, 'content': [{'end': 6530.235, 'text': 'Similarly, you can use the lower function to convert any text string into lower case.', 'start': 6525.854, 'duration': 4.381}, {'end': 6532.532, 'text': "I'll use the lower function.", 'start': 6531.311, 'duration': 1.221}, {'end': 6534.593, 'text': 'Select that string.', 'start': 6533.753, 'duration': 0.84}, {'end': 6536.775, 'text': 'Enter There you go.', 'start': 6535.094, 'duration': 1.681}, {'end': 6541.438, 'text': 'So, all the letters in the word India has been converted into lower case.', 'start': 6537.615, 'duration': 3.823}, {'end': 6546.842, 'text': "Now, there's another function called proper which is used to convert any string into proper case.", 'start': 6541.978, 'duration': 4.864}, {'end': 6550.484, 'text': 'So, I can write a VLOOKUP function something like this.', 'start': 6548.103, 'duration': 2.381}, {'end': 6553.366, 'text': "I'll give equal to search for VLOOKUP.", 'start': 6550.504, 'duration': 2.862}], 'summary': 'Demonstrates usage of lower and proper functions in excel.', 'duration': 27.512, 'max_score': 6525.854, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI6525854.jpg'}, {'end': 6804.185, 'src': 'embed', 'start': 6773.233, 'weight': 0, 'content': [{'end': 6775.253, 'text': "So I'll use the HLOOKUP function again.", 'start': 6773.233, 'duration': 2.02}, {'end': 6777.954, 'text': 'Select my lookup value as Davis.', 'start': 6775.273, 'duration': 2.681}, {'end': 6779.034, 'text': "I'll give a comma.", 'start': 6777.974, 'duration': 1.06}, {'end': 6785.636, 'text': "Now my table array, I'll select from G2 to M5.", 'start': 6779.354, 'duration': 6.282}, {'end': 6791.017, 'text': 'Now in this selection, my row index number will be 2, which is the department column.', 'start': 6786.256, 'duration': 4.761}, {'end': 6792.782, 'text': 'give a zero.', 'start': 6792.102, 'duration': 0.68}, {'end': 6793.622, 'text': 'close the bracket.', 'start': 6792.782, 'duration': 0.84}, {'end': 6795.223, 'text': 'hit enter.', 'start': 6793.622, 'duration': 1.601}, {'end': 6796.503, 'text': 'it has returned sales.', 'start': 6795.223, 'duration': 1.28}, {'end': 6800.464, 'text': 'so Davis is from sales department, moving ahead.', 'start': 6796.503, 'duration': 3.961}, {'end': 6804.185, 'text': "now let's explore the if function.", 'start': 6800.464, 'duration': 3.721}], 'summary': 'Using hlookup, found davis in sales department.', 'duration': 30.952, 'max_score': 6773.233, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI6773233.jpg'}, {'end': 7050.958, 'src': 'embed', 'start': 7018.809, 'weight': 2, 'content': [{'end': 7020.691, 'text': 'So it returned value not found.', 'start': 7018.809, 'duration': 1.882}, {'end': 7025.897, 'text': 'Similarly, there is one more function called ifReturned, value not found.', 'start': 7021.613, 'duration': 4.284}, {'end': 7034.106, 'text': 'Similarly, there is one more function called ifError, So, the IFERROR function returns the error value if the expression is an error.', 'start': 7026.778, 'duration': 7.328}, {'end': 7037.568, 'text': 'Otherwise, it returns the value of the expression if there is no error.', 'start': 7034.766, 'duration': 2.802}, {'end': 7042.512, 'text': 'So, these two cells you can see we have some values 10 and 0.', 'start': 7037.848, 'duration': 4.664}, {'end': 7050.958, 'text': "Let's say I want to use the IFERROR function and the value I will give is, say I want to divide U3 by U4.", 'start': 7042.512, 'duration': 8.446}], 'summary': 'Demonstrates the use of iferror function to handle errors in excel, with an example of dividing cells u3 by u4.', 'duration': 32.149, 'max_score': 7018.809, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI7018809.jpg'}], 'start': 5619.41, 'title': 'Using excel functions', 'summary': 'Covers excel character, text, type, isblank, trim, find, clean, vlookup, hlookup, if, and, ifna, and iferror functions with examples and demonstrations. it includes using istext, length, text, replace, substitute, concatenate, rept, and the type function returning data type values such as 1 for number, 2 for text, 16 for error, 64 for array, and isblank function checking cell emptiness.', 'chapters': [{'end': 5833.232, 'start': 5619.41, 'title': 'Excel character functions', 'summary': 'Covers using excel character functions including istext, length, and text to check text values, count characters, and convert date values into text, with examples demonstrating their usage and outputs.', 'duration': 213.822, 'highlights': ['The isText function checks if the given value is a text value and returns true or false. The isText function is demonstrated to check if a value is a text value and returns true or false, with examples of its outputs.', 'The length function returns the number of characters in a string, including spaces and special characters. The length function, demonstrated with examples, returns the number of characters in a string, including spaces and special characters.', 'The text function converts a value to text in a specified number format, demonstrated with examples converting date values into text. The text function is showcased to convert date values into text in specified number formats, with examples illustrating the conversion outputs.']}, {'end': 6131.217, 'start': 5833.772, 'title': 'Excel text functions', 'summary': "Covers excel's text functions including replace, substitute, concatenate, and rept, demonstrating their usage and impact with examples, such as replacing text strings, concatenating text, and repeating text a given number of times.", 'duration': 297.445, 'highlights': ["The SUBSTITUTE function replaces existing text with new text in a text string, as shown by replacing 'i work' with 'he works' in a cell, resulting in the replacement of '2010' with '2016' at two places in the string. Demonstration of using SUBSTITUTE function to replace specific text and the impact of the replacement, with an example of replacing 'i work' with 'he works' and '2010' with '2016' at two places.", "The CONCATENATE function joins or merges several text strings into one text string, illustrated by combining 'hello' and 'world' into 'hello, world' and 'Excel is' and 'fun to learn' into 'Excel is fun to learn'. Illustration of using CONCATENATE function to merge text strings with examples of combining 'hello' and 'world' and 'Excel is' and 'fun to learn' into single strings.", "The REPLACE function replaces a part of a text string within a different text string, demonstrated by replacing specific characters in a text string, such as 'a' with 'b' and 'AD' with 'SA', showcasing the impact of the replacement. Demonstration of using REPLACE function to replace specific characters in a text string and the impact of the replacement, with examples of replacing 'a' with 'b' and 'AD' with 'SA'.", "The REPT function repeats text a given number of times, as shown by repeating a special character multiple times to create a histogram and repeating 'hashtag' and 'at the rate' symbol 10 times. Illustration of using REPT function to repeat text a given number of times with examples of repeating a special character to create a histogram and repeating 'hashtag' and 'at the rate' symbol 10 times."]}, {'end': 6506.171, 'start': 6131.217, 'title': 'Excel type, isblank, trim, find, clean functions', 'summary': 'Covers the use of excel functions including type, isblank, trim, find, and clean, with key points such as type function returning data type values like 1 for number, 2 for text, 16 for error, 64 for array, and isblank function checking cell emptiness.', 'duration': 374.954, 'highlights': ['The type function returns data type values like 1 for number, 2 for text, 16 for error, 64 for array. The type function returns specific data type values, such as 1 for number, 2 for text, 16 for error, and 64 for array.', 'The isBlank function checks if a cell is empty or blank, returning true if empty and false if not. The isBlank function checks if a cell is empty or blank, returning true if empty and false if not.', 'The trim function removes unnecessary spaces from a string, except the space between words. The trim function removes unnecessary spaces from a string, except the space between words, as demonstrated by removing unnecessary spaces from the original string.', 'The find function returns the starting position of one string within another string, and it is case sensitive. The find function is case sensitive and returns the starting position of one string within another string.', 'The clean function returns all non-printable characters from the text, allowing for cleaning up cell contents. The clean function removes all non-printable characters from the text, allowing for cleaning up cell contents and printing in one line.']}, {'end': 7097.509, 'start': 6506.411, 'title': 'Excel functions overview', 'summary': 'Covers the usage of various excel functions including vlookup, hlookup, if, and, ifna, and iferror, demonstrating their applications and error handling, with examples and explanations.', 'duration': 591.098, 'highlights': ['Explained the usage of various Excel functions such as VLOOKUP, HLOOKUP, IF, AND, IFNA, and IFERROR, with examples and explanations, showcasing practical applications and error handling.', 'Demonstrated the application of the VLOOKUP function to convert text strings to upper case, lower case, and proper case, and showcased the use of VLOOKUP to search for specific data within a table.', 'Highlighted the practical use of the IF function to check conditions and return specific values based on the evaluation of logical tests, with examples demonstrating its functionality and error handling.', 'Showcased the application of the IFNA function to handle situations where a specified value is not found, with an example illustrating its usage and the returned result when the value is not found.', 'Explained the functionality of the IFERROR function in handling errors, with examples demonstrating its application to return specific values when an expression results in an error and when it does not.']}], 'duration': 1478.099, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI5619410.jpg', 'highlights': ['Explained the usage of various Excel functions such as VLOOKUP, HLOOKUP, IF, AND, IFNA, and IFERROR, with examples and explanations, showcasing practical applications and error handling.', 'Demonstrated the application of the VLOOKUP function to convert text strings to upper case, lower case, and proper case, and showcased the use of VLOOKUP to search for specific data within a table.', 'Highlighted the practical use of the IF function to check conditions and return specific values based on the evaluation of logical tests, with examples demonstrating its functionality and error handling.', 'Showcased the application of the IFNA function to handle situations where a specified value is not found, with an example illustrating its usage and the returned result when the value is not found.', 'Explained the functionality of the IFERROR function in handling errors, with examples demonstrating its application to return specific values when an expression results in an error and when it does not.', "The SUBSTITUTE function replaces existing text with new text in a text string, as shown by replacing 'i work' with 'he works' in a cell, resulting in the replacement of '2010' with '2016' at two places in the string.", "The CONCATENATE function joins or merges several text strings into one text string, illustrated by combining 'hello' and 'world' into 'hello, world' and 'Excel is' and 'fun to learn' into 'Excel is fun to learn'.", "The REPLACE function replaces a part of a text string within a different text string, demonstrated by replacing specific characters in a text string, such as 'a' with 'b' and 'AD' with 'SA', showcasing the impact of the replacement.", "The REPT function repeats text a given number of times, as shown by repeating a special character multiple times to create a histogram and repeating 'hashtag' and 'at the rate' symbol 10 times.", 'The type function returns data type values like 1 for number, 2 for text, 16 for error, 64 for array.', 'The isBlank function checks if a cell is empty or blank, returning true if empty and false if not.', 'The trim function removes unnecessary spaces from a string, except the space between words.', 'The find function returns the starting position of one string within another string, and it is case sensitive.', 'The clean function returns all non-printable characters from the text, allowing for cleaning up cell contents.']}, {'end': 8774.272, 'segs': [{'end': 7129.385, 'src': 'embed', 'start': 7097.509, 'weight': 0, 'content': [{'end': 7105.034, 'text': "similarly, let's say i want to find the city to which, again, there is no anthony.", 'start': 7097.509, 'duration': 7.525}, {'end': 7106.255, 'text': "let's try this out.", 'start': 7105.034, 'duration': 1.221}, {'end': 7108.335, 'text': "i'll use the index function.", 'start': 7106.255, 'duration': 2.08}, {'end': 7116.898, 'text': 'now the array i will give is from z2 to ac7 comma.', 'start': 7108.335, 'duration': 8.563}, {'end': 7121.44, 'text': 'my row num is 3 and the column number i want is 2.', 'start': 7116.898, 'duration': 4.542}, {'end': 7122.781, 'text': 'close, it returns me.', 'start': 7121.44, 'duration': 1.341}, {'end': 7127.244, 'text': 'davis, which is this one, the reason being we skipped the header column.', 'start': 7122.781, 'duration': 4.463}, {'end': 7129.385, 'text': 'our column was selected from this cell.', 'start': 7127.244, 'duration': 2.141}], 'summary': "Using the index function, the city 'davis' was found at row 3 and column 2 in the specified array.", 'duration': 31.876, 'max_score': 7097.509, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI7097509.jpg'}, {'end': 7189.308, 'src': 'embed', 'start': 7157.921, 'weight': 1, 'content': [{'end': 7162.304, 'text': 'You can see the fifth row is this one and the fourth column is city.', 'start': 7157.921, 'duration': 4.383}, {'end': 7164.506, 'text': 'So it returned as Boston.', 'start': 7162.585, 'duration': 1.921}, {'end': 7170.871, 'text': 'Now the MATCH function returns the relative position of an item in an array that matches a specified value.', 'start': 7164.786, 'duration': 6.085}, {'end': 7181.404, 'text': 'So if you want to find the position at which events is located in the table so you can use the match function, something like this match.', 'start': 7171.231, 'duration': 10.173}, {'end': 7189.308, 'text': "I'll take my lookup value as y15, which has events comma.", 'start': 7181.404, 'duration': 7.904}], 'summary': "The match function returns the position of 'events' in the table.", 'duration': 31.387, 'max_score': 7157.921, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI7157921.jpg'}, {'end': 7710.729, 'src': 'heatmap', 'start': 7458.006, 'weight': 0.7, 'content': [{'end': 7461.749, 'text': 'If I hit enter, it gives me 146.', 'start': 7458.006, 'duration': 3.743}, {'end': 7466.413, 'text': 'So in this column, that is the G column, Afghanistan is present 146 times.', 'start': 7461.749, 'duration': 4.664}, {'end': 7471.402, 'text': "Similarly, let's count the number of times India is present in the data set.", 'start': 7468.095, 'duration': 3.307}, {'end': 7474.247, 'text': "So I'll use the COUNTI function again.", 'start': 7472.263, 'duration': 1.984}, {'end': 7476.572, 'text': "I'll select the same range.", 'start': 7474.267, 'duration': 2.305}, {'end': 7477.734, 'text': "I'll give a comma.", 'start': 7477.033, 'duration': 0.701}, {'end': 7479.898, 'text': 'Now my criteria will be India.', 'start': 7478.155, 'duration': 1.743}, {'end': 7483.333, 'text': 'You can see here India is present 155 times.', 'start': 7480.472, 'duration': 2.861}, {'end': 7487.054, 'text': "Moving on, the next function we'll talk about is COUNTIFS.", 'start': 7483.773, 'duration': 3.281}, {'end': 7492.216, 'text': 'So COUNTIFS function counts the number of cells specified in a given set of conditions or criteria.', 'start': 7487.314, 'duration': 4.902}, {'end': 7497.578, 'text': 'So our question is count the total number of days in which the cases in India have been greater than 100.', 'start': 7492.496, 'duration': 5.082}, {'end': 7501.959, 'text': 'So to do this COUNTIFS function.', 'start': 7497.578, 'duration': 4.381}, {'end': 7507.294, 'text': "I'll select my criteria range.", 'start': 7504.392, 'duration': 2.902}, {'end': 7511.436, 'text': "First, I'll select the D column, comma.", 'start': 7508.534, 'duration': 2.902}, {'end': 7515.678, 'text': 'My first criteria is we want to check for India.', 'start': 7512.796, 'duration': 2.882}, {'end': 7523.562, 'text': 'And then my second condition is that the number of cases has to be greater than 100.', 'start': 7515.698, 'duration': 7.864}, {'end': 7528.225, 'text': 'So my next criteria range will be the cases column, comma.', 'start': 7523.562, 'duration': 4.663}, {'end': 7533.237, 'text': 'And my condition is greater than 100.', 'start': 7529.405, 'duration': 3.832}, {'end': 7537.48, 'text': 'close the bracket it enter, the result is 68.', 'start': 7533.237, 'duration': 4.243}, {'end': 7542.843, 'text': 'so the total of 68 days India had more than 100 cases.', 'start': 7537.48, 'duration': 5.363}, {'end': 7550.869, 'text': 'the next example is we need to count the number of days in which the deaths in United States have been greater than 200.', 'start': 7542.843, 'duration': 8.026}, {'end': 7556.012, 'text': "so for that I'll use the COUNTIFS function.", 'start': 7550.869, 'duration': 5.143}, {'end': 7557.873, 'text': "I'll give my criteria.", 'start': 7556.012, 'duration': 1.861}, {'end': 7563.782, 'text': 'so this time I am taking my first criteria as the cases comma.', 'start': 7557.873, 'duration': 5.909}, {'end': 7568.125, 'text': 'so the cases have to be greater than 200.', 'start': 7563.782, 'duration': 4.343}, {'end': 7570.526, 'text': 'close the bracket, hit enter.', 'start': 7568.125, 'duration': 2.401}, {'end': 7578.532, 'text': 'so you can see the total cases in india till third of june have been more than two lakhs.', 'start': 7570.526, 'duration': 8.006}, {'end': 7584.116, 'text': 'now i want to find the total deaths in the united states till the third of june.', 'start': 7578.532, 'duration': 5.584}, {'end': 7588.239, 'text': "i'll use the same sumif function.", 'start': 7584.116, 'duration': 4.123}, {'end': 7594.908, 'text': 'now my first criteria will be the g column comma.', 'start': 7588.239, 'duration': 6.669}, {'end': 7599.352, 'text': "I'll give my second value as United States of America.", 'start': 7594.908, 'duration': 4.444}, {'end': 7610, 'text': "I'll use the same format using underscore States of America comma.", 'start': 7599.352, 'duration': 10.648}, {'end': 7612.983, 'text': 'now the second range will be the deaths column.', 'start': 7610, 'duration': 2.983}, {'end': 7616.674, 'text': 'close the bracket, hit enter.', 'start': 7614.653, 'duration': 2.021}, {'end': 7622.838, 'text': 'you can see the total deaths in the united states till 3rd of june have crossed 1 lakh.', 'start': 7616.674, 'duration': 6.164}, {'end': 7627.141, 'text': "similarly, let's now see our sumips function.", 'start': 7622.838, 'duration': 4.303}, {'end': 7632.984, 'text': 'so the sumips function adds the cells specified in a given set of conditions or criteria.', 'start': 7627.141, 'duration': 5.843}, {'end': 7634.284, 'text': 'so we have a question here.', 'start': 7632.984, 'duration': 1.3}, {'end': 7641.527, 'text': 'we want to find the total cases in france on those days where the deaths have been less than 100.', 'start': 7634.284, 'duration': 7.243}, {'end': 7646.048, 'text': 'so this should be those days instead of whose?', 'start': 7641.527, 'duration': 4.521}, {'end': 7650.59, 'text': "okay, so let's see our sumips function now.", 'start': 7646.048, 'duration': 4.542}, {'end': 7656.292, 'text': 'first we need to give the sum range, since we want to find the total cases.', 'start': 7650.59, 'duration': 5.702}, {'end': 7661.028, 'text': "so we'll give the sum range as the column e comma.", 'start': 7656.292, 'duration': 4.736}, {'end': 7664.729, 'text': 'now the second criteria is the countries column.', 'start': 7661.028, 'duration': 3.701}, {'end': 7669.711, 'text': "so I'll give the G column and we want to check for France.", 'start': 7664.729, 'duration': 4.982}, {'end': 7671.831, 'text': 'give that criteria comma.', 'start': 7669.711, 'duration': 2.12}, {'end': 7678.774, 'text': 'the second criteria is I need to find only for those days where the deaths have been less than hundred.', 'start': 7671.831, 'duration': 6.943}, {'end': 7681.974, 'text': "so I'll give the deaths column, comma.", 'start': 7678.774, 'duration': 3.2}, {'end': 7685.496, 'text': 'the criteria should be less than hundred.', 'start': 7681.974, 'duration': 3.522}, {'end': 7688.399, 'text': 'close the bracket hit enter is the result.', 'start': 7685.496, 'duration': 2.903}, {'end': 7696.602, 'text': 'So, the total cases in France on those days where the deaths have been less than 100 is 20,638.', 'start': 7689, 'duration': 7.602}, {'end': 7699.464, 'text': "Now, let's talk about the average if function.", 'start': 7696.603, 'duration': 2.861}, {'end': 7705.927, 'text': 'So, the average if function finds the average or the arithmetic mean of the cells specified by a given condition or criteria.', 'start': 7699.664, 'duration': 6.263}, {'end': 7710.729, 'text': 'So, our question is, we want to find the total average daily cases in India.', 'start': 7706.267, 'duration': 4.462}], 'summary': 'The transcript covers excel functions like countif, countifs, sumif, and averageif to analyze covid-19 data, with india having 155 cases and 68 days with over 100 cases, the us having over 200,000 cases, and france having 20,638 cases on days with less than 100 deaths.', 'duration': 252.723, 'max_score': 7458.006, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI7458006.jpg'}, {'end': 7570.526, 'src': 'embed', 'start': 7542.843, 'weight': 3, 'content': [{'end': 7550.869, 'text': 'the next example is we need to count the number of days in which the deaths in United States have been greater than 200.', 'start': 7542.843, 'duration': 8.026}, {'end': 7556.012, 'text': "so for that I'll use the COUNTIFS function.", 'start': 7550.869, 'duration': 5.143}, {'end': 7557.873, 'text': "I'll give my criteria.", 'start': 7556.012, 'duration': 1.861}, {'end': 7563.782, 'text': 'so this time I am taking my first criteria as the cases comma.', 'start': 7557.873, 'duration': 5.909}, {'end': 7568.125, 'text': 'so the cases have to be greater than 200.', 'start': 7563.782, 'duration': 4.343}, {'end': 7570.526, 'text': 'close the bracket, hit enter.', 'start': 7568.125, 'duration': 2.401}], 'summary': 'Count the days with deaths > 200 using countifs function.', 'duration': 27.683, 'max_score': 7542.843, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI7542843.jpg'}, {'end': 7664.729, 'src': 'embed', 'start': 7634.284, 'weight': 4, 'content': [{'end': 7641.527, 'text': 'we want to find the total cases in france on those days where the deaths have been less than 100.', 'start': 7634.284, 'duration': 7.243}, {'end': 7646.048, 'text': 'so this should be those days instead of whose?', 'start': 7641.527, 'duration': 4.521}, {'end': 7650.59, 'text': "okay, so let's see our sumips function now.", 'start': 7646.048, 'duration': 4.542}, {'end': 7656.292, 'text': 'first we need to give the sum range, since we want to find the total cases.', 'start': 7650.59, 'duration': 5.702}, {'end': 7661.028, 'text': "so we'll give the sum range as the column e comma.", 'start': 7656.292, 'duration': 4.736}, {'end': 7664.729, 'text': 'now the second criteria is the countries column.', 'start': 7661.028, 'duration': 3.701}], 'summary': 'Find total cases in france on days with deaths < 100', 'duration': 30.445, 'max_score': 7634.284, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI7634284.jpg'}, {'end': 8282.882, 'src': 'embed', 'start': 8244.485, 'weight': 5, 'content': [{'end': 8252.571, 'text': "So first, let's use our network days function to find the number of working days between two date values.", 'start': 8244.485, 'duration': 8.086}, {'end': 8255.714, 'text': "So I'll write network days.", 'start': 8253.392, 'duration': 2.322}, {'end': 8266.787, 'text': "And let's say I'll give my first date or the starting date as 1st of July 2020.", 'start': 8256.754, 'duration': 10.033}, {'end': 8272.798, 'text': "and i'll give my second date value as 31st of july 2020.", 'start': 8266.787, 'duration': 6.011}, {'end': 8275.964, 'text': 'close the bracket.', 'start': 8272.798, 'duration': 3.166}, {'end': 8276.764, 'text': 'hit, enter.', 'start': 8275.964, 'duration': 0.8}, {'end': 8278.558, 'text': 'it gives me 23.', 'start': 8276.764, 'duration': 1.794}, {'end': 8280.96, 'text': 'now let me show you the calendar now.', 'start': 8278.558, 'duration': 2.402}, {'end': 8282.882, 'text': 'this is the july calendar.', 'start': 8280.96, 'duration': 1.922}], 'summary': 'Using network days function, 23 working days between 1st-31st july 2020.', 'duration': 38.397, 'max_score': 8244.485, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI8244485.jpg'}, {'end': 8467.897, 'src': 'embed', 'start': 8443.068, 'weight': 6, 'content': [{'end': 8450.009, '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': 8443.068, 'duration': 6.941}, {'end': 8454.091, 'text': "so I'll just show you the arguments the PMT function takes.", 'start': 8450.009, 'duration': 4.082}, {'end': 8459.393, 'text': 'you can see it takes rate, NPR or NPER, PV, FP and a type.', 'start': 8454.091, 'duration': 5.302}, {'end': 8461.574, 'text': 'so rate is the interest rate for the loan.', 'start': 8459.393, 'duration': 2.181}, {'end': 8464.215, 'text': 'NPER is the total number of payments for the loan.', 'start': 8461.574, 'duration': 2.641}, {'end': 8467.897, 'text': 'PV is the future value or the total value of all loan payments.', 'start': 8464.215, 'duration': 3.682}], 'summary': 'Pmt function calculates future loan payments using rate, nper, pv, and type arguments.', 'duration': 24.829, 'max_score': 8443.068, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI8443068.jpg'}, {'end': 8677.675, 'src': 'embed', 'start': 8611.937, 'weight': 7, 'content': [{'end': 8618.687, 'text': "I'll use the XIRR function that returns the internal rate for a schedule of cash flows that occur in irregular intervals.", 'start': 8611.937, 'duration': 6.75}, {'end': 8623.488, 'text': "Now for using the XIRR function, I'll use this table.", 'start': 8619.247, 'duration': 4.241}, {'end': 8628.289, 'text': 'Now you can see it has irregular cash flows over a period of time.', 'start': 8623.508, 'duration': 4.781}, {'end': 8631.61, 'text': 'So let me use my XIRR function.', 'start': 8629.509, 'duration': 2.101}, {'end': 8644.083, 'text': "I'll give my values ranging from F22 to F27 comma.", 'start': 8633.57, 'duration': 10.513}, {'end': 8649.11, 'text': "the dates I'll give from E22 to E27.", 'start': 8644.083, 'duration': 5.027}, {'end': 8649.971, 'text': 'close the bracket.', 'start': 8649.11, 'duration': 0.861}, {'end': 8651.213, 'text': 'hit enter.', 'start': 8649.971, 'duration': 1.242}, {'end': 8653.596, 'text': 'this is my XIRR value up.', 'start': 8651.213, 'duration': 2.383}, {'end': 8657.541, 'text': "next we'll see how to find out the future value.", 'start': 8653.596, 'duration': 3.945}, {'end': 8665.411, 'text': 'So for using the future value, use the FV function in Excel that returns the future value of an investment.', 'start': 8658.882, 'duration': 6.529}, {'end': 8673.563, 'text': 'You can use the FV function to get the future value of an investment assuming periodic constant payments with a constant interest rate also.', 'start': 8665.892, 'duration': 7.671}, {'end': 8677.675, 'text': 'so this is my table.', 'start': 8675.293, 'duration': 2.382}], 'summary': 'Using xirr function for irregular cash flows and fv function for future value calculation in excel.', 'duration': 65.738, 'max_score': 8611.937, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI8611937.jpg'}, {'end': 8774.272, 'src': 'embed', 'start': 8743.999, 'weight': 9, 'content': [{'end': 8751.083, 'text': 'my PMT or payment per period is 10,000 comma.', 'start': 8743.999, 'duration': 7.084}, {'end': 8754.804, 'text': 'future value 0 type is also 0.', 'start': 8751.083, 'duration': 3.721}, {'end': 8758.946, 'text': 'so this is my present value up.', 'start': 8754.804, 'duration': 4.142}, {'end': 8763.147, 'text': 'next we have the net present value or the NPV function.', 'start': 8758.946, 'duration': 4.201}, {'end': 8774.272, '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': 8763.147, 'duration': 11.125}], 'summary': "Pmt is $10,000, npv calculates investment's value using discount rate & future payments.", 'duration': 30.273, 'max_score': 8743.999, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI8743999.jpg'}], 'start': 7097.509, 'title': 'Excel functions', 'summary': 'Demonstrates the usage of index and match functions to find specific values, excel functions like countif, countifs, sumifs, and averageif, date and time functions like today, now, time, and various financial functions with detailed explanations and examples, covering a wide range of functions and providing practical insights.', 'chapters': [{'end': 7378.468, 'start': 7097.509, 'title': 'Index and match functions in excel', 'summary': 'Demonstrates the use of index and match functions in excel to find specific values in a table, with examples such as finding cities and departments based on criteria, returning relative positions, and locating matching values.', 'duration': 280.959, 'highlights': ['The INDEX function returns the value at the intersection of a specific row and column in a table, demonstrated by examples where specific cities are found using row and column numbers.', "The MATCH function returns the position of an item in an array that matches a specified value, exemplified by finding the relative position of 'events' and 'Boston' in the table.", "The combination of INDEX and MATCH functions is used to return values based on specific criteria, such as finding the city associated with the last name 'Evans' and the department associated with the name 'Zampa' in the table."]}, {'end': 7943.055, 'start': 7379.509, 'title': 'Excel functions & date-time', 'summary': 'Covers the usage of excel functions like countif, countifs, sumifs, and averageif with examples from a coronavirus dataset, and explains date and time functions including today, now, time, datedif, and yearfrac.', 'duration': 563.546, 'highlights': ["The COUNTIF function was used to count the occurrences of 'Afghanistan' in the dataset, resulting in 146 occurrences, and 'India' with 155 occurrences. COUNTIF function counted 'Afghanistan' 146 times and 'India' 155 times in the dataset.", 'The COUNTIFS function was employed to find the total days when cases in India were over 100, yielding 68 days, and for the United States deaths exceeding 200, resulting in a total of more than 100,000 deaths. COUNTIFS function identified 68 days with cases over 100 in India and over 100,000 deaths in the United States.', 'The SUMIFS function was utilized to compute the total deaths in the United States, surpassing 100,000, and the total cases in France where deaths were less than 100, amounting to 20,638. SUMIFS function determined over 100,000 deaths in the United States and 20,638 cases in France with deaths less than 100.', 'The AVERAGEIF function was used to find the average daily cases in India, yielding the result till the 3rd of June. AVERAGEIF function computed the average daily cases in India till the 3rd of June.', 'The transcript also explained the usage of date and time functions such as TODAY, NOW, TIME, DATEDIF, and YEARFRAC with practical examples. Practical examples were provided for date and time functions like TODAY, NOW, TIME, DATEDIF, and YEARFRAC.']}, {'end': 8421.284, 'start': 7943.055, 'title': 'Excel date functions', 'summary': 'Covers excel date functions such as weekday, day, month, year, hour, minute, second, days360, eomonth, weekday, networkdays, and workday, providing detailed explanations and examples, with 23 working days in july and the next working day after 5 days, accounting for holidays.', 'duration': 478.229, 'highlights': ['The network days function returns the number of whole working days between two dates, excluding weekends and optional holidays, with 23 working days in July 2020. The network days function calculates the number of working days between two dates, automatically excluding weekends and optionally excluding a list of holidays, resulting in 23 working days in July 2020.', 'The workday function returns the serial number of the date before or after a specified number of workdays, skipping weekends and accounting for holidays, providing the next working day after 5 days as 8th of July 2020. The workday function calculates the serial number of the date before or after a specified number of workdays, excluding weekends and considering holidays, resulting in the next working day after 5 days as 8th of July 2020.', "The day function returns the day of the month, with today's date returning 28, representing the 28th of July. The day function returns the day of the month, where today's date returns 28, indicating the 28th of July."]}, {'end': 8774.272, 'start': 8421.784, 'title': 'Financial functions in excel', 'summary': 'Covers the usage of financial functions in excel, including pmt, irr, xirr, fv, pv, and npv, with examples and their respective results, such as finding the periodic payment for a loan, the internal rate of return, future value, and present value.', 'duration': 352.488, 'highlights': ['The PMT function in Excel is used to calculate the future payments for a loan, assuming constant payments and interest rate, and takes arguments such as rate, NPER, PV, FV, and type. The PMT function in Excel calculates the future payments for a loan, assuming constant payments and a constant interest rate. It takes arguments such as rate, NPER, PV, FV, and type.', 'Demonstration of using the PMT function to find the future payments for a loan with specific data, resulting in the principal amount and conversion to monthly payments. The demonstration of using the PMT function to find the future payments for a loan with specific data, resulting in the principal amount and conversion to monthly payments.', 'The IRR function in Excel returns the internal rate of a series of cash flows, and the XIRR function returns the internal rate for cash flows occurring in irregular intervals. The IRR function in Excel returns the internal rate of a series of cash flows, and the XIRR function returns the internal rate for cash flows occurring in irregular intervals.', 'Usage of the FV function in Excel to find the future value of an investment, assuming periodic constant payments with a constant interest rate, demonstrated with specific data. The usage of the FV function in Excel to find the future value of an investment, assuming periodic constant payments with a constant interest rate, demonstrated with specific data.', 'Demonstration of using the PV function in Excel to find the present value of an investment, with specific data and resulting in the present value. Demonstration of using the PV function in Excel to find the present value of an investment, with specific data and resulting in the present value.', 'Explanation of the NPV function in Excel, which calculates the net present value of an investment based on a discount rate and a series of future payments and income. Explanation of the NPV function in Excel, which calculates the net present value of an investment based on a discount rate and a series of future payments and income.']}], 'duration': 1676.763, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI7097509.jpg', 'highlights': ['The INDEX function returns the value at the intersection of a specific row and column in a table, demonstrated by examples where specific cities are found using row and column numbers.', "The MATCH function returns the position of an item in an array that matches a specified value, exemplified by finding the relative position of 'events' and 'Boston' in the table.", "The combination of INDEX and MATCH functions is used to return values based on specific criteria, such as finding the city associated with the last name 'Evans' and the department associated with the name 'Zampa' in the table.", 'The COUNTIFS function was employed to find the total days when cases in India were over 100, yielding 68 days, and for the United States deaths exceeding 200, resulting in a total of more than 100,000 deaths. COUNTIFS function identified 68 days with cases over 100 in India and over 100,000 deaths in the United States.', 'The SUMIFS function was utilized to compute the total deaths in the United States, surpassing 100,000, and the total cases in France where deaths were less than 100, amounting to 20,638. SUMIFS function determined over 100,000 deaths in the United States and 20,638 cases in France with deaths less than 100.', 'The network days function returns the number of whole working days between two dates, excluding weekends and optional holidays, with 23 working days in July 2020. The network days function calculates the number of working days between two dates, automatically excluding weekends and optionally excluding a list of holidays, resulting in 23 working days in July 2020.', 'The PMT function in Excel is used to calculate the future payments for a loan, assuming constant payments and interest rate, and takes arguments such as rate, NPER, PV, FV, and type. The PMT function in Excel calculates the future payments for a loan, assuming constant payments and a constant interest rate. It takes arguments such as rate, NPER, PV, FV, and type.', 'The IRR function in Excel returns the internal rate of a series of cash flows, and the XIRR function returns the internal rate for cash flows occurring in irregular intervals. The IRR function in Excel returns the internal rate of a series of cash flows, and the XIRR function returns the internal rate for cash flows occurring in irregular intervals.', 'Usage of the FV function in Excel to find the future value of an investment, assuming periodic constant payments with a constant interest rate, demonstrated with specific data. The usage of the FV function in Excel to find the future value of an investment, assuming periodic constant payments with a constant interest rate, demonstrated with specific data.', 'Explanation of the NPV function in Excel, which calculates the net present value of an investment based on a discount rate and a series of future payments and income. Explanation of the NPV function in Excel, which calculates the net present value of an investment based on a discount rate and a series of future payments and income.']}, {'end': 9891.161, 'segs': [{'end': 8860.926, 'src': 'embed', 'start': 8836.068, 'weight': 5, 'content': [{'end': 8841.952, 'text': 'and here you wanted to add a second name or the last name, which is in between the columns b and c.', 'start': 8836.068, 'duration': 5.884}, {'end': 8847.055, 'text': 'so remember that excel adds a column, always towards the left side of a column.', 'start': 8841.952, 'duration': 5.103}, {'end': 8849.717, 'text': 'so here, if you wanted to add a column next to b,', 'start': 8847.055, 'duration': 2.662}, {'end': 8860.926, 'text': 'Then you want to select the column C and right click and select the insert option so that it can be added in between B and C, which is adjacent to B.', 'start': 8850.196, 'duration': 10.73}], 'summary': 'Excel adds a column to the left. to insert a column between b and c, select column c, right click, and choose the insert option.', 'duration': 24.858, 'max_score': 8836.068, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI8836068.jpg'}, {'end': 9065.733, 'src': 'embed', 'start': 9033.936, 'weight': 2, 'content': [{'end': 9037.358, 'text': 'And in conditional formatting, you can see highlight cells.', 'start': 9033.936, 'duration': 3.422}, {'end': 9040.78, 'text': 'So in that, you can use duplicate values.', 'start': 9038.018, 'duration': 2.762}, {'end': 9045.242, 'text': 'When you click on duplicate values, a small pop-up window will come on your screen.', 'start': 9041.3, 'duration': 3.942}, {'end': 9050.422, 'text': 'here you have an option of choosing whether duplicate or unique.', 'start': 9046.099, 'duration': 4.323}, {'end': 9057.227, 'text': "so duplicate means you're comparing the cells and you can see there are some duplicate cells which are present in column 1,", 'start': 9050.422, 'duration': 6.805}, {'end': 9059.128, 'text': 'are also present in column 2.', 'start': 9057.227, 'duration': 1.901}, {'end': 9065.733, 'text': 'now you can also check how, to you know, find out the unique values which are only present in column 1 but not in column 2.', 'start': 9059.128, 'duration': 6.605}], 'summary': 'Conditional formatting can identify duplicate and unique values in cells for comparison.', 'duration': 31.797, 'max_score': 9033.936, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI9033936.jpg'}, {'end': 9378.676, 'src': 'embed', 'start': 9344.471, 'weight': 4, 'content': [{'end': 9359.34, 'text': 'So using the same lookup formula, you can add equals to VLOOKUP and also the comparison cell, which is this one.', 'start': 9344.471, 'duration': 14.869}, {'end': 9361.721, 'text': "And you're not stopping there.", 'start': 9360.12, 'duration': 1.601}, {'end': 9365.503, 'text': "You're trying to add the wildcard here.", 'start': 9362.121, 'duration': 3.382}, {'end': 9370.466, 'text': 'So the wildcard is asterisk, which means if there is anything like,', 'start': 9365.603, 'duration': 4.863}, {'end': 9378.676, 'text': 'if you get the comparison between the first cell and the second cell which matches, and if there is anything extra rather than the actual cell,', 'start': 9370.466, 'duration': 8.21}], 'summary': 'Using vlookup with wildcard to match extra content in cells.', 'duration': 34.205, 'max_score': 9344.471, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI9344471.jpg'}, {'end': 9599.449, 'src': 'embed', 'start': 9574.222, 'weight': 0, 'content': [{'end': 9579.426, 'text': 'So with that we have come to an end of this tutorial on how to freeze rows or how to freeze columns,', 'start': 9574.222, 'duration': 5.204}, {'end': 9583.309, 'text': 'or how to freeze both the panes in Excel spreadsheet.', 'start': 9579.426, 'duration': 3.883}, {'end': 9585.171, 'text': 'Lock or protect cells in Excel.', 'start': 9583.549, 'duration': 1.622}, {'end': 9589.794, 'text': "Now, let's get back into the practical mode and start our Microsoft Excel.", 'start': 9585.651, 'duration': 4.143}, {'end': 9595.559, 'text': 'Now, we are on the Microsoft Excel and this particular spreadsheet is related to employee details.', 'start': 9590.415, 'duration': 5.144}, {'end': 9599.449, 'text': 'Yeah, now the screen is visible a bit better.', 'start': 9597.588, 'duration': 1.861}], 'summary': 'Tutorial on freezing rows, columns, and panes in excel, with a focus on locking or protecting cells. practical demonstration related to employee details.', 'duration': 25.227, 'max_score': 9574.222, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI9574222.jpg'}, {'end': 9698.926, 'src': 'embed', 'start': 9664.893, 'weight': 1, 'content': [{'end': 9667.414, 'text': 'So, now you can lock the cells by the following process.', 'start': 9664.893, 'duration': 2.521}, {'end': 9671.798, 'text': 'So, in this process, you have two major steps.', 'start': 9669.336, 'duration': 2.462}, {'end': 9677.782, 'text': 'First one is to lock the cells and the next one is to protect the cells by a password.', 'start': 9672.358, 'duration': 5.424}, {'end': 9685.748, 'text': 'Now, when you select all the cells in this particular sheet, you can just click on this edge here and you have all the cells selected.', 'start': 9678.502, 'duration': 7.246}, {'end': 9691.472, 'text': 'And now, right click somewhere on the sheet and you can see an option called Format Cells.', 'start': 9686.208, 'duration': 5.264}, {'end': 9698.926, 'text': 'So, in this particular option, you have some options provided, which is number, alignment, font, and you want to go into the protection part.', 'start': 9692.799, 'duration': 6.127}], 'summary': 'Process: lock cells, protect with password. select all cells, right-click, format cells, choose protection.', 'duration': 34.033, 'max_score': 9664.893, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI9664893.jpg'}], 'start': 8774.272, 'title': 'Excel data operations', 'summary': 'Covers adding and comparing columns, freezing rows and columns, and locking and protecting cells in excel, potentially saving hours or days of manual work, discussing various methods for comparing columns, emphasizing practical applications, and demonstrating the process of locking and protecting cells to avoid mistakes while editing confidential data.', 'chapters': [{'end': 9006.547, 'start': 8774.272, 'title': 'Adding columns in excel', 'summary': 'Demonstrates how to add columns in excel, including adding single and multiple columns, utilizing right-click and insert option, with the potential to save hours or days of manual work in data analytics.', 'duration': 232.275, 'highlights': ['The chapter demonstrates how to add a new column in Excel next to an existing column using the right-click and insert option, potentially saving time and effort in manual data analysis.', 'It also explains how to add multiple columns at once by selecting and inserting them, potentially saving hours or even days of manual work in data analytics.', 'The chapter emphasizes the importance of comparing different columns in data analytics and highlights the time-saving potential of the demonstrated Excel techniques.']}, {'end': 9343.931, 'start': 9007.507, 'title': 'Comparing columns in excel', 'summary': 'Discusses various methods for comparing two columns in excel, including using conditional formatting to find duplicate and unique values, using the equals to operator to identify matches, and employing vlookup function to compare elements, with a focus on handling errors and discrepancies.', 'duration': 336.424, 'highlights': ['Using conditional formatting to find duplicate and unique values The speaker demonstrates using conditional formatting to identify duplicate and unique values in two columns, allowing users to easily visualize the matches and differences.', 'Using equals to operator to identify matches and mismatches The chapter explains the usage of the equals to operator to compare cells and determine matches or mismatches, enabling users to efficiently identify corresponding values.', 'Employing vlookup function to compare elements and handling errors The transcript details the usage of the vlookup function to compare elements in two columns and how to handle errors by adding the if error function, providing a comprehensive approach to comparing data with potential discrepancies.']}, {'end': 9595.559, 'start': 9344.471, 'title': 'Excel: freeze rows and columns', 'summary': 'Explains how to freeze rows, columns, or both in excel, using vlookup with wildcards, and emphasizes the practical application of the concept in managing spreadsheet data.', 'duration': 251.088, 'highlights': ["The tutorial demonstrates how to freeze rows and columns in Excel, providing step-by-step instructions for using the 'freeze panes' feature to keep headers visible while scrolling. It provides a detailed guide on freezing both rows and columns in Excel, ensuring the visibility of headers and data while scrolling, enhancing user experience.", 'The chapter illustrates using VLOOKUP with wildcards to fetch data, showcasing the application of the asterisk wildcard for matching and considering extra elements in comparison, resulting in improved data retrieval. It showcases the usage of VLOOKUP with wildcards, specifically the asterisk wildcard, to enhance data retrieval by considering extra elements in comparison, resulting in more comprehensive matches.', 'The transcript emphasizes the practical application of freezing rows and columns in Excel to manage spreadsheet data effectively, addressing common challenges faced when working with large datasets. It emphasizes the practical application of freezing rows and columns in Excel to effectively manage large datasets, addressing challenges related to the visibility of headers and data while scrolling.']}, {'end': 9891.161, 'start': 9597.588, 'title': 'Locking and protecting cells in excel', 'summary': 'Demonstrates the process of locking and protecting cells in excel to avoid mistakes while editing confidential data, allowing specific columns to be unlocked and edited, and ensuring protection with a simple password.', 'duration': 293.573, 'highlights': ['The chapter explains the process of locking and protecting cells in Excel to avoid mistakes while editing confidential data, allowing specific columns to be unlocked and edited, and ensuring protection with a simple password.', 'The process involves two major steps: locking the cells and protecting them with a password to prevent unauthorized edits, ensuring that only designated columns, such as phone numbers and designations, are editable.', 'The demonstration verifies the effectiveness of the protection by showing that locked cells cannot be edited without unprotecting the sheet and entering a password, while the unlocked cells remain editable for making necessary changes.']}], 'duration': 1116.889, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI8774272.jpg', 'highlights': ['The chapter emphasizes the practical application of freezing rows and columns in Excel to manage spreadsheet data effectively, addressing common challenges faced when working with large datasets.', 'The chapter explains the process of locking and protecting cells in Excel to avoid mistakes while editing confidential data, allowing specific columns to be unlocked and edited, and ensuring protection with a simple password.', 'Using conditional formatting to find duplicate and unique values The speaker demonstrates using conditional formatting to identify duplicate and unique values in two columns, allowing users to easily visualize the matches and differences.', "The tutorial demonstrates how to freeze rows and columns in Excel, providing step-by-step instructions for using the 'freeze panes' feature to keep headers visible while scrolling. It provides a detailed guide on freezing both rows and columns in Excel, ensuring the visibility of headers and data while scrolling, enhancing user experience.", 'The chapter illustrates using VLOOKUP with wildcards to fetch data, showcasing the application of the asterisk wildcard for matching and considering extra elements in comparison, resulting in improved data retrieval.', 'The chapter demonstrates how to add a new column in Excel next to an existing column using the right-click and insert option, potentially saving time and effort in manual data analysis.']}, {'end': 10639.751, 'segs': [{'end': 9929.989, 'src': 'embed', 'start': 9891.662, 'weight': 0, 'content': [{'end': 9894.984, 'text': 'Not the entire sheet, but only the cells that we kept in the unlock mode.', 'start': 9891.662, 'duration': 3.322}, {'end': 9912.775, 'text': 'Okay Now, this is how you lock and protect your cells in Excel.', 'start': 9906.171, 'duration': 6.604}, {'end': 9919.548, 'text': "How to merge cells in Excel? So let's get started and let's begin with our practical mode.", 'start': 9913.495, 'duration': 6.053}, {'end': 9929.989, 'text': 'So we are in practical mode and we have started our Microsoft Excel and you can see on my screen we have the sales data of one single employee in the month of January.', 'start': 9919.908, 'duration': 10.081}], 'summary': 'Demonstrates locking and protecting cells, merging cells, and working with sales data in excel.', 'duration': 38.327, 'max_score': 9891.662, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI9891662.jpg'}, {'end': 10241.081, 'src': 'embed', 'start': 10189.659, 'weight': 1, 'content': [{'end': 10200.888, 'text': 'so this is how the option of merge across works, and we already have been through the margin center and merge is completely similar to margin center.', 'start': 10189.659, 'duration': 11.229}, {'end': 10212.404, 'text': 'so this is how margin center merge cells and merge across and unmerge cells in excel works, how to convert rows to columns in excel.', 'start': 10200.888, 'duration': 11.516}, {'end': 10215.726, 'text': 'so for that we might want to get started with our practical mode.', 'start': 10212.404, 'duration': 3.322}, {'end': 10222.57, 'text': "now you can see, i'm on my excel worksheet and i have one table on my worksheet right now.", 'start': 10215.726, 'duration': 6.844}, {'end': 10229.134, 'text': 'so you can, you know, transposing, or you know, converting rows to columns is a very simple task.', 'start': 10222.57, 'duration': 6.564}, {'end': 10231.055, 'text': "i'll explain you in two different ways.", 'start': 10229.134, 'duration': 1.921}, {'end': 10241.081, 'text': 'So the first way select all the cells that you have on your sheet and press on control C, so it will give you the copy option.', 'start': 10231.713, 'duration': 9.368}], 'summary': 'The transcript covers merging cells, converting rows to columns, and practical demonstration in excel.', 'duration': 51.422, 'max_score': 10189.659, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI10189659.jpg'}, {'end': 10347.365, 'src': 'embed', 'start': 10319.886, 'weight': 4, 'content': [{'end': 10324.028, 'text': 'so on my screen you can see an excel spreadsheet of sales data.', 'start': 10319.886, 'duration': 4.142}, {'end': 10329.591, 'text': 'so here you have various columns regions, category, state, subcategory, sales quantity and much more.', 'start': 10324.028, 'duration': 5.563}, {'end': 10334.093, 'text': 'right. so now let us imagine that we want to add filters on the region part.', 'start': 10329.591, 'duration': 4.502}, {'end': 10336.394, 'text': 'so we have south, west, east, etc.', 'start': 10334.093, 'duration': 2.301}, {'end': 10341.277, 'text': 'right. so we want to you know, let us imagine that you want to extract the data of only best region.', 'start': 10336.394, 'duration': 4.883}, {'end': 10344.883, 'text': 'you wanted a filter, so doing it manually could take a lot of time.', 'start': 10341.901, 'duration': 2.982}, {'end': 10347.365, 'text': 'so using filters will be helpful.', 'start': 10344.883, 'duration': 2.482}], 'summary': 'Using filters in excel to extract sales data by region saves time and is helpful.', 'duration': 27.479, 'max_score': 10319.886, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI10319886.jpg'}, {'end': 10436.405, 'src': 'embed', 'start': 10411.9, 'weight': 3, 'content': [{'end': 10419.302, 'text': 'So, slices in Excel are software filters used along with Excel tables or Excel pivot tables over a large amount of data.', 'start': 10411.9, 'duration': 7.402}, {'end': 10426.923, 'text': 'Not just filtering out data, but slices also help you with an easy understanding of the information being extracted and displayed on the screen.', 'start': 10419.822, 'duration': 7.101}, {'end': 10432.104, 'text': 'Now, Microsoft Excel slices are compatible with Windows and Macintosh operating systems.', 'start': 10427.683, 'duration': 4.421}, {'end': 10436.405, 'text': 'Now, let us understand how to implement slices in Excel.', 'start': 10432.704, 'duration': 3.701}], 'summary': 'Excel slices are software filters for easy data extraction and display, compatible with windows and mac.', 'duration': 24.505, 'max_score': 10411.9, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI10411900.jpg'}, {'end': 10490.236, 'src': 'embed', 'start': 10459.036, 'weight': 5, 'content': [{'end': 10464.882, 'text': 'As you know, by default Excel considers all the data which has been inserted into the spreadsheet as a database.', 'start': 10459.036, 'duration': 5.846}, {'end': 10472.526, 'text': 'And to implement the slices in Excel, we might want to create or convert the format of database into a normal table.', 'start': 10465.542, 'duration': 6.984}, {'end': 10481.711, 'text': 'For that, you can select all your data and just press Ctrl T and that will allow you an option called convert the data into table.', 'start': 10472.586, 'duration': 9.125}, {'end': 10490.236, 'text': 'Now, since this table is already converted as a table, we can directly start implementing or inserting slices into the spreadsheet.', 'start': 10482.652, 'duration': 7.584}], 'summary': 'To implement slices in excel, convert data into a table using ctrl t, then insert slices into the spreadsheet.', 'duration': 31.2, 'max_score': 10459.036, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI10459036.jpg'}], 'start': 9891.662, 'title': 'Excel data manipulation', 'summary': 'Covers various excel data manipulation techniques, including locking, merging, centering, transposing, adding filters, and implementing slices, with practical demonstrations and step-by-step instructions, focusing on effective organization and display of data.', 'chapters': [{'end': 10085.978, 'start': 9891.662, 'title': 'Excel cells locking and merging', 'summary': 'Provides a practical demonstration of locking and protecting cells in excel, as well as merging and unmerging cells to organize and present data effectively, with a focus on the process and options available.', 'duration': 194.316, 'highlights': ['The tutorial demonstrates locking and protecting cells in Excel, and provides a practical example of organizing sales data for January, February, and March into a single quarter using the merge and unmerge functions. Sales data for January, February, and March', 'The process of merging cells in Excel is explained, including the option to merge and center cells, as well as the subsequent unmerging process. Merging and unmerging cells in Excel', 'The importance of organizing and presenting data effectively by utilizing the merge and unmerge functions in Excel is emphasized, with practical demonstrations and examples. Effective organization and presentation of data']}, {'end': 10409.394, 'start': 10086.451, 'title': 'Excel: merge, center, transpose, and filters', 'summary': 'Demonstrates how to merge and center cells, use merge across to save time, transpose rows to columns, and add filters in excel, with practical examples and step-by-step instructions.', 'duration': 322.943, 'highlights': ['Demonstrates how to use merge and center, merge across, and unmerge cells in Excel, saving time when dealing with multiple rows or columns. The speaker explains how to use merge and center, merge across, and unmerge cells in Excel, emphasizing the time-saving benefits when dealing with multiple rows or columns.', 'Explains the process of transposing rows to columns in Excel using two methods: copy-paste with transpose option and using the transpose function, offering practical steps for each method. The chapter provides step-by-step instructions for transposing rows to columns in Excel, detailing two methods: copy-paste with transpose option and using the transpose function.', 'Illustrates how to add filters in Excel to extract specific data, using practical examples to demonstrate the process of filtering data based on region, category, and subcategory. The speaker illustrates the process of adding filters in Excel, using practical examples to demonstrate how to filter data based on region, category, and subcategory.']}, {'end': 10639.751, 'start': 10411.9, 'title': 'Implementing slices in excel', 'summary': 'Explains how to implement slices in excel, allowing for easy filtering and display of data, compatible with both windows and macintosh operating systems. it details the process of converting data into a table, inserting and rearranging slices, and using them to filter and display specific information.', 'duration': 227.851, 'highlights': ['The chapter explains the process of implementing slices in Excel, which facilitates easy filtering and display of data, compatible with both Windows and Macintosh operating systems.', "The process of converting data into a table using the 'Ctrl T' shortcut is detailed, making it easier for users to implement slices in Excel.", 'The chapter details the process of inserting and rearranging slices in the Excel spreadsheet, providing a user-friendly approach to filtering and displaying specific information.']}], 'duration': 748.089, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI9891662.jpg', 'highlights': ['The tutorial demonstrates locking and protecting cells in Excel, and provides a practical example of organizing sales data for January, February, and March into a single quarter using the merge and unmerge functions. Sales data for January, February, and March', 'Demonstrates how to use merge and center, merge across, and unmerge cells in Excel, saving time when dealing with multiple rows or columns. The speaker explains how to use merge and center, merge across, and unmerge cells in Excel, emphasizing the time-saving benefits when dealing with multiple rows or columns.', 'Explains the process of transposing rows to columns in Excel using two methods: copy-paste with transpose option and using the transpose function, offering practical steps for each method. The chapter provides step-by-step instructions for transposing rows to columns in Excel, detailing two methods: copy-paste with transpose option and using the transpose function.', 'The chapter explains the process of implementing slices in Excel, which facilitates easy filtering and display of data, compatible with both Windows and Macintosh operating systems.', 'Illustrates how to add filters in Excel to extract specific data, using practical examples to demonstrate the process of filtering data based on region, category, and subcategory. The speaker illustrates the process of adding filters in Excel, using practical examples to demonstrate how to filter data based on region, category, and subcategory.', "The process of converting data into a table using the 'Ctrl T' shortcut is detailed, making it easier for users to implement slices in Excel.", 'The process of merging cells in Excel is explained, including the option to merge and center cells, as well as the subsequent unmerging process. Merging and unmerging cells in Excel', 'The importance of organizing and presenting data effectively by utilizing the merge and unmerge functions in Excel is emphasized, with practical demonstrations and examples. Effective organization and presentation of data']}, {'end': 12174.519, 'segs': [{'end': 10870.938, 'src': 'embed', 'start': 10840.608, 'weight': 0, 'content': [{'end': 10844.209, 'text': 'So now you can see our database has been successfully converted into a table.', 'start': 10840.608, 'duration': 3.601}, {'end': 10846.637, 'text': "Now let's begin with our SUMIFS.", 'start': 10844.775, 'duration': 1.862}, {'end': 10851.041, 'text': 'Now again, remember we are using SUMIFS here, not SUMIF or SUM.', 'start': 10847.057, 'duration': 3.984}, {'end': 10856.205, 'text': 'So select SUMIFS and press tab and we have our SUMIFS function.', 'start': 10851.741, 'duration': 4.464}, {'end': 10859.668, 'text': 'Now the first step which column are we looking at??', 'start': 10856.705, 'duration': 2.963}, {'end': 10861.229, 'text': 'What are we finding out right??', 'start': 10859.828, 'duration': 1.401}, {'end': 10862.41, 'text': 'Which is a SUM range?', 'start': 10861.33, 'duration': 1.08}, {'end': 10863.511, 'text': 'What SUM do you want?', 'start': 10862.591, 'duration': 0.92}, {'end': 10866.894, 'text': 'For now, we need SUM of sales.', 'start': 10864.212, 'duration': 2.682}, {'end': 10870.938, 'text': 'So select the sales column and we have a simple key function here.', 'start': 10867.175, 'duration': 3.763}], 'summary': 'Database converted to table, using sumifs for sales data.', 'duration': 30.33, 'max_score': 10840.608, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI10840608.jpg'}, {'end': 11273.077, 'src': 'embed', 'start': 11248.562, 'weight': 3, 'content': [{'end': 11256.807, 'text': 'so you can do this because it is just a small table which has least number of data and you just have one single cell to modify.', 'start': 11248.562, 'duration': 8.245}, {'end': 11258.908, 'text': 'so that can be done in small tables.', 'start': 11256.807, 'duration': 2.101}, {'end': 11264.071, 'text': 'but what if you had some table with hundreds of rows or thousands of rows?', 'start': 11258.908, 'duration': 5.163}, {'end': 11265.972, 'text': 'that will be time consuming, right.', 'start': 11264.071, 'duration': 1.901}, {'end': 11269.615, 'text': 'so for that we have some inbuilt functionality in excel.', 'start': 11265.972, 'duration': 3.643}, {'end': 11273.077, 'text': 'that is called goal seek in excel.', 'start': 11269.615, 'duration': 3.462}], 'summary': "Excel's goal seek feature enables efficient modification of large tables, saving time and effort.", 'duration': 24.515, 'max_score': 11248.562, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI11248562.jpg'}, {'end': 11395.292, 'src': 'embed', 'start': 11368.05, 'weight': 4, 'content': [{'end': 11379.459, 'text': 'Now you can see that Excel has automatically run all the permutations and combinations and has come up with a number so that the overall percentage of Mike will be 75%,', 'start': 11368.05, 'duration': 11.409}, {'end': 11390.148, 'text': "right?. So the expected marks that Mike should be getting in his computer's improvement exam should be equal to 56 or greater than 56,", 'start': 11379.459, 'duration': 10.689}, {'end': 11395.292, 'text': "to get the final target as 75 so that he can attend the company's interview.", 'start': 11390.148, 'duration': 5.144}], 'summary': "Excel calculated 75% for mike, needing 56+ marks for 75% in computer's improvement exam.", 'duration': 27.242, 'max_score': 11368.05, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI11368050.jpg'}, {'end': 11601.95, 'src': 'embed', 'start': 11536.587, 'weight': 5, 'content': [{'end': 11543.491, 'text': 'all you need to do is select the cells and drag them down until you get your required dates right.', 'start': 11536.587, 'duration': 6.904}, {'end': 11547.593, 'text': "so let's keep it till 31 days.", 'start': 11543.491, 'duration': 4.102}, {'end': 11553.336, 'text': 'there you go, you have the days, so these will be your dates.', 'start': 11547.593, 'duration': 5.743}, {'end': 11556.443, 'text': 'and here, Now, the next option was month, right?', 'start': 11553.336, 'duration': 3.107}, {'end': 11558.024, 'text': 'Yeah, month and year.', 'start': 11556.883, 'duration': 1.141}, {'end': 11564.188, 'text': 'Now using the same flash fill option, you will add the months into your sheet.', 'start': 11558.704, 'duration': 5.484}, {'end': 11567.509, 'text': 'So this will be Sunday.', 'start': 11565.328, 'duration': 2.181}, {'end': 11581.438, 'text': 'So Excel has this smart flash fill option where you can just drag the data and everything will be filled according to your requirements.', 'start': 11572.993, 'duration': 8.445}, {'end': 11583.879, 'text': 'So Sunday to Saturday.', 'start': 11582.058, 'duration': 1.821}, {'end': 11585.8, 'text': 'now the next one.', 'start': 11584.619, 'duration': 1.181}, {'end': 11589.042, 'text': 'right, that is your month.', 'start': 11585.8, 'duration': 3.242}, {'end': 11593.485, 'text': "so, same as the before process, we'll create january.", 'start': 11589.042, 'duration': 4.443}, {'end': 11601.95, 'text': 'you can use the flash fill option and all the way up to december.', 'start': 11593.485, 'duration': 8.465}], 'summary': "Using excel's flash fill, fill dates and months from january to december.", 'duration': 65.363, 'max_score': 11536.587, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI11536587.jpg'}, {'end': 11665.35, 'src': 'embed', 'start': 11636.268, 'weight': 7, 'content': [{'end': 11643.698, 'text': 'now, coming back to the original sheet, so this is where you create your drop down list right.', 'start': 11636.268, 'duration': 7.43}, {'end': 11646.86, 'text': 'so select the cell where you want to add the drop down list.', 'start': 11643.698, 'duration': 3.162}, {'end': 11652.223, 'text': 'navigate to data and then here is your data validation menu.', 'start': 11646.86, 'duration': 5.363}, {'end': 11655.384, 'text': 'so we have a variety of data validation options over here.', 'start': 11652.223, 'duration': 3.161}, {'end': 11657.025, 'text': 'there is an exclusive tutorial on that.', 'start': 11655.384, 'duration': 1.641}, {'end': 11658.306, 'text': 'you can go through it.', 'start': 11657.025, 'duration': 1.281}, {'end': 11665.35, 'text': "for now, we'll look into the list option only because we want to create a drop down list right.", 'start': 11658.306, 'duration': 7.044}], 'summary': 'Tutorial on creating a drop down list in excel.', 'duration': 29.082, 'max_score': 11636.268, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI11636268.jpg'}, {'end': 12110.275, 'src': 'embed', 'start': 12080.911, 'weight': 9, 'content': [{'end': 12087.894, 'text': 'All you have to use is the dated if function, or if you also call it as date diff function based on your choice.', 'start': 12080.911, 'duration': 6.983}, {'end': 12092.997, 'text': 'We also have a specific entire tutorial based on date diff in Excel for further information.', 'start': 12088.235, 'duration': 4.762}, {'end': 12095.628, 'text': 'That video will be linked in the description box below.', 'start': 12093.447, 'duration': 2.181}, {'end': 12103.472, 'text': 'You can go through it or you can use the end screens and I cuts link to this video to get back to the tutorial based on date diff in Excel.', 'start': 12095.708, 'duration': 7.764}, {'end': 12110.275, 'text': "Now let's calculate the age of these dates or these people with these data birds.", 'start': 12104.032, 'duration': 6.243}], 'summary': 'Use date diff function in excel for calculating age.', 'duration': 29.364, 'max_score': 12080.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12080911.jpg'}], 'start': 10640.571, 'title': 'Excel data analysis and validation', 'summary': 'Covers using sumifs to calculate sales based on conditions, goal seek to determine minimum exam score, creating drop-down menus for calendar and data validation, and setting up drop-down lists for data validation in excel.', 'chapters': [{'end': 11145.542, 'start': 10640.571, 'title': 'Using sumifs in excel', 'summary': 'Explains how to use sumifs in excel to calculate the sum of sales based on specific conditions, such as region and category, simplifying data analysis and reporting.', 'duration': 504.971, 'highlights': ['The tutorial demonstrates using SUMIFS in Excel to calculate the sum of sales based on specific conditions, such as region and category, simplifying data analysis and reporting. Demonstrates usage of SUMIFS in Excel, simplifies data analysis and reporting', 'Explains the process of converting data into a table format in Excel and using the SUMIFS function to fetch and calculate data from specific columns based on given conditions. Conversion of data into table format, usage of SUMIFS function for data calculation', 'Illustrates the step-by-step process of using SUMIFS in Excel, including selecting the sum range, criteria range, and specifying the data conditions to fetch specific sales data. Step-by-step process of using SUMIFS, selecting sum range and criteria range, specifying data conditions']}, {'end': 11395.292, 'start': 11145.542, 'title': 'Using goal seek in excel', 'summary': "Discusses using the 'goal seek' functionality in excel to determine the minimum score required in an exam to reach a target percentage, demonstrated with a student needing to achieve 75% overall to attend a company interview.", 'duration': 249.75, 'highlights': ['The minimum percentage to attend the interview is 75%, which all students, except for one, have achieved.', "The student, Mike, needs to use the 'Goal Seek' functionality in Excel to determine the minimum score required in his computer subject to reach a 75% overall percentage.", "Excel's 'Goal Seek' feature automatically runs permutations and combinations to calculate the required score for a student to achieve a specific target percentage, such as 75% for Mike.", "The 'Goal Seek' functionality in Excel can be used to efficiently determine the needed exam score, especially for large datasets, saving time and effort."]}, {'end': 11636.268, 'start': 11395.732, 'title': 'Excel data validation and drop-down menus', 'summary': 'Demonstrates how to create a calendar with drop-down menus in excel using data validation and flash fill for days, months, and years, reducing manual effort and ensuring accuracy for up to 31 days, 12 months, and years from 1990 to 2022.', 'duration': 240.536, 'highlights': ['Creating a Calendar with Drop-Down Menus The chapter explains the process of creating a calendar with drop-down menus in Excel using data validation and flash fill for days, months, and years, ensuring accuracy for up to 31 days, 12 months, and years from 1990 to 2022.', 'Utilizing Data Validation and Flash Fill The process involves using data validation methods and flash fill for days, months, and years, reducing manual effort and ensuring accuracy for up to 31 days, 12 months, and years from 1990 to 2022.', 'Importance of Drop-Down Menus for Calendar Creation The drop-down menus for days, months, and years in the calendar creation process reduce manual effort and ensure accuracy for up to 31 days, 12 months, and years from 1990 to 2022.']}, {'end': 12174.519, 'start': 11636.268, 'title': 'Creating excel drop-down lists', 'summary': 'Demonstrates how to create drop-down lists in excel to ensure data validation, with examples and explanations of setting up the lists and error messages, and concludes with a brief overview of calculating age using the datedif function.', 'duration': 538.251, 'highlights': ['The chapter explains how to set up drop-down lists in Excel for data validation, including selecting the range, adding input messages, and specifying error alert titles, with an emphasis on ensuring correct and valid data entry.', 'Demonstrating the process of setting up drop-down lists in Excel for days, months, and weeks, along with providing examples of error messages and the importance of selecting from the drop-down lists only, to maintain data integrity.', "The tutorial includes a brief overview of calculating age in Excel using the datedif function, with a step-by-step demonstration of the function's parameters and the resulting age calculation based on today's date."]}], 'duration': 1533.948, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI10640571.jpg', 'highlights': ['Demonstrates usage of SUMIFS in Excel, simplifies data analysis and reporting', 'Conversion of data into table format, usage of SUMIFS function for data calculation', 'Step-by-step process of using SUMIFS, selecting sum range and criteria range, specifying data conditions', "The 'Goal Seek' functionality in Excel can be used to efficiently determine the needed exam score, especially for large datasets, saving time and effort", "Excel's 'Goal Seek' feature automatically runs permutations and combinations to calculate the required score for a student to achieve a specific target percentage, such as 75% for Mike", 'Creating a calendar with drop-down menus in Excel using data validation and flash fill for days, months, and years, ensuring accuracy for up to 31 days, 12 months, and years from 1990 to 2022', 'Utilizing data validation methods and flash fill for days, months, and years, reducing manual effort and ensuring accuracy for up to 31 days, 12 months, and years from 1990 to 2022', 'The chapter explains how to set up drop-down lists in Excel for data validation, including selecting the range, adding input messages, and specifying error alert titles, with an emphasis on ensuring correct and valid data entry', 'Demonstrating the process of setting up drop-down lists in Excel for days, months, and weeks, along with providing examples of error messages and the importance of selecting from the drop-down lists only, to maintain data integrity', "The tutorial includes a brief overview of calculating age in Excel using the datedif function, with a step-by-step demonstration of the function's parameters and the resulting age calculation based on today's date"]}, {'end': 12853.557, 'segs': [{'end': 12314.951, 'src': 'embed', 'start': 12265.906, 'weight': 0, 'content': [{'end': 12269.408, 'text': 'So here also we have made some minor cell formatting.', 'start': 12265.906, 'duration': 3.502}, {'end': 12276.811, 'text': 'So usually it comes out as a MPM, right? But instead of that, we have made a minor cell formatting.', 'start': 12270.068, 'duration': 6.743}, {'end': 12283.533, 'text': 'We have navigated into time and we have selected the 24 hour formatting so that we have just ours in our output.', 'start': 12277.011, 'duration': 6.522}, {'end': 12285.554, 'text': "So that's how we have calculated the.", 'start': 12283.953, 'duration': 1.601}, {'end': 12290.78, 'text': 'time difference between two in times and out times of an employee using Excel.', 'start': 12286.018, 'duration': 4.762}, {'end': 12293.281, 'text': 'Calculating percentage in Excel.', 'start': 12291.22, 'duration': 2.061}, {'end': 12295.562, 'text': "Now let's get into practical mode.", 'start': 12293.941, 'duration': 1.621}, {'end': 12302.605, 'text': 'So on my screen, you can see a simple spreadsheet with student names and their marks on my sheet.', 'start': 12296.302, 'duration': 6.303}, {'end': 12310.428, 'text': 'So we will be calculating what are the total marks obtained by the students in overall subjects and calculate what is the percentage.', 'start': 12303.205, 'duration': 7.223}, {'end': 12314.951, 'text': 'Now we will carry forward a few simple inbuilt functions in Excel to calculate that.', 'start': 12311.01, 'duration': 3.941}], 'summary': 'Demonstrated formatting, time calculation, and percentage calculation in excel, using simple inbuilt functions.', 'duration': 49.045, 'max_score': 12265.906, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12265906.jpg'}, {'end': 12550.853, 'src': 'embed', 'start': 12515.082, 'weight': 2, 'content': [{'end': 12519.224, 'text': 'It would be a little bit more, or good to read, right?', 'start': 12515.082, 'duration': 4.142}, {'end': 12521.546, 'text': "So that's what a round off actually means.", 'start': 12519.605, 'duration': 1.941}, {'end': 12525.788, 'text': "So now let's work on having rounded off values for the percentage.", 'start': 12521.966, 'duration': 3.822}, {'end': 12528.349, 'text': "Now let's create a new column.", 'start': 12526.288, 'duration': 2.061}, {'end': 12538.015, 'text': "Let's name it as round off percentage.", 'start': 12530.09, 'duration': 7.925}, {'end': 12550.853, 'text': 'Great. so to actually perform the round off formula, we do have a predefined method for that.', 'start': 12540.476, 'duration': 10.377}], 'summary': 'Creating a new column for rounded off percentage values using a predefined method.', 'duration': 35.771, 'max_score': 12515.082, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12515082.jpg'}, {'end': 12739.56, 'src': 'embed', 'start': 12709.795, 'weight': 3, 'content': [{'end': 12714.118, 'text': 'So the way is to select the cells and right click and select the format cells option.', 'start': 12709.795, 'duration': 4.323}, {'end': 12722.376, 'text': 'And in here you can see the date function and in here you can see different types of modifications that you can do to your data.', 'start': 12714.835, 'duration': 7.541}, {'end': 12728.117, 'text': 'Right So you might want to also change your days based on the location.', 'start': 12722.856, 'duration': 5.261}, {'end': 12729.378, 'text': 'So right now we are in India.', 'start': 12728.157, 'duration': 1.221}, {'end': 12734.959, 'text': 'And imagine if you wanted to change something based on U.S., or if you are.', 'start': 12729.938, 'duration': 5.021}, {'end': 12739.56, 'text': 'if you are having your client in U.S. and he wants the dates in U.S. format, you can also change that.', 'start': 12734.959, 'duration': 4.601}], 'summary': 'Instructions on formatting cells and changing date formats for international use.', 'duration': 29.765, 'max_score': 12709.795, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12709795.jpg'}], 'start': 12174.92, 'title': 'Using excel functions for practical applications', 'summary': 'Covers practical applications such as calculating time difference for employee work hours and using excel functions for tasks like summing, finding percentages, averaging, rounding off, and customizing date formats.', 'chapters': [{'end': 12290.78, 'start': 12174.92, 'title': 'Calculating time difference in excel', 'summary': 'Explains how to calculate the time difference between in and out timings of an employee using excel, including formatting options and the simple calculation method, resulting in the total number of hours worked.', 'duration': 115.86, 'highlights': ['The chapter explains how to calculate the time difference between in and out timings of an employee using Excel It demonstrates the process of calculating the total number of hours worked by an employee in an organization based on in and out timings.', "The simple calculation method of time difference is demonstrated, resulting in the total number of hours worked. It instructs to use the formula 'out time minus in time' to calculate the time difference and obtain the total number of hours worked by the employee.", 'The chapter also covers the cell formatting options for AM PM and 24-hour timing formats. It explains the option of choosing between AM PM and 24-hour timing formats for cell formatting in Excel.']}, {'end': 12853.557, 'start': 12291.22, 'title': 'Excel functions: sum, percentage, average, round off, and date format', 'summary': 'Covers the practical use of excel functions, including calculating total marks and percentage, finding the average of sales data, rounding off percentage values, and customizing date formats, demonstrating various operations using inbuilt excel functions.', 'duration': 562.337, 'highlights': ['Calculating Percentage Demonstrated the process of calculating percentage in Excel by dividing the total marks obtained by the total marks available and multiplying the result by 100, showcasing the practical use of inbuilt functions to calculate percentage for individual students.', 'Calculating Sum and Average Illustrated the use of the sum function to calculate the total marks obtained by students and the average function to find the average of sales data, providing practical demonstrations of these operations in Excel.', 'Rounding Off Formula Explained the process of rounding off percentage values in Excel using the round function, showcasing how to achieve rounded off values with specific decimal points for better readability and presentation.', 'Formatting Date in Excel Demonstrated the customization of date formats in Excel, including changing date formats based on location, modifying formats to display only months or adding granularity with day, time, and timing formats, showcasing the versatility of date formatting options.']}], 'duration': 678.637, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12174920.jpg', 'highlights': ['Demonstrates calculating time difference for employee work hours using Excel.', 'Illustrates calculating percentage, sum, and average using inbuilt Excel functions.', 'Explains rounding off percentage values in Excel using the round function.', 'Demonstrates customization of date formats in Excel for better readability and presentation.']}, {'end': 15011.425, 'segs': [{'end': 12876.486, 'src': 'embed', 'start': 12855.945, 'weight': 0, 'content': [{'end': 12866.035, 'text': 'DAX in Excel is a sophisticated formulas-type language that comes in handy while working with relational data and extracting information via dynamic aggregation functions.', 'start': 12855.945, 'duration': 10.09}, {'end': 12870.179, 'text': 'DAX in Excel stands for Data Analysis Expressions.', 'start': 12866.676, 'duration': 3.503}, {'end': 12876.486, 'text': 'DAX functions are completely familiar to the general and default functions that are available in Excel.', 'start': 12870.86, 'duration': 5.626}], 'summary': 'Dax in excel is a powerful language for dynamic aggregation functions and data extraction.', 'duration': 20.541, 'max_score': 12855.945, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12855945.jpg'}, {'end': 12929.204, 'src': 'embed', 'start': 12898.599, 'weight': 2, 'content': [{'end': 12903.361, 'text': 'date and time information, time, logical parent and many more.', 'start': 12898.599, 'duration': 4.762}, {'end': 12911.284, 'text': "now in today's session, we will look into one such type of dax function in excel, that is, dax date and time function in excel.", 'start': 12903.361, 'duration': 7.923}, {'end': 12915.486, 'text': "now, without further ado, let's get into practical mode.", 'start': 12911.284, 'duration': 4.202}, {'end': 12918.067, 'text': 'and we have started our microsoft excel.', 'start': 12915.486, 'duration': 2.581}, {'end': 12921.488, 'text': 'inside excel we have opened a new blank worksheet.', 'start': 12918.067, 'duration': 3.421}, {'end': 12929.204, 'text': 'now navigate to data option in the toolbar and in that go to the power pivot window.', 'start': 12921.488, 'duration': 7.716}], 'summary': 'Exploring dax date and time function in excel.', 'duration': 30.605, 'max_score': 12898.599, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12898599.jpg'}, {'end': 13054.927, 'src': 'embed', 'start': 13024.516, 'weight': 3, 'content': [{'end': 13026.398, 'text': 'Right now we have so much of data.', 'start': 13024.516, 'duration': 1.882}, {'end': 13030.464, 'text': 'Now let us imagine that we want to calculate the retirement age of all the employees.', 'start': 13026.879, 'duration': 3.585}, {'end': 13034.59, 'text': "So now let's rename the column to retirement.", 'start': 13030.945, 'duration': 3.645}, {'end': 13045.102, 'text': 'So we have our new column that is retirement date.', 'start': 13043.001, 'duration': 2.101}, {'end': 13054.927, 'text': 'Now we will make use of DAX date time functions to calculate the age and find out the exact retirement date of that particular employee.', 'start': 13045.522, 'duration': 9.405}], 'summary': 'Using dax date time functions to calculate retirement age for all employees.', 'duration': 30.411, 'max_score': 13024.516, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI13024516.jpg'}, {'end': 13407.784, 'src': 'embed', 'start': 13374.779, 'weight': 5, 'content': [{'end': 13376.42, 'text': 'We are on our Excel spreadsheet.', 'start': 13374.779, 'duration': 1.641}, {'end': 13380.123, 'text': 'Now creating a pivot table is just away from a few clicks.', 'start': 13376.721, 'duration': 3.402}, {'end': 13384.486, 'text': "Trust me, it is just a couple of clicks and you'll have your pivot table ready to operate.", 'start': 13380.643, 'duration': 3.843}, {'end': 13388.709, 'text': 'Now click any cell or select any cell on your spreadsheet.', 'start': 13384.946, 'duration': 3.763}, {'end': 13394.415, 'text': 'which has the data and navigate to insert menu on the toolbar and select pivot table.', 'start': 13389.232, 'duration': 5.183}, {'end': 13396.397, 'text': "and that's how simple it is.", 'start': 13394.415, 'duration': 1.982}, {'end': 13399.799, 'text': 'now automatically excel will choose your data.', 'start': 13396.397, 'duration': 3.402}, {'end': 13402.641, 'text': 'you can see the lines over here dotted lines.', 'start': 13399.799, 'duration': 2.842}, {'end': 13407.784, 'text': 'that means the excel has already selected the range of data that you want to put into your pivot table,', 'start': 13402.641, 'duration': 5.143}], 'summary': 'Creating a pivot table in excel is just a few clicks away, making it simple and efficient for data analysis.', 'duration': 33.005, 'max_score': 13374.779, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI13374779.jpg'}, {'end': 13931.887, 'src': 'heatmap', 'start': 13417.478, 'weight': 6, 'content': [{'end': 13420.199, 'text': 'Now, here you can see your pivot table is a little empty.', 'start': 13417.478, 'duration': 2.721}, {'end': 13424.26, 'text': 'All you need to do is drag and drop the data elements that you want on your pivot table.', 'start': 13420.339, 'duration': 3.921}, {'end': 13435.842, 'text': 'So here you can see on a spreadsheet we have some business data related to a store which has furniture and states and subcategories of the furniture and all the you know.', 'start': 13424.9, 'duration': 10.942}, {'end': 13438.903, 'text': 'in in category you have furniture, office supplies, technology, et cetera, right?', 'start': 13435.842, 'duration': 3.061}, {'end': 13442.724, 'text': 'So those have some subcategories as well and different states.', 'start': 13439.303, 'duration': 3.421}, {'end': 13446.893, 'text': 'and the regions as well, and the quantity total sales happen, right.', 'start': 13443.288, 'duration': 3.605}, {'end': 13453.963, 'text': 'so, using this business data, we can find out some key insights of this particular data using our pivot table right.', 'start': 13446.893, 'duration': 7.07}, {'end': 13456.186, 'text': "so here we'll add the data.", 'start': 13453.963, 'duration': 2.223}, {'end': 13467.457, 'text': 'now let me drag the region into rows, click and drag, and let me drag uh categories into columns,', 'start': 13456.186, 'duration': 11.271}, {'end': 13471.258, 'text': 'which are the different categories which are involved in sales data and values.', 'start': 13467.457, 'duration': 3.801}, {'end': 13472.939, 'text': 'what are the sales happened?', 'start': 13471.258, 'duration': 1.681}, {'end': 13481.223, 'text': 'right, and there you go, you have furnitures, office supplies, technology as the three different categories,', 'start': 13472.939, 'duration': 8.284}, {'end': 13487.406, 'text': 'and those are the sales happened in central east, south and west regions, and this is the grand total.', 'start': 13481.223, 'duration': 6.183}, {'end': 13491.529, 'text': 'and instead of regions, you know, you have all the regions over here, right.', 'start': 13487.406, 'duration': 4.123}, {'end': 13493.61, 'text': 'so central east, south, west.', 'start': 13491.529, 'duration': 2.081}, {'end': 13502.954, 'text': 'or instead of, uh, all the regions, or instead of all the categories, you can, you know, place a specific category in your pivot table.', 'start': 13493.61, 'duration': 9.344}, {'end': 13511.778, 'text': 'for example, let me push the category into the filters and here you can see the total sales happened in those particular regions.', 'start': 13502.954, 'duration': 8.824}, {'end': 13517.081, 'text': 'now, what if someone asks you to find the sales of only furniture right?', 'start': 13511.778, 'duration': 5.303}, {'end': 13524.024, 'text': "so your manager comes to you and he'll ask you getting the sales of only furnitures, you can select that and there you go.", 'start': 13517.081, 'duration': 6.943}, {'end': 13528.626, 'text': 'you have the furniture sales in all the regions, right, or you can also do the vice versa.', 'start': 13524.024, 'duration': 4.602}, {'end': 13533.494, 'text': 'He can also drag regions into the filters.', 'start': 13529.451, 'duration': 4.043}, {'end': 13539.537, 'text': 'And if you wanted to find out the region, wise sales of furniture, you can also do that right?', 'start': 13533.714, 'duration': 5.823}, {'end': 13545.641, 'text': 'So here we have furniture and here you have all the regions.', 'start': 13542.459, 'duration': 3.182}, {'end': 13549.944, 'text': 'And let us try to find the sales of furniture in the central region.', 'start': 13546.102, 'duration': 3.842}, {'end': 13554.286, 'text': 'And there you go, right? So this is how the pivot table works.', 'start': 13550.224, 'duration': 4.062}, {'end': 13555.586, 'text': 'And you can also remove the regions.', 'start': 13554.346, 'duration': 1.24}, {'end': 13561.37, 'text': 'For example, if you wanted to remove a certain element from filters or anywhere if it is columns, rows,', 'start': 13555.666, 'duration': 5.704}, {'end': 13564.151, 'text': 'anything you can just drag and drop it right?', 'start': 13561.37, 'duration': 2.781}, {'end': 13565.872, 'text': "So that's how it works.", 'start': 13564.531, 'duration': 1.341}, {'end': 13577.119, 'text': 'And also you can increase the level of detail or improve the granularity, right? So here we have categories on our columns.', 'start': 13566.613, 'duration': 10.506}, {'end': 13580.941, 'text': 'So you can also include subcategories into the columns.', 'start': 13577.279, 'duration': 3.662}, {'end': 13603.169, 'text': 'right and and you have the region wise sales so in the region filter select all press ok and in the category select all Press OK.', 'start': 13581.977, 'duration': 21.192}, {'end': 13608.473, 'text': 'And you have the furniture here, office supplies here and technology here.', 'start': 13604.01, 'duration': 4.463}, {'end': 13617.678, 'text': 'And also if you want to include the quantity, how much or how many number of things got sold in those particular categories and subcategories.', 'start': 13609.133, 'duration': 8.545}, {'end': 13618.579, 'text': 'you can also do that.', 'start': 13617.678, 'duration': 0.901}, {'end': 13627.404, 'text': 'So here in the furniture section, books, these are the sales and these are the number of quantity or elements sold in those particular sectors.', 'start': 13619.119, 'duration': 8.285}, {'end': 13628.73, 'text': 'you can.', 'start': 13628.45, 'duration': 0.28}, {'end': 13631.491, 'text': 'you can also improve the level of granularity.', 'start': 13628.73, 'duration': 2.761}, {'end': 13638.072, 'text': 'you can also improve the level of detail using pivot tables in excel.', 'start': 13631.491, 'duration': 6.581}, {'end': 13641.573, 'text': 'now here we have four different sheets of the sales.', 'start': 13638.072, 'duration': 3.501}, {'end': 13647.715, 'text': 'so the first sheet is the west zone, next is south zone, east zone and central zone.', 'start': 13641.573, 'duration': 6.142}, {'end': 13650.415, 'text': 'so all this data belongs to one single sheet.', 'start': 13647.715, 'duration': 2.7}, {'end': 13652.356, 'text': 'that is superstore data set.', 'start': 13650.415, 'duration': 1.941}, {'end': 13660.951, 'text': 'so i have categorized or i have taken a part of the data which belongs to Central Region, South Region, West Region and East Region.', 'start': 13652.356, 'duration': 8.595}, {'end': 13667.453, 'text': 'Now we are going to use all these four sheets and create one single sheet which is the pivot table of all the four sheets.', 'start': 13661.371, 'duration': 6.082}, {'end': 13670.773, 'text': "Now without further ado, let's start the process.", 'start': 13668.233, 'duration': 2.54}, {'end': 13675.895, 'text': 'Now for that we might want to use the shortcut key that is Alt D.', 'start': 13671.314, 'duration': 4.581}, {'end': 13677.675, 'text': 'Now press P.', 'start': 13675.895, 'duration': 1.78}, {'end': 13679.736, 'text': 'Now this starts the pivot chart wizard.', 'start': 13677.675, 'duration': 2.061}, {'end': 13684.737, 'text': 'Now here we have to choose the option Multiple Consolidation Ranges.', 'start': 13681.036, 'duration': 3.701}, {'end': 13687.179, 'text': 'Then click on next.', 'start': 13686.218, 'duration': 0.961}, {'end': 13693.522, 'text': 'And here you can see the pivot table is clicked by default.', 'start': 13689.2, 'duration': 4.322}, {'end': 13695.203, 'text': "Now we'll go to next.", 'start': 13694.382, 'duration': 0.821}, {'end': 13703.867, 'text': 'Now here click I will create page fields or the pivot chart wizard will automatically create if you want that to create for you.', 'start': 13695.943, 'duration': 7.924}, {'end': 13705.648, 'text': 'Now click on next.', 'start': 13704.608, 'duration': 1.04}, {'end': 13708.17, 'text': "Now here let's select the range.", 'start': 13706.289, 'duration': 1.881}, {'end': 13711.291, 'text': "Now I'll be selecting the range.", 'start': 13710.011, 'duration': 1.28}, {'end': 13714.593, 'text': 'Add it.', 'start': 13714.453, 'duration': 0.14}, {'end': 13717.804, 'text': "Now next we'll select East.", 'start': 13716.423, 'duration': 1.381}, {'end': 13726.97, 'text': 'Followed by that we have South.', 'start': 13723.208, 'duration': 3.762}, {'end': 13731.013, 'text': 'And finally we have West.', 'start': 13729.732, 'duration': 1.281}, {'end': 13737.978, 'text': 'Now we will name these according to the regions.', 'start': 13735.336, 'duration': 2.642}, {'end': 13744.783, 'text': 'So the first one will be West.', 'start': 13743.142, 'duration': 1.641}, {'end': 13748.793, 'text': 'Next one will be south.', 'start': 13747.432, 'duration': 1.361}, {'end': 13758.54, 'text': 'Next one will be east.', 'start': 13757.299, 'duration': 1.241}, {'end': 13762.543, 'text': 'And the last one will be central.', 'start': 13760.862, 'duration': 1.681}, {'end': 13776.554, 'text': 'Click on next and here select new worksheet and finish.', 'start': 13772.491, 'duration': 4.063}, {'end': 13778.791, 'text': 'there you go.', 'start': 13777.911, 'duration': 0.88}, {'end': 13780.452, 'text': 'you have the pivot table here.', 'start': 13778.791, 'duration': 1.661}, {'end': 13783.633, 'text': 'now you might want to change the summation value.', 'start': 13780.452, 'duration': 3.181}, {'end': 13795.516, 'text': 'so click anywhere on the pivot table and change it from count to sum so that you have the summation of the total number of sales, subcategory,', 'start': 13783.633, 'duration': 11.883}, {'end': 13798.477, 'text': 'sales and grand total of sales, etc.', 'start': 13795.516, 'duration': 2.961}, {'end': 13801.197, 'text': 'now, here you can see all option.', 'start': 13798.477, 'duration': 2.72}, {'end': 13810.024, 'text': "now let's select central and you'll get the details of the central region, the grand total, the sales happening, quantity of sales, etc.", 'start': 13801.197, 'duration': 8.827}, {'end': 13817.166, 'text': 'now here you can see the furniture, office supplies, technology and the grand total, all in one single sheet.', 'start': 13810.024, 'duration': 7.142}, {'end': 13819.667, 'text': "now let's get back to the original sheet.", 'start': 13817.166, 'duration': 2.501}, {'end': 13827.729, 'text': 'so, as you can see, this is the original sheet and in the original sheet you can see various more categories, which is the,', 'start': 13819.667, 'duration': 8.062}, {'end': 13838.788, 'text': 'which is the shipment mode, customer, customer segment, country, city, state, and you also have product name, subcategory, etc.', 'start': 13828.766, 'duration': 10.022}, {'end': 13847.21, 'text': 'So if you had added all these into this sheet, you can also create a separate option of adding minute details to your pivot table.', 'start': 13839.169, 'duration': 8.041}, {'end': 13851.692, 'text': 'So that is how the pivot table is created using multiple sheets.', 'start': 13847.891, 'duration': 3.801}, {'end': 13853.432, 'text': 'Pivot charts in Excel.', 'start': 13852.052, 'duration': 1.38}, {'end': 13857.672, 'text': 'So how to create a Pivot Chart you ask? It is really simple.', 'start': 13854.19, 'duration': 3.482}, {'end': 13864.436, 'text': 'All you need to do is just follow few steps and you have your Pivot Chart ready, which is completely interactive.', 'start': 13858.232, 'duration': 6.204}, {'end': 13872.82, 'text': 'Now all you have to do is select any cell in the data set you have and rush into the insert toolbar.', 'start': 13864.956, 'duration': 7.864}, {'end': 13877.203, 'text': 'Then in the group of charts, you have an option called Pivot Chart.', 'start': 13873.301, 'duration': 3.902}, {'end': 13878.143, 'text': 'Select on it.', 'start': 13877.503, 'duration': 0.64}, {'end': 13886.833, 'text': "Now you'll be asked to select the data range and you can see excel has automatically select all the range that you want to include in your pivot chart.", 'start': 13878.563, 'duration': 8.27}, {'end': 13894.475, 'text': 'then you have another option, which is asking you to either create the pivot chart in the current worksheet or a new worksheet.', 'start': 13886.833, 'duration': 7.642}, {'end': 13898.716, 'text': "for now, let's select a new worksheet and there you go.", 'start': 13894.475, 'duration': 4.241}, {'end': 13900.616, 'text': 'voila, the pivot chart is completely ready.', 'start': 13898.716, 'duration': 1.9}, {'end': 13903.257, 'text': 'you might ask me this is completely empty.', 'start': 13900.616, 'duration': 2.641}, {'end': 13908.543, 'text': 'yes, the default pivot chart, what you get from excel, will be completely empty,', 'start': 13903.257, 'duration': 5.286}, {'end': 13919.505, 'text': 'but you can add the legends and all the data that you want to add onto your pivot chart just by dragging and dropping the data from the right hand side of your excel sheet,', 'start': 13908.543, 'duration': 10.962}, {'end': 13921.906, 'text': 'that is, from the pivot chart fields.', 'start': 13919.505, 'duration': 2.401}, {'end': 13928.227, 'text': 'so you can see we have various options region, category, state subcategory, sales quantity, etc.', 'start': 13921.906, 'duration': 6.321}, {'end': 13931.887, 'text': "now i'll add region,", 'start': 13928.227, 'duration': 3.66}], 'summary': 'Using pivot tables in excel, sales data is analyzed to derive key insights and trends, allowing for easy manipulation and visualization of data.', 'duration': 514.409, 'max_score': 13417.478, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI13417478.jpg'}, {'end': 13877.203, 'src': 'embed', 'start': 13847.891, 'weight': 7, 'content': [{'end': 13851.692, 'text': 'So that is how the pivot table is created using multiple sheets.', 'start': 13847.891, 'duration': 3.801}, {'end': 13853.432, 'text': 'Pivot charts in Excel.', 'start': 13852.052, 'duration': 1.38}, {'end': 13857.672, 'text': 'So how to create a Pivot Chart you ask? It is really simple.', 'start': 13854.19, 'duration': 3.482}, {'end': 13864.436, 'text': 'All you need to do is just follow few steps and you have your Pivot Chart ready, which is completely interactive.', 'start': 13858.232, 'duration': 6.204}, {'end': 13872.82, 'text': 'Now all you have to do is select any cell in the data set you have and rush into the insert toolbar.', 'start': 13864.956, 'duration': 7.864}, {'end': 13877.203, 'text': 'Then in the group of charts, you have an option called Pivot Chart.', 'start': 13873.301, 'duration': 3.902}], 'summary': "Creating a pivot chart in excel is simple, just follow a few steps, and it's completely interactive.", 'duration': 29.312, 'max_score': 13847.891, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI13847891.jpg'}, {'end': 13966.249, 'src': 'embed', 'start': 13943.638, 'weight': 8, 'content': [{'end': 13952.362, 'text': 'Now you have all the data in the form of pivot chart, which is completely interactive, which represents the sales happening in those regions.', 'start': 13943.638, 'duration': 8.724}, {'end': 13955.783, 'text': 'Now you can also select a particular region right?', 'start': 13952.782, 'duration': 3.001}, {'end': 13960.685, 'text': 'By default, all the regions are selected, but if you want to select only central,', 'start': 13956.203, 'duration': 4.482}, {'end': 13964.887, 'text': 'then you can do that by deselecting everything and select only central and select.', 'start': 13960.685, 'duration': 4.202}, {'end': 13966.249, 'text': 'Okay And there you go.', 'start': 13965.027, 'duration': 1.222}], 'summary': 'Interactive pivot chart displays regional sales data, allowing selection of specific regions.', 'duration': 22.611, 'max_score': 13943.638, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI13943638.jpg'}, {'end': 14044.353, 'src': 'embed', 'start': 14018.622, 'weight': 11, 'content': [{'end': 14026.175, 'text': 'so here you can see both pivot table and pivot charts are completely interactive charts in excel.', 'start': 14018.622, 'duration': 7.553}, {'end': 14033.482, 'text': "now let's quickly get on to the practical mode, where we will have some data sets over which we will be trying to apply the charts.", 'start': 14026.175, 'duration': 7.307}, {'end': 14040.509, 'text': 'now there are various types of charts in tableau and those are the pie chart, the column chart, the bar chart,', 'start': 14033.482, 'duration': 7.027}, {'end': 14044.353, 'text': 'column versus line pivot chart and sparkline charts.', 'start': 14040.509, 'duration': 3.844}], 'summary': 'Learn about interactive pivot tables and various types of charts in excel and tableau.', 'duration': 25.731, 'max_score': 14018.622, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI14018622.jpg'}, {'end': 14512.272, 'src': 'embed', 'start': 14487.467, 'weight': 10, 'content': [{'end': 14494.931, 'text': 'select the change chart type option and since we wanted to create a column versus line chart, it is a combination.', 'start': 14487.467, 'duration': 7.464}, {'end': 14502.255, 'text': 'whenever you want one or two types of charts included in one single chart window, then it has a combo option.', 'start': 14494.931, 'duration': 7.324}, {'end': 14508.639, 'text': 'so here we need to select the combo option and inside that we have various types of combination charts.', 'start': 14502.255, 'duration': 6.384}, {'end': 14509.84, 'text': 'we have clustered column.', 'start': 14508.639, 'duration': 1.201}, {'end': 14512.272, 'text': 'We have clustered line on secondary axis.', 'start': 14510.25, 'duration': 2.022}], 'summary': 'To combine column and line charts, choose combo option, including clustered column and clustered line on secondary axis.', 'duration': 24.805, 'max_score': 14487.467, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI14487467.jpg'}], 'start': 12855.945, 'title': 'Excel data analysis and visualization', 'summary': 'Explores dax in excel for relational data, dynamic aggregation functions, and date/time functions. it also covers importing data with power pivot and dax functions, creating pivot tables for data analysis, and generating various types of charts for data visualization in excel.', 'chapters': [{'end': 12921.488, 'start': 12855.945, 'title': 'Dax in excel: data analysis expressions', 'summary': 'Introduces dax in excel as a powerful language for working with relational data and dynamic aggregation functions, with a focus on date and time functions and various types of dax functions.', 'duration': 65.543, 'highlights': ['DAX in Excel is a sophisticated formulas-type language that comes in handy while working with relational data and extracting information via dynamic aggregation functions. DAX in Excel stands for Data Analysis Expressions. DAX functions are completely familiar to the general and default functions that are available in Excel.', 'DAX allows users to perform slightly escalated and advanced and custom calculations upon various data types like character data, date time, time, intelligence functions and many more. There are a variety of functions, such as DAX table valued functions, DAX filter functions, DAX aggregation functions, time intelligence functions.', 'The session focuses on DAX date and time function in Excel, providing practical insights into its usage.']}, {'end': 13374.659, 'start': 12921.488, 'title': 'Importing and calculating data with power pivot and dax functions', 'summary': 'Demonstrates importing data from an excel file into power pivot, using dax date time functions to calculate retirement age for employees, and finding the difference in years between two dates with the datedif function in excel.', 'duration': 453.171, 'highlights': ['Importing data from Excel into Power Pivot and using DAX date time functions to calculate retirement age for employees. The process involves selecting the data option, navigating to the power pivot window, getting external data from an Excel file, adding DAX-related functions, and calculating retirement dates based on employee birth and joining dates.', 'Demonstrating the use of DAX date time functions in Excel for calculating retirement dates based on employee birth and joining dates. The chapter provides a practical demonstration of using DAX date time functions to calculate retirement dates based on employee birth and joining dates, with examples of specific retirement dates for employees.', "Using the datedif function in Excel to find the difference in years between two dates. The process involves selecting the dates, specifying the third parameter as 'Y' to find the difference in years, and demonstrating the practical application of the datedif function to calculate the years an employee has worked with an organization."]}, {'end': 13631.491, 'start': 13374.779, 'title': 'Creating pivot tables in excel', 'summary': 'Explains how to easily create a pivot table in excel, demonstrating the process of selecting and organizing data elements to gain insights, and how to manipulate the pivot table to analyze specific data.', 'duration': 256.712, 'highlights': ['The process of creating a pivot table in Excel involves just a few simple clicks, making it quick and easy to operate.', 'Excel automatically selects the range of data for the pivot table, simplifying the initial setup process.', 'The pivot table allows for easy manipulation of data elements, such as dragging and dropping regions and categories to analyze sales data.', 'The pivot table enables users to filter and analyze specific data, such as finding sales of only furniture in different regions, demonstrating its flexibility and functionality.', 'Users can also increase the level of detail and granularity in the pivot table, including subcategories and quantity of sales, providing enhanced data analysis capabilities.']}, {'end': 14179.044, 'start': 13631.491, 'title': 'Creating pivot tables and pivot charts in excel', 'summary': 'Demonstrates the creation of pivot tables and pivot charts in excel using multiple sales sheets, allowing for consolidated analysis and visualization of sales data by region and category, as well as the creation of interactive pivot charts and the application of pie charts in data visualization.', 'duration': 547.553, 'highlights': ['The chapter demonstrates the creation of pivot tables and pivot charts in Excel using multiple sales sheets The speaker explains the process of using four different sales sheets (west, south, east, and central zones) to create a single pivot table, enabling the consolidation and analysis of sales data by region.', 'Interactive pivot charts are created to visualize sales data by region and category The pivot charts are shown to be interactive, allowing users to select specific regions and categories to visualize sales data, providing a dynamic and customizable data representation.', 'Application of pie charts in data visualization for shares of the automobile industry The speaker demonstrates the creation and customization of a three-dimensional pie chart to visually represent the shares of the automobile industry, including adding data labels and adjusting the presentation of specific data points.']}, {'end': 15011.425, 'start': 14179.044, 'title': 'Excel charts: pie, column, bar, combo, pivot, sparkline', 'summary': 'Covers creating pie, column, bar, combo, pivot, and sparkline charts in excel, with details on chart types, data manipulation, and chart customization, offering a comprehensive guide to chart creation and data visualization in excel.', 'duration': 832.381, 'highlights': ['Creating pivot charts in Excel allows for interactive charts that respond to data changes, enabling users to analyze and visualize data dynamically. Pivot charts in Excel provide interactive charts that respond to changes in data, allowing dynamic analysis and visualization.', 'The chapter explains the process of creating a combo chart in Excel, demonstrating the combination of column and line charts to visualize data effectively. Demonstrates the process of creating a combo chart in Excel, combining column and line charts for effective data visualization.', 'The transcript details the creation of various types of charts in Excel, such as pie, column, bar, combo, pivot, and sparkline charts, offering comprehensive guidance on data visualization in Excel. Provides comprehensive guidance on creating various types of charts in Excel, including pie, column, bar, combo, pivot, and sparkline charts.']}], 'duration': 2155.48, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI12855945.jpg', 'highlights': ['DAX in Excel is a sophisticated formulas-type language for working with relational data and dynamic aggregation functions.', 'DAX functions are familiar to general Excel functions and allow custom calculations on various data types.', 'The session focuses on practical insights into using DAX date and time functions in Excel.', 'Importing data from Excel into Power Pivot and using DAX date time functions to calculate retirement age for employees.', 'Demonstrating the use of DAX date time functions in Excel for calculating retirement dates based on employee birth and joining dates.', 'Creating a pivot table in Excel involves just a few simple clicks, making it quick and easy to operate.', 'The pivot table enables users to filter and analyze specific data, demonstrating its flexibility and functionality.', 'The chapter demonstrates the creation of pivot tables and pivot charts in Excel using multiple sales sheets.', 'Interactive pivot charts are created to visualize sales data by region and category.', 'Creating pivot charts in Excel allows for interactive charts that respond to data changes, enabling dynamic analysis and visualization.', 'The chapter explains the process of creating a combo chart in Excel, combining column and line charts for effective data visualization.', 'Provides comprehensive guidance on creating various types of charts in Excel, including pie, column, bar, combo, pivot, and sparkline charts.']}, {'end': 17506.91, 'segs': [{'end': 15073.069, 'src': 'embed', 'start': 15037.028, 'weight': 0, 'content': [{'end': 15039.55, 'text': "Copy and let's paste it over here.", 'start': 15037.028, 'duration': 2.522}, {'end': 15041.271, 'text': 'So there you go.', 'start': 15040.651, 'duration': 0.62}, {'end': 15043.793, 'text': 'We have the extra row here.', 'start': 15041.631, 'duration': 2.162}, {'end': 15047.235, 'text': 'So yeah, now we have added the miscellaneous row.', 'start': 15044.373, 'duration': 2.862}, {'end': 15052.518, 'text': 'And right now we have the sparkline chart for only four columns or four rows.', 'start': 15047.615, 'duration': 4.903}, {'end': 15055.52, 'text': 'That is furniture, technology, home appliances and office needs.', 'start': 15052.538, 'duration': 2.982}, {'end': 15057.261, 'text': 'And miscellaneous is missing.', 'start': 15056.1, 'duration': 1.161}, {'end': 15059.405, 'text': 'Now to add that as well.', 'start': 15057.745, 'duration': 1.66}, {'end': 15073.069, 'text': "let's select sparkline and inside sparkline just select edit group, location and data and now let's select the entire data and now select ok.", 'start': 15059.405, 'duration': 13.664}], 'summary': 'A sparkline chart is being edited to include a miscellaneous row, completing data for furniture, technology, home appliances, and office needs.', 'duration': 36.041, 'max_score': 15037.028, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI15037028.jpg'}, {'end': 15242.194, 'src': 'embed', 'start': 15211.319, 'weight': 1, 'content': [{'end': 15216.342, 'text': "Using this data set, we'll understand how VLOOKUP performs an exact and approximate match.", 'start': 15211.319, 'duration': 5.023}, {'end': 15219.124, 'text': "We'll see how VLOOKUP is case insensitive.", 'start': 15216.863, 'duration': 2.261}, {'end': 15222.346, 'text': "Then we'll perform a wildcard character search.", 'start': 15219.905, 'duration': 2.441}, {'end': 15226.189, 'text': "We'll understand how to handle errors when a match is not found.", 'start': 15223.087, 'duration': 3.102}, {'end': 15230.083, 'text': 'And finally, you will look at two-way lookups.', 'start': 15226.749, 'duration': 3.334}, {'end': 15236.108, 'text': 'Here is a small example of how VLOOKUP works.', 'start': 15233.506, 'duration': 2.602}, {'end': 15242.194, 'text': 'So we want to find the unit sold by Morgan using the data set on the left.', 'start': 15237.289, 'duration': 4.905}], 'summary': 'Analyzing vlookup for exact, approximate match, wildcard search, error handling, and two-way lookups.', 'duration': 30.875, 'max_score': 15211.319, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI15211319.jpg'}, {'end': 16560.371, 'src': 'embed', 'start': 16525.486, 'weight': 3, 'content': [{'end': 16526.307, 'text': 'the range lookup is 0.', 'start': 16525.486, 'duration': 0.821}, {'end': 16529.389, 'text': 'if I close the bracket, hit enter.', 'start': 16526.307, 'duration': 3.082}, {'end': 16531.451, 'text': 'you see, we have the same value.', 'start': 16529.389, 'duration': 2.062}, {'end': 16535.473, 'text': 'so this is another method or another way of using VLOOKUPS.', 'start': 16531.451, 'duration': 4.022}, {'end': 16541.195, 'text': "Okay, now moving ahead, let's explore another feature of VLOOKUP.", 'start': 16537.412, 'duration': 3.783}, {'end': 16545.958, 'text': 'So if the VLOOKUP function cannot find a match, it returns a has na error.', 'start': 16541.776, 'duration': 4.182}, {'end': 16552.144, 'text': 'Now if you want, you can use the if na function to replace the has na error with a friendly message.', 'start': 16546.319, 'duration': 5.825}, {'end': 16560.371, 'text': 'So suppose you want to find the region in which an order was placed on 10th of May 2018.', 'start': 16552.743, 'duration': 7.628}], 'summary': 'Demonstrates use of vlookup and handling errors in excel.', 'duration': 34.885, 'max_score': 16525.486, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI16525486.jpg'}], 'start': 15012.326, 'title': 'Excel lookup functions', 'summary': 'Explains adding and updating data for sparkline charts and provides tutorials on vlookup function with syntax, parameters, demonstrations, and examples including exact and approximate matches, case-insensitive searches, wildcard character searches, handling errors, and two-way lookups using quantifiable data.', 'chapters': [{'end': 15104.575, 'start': 15012.326, 'title': 'Adding and updating data for sparkline charts', 'summary': 'Explains adding new data and updating existing data for sparkline charts, including adding a new row and ensuring all data is reflected in the charts.', 'duration': 92.249, 'highlights': ["Adding a new row for 'miscellaneous' data and ensuring it is reflected in sparkline charts.", 'Updating sparkline charts to include all five rows of data: furniture, technology, home appliances, office needs, and miscellaneous.']}, {'end': 15595.579, 'start': 15105.295, 'title': 'Excel vlookup function tutorial', 'summary': 'Explains the vlookup function in excel, its syntax with four parameters, and provides demonstrations on performing exact and approximate matches, case-insensitive searches, wildcard character searches, handling errors, and two-way lookups using a sample sales dataset.', 'duration': 490.284, 'highlights': ['The VLOOKUP function in Excel allows users to search for specific information in a spreadsheet. VLOOKUP enables searching for specific values in a column and retrieving corresponding values from a different column, utilizing vertically aligned tables.', 'The syntax of the VLOOKUP function involves four parameters: lookup value, table array, column index number, and range lookup. The VLOOKUP function syntax includes four parameters: lookup value, table array, column index number, and an optional range lookup parameter, which determines the type of match (exact or approximate).', 'The demonstration includes scenarios of performing exact and approximate matches, case-insensitive searches, wildcard character searches, handling errors, and two-way lookups using a sample sales dataset. The demonstration covers performing exact and approximate matches, case-insensitive searches, wildcard character searches, handling errors, and two-way lookups using a sample sales dataset.']}, {'end': 16525.486, 'start': 15595.579, 'title': 'Vlookup in excel', 'summary': 'Explains the usage of vlookup in excel, demonstrating methods for exact and approximate matches, the directional limitation, first match retrieval, case insensitivity, and wildcard support.', 'duration': 929.907, 'highlights': ['VLOOKUP used to find exact match in Excel Demonstrated the use of VLOOKUP to find an exact match in Excel, returning the exact result using specified lookup value, table array, column index number, and range lookup.', 'VLOOKUP for approximate match in Excel Explained the process of using VLOOKUP for an approximate match in Excel, including the requirement for the lookup value column to be sorted in ascending order, and the method for obtaining the item and unit cost for the approximate match.', 'VLOOKUP directional limitation in Excel Highlighted the directional limitation of VLOOKUP in Excel, emphasizing that the lookup value must be on the left and the value to be found must be on the right, leading to an error if this condition is not met.', 'VLOOKUP for first match retrieval in Excel Demonstrated the behavior of VLOOKUP to only return the first match in Excel, providing examples of retrieving units sold and unit cost for specific representatives, showcasing how VLOOKUP disregards subsequent matches.', 'Case insensitivity of VLOOKUP in Excel Illustrated the case insensitivity of VLOOKUP in Excel, where uppercase and lowercase representations of the same lookup value resulted in the same retrieval, showcasing that VLOOKUP is not case sensitive.', 'Wildcard support with VLOOKUP in Excel Explained the usage of wildcard characters with VLOOKUP in Excel, providing an example of performing a partial match on a lookup value using the wildcard feature, demonstrating the retrieval of values from a table based on partial lookup values.']}, {'end': 17035.645, 'start': 16525.486, 'title': 'Vlookup function and two-way lookup', 'summary': 'Demonstrates the usage of vlookup function including handling errors and two-way lookup using the match function, showcasing examples with quantifiable data such as specific lookup values and their corresponding results.', 'duration': 510.159, 'highlights': ["The VLOOKUP function can be used to handle errors by replacing the #N/A error with a friendly message, such as returning 'invalid date' if the lookup value is not found, demonstrated with the specific case of finding the region for an order placed on 10th of May 2015. Demonstrates the usage of the IFNA function to handle errors in VLOOKUP by replacing #N/A with a specific message like 'invalid date' when the lookup value is not found, exemplified with the scenario of finding the region for an order placed on 10th of May 2015.", 'The chapter also showcases the application of the MATCH function within the VLOOKUP formula to create a dynamic column index for two-way lookup, demonstrated with the specific example of finding the unit cost for a representative and the total sales for a specific region and representative. Illustrates the utilization of the MATCH function within the VLOOKUP formula to create a dynamic column index for two-way lookup, evidenced with examples of finding the unit cost for a representative and the total sales for a specific region and representative.', 'The demonstration covers various aspects of VLOOKUP function, including handling errors, two-way lookup, and the application of specific scenarios such as finding unit cost for a representative and total sales for a region and representative. Encompasses a comprehensive understanding of the VLOOKUP function, encompassing error handling, two-way lookup, and practical scenarios like finding unit cost for a representative and total sales for a region and representative.']}, {'end': 17506.91, 'start': 17035.645, 'title': 'Using hlookup and xlookup in excel', 'summary': 'Covers the usage of hlookup and xlookup in excel to perform horizontal lookups, with demonstrations of finding employee ids, salaries, and updating job profiles, and calculating bonuses based on salaries.', 'duration': 471.265, 'highlights': ['Demonstration of using HLOOKUP to find employee IDs and salaries based on employee names in Excel. The chapter provides a detailed demonstration of using HLOOKUP to find employee IDs and salaries based on employee names, showcasing the practical implementation and outcomes.', 'Explanation and demonstration of using XLOOKUP to update job profiles and find current departments in Excel. The chapter explains and demonstrates using XLOOKUP to update job profiles and find current departments, with examples of practical application and outcomes for various employees.', 'Practical application of XLOOKUP to calculate bonuses based on employee salaries in Excel. The chapter showcases the practical application of XLOOKUP to calculate bonuses based on employee salaries, including examples of defining salary ranges and corresponding bonus percentages.']}], 'duration': 2494.584, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI15012326.jpg', 'highlights': ["Demonstrates adding a new row for 'miscellaneous' data and updating sparkline charts.", 'Explains VLOOKUP function syntax and parameters for exact and approximate matches.', 'Illustrates using VLOOKUP for exact and approximate matches, case-insensitive searches, and wildcard character searches.', 'Demonstrates handling errors in VLOOKUP using the IFNA function and utilizing the MATCH function for two-way lookup.', 'Provides detailed demonstrations of using HLOOKUP and XLOOKUP for various practical applications.']}, {'end': 18672.271, 'segs': [{'end': 17587.442, 'src': 'embed', 'start': 17554.625, 'weight': 0, 'content': [{'end': 17561.248, 'text': 'and you were supposed to combine all the data from all the four different zones into one single sheet.', 'start': 17554.625, 'duration': 6.623}, {'end': 17563.769, 'text': 'it would consume a lot of time, right?', 'start': 17561.248, 'duration': 2.521}, {'end': 17574.373, 'text': 'so recently microsoft has released some latest updates based on arrays and datas, so one of those is vstack function in excel.', 'start': 17563.769, 'duration': 10.604}, {'end': 17575.994, 'text': 'so how to use it?', 'start': 17574.373, 'duration': 1.621}, {'end': 17577.774, 'text': 'since it is the latest function,', 'start': 17575.994, 'duration': 1.78}, {'end': 17587.442, 'text': 'i recommend you to go to the file menu and in the file menu you can navigate to account and in the account can see the option of update options.', 'start': 17577.774, 'duration': 9.668}], 'summary': 'Microsoft released vstack function in excel for combining data from four zones, saving time. to use it, navigate to file menu > account > update options.', 'duration': 32.817, 'max_score': 17554.625, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI17554625.jpg'}, {'end': 17626.982, 'src': 'embed', 'start': 17600.525, 'weight': 1, 'content': [{'end': 17605.346, 'text': 'etc. now we are going to implement the vstack function in excel.', 'start': 17600.525, 'duration': 4.821}, {'end': 17606.886, 'text': 'it is completely simple.', 'start': 17605.346, 'duration': 1.54}, {'end': 17612.988, 'text': 'all you need to do is equals to stack and then select the data.', 'start': 17606.886, 'duration': 6.102}, {'end': 17619.296, 'text': 'remember to select the data from column a and cell 2 or the row 2.', 'start': 17612.988, 'duration': 6.308}, {'end': 17626.982, 'text': 'we are not selecting the employment and employee id, we are just selecting the data now, separate them by comma, then again this one here,', 'start': 17619.296, 'duration': 7.686}], 'summary': 'Implementing vstack function in excel to stack data from column a and cell 2, excluding employment and employee id.', 'duration': 26.457, 'max_score': 17600.525, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI17600525.jpg'}, {'end': 17681.045, 'src': 'embed', 'start': 17652.103, 'weight': 2, 'content': [{'end': 17657.847, 'text': 'Now, hyperlink is something which is clickable and when you click on it, it will redirect you to a different web page,', 'start': 17652.103, 'duration': 5.744}, {'end': 17661.669, 'text': 'to a different worksheet or a different location in your local system.', 'start': 17657.847, 'duration': 3.822}, {'end': 17665.872, 'text': 'So there are a variety of options and we will explore all of them using Excel.', 'start': 17662.109, 'duration': 3.763}, {'end': 17669.116, 'text': 'now. creating a hyperlink is really simple.', 'start': 17667.034, 'duration': 2.082}, {'end': 17670.457, 'text': 'we have multiple ways.', 'start': 17669.116, 'duration': 1.341}, {'end': 17676.121, 'text': "now let's write some text on the excel worksheet.", 'start': 17670.457, 'duration': 5.664}, {'end': 17681.045, 'text': "i'll write simply long now.", 'start': 17676.121, 'duration': 4.924}], 'summary': 'The transcript explores creating hyperlinks in excel, offering multiple options for redirection and emphasizing simplicity.', 'duration': 28.942, 'max_score': 17652.103, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI17652103.jpg'}, {'end': 18446.449, 'src': 'embed', 'start': 18413.456, 'weight': 3, 'content': [{'end': 18419.559, 'text': 'so next thing is how you actually activate the macros in your Excel.', 'start': 18413.456, 'duration': 6.103}, {'end': 18430.345, 'text': "it's a simple tab that you need to add in the ribbon section, which I'll be showing you across, and this will enable the macros on your Excel.", 'start': 18419.559, 'duration': 10.786}, {'end': 18439.51, 'text': 'so you go to file, go to options and then you need to customize the ribbon.', 'start': 18430.345, 'duration': 9.165}, {'end': 18446.449, 'text': 'click customize ribbon and then there is a developer tab which you see here it should be activated.', 'start': 18439.51, 'duration': 6.939}], 'summary': 'Activate macros in excel by adding a developer tab in the ribbon section, enabling macro usage.', 'duration': 32.993, 'max_score': 18413.456, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI18413456.jpg'}], 'start': 17506.95, 'title': 'Excel functions and macros', 'summary': 'Covers vstack function for data consolidation, hyperlinks for navigation and automation, and macro recording for task automation, potentially saving significant time and effort in handling large volumes of data and automating repetitive tasks for daily use.', 'chapters': [{'end': 17600.525, 'start': 17506.95, 'title': 'Vstack function in excel', 'summary': 'Introduces the vstack function in excel, which provides a practical solution for combining data from different zones into one sheet, potentially saving significant time and effort in handling large volumes of data.', 'duration': 93.575, 'highlights': ['The vstack function in Excel allows for combining data from different zones into one sheet, potentially saving significant time and effort in handling large volumes of data.', 'Microsoft has released some latest updates based on arrays and datas, including the vstack function in Excel, representing the latest function in the software.', 'To access the vstack function and other latest functions in Excel, users are recommended to update their Microsoft Office to the latest version through the file menu.']}, {'end': 18115.846, 'start': 17600.525, 'title': 'Excel vstack and hyperlinks', 'summary': 'Explains how to use the vstack function in excel to stack and select data, and also demonstrates creating hyperlinks in excel, including linking to websites, existing files, and within the document.', 'duration': 515.321, 'highlights': ['The chapter explains how to use the VStack function in Excel to stack and select data. It provides step-by-step instructions for using the VStack function, including selecting specific data from column A and cell 2, and separating the data with commas to create a data set.', 'Creating hyperlinks in Excel, including linking to websites and existing files, is demonstrated. The process of creating hyperlinks in Excel is explained, covering methods such as using existing web page links, creating hyperlinks to existing PDF files, and editing the display text for hyperlinks.', 'Demonstrates creating hyperlinks within the document to navigate to specific cells in different sheets. The chapter illustrates how to use hyperlinks to navigate to specific cells within the same document, such as linking to different sheets and specific cell addresses for quick navigation.']}, {'end': 18412.918, 'start': 18116.507, 'title': 'Excel hyperlink options', 'summary': 'Demonstrates how to use hyperlinks in excel to create cheat sheets, track updates in a text document, and compose emails with a single click, covering existing file or web page, creating a new document, and including email options.', 'duration': 296.411, 'highlights': ['The chapter demonstrates how to use hyperlinks in Excel to create cheat sheets and track updates in a text document. This highlights the key points of using hyperlinks in Excel to create cheat sheets and track updates, providing practical examples of the process.', 'The chapter explains how to compose emails with a single click using hyperlinks in Excel. This demonstrates the process of using hyperlinks in Excel to compose emails with a single click, showcasing the ease and efficiency of the method.', 'The chapter covers the options of placing hyperlinks in Excel, including existing file or web page, creating a new document, and including email options. This highlights the comprehensive coverage of hyperlink options in Excel, including existing file or web page, creating a new document, and including email options for efficient use.']}, {'end': 18672.271, 'start': 18413.456, 'title': 'Activating and recording macros in excel', 'summary': 'Explains how to activate the developer tab in excel to enable macros, and demonstrates the process of recording a macro to automate repetitive tasks, emphasizing the removal of obsolete data and the streamlined extraction of specific information for daily use.', 'duration': 258.815, 'highlights': ['The process of activating the developer tab in Excel to enable macros is demonstrated. The chapter explains the steps to activate the developer tab in Excel to enable macros, emphasizing the importance of adding this tab to the ribbon section and customizing the ribbon to include the developer tab.', 'The method of recording a macro in Excel is explained, emphasizing the process of automating repetitive tasks and removing obsolete data. The process of recording a macro in Excel is explained, highlighting the automation of repetitive tasks and the removal of obsolete data, with a focus on streamlining specific information extraction for daily use.', 'The demonstration of recording a macro in Excel to automate the extraction of specific information for daily use is outlined. The chapter details the demonstration of recording a macro in Excel to automate the extraction of specific information for daily use, showcasing the removal of obsolete data and the streamlined extraction of crucial information.']}], 'duration': 1165.321, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI17506950.jpg', 'highlights': ['The vstack function in Excel allows for combining data from different zones into one sheet, potentially saving significant time and effort in handling large volumes of data.', 'The chapter explains how to use the VStack function in Excel to stack and select data. It provides step-by-step instructions for using the VStack function, including selecting specific data from column A and cell 2, and separating the data with commas to create a data set.', 'The chapter demonstrates how to use hyperlinks in Excel to create cheat sheets and track updates in a text document. This highlights the key points of using hyperlinks in Excel to create cheat sheets and track updates, providing practical examples of the process.', 'The process of activating the developer tab in Excel to enable macros is demonstrated. The chapter explains the steps to activate the developer tab in Excel to enable macros, emphasizing the importance of adding this tab to the ribbon section and customizing the ribbon to include the developer tab.']}, {'end': 19458.298, 'segs': [{'end': 18765.948, 'src': 'embed', 'start': 18739.642, 'weight': 1, 'content': [{'end': 18743.406, 'text': 'so I just click run and it does the rest for me.', 'start': 18739.642, 'duration': 3.764}, {'end': 18747.65, 'text': 'I have my report ready and is in proper format.', 'start': 18743.406, 'duration': 4.244}, {'end': 18750.958, 'text': 'that is required and it can be sent across.', 'start': 18747.65, 'duration': 3.308}, {'end': 18760.525, 'text': 'so here, as we see, it is done in a single click, by running a simple macro, continuing our work on macro.', 'start': 18750.958, 'duration': 9.567}, {'end': 18765.948, 'text': 'we can simply do this by a single click of a button, by adding a button.', 'start': 18760.525, 'duration': 5.423}], 'summary': 'Report ready in proper format with a single click using a simple macro.', 'duration': 26.306, 'max_score': 18739.642, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI18739642.jpg'}, {'end': 18829.452, 'src': 'embed', 'start': 18796.969, 'weight': 2, 'content': [{'end': 18804.673, 'text': 'And now if you see, if I just click this button, it will run the macro and format our sheet.', 'start': 18796.969, 'duration': 7.704}, {'end': 18811, 'text': 'So this is how we run the macros in a single click of a button by adding the button.', 'start': 18805.598, 'duration': 5.402}, {'end': 18823.984, 'text': 'There is another alternative way to go to the macros instead of the developer tab and you go to the view button and then go to macros at the end.', 'start': 18812.36, 'duration': 11.624}, {'end': 18829.452, 'text': 'You can view macros, you can record, and you can edit your macro.', 'start': 18825.27, 'duration': 4.182}], 'summary': 'Run macros with a single click, or access them through the view button. can view, record, and edit macros.', 'duration': 32.483, 'max_score': 18796.969, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI18796969.jpg'}, {'end': 19020.366, 'src': 'embed', 'start': 18957.631, 'weight': 0, 'content': [{'end': 18966.473, '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': 18957.631, 'duration': 8.842}, {'end': 18974.921, 'text': 'We have seen how we use macros, how it simplifies our tedious daily tasks by running a simple macro.', 'start': 18966.933, 'duration': 7.988}, {'end': 18991.322, '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': 18975.561, 'duration': 15.761}, {'end': 18993.742, 'text': 'so why VBA?', 'start': 18991.322, 'duration': 2.42}, {'end': 19001.105, 'text': 'there are many advantages of using VBA and why we prefer VBA in macros for excel.', 'start': 18993.742, 'duration': 7.363}, {'end': 19004.966, 'text': 'so basically, it is a very simple language.', 'start': 19001.105, 'duration': 3.861}, {'end': 19008.847, 'text': 'it saves time in performing tedious tasks.', 'start': 19004.966, 'duration': 3.881}, {'end': 19011.808, 'text': 'you have number of tasks that you do every day,', 'start': 19008.847, 'duration': 2.961}, {'end': 19020.366, 'text': 'as we have seen in the previous section how we can simplify your daily task by just running a simple macro,', 'start': 19011.808, 'duration': 8.558}], 'summary': 'Vba in excel simplifies tasks, saves time, and eases automation.', 'duration': 62.735, 'max_score': 18957.631, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI18957631.jpg'}, {'end': 19139.296, 'src': 'embed', 'start': 19107.959, 'weight': 5, 'content': [{'end': 19112.783, 'text': 'this is basically the vba editor interface where you can see the.', 'start': 19107.959, 'duration': 4.824}, {'end': 19121.328, 'text': 'uh, this is the toolbar section where it shows all the options where you undo or save or file.', 'start': 19112.783, 'duration': 8.545}, {'end': 19125.111, 'text': 'we will see those details in the next section.', 'start': 19121.328, 'duration': 3.783}, {'end': 19129.134, 'text': 'this is basically the toolbar section of the vba editor interface.', 'start': 19125.111, 'duration': 4.023}, {'end': 19130.654, 'text': 'The next thing is the menu bar.', 'start': 19129.474, 'duration': 1.18}, {'end': 19139.296, 'text': 'Menu bar as we all know it is simply shows you the menu wherein you have all the options like file, edit, view, insert.', 'start': 19130.694, 'duration': 8.602}], 'summary': 'Overview of vba editor interface including toolbar and menu bar options.', 'duration': 31.337, 'max_score': 19107.959, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI19107959.jpg'}, {'end': 19412.275, 'src': 'embed', 'start': 19368.826, 'weight': 6, 'content': [{'end': 19370.707, 'text': 'So as we see, the code is empty.', 'start': 19368.826, 'duration': 1.881}, {'end': 19375.17, 'text': "here we'll try to record a macro and see how the code develops.", 'start': 19370.707, 'duration': 4.463}, {'end': 19389.199, 'text': 'Okay, so I just go here and click record macro and I save it as a format and columns.', 'start': 19375.35, 'duration': 13.849}, {'end': 19397.764, 'text': 'and I just save it, click OK and it has started recording.', 'start': 19393.621, 'duration': 4.143}, {'end': 19405.89, 'text': "so now what I'll do is make it simple and I just delete some columns here.", 'start': 19397.764, 'duration': 8.126}, {'end': 19408.972, 'text': 'okay, I do not need this.', 'start': 19405.89, 'duration': 3.082}, {'end': 19409.673, 'text': 'I delete this.', 'start': 19408.972, 'duration': 0.701}, {'end': 19411.474, 'text': 'is there a wait?', 'start': 19409.673, 'duration': 1.801}, {'end': 19412.275, 'text': 'I want this.', 'start': 19411.474, 'duration': 0.801}], 'summary': 'Recording a macro to format and delete columns in code.', 'duration': 43.449, 'max_score': 19368.826, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI19368826.jpg'}], 'start': 18672.271, 'title': 'Automating excel tasks with vba', 'summary': 'Covers automating daily tasks in excel using vba macros to execute multiple steps in a single click, improving efficiency and productivity. it also introduces vba for excel macros, highlighting time-saving, task simplification, and excel functionality improvement, along with instructions on using the vba editor interface and recording macros.', 'chapters': [{'end': 18924.148, 'start': 18672.271, 'title': 'Automating tasks with macros', 'summary': 'Discusses the process of automating daily tasks in excel using macros, enabling users to execute multiple steps in a single click, ultimately improving efficiency and productivity.', 'duration': 251.877, 'highlights': ['By using macros, users can automate daily tasks in Excel, reducing the time and effort required for repetitive actions. The process of automating daily tasks in Excel using macros allows users to execute multiple steps in a single click, improving efficiency and productivity.', 'Macros enable users to format reports and execute tasks with a single click, saving time and ensuring proper formatting. The use of macros allows users to format reports and execute tasks with a single click, ultimately saving time and ensuring proper formatting.', 'Users can add buttons to execute macros with a single click, simplifying the process and enhancing user experience. Adding buttons to execute macros with a single click simplifies the process and enhances the user experience, improving overall efficiency.']}, {'end': 19458.298, 'start': 18924.148, 'title': 'Vba for excel macros', 'summary': 'Introduces vba for excel macros, highlighting its advantages such as time-saving, simplifying tasks, and improving excel functionality, and explains the vba editor interface. it also demonstrates how to open the visual basic application and record a macro to perform tasks.', 'duration': 534.15, 'highlights': ['VBA advantages include time-saving and simplifying tasks VBA is praised for its time-saving capabilities and simplifying tedious daily tasks through the use of macros.', 'VBA improves Excel functionality VBA allows users to make Excel behave the way they want, improving the functionality and ease of performing daily tasks.', 'Explanation of VBA editor interface The transcript details the VBA editor interface, including the toolbar section, menu bar, VBA code window, project explorer, properties window, immediate window, and watch window.', 'Demonstration of recording a macro in VBA The transcript demonstrates the process of recording a macro in VBA to perform tasks such as deleting columns, changing font, and simplifying data.']}], 'duration': 786.027, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI18672271.jpg', 'highlights': ['By using macros, users can automate daily tasks in Excel, reducing the time and effort required for repetitive actions.', 'Macros enable users to format reports and execute tasks with a single click, saving time and ensuring proper formatting.', 'Users can add buttons to execute macros with a single click, simplifying the process and enhancing user experience.', 'VBA is praised for its time-saving capabilities and simplifying tedious daily tasks through the use of macros.', 'VBA allows users to make Excel behave the way they want, improving the functionality and ease of performing daily tasks.', 'The transcript details the VBA editor interface, including the toolbar section, menu bar, VBA code window, project explorer, properties window, immediate window, and watch window.', 'The transcript demonstrates the process of recording a macro in VBA to perform tasks such as deleting columns, changing font, and simplifying data.']}, {'end': 21982.337, 'segs': [{'end': 19611.617, 'src': 'embed', 'start': 19580.144, 'weight': 0, 'content': [{'end': 19584.546, 'text': 'and whatever is there should be here, and this is the name of the out of the macro.', 'start': 19580.144, 'duration': 4.402}, {'end': 19590.168, 'text': "Now I'll explain it further about the dim statement why is it used and what does it use for.", 'start': 19584.986, 'duration': 5.182}, {'end': 19599.512, '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': 19590.928, 'duration': 8.584}, {'end': 19611.617, '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': 19599.612, 'duration': 12.005}], 'summary': "Using 'dim' keyword to declare variables in vba for specifying a variable called 'x' as a number or an integer.", 'duration': 31.473, 'max_score': 19580.144, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI19580144.jpg'}, {'end': 20606.371, 'src': 'embed', 'start': 20562.34, 'weight': 3, 'content': [{'end': 20572.129, 'text': 'So it gives the value of C1 through C4 and D1 through D4 as 230 as we specified it in the code.', 'start': 20562.34, 'duration': 9.789}, {'end': 20583.099, 'text': 'So this is one more example where you can assign values to the range of cells or cells with the VBA.', 'start': 20572.909, 'duration': 10.19}, {'end': 20591.084, 'text': 'One more thing, I can assign a value to a particular cell by specifying these cells.', 'start': 20585.521, 'duration': 5.563}, {'end': 20592.204, 'text': "It's very simple.", 'start': 20591.184, 'duration': 1.02}, {'end': 20602.069, 'text': 'Just specify the cells like 06, 7.', 'start': 20592.725, 'duration': 9.344}, {'end': 20604.05, 'text': 'And I close the parenthesis.', 'start': 20602.069, 'duration': 1.981}, {'end': 20606.371, 'text': 'I assign a value to it.', 'start': 20605.03, 'duration': 1.341}], 'summary': 'Vba code assigns value 230 to cells c1-c4 and d1-d4, with simple syntax.', 'duration': 44.031, 'max_score': 20562.34, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI20562340.jpg'}, {'end': 21291.654, 'src': 'embed', 'start': 21264.109, 'weight': 1, 'content': [{'end': 21273.019, 'text': 'if statement is one of the most widely used statement, and whoever is in programming are very much familiar with this function.', 'start': 21264.109, 'duration': 8.91}, {'end': 21276.523, 'text': 'so what it does is we specify a condition.', 'start': 21273.019, 'duration': 3.504}, {'end': 21280.748, 'text': 'if a condition is met, then the output output should be such and such.', 'start': 21276.523, 'duration': 4.225}, {'end': 21291.654, 'text': 'we can specify the condition and it will check whether that condition is there and if it is specified, if it is achieved,', 'start': 21280.748, 'duration': 10.906}], 'summary': "The 'if' statement is widely used in programming to specify conditions and control output.", 'duration': 27.545, 'max_score': 21264.109, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI21264109.jpg'}, {'end': 21882.167, 'src': 'embed', 'start': 21851.029, 'weight': 2, 'content': [{'end': 21856.111, 'text': 'And we are defining it with the number of values and the variable as x.', 'start': 21851.029, 'duration': 5.082}, {'end': 21857.711, 'text': 'And the number of values is going to be 24.', 'start': 21856.111, 'duration': 1.6}, {'end': 21863.163, 'text': 'Okay This is one basic example of a per-loop statement.', 'start': 21857.711, 'duration': 5.452}, {'end': 21876.846, 'text': "now let's do some more addition to this example by, you know, executing another set of for loop and giving the output,", 'start': 21865.122, 'duration': 11.724}, {'end': 21881.287, 'text': 'and we put the output to be the color coded once in the column B.', 'start': 21876.846, 'duration': 4.441}, {'end': 21882.167, 'text': 'so how we do it?', 'start': 21881.287, 'duration': 0.88}], 'summary': 'Using 24 values, 2 for-loops are executed to generate color-coded output in column b.', 'duration': 31.138, 'max_score': 21851.029, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI21851029.jpg'}], 'start': 19458.298, 'title': 'Vba code fundamentals and practical applications', 'summary': 'Covers vba code basics, functions, dim keyword usage, cell value assignment, excel vba selection, counting, if statement, and for loop statement. it includes examples and practical applications, such as assigning values, selecting ranges, and using if-else statements in a programming context.', 'chapters': [{'end': 19692.319, 'start': 19458.298, 'title': 'Vba code basics and functions', 'summary': "Introduces vba code basics and functions, covering the structure of a vba code, the use of the 'dim' keyword to declare variables, and an example of assigning a value using the 'dim' keyword, demonstrating how to assign the value of a cell to be 500.", 'duration': 234.021, 'highlights': ["The chapter introduces VBA code basics and functions It covers the structure of a VBA code and the use of the 'dim' keyword to declare variables.", "An example of assigning a value using the 'dim' keyword The example demonstrates how to assign the value of a cell to be 500 using the 'dim' keyword.", "The 'dim' keyword is used to declare variables in VBA It is explained that 'dim' is the short form of 'dimension' and is used to declare variables in VBA."]}, {'end': 19998.525, 'start': 19692.319, 'title': 'Using dim keyword in vba', 'summary': 'Covers the usage of the dim keyword to assign string, double, date values, and demonstrate their usage in vba code, including setting variables, assigning values, and displaying pop-up messages.', 'duration': 306.206, 'highlights': ['The chapter covers the usage of the dim keyword to assign string, double, date values, and demonstrate their usage in VBA code, including setting variables, assigning values, and displaying pop-up messages.', 'The dim keyword is used to set variables as double, which can accommodate larger numbers, and it uses more space.', 'The usage of the dim keyword to store date values and text (string), which can contain numbers and perform calculations, is demonstrated in the VBA code.', 'A demonstration of using the dim keyword to assign a string value (name of a country) to a cell in VBA code is presented, showcasing the practical usage of the dim keyword.', "The chapter also includes showcasing how to display a pop-up message box using VBA code to show the value of a variable, such as 'the value of a is 250.'"]}, {'end': 20784.283, 'start': 19998.525, 'title': 'Vba code for cell value assignment', 'summary': 'Discusses vba code examples for assigning values to cells, such as assigning a user input to a selected cell, displaying custom message boxes, and assigning values to ranges of cells, showcasing the simplicity and utility of vba in excel tasks.', 'duration': 785.758, 'highlights': ["VBA code example for assigning user input to a selected cell Demonstrates assigning a user input 'This is a report for vba' to the active cell, showcasing the simplicity and utility of VBA in assigning values.", "VBA code example for displaying custom message boxes Illustrates the use of message boxes to display custom messages such as 'My name is Irfan', showcasing the customization capabilities of VBA in Excel tasks.", 'VBA code example for assigning a value to a particular cell Shows assigning a value of 250 to the cell V5, displaying the simplicity of assigning values to specific cells using VBA.', 'VBA code example for assigning values to ranges of cells Demonstrates assigning values to ranges of cells, such as B1 through B5 and C1 through C4, showcasing the utility of VBA in assigning values to multiple cells at once.', 'VBA code example for selecting a range of cells in a sheet Illustrates the selection of a range of cells and assigning a value to them, showcasing the simplicity of utilizing VBA for such tasks.']}, {'end': 21264.109, 'start': 20784.283, 'title': 'Excel vba selection and counting', 'summary': 'Demonstrates selecting specific ranges in different worksheets, copying and pasting cells, and counting the number of cells, where specific vba commands are used to achieve these tasks, with examples showing the selection of ranges, copying and pasting cells, and counting the number of cells, columns, and rows.', 'duration': 479.826, 'highlights': ['Demonstrating Selection of Ranges in Different Worksheets The speaker demonstrates how to select a specific range in different worksheets using VBA commands, providing an example of selecting range A1 to D5 in sheet 2, showcasing the ability to select cells in different sheets.', 'Copying and Pasting Cells The tutorial explains a simple VBA code to copy and paste cells, with the speaker providing an example of specifying the range to copy, pasting it to another location, and mentioning the ease of executing this task.', 'Counting the Number of Cells, Columns, and Rows The tutorial demonstrates how to count the number of cells, columns, and rows using VBA commands, with the speaker providing examples of setting the range to count and displaying the count using message boxes, showcasing the ability to calculate the number of cells, columns, and rows.']}, {'end': 21654.122, 'start': 21264.109, 'title': 'If statement in programming', 'summary': 'Introduces the concept of the if statement in programming, explaining its syntax and usage. it demonstrates how to use if and if-else statements to assign results based on specified conditions, and provides examples of using these statements in a programming context.', 'duration': 390.013, 'highlights': ['The if statement is widely used and allows specifying a condition, checking if it is met, and specifying the output, making it easy to understand and use.', 'Demonstrates using if and if-else statements to assign pass or fail results based on specified score conditions, providing a practical example of using the if statement in programming.', 'Illustrates the basic syntax of the if statement, specifying conditions and results using variables and ranges, and testing the conditions with different score values.']}, {'end': 21982.337, 'start': 21654.122, 'title': 'For loop statement in vba', 'summary': 'Discusses the for loop statement in vba, which allows executing a statement or a group of statements multiple times, exemplified by filling column a from 1 to 24 using a for loop and color coding column b based on the values, demonstrating the practical application of for loop statements in vba.', 'duration': 328.215, 'highlights': ['The chapter discusses the practical application of for loop statements in VBA, exemplified by filling column A from 1 to 24 using a for loop and color coding column B based on the values, demonstrating the repetitive execution of tasks in VBA.', 'The for loop statement in VBA allows executing a statement or a group of statements multiple times, as demonstrated by filling column A from 1 to 24 using a for loop, showcasing the sequential execution of tasks in VBA.', 'An example of the practical application of for loop statements in VBA is demonstrated by filling column A from 1 to 24 using a for loop and color coding column B based on the values, showcasing the repetitive nature of tasks in VBA.', 'The chapter illustrates the practical application of for loop statements in VBA through an example of filling column A from 1 to 24 using a for loop and color coding column B based on the values, highlighting the repetitive execution of tasks in VBA.']}], 'duration': 2524.039, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI19458298.jpg', 'highlights': ['The chapter covers the usage of the dim keyword to assign string, double, date values, and demonstrate their usage in VBA code, including setting variables, assigning values, and displaying pop-up messages.', 'The if statement is widely used and allows specifying a condition, checking if it is met, and specifying the output, making it easy to understand and use.', 'The chapter discusses the practical application of for loop statements in VBA, exemplified by filling column A from 1 to 24 using a for loop and color coding column B based on the values, demonstrating the repetitive execution of tasks in VBA.', 'VBA code example for assigning values to ranges of cells Demonstrates assigning values to ranges of cells, such as B1 through B5 and C1 through C4, showcasing the utility of VBA in assigning values to multiple cells at once.', 'An example of the practical application of for loop statements in VBA is demonstrated by filling column A from 1 to 24 using a for loop and color coding column B based on the values, showcasing the repetitive nature of tasks in VBA.']}, {'end': 24438.804, 'segs': [{'end': 22036.041, 'src': 'embed', 'start': 22003.174, 'weight': 0, 'content': [{'end': 22007.515, 'text': 'and what to do if you cannot save your data and how can you recover it? Right?', 'start': 22003.174, 'duration': 4.341}, {'end': 22010.376, 'text': 'So this might be a really big hassle today.', 'start': 22007.915, 'duration': 2.461}, {'end': 22013.978, 'text': 'We are exactly going to discuss the same and see how to do it in multiple ways.', 'start': 22010.436, 'duration': 3.542}, {'end': 22018.58, 'text': 'So Excel comes up with an option to automatically save the data.', 'start': 22014.358, 'duration': 4.222}, {'end': 22024.818, 'text': 'Also, it will try to warn you that your data is not being saved, Would you like me to save it or not? Right.', 'start': 22018.94, 'duration': 5.878}, {'end': 22026.078, 'text': "So we'll do the first one.", 'start': 22024.858, 'duration': 1.22}, {'end': 22028.078, 'text': 'So here you have the auto save option.', 'start': 22026.158, 'duration': 1.92}, {'end': 22036.041, 'text': 'You can either say OK to it or you can ignore it in case if you ignore it, if you choose to ignore it or if you choose to manually save it.', 'start': 22028.379, 'duration': 7.662}], 'summary': 'Excel offers auto-save option to prevent data loss.', 'duration': 32.867, 'max_score': 22003.174, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI22003174.jpg'}, {'end': 22213.401, 'src': 'embed', 'start': 22189.26, 'weight': 1, 'content': [{'end': 22195.227, 'text': 'So this is how you can recover the unsaved data in excel using multiple ways.', 'start': 22189.26, 'duration': 5.967}, {'end': 22201.293, 'text': 'first thing, excel will automatically ask you if you have to save the file or not and in case, if you forget to save it,', 'start': 22195.227, 'duration': 6.066}, {'end': 22204.956, 'text': 'you can directly recover it by default by excel.', 'start': 22201.293, 'duration': 3.663}, {'end': 22211.059, 'text': 'it will automatically show that some folder was not saved on the left side of your Excel spreadsheet by default.', 'start': 22204.956, 'duration': 6.103}, {'end': 22213.401, 'text': 'And in case if you missed out on that one,', 'start': 22211.4, 'duration': 2.001}], 'summary': 'Excel offers multiple ways to recover unsaved data, including automatic prompts and default recovery options.', 'duration': 24.141, 'max_score': 22189.26, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI22189260.jpg'}, {'end': 22277.535, 'src': 'embed', 'start': 22251.914, 'weight': 2, 'content': [{'end': 22260.156, 'text': 'The keyboard shortcuts in Excel helps you perform data analysis better and faster, but it would be an advantage if you can remember a few of them.', 'start': 22251.914, 'duration': 8.242}, {'end': 22267.633, 'text': "Now, let's begin by looking at some of the important workbook related shortcuts.", 'start': 22263.672, 'duration': 3.961}, {'end': 22271.654, 'text': 'So we will see how to create a new workbook,', 'start': 22269.073, 'duration': 2.581}, {'end': 22277.535, 'text': "open an existing workbook and save a spreadsheet so that you don't lose any data or calculations that you have done.", 'start': 22271.654, 'duration': 5.881}], 'summary': 'Excel shortcuts improve data analysis speed and accuracy.', 'duration': 25.621, 'max_score': 22251.914, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI22251914.jpg'}, {'end': 22738.685, 'src': 'embed', 'start': 22711.178, 'weight': 3, 'content': [{'end': 22717.98, 'text': 'Now here I will tell you the different shortcuts that can be applied to a cell, such as editing a cell, aligning cell contents,', 'start': 22711.178, 'duration': 6.802}, {'end': 22722.801, 'text': 'adding a border to a cell, adding an outline to all the selected cells, etc.', 'start': 22717.98, 'duration': 4.821}, {'end': 22725.281, 'text': "So let's go ahead and do these operations.", 'start': 22723.441, 'duration': 1.84}, {'end': 22738.685, 'text': "So I'll go to my book 1 and the first shortcut that we'll see is how to edit a cell.", 'start': 22727.682, 'duration': 11.003}], 'summary': 'Demonstrating various cell shortcuts, including editing and aligning cell contents.', 'duration': 27.507, 'max_score': 22711.178, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI22711178.jpg'}, {'end': 23609.043, 'src': 'embed', 'start': 23579.251, 'weight': 4, 'content': [{'end': 23583.993, 'text': "going back now i'll tell you how to apply certain formats.", 'start': 23579.251, 'duration': 4.742}, {'end': 23587.275, 'text': "so let's see how you can apply a currency format.", 'start': 23583.993, 'duration': 3.282}, {'end': 23591.59, 'text': 'so you can see we have some numerical values here.', 'start': 23587.275, 'duration': 4.315}, {'end': 23603.579, 'text': "I'll select this values and if I hit control shift dollar now this numbers have been converted into currency format.", 'start': 23591.59, 'duration': 11.989}, {'end': 23609.043, 'text': 'we have all in the form of rupees.', 'start': 23603.579, 'duration': 5.464}], 'summary': 'Learn to apply currency format to numerical values using control shift dollar, converting them into rupees.', 'duration': 29.792, 'max_score': 23579.251, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI23579251.jpg'}, {'end': 24087.391, 'src': 'embed', 'start': 24059.681, 'weight': 5, 'content': [{'end': 24064.722, 'text': 'In Microsoft Excel, a pivot table is a table of statistics that summarizes your data.', 'start': 24059.681, 'duration': 5.041}, {'end': 24069.973, 'text': 'For example, you have sales data for different regions and for different products.', 'start': 24065.609, 'duration': 4.364}, {'end': 24078.823, 'text': 'Using a pivot table, you can summarize the data by region and find the average sales per region, the maximum and minimum sales per region,', 'start': 24070.914, 'duration': 7.909}, {'end': 24082.847, 'text': 'total sales made for each region and for each category of products, etc.', 'start': 24078.823, 'duration': 4.024}, {'end': 24087.391, 'text': 'It allows you to summarize and show only relevant data in your reports.', 'start': 24083.688, 'duration': 3.703}], 'summary': 'Pivot tables in excel summarize sales data by region and product, finding averages, maximums, minimums, and total sales.', 'duration': 27.71, 'max_score': 24059.681, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI24059681.jpg'}], 'start': 21985.02, 'title': 'Excel data recovery and shortcut keys', 'summary': 'Discusses the importance of auto-saving in excel to prevent data loss and provides solutions for recovering unsaved data. it also highlights the significance of excel shortcut keys, including workbook-related, formatting, and pivot table shortcuts, to enhance work efficiency and productivity.', 'chapters': [{'end': 22024.818, 'start': 21985.02, 'title': 'Excel data recovery and auto-save', 'summary': 'Discusses the importance of auto-saving in excel to prevent data loss, especially in scenarios involving confidential company data, and provides solutions for recovering unsaved data.', 'duration': 39.798, 'highlights': ['Excel has an option to automatically save data, preventing potential data loss, especially for important and confidential files.', 'The chapter highlights the potential issues of not saving data, such as facing a pocket issue or forgetting to save, and emphasizes the importance of addressing this potential hassle.', 'Excel provides warnings when data is not being saved, offering the user the option to save the data, which serves as a helpful reminder and preventive measure against data loss.']}, {'end': 22231.134, 'start': 22024.858, 'title': 'Recovering unsaved data in excel', 'summary': 'Discusses how to recover unsaved data in excel using multiple ways, including automatic prompts and manual recovery options, after forcefully closing the file without saving, demonstrating the process and the options available for recovering unsaved workbooks.', 'duration': 206.276, 'highlights': ["Excel automatically prompts to recover unsaved data by showing a folder on the left side of the spreadsheet and offers manual recovery options through the 'Open' or 'Info' menu. Excel automatically prompts to recover unsaved data by showing a folder on the left side of the spreadsheet and offers manual recovery options through the 'Open' or 'Info' menu, which provides a user-friendly way to retrieve unsaved files.", "Multiple manual recovery options are available, including 'Recover Unsaved Workbooks' in the 'Open' menu and 'Manage Workbook' in the 'Info' menu, providing various ways to retrieve unsaved workbooks related to Excel. Multiple manual recovery options are available, including 'Recover Unsaved Workbooks' in the 'Open' menu and 'Manage Workbook' in the 'Info' menu, providing various ways to retrieve unsaved workbooks related to Excel, giving users flexibility in recovering their unsaved work.", "Excel automatically prompts to recover unsaved data by showing a folder on the left side of the spreadsheet and offers manual recovery options through the 'Open' or 'Info' menu. Excel automatically prompts to recover unsaved data by showing a folder on the left side of the spreadsheet and offers manual recovery options through the 'Open' or 'Info' menu, which provides a user-friendly way to retrieve unsaved files."]}, {'end': 22559.484, 'start': 22235.33, 'title': 'Excel shortcut keys for efficient work', 'summary': 'Highlights the importance of excel shortcut keys, featuring essential workbook-related shortcuts such as creating new workbooks, opening existing workbooks, saving spreadsheets, and toggling between different sheets, with specific shortcut keys and their functions.', 'duration': 324.154, 'highlights': ['The keyboard shortcuts in Excel help perform data analysis better and faster, with the advantage of remembering a few of them.', 'The shortcut to open a new workbook is Ctrl + N, which aids in creating new workbooks efficiently.', 'The shortcut to open an existing workbook is Ctrl + O, facilitating quick access to and opening of existing Excel files.', 'The shortcut to save a workbook or a spreadsheet is Ctrl + S, ensuring efficient and quick saving of work in Excel.', 'The shortcut to close the current workbook is Ctrl + W, providing a convenient way to close the current file and prompting for saving if necessary.', 'The shortcut to move to the next sheet is Ctrl + Page Down, allowing seamless navigation between different sheets within a workbook.', 'The shortcut to move to the previous sheet is Ctrl + Page Up, enabling swift movement to the preceding sheet for enhanced productivity.']}, {'end': 23579.251, 'start': 22559.484, 'title': 'Excel shortcuts and functions', 'summary': 'Covers excel shortcut keys including moving between tables, switching tabs, editing cells, copy-pasting cells, formatting font values, adding borders, selecting cells, applying filters, inserting current date and time, adding hyperlinks, and comments.', 'duration': 1019.767, 'highlights': ['The chapter covers Excel shortcut keys including moving between tables, switching tabs, editing cells, copy-pasting cells, formatting font values, adding borders, selecting cells, applying filters, inserting current date and time, adding hyperlinks, and comments. The summary of the entire transcript.', 'The shortcut for switching between different tabs in Excel is Alt + [Tab Letter], such as Alt A for the data tab, Alt W for the view tab, and Alt M for the formula tab. Provides specific shortcut keys for switching between different tabs in Excel.', "The shortcut for editing a cell is F2, and for copy-pasting cells, it's CTRL C and CTRL V. Formatting shortcuts include CTRL B for bold, CTRL I for italicize, and CTRL U for underlining. Lists specific shortcut keys for editing cells and formatting font values.", 'Shortcuts for adding borders to cells include ALT H H for filling colors, ALT H B for adding a border, and CTRL + SHIFT + _ for removing an outline border. Details the specific shortcuts for adding borders and removing outline borders.', 'Shortcuts for selecting cells and applying filters include CTRL SHIFT right arrow for cells on the right, CTRL SHIFT left arrow for cells on the left, CTRL SHIFT down arrow for the entire column, and CTRL SHIFT up arrow for data above the selected cell. Activating a filter can be done with ALT down arrow. Provides a range of shortcuts for selecting cells and applying filters.', "The shortcut for inserting the current date is CTRL + ; and for the current time, it's CTRL SHIFT :. To insert a hyperlink, the shortcut is CTRL + K and to add a comment, it's Shift F2. Lists specific shortcuts for inserting current date, time, hyperlinks, and comments."]}, {'end': 23949.189, 'start': 23579.251, 'title': 'Excel formatting shortcuts', 'summary': 'Demonstrates how to apply currency and percentage formats using shortcuts, select entire rows and columns, delete rows and columns, hide and unhide selected rows and columns, and the process to unhide a selected column.', 'duration': 369.938, 'highlights': ['The chapter demonstrates how to apply currency and percentage formats using shortcuts. The transcript explains the process of converting numerical values into currency format using the shortcut Control Shift Dollar and converting a particular cell value into a percentage using the shortcut Control Shift Percentage.', 'The process of selecting entire rows and columns is explained using shortcuts. The transcript details the shortcuts for selecting entire rows using Shift Space, entire columns using Control Space, and the entire worksheet using Control Shift Space.', 'The process of deleting rows and columns using shortcuts is explained. The transcript discusses using shortcuts such as Alt H D C for deleting a column and Shift Space followed by Control Minus for deleting a row.', 'The process of hiding and unhiding selected rows and columns is demonstrated using shortcuts. The transcript explains the shortcuts for hiding a selected row using Control 9 and unhiding it using Control Shift 9. It also covers the process of hiding a selected column using Ctrl 0.']}, {'end': 24438.804, 'start': 23950.93, 'title': 'Excel pivot table shortcuts', 'summary': 'Covers shortcut keys for formatting, grouping, ungrouping, hiding, and creating pivot tables and charts in excel, with practical demonstrations and examples, helping to speed up work and enhance data analysis.', 'duration': 487.874, 'highlights': ["The shortcut key for ungrouping rows in Excel is alt shift, left arrow. The shortcut key 'alt shift, left arrow' can ungroup rows in Excel, providing a quick way to manage data and improve workflow efficiency.", 'Pivot table in Excel allows summarizing data by region, finding average sales per region, maximum and minimum sales, and total sales per region and category of products. Pivot tables in Excel enable the summarization of data by region, calculation of average, maximum, and minimum sales, and determination of total sales per region and product category, enhancing data analysis capabilities.', "The shortcut key for creating a pivot chart on a new worksheet in Excel is F11. In Excel, using the shortcut key 'F11' allows for the quick creation of a pivot chart on a new worksheet, streamlining the process of visualizing data for analysis and reporting."]}], 'duration': 2453.784, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/tuxHlIumThI/pics/tuxHlIumThI21985020.jpg', 'highlights': ['Excel provides warnings when data is not being saved, offering the user the option to save the data, serving as a helpful reminder and preventive measure against data loss.', "Excel automatically prompts to recover unsaved data by showing a folder on the left side of the spreadsheet and offers manual recovery options through the 'Open' or 'Info' menu, providing a user-friendly way to retrieve unsaved files.", 'The keyboard shortcuts in Excel help perform data analysis better and faster, with the advantage of remembering a few of them.', 'The chapter covers Excel shortcut keys including moving between tables, switching tabs, editing cells, copy-pasting cells, formatting font values, adding borders, selecting cells, applying filters, inserting current date and time, adding hyperlinks, and comments.', 'The chapter demonstrates how to apply currency and percentage formats using shortcuts.', 'Pivot table in Excel allows summarizing data by region, finding average sales per region, maximum and minimum sales, and total sales per region and category of products.']}], 'highlights': ['The session covers data analytics with Excel.', 'The chapter covers a comprehensive agenda for Excel data analytics training, including functions and formulas, conditional formatting, pivot tables, and data recovery, aimed at improving data management efficiency and providing insights and visualizations for reporting and analysis.', 'The chapter provides a demonstration of sorting data in ascending order of area and descending order of agent name in Excel.', 'The chapter explains how to use SUM, AVERAGE, and SUMPRODUCT functions in Excel to calculate total sales and average sales, providing examples and results as 1165 and 45.', 'Explained the usage of various Excel functions such as VLOOKUP, HLOOKUP, IF, AND, IFNA, and IFERROR, with examples and explanations, showcasing practical applications and error handling.', 'The INDEX function returns the value at the intersection of a specific row and column in a table, demonstrated by examples where specific cities are found using row and column numbers.', 'The chapter emphasizes the practical application of freezing rows and columns in Excel to manage spreadsheet data effectively, addressing common challenges faced when working with large datasets.', 'The tutorial demonstrates locking and protecting cells in Excel to avoid mistakes while editing confidential data, allowing specific columns to be unlocked and edited, and ensuring protection with a simple password.', 'Demonstrates usage of SUMIFS in Excel, simplifies data analysis and reporting', "The 'Goal Seek' functionality in Excel can be used to efficiently determine the needed exam score, especially for large datasets, saving time and effort", 'DAX in Excel is a sophisticated formulas-type language for working with relational data and dynamic aggregation functions.', 'By using macros, users can automate daily tasks in Excel, reducing the time and effort required for repetitive actions.', 'Excel provides warnings when data is not being saved, offering the user the option to save the data, serving as a helpful reminder and preventive measure against data loss.', 'The keyboard shortcuts in Excel help perform data analysis better and faster, with the advantage of remembering a few of them.']}