Excel Training: Unlocking Advanced Skills
Master Microsoft Excel 2021 with our Advanced Excel Training
Welcome to Info Trek’s Advanced Excel Training, your gateway to unlocking the full potential of Microsoft Excel 2021. In this comprehensive course, we take you on a journey from spreadsheet proficiency to Excel mastery. Whether you’re a clerk, an executive, or a manager, our program is tailored to elevate your Excel game and empower you to conquer complex data challenges.
What You'll Learn
Our course covers a wide array of advanced Excel techniques, from organizing and outlining data to diving into data analysis with pivot tables, goal-seeking, and solver tools. You’ll also harness the power of functions like XLOOKUP, INDEX, MATCH, and learn to link, consolidate, and combine data seamlessly. Plus, you’ll create compelling visual reports, navigate large workbooks, and secure your worksheets and workbooks effectively.
By the end of this program, you’ll be equipped to streamline your workflow, make data-driven decisions, and stand out as an Excel expert in your professional journey. Join us today and embark on a transformative Excel training experience that will set you apart in the fast-paced corporate world. Don’t miss the chance to supercharge your skills and achieve Excel-lence!
Are you ready to excel in Excel? Enroll now and let’s dive into the world of data mastery!
Course Details
Course Code: EX21A-2; Instructor-led
Audience
This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.
Prerequisites
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
- Have attended Microsoft Excel – Foundation & Intermediate Level; OR
- Able to create simple to complex formulas and functions
- Able to validate data in a Worksheet
- Able to apply Filter data using Auto & Advanced Filters
- Able to clean Duplicate Records
Methodology
This program will be conducted with interactive lectures, PowerPoint presentation, discussion and practical exercise.
Course Objectives
Upon completion of this program, participants should be able to:
- Create Subtotal Using the Subtotal Function
- Analyze data Using Pivot Tables
- Perform What If Analysis using:
- Goal Seek
- Solver
- Input Table
- Scenarios
- Use XLOOKUP function to extract data
- Nesting INDEX and MATCH Function
- Combining & Consolidating Data
- Summarizing & Analyzing with Pivot Table and Pivot Charts
Outlines
Module 1: Organize Summary and Details on Your Data
Topic A: Outlining and Grouping Data
- Using Automatic Outlining
- Displaying and Collapsing Levels
- Grouping Data Manually
- Creating Subtotals
Module 2: Planning, Forecasting & Projecting of Your Data
Topic A: Using Data Analysis Tools
- Using a One or Two Input Data Table
- Using Goal Seek
Topic B: Exploring Scenarios
- Creating a Scenario
- Editing Scenarios
- Creating Scenario Summary Report
Topic C: Using Solver
- Generating Reports with Solver
- Changing Solver Values
- Managing Solver Constraints
- Using Solver as a Goal Seek Tool
Module 3: Retrieve, Compare or Compiling Data with Functions
Topic A: Using the XOOKUP Function
- Using XLOOKUP to Find Data
- Find Exact Match with XLOOKUP
- Find Closest Match with XLOOKUP
Topic B: Alternative LOOKUP Function
- Using the INDEX Function
- Using the MATCH Function
- Combining the MATCH and INDEX functions
Topic C: Linking, Consolidating, and Combining Data
- Linking Workbooks
- Consolidating Workbooks
- Combining Worksheets
Module 4: Present Data with Visual Report
Topic A: Create Combo Chart
- Comparing 2 Types of Values with high Variance
- Creating Combo Chart
- Editing Combo Chart
Module 5: Summarizing and Analyzing Data with PivotTable, Slicer and PivotCharts
Topic A: Create a PivotTable
- Create PivotTable Dialog Box
- PivotTable Fields Pane
- Summarize Data in a PivotTable
- Show Values as Functionality of a PivotTable
- Personalize the default PivotTable layout
Topic B: Filter Data by Using Slicer & Timeline
- Insert Slicers Dialog Box
- Insert Timeline
Topic C: Analyzing Data with PivotChart
- Creating PivotChart
- Editing a PivotChart
Module 6: Navigating Large Workbooks
Topic A: Linking Workbooks
- Inserting Hyperlinks
- Editing Hyperlinks
- Formatting Hyperlinks
- Using Hyperlinks in Excel
Module 7: Prevent User Changes with Protection
Topic A: Protecting Worksheet and Workbook
- Lock & Unlock Cells
- Protecting the Current Sheet
- Allow users to edit range
- Protecting an Entire Workbook
- File Protection
