CREATING DASHBOARDS WITH EXCEL AND POWER BI

Course overview

This course is highly interactive from the very beginning to the very end. At every stage delegates will be in no doubt as to the reason for everything we are doing as well as appreciating the ways in which they can apply their new knowledge and skills when they return to work. The course begins by ensuring all delegates are aware of and can apply the most important and fundamental concepts and techniques using both Microsoft Excel
and Microsoft Power BI for the desktop.

Delegates will appreciate this course most of all if they come to it not only with Excel 2016 or Office 365 fully installed but also with the mindset that is ready to contribute to all discussions and being ready to illustrate their discussions with examples and suggestions of their own.

Before the course begins, delegates will be provided with detailed instructions on setting up Excel and downloading and installing power BI and anything else that is appropriate at the time.

Not only is this cause interactive it is intensive and even though it is only 2 days long, there is still a great deal we can achieve.

Who should attend?

  • Financial accountants
  • Management accountants
  • Corporate financiers
  • Investment bankers
  • Financial controllers
  • Sales and marketing staff
  • Auditors
  • Finance staff
  • Commercial bankers
  • Business analysists
  • Financial analysists
  • Statisticians
  • Production managers
  • Professors and other teachers

Course Content

High quality dashboards rely on a number of ideas and skills being used. In this first four modules, we will review the following key skills that everyone needs to understand and apply in order to create and use effective dashboards.
Excel Tables

  • Appreciating why we need them
  • Maximizing their power

Pivot Tables

  • Effectively using
    • Filter
    • Row
    • Column
    • Value
    • Slicers

Charts and Graphs

Best practice visualizations: including linking charts with Excel tables and Pivot tables.

  •  

In addition to exploring the most basic functions in Excel,
we will explore and apply the following, among others

  • IF … and IF(AND()) … IF(OR()) … IF(AND(OR)))
  • SUMIFS()
  • SUMPRODUCT()
  • VLOOKUP
  • FREQUENCY()
  • TRANSPOSE()
  • IFERROR()
  • Dynamic Array Functions
    • UNIQUE()
    • SORT()
    • SORTBY()
    • RANDARRAY()
    • FILTER()
    • SEQUENCE()
  • Flash Fill
  • Data Types
  • Forecast Sheet

  • Importing data from
    • Excel
    • CSV Files
    • The Web
    • Databases
  • Basic Visualizations
  • Tabulation of data
  • Manipulating data: some basic calculation and measures

In this module we will use Power BI, Power Query and Power Pivot as we start to unleash our importing, manipulating, cleaning and analytical skills. Taking data from a variety of sources to ensure your data are clean and ready to use. We will use a variety of techniques in this module including

  • Manually cleaning, by inspection
  • Using Power Query to help with data cleaning
  • Power Query can replace your VLOOKUP functions
  • By using the following data importing techniques, we will use Power Query to optimize our data, create basic models, break down large tables into smaller, more manageable tables
  • Importing data from
    • A worksheet
    • Another Excel file
    • A CSV file
    • A database
    • A web page
  • Unpivoting data
  • Manage data types that Excel sometimes cannot manage
  • Combine tables from within a folder
  • Split complex columns into multiple columns
  • Create functions
  • Create ratio analysis tables
  • And more

The following Power Pivot/Power BI work cannot be done in basic or ordinary Excel and yet it is so powerful yet simple to do!

  • Power Pivot: Creating data models to create relationships between Excel and database tables
  • Power BI can be used as well as and instead of Power Pivot in this context

Delegates are encouraged to bring along or provide links to their own dashboards or dashboards they like or dislike. This will encourage full and free discussion of the kinds of elements needed for a successful dashboard.

We will refer to examples and suggestions from

  • Edward Tufte
  • Stephen Few
  • Microsoft
  • And more

  • Charts for Time
  • Series/Trending
  • Smoothing Data
  • Sparkline's
  • Highlighting Comparisons
  • Frequency Distribution
  • Target v Variance Charts
  • Performance Against Target Range
  • Win/Loss/Draw Conditional Format
  • Text Formulas
  • Dynamic Data Validation List
  • Panel Charts

  • Form Controls Introduction
    • Check Boxes
    • List Boxes
    • Option Buttons
    • Group Boxes
    • Scroll Bar
    • Buttons
    • Combo Boxes
  • INDIRECT Function
  • Dynamic Named Range with OFFSET
  • Dynamic Named Range with INDEX
  • Dynamic Ranges for Charts

  • Pivot Charts
  • Slicers
  • Power Pivot

  • Linking to various data sources through
    • Power Query
    • Power Pivot and
    • Power BI
  • Incorporating New Data Automatically
  • Auto Refresh Pivot Tables

  • Choosing the appropriate visualization(s)
    • Column Chart
    • Line Chart
    • Pie Chart
    • Scatter Chart
    • Step Graphs
    • Pyramid Chart
    • Heatmaps
    • Sunburst chart
    • Histograms
    • Box & Whisker Plots
  • Array Formula
  • Alternative to SUMIFS()
  • LARGE() and SMALL()
  • Array and DAVERAGE
  • Rank Values with Pivot Table
  • Formatting Quickly