fbpx

CREATING DASHBOARDS WITH
EXCEL ADVANCED MASTERCLASS:

Automate repetitive steps (no coding) &
instantly update dynamic dashboards/reports

Download brochure

Click on the links below to request for Excel Dashboard Advanced Training brochures

11-12 Nov Live Online Class
Key Topics Covered
  • Power Query
  • Power Map
  • Automation techniques
  • Interactive dashboards
  • Dynamic drop-down lists
  • Advanced chartings
why you should attend? (ADVANCED EXCEL training)

Do you spend hours just to update your daily, weekly or monthly Excel reports?

If so, you are doing it wrong! All you need are a few key techniques to set up your reports, then you can simply click the refresh button and the reports will be updated. Job done!

One of the key techniques is Power Query. Power Query has been a built-in feature since Excel 2016! It is Get & Transform within the Data tab in the ribbon. Unfortunately many people haven’t explored what Power Query has to offer! For examples:

  • Do you work with multiple data sources and tables?
  • Do you use V-Lookup to create a single table from multiple data sets?
  • Do you use copy & paste to combine multiple tables into one?
  • Do you spend hours cleaning and processing your data, only to repeat the same steps when the data is updated

If you answered ‘yes’ to any of these, Power Query will be your game-changer.

These are some of the many functions of Power Query:

  • Connect to multiple data sources
  • Reshape, transform and clean data
  • Combine tables using merge or append
  • Record and automate transformation steps

In addition, elevate your dashboard reporting skills with this advanced masterclass by:

  • Building dynamic interactive dashboards where users can customise their dashboard views
  • Enhancing user experience with form controls (eg. scroll bars, spinners, option buttons) and dynamic drop-down lists
  • Using advanced charting techniques like helper columns, error bars, axis label customisation
  • Mastering advanced Excel formulas to gain deeper insights from your data
  • Transforming large amount of data into succinct visual content
Key Takeaways (ADVANCED EXCEL training)

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

  • Use Power Query to clean and transform data
  • Save time by automating routine data wrangling steps
  • Combine multiple data sets into one table for analysis
  • Build impressive dynamic dashboard reports
  • Use Power Map to visualise geographical data over time
  • Creating advanced scrolling charts through time
Methodologies

Clear explanation of theories coupled with hands-on exercises for a firm grasp of
Power Query and advanced dashboards through:

  • Practical Examples
  • Case Studies
  • Short Exercises
  • Dashboards Projects
Who Should Attend

This workshop is crucial for analysts, managers, business owners, executives, and anyone responsible for analysing data to gain business insights and preparing Excel based dashboards, scorecards or KPI reports. This training is highly recommended for:

  • Directors
  • General Managers
  • Chief Financial Officers
  • Finance Managers
  • Accountants
  • Marketing Managers
  • Sales Managers
  • HR Managers
  • Business Owners
  • Anyone who deals with business, management and financial reporting
Software Used

The course is demonstrated using Excel for Microsoft 365, but participants can use Excel 2016 or 2019 (Windows)

ProgramME Agenda (ADVANCED EXCEL training)

Session 1: INTRODUCTION

  • Overview of training aim, objectives and agenda

Session 2: WHAT IS POWER QUERY?

  • Extracting, Transforming and Loading (ETL) data
  • Power Query vs “normal” Excel
  • Importing data with Power Query
  • Sources of data: Excel, CSV/Text, SQL Database, Web, etc
  • Query Editor ribbon, Navigator pane & Preview grid
  • Query settings panel - recording of transformation steps

Session 3: DATA CLEANING & FORMATTING

  • Data types: Number, Date, Text, True/False
  • Remove Columns, Remove Other Columns & Choose Columns
  • Removing duplicates from a data set
  • Replacing values and errors
  • Null values: Replace, fill or filter out
  • Removing whitespaces and non-printable characters

Session 4: DATA TRANSFORMATION TECHNIQUES

  • Transpose, Unpivot Columns and Pivot Columns: What are the differences? When to use them?
  • Text transformations: Extract texts before/after delimiter, Add Prefix/Suffix, Convert texts to uppercase/lowercase

Session 5: DATA AGGREGATION & CALCULATION

  • Summarise data with Group By (Sum, Median, Min, Max, etc)
  • Custom Columns: Create formulas for computations
  • Index Columns
  • Number columns statistical calculations

Session 6: COMBINING DATA (QUERIES)

  • Difference between merge and append
  • Merge queries (the V-Lookup in Power Query)
  • Append queries (combining tables with the same headers)
  • Use cases of merge and append queries

Session 7: ORGANISING POWER QUERY AUTOMATION

  • Best practices in organising your data sources
  • How to automate your data refresh
  • Save time by importing multiple files located in a folder
  • Duplicate and reference queries
  • Update Power Query for change in location of source data
  • Change the default “Close & Load” options

Session 8: USING THE NEW CHARTS IN EXCEL 2016

  • Waterfall Chart
  • Histogram Chart: Displays frequencies within a data set
  • Pareto Chart: Sorted histogram
  • Box and Whisker Chart: Shows distribution of data into quartiles, highlighting the mean and outliers
  • Treemap: Hierarchical view of your data in rectangles
  • Sunburst Chart: Hierarchical data in a multi-level pie chart
  • 3D Map: Plots geographic and temporal data on a 3D globe, show it over time, and create visual tours.

Session 9: BUILDING INTERACTIVE CHARTS AND DASHBOARDS CONTROLS

  • Using form controls: Combo Box, Check Box, Spin Button, List Box, Option Button, Scroll Bar and Group Box
  • Set up dynamic data validation list
  • Creating dynamic named range with OFFSET and COUNTA
  • Creating dynamic named range with INDEX
  • Using the INDIRECT function
  • Use CHOOSE to return a value from an array based on index number
  • Using ROW(S) and COLUMN(S) for indexing
  • Rank and sort your data using RANK, LARGE and SMALL

Session 10: BUILDING DYNAMIC CHARTS

  • Selecting data with a combo box (or data validation list) for dynamic Excel charting
  • Using a check box to show or hide data or trend lines
  • Scrolling a dynamic excel chart through time
  • Using helper columns to manipulate your data
  • Linked textbox for dynamic chart titles
  • Tricks to avoid crashing lines in line chart
  • Linking legends to cell contents

Session 11: HANDS-ON DYNAMIC DASHBOARD PROJECTS

  • Datasets:
    • HR dataset
    • Banking dataset
    • Bring Your Own Data (BYOD)
Prerequisite

Participants must EITHER
• Have attended Creating Dashboards with Excel Masterclass
OR
• Demonstrate competencies in topics covered in Creating Dashboards with Excel Masterclass by taking a mini quiz