All courses
Hamburger Icon

Intermediate Excel

Course Summary

This course builds on foundational knowledge, providing participants with advanced skills for efficient data analysis and visualization. Participants will also learn to use Power Query for data import and transformation.

Purpose
Expand knowledge of Excel with advanced skill for data analysis and visualization.
Prerequisites
Proficiency working with Excel workbooks, entering and formatting data, and using basic functions.
Ability to sort and filter data using AutoFilter and custom sorting.
Ability to create basic charts and graphs in Excel.
Basic understanding of PivotTables and their use for data summarization.

Role
Business Analyst | Admin Support | Data Engineer
Skill level
Intermediate
Style
Lecture | Hands-on Activities
Duration
2 days
Related technologies
MS Office | Office 365

 

Learning objectives
  • Use advanced data analysis techniques with nested functions, data tables, and what-if analysis.
  • Demonstrate improved charting skills with advanced chart types and customization.
  • Unlock the full potential of PivotTables with advanced features and dynamic filtering.
  • Optimize array formulas and INDEX-MATCH lookups.
  • Use Power Query for data import and transformation.

What you'll learn:

In this course, you'll learn:
  • Nested Functions and Formulas
    • Explore advanced nested functions for intricate calculations.
    • Master the use of multiple functions within a single formula.
  • Data Tables and What-If Analysis
    • Learn to create data tables for different scenarios.
    • Utilize Goal Seek and Solver for what-if analysis.
  • Advanced Charting Techniques
    • Dive into advanced chart types like combo charts and sparklines.
    • Customize charts with trendlines, data labels, and more.
  • Advanced PivotTable Features
    • Enhance PivotTables with calculated fields and items.
    • Utilize slicers for dynamic filtering.
  • Advanced Data Validation
    • Implement dynamic dropdown lists and dependent validation.
    • Explore custom formulas for data validation.
  • Data Analysis with PivotCharts
    • Create dynamic PivotCharts for visual data analysis.
    • Link PivotCharts with PivotTables for interactive reporting.
  • Advanced Formulas for Efficiency
    • Harness array formulas for complex data manipulation.
    • Understand INDEX and MATCH for efficient lookups.
  • Power Query Basics
    • Introduction to Power Query for data import and transformation.
    • Clean and shape data using Power Query.

Intermediate Excel

real world content

Real-World Content

Project-focused demos and labs using your tool stack and environment, not some canned "training room" lab.
expert practitioners

Expert Practitioners

Industry experts that bring their battle scars into the classroom.
experiential learning

Experiential Learning

More coding than lecture, coupled with architectural and design discussions.
fully customized

Tailored Outlines

Once-size-fits-all doesn't apply to training teams. That's where we come in!

Dive in and learn more

When transforming your workforce, it’s important to have expert advice and tailored solutions. We can help. Tell us your unique needs and we'll explore ways to address them.

Let's chat

By filling out this form and clicking submit, you acknowledge our privacy policy.