Advanced MS - Excel

Unit 1: Basic and new excel features in different versions of Excel, Types of
references to perform complex calculations
• Introduction of Excel with different Excel versions
• Work with Comments
• Copy/Paste, Paste Special
• Changing the font and font size
• Changing font color
• Fill Effects
• Wrapping text within a cell
• Cell Formatting
• Filter, Advance Filter, Color Filter
• Sorting, Advance Sorting, Custom Sorting
• Flash Fill Feature in Excel 2013
• Absolute, Relative and Mixed Cell References
• Protect sheet and workbook
• Lock cell
• Table Feature
• Freeze and unfreeze panes and Split
• Excel Keyboard Shortcut Keys
Unit 2: Custom Number Format, GOTO Special, Hyperlink, Remove
Duplicates and Data Validation
• Changing the number format
• Understanding the number format codes
• Changing the font color with number format codes
• Adding the text with number format codes
• Hide Number and Text based on criteria
• GOTO SPEICAL functionality in Excel
• Hyperlink from different sheet, workbook, cell, defined names and websites
• Using Hyperlink function open any website, send email
• Remove Duplicates
• Data Validation: Avoid any duplicate entry in range, Create Validation List, create
error message and input message etc. • Watch Window
Unit 3: Name Manager, COUNT Functions, COUNTIF with wild card,
Running Count and Arrange Data with Function
• Define Name using Name Manager
• Create from Selection
• Name Convention in Name Manager
• COUNT, COUNTA, COUNTBLANK
• COUNTIF,COUNTIFS
• COUNTIF with wildcard
• Arrange Data
• Unique Entry using COUNTIF function
• Find Duplicates using COUNTIF Function
• Running Count
Unit 4: SUMPRODUCT Function, logical Functions and Grouping and
ungrouping in Excel
• SUMPRODUCT, SUMIF, SUMIFS, Group, ungroup and Subtotal, Freeze Panes and Split
• Learn to use SUMPRODUCT for COUNT, SUM, AVERAGE, COUNTIF, SUMIF and
AVERAGEIF etc.
• Group Rows
• Group Columns
• Ungroup Rows and Columns
• Subtotal Feature
• Subtotal Function
• Logical Functions: IF, Nested IF, AND, OR, NOT, XOR, IFERROR
Unit 5: Text functions in Excel to solve complex text problems, text to column
functionality
• Different TEXT Functions used in Excel
• Using multiple text functions for complex problems
• Text to column functionality of excel
• What If Analysis in Excel
Unit 6: Date Functions in Excel, creating dynamic calendar for whole months
and years
• Date Functions in excel.
• Create and Design Calendar using Different Date Functions (WEEKDAY, WEEKNUM,
EOMONTH, EDATE) in excel.
• Calculate Age using DATEDIF Function
• count working days using NETWORKDAYS Function
Unit 7: LOOKUP, VLOOKUP, HLOOKUP function and OFFSET functions
• VLOOKUP, HLOOKUP, LOOKUP, OFFSET AND CHOOSE
• 30 Different usage of VLOOKUP
• VLOOKUP with Exact Match
• VLOOKUP with Approximate Match
• OFFSET function with drop down list
Unit 8: Array formula, INDEX, MATCH, SMALL, LARGE, ROW and
COLUMN functions
• Advance uses of Array Formula INDEX, MATCH, SMALL, LARGE,
• ROW,COLUMN
• Use of INDEX Formula
• To get INDEX number using MATCH function
• SMALL and LARGE Functions with array
• Difference and use of ROW and ROWS functions
• COLUMN and COLUMNS function
• Lookup image using INDEX Function
Unit 9: Advanced Conditional Formatting and Data Bars
• Advanced Conditional Formatting
• Highlight Cell Rule
• Text that contains
• Highlight Duplicate cell value
• Top/Bottom Rules
• Data Bars
• color Scale
• Icon Set
• using formula in Conditional Formatting
Unit 10: Advanced Formula, uses of INDIRECT function, creating range by
text strings
• Advance Formula
• Create Dependent Validation List using INDIRECT function
• Create range by TEXT strings
• Use of ACTIVEX Control
• Create Dashboard using INDIRECT and ACTIVEX Control
Unit 11: Working on Pivots, creating Pivots from different sources and
adding fields into it
Pivot table, Slicer, GETPIVOTDATA function, Pivot Chart
• Design pivot table
• use of slicer
• Add Calculated Field and Item in Pivot Create Pivot table from multiple sheets
• Create Pivot table from External Data Sources
• Introduction to Different types of Pivot Charts
• Dynamic Pivot Charts
Unit 12: Creating dashboards and reports by using excel features and
functions, live experience to create dashboard
• Special Class for Dashboard Creation
• How to Design Dashboard using Pivot table, Pivot Chart
• Use of ACTIVEX Control in Dashboard
• Effective way of using Color Combination
• Use of Binary format Excel