Advanced MS Excel Training
By
Capital Trainers

Microsoft excel Training
MODULE 1 - EXCEL BASICS (FOR BEGINNERS)
?Getting Started with Excel
Structure of the Excel working area,
How to navigate in Excel,
Ribbons and tabs,
Quick Access Toolbar (QAT)
Dialog Box & Task Panes,
Customizing ribbons and QAT,
What is an active cell
How to use ALT shortcuts
Data Entry, Data Editing, and Number Formatting

MODULE 2 - EXCEL ESSENTIALS
Introduction to Excel Tables
?Creating an Excel Table
Excel Table features
Structured references
Table slicers
Auto-fill, Custom Lists, and Flash Fill
Autofill: This allows you to quickly fill data in contiguous cells. For example, you can have 'Jan' and 'Feb' in two adjacent cells and then you can use Autofill to get the other month names
Custom Lists: Custom lists allows you to create your own lists that you can use in Autofill. For example, if you have 20 names, you can create a custom list and then use Autofill to get all the names by just dragging the mouse
Flash Fill: Flash fill is a new feature in Excel 2013 and allows you to identify a pattern in data and perform data slice and dice based on the patte
Number Formatting in Excel
How to access number formatting options
Using number formatting options using a dialog box
Using custom number formatting

MODULE 3 - EXCEL FORMULAS
Excel Formula Basics
Constituents of Excel formula
Operators in formulas
Entering a formula in Excel
Editing a formula
Absolute/relative cell references
Copying/pasting formulas in Excel, and
Formula auditing and debugging.
Logical Formulas in Excel
AND function
OR function
NOT function
TRUE function
FALSE function
IF function
IFERROR function
IS function
Math Formulas in Excel
INT function
MOD function
RAND function
RANDBETWEEN function
ROUND function
SUM function
SUMIF function
SUMIFS function
SUMPRODUCT function
Lookup and Reference Formulas in Excel
VLOOKUP function
HLOOKUP function
INDEX function
MATCH function
OFFSET function
INDIRECT function
ROW function
ROWS function
COLUMN function
COLUMNS function
Stats Formulas in Excel
AVERAGE function
AVERAGEIF function
AVERAGEIFS function
COUNT function
COUNTA function
COUNTBLANK function
COUNTIF function
COUNTIFS function
LARGE function
SMALL function
RANK function
MAX function
MIN function
Text Formulas in Excel
LEFT function
RIGHT function
MID function
LEN function
LOWER function
PROPER function
UPPER function
FIND function
REPLACE function
SUBSTITUTE function
TEXT function
Date and Time Formulas in Excel
DAY function
HOUR function
MINUTE function
DATE function
DATEVALUE function
TODAY function
NOW function
WEEKDAY function
NETWROKDAYS function
NETWORKDAYS.INTL function
WORKDAY function
WORKDAY.INTL function

MODULE 4 - DATA ANALYSIS
Named Ranges in Excel
Creating Named Ranges
Managing Named Ranges
Important Keyboard Shortcuts
Creating Dynamic Named Ranges using OFFSET and INDEX formulas
 Data Validation in Excel
Data Validation Criteria
Input Message
Customizing Error Messages
Circle Invalid Entries
Advanced Data Validation Tricks (such as Create Dependent Validation, Make Sub Headings in Validation, Disguise Numbers as Text, Creating Dynamic Drop Down List)
Data Sorting and Filtering in Excel
Data Sorting
Multi-Levels of Sorting
Data Filtering
Advanced Filtering
Filtering and Sorting in Excel Data Tables
Conditional Formatting in Excel
Introduction to Conditional Formatting
Using Formula in Conditional Formatting
Advanced Examples (Highlight Every Nth Row, Creating Dynamic Search)

MODULE 5 - PIVOT TABLE
Overview of Excel Pivot Table
Introduction to Pivot Tables
Creating a Pivot Table in Excel
Formatting the Pivot Table
Grouping data in an Excel Pivot table
Calculated Field/Items in Pivot Table
Pivot Table Slicers
Pivot Charts

MODULE 6 - EXCEL EXTRAS
?Protection in Excel
Excel Charts
Combination Charts
Dynamic Charts