| Overview of the Basics of Excel |
: |
An overview of the screen, navigation and basic spreadsheet concepts , Various selection techniques , Shortcut keys , Worksheet operations
|
| Working with Functions |
: |
Absolute, Relative and Mixed referencing , Writing conditional expressions (using IF) , Using logical functions (AND, OR, NOT)
|
| Lookup |
: |
-
VLOOKUP, HLOOKUP, MATCH, INDEX , VlookUP with Exact Match, Approximate Match , Nested VlookUP with Exact Match ,
-
VlookUP with Tables, Dynamic Ranges , Nested VlookUP with Exact Match , Using VLookUP to consolidate Data
from Multiple Sheets
|
| Data Validations |
: |
-
Number, Date & Time validation , Text and List Validation , Custom validations based on formula for a cell
- Dynamic dropdown list creation using Data validation – Dependency List
|
| Sorting and Filtering Data |
: |
-
Sorting tables , Using multiple-level sorting , Using custom sorting
-
Filtering data for selected view (AutoFilter) , Using advanced filter options
|
|
Working with Reports
|
: |
-
Creating subtotals , Multiple-level subtotals , Creating Pivot tables , Formatting and customizing Pivot tables
-
Using advanced options of Pivot tables , Pivot charts , Consolidating data from multiple sheets and files using Pivot tables
-
Using external data sources , Using data consolidation feature to consolidate data , Show Value as (% of Row, % of Column,
Running Total, Compare with Specific Field) , Viewing Subtotal under Pivot , Creating Slicers
|
|
Important Functions
|
: |
• Date and time , Statistical , Database , Power Functions (CountIf, CountIFS, SumIF, SumIfS)
|
|
What If Analysis
|
: |
Goal Seek , Data Tables , Scenario Manager , Solver |
|
Macros
|
: |
Relative & Absolute Macros , Editing Macro's , Introduction to VBA |
|
Charts
|
: |
-
Using Charts , Formatting Charts , Using 3D Graphs , Using Bar and Line Chart together
-
• Using Secondary Axis in Graphs , Sparklines, Inline Charts, data Chart , Sharing Charts with PowerPoint / MS Word, dynamically
(Data Modified in Excel, Chart would automatically get updated)
|
|
Miscellaneous Features
|
: |
-
Flush Fill , INDEX-MATCH , Protect Sheet
-
Linking Cells , Working with multiple workbooks , Importing and Exporting data
|