Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Dashboard Automation in Excel

Build interactive dashboards with KPIs, PivotTables, charts, slicers, and VBA-based refresh automation.

1. Learning Objectives

After completing this module, learners will be able to:

  • Understand the structure of an automated Excel dashboard.
  • Create KPI cards using Excel formulas.
  • Prepare data for PivotTables and charts.
  • Understand interactive filtering with slicers.
  • Write VBA macros to refresh dashboard data.
  • Validate dashboard results and troubleshoot common errors.

2. What Is Dashboard Automation?

A dashboard is a visual summary of important business information. It displays key metrics, charts, and trends in a single worksheet. Dashboard automation reduces repetitive manual work by refreshing data, updating PivotTables, and recalculating reports through predefined processes.

Example: A sales manager receives new sales records every day. Instead of manually updating every chart and summary, the manager can run a macro to refresh the dashboard.

Common Dashboard Components

KPI Cards

Display total sales, order count, average order value, and other metrics.

Charts

Show trends, category comparisons, and performance distributions.

Interactive Filters

Allow users to filter reports by month, region, product, or category.

3. Dashboard Architecture

A reliable dashboard separates raw data, calculations, and presentation.

Raw Data Sales / Orders Data Processing Clean and validate Analysis Formulas / PivotTables Excel Dashboard KPI Cards + Charts + Filters VBA Refresh Automation Refresh cycle

Recommended structure: keep source records in a SalesData worksheet, calculations or PivotTables in a Analysis worksheet, and visual summaries in a Dashboard worksheet.

4. Prepare Sample Sales Data

Create a worksheet named SalesData and enter the following sample records.

Date Region Product Quantity UnitPrice Sales
01-Oct-2026WestLaptop250000100000
02-Oct-2026NorthMouse105005000
03-Oct-2026SouthKeyboard512006000
04-Oct-2026WestMonitor31200036000
05-Oct-2026NorthLaptop15000050000
  1. Enter the headers in row 1.
  2. Enter each sales record in a separate row.
  3. In cell F2, enter =D2*E2.
  4. Copy the formula down for the remaining records.
  5. Select the complete dataset and press Ctrl + T.
  6. Confirm that the table has headers and name the table tblSales.
Important: Dates should be stored as real Excel dates, quantities and prices should be numeric, and the column headers should be unique and non-empty.

5. Create KPI Cards

KPI cards display the most important metrics at the top of the dashboard. For the sample data, the expected total sales are ₹197,000 and the total quantity is 21 units.

Total Sales
₹197,000
Sum of all sales records
Units Sold
21
Total quantity sold
Sales Records
5
Number of transactions

Useful KPI Formulas

Assuming your Excel table is named tblSales:

KPI Formula Purpose
Total Sales =SUM(tblSales[Sales]) Calculates total revenue.
Total Quantity =SUM(tblSales[Quantity]) Calculates total units sold.
Number of Records =ROWS(tblSales[Sales]) Counts sales data rows.
Average Sale =IFERROR(AVERAGE(tblSales[Sales]),0) Calculates average sales per record.
Maximum Sale =IFERROR(MAX(tblSales[Sales]),0) Finds the largest sales value.
Note: The KPI values above are based on the five sample records. Your dashboard should use formulas linked to the actual data, rather than hard-coded values.

6. Dashboard Layout Design

Arrange the dashboard so that users can understand the main results quickly.

SALES PERFORMANCE DASHBOARD Total Sales ₹197,000 Units Sold 21 Transactions 5 Average Sale ₹39,400 Sales Trend Sales by Region West North South Illustrative chart layout

This diagram illustrates a possible layout. The trend line and bar sizes are illustrative; create real Excel charts from your data.

7. Create PivotTables and Charts

A. Create a PivotTable

  1. Select any cell inside the tblSales table.
  2. Choose Insert → PivotTable.
  3. Select the existing worksheet or a new worksheet named Analysis.
  4. Drag Region into Rows.
  5. Drag Sales into Values.
  6. Ensure the calculation is set to Sum of Sales.

B. Create a PivotChart

  1. Select a cell inside the PivotTable.
  2. Choose Insert → PivotChart.
  3. Choose a column or bar chart to compare regional sales.
  4. Move the chart to the Dashboard worksheet.
  5. Add a clear title such as Sales by Region.

C. Add Slicers

  1. Select the PivotTable.
  2. Choose PivotTable Analyze → Insert Slicer.
  3. Select fields such as Region and Product.
  4. Position the slicers near the top of the dashboard.
  5. Use Report Connections to connect a slicer to other compatible PivotTables.
Remember: Slicers filter connected PivotTables. Formula-based KPI cards do not automatically respond to a PivotTable slicer unless they are connected through an appropriate calculation or linked to the filtered report.

8. Dashboard Refresh Workflow

When new data is added, follow a repeatable refresh process.

1. Add New Source Data 2. Validate the Data 3. Refresh PivotTables 4. Recalculate KPIs 5. Verify Dashboard Results

9. VBA Macro to Refresh the Dashboard

The following macro refreshes workbook data connections and PivotTables, recalculates formulas, and displays a completion message.

Step 1: Open the VBA Editor

  1. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
  2. Press Alt + F11.
  3. Choose Insert → Module.
  4. Paste the following code into the module.

Step 2: Write the Refresh Macro

Option Explicit

Sub RefreshDashboard()

    Dim ws As Worksheet
    Dim pt As PivotTable

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Application.StatusBar = "Refreshing dashboard..."

    ' Refresh workbook connections and queries
    ThisWorkbook.RefreshAll

    ' Recalculate workbook formulas
    Application.CalculateFull

    ' Refresh PivotTables in every worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    ' Recalculate formulas after refreshing
    Application.CalculateFull

    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox "Dashboard refresh completed. Please verify the results.", _
           vbInformation, "Dashboard Automation"

    Exit Sub

ErrorHandler:
    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox "Refresh failed: " & Err.Description, _
           vbExclamation, "Dashboard Automation"

End Sub
Important: RefreshAll may start some connections asynchronously. For workbooks with Power Query or external connections, configure and wait for the relevant refresh operations to finish before assuming the dashboard is up to date. The macro should also be tested against the actual workbook and data sources.

Step 3: Add a Refresh Button

  1. Return to the Excel workbook.
  2. Enable the Developer tab if it is not visible.
  3. Choose Developer → Insert → Button (Form Control).
  4. Draw the button on the Dashboard worksheet.
  5. Assign the RefreshDashboard macro.
  6. Change the button text to Refresh Dashboard.

10. Refresh the Dashboard When the Workbook Opens

If the dashboard should refresh whenever the workbook opens, add the following code to the ThisWorkbook object in the VBA editor.

Private Sub Workbook_Open()

    RefreshDashboard

End Sub

This event runs when the workbook opens and macros are enabled. For workbooks with slow external connections, a controlled refresh sequence may be required. Avoid enabling macros in files from untrusted sources.

11. Common Errors and Solutions

Problem Possible Cause Solution
PivotTable does not include new rows Source range does not include the new data. Use an Excel Table as the source and refresh the PivotTable.
KPI shows an incorrect total Missing rows, text-formatted numbers, or incorrect formulas. Verify the table range, numeric data types, and formula references.
Macro does not run Macros are disabled or the code is stored in the wrong module. Check workbook format, macro settings, and procedure location.
Chart does not update Chart source range or PivotTable has not refreshed. Check the chart source and refresh the underlying data.
Slicer does not filter every chart PivotTables may not share a compatible source or connection. Check Report Connections and the PivotTable data sources.
External data is still loading A query or connection may refresh asynchronously. Wait for the refresh to complete before validating results.

12. Dashboard Automation Best Practices

  • Keep raw data separate from dashboard presentation.
  • Use Excel Tables instead of fixed source ranges where practical.
  • Use consistent date, currency, and number formats.
  • Give KPI cards clear labels and meaningful units.
  • Use charts that match the business question.
  • Validate totals against the original source data.
  • Handle VBA errors and restore application settings.
  • Test refresh behavior with new records and empty data.
  • Keep a backup before changing important reporting workbooks.
  • Protect sensitive business information and use trusted macro-enabled files.

13. Practical Assignment

Build a complete Sales Dashboard using the sample data.

Requirements

  1. Create the SalesData worksheet and tblSales table.
  2. Calculate Total Sales, Total Quantity, Average Sale, and Transaction Count.
  3. Create a PivotTable showing total sales by region.
  4. Create a chart showing sales by product.
  5. Create a sales trend chart using dates.
  6. Add Region and Product slicers.
  7. Create a Refresh Dashboard button using VBA.
  8. Add new records and verify that the dashboard refreshes correctly.

Challenge Tasks

  • Add a date filter to analyze sales for a selected period.
  • Highlight the highest-performing region.
  • Add conditional formatting to identify low sales.
  • Create a printable dashboard layout.
  • Document how the dashboard handles missing or invalid data.

14. Important Points to Remember

  • A dashboard converts detailed records into a quick visual summary.
  • KPI formulas should reference the actual data source.
  • PivotTables summarize data and PivotCharts visualize those summaries.
  • Slicers filter only the compatible reports to which they are connected.
  • VBA can automate refresh operations and reduce repetitive tasks.
  • External data refresh may require additional waiting or connection-specific handling.
  • Always validate results after automation.

15. Summary

In this module, you learned how to design an Excel dashboard using KPI cards, formulas, PivotTables, PivotCharts, and slicers. You also learned how VBA can automate refresh operations and how to test the dashboard after new data is added.

Capstone Outcome: A structured, interactive Sales Dashboard that helps users review business performance with less repetitive manual work.