Microsoft Excel (Advanced)
Course Objective:
Microsoft Excel Advanced course will equip you with hands-on knowledge of advanced data analysis, collaborate on workbooks with other users and automate workbook functionality.
Target Audience
This course is designed for students who already have foundational knowledge and skills in Microsoft Excel and who wish to begin taking advantage of some of the higher-level functionality in Microsoft Excel to analyze and present data.
Training Methodologies
- Training is hands-on using easy-to-follow step-by-step instructions.
- Instructor-Led Training (ILT) on live exercises of each topic.
Trainer
Training will be conducted by our experienced local trainer who has more than 10 years of experience with relevant certification.
Course Content
Lesson 1: Conditional Formats and Templates
- Apply Conditional Formatting
- Templates
Lesson 2: Advanced Formulas, Functions and Conditional Formatting
- Date Function
- Truncate Text Data in Microsoft Excel with the LEFT Function
- LEFT Function
- RIGHT Function
- Using Logical Functions
- VLookup
- HLookup
Lesson 3: Working with Range Names
- Assign a Range Name
- Edit a Range Name
- Consolidating Data
Lesson 4: Organizing and Analyzing Datasets and Tables
- Create and Modify Tables
- Perform a Quick Sort
- Custom Sort
- Filter Data Apply Multiple Filters Advanced Filtering
- Custom AutoFilter
- Groups and Subtotals
Lesson 5: Visualizing Data with Charts
- Types of Charts
- Create a Chart
- Chart Layout and Style
- Create Sparklines Modify Sparklines
Lesson 6: Using Automated Analysis Tools
- Use the Goal Seek Feature
- Other Types of What-If Analysis
Lesson 7: Sharing and Collaborate Workbooks
- Sharing Workbook
- Understanding Track Changes
- To Turn on Track Changes
- Accept/Reject Changes
- Turning off Track Changes
- Add, Edit and Hide Comments Delete a Comment
Lesson 8: Protecting Workbooks
- Document Inspector
- Protect a Cell
- Protect Workbook
Lesson 9: Analyzing Data with PivotTables, Slicers, and PivotCharts
- Create a PivotTable
- Pivoting Data
- To Add Filters
- To Add Slicers PivotCharts
Lesson 10: Recording and Using Macro
- Display the Developer Tab
- Macro Security Setting
- Record Macros
- Edit Macros
- Run Macros
- Placing a Macro on the Quick Access Toolbar
- A Quick Introduction to VBA Programming in Microsoft Excel