Advanced Excel Dashboards course

Advanced Excel Dashboards course

Introduction

This course teaches participant on how to create interactive Excel dashboards that update with the click of the Refresh button, or with a simple copy and paste of your new data into your spreadsheet. Excel Dashboards provide insight, analysis and even alerts. They are interactive and dynamic, and have a wide range of applications, providing help with project management, customer service, KPI management, forecasting and so on. They usually fit on one page, displaying key trends, comparisons and data in small graphs or tables. This makes them easy to read and digest the information

Duration

5days

Who should attend?

Anyone with an advanced knowledge of Excel who wants to learn how to create dashboards which will enhance their spreadsheets with easily understood and interactive data displays.

Course Objective:

·         Creating Professional Dashboards

·         Pivot Tables and Pivot Charts, Conditional Formatting, Functions, Formulas and Macros

·         Create visualizations charts

·         Understand the principles of data analysis

·         Uncover the tools to analyse data and construct reports using Excel

·         See how to use visualisation techniques to improve presentation of information

·         Study how to condense, present and convey data clearly and succinctly

·         Enhance the efficiency of executing mundane tasks through recording, writing and editing macros

·         Understand the principles of great dashboard design and how to present data vividly

·         Perform advanced and dynamic data validations

·         Design exceptional visualisation charts, dashboards, scorecards, and flash reports

·         Build custom reports using advanced form controls and buttons

Course content

 MODULE 1: ESSENTIAL REPORTING REQUIREMENT SKILLS

  • Advanced pivot charts techniques
  • Multiple consolidation ranges
  • Retrieving external data using Microsoft query
  • The rules of pivot tables and pivot charts
  • Slicer techniques
  • Importing text files using MS query
  • Connecting to access databases
  • Connecting to SQL databases
  • Importing from data connection wizard
  • Importing from Microsoft query
  • Customising connections properties

MODULE 2: BUILDING THE EXCEL DASHBOARD – LOOKUP DATA

  • Looking up customer info
  • Preparing the data using format as table
  • Creating a dropdown menu
  • Looking up data with Excel’s VLOOKUP function
  • Cleaning up data with Excel’s if function
  • Index and match an alternative to VLOOKUP

MODULE 3: BUILDING THE EXCEL DASHBOARD – FILTERING DATA

  • Adding the order history table
  • Formatting orders as a table
  • Using excels advanced filter feature
  • Record macro for advanced filter
  • Modify the VBA filter code

MODULE 4: BUILDING THE EXCEL DASHBOARD – SUBTOTALS

  • Why use Excel’s subtotal function
  • Implementing the subtotal function

MODULE 5: BUILDING THE EXCEL DASHBOARD – PIVOT TABLES AND PIVOT CHARTS

  • Why use pivot tables
  • Summarising order info with pivot tables
  • Prepare a pivot table for customer filter
  • Creating the VBA procedure
  • Declaring VBA variables
  • Assigning values to VBA variables
  • Connecting the filter to the pivot table
  • Customers with no orders error

MODULE 6: BUILDING THE EXCEL DASHBOARD – INTERACTIVE BUTTONS

  • Creating interactive charts with slicers
  • Modifying the chart slicer

MODULE 7: BUILDING THE EXCEL DASHBOARD – FORMATTING

  • Hiding extra worksheets and columns
  • Cleaning up the Excel default settings
  • Protecting the dashboard

MODULE 8: ADVANCED DATA STRUCTURING TECHNIQUES

  • Custom and advanced data validation
  • Creating and managing innovative conditional formatting

MODULE 9: CHARTING AND VISUALISATION TECHNIQUES

  • Creating dynamic labels
  • Using the camera tool
  • Working with formula-driven visualisations
  • Using fancy fonts
  • Leveraging symbols in formulas
  • Working with sparklines
  • Creating unconventional style charts
  • Fancy thermometer charts
  • Coloured chart bars

MODULE 10: BUILDING REPORT SOLUTIONS

  • Conceptualising and understanding report solutions
  • Developing a report solution
  • Configuring spreadsheet report data options
  • Enabling background refresh
  • Refreshing data when opening the file
  • Combo-box data modelling tool
  • List-box data modelling tool
  • Form controls data modelling tools
  • Spinner
  • Option-button modelling
  • Check-box data models
  • Combo and group-box

MODULE 11: MACRO CHARGED REPORTING

  • Recording, editing, testing VBA macros
  • Building a macro-driven reconciliation program
  • Building a budget variance reporting program
  • Building a vendor and invoice analysis report

 General Notes

·         All our courses can be Tailor-made to participants needs

·         The participant must be conversant with English

·         Presentations are well guided, practical exercise, web based tutorials and group work. Our facilitators are expert with more than 10years of experience.

·         Upon completion of training the participant will be issued with Foscore development center certificate (FDC-K)

·         Training will be done at Foscore development center (FDC-K) center in Nairobi Kenya. We also offer more than five participants training at requested location within Kenya, more than ten participant within east Africa and more than twenty participant all over the world.

·         Course duration is flexible and the contents can be modified to fit any number of days.

·         The course fee includes facilitation training materials, 2 coffee breaks, buffet lunch and a Certificate of successful completion of Training. Participants will be responsible for their own travel expenses and arrangements, airport transfers, visa application dinners, health/accident insurance and other personal expenses.

·         Accommodation, pickup, freight booking and Visa processing arrangement, are done on request, at discounted prices.

·         One year free Consultation and Coaching provided after the course.

·         Register as a group of more than two and enjoy discount of (10% to 50%) plus free five hour adventure drive to the National game park.

·         Payment should be done two week before commence of the training, to FOSCORE DEVELOPMENT CENTER account, so as to enable us prepare better for you.

·         For any enquiry at:training@fdc-k.org or +254712260031

 

Start Date: 15/06/2020
End Date 19/06/2020
Registration for this course has been closed. Please check upcoming course on the right section

Course date, duration and fee

Start Date: 15/06/2020

End Date: 19/06/2020

Duration: 5 Days

Fees: USD 1,000, KES 80,000

Online Cost: USD 600, KES 48,000

Registration for this course has been closed. Please check upcoming course on the section below

Upcoming Courses

Download our 2024 course calendar with the list of all course schedule

Our goal is to deliver professional, practical, educational and cultivated training solutions aimed at bettering the performance of individuals and groups within the organization