Data Analysis and Visualisation with Microsoft  Excel

dotLogic - Data Analysis & Visualisation with Microsoft Excel


Delegates per class: 10-15
Intake frequency:
Next course: Monday, 9th January 2017
Location(s): Belville, Cape Town ↘ Duration: 1 day


$395 / £275 / €350 / R3000

🎓 About this Course

Microsoft Excel is one of the most widely used solutions for analysing and visualizing data. Excel now includes an excellent range of power tools that enable the analysis of large data sets, with improved visualisations and business logic.

In this course, you will learn how to model your data, to gain actionable insight through the use of the latest versions of these new tools; Power Query, Power Pivot and Power BI.

🏆 Course Objectives

After completing this course, you will be able to:

  • Import and clean data from multiple sources
  • Learn about data model creation
  • Shape data for analysis using Power Query and Power Pivot
  • Create basic Data Analysis Expressions (DAX) for calculated columns and measures
  • Explore, analyse and visualise data

🏆 Targeted Audience

This course is ideal for candidates who:

  • find they spend too much time copying and pasting data into Excel
  • need to automate their data manipulation and reporting processes
  • would like to learn some simple best practices for shaping and analysing data
  • are required to unlock the valuable insight hidden in their data

📖 Course Aid & Material

  • Laptop and course material provided

📖 Course Outline

Module 1: Data analysis in Excel

  • Navigating Excel 2016
  • Basic concepts
  • Import, clean and transform data

Module 2: The Excel data model and Intro to DAX

  • How to model your data
  • Columns, rows, tables and relationships
  • Intro to DAX (calculated columns and measures)

Module 3: Importing data from Files/Folders

  • Intro to Power Query
  • Importing data from sources
  • Query editing and refresh

Module 4: Cleaning Data

  • Unpivot, remove, filter, header
  • Preparing data

Module 5: Time Based Analytics with DAX

  • Calculated measures and columns
  • DAX functions
  • Time intelligence

Module 6: Shaping Data

  • Creating date table
  • Creating relationships

Module 7: Visualising Data in Excel

  • Model based visualisations
  • Pivot charts, slicers and graphs
  • Formatting visuals

Module 8: Introduction to Power BI

  • Developing interactive dashboards
  • Bar charts, pie charts, line graphs and score cards