MS Excel

An advanced Excel course is like a suite of program tools that will no less refine your abilities and new knowledge of how you can operate Microsoft Excel for data analysis, modeling, and automation. Some of the topics that are commonly covered in an advanced Excel course are given below:

Course Overview:

• Advanced Formulas and Functions:
• Be well-versed with complex formulas, array formulas, logical functions, lookup functions, text functions and advanced mathematical functions.
• Data Analysis Tools:
• Investigate the in-built data analysis tools of Excel like Pivot tables, Pivot Charts, data validation, conditional formatting, and goal seek.
• Advanced Data Manipulation:
• Dive into the methods of sorting, filtering, and manipulating large datasets using the features such as data tables, sorting and filtering, advanced filtering and data consolidation.
• Advanced Charting:
• Explore creating advanced charts and graphs, modifying chart components, the use of secondary axes, and making combination charts.
• Data Visualization
• Learn methods for displaying data in a visual way through features including sparklines, data bars, color scales, and icon sets.
• Data Import and Export:
• Get a clear understanding of how to pull the data from text files, databases, and web queries. Grasp the concept of data export to different file formats and follow automation of the data import/export processes.
• Macros and VBA:
• Improve the use of Visual Basic for Applications (VBA) and the utilization of macros to automate tasks, create custom functions, and build user-friendly interfaces.
• Data Analysis with Power Query:
• How to use Power Query to import, transform, and cleanse data from multiple sources, and then load it into Excel for analysis.
• Advanced Data Modeling:
• Investigate the methods that are available for creating complicated data models with the help of features such as tables, relationships, calculated columns, and measures.
• What-If Analysis and Scenario Manager:
• Apply Scenarios to construct and compare several sets of input values to your data models.Use Data Tables for analyzing the situation when one or two variables are changed causing different outcomes to be a possibility.
• Data Validation and Protection:
• Besides validation, Excel allows you to manage data protection and set user-level permissions to keep the data accurate and thus improve it.

Curriculum:

• Basic Excel
• Structure of excel application.
• Hierarchy of excel application
• Basic features of excel application.
• Formatting (Applying Borders, Colors and Font styles)
• Different Excel Versions (Differences)
• Reference Concept (working with different sheets, workbooks s Closed workbook)          
• Define Name (Single Cell Group of cells, formulae)
• Creating Dropdowns by using Data validation and combo box
• Hierarchy dropdown
• Shortcut keys
• Date & Time Functions
• Logical Functions
• Arithmetic Functions
• Lookup & Reference Functions
• Text Funtions
• Charts
• Pivots
• All Menu Tabs
• Dashboard