Introduction

Data analysis and dashboard reporting in Excel has become an emerging requirement for many businesses today. Employees are frequently asked to prepare management reports, scorecards, and dashboard charts in order to help the management in their decision-making process.

This course will help you understand advanced levels of analysis and reporting and allow you to create custom reports. The course will cover various tools and techniques used to perform data reporting, analysis, and visualization. It will also discuss high-level modeling techniques, data amalgamation, report visualization, and possible cases of automation.

Course Objectives for Data Analysis and Dashboard Reporting in Excel

Upon completing this Advanced Data Analysis and Dashboard Reporting in Excel course successfully, participants will be able to:

• Understand the principles of data analysis

• Uncover the tools to analyze data and build reports using Excel

• Understand how to use visualization techniques to improve the 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 visualization charts, dashboards, scorecards, and flash reports

• Build custom reports using advanced form controls and buttons

Training Methodology for Data Analysis and Dashboard Reporting in Excel

This is an interactive Advanced Data Analysis and Dashboard Reporting in Excel training program and will consist of the following training approaches:

• Lectures

• Seminars & Presentations

• Group Discussions

• Assignments

• Case Studies & Functional Exercises using MS Excel

Organizational Benefits 

Companies who send in their employees to participate in this Advanced Data Analysis and Dashboard Reporting course can benefit in the following ways:

• Give your employees the ability to manage large data volumes using the latest tools

• Provide your workforce with flexible and cost-effective professional development opportunities

• Analyze case studies in this domain and be able to apply successful techniques in your organization

• Comprehend the principles and practice of data analysis for project M&E and the context in which this operates

Personal Benefits

Professionals who participate in this Advanced Data Analysis and Dashboard Reporting course can benefit in the following ways:

• Learn and work with data analysis and management tools that are used widely

• Study each of the major fields of data analytics in an organized and logical manner

• Increase your demand as a professional with experience in data analytics because most organizations are now looking for ways to exploit the power of big data

• Recognize how to apply big data analytics across various industries

• Keep yourself updated with the latest industry trends in technology and use them to make better decisions at your workplace, thereby increasing your chance of success, and improving your credibility subsequently

Target Audience for Data Analysis and Dashboard Reporting in Excel

This Advanced Data Analysis and Dashboard Reporting in Excel course would be suitable for:

• Business professionals

• Accountants, finance analysts/managers/controllers, senior and junior accountants

• Business analysts

• Research analysts

• Marketing and sales, administrative staff, supervisors,

• Specialists engaged in data analysis and dashboard reporting using Excel

• Employees from any function who need to learn and apply state-of-the-art techniques to their daily business reporting, reconciliations, and analysis

Course Outline

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

• Customizing connections properties

BUILDING THE EXCEL DASHBOARD – LOOKUP DATA

• Looking up customer info

• Preparing the data using the format as a 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

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

BUILDING THE EXCEL DASHBOARD – SUBTOTALS

• Why use Excel’s subtotal function

• Implementing the subtotal function

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

BUILDING THE EXCEL DASHBOARD – INTERACTIVE BUTTONS

• Creating interactive charts with slicers

• Modifying the chart slicer

BUILDING THE EXCEL DASHBOARD – FORMATTING

• Hiding extra worksheets and columns

• Cleaning up the Excel default settings

• Protecting the dashboard

ADVANCED DATA STRUCTURING TECHNIQUES

• Custom and advanced data validation

• Creating and managing innovative conditional formatting

CHARTING AND VISUALISATION TECHNIQUES

• Creating dynamic labels

• Using the camera tool

• Working with formula-driven visualizations

• Using fancy fonts

• Leveraging symbols in formulas

• Working with sparklines

• Creating unconventional style charts

• Fancy thermometer charts

• Colored chart bars

BUILDING REPORT SOLUTIONS

• Conceptualizing 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 modeling tools

• Spinner

• Option-button modeling

• Check-box data models

• Combo and group-box

MACRO CHARGED REPORTING

• Recording, editing, and testing VBA macros

• Building a macro-driven reconciliation program

• Building a budget variance reporting program

• Building a vendor and invoice analysis report