Microsoft Excel has proven to be extremely advantageous for day to day business activities. Small, Medium and Large-scale businesses depend on Excel extensively for data management and other organizational jobs. Many organizations manage their reports, MIS, client data and financials using Excel Sheets. The knowledge of Microsoft Excel has become essential for every business establishments to carry out their day to day business and reap the best results from their business.

The five-day program with Microsoft Certification is all designed to make you a complete professional on Excel and you will be able to manage your office work extremely swiftly and become more productive at the workplace. Be it a student an entrepreneur, a senior busines executive, an accountant, fashion stylist, animator, Programmer, engineer or a doctor this is a must do course for you.

Program Outline for Advanced Excel.

The four-day program curriculum below will be covered on line in 5 days including time to be allotted to participants to apply the learnings during the class.

  DAY – I
Session I • Excel 2019 Interface
• New Features
• Basic Functions
• Default Settings
• Contextual Tabs
• Ribon Customization
• Data Import (TXT, CSV)
• Insert/ remove hyperlink
• Inspect Workbook for issues
• Filter  /Sort
• Format and Modify Text
• Paste special, links
• Using AutoFill to enter a series of values
• Using AutoCorrect for shorthand data entry
• Forcing text to appear on a new line within a cell
• Using the Office Clipboard to paste
• Transposing a range
Session II • Named Cells, Ranges
• Define Named Range
• Name a table
• Create and format Tables
• Inserting References
• Calculate and transform data
• Conditional cell formating ( Logical Functions)
Session III • Autocreation of Names
• Slicer in a table
• Data Validation
•  Dependent Validation rules
•  Customize Validation through Formulas
•  Custom Error Messages
•  Advance use of Data Validation



  DAY – II
Session I Data Filter/Advanced Filter/Group/Subtotal
Use and benefits

• Date Filters/Text Filters
•  Removing/copying duplicates
• Advanced Filter
• Grouping /Ungrouping of Data
• Auto Grouping of data
• Subtotal
• Using Relative & Absolute References
• Logical Formulas (IF, AND, OR, IFERROR, IFNA, XOR)
Session II Formula and Functions
• Lookup

• Hlookup
• Vlookup
• Index
• Match
• Sumif, Countif, Sumproduct and Averageif
• Vlookup from left side/repeating lookup
• Advance formulas (OFFSET/INDIRECT)
• Statistical Functions
• DSUM function
Session III





Session I Creating Formulas for Financial Applications
 Worksheet functions for calculating loan information-   PMT,   PPMT,   IPMT,   RATE,   NPER,   PV,   Depreciation Calculations

Introducing Pivot Tables
Specifying the location for the pivot table
Laying out the pivot table
Formatting the pivot table
Modifying the pivot table
Working with Non-Numeric Data
Grouping Pivot Table Items
A manual grouping example
Automatic grouping examples
Creating a Calculated Field or Calculated Item
Filtering Pivot Tables with Slicers
Creating Pivot Charts
Monthly/quarterly/yearly/weekly/ reports
 Multiple Reports with click of a button
 Pivot Charts & Modifying Pivot Data
Consolidate multiple sheets in to Pivot table


Session II
Session III Performing Spreadsheet What-If Analysis
Types of What-If Analyses,   Creating Data Tables,   Creating a one-input data table,   Creating a two-input data table,   Using Scenario Manager,   Generating a scenario report,   What-If Analysi  in Reverse,  Single-Cell Goal Seeking,   A goal-seeking example, Introducing Solver



  DAY – IV
Session I Working with Dates and Times
Displaying the static and dynamic dates,   Calculating the number of days between two dates,   Calculating the number of work days between two dates,   Offsetting a date using only work day,  Calculating the number of years between two dates
Tracing precedent cells
Tracing the dependents of a cell
Displaying all formulas within a worksheet
Adding ,   Displaying, Removing,   Editing comments
Inserting watermark, Protect Sheet, Workbook, few Cells/Column of a sheet, Restrict that no one can copy any cell/Range/Sheet, Dynamically update PPT with excel files
Joining two or more cells, Removing excess spaces and nonprinting characters
Session II Creating a Chart,   Choosing a chart type,   Adding chart elements,   Modifying the Axes,   Value axis,   Category axis
Creating, Customizing sparkline graphics
Overview and use of Macro
Creating Dashboard
Session III MOS Expert Exam overview
Q & A


Our cutting-edge Excel certification program gives testing candidates practical, performance-based scenarios to gauge their real-world capabilities with project and standards-based outcome testing.

Certification candidates will demonstrate the fundamentals of creating and managing worksheets and workbooks, creating cells and ranges, creating tables, applying formulas and functions and creating charts and objects.

To make the exam more helpful, we’ve replaced individual tasks with comprehensive, project-based testing. This live-in-the-application environment confirms that certified individuals have demonstrated the ability to complete a realistic Excel project.

An individual earning this certification has approximately 150 hours of instruction and hands-on experience with the product, has proven competency at an industry entry-level and is ready to enter into the job market.