Section 01 : Microsoft Excel Version History

Section 02 : Version wise Features Comparison

Section 03 : Advantages of Microsoft Excel

Section 04 : Quick facts and figures about Microsoft Excel

Section 05 : What's new in Microsoft Excel

Section 06 : Portability features with earlier versions

Section 07 : Application Areas of Microsoft Excel

Section 08 : Microsoft Excel and Analytics

Section 09 : Microsoft Excel and BIG Data

Section 10 : Excel Beyond Excel

Understanding Excel Navigation System

Transformation from Menus to Ribbon

Overview of Ribbon, Groups & Tabs

Customizing Excel Ribbon

Importing and Exporting Customization Settings

Understanding Contextual Tabs

Overview of Quick Access Toolbars

Adding and Removing tools from Quick Access Toolbar

Navigating Back Stage View

Understanding Modules of Back Stage View

Excel Keyboard Shortcuts

Format Cell Basics

Navigating Format Cells Dialog Box

Understanding Excel Data Types

Understanding Custom Formatting

Transformation from Data Type to Custom

Creating custom Date Formatting

Creating custom Time Formatting

Creating custom Number Formatting

Creating custom Text Formatting

Conditional Custom Formatting

Custom Color Formatting

Format Cells to multiple places

Keyboard Shortcuts for Cell Formatting

A walkthrough to Formatting Options in Home Tab

Working with Paste Special - Magical

Splitting Data using Text to Columns

Sorting Data with Basic & Custom Options

Working with Excel Comments

Working with Goto Options – Magical

Working with Freeze Panes

Working with Grouping & Subtotals

Excel Security – Protecting a Selected Range

Excel Security – Protecting an Entire Sheet

Excel Security – Allow users to edit Range

Excel Security – Protecting Workbook Structure

Excel Security – Full Protection of Workbook

Excel Security – Partial Protection of Workbook

Working on Excel Data Backup

• Introduction to Cell Referencing

• Importance of Cell Referencing

• Understanding Relative Referencing

• Understanding Absolute Referencing

• Understanding Mixed Referencing

• Common challenges while using Referencing

Difference between Functions & Formulas

Concept of Nested Formulas

Formula Auditing

Various Calculation Modes and How to use them

Circular References – What are they?

Concept of Wild Cards

Concept of important Functions: AND, OR, NOT, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, MAX, MIN, LARGE, SMALL, UPPER, LOWER, PROPER, RIGHT, LEFT, MID, SEARCH, TEXT, VALUE, POWER, SUM, SUMIF, SUMIFS, AVERAGE, AVERAGEIF, AVERAGEIFS, EXACT, CONCATENATE, LEN, SUBSTITUTE, NETWORKDAYS, NETWORKDAYS.INTL, DATE, TIME, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WORKDAY, WORKDAY.INTL, EOMONTH, DATEDIF, VALUE, INT, ISERROR, ISTEXT, ISNUMBER, ISBLANK, ISODD, ISEVEN, MATCH, INDEX, IF, VLOOKUP – 1D, VLOOKUP – 2D, Reverse LOOKUP, HLOOKUP, CHOOSE, RANDBETWEEN, ROW, ROWS, COLUMN, COLUMNS, MOD, IFERROR, SUBTOTAL, SUMPRODUCT, OFFSET, ADDRESS, INDIRECT, REPT

Combinations of the above Functions

Data Consolidation using 3D Formulas

Concept of Precedents

Concept of Dependents

Introduction to Name Management in Excel

Overview of Name Box

Creating, editing and deleting Name

Understanding Name Scope

Defining Static and Dynamic List using names

Displaying Name Map in Excel Worksheet

Creating Single and Multidimensional Arrays using Name Manager

Use of Names in advance Reports

Common challeges in Name Management

Use of Name Manager for Extracting Data - The Magical

Introduction to Data Validation

Creating basic Data Validation

Creating Data Validation for Test

Creating Data Validation for Numbers

Creating Data Validation for Date and Time

Creating basic drop down list using Data Validation

Creating dynamic list using Data Validation

Creating Basic Dependent List

Creating Multilevel Dependent List

Use of Basic Formulas in Data Validation

Creating complex validation using Formulas

Hacks of Data Validation

Applying Data Validation to Multiple Places

Using Custom Error Alerts in Validation Conflicts

Using Data Validation as a Smart Comments

Steps to remove Data Validation

Highlighting Invalid Data

Introduction to Conditional Formatting

Understanding Static vs Dynamic Formatting

Text based Conditional Formatting Rules

Numbers based Conditional Formatting Rules

Date based Conditional Formatting Rules 

Dealing with Unique and Duplicates in Conditional Formatting 

Top and Bottom Rules in Conditional Formatting 

Introduction to Data Bars, Icon Sets, Color Scales

Creating Dashboard using Conditional Formatting

Creating Conditional Formatting using Basic Functions

Use Single Formatting with Multiple Conditions

Implementing Conditional Formatting in Attendance Trackers - Case Study

Using Complex Formulas in Conditional Formatting

Setting Priorities on Conditional Formatting Conflicts

Developing Smart Signalling System using Conditional Formatting

Reusing Conditional Formatting

Clear Conditional Formatting Rules

Introduction of Excel Tables

Power of Excel Tables

Time Saving features of Excel Tables

Calculation Logic of Excel Tables

Format As Table

Understanding Table Formatting Options

Multiple Filters In The Same Sheet

Total Row, Ability To Select Type Of Summary

Structured References – What Are They?

Advantages Of Tables

Convert Table To Named Range

Compatibility Of Tables With Earlier Versions

Limitation of Excel Tables

Introduction to Charts

Understading elements of Chart

Activating Chart Elements

Major charts used in Industry

Visualizing data using Column Charts

Visualizing data using Bar Charts

Visualizing data using Line Charts

Visualizing data using Pie Charts

Creating Combination Chart

Changing Chart Themes

Using Picture in Charts

Introduction to Tiny Charts – Sparklines

Creating Column Sparklines

Creating Line Sparklines

Creating Win-Loss Sparklines

Introduction to Advanced Charting

Why PivotTables? 

Structuring Your Source Data 

Inserting Your First PivotTable 

Navigating the Field List 

PivotTable Options: "Analyze" & "Design" 

Selecting, Clearing, Moving & Copying Pivots 

Refreshing & Updating Pivots 

Generating New Report from PivotTable

Managing Grand Totals

PivotTable Subtotals

Dealing with Growing Source Data

Removing & Reviving Data from Cache 

How PivotTables Works?

Format Cells vs Number Format

PivotTable Number Formatting 

Automatically Formatting Empty Cells in PivotTable (Pro Tip)

Understanding Table Layouts & Styles 

Customizing Headers & Labels

Introduction to Data Sorting in PivotTable

PivotTable Sorting Options

Filtering Data in PivotTables

Filter using Wild Cards

Using Slicers & Timelines to Filter Data 

Controlling multiple Pivots using Slicers and Timelines

Working on PivotTable Grouping

Date Grouping in PivotTable

Grouping in Numbers and Text

Value Summarization Modes 

Working on "Show Values As" Calculations 

Performing custom calculations using Calculated Fields 

Calculations in Pivots vs. Raw Data 

Inserting a Calculated Item for combined calculation

The Solve Order & List Formulas Tools 

Introduction to PivotCharts 

Applying Slicers & Timelines to Multiple Charts 

Building dynamic dashboards using Slicer and Timeline

Consolidating data from multiple sheets in few seconds

Consolidating data from multiple files in few seconds

Creating custom Page in PivotTable

Splitting Data to Multiple Reports from huge data in seconds

Developing Dynamic Tutorial using Slicer and Pivot (Pro Tip)

Challenges in PivotTables - What Next?

• Introduction to Excel Filter

• Data Filtering Techniques

• Introduction to Auto Filter

• Auto Filter Checkbox

• Filter using free text

• Using predefined modes in Text Filter

• Using predefined modes in Number Filter

• Effectively using Custom Filters

• Using Color Filtering

• Conditional Formatting Icons Filtering

• Filter using wild Cards

• Refershing modified data while keeping Filter Intact

• Challenges in Auto Filter

• Introduction to Advance Filter

• Exploring Advance Filter Dialog Box

• Advance Filter Modes

• Implementing AND, OR, NOT in Advance Filter

• Advance Filter with basic calculation

• Using Excel Funtions in Advance Filter

• Using Advance Filter as a Lookup Tool

• Extracting unique list with Advance Filter

• Clearing Filters

Introduction to Arrays

Introduction to Excel Array Formulas

Why and when to use Array Fomulas?

The internal logic behind Arrays

Concept of Logical Operators in Array Formulas

The CSE Method

Comparision Matrix with Excel conditional functions

Array Formulas using basic Excel Functions

How to create complex Array Formulas?

How to Master Array Formulas - What Next?

Introduction to What-If Analysis

Why and when to use What-If Analysis?

Working with Goal Seek for Reverse calculation

Multiple case studies on Goal Seek

Challenges in Goal Seek

Working with Data Table for Sensitivity analysis

Understanding 1 Variable and 2 Variables Data Table

Selection Mechanism in Data Table

Developing Bank Loan comparision analysis using Data Table

Working with Scenario Manager for Scenarios Management

Understanding Input & Output Variables in Scenario Manager

Bank Loan Scenario Management - Case Study

Introduction to Solver Add-In

Understanding Algorithms and Optimizations

Why and When to use Solver

Creating Business Models

Case Study - Resource Management

Case Study - Logistics Optimization

Case Study - Salary Management