Course Outline of MS Excel Training Outline

Course Duration: 16 Hours, 2 Days

Course Trainer: Ahmad Iqbal, ACCA, CMA

Learnings for Day 1

• Introduction
• History & Introduction of Spreadsheets & Excel
• Benefits of Excel to Business

• Basic Formula & Function
• Difference between a Formula & Function
• How to Write a Formula & Function?
• Core Excel Functions
• How to Nest Functions
• Relative, Absolute & Mixed Referencing
• Defining Names and Using them in Functions
• Working with Name Manager

• Formatting Techniques
• Basic & Advanced Formatting Tools
• Using the Format Dialog to Maximum Use
• Basic Dates & Time Formatting
• Customized Number Formatting
• Appling Predefined Styles & Themes
• Inserting Pictures, Objects & Smart Art
• Formatting & Designing Tips for Pictures, Objects & Smart Art

• Dates & Time
• The Science of Dates in Excel
• Dates Formatting in Detail
• Calculations for finding Due Date etc.
• Very Useful Date Functions
• Working of Time in Excel
• Time Formatting in Detail
• Useful Time Functions

Learnings for Day 2

• Efficient Handling of Data
• Quick Data Selection Techniques – Use of Ctrl & Shift Keys
• Quick Data Entry Techniques
• Use of Paste Special
• Using the Fill Handle
• Accelerating Copy Paste
• Managing a Large Quantity of Data
• Excel Advanced New Feature:
  • Flash Fill & Quick Analysis
• Advanced Sorting Techniques
• Advanced Filtering Techniques
• Advanced Use of Find & Replace

• Working with Tables
• Performing Calculations with Tables
• Creating Dynamic Data Range with Tables
• Using Tables in Formulas & Functions
• Working with Table Tools
• Using Slicers in Tables

• Charts & Graphs
• Creating a Chart in no time!
• Most Commonly used Charts
• Understanding & Using Chart Elements
• Working with Chart Design & Layout
• Learning to Use Chart Tools
• Creating a Dynamic Chart

• Text Functions
• UPPER, LOWER, PROPER Function
• TRIM, REPT & CONCATENATE Function
• Using the “Text to Column” Feature

Learnings for Day 3

• IF Function with AND & OR
• Performing a Simple IF Calculation
• Assessing more than one criteria with Nested IF Function
• Using the AND Function
• Nesting AND Function with IF Function
• Using the OR Function
• Nesting OR Function with IF Function
• Nesting AND & OR Function with IF Function

• Lookup Functions
• VLOOKUP and HLOOKUP
• VLOOKUP Limitations
• Combining INDEX & MATCH Function for Complex Lookup Problems
• Learning SUMIF, AVERAGEIF, COUNTIF
• Learning SUMIFS, AVERAGEIFS & COUNTIFS Functions
• Learning OFFSET and CHOOSE Function

• Conditional Formatting & Tables
• Highlighting Positive/Negative Values
• Highlighting Top/Bottom 10 Values
• Highlighting Dates, Specific Text and Duplicates
• Using Icon Sets, Data Bars & Color Scales
• Applying & Managing Specific Formatting Rules

• Pivot Tables
• Analyzing Data with Pivot Tables
• Walking through Report Builder Layout
• Performing Calculations with Pivot Table Data
• Managing & Presenting Pivot Tables Data
• Working with Pivot Charts
• Excel Advanced New Feature: Slicers & Timeline

Learnings for Day 4

• Data Validation
• Creating Drop Down List
• Regulating & Managing the Entry of Data
• Regulating Data Entry with Custom Validations
• Creating a Custom Input Message
• Creating a Custom Error Alert
• Finding Invalid Data in Validation Process
• Managing & Processing Invalid Data

• What-IF Analysis
• Using the brilliant Goal-Seek Feature
• Use of Data Tables to Display Probable Results
• Using Scenario Manager to Summarize Results of Varying Scenarios
• A Sneak Peak of Excel Solver Model!

• Auditing Spreadsheet
• Identifying the Source Cell
• Tracking Changes made to Data

• Managing Worksheets
• Using the 3D Formula
• Creating a Hyperlink
• Managing Worksheets and Workbook

• Printing
• Printing Layouts & Tools
• Working with Margins & Scaling

• Security
• Protecting Workbook with Password
• Protecting Your Data with a Password
• Restricting Editing in Data

• Macros
• What is a Macro?
• Automating Tasks with Macros
• Recording & Running/Executing a Macro
• Creating Buttons to Run a Macro