title
Advanced Excel Tutorial | Excel Training | Edureka

description
πŸ”΄Subscribe to Edureka YouTube channel for latest Excel video updates: http://bit.ly/2ADbbb1 πŸ”₯ For Edureka Online Certifications (Use Code "π˜πŽπ”π“π”ππ„πŸπŸŽ"): https://www.edureka.co/search This Edureka video on 'Advanced Excel Tutorial' will help you learn MS Excel for all kinds of Data Analysis, data visualizations, data security, etc. Here are all the categories the topics are divided into for this Excel Tutorial: 1:06 Security 5:50 MS Excel Themes 8:55 Templates 9:36 Graphics 10:36 Printing Options 13:06 Data Tables 16:09 Charts 17:22 Pivot Tables and Pivot Charts 20:03 Data Validation 22:18 Filtering Data 23:29 Data Sorting 24:54 Cross Referencing in Excel 27:46 Macros 30:00 Language Translation #Edureka #ExcelEdureka #AdvancedExcelTutorial#ExcelTutorial Do subscribe to our channel and hit the bell icon to never miss an update from us in the future: https://goo.gl/6ohpTV Edureka Community: https://bit.ly/EdurekaCommunity Instagram: https://www.instagram.com/edureka_learning/ Facebook: https://www.facebook.com/edurekaIN/ Twitter: https://twitter.com/edurekain LinkedIn: https://www.linkedin.com/company/edureka ----------------------------------------------------------------------------------------------------------- For more information, please write back to us at sales@edureka.in or call us at IND: 9606058406 / US: 1833

detail
{'title': 'Advanced Excel Tutorial | Excel Training | Edureka', 'heatmap': [{'end': 902.724, 'start': 841.107, 'weight': 0.865}], 'summary': 'This advanced ms excel tutorial covers security, themes, templates, graphics, printing options, data tables, pivot charts, data validation, filtering, sorting, vlookup, and language translation, aiming to help users efficiently manage huge data. it also includes excel file level security, encryption, password management, and customization of themes, templates, graphics, and printing options. additionally, it demonstrates the creation and visualization of one and two variable data tables, pivot tables, charts, data validation, filtering, sorting, cross-referencing, macros, and language translation for automation and efficient data management.', 'chapters': [{'end': 63.192, 'segs': [{'end': 63.192, 'src': 'embed', 'start': 11.641, 'weight': 0, 'content': [{'end': 18.071, 'text': "It's really nice to manage huge data with just a few mouse clicks and Excel is definitely one tool that will allow you to do this.", 'start': 11.641, 'duration': 6.43}, {'end': 21.356, 'text': "So, in case you're unaware of the magical tricks of Excel,", 'start': 18.571, 'duration': 2.785}, {'end': 27.445, 'text': 'here is Vajija from Edureka with this advanced MS Excel tutorial wherein you will be learning all the advanced topics in Excel.', 'start': 21.356, 'duration': 6.089}, {'end': 35.447, 'text': 'So the first topic of discussion will be security in Excel, followed by themes, templates, Excel graphics, document printing options,', 'start': 28.143, 'duration': 7.304}, {'end': 42.952, 'text': 'data tables in Excel graphs or charts to visualize your data by with tables and Pivot charts, data validation, filtering, sorting,', 'start': 35.447, 'duration': 7.505}, {'end': 45.834, 'text': 'cross-referencing using vlookup and language translation.', 'start': 42.952, 'duration': 2.882}, {'end': 52.658, 'text': 'So before we begin just make sure you subscribe to our channel and hit the bell icon to stay updated with all the latest edureka videos.', 'start': 46.534, 'duration': 6.124}, {'end': 57.767, 'text': "Also, if you're looking for an online training certification in any of the training technologies,", 'start': 53.343, 'duration': 4.424}, {'end': 59.809, 'text': 'check out the link given in the description box below.', 'start': 57.767, 'duration': 2.042}, {'end': 63.192, 'text': "Okay, so without any further delays, let's get started.", 'start': 60.59, 'duration': 2.602}], 'summary': "Learn advanced excel topics like security, themes, graphics, data tables, pivot charts, vlookup, and more with edureka's tutorial.", 'duration': 51.551, 'max_score': 11.641, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY11641.jpg'}], 'start': 11.641, 'title': 'Advanced ms excel tutorial', 'summary': 'Discusses an advanced ms excel tutorial covering security, themes, templates, graphics, printing options, data tables, pivot charts, data validation, filtering, sorting, vlookup, and language translation, aiming to help users manage huge data efficiently with excel.', 'chapters': [{'end': 63.192, 'start': 11.641, 'title': 'Advanced ms excel tutorial', 'summary': 'Discusses an advanced ms excel tutorial covering security, themes, templates, graphics, printing options, data tables, pivot charts, data validation, filtering, sorting, vlookup, and language translation, aiming to help users manage huge data efficiently with excel.', 'duration': 51.551, 'highlights': ['The tutorial covers advanced topics in Excel such as security, themes, templates, Excel graphics, document printing options, data tables, Pivot charts, data validation, filtering, sorting, vlookup, and language translation.', 'Excel is a tool that allows users to manage huge data with just a few mouse clicks.', 'The chapter emphasizes the importance of subscribing to their channel and staying updated with the latest edureka videos for online training certification.', 'The chapter introduces Vajija from Edureka as the instructor for the advanced MS Excel tutorial.', 'The chapter encourages viewers to check out the link in the description box for online training certification in various technologies.']}], 'duration': 51.551, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY11641.jpg', 'highlights': ['The tutorial covers advanced topics in Excel such as security, themes, templates, Excel graphics, document printing options, data tables, Pivot charts, data validation, filtering, sorting, vlookup, and language translation.', 'Excel is a tool that allows users to manage huge data with just a few mouse clicks.', 'The chapter introduces Vajija from Edureka as the instructor for the advanced MS Excel tutorial.', 'The chapter encourages viewers to check out the link in the description box for online training certification in various technologies.', 'The chapter emphasizes the importance of subscribing to their channel and staying updated with the latest edureka videos for online training certification.']}, {'end': 535.295, 'segs': [{'end': 100.379, 'src': 'embed', 'start': 63.973, 'weight': 0, 'content': [{'end': 67.857, 'text': 'So, moving on towards the first topic of the session, which is security.', 'start': 63.973, 'duration': 3.884}, {'end': 72.041, 'text': 'security is one aspect that we need in every application that we use.', 'start': 67.857, 'duration': 4.184}, {'end': 78.327, 'text': 'Excel provides three levels of security, that is, file level security, worksheet level security and workbook level security.', 'start': 72.041, 'duration': 6.286}, {'end': 85.869, 'text': 'File level security refers to securing your Excel file by making use of password so as to prevent others from opening and modifying your file.', 'start': 79.065, 'duration': 6.804}, {'end': 89.392, 'text': 'So now to show you guys how to enable this file level security.', 'start': 86.61, 'duration': 2.782}, {'end': 92.914, 'text': "I'll jump on to Excel and over here from the ribbon tab.", 'start': 89.412, 'duration': 3.502}, {'end': 97.957, 'text': "I'll click on file and go to info from this protect workbook drop-down list.", 'start': 92.954, 'duration': 5.003}, {'end': 100.379, 'text': "I'll select the option of encrypt with password.", 'start': 98.257, 'duration': 2.122}], 'summary': 'Excel offers three levels of security: file, worksheet, and workbook; file level security involves encrypting with password.', 'duration': 36.406, 'max_score': 63.973, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY63973.jpg'}, {'end': 191.467, 'src': 'embed', 'start': 159.029, 'weight': 2, 'content': [{'end': 162.05, 'text': 'So there are a few things that you guys have to remember when it comes to passwords.', 'start': 159.029, 'duration': 3.021}, {'end': 166.448, 'text': "Do not forget your password as there's no password recovery available in Excel.", 'start': 162.685, 'duration': 3.763}, {'end': 170.671, 'text': 'No restrictions are labied, but Excel passwords are case-sensitive.', 'start': 167.028, 'duration': 3.643}, {'end': 175.415, 'text': 'avoid distributing password protected files with sensitive information, such as bank details,', 'start': 170.671, 'duration': 4.744}, {'end': 180.238, 'text': 'and protecting a file with a password will not necessarily protect malicious activities.', 'start': 175.415, 'duration': 4.823}, {'end': 181.499, 'text': 'avoid sharing your passwords.', 'start': 180.238, 'duration': 1.261}, {'end': 189.085, 'text': 'So now moving on towards the next level security, which is worksheet level to protect the data present in the worksheet from being modified.', 'start': 182.22, 'duration': 6.865}, {'end': 191.467, 'text': 'You can lock the cells and protect the worksheet.', 'start': 189.405, 'duration': 2.062}], 'summary': 'Remember password guidelines; excel has no recovery. protect sensitive files and avoid sharing passwords.', 'duration': 32.438, 'max_score': 159.029, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY159029.jpg'}, {'end': 391.992, 'src': 'embed', 'start': 367.758, 'weight': 4, 'content': [{'end': 374.282, 'text': 'So, using the msxl themes, you can make use of standard color themes, create your team, modify the fonts of themes,', 'start': 367.758, 'duration': 6.524}, {'end': 376.923, 'text': 'change effects and also save your customized themes.', 'start': 374.282, 'duration': 2.641}, {'end': 379.665, 'text': "So now let's get back to Excel and see how this works.", 'start': 377.564, 'duration': 2.101}, {'end': 382.847, 'text': 'Teams will be present in this page layout tab.', 'start': 380.665, 'duration': 2.182}, {'end': 387.77, 'text': "So I'll click on page layout and from here to change the color of this theme.", 'start': 383.387, 'duration': 4.383}, {'end': 391.992, 'text': "I'll just open up the colors drop-down list and I'll select some different color.", 'start': 388.09, 'duration': 3.902}], 'summary': 'Using msxl themes, modify color, fonts, effects, and save customized themes in excel.', 'duration': 24.234, 'max_score': 367.758, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY367758.jpg'}], 'start': 63.973, 'title': 'Excel file and worksheet security in ms excel', 'summary': 'Discusses excel file level security, including encrypting a workbook with a password, and provides tips for creating and managing secure passwords. it also covers the levels of security in excel, such as protecting worksheets and workbooks with passwords, and the customization of themes in ms excel.', 'chapters': [{'end': 175.415, 'start': 63.973, 'title': 'Excel file level security', 'summary': 'Discusses excel file level security, which includes encrypting a workbook with a password to prevent unauthorized access, and provides tips for creating and managing secure passwords.', 'duration': 111.442, 'highlights': ['Excel provides three levels of security: file level security, worksheet level security, and workbook level security, with a focus on file level security for this session.', "Enabling file level security in Excel involves going to the info tab, selecting 'encrypt with password' from the protect workbook dropdown list, and entering a password when prompted, which effectively restricts unauthorized access to the file.", 'Important considerations for managing passwords in Excel include the absence of password recovery options, the case-sensitive nature of Excel passwords, and the recommendation to avoid distributing sensitive information like bank details in password-protected files.']}, {'end': 535.295, 'start': 175.415, 'title': 'Excel security and themes', 'summary': 'Covers the levels of security in excel, including protecting worksheets and workbooks with passwords, and the customization of themes in ms excel, allowing users to harmonize fonts, colors, and graphics.', 'duration': 359.88, 'highlights': ['Protecting worksheets and workbooks with passwords to prevent malicious activities and unauthorized access. Worksheet level security allows users to lock cells, selectively allow or disallow access to particular cells, and protect the sheet with a password. Workbook level security prevents users from adding, deleting, hiding, or renaming sheets.', 'Customizing themes in MS Excel, including changing colors, fonts, and effects, and saving customized themes for future use. Users can change the complete theme or just the colors and fonts, modify fonts of themes, change effects, and save customized themes. Themes are present in the page layout tab, and users can also save their customized theme in the document themes folder in the dot dhmx format.']}], 'duration': 471.322, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY63973.jpg', 'highlights': ['Excel provides three levels of security: file level security, worksheet level security, and workbook level security, with a focus on file level security for this session.', "Enabling file level security in Excel involves going to the info tab, selecting 'encrypt with password' from the protect workbook dropdown list, and entering a password when prompted, which effectively restricts unauthorized access to the file.", 'Important considerations for managing passwords in Excel include the absence of password recovery options, the case-sensitive nature of Excel passwords, and the recommendation to avoid distributing sensitive information like bank details in password-protected files.', 'Protecting worksheets and workbooks with passwords to prevent malicious activities and unauthorized access. Worksheet level security allows users to lock cells, selectively allow or disallow access to particular cells, and protect the sheet with a password.', 'Customizing themes in MS Excel, including changing colors, fonts, and effects, and saving customized themes for future use. Users can change the complete theme or just the colors and fonts, modify fonts of themes, change effects, and save customized themes.']}, {'end': 809.359, 'segs': [{'end': 584.849, 'src': 'embed', 'start': 558.815, 'weight': 2, 'content': [{'end': 565.839, 'text': 'Okay, as you scroll down, you can see that Excel provides a number of templates, such as the investment tracker, the seasonal photo calendar, Etc.', 'start': 558.815, 'duration': 7.024}, {'end': 568.28, 'text': 'You can choose any template of your choice.', 'start': 566.379, 'duration': 1.901}, {'end': 574.563, 'text': 'And you will see that your workbook will be of that pattern.', 'start': 572.122, 'duration': 2.441}, {'end': 578.726, 'text': 'So now moving on towards the next topic, which is graphics.', 'start': 575.244, 'duration': 3.482}, {'end': 584.849, 'text': 'unlike what many people think, Excel does not just allow you to play around with data, but also allows you to add graphics to it.', 'start': 578.726, 'duration': 6.123}], 'summary': 'Excel offers various templates and allows adding graphics to workbooks.', 'duration': 26.034, 'max_score': 558.815, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY558815.jpg'}, {'end': 690.874, 'src': 'embed', 'start': 663.392, 'weight': 1, 'content': [{'end': 667.473, 'text': 'The first option that is print area will allow you to set the area that you need to print.', 'start': 663.392, 'duration': 4.081}, {'end': 674.314, 'text': 'The second option will allow you to set the rows and columns title to appear at the top and towards the left respectively.', 'start': 668.373, 'duration': 5.941}, {'end': 679.086, 'text': 'When you check the grid lines option, grid lines will be added to the printout.', 'start': 675.043, 'duration': 4.043}, {'end': 683.089, 'text': 'black and white will print the document as black and white or in monochrome.', 'start': 679.086, 'duration': 4.003}, {'end': 687.772, 'text': 'the draft quality option will allow you to print the sheet using the printers draft quality and.', 'start': 683.089, 'duration': 4.683}, {'end': 690.874, 'text': 'the row and column headings will allow you to print the row and column headings.', 'start': 687.772, 'duration': 3.102}], 'summary': 'Print options include setting area, grid lines, color, and quality.', 'duration': 27.482, 'max_score': 663.392, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY663392.jpg'}, {'end': 816.661, 'src': 'embed', 'start': 789.527, 'weight': 0, 'content': [{'end': 793.789, 'text': 'data tables in Excel are created to experiment with different values for a formula.', 'start': 789.527, 'duration': 4.262}, {'end': 802.935, 'text': 'You can create both one or two variable data tables in Excel data tables are one of the three types of what-if analysis tools available in Excel.', 'start': 794.47, 'duration': 8.465}, {'end': 809.359, 'text': "So in this advanced tutorial, I'll be showing you guys how to create both one variable and two variable data tables.", 'start': 803.595, 'duration': 5.764}, {'end': 812.621, 'text': "So as you can see on the screen, I've already prepared some data.", 'start': 810.139, 'duration': 2.482}, {'end': 816.661, 'text': 'Here I have purchased 16 glasses at the rate of 20 each.', 'start': 813.32, 'duration': 3.341}], 'summary': 'Excel data tables used for formula experimentation. tutorial covers one and two variable data tables.', 'duration': 27.134, 'max_score': 789.527, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY789527.jpg'}], 'start': 536.127, 'title': 'Excel templates and data tables', 'summary': 'Covers excel templates, graphics, printing options, and data tables, highlighting how to use templates and graphics, configure printing options, and create one and two variable data tables in excel.', 'chapters': [{'end': 809.359, 'start': 536.127, 'title': 'Excel templates, graphics, printing options, data tables', 'summary': 'Covers excel templates, graphics, printing options, and data tables, highlighting how to use templates and graphics, configure printing options, and create one and two variable data tables in excel.', 'duration': 273.232, 'highlights': ['The chapter covers how to use Excel templates to increase production rates by saving time and effort to create documents, and how to add graphics such as shapes, images, icons, and Smart Arts to Excel sheets, providing practical examples and benefits for users.', 'The section on printing options details various features such as setting print area, rows and columns titles, grid lines, black and white printing, draft quality, row and column headings, page order, margins, headers, footers, and page orientation, emphasizing the extensive range of customization options available in Excel.', 'The tutorial also explores the creation of one and two variable data tables in Excel, highlighting their significance as part of the what-if analysis tools available in Excel for experimenting with different values for a formula, providing practical examples and explaining the purpose and application of data tables in Excel.']}], 'duration': 273.232, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY536127.jpg', 'highlights': ['The tutorial explores the creation of one and two variable data tables in Excel, highlighting their significance as part of the what-if analysis tools available in Excel for experimenting with different values for a formula, providing practical examples and explaining the purpose and application of data tables in Excel.', 'The section on printing options details various features such as setting print area, rows and columns titles, grid lines, black and white printing, draft quality, row and column headings, page order, margins, headers, footers, and page orientation, emphasizing the extensive range of customization options available in Excel.', 'The chapter covers how to use Excel templates to increase production rates by saving time and effort to create documents, and how to add graphics such as shapes, images, icons, and Smart Arts to Excel sheets, providing practical examples and benefits for users.']}, {'end': 1038.651, 'segs': [{'end': 840.607, 'src': 'embed', 'start': 810.139, 'weight': 0, 'content': [{'end': 812.621, 'text': "So as you can see on the screen, I've already prepared some data.", 'start': 810.139, 'duration': 2.482}, {'end': 816.661, 'text': 'Here I have purchased 16 glasses at the rate of 20 each.', 'start': 813.32, 'duration': 3.341}, {'end': 821.622, 'text': "So this way I'll have to pay a total of $320 for 16 glasses.", 'start': 817.381, 'duration': 4.241}, {'end': 827.023, 'text': 'Now in case I want to create a one variable data table that will show me the prices for different quantities.', 'start': 822.362, 'duration': 4.661}, {'end': 830.984, 'text': 'All I have to do is copy the value present in B3 to some other cell.', 'start': 827.483, 'duration': 3.501}, {'end': 833.345, 'text': "So I'll just say is equal to B3.", 'start': 831.004, 'duration': 2.341}, {'end': 836.406, 'text': 'So as you can see over here, the value has been copied.', 'start': 834.305, 'duration': 2.101}, {'end': 840.607, 'text': "Now, what I'm going to do is write down different quantities for this item.", 'start': 837.366, 'duration': 3.241}], 'summary': 'Purchased 16 glasses at $20 each, totaling $320. creating a one variable data table for different quantities.', 'duration': 30.468, 'max_score': 810.139, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY810139.jpg'}, {'end': 921.276, 'src': 'heatmap', 'start': 841.107, 'weight': 2, 'content': [{'end': 846.573, 'text': "So I'll start with 16 17.", 'start': 841.107, 'duration': 5.466}, {'end': 847.774, 'text': "I'll just copy the series guys.", 'start': 846.573, 'duration': 1.201}, {'end': 854.697, 'text': "And now I'll select this newly created range.", 'start': 852.496, 'duration': 2.201}, {'end': 857.359, 'text': 'Click on the data tab.', 'start': 856.358, 'duration': 1.001}, {'end': 862.182, 'text': "And then from this what-if analysis tools, I'm going to choose data table.", 'start': 858.56, 'duration': 3.622}, {'end': 869.746, 'text': "As you can see over here a new window has opened wherein it's asking me to enter the row input cell and the column input cell.", 'start': 863.442, 'duration': 6.304}, {'end': 877.739, 'text': "Since I do not have any row input cells and specify the input to be as a column input cell since I'm dealing with quantities over here.", 'start': 870.433, 'duration': 7.306}, {'end': 881.582, 'text': "I'll have to specify the target cell to be b2 and then I'll click on OK.", 'start': 877.819, 'duration': 3.763}, {'end': 888.928, 'text': 'So as you can see over here, I have a data table that has been created for different quantities at the rate of 20 per unit.', 'start': 882.463, 'duration': 6.465}, {'end': 893.832, 'text': "So there's one thing that's missing over here, which is the dollar sign now to add a dollar sign.", 'start': 889.709, 'duration': 4.123}, {'end': 899.001, 'text': "What I'm going to do is select all these cells and then from the home tab and the numbers group.", 'start': 893.852, 'duration': 5.149}, {'end': 900.302, 'text': "I'll click on the dollar symbol.", 'start': 899.242, 'duration': 1.06}, {'end': 902.724, 'text': 'Okay, so I hope you guys are clear with this.', 'start': 901.103, 'duration': 1.621}, {'end': 909.128, 'text': 'So now moving on towards the two variable data table just like how we created a one variable data table.', 'start': 903.564, 'duration': 5.564}, {'end': 911.51, 'text': 'We can also create a two variable data table.', 'start': 909.348, 'duration': 2.162}, {'end': 916.173, 'text': "So now to create a two variable data cell what I'm going to do is copy this value again.", 'start': 912.17, 'duration': 4.003}, {'end': 921.276, 'text': "And for the rows, I'll specify some quantities again.", 'start': 919.154, 'duration': 2.122}], 'summary': 'Demonstrates creating one and two variable data tables for quantities at the rate of 20 per unit, adding dollar signs, and using excel functions.', 'duration': 38.813, 'max_score': 841.107, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY841107.jpg'}, {'end': 1001.767, 'src': 'embed', 'start': 975.867, 'weight': 3, 'content': [{'end': 980.631, 'text': 'These charts can visualize numerical values in a very meaningful and easy to understand manner.', 'start': 975.867, 'duration': 4.764}, {'end': 986.683, 'text': 'Charts are a very essential part of Excel and they are improved greatly with every new version of MS Excel.', 'start': 981.221, 'duration': 5.462}, {'end': 990.964, 'text': 'So as you can see on the screen, I already have a table for which I want to create a chart.', 'start': 987.403, 'duration': 3.561}, {'end': 993.745, 'text': "Okay So all I'm going to do is select this table.", 'start': 991.584, 'duration': 2.161}, {'end': 995.605, 'text': "I'll open up the charts pane.", 'start': 994.425, 'duration': 1.18}, {'end': 999.306, 'text': "And from all charts, I'll select the column chart.", 'start': 997.246, 'duration': 2.06}, {'end': 1001.767, 'text': 'Let me also change the design of this.', 'start': 1000.346, 'duration': 1.421}], 'summary': 'Excel charts improve data visualization, essential for making data meaningful and easy to understand.', 'duration': 25.9, 'max_score': 975.867, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY975867.jpg'}], 'start': 810.139, 'title': 'Data table creation and visualization', 'summary': 'Covers the creation of one variable data table to analyze prices based on a purchase of 16 glasses at $20 each, resulting in a total cost of $320. it also demonstrates the creation of one and two variable data tables in ms excel, focusing on adding dollar signs to cells and customizing column charts for effective visualization and filtering of numerical values.', 'chapters': [{'end': 862.182, 'start': 810.139, 'title': 'Creating one variable data table', 'summary': 'Explains the process of creating a one variable data table to analyze prices for different quantities based on a purchase of 16 glasses at $20 each, resulting in a total cost of $320.', 'duration': 52.043, 'highlights': ['The process involves purchasing 16 glasses at the rate of $20 each, resulting in a total cost of $320.', 'Creating a one variable data table allows for the analysis of prices for different quantities based on the initial purchase.', 'Copying the value present in cell B3 to another cell facilitates the creation of the data table.']}, {'end': 1038.651, 'start': 863.442, 'title': 'Data table creation and chart visualization in ms excel', 'summary': 'Demonstrates the creation of one and two variable data tables in ms excel, with a focus on adding dollar signs to cells, along with creating and customizing column charts to visualize and filter numerical values effectively.', 'duration': 175.209, 'highlights': ['Creation of two variable data table The speaker demonstrates the process of creating a two variable data table by specifying quantities and price ranges, resulting in the creation of a data table for two variable values.', 'Adding dollar signs to cells The presenter shows how to add dollar signs to cells by selecting all cells and clicking on the dollar symbol in the home tab, enhancing the visual representation of monetary values.', 'Customizing and filtering column charts The chapter illustrates the process of creating and customizing column charts, including changing the design, filtering data, and configuring charts with different types and colors to effectively visualize numerical values.']}], 'duration': 228.512, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY810139.jpg', 'highlights': ['Creating a one variable data table allows for the analysis of prices for different quantities based on the initial purchase.', 'The process involves purchasing 16 glasses at the rate of $20 each, resulting in a total cost of $320.', 'The presenter shows how to add dollar signs to cells by selecting all cells and clicking on the dollar symbol in the home tab, enhancing the visual representation of monetary values.', 'The chapter illustrates the process of creating and customizing column charts, including changing the design, filtering data, and configuring charts with different types and colors to effectively visualize numerical values.', 'The speaker demonstrates the process of creating a two variable data table by specifying quantities and price ranges, resulting in the creation of a data table for two variable values.']}, {'end': 1339.407, 'segs': [{'end': 1064.079, 'src': 'embed', 'start': 1039.423, 'weight': 0, 'content': [{'end': 1045.146, 'text': 'So now moving on towards the next topic of the session, which is Pivot tables and Pivot charts in Excel.', 'start': 1039.423, 'duration': 5.723}, {'end': 1051.291, 'text': 'a Pivot table in Excel is a statistical table that condenses the data of those tables that have extensive information.', 'start': 1045.146, 'duration': 6.145}, {'end': 1058.575, 'text': 'These tables help you visualize your data based on any of the fields present in your data table using these Pivot tables.', 'start': 1052.191, 'duration': 6.384}, {'end': 1064.079, 'text': 'You can visualize your data by changing the fields rows and columns add filters sort your data Etc.', 'start': 1058.755, 'duration': 5.324}], 'summary': 'Pivot tables in excel condense extensive data, allowing visualization and manipulation for better insights.', 'duration': 24.656, 'max_score': 1039.423, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1039423.jpg'}, {'end': 1119.424, 'src': 'embed', 'start': 1095.217, 'weight': 1, 'content': [{'end': 1101.079, 'text': 'Also the pivot tables field window has opened which contains all the fields that are available in my table over here.', 'start': 1095.217, 'duration': 5.862}, {'end': 1105.94, 'text': 'Now in case I want to add any of these fields to the columns rows values and filters.', 'start': 1101.779, 'duration': 4.161}, {'end': 1108.761, 'text': 'All I have to do is drag and drop it in that area.', 'start': 1106.32, 'duration': 2.441}, {'end': 1111.882, 'text': "So what I'm going to do is add in the amounts and values.", 'start': 1109.561, 'duration': 2.321}, {'end': 1119.424, 'text': "I'll keep City as the filters name of the vendors in rows and the name of the item as columns.", 'start': 1113.162, 'duration': 6.262}], 'summary': 'Demonstrates using pivot tables to analyze data, dragging and dropping fields into columns, rows, values, and filters.', 'duration': 24.207, 'max_score': 1095.217, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1095217.jpg'}, {'end': 1246.162, 'src': 'embed', 'start': 1217.048, 'weight': 2, 'content': [{'end': 1220.089, 'text': 'you want certain cells in your sheet to accept only dates.', 'start': 1217.048, 'duration': 3.041}, {'end': 1223.93, 'text': 'You can do this easily using the data validation feature of Excel.', 'start': 1220.709, 'duration': 3.221}, {'end': 1226.35, 'text': "So what I'm going to do is open up a new sheet.", 'start': 1224.51, 'duration': 1.84}, {'end': 1229.61, 'text': "So what I'm going to do here is select a few cells.", 'start': 1227.31, 'duration': 2.3}, {'end': 1237.112, 'text': "And I'll make the specific to accept only one type of data now to do that.", 'start': 1233.031, 'duration': 4.081}, {'end': 1240.379, 'text': "I'll have to go to this data tab and from the data tools.", 'start': 1237.172, 'duration': 3.207}, {'end': 1242.2, 'text': "I'll have to select data validation.", 'start': 1240.599, 'duration': 1.601}, {'end': 1246.162, 'text': 'So as you can see over here, I have a number of options.', 'start': 1243.901, 'duration': 2.261}], 'summary': "Use excel's data validation to restrict cells to accept only specific data types.", 'duration': 29.114, 'max_score': 1217.048, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1217048.jpg'}], 'start': 1039.423, 'title': 'Excel data management', 'summary': 'Covers the creation and manipulation of pivot tables and charts in excel, allowing users to condense and visualize extensive data, as well as the importance of data validation in excel, highlighting user benefits and impacts of invalid input.', 'chapters': [{'end': 1094.382, 'start': 1039.423, 'title': 'Excel pivot tables and charts', 'summary': 'Covers the creation of pivot tables in excel, allowing users to condense and visualize extensive data by changing fields, adding filters, and sorting data.', 'duration': 54.959, 'highlights': ['Pivot tables in Excel condense extensive data and allow visualization based on fields present in the data table.', 'Creating a pivot table in Excel involves selecting the data, clicking on the insert tab, and choosing the option to create a pivot table.', 'Pivot tables enable users to visualize data by changing fields, adding filters, and sorting data.']}, {'end': 1195.035, 'start': 1095.217, 'title': 'Creating pivot tables and pivot charts', 'summary': 'Demonstrates how to create a pivot table, interchange row and column labels, change value field settings, and create a pivot chart in excel, providing insights into the process and features of these functions.', 'duration': 99.818, 'highlights': ["Demonstrates adding fields to columns, rows, values, and filters in a pivot table by drag-and-drop, with examples of adding 'amounts' and 'values'. ", 'Illustrates the process of interchanging row labels and column labels in a pivot table by drag-and-drop, providing a visual demonstration of the pivot table changing accordingly. ', "Explains changing the value field settings to display figures other than the sum, with a demonstration of changing from 'sum' to 'count' and back. ", 'Demonstrates creating a pivot chart by clicking on the insert tab and choosing a Pivot chart from the chart group, with a visual demonstration of selecting a basic type and a different style. ']}, {'end': 1339.407, 'start': 1195.395, 'title': 'Excel data validation', 'summary': 'Covers the importance of data validation in excel, demonstrating how to configure cells to accept specific data types, with examples of setting validation for whole numbers and providing input and error messages, highlighting the user benefits and the impact of invalid input.', 'duration': 144.012, 'highlights': ['Demonstrating how to configure cells to accept specific data types, with examples of setting validation for whole numbers. Configuration of cells for data validation, setting validation for whole numbers.', 'Providing input and error messages for user guidance and alerting about invalid input. Provision of input and error messages, alerting about invalid input.', 'The feature of data validation in Excel allows configuring cells to accept specific data types, benefiting user experience and ensuring data integrity. Benefit of data validation feature, ensuring data integrity, enhancing user experience.']}], 'duration': 299.984, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1039423.jpg', 'highlights': ['Pivot tables in Excel condense extensive data and allow visualization based on fields present in the data table.', "Demonstrates adding fields to columns, rows, values, and filters in a pivot table by drag-and-drop, with examples of adding 'amounts' and 'values'.", 'Demonstrating how to configure cells to accept specific data types, with examples of setting validation for whole numbers.']}, {'end': 1904.772, 'segs': [{'end': 1430.068, 'src': 'embed', 'start': 1356.669, 'weight': 0, 'content': [{'end': 1362.81, 'text': "So, as you can see over here, once I've selected this option, I have a drop-down list that has been created over here.", 'start': 1356.669, 'duration': 6.141}, {'end': 1367.131, 'text': 'when I open this drop-down list, you can see over here that all the cities have been selected.', 'start': 1362.81, 'duration': 4.321}, {'end': 1370.471, 'text': "So I'll just unselect this and they'll just select Washington.", 'start': 1367.751, 'duration': 2.72}, {'end': 1372.571, 'text': "And now I'll click on OK.", 'start': 1371.611, 'duration': 0.96}, {'end': 1377.592, 'text': 'So as you can see the data only for Washington City has been filtered out.', 'start': 1373.672, 'duration': 3.92}, {'end': 1380.693, 'text': "So here I've applied a filter just to one column.", 'start': 1378.313, 'duration': 2.38}, {'end': 1384.245, 'text': 'In case I want to filter data based on multiple criterias.', 'start': 1381.363, 'duration': 2.882}, {'end': 1389.168, 'text': 'All I have to do is select those columns and then I have to add filters to them.', 'start': 1384.565, 'duration': 4.603}, {'end': 1392.01, 'text': "I'll filter out the data for New York.", 'start': 1390.389, 'duration': 1.621}, {'end': 1396.653, 'text': "And the item I'll say should be apples.", 'start': 1394.972, 'duration': 1.681}, {'end': 1404.759, 'text': 'So as you can see over here, I filtered out data based on the items as well as the city.', 'start': 1400.276, 'duration': 4.483}, {'end': 1407.701, 'text': "So I'll just undo the changes.", 'start': 1406.58, 'duration': 1.121}, {'end': 1413.224, 'text': "So now let's move on towards the next topic, which is sorting data.", 'start': 1408.863, 'duration': 4.361}, {'end': 1417.925, 'text': 'sorting in Excel refers to arranging the data rows on the basis of the data present in the columns.', 'start': 1413.224, 'duration': 4.701}, {'end': 1424.166, 'text': 'So for example, you can arrange the names from A to Z or arrange numbers from ascending and descending orders.', 'start': 1418.805, 'duration': 5.361}, {'end': 1430.068, 'text': "So for the same table that I've taken previously in case I want to sort the names from A to Z.", 'start': 1425.067, 'duration': 5.001}], 'summary': 'Demonstration of filtering and sorting data in excel, including selecting specific cities and items for filtering, and arranging data rows based on column data.', 'duration': 73.399, 'max_score': 1356.669, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1356669.jpg'}, {'end': 1530.829, 'src': 'embed', 'start': 1505.821, 'weight': 3, 'content': [{'end': 1513.805, 'text': 'You can make use of the vlookup function the vlookup function in Excel can be used to bring forth required data from different spreadsheets as well.', 'start': 1505.821, 'duration': 7.984}, {'end': 1517.939, 'text': 'However, if you do not have the knowledge of this vlookup function,', 'start': 1514.716, 'duration': 3.223}, {'end': 1522.962, 'text': 'you can refer to the vlookup in Excel video of edureka present on edureka YouTube channel.', 'start': 1517.939, 'duration': 5.023}, {'end': 1530.829, 'text': "So as you can see on the screen, I've already created a table with the names of employees along with their IDs designations and the salaries.", 'start': 1523.783, 'duration': 7.046}], 'summary': "Vlookup function in excel retrieves data from spreadsheets, refer to edureka's youtube video for guidance.", 'duration': 25.008, 'max_score': 1505.821, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1505821.jpg'}, {'end': 1690.02, 'src': 'embed', 'start': 1662.959, 'weight': 4, 'content': [{'end': 1666.541, 'text': 'So, as you can see over here, my table has been completed.', 'start': 1662.959, 'duration': 3.582}, {'end': 1670.283, 'text': "now moving on to what's a very important topic, that is, macros.", 'start': 1666.541, 'duration': 3.742}, {'end': 1674.085, 'text': 'macros are a must to be learned in Excel using these macros.', 'start': 1670.283, 'duration': 3.802}, {'end': 1678.648, 'text': 'You can automate the tasks that you perform regularly by just recording them as a macro.', 'start': 1674.305, 'duration': 4.343}, {'end': 1685.439, 'text': 'A macro in Excel is basically an action or a set of them that can be performed again and again automatically.', 'start': 1679.657, 'duration': 5.782}, {'end': 1690.02, 'text': 'So in order to record a macro all you have to do is click on this view tab.', 'start': 1686.459, 'duration': 3.561}], 'summary': 'Learn to automate tasks in excel using macros for efficiency.', 'duration': 27.061, 'max_score': 1662.959, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1662959.jpg'}, {'end': 1841.372, 'src': 'embed', 'start': 1810.065, 'weight': 5, 'content': [{'end': 1817.029, 'text': "It can auto detect the language present in your data and then convert it into desired languages that is present in Excel's list of languages.", 'start': 1810.065, 'duration': 6.964}, {'end': 1822.732, 'text': "So now just to show this to you guys what I'm going to do is open up a new sheet and over here.", 'start': 1817.91, 'duration': 4.822}, {'end': 1823.773, 'text': "I'll just type in hello world.", 'start': 1822.752, 'duration': 1.021}, {'end': 1829.169, 'text': "Now, I'll open the review tab.", 'start': 1827.569, 'duration': 1.6}, {'end': 1841.372, 'text': 'From the review tab over here at select translate.', 'start': 1839.152, 'duration': 2.22}], 'summary': 'Excel can auto detect and translate languages from its list.', 'duration': 31.307, 'max_score': 1810.065, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1810065.jpg'}], 'start': 1339.407, 'title': 'Excel data filtering, sorting, cross-referencing, macros, and translation', 'summary': 'Explains data filtering and demonstrates filtering for specific cities and items, creating dropdown lists, and filtering based on multiple criteria. it also covers sorting data, cross-referencing using vlookup, recording and running macros, and translating data into different languages, enabling automation and language translation.', 'chapters': [{'end': 1407.701, 'start': 1339.407, 'title': 'Data filtering in excel', 'summary': 'Explains how to filter data in excel, demonstrating the process of filtering data for specific cities and items, showcasing the creation of dropdown lists and the ability to filter based on multiple criteria.', 'duration': 68.294, 'highlights': ['The process of filtering data for specific cities and items is demonstrated, showing how to create a dropdown list and filter data only for a particular city, such as Washington. (Relevance: 5)', 'The ability to filter data based on multiple criteria, such as filtering data for New York City and specifying the item as apples, is showcased. (Relevance: 4)', 'The demonstration includes the process of adding filters to selected columns to filter data based on multiple criteria. (Relevance: 3)']}, {'end': 1904.772, 'start': 1408.863, 'title': 'Excel data sorting, cross-referencing, macros & language translation', 'summary': 'Covers sorting data in excel, using vlookup for cross-referencing data across sheets, recording and running macros, and translating data into different languages, enabling automated tasks and language translation.', 'duration': 495.909, 'highlights': ['The vlookup function in Excel can be used to bring forth required data from different spreadsheets as well. The vlookup function in Excel can be used to bring forth required data from different spreadsheets, enabling efficient data retrieval from multiple sources.', 'Macros in Excel can automate the tasks that you perform regularly by just recording them as a macro. Macros in Excel can automate tasks by recording and running them, saving time and effort in performing regular tasks.', "Excel allows users to translate the data into different languages and can auto detect the language present in the data. Excel's language translation feature can auto detect and translate data into different languages, providing multilingual support for data analysis and presentation.", 'Sorting in Excel refers to arranging the data rows on the basis of the data present in the columns. Sorting in Excel refers to arranging data rows based on the data in the columns, allowing for organized data presentation and analysis.']}], 'duration': 565.365, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/ezA8RPqkExY/pics/ezA8RPqkExY1339407.jpg', 'highlights': ['The process of filtering data for specific cities and items is demonstrated, showing how to create a dropdown list and filter data only for a particular city, such as Washington. (Relevance: 5)', 'The ability to filter data based on multiple criteria, such as filtering data for New York City and specifying the item as apples, is showcased. (Relevance: 4)', 'The demonstration includes the process of adding filters to selected columns to filter data based on multiple criteria. (Relevance: 3)', 'The vlookup function in Excel can be used to bring forth required data from different spreadsheets, enabling efficient data retrieval from multiple sources.', 'Macros in Excel can automate tasks by recording and running them, saving time and effort in performing regular tasks.', "Excel's language translation feature can auto detect and translate data into different languages, providing multilingual support for data analysis and presentation.", 'Sorting in Excel refers to arranging data rows based on the data in the columns, allowing for organized data presentation and analysis.']}], 'highlights': ['The tutorial covers advanced topics in Excel such as security, themes, templates, Excel graphics, document printing options, data tables, Pivot charts, data validation, filtering, sorting, vlookup, and language translation.', 'Excel is a tool that allows users to manage huge data with just a few mouse clicks.', 'Excel provides three levels of security: file level security, worksheet level security, and workbook level security, with a focus on file level security for this session.', 'The tutorial explores the creation of one and two variable data tables in Excel, highlighting their significance as part of the what-if analysis tools available in Excel for experimenting with different values for a formula, providing practical examples and explaining the purpose and application of data tables in Excel.', 'Pivot tables in Excel condense extensive data and allow visualization based on fields present in the data table.', 'The process of filtering data for specific cities and items is demonstrated, showing how to create a dropdown list and filter data only for a particular city, such as Washington. (Relevance: 5)', 'The ability to filter data based on multiple criteria, such as filtering data for New York City and specifying the item as apples, is showcased. (Relevance: 4)', 'The demonstration includes the process of adding filters to selected columns to filter data based on multiple criteria. (Relevance: 3)', 'The vlookup function in Excel can be used to bring forth required data from different spreadsheets, enabling efficient data retrieval from multiple sources.', 'Macros in Excel can automate tasks by recording and running them, saving time and effort in performing regular tasks.', "Excel's language translation feature can auto detect and translate data into different languages, providing multilingual support for data analysis and presentation.", 'Sorting in Excel refers to arranging data rows based on the data in the columns, allowing for organized data presentation and analysis.']}