Advanced MS Excel
Training Outline
Course Duration: 16 Hours, 2 Days
Course Trainer: Ahmad Iqbal, ACCA, CMA
Learnings for Day 1
Introduction
o History & Introduction of Spreadsheets & Excel
o Benefits of Excel to Business
Basic Formula & Function
o Difference between a Formula & Function
o How to Write a Formula & Function?
o Core Excel Functions
o How to Nest Functions
o Relative, Absolute & Mixed Referencing
o Defining Names and Using them in Functions
o Working with Name Manager
Formatting Techniques
o Basic & Advanced Formatting Tools
o Using the Format Dialog to Maximum Use
o Basic Dates & Time Formatting
o Customized Number Formatting
o Appling Predefined Styles & Themes
o Inserting Pictures, Objects & Smart Art
o Formatting & Designing Tips for Pictures, Objects & Smart Art
Dates & Time
o The Science of Dates in Excel
o Dates Formatting in Detail
o Calculations for finding Due Date etc.
o Very Useful Date Functions
o Working of Time in Excel
o Time Formatting in Detail
o Useful Time Functions
Learnings for Day 2
Efficient Handling of Data
o Quick Data Selection Techniques Use of Ctrl & Shift Keys
o Quick Data Entry Techniques
o Use of Paste Special
o Using the Fill Handle
o Accelerating Copy Paste
o Managing a Large Quantity of Data
o Excel Advanced New Feature:
Flash Fill & Quick Analysis
o Advanced Sorting Techniques
o Advanced Filtering Techniques
o Advanced Use of Find & Replace
Working with Tables
o Performing Calculations with Tables
o Creating Dynamic Data Range with Tables
o Using Tables in Formulas & Functions
o Working with Table Tools
o Using Slicers in Tables
Charts & Graphs
o Creating a Chart in no time!
o Most Commonly used Charts
o Understanding & Using Chart Elements
o Working with Chart Design & Layout
o Learning to Use Chart Tools
o Creating a Dynamic Chart
Text Functions
o UPPER, LOWER, PROPER Function
o TRIM, REPT & CONCATENATE Function
o Using the Text to Column Feature
Learnings for Day 3
IF Function with AND & OR
o Performing a Simple IF Calculation
o Assessing more than one criteria with Nested IF Function
o Using the AND Function
o Nesting AND Function with IF Function
o Using the OR Function
o Nesting OR Function with IF Function
o Nesting AND & OR Function with IF Function
Lookup Functions
o VLOOKUP and HLOOKUP
o VLOOKUP Limitations
o Combining INDEX & MATCH Function for Complex Lookup Problems
o Learning SUMIF, AVERAGEIF, COUNTIF
o Learning SUMIFS, AVERAGEIFS & COUNTIFS Functions
o Learning OFFSET and CHOOSE Function
Conditional Formatting & Tables
o Highlighting Positive/Negative Values
o Highlighting Top/Bottom 10 Values
o Highlighting Dates, Specific Text and Duplicates
o Using Icon Sets, Data Bars & Color Scales
o Applying & Managing Specific Formatting Rules
Pivot Tables
o Analyzing Data with Pivot Tables
o Walking through Report Builder Layout
o Performing Calculations with Pivot Table Data
o Managing & Presenting Pivot Tables Data
o Working with Pivot Charts
o Excel Advanced New Feature: Slicers & Timeline
Learnings for Day 4
Data Validation
o Creating Drop Down List
o Regulating & Managing the Entry of Data
o Regulating Data Entry with Custom Validations
o Creating a Custom Input Message
o Creating a Custom Error Alert
o Finding Invalid Data in Validation Process
o Managing & Processing Invalid Data
What-IF Analysis
o Using the brilliant Goal-Seek Feature
o Use of Data Tables to Display Probable Results
o Using Scenario Manager to Summarize Results of Varying Scenarios
o A Sneak Peak of Excel Solver Model!
Auditing Spreadsheet
o Identifying the Source Cell
o Tracking Changes made to Data
Managing Worksheets
o Using the 3D Formula
o Creating a Hyperlink
o Managing Worksheets and Workbook
Printing
o Printing Layouts & Tools
o Working with Margins & Scaling
Security
o Protecting Workbook with Password
o Protecting Your Data with a Password
o Restricting Editing in Data
Macros
o What is a Macro?
o Automating Tasks with Macros
o Recording & Running/Executing a Macro
o Creating Buttons to Run a Macro
