ADVANCE EXCEL TRAINING

1. Basics of Microsoft Excel

• Introduction of MS Excel
• Uses and Limitations
• Basic operators - ( + , - , /, *, ^, >, < , =)
• Arithmetic operators
• Comparative Operators
• Calculations of Numbers and Date
• Percentage (% ) Computation
• Use of Formulas
• Absolute & Relative referencing ($)
• Auto fill options
• Number formats
• Paste Special
• Selection- Keyboard shortcuts
• Conditional formatting
• Creating, editing, saving and printing spreadsheets
• Working with formulas
• Formatting worksheets
• Securing & Protecting spreadsheets
• Page Setup & Print formatting

1. Most Important Formulas& Calculations (100+ Formulas)

• Lookup & Reference Functions: -VLOOKUP, HLOOKUP, INDEX, MATCH, INDIRECT, COLUMN, COLUMNS, ROW, ROWS, CHOOSE, TRANSPOSE, ARRAYS CONCEPT IN LOOKUP FORMULA’S ETC.
• Logical Functions: -AND, OR, IF, IFERROR, NOT, NESTED IF
• Mathematical Functions: -SUM, SUMIF, SUMIFS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, AVERAGE, AVERAGEIF, AVERAGEIFS, RAND, RANDBETWEEN, ROUND etc.
• Text Functions:- CHAR, CLEAN, CODE, CONCATENATE, FIND, SUBSTITUTE, REPLACE, LEN, RIGHT, LEFT, MID, LOWER, UPPER, PROPER, TEXT, TRIM, VALUE etc.
• Date & Time Function: -DATE, DAY, MONTH, YEAR, EDATE, EOMONTH, NETWORKDAYS, MINUTE, SECOND, NOW, TODAY, TIME ETC.
• More Formulas: -ISTEXT, ISNUMBER, ISERROR, IFERROR, ISBLANK, IFNA etc.

1. Analyzing and Summarization of data

        Experiencing Of Handling Large Data

• ü Data Validation
• ü Conditional Formatting
• ü Sort& Filter, Advance Filter
• ü Name Cells /Ranges /Constants
• ü Importing Data From Other Sources
• ü Consolidation Of Data
• ü Sheet Referencing, Hyperlink
• ü What-if-analysis, Goal Seek
• ü Pivot Tables & Reporting
• ü Graphically Representing Data : Charts & Graphs

1. Advanced Reports and Dashboard Creation

• Advanced Data Validation
• Advance Conditional formatting
• Advanced Number formats
• More keyboards shortcuts
• Advanced charts and Graphs
• Creating Dashboards
• Some more Functions
• Case Study Questions
• Work on real data
• Techniques of Putting Combinations of Formulas
• Improving Logical Concepts
• Excel tricks to work fast
• Introduction to VBA