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 

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

Target Audience 

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

Day 1: Data Connectivity & External Sources

Essential Reporting Requirement Skills
Multiple Consolidation Ranges
Retrieving External Data using Microsoft Query
Importing Text Files using MS Query
Connecting to Access Databases
Connecting to SQL Databases
Importing from Data Connection Wizard
Importing from Microsoft Query
Customizing Connection Properties

Day 2: Data Preparation & Lookup Techniques

Building Excel Dashboard – Lookup Data
Preparing Data as Tables
Creating Dropdown Menus
VLOOKUP Function
IF Function for Data Cleaning
INDEX & MATCH Alternative to VLOOKUP
Looking up Customer Information

Day 3: Filtering & Aggregation Techniques

Building Excel Dashboard – Filtering Data
Order History Table Setup
Formatting Data as Tables
Advanced Filter Feature
Recording Macros for Filters
Modifying VBA Filter Code
Building Excel Dashboard – Subtotals
Using Subtotal Function

Day 4: Pivot Tables, Charts & Interactive Dashboards

Pivot Table and Pivot Chart Rules
Why Use Pivot Tables
Summarizing Data with Pivot Tables
Customer Filter Pivot Setup
VBA Procedures for Pivot Tables
Declaring and Assigning VBA Variables
Connecting Filters to Pivot Tables
Handling Errors (No Orders Case)
Creating Interactive Charts with Slicers
Modifying Slicer Controls

Day 5: Advanced Dashboarding & VBA Reporting

Dashboard Formatting Techniques
Hiding Worksheets and Cleanup
Protecting Dashboards
Advanced Data Validation
Conditional Formatting Techniques
Dynamic Labels and Visual Enhancements
Sparklines and Advanced Charts
Report Solutions Design
Form Controls (Combo Box, List Box, Spinner, Check Box, Option Buttons)
Macro-Driven Reporting
VBA Macros Recording, Editing, Testing
Reconciliation Report Automation
Budget Variance Report
Vendor & Invoice Analysis Report