Learn how to display large quantities of data easily with our Excel Business Intelligence training course.
This course was created by popular demand, featuring Power Pivot, Power Query, Power View and even more exciting data analysis features in Excel.
For a full list of topics you can view and download our course outline.
Many of the topics covered are only available in Office Professional Plus 2013 or higher. If you have an earlier version of Office we do not recommend taking this course.
What Will I Learn?
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.
You’ll learn about:
- Creating a Power Pivot model by importing data from various sources.
- Understanding and managing data relationships.
- Creating and editing hierarchies.
- Inserting calculated columns using Data Analysis Expression (DAX) formulas.
- How to Insert, modify, and format PivotTables and PivotCharts.
- Creating and editing Measures (DAX aggregate functions).
- Creating report dashboards using slicers, timelines and KPIs.
- Using Power View, Power Maps and Power Query tools.
Am I ready for this course?
You need to be a proficient user of Excel at least to our Advanced level standard.
We do send out a pre-course assessment so you can be sure you are at the right level for this course.
To be able to get the most 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 the 2010 version of office however there is a big difference compared to the 2016 version which we train on. We train on 2016 as it is the latest and most supported version from Microsoft.
Your Excel Training Day
Our courses run from 9.30am to roughly 4.15pm with refreshments throughout the day and a break for lunch.
We offer a relaxed, supportive learning environment, fully air-conditioned facilities and some of the nicest instructors on the planet.
Also, you’ll receive:
✔ A full-colour manual covering everything in the course so that you can recap.
✔ A USB stick for the course exercises to let you practice more in your own time.
✔ A snazzy Certificate of Attendance to show that you trained with the best.
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