Posted on by

Handling Large Data Sets

Introduction

This training is designed to provide senior managers with the skills necessary to analyze and interpret data using Excel.

Participants will learn how to import and organize data, create and modify tables, use formulas and functions to perform calculations, and create charts and graphs to visualize data.

Additionally, the training will cover advanced topics such as PivotTables, data modeling, Power Query, Power Pivot, and What-If Analysis.

Course Objective

Upon completion of this course, participants will be able to:

  1. Import data into Excel and organize it for analysis
  2. Create and modify tables to manage data more effectively
  3. Use formulas and functions to perform calculations and manipulate data
  4. Create charts and graphs to visualize data and identify trends
  5. Understand advanced concepts such as PivotTables, data modeling, Power Query, Power Pivot, and What-If Analysis

Introduction to Data Analysis in Excel

  • Organizing data for analysis
  • Basic data cleaning and preparation techniques

Formulas and Functions

  • Excel formulas and functions
  • Using logical functions to manipulate data
  • Text functions for cleaning and formatting data
  • Date and time functions

Charts and Graphs to present the data.

  • Creating charts and graphs to visualize data
  • Choosing the right chart type for your data
  • Customizing charts to highlight important information.
  • Identifying trends and outliers in data

PivotTables and Data Modeling

  • Creating and manipulating PivotTables to summarize and analyze data
  • Understanding data modeling and its importance in data analysis
  • Creating and using data models to analyze large datasets
  • Using Excel add-ins to extend functionality

Power Query & Power Pivot for advanced data analysis

  • Power Query: a data transformation and cleaning tool that helps you easily connect to and transform data from various sources.
    • Quickly clean and shape data with an intuitive interface
    • Combine data from multiple sources, like Excel sheets, PDF, Folders, CSV files, and databases
    • Transform data using over 300 built-in functions and formulas
    • Automatically refresh data with the click of a button
  • Power Pivot: an advanced data modeling tool that helps you analyze large datasets and create complex relationships between data.
    • Quickly analyze large datasets without the need for complicated formulas or macros
    • Create and manage relationships between multiple tables with ease
    • Create custom calculations and measures with DAX formulas
    • Build interactive reports with PivotTables, PivotCharts, and Slicers
    • Work with larger datasets without the need for complex formulas or macros
    • Create complex relationships between data that can help you gain insights and make better decisions
    • Build interactive reports and dashboards that can help you easily communicate your findings to others

What-If Analysis

  • Understanding the importance of What-If Analysis in decision making
  • Creating and using scenarios to analyze data
  • Goal Seek and Solver: using these tools to find optimal solutions
  • Using data tables to perform sensitivity analysis