Advanced Data Analysis AND Visualisation with Excel:
Discover Strategic Business Insights

KEY TOPICS COVERED
  • Data Models
  • Power Pivot, Power Query, Power View & Power Map
  • DAX (Data Analysis Expressions)

Workshop Overview

In this Excel data analysis course, you use advanced Excel techniques to build sophisticated spreadsheets. You learn to perform “what if” analyses, leverage features and apply functions, manipulate PivotTables to analyze large amounts of data, and present your results to make better business decisions. You also learn how to automate processes and enhance worksheet models.

Do you know what “Power Pivot” is? Do you know that Power Pivot is available for all versions of Excel. You will learn how to use Power Pivot to import and merge large amounts of data, as well as summarize and aggregate data.

You will also gain the knowledge and skills to present strategic data, provide better business insights, and empower informed decision-making. The workshop covers basic concepts of Power Pivot, data models and Data Analysis Expressions (DAX) formulas.

Why You Should Attend?

√  Do you work with multiple data sources?
√  Are your source data split into multiple tables?
√  Do you work with very large data sets?
√  Do you use PivotTable on a regular basis?
√  Do you use V-Lookup to create a single report from multiple data sets?
√  Do you find calculations in PivotTable cumbersome with Calculated Items and Calculated Fields?

If you answered ‘yes’ to any of these then you need to level up your Excel skills with this workshop.

Key Takeaways

By the end of the course, participants will be able to:

  • Transform and present complex data
  • Actualize a data model from multiple sources
  • Shape and filter tables to prepare data for analyses
  • Mine and uncover hidden information with DAX
  • Present business insights with dashboards
Prerequisites

Participants should be comfortable with basic Excel tools and functions. At a minimum, participants should know how to navigate confidently in Excel, create and use simple formulas and link between workbooks.

Methodologies

Clear explanation of theories coupled with hands-on exercises for a firm grasp of Excel data analysis through:

  • Practical Examples
  • Case Studies
  • Short Exercises
  • Group Discussions
Who Should Attend

This workshop is crucial for analysts, managers, business owners, executives, and anyone responsible for analyzing data to gain business insights. This training is highly recommended for:

Excel Users:
If you use Excel daily and want to exploit the full power of the Excel to improve reporting capabilities.

IT and BI People:
Professionals that are in charge of the data warehouse management of their company and need to understand how to provide support to analysts who need to extract data from PowerPivot.

Software Used

The course is demonstrated using Microsoft Excel 2016, but participants can use Excel 2010 or 2013 if they prefer.

ProgramME Agenda

Session 1: ANALYSING DATA WITH FUNCTIONS – SUMMARISING BUSINESS DATA WITH FUNCTIONS

  • Identifying the correct statistical function to aid analysis
  • Applying basic financial functions
  • Differentiating serial dates and date presentations
  • Calculating the number of working days

Session 2: ANALYSING DATA WITH FUNCTIONS – CONTROLLING CALCULATIONS AND NESTED FORMULAS

  • Interpreting data variations with the IF function
  • Streamlining calculations with referencing
  • Developing nested functions for multiple conditions
  • Capturing information with lookup functions
  • Applying techniques to implement and troubleshoot nested calculations

Session 3: TROUBLESHOOTING AND ENHANCING PROFESSIONAL WORKBOOKS

  • Deciphering and correcting functions for data integrity
  • Accurately interpreting calculations
  • Implementing Names to enhance your workbook model
  • Monitoring KPIs using conditional formatting

Session 4: OPTIMISING WORKBOOK MODELS WITH “WHAT-IF” ANALYSIS

  • Planning for contingencies
  • Managing variables in worksheets with Scenarios
  • Comparing and contrasting different data sets with Scenarios reports
  • Quantifying variables in a workbook model
  • Determining the magnitude of a variable with Goal Seek to achieve an end value
  • Calculating the optimum variable values in a worksheet model with Solver

Session 5: POWER PIVOT CONCEPTS

  • Extracting information from data with Power Pivot
  • Uncovering data interpretation issues

Session 6: POWER PIVOT DATA MODEL

  • Data Terminology
  • Defining a consolidated view of data
  • Generating a data mashup from structured and unstructured data sources into a data model
  • Deriving relationships from data sources with the Relationship tool and the Diagram View
  • Acquiring data from related tables
  • Defining calculated columns

Session 7: POWER QUERY: FIXING COMMON DATA ISSUES WITH POWER QUERY

  • Extracting, Transforming and Loading (ETL) data
  • Converting data formats with Power Query steps
  • Parsing columns to aid analysis
  • Removing duplicates from a data set
  • Constructing a single data set from multiple sources with the same field headings

Session 8: IMPLEMENTING DAX FUNCTIONS IN POWER PIVOT

  • Expressing information with measures
  • Exposing hidden information from data
  • Troubleshooting and debugging DAX calculations
  • Quantifying and mining information with DAX functions
  • Summarizing and aggregating data from other tables with the X functions
  • Evaluating expressions with the CALCULATE() function and filter functions
  • Substituting values with the SWITCH() function.

Session 9: MINING FOR INFORMATION WITH DATA AND TIME ANALYSIS

  • Grouping dates for time analysis
  • Comparing and categorizing time periods with Time Intelligence functions

Session 10: PRESENTING INFORMATION WITH DASHBOARDS

  • Articulating and analyzing data
  • Drilling down into data using a hierarchy
  • Managing data with perspectives
  • Identifying patterns and trends in your Power Pivot data with Power View Charts
  • Classifying data into different geographical regions

Session 11: DESIGNING EFFECTIVE DASHBOARDS

  • Contextualizing measures with PivotTable slicers and Power View filters
  • Documenting structures with Hierarchical Diagrams
  • Visualizing and comparing performance matrices with Power View multipliers
  • Globalizing location-based results to identify trends and patterns on a 3D scale with Power Map