Advanced Microsoft Excel

Course Duration – 2 Months / 8 Sessions

Session 1 – Getting Started with Excel

– Introduction to Excel 2013/2016
– Open an Excel Workbook with Different Ways
– Interface and Key Components of Excel
– Navigating Through Excel Window
– Exploring Important Excel Options*
– Creating Sample Employee Database
– Working with Cells & Ranges
– Auto- Fit Content to Column Width / Row Height
– Merge / Unmerge Cells
– Extracting Unique Values
– Text to Columns (Importing txt file in Excel)
– Use of Auto- fill and Flash Fill with Multiple Examples
– Important Ribbon, General and Data Entry Keyboard Shortcuts
– Session Case Study

Session 2 – Formatting Essentials

– Formatting Essentials
– Custom Cell Number Formats
– Date and Time Formats
– Working with Comments
– Format Painter
– Paste Special
– Table, Table Styles & Formatting
– Freeze Panes
– Page Setup & Print Formatting
– Session Case Study

Session 3 – Functions & Formulas

– Introduction to Excel Functions
– Difference between Functions & Formulas
– Basics of Functions & Formulas
– Working with Cell References
– Most Used Basics & Advanced Functions & Formulas
– Working with Array Formulas
– Creating Customize Formulas Step-by-Step with examples
– Creating and Working with Dynamic Ranges using OFFSET
– Formulas Debugging / Formulas Auditing
– Types of Formula Errors / Error Handling
– Functions & Formulas

a) Text Function
b) Date & Time Functions
c) Math & Trig Functions
d) Statistical Functions
e) Logical Functions
f) Lookup & Reference Functions
g) Financial Functions
h) Compatibility Functions

Session 4 – Data Analysis

– Data Sorting
– Data Filtering
– Named Ranges
– Conditional Formatting
– Data Validation

Session 5 – Excel Charts

– Introduction to Excel Charts
– Exploring most commonly used Charts and Templates
– Basics of Charts
– Selecting Requirement based Charts
– Working with Basic Charts:
– Creating Customized / Advanced Charts
– Creating Dynamic Chart
– Creating Histogram Chart and Its’ uses
– Working with Dynamic Interactive Charts in Excel using Drop Down
– Working with Chart Elements, Formatting, Chart Styles, Properties etc.

Session 6 – Pivot Tables

– Introduction to Pivot Table
– Creating a Pivot Table
– Use of Calculated Fields/Items
– Pivot Table Formatting
– Grouping Items & Summarizing data in Pivot Tables
– Grouping and Bucketing data in Pivot Table
– Changing/Modifying Data Sources
– Working with Pivot Table Designs & Layouts
– Exploring Important Pivot Table Options & Field Settings
– Pivot Table Filters
– Creating Dynamic Pivot Table Summary
– Changing Pivot Table Summary Calculation
– Use of Slicers in Pivot Table
– Use of Timeline in Pivot Table
– Introduction to Pivot Charts

Session 7 – Bonus Module

– Sheet/Range Protection in Excel
– 350+ Excel Keyboard Shortcuts
– Introduction to Dashboard & Templates
– Introduction to Macro
– How to record Macro in Excel
– Q & A

 What you will learn after completing this course: –

– Proficient in Ms-Excel
– Having good command on keyboard
– Skilled in formatting the small and large datasets
– Capable of handling the large data
– Reading any type of data
– Able to choose the appropriate function/formula for any situation
– Building own formulas for customize requirements
– Navigating through Excel efficiently & effectively
– Able to create dynamic ranges
– Debugging the functions/formulas
– Understanding the functionality of simple and Complex functions & formulas
– Plotting various charts & tables
– Creating various reports using different features & functions
– Having strong knowledge of Advanced Formulas
– Able to analyze any data
– Ability to create Good Looking and World Class Dashboards