ADVANCE EXCEL COURSES

MODULE 1. INTRODUCTION

Course Introduction

MODULE 2. FUNDAMENTALS OF MICROSOFT EXCEL

Launching Excel

Microsoft Excel Startup Screen

Introduction to Excel interface

Customizing the Excel quick access tool bar

More about Excel Workbook

Saving an Excel document

Opening an Excel document

Common excel shortcut keys

Handout Common Excel shortcut keys

MODULE 3 : Entering and editing texts and formulas

Entering text to create spread sheet title

Working with numeric data in excel

Entering data value in excel

Working with cell references

Creating basic formulas in excel

Relative v/s Absolute cell references - Part A

Relative v/s Absolute cell references - Part B

Order of Operation 

Practice 

MODULE 4: BASIC EXCEL FUNCTIONS

Structure of an Excel functions

Functions

Working with sum()function 

Working with min()and max() function

Working with average() function

Adjacent cells error in excel calculations

Using AutoSum command 

AutoSum shortcut key

Using Autofill command to copy formulas

MODULE 5: MODIFYING AN EXCEL WORKSHEET

Moving and copying data in excel worksheet

Inserting and Deleting Rows and Columns

Changing and width and height of the cells

Hiding and Unhiding Excel Rows and Column

Renaming and Excel worksheet 

Deleting an Excel worksheet

Moving and Copying excel worksheet 

MODULE 6: FORMATTING DATA IN AN EXCEL WORKSHEET

Working with font formatting

Changing the background colour of a cell

Adding borders to cell

Formatting data as currency values

Formulating Percentage

Using Excels Format painter

Creating styles to format data 

Merging and Centering cells

Using Conditional formatting 

MODULE :7 INSERTING IMAGES AND SHAPES INTO AN EXCEL 

Inserting Images

Inserting Excels Shapes

Formatting Excel Shapes

Working with Excel Smart Art 

MODULE 8: UNDERSTANDING AND CREATING BASIC CHARTS IN EXCEL

Creating an Excel column chart

Working with Excel chart Ribbon

Adding and Modifying Data on an Excel chart

Formatting an Excel chart

Moving the chart to another worksheet

Working with Excel Pie charts

MODULE 9: Printing an excel worksheet

Viewing your document in print preview

Changing the margin scalling and orientation

Working with page layout view

Adding header and footer content

Printing a specific range of cells 

MODULE 10: WORKING WITH EXCEL TEMPLATES

Introduction to excel templates 

Open an existing templates 

Creating an custom template

MODULE 11: FOUNDATION LEVEL CONCLUSION AND RECAP

Practical TEST

MODULE 12: INTERMEDIATE LEVEL CONCLUSION AND RECAP 

MODULE 13: WORKING WITH AN EXCEL LIST

Understanding excel list structure

Sorting a list using single level sort

Sorting a list using a multilevel sort

Using a custom sort in excel list

Filter an excel list using the autofilter tool

Creating subtotals in the list

format list as a table

Using Conditional formatting to list duplicates

Removing duplicates

MODULE 14: EXCEL LIST FUNCTIONS

Introduction to excel Functions - Dsum()

Excel Dsum function single criteria continued 

Excel Dsum function with OR Criteria 

Excel Functions with AND Criteria 

Excel Functions Daverage() 

Excel Functions Dcount()

Excel Functions SUBTOTAL()

MODULE 15: DATA VALIDATION IN EXCEL 

Understanding the need for Excel data validation 

Creating an Excel Data validation list 

Excel Numeric data Validation 

Adding an custom data validation error 

Dynamic formulas by using Excel data validation technique

MODULE 16: EXCEL PIVOT TABLETS

Understanding Pivot Tablets

Creating an Excel Pivot Tablet 

Modifying an Excel Pivot Table calculations

Grouping Pivot Table data

Formatting Pivot Table data

Drilling down the Pivot Table data

Creating Pivot charts

Filtering Pivot table data

Filtering with the Slicer tool

MODULE 17: WORKING WITH EXCEL POWER PIVOT TOOLS

Why Power Pivots

Activating the cell power pivot Adding

Create data models with power pivot 

Creating Pivot tables based on data model

Power Pivot Calculation Fields

Power Pivot KPI's

MODULE 18: WORKING WITH LARGE SETS OF EXCEL DATA

Using the freeze pane tools

Grouping data (Rows and columns)

Print option for large set of data

Linking worksheets (3D formulas)

Consolidating data from Multiple worksheets

MODULE 19: INTERMEDIATE LEVEL CONCLUSION AND RECAP

Practical test

MODULE 20: ADVANCED MS EXCEL INTRODUCTION 

MODULE 21: WORKING WITH EXCEL CONDITIONAL FUNCTIONS

Working with Excel Name Ranges

Advantages and Disadvantages of Excel Name Ranges 

Editing an Excel Name Range 

Using Excel IF () Function 

Using Excel IF () Function with name range 

Nesting Function with Excel 

Nesting Excel AND () function with IF () Function 

Using Excel's COUNTIF() Function 

Using Excel's SUMIF() Function

Using Excel's IFERROR() Function 

MODULE 22: WORKING WITH EXCEL LOOKUP FUNCTIONS

Microsoft Excel VLOOKUP() Function 

Microsoft Excel HLOOKUP() Function

Using Excel's INDEX() Function 

Using Excel's MATCH() Function

Using Excel's INDEX() and MATCH() Function combined PART A

Using Excel's INDEX() and MATCH() Function combined PART B

Creating Dynamic HLOOKUP() with MATCH() Function

MODULE 23: WORKING WITH EXCEL TEXT BASED FUNCTION

Using Excel's LEFT(), RIGHT() and MID() Function 

Using Excel's LEN() Function

Using Excel's SEARCH() Function

Using Excel's CONCATENATE() Functions

MODULE 24: AUDITING AN EXCEL WORKSHEET

Tracing Precedents in Excel Formula

Tracing Dependents in Excel Formula

Working with the watch window

Showing formula

MODULE 25: PROTECTING EXCEL WORKSHEET AND WORKBOOK 

Protecting specific cells in worksheet

Protecting the Structure of a workbook

Adding a workbook password

MODULE 26: MASTERING EXCEL'S WHATIF TOOLS

Working with Excel Goal seek tool 

Working with Excel Solver tool

Building Effective Data tables in Excel

Creating Scenarios in Excel

MODULE 27: AUTOMATING REPETETIVE TASKS IN EXCEL WITH MACROS 

Understanding Excel Macros

Activating the Developer tab in Excel

Creating a Macro with Macro recorder

Editing a Macro with VBA

Creating buttons to run Macros 

MODULE 28: ADVANCED LEVEL CONCLUSION AND RECAP

Practical test 

MODULE 29: FINAL GRADE MCQ EXAM