Excel Business Intelligence (BI)

MS Excel Training Course IconThis one day intensive workshop will provide you with the knowledge to make best use of Excel’s Business Intelligence tools to mash up, analyse and visualise your data. By the end of this course you will be able to use Excel Power Pivot tools confidently. Many of the topics covered are only available in Office Professional Plus 2013 or higher. If you have an earlier version we do not recommend taking this course.

This one day intensive workshop will provide you with the knowledge to make best use of Excel’s Business Intelligence tools to mash up, analyse and visualise your data. By the end of this course you will be able to use Excel Power Pivot tools confidently and be able to:

  • Create a Power Pivot model by importing data from various sources
  • Understand and manage data relationships
  • Create and edit hierarchies
  • Insert calculated columns using Data Analysis Expression (DAX) formulas
  • Insert, modify, and format PivotTables and PivotCharts
  • Create and edit Measures (DAX aggregate functions)
  • Create report dashboards using slicers, timelines and KPIs
  • Use Power View, Power Maps and Power Query tools

Anyone wishing to be able to display large quantities of data in an easy to view format.

Our courses run from 9.30 am to approximately 4.30pm. While you are with us we are focused on offering you a relaxed and productive learning environment. When you book a course with us you get the following:
  • A full colour Excel training manual.
  • The exercises that you have worked on during your course to allow you to practice afterwards.
  • USB stick to take the exercises away (where relevant).
  • Freshly prepared lunch
  • Refreshments though out the day.
  • Training in fully air conditioned facilities from an experienced Excel instructor.

You need to be a proficient user of Excel at least to our Intermediate level standard, preferably advanced. We do send out a pre-course assessment to ensure that everyone is at the right level

To be able to get the best from this course you need to have Office Professional Plus 2013 or higher installed on your PC (we do provide the PCs for you to train on here). It is possible on 2010 but there is a big difference and we train on version 2013 so you may not be able to take back all of what you have learned and use it quite so easily.

 

We start this session with an overview of the Excel BI tools and ensure that you know how to enable them.  We look at the data we will be using to create the Power Pivot reports throughout the day and answer the following:

  • What is Power Pivot for Excel and how is it different to PivotTables?
  • What are the differences between Excel 2013 and 2016 Power Pivot tools?
  • How do I create a Power Pivot Data Model by importing data from sources including Access databases, Excel and Text files?
  • What other data sources can I import into the model?
  • What data types does Power Pivot support and how does this impact my existing data
  • How do I create table relationships and why this is important?
  • How do I create a Date Table and why this is important?

In order to use Power Pivot effectively, data needs to be in the correct format.  In this part of the workshop we share tips on how to:

  • Use Power Query to import data into the Data Model
  • Save and work with ‘Steps’
  • Transpose data to swap rows and columns
  • Fill data down to populate missing values
  • Unpivot columns to flatten a “cross tab” table
  • Use Text Functions to clean and trim data

It is important that you can use the Power Pivot tool effectively and know how to how to:

  • Open an existing model
  • Rename a Table or Column
  • Hide or Freeze Columns
  • Sort and filter data in the model
  • Create Hierarchies
  • Refresh data
  • Edit the data source

During this part of the workshop you learn how to:

  • Insert Pivot Tables and Charts using different Power Pivot layouts
  • Use Slicers and Timelines to Filter Data and connect these to more than one PivotTable
  • Use Charts, Slicers, Timelines and conditional formatting to create interactive Dashboards

This session is an overview of Data Analysis eXpressions (DAX) formula language and covers:

  • Creating Calculated Columns & Calculated Fields
  • Implicit and Explicit Calculated Fields
  • Using DAX aggregate functions in a Power Pivot PivotTable
  • Creating some useful DAX functions and using them to create KPIs

The workshop finishes with a brief overview of Power View and Power Map (known as 3D maps in Excel 2016); topics include:

  • The Power View Field List
  • Using Text Boxes
  • Creating Tables
  • Changing Table Visualisations
  • Using a Matrix and Enable Drill Down
  • Using Cards; Setting up Titles and Pictures for Cards
  • Creating Chart Visualisations and Interactive Charts
  • Creating Power View Slicers
  • Using Tiles
  • Using Power View Filters
  • Working with the Power Map tools

Related courses