Best Advanced excel training institute

Module 1: Excel Introduction & Setting (1 Hour)

• Excel menu button
• Excel settings
• Shortcut Keys
• Excel Tricks

Module 2: Cell Referencing & Number Formatting (1 Hour)

 

• Absolute, Relative, Mixed Reference
• Format as Currency, Comma Separator
• Format as Date
• Custom Format – Currency, Date & Other

 

Module 3: Named Ranges, Tables & Styles (30 Mins)

 

• Naming Cells and Ranges
• Using named ranges in formulas

• Creating tables in excel
• Applying new style to tables
• Cell styles for titles & headings

 

Module 4: Protection (30 Mins)

 

• Cell Protection
• Worksheet Protection
• Workbook Protection

 

Module 5: Conditional Formatting (1 Hour)

 

• Highlighting the cells
• Includes icon sets, Data Bar
• Creating new rule
• Appling formula in new rule
• Highlight Cells – Single, Multiple Conditions
• Condition based on selection Referencing

 

Module 6: Pivot Tables and Pivot Charts (1 Hour)

 

• Creating Pivot Table
• Filter Date Values as Month, Quarter, Years
• Classic pivot table
• Recommended pivot table
• Report Filter On Pages.
• Pivot Chart with table
• Include Slicer & Creating Dashboard

 

Module 7: Advanced Charting (1 Hour)

 

• Creating Multiple Chart Type
• Primary, Secondary Chart, Sparkline charts
• Smart arts

 

Module 8: Working with Data Lists (1 Hour)

 

• Sorting – Custom Sort, Multilevel
• Subtotals – Formula, Function
• Filtering – Advanced Filter

 

Module 9: Data Tools & External Data (1 Hour)

 

• Grouping

• Import data from web
• Import from other sources like text file, access, Power Query

 

Module 10: Data Validation & Forecasting (1 Hour)

 

• Setting Criteria - Text, Values and Dates
• Drop-Down List – Dependent
• Consolidation
• Advance filter
• Remove duplicates
• What IF Analysis with Scenario, Goal Seek, data table
• Forecast Sheet
• Solver

 

 

Module 11: Logical Functions (1 Hour)

 

• IF, Nested IF, AND, OR
• IF Condition with Text Contains
• Compound IF Function

 

 Module 12: Lookup Functions (1 Hour)

 

• VLOOKUP, HLOOKUP
• MATCH, INDEX, INDIRECT

 

Module 13: Statistical Functions (1 Hour)

 

• COUNTIF, SUMIF
• COUNTIFS, SUMIFS
• COUNTA, COUNT, COUNTBLANK

 

Module 14: Excel Advanced Formula’s (2 Hour)

 

• LEFT, RIGHT, MID, UPPER, PROPER, LOWER
• EOMONTH, EDATE, NETWORKDAYS, WORKDAY
• DATE, TIME, TODAY, NOW, TEXT, SUBSTITUTE,
• SEARCH, FIND, LEN, TRIM, SUBTOTAL, AVERAGE
• SUM, MIN, MAX, PMT, FORECAST, IFERROR, EXACT

 

 

Module 15: Record Macro (1 Hour)

 

• Record macros for automating tasks
• Creating Button to run a Macro
• Macro with Keyboard & its benefit
• Run / Delete a macro

 

Module 16: VBA Macro (1 Hour)

 

• Introduction about VBA Scripts
• Advantages
• First Macro Creation