Advanced MS Excel Training Outline
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
