Excel

Course Content:

Overview of Basics of Excel

Customizing common options in Excel

Absolute and relative cells

Protecting and un-protecting worksheets and cells

Working with Functions

Writing conditional expressions (using IF)

Using Logical operators (AND, OR , NOT)

Using Lookup and reference functions (VLOOKUP,HLOOKUP,MATCH,INDEX)

Vlookup with Exact Match , Appropriate Match

Nested Vlookup with Exact Match

Vlookup with Tables, Dynamic Ranges

Using Vlookup to consolidate Data from Multiple Sheets

Data Validations

Specifying a valid range of values for a cell

Specifying a list of valid values for a cell

Specifying custom validations based on formula for a cell

Working with Templates

Designing the structure of a template

Using templates for standardization of worksheets

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 Advance 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)

Creating Slicers (Version 2010 & Above)

More Functions

Date and Time functions, Text Functions, Database Functions

Power Functions (COUNTIF,COUNTIFS,SUMIF,SUMIFS)

Formatting

Using auto formatting option for worksheets

Using Conditional formatting option for rows, columns and cells.

Macros

Relative & Absolute Macros, Editing Macros

Charts & New Features of Excel

Using charts, Formatting charts, Using 3D Graphs , Bar , Line ,Pie charts

Sharing charts with PowerPoint / MS-Word ,Dynamically

Sparklines, Inline Charts, data charts.