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

Excel Dashboard Exercises

Learn to build interactive Excel dashboards using KPI cards, PivotTables, charts, slicers, and summary metrics. Practise transforming business data into clear visual reports that support analysis and decision-making.

1. Introduction to Excel Dashboards

An Excel dashboard is a visual summary of important information from a dataset. It combines key performance indicators (KPIs), charts, summaries, and filters into a single worksheet so users can understand business performance quickly.

For example, a sales dashboard may display total sales, total profit, number of orders, and average order value. Charts can compare sales across regions and products, while slicers allow users to explore selected categories.

A good dashboard is accurate, readable, consistent, and focused on the questions its audience needs to answer.

Learning objective: Build a dashboard that summarizes the data correctly, presents important metrics clearly, and allows users to explore the results through filters.
Excel Dashboard Architecture
Source Data: Orders, Products, Regions and Sales Prepare and Validate Data Analyse with Formulas and PivotTables KPI Cards Charts Slicers and Filters

A dashboard combines validated data, analysis, metrics, visualizations, and interactive controls.

2. Prepare the Sales Dataset

Create an Excel worksheet named SalesData. Enter the following records with unique column headings. This dataset will be used throughout the exercises.

Order ID Date Region Product Quantity Sales (₹) Cost (₹)
ORD001 01-Oct-2026 West Laptop 2 1,00,000 70,000
ORD002 02-Oct-2026 South Mobile 3 60,000 42,000
ORD003 03-Oct-2026 North Tablet 4 48,000 32,000
ORD004 04-Oct-2026 West Laptop 1 50,000 35,000
ORD005 05-Oct-2026 North Mobile 2 40,000 28,000
Important: The sample contains only five orders. It is suitable for learning the dashboard process; use a larger dataset when practising more realistic business analysis.

3. Calculate Dashboard KPIs

Key performance indicators are summarized values that help users evaluate performance. For a sales dashboard, start with total sales, total cost, total profit, order count, and average order value.

3.1 Add Calculated Columns

Assume Sales is in column F and Cost is in column G. Add a Profit column in H and calculate the profit for each order.

Excel Formula — H2
=F2-G2

Fill the formula down through the remaining rows.

3.2 KPI Formulas
KPI Formula Expected Result
Total Sales =SUM(F2:F6) ₹2,98,000
Total Cost =SUM(G2:G6) ₹2,07,000
Total Profit =SUM(H2:H6) ₹91,000
Order Count =COUNTA(A2:A6) 5
Average Order Value =AVERAGE(F2:F6) ₹59,600
Profit Margin =IFERROR(SUM(H2:H6)/SUM(F2:F6),0) 30.54% approximately

Profit margin is calculated as total profit divided by total sales. Format the result as a percentage with two decimal places.

Validation check: Total Profit = ₹2,98,000 − ₹2,07,000 = ₹91,000. Profit Margin = ₹91,000 ÷ ₹2,98,000 ≈ 30.54%.

4. Design KPI Cards

KPI cards display the most important values prominently. In Excel, they can be created with formatted cells or shapes linked to cells containing formulas.

Total Sales

₹2,98,000

All sample orders

Total Profit

₹91,000

Sales minus cost

Total Orders

5

Order records

Profit Margin

30.54%

Profit ÷ Sales
How to Create KPI Cards in Excel
  1. Create a separate worksheet named Dashboard.
  2. Calculate each KPI on a Summary worksheet or in designated cells.
  3. Insert a shape or format a group of cells as a KPI card.
  4. Link the displayed value to the cell containing the KPI formula.
  5. Apply a consistent number format, font size, and heading style.
  6. Keep the layout aligned and ensure that the labels are clear.
Best practice: Keep KPI values formula-driven. Avoid typing totals directly into dashboard cards because those values will not update automatically when the underlying data changes.

5. Create a Region-Wise Sales Chart

A column chart is useful for comparing sales across regions. Create a PivotTable with Region in Rows and Sum of Sales in Values, then insert a PivotChart or regular chart based on the summarized data.

Expected Summary
Region Total Sales
North ₹88,000
South ₹60,000
West ₹1,50,000
Steps to Create the Chart
  1. Select the region-wise summary.
  2. Choose Insert → Column or Bar Chart.
  3. Add a meaningful title such as "Sales by Region".
  4. Format the value axis as currency if appropriate.
  5. Ensure that region names are readable.
  6. Verify the chart against the source summary.

Use a column chart to compare categories. Use a line chart when the main goal is to show change over time, such as monthly sales.

6. Choose the Right Chart

Different chart types communicate different kinds of information. Choose a chart according to the question the dashboard needs to answer.

Chart Type Best Use Example
Column Chart Compare values across categories. Sales by region.
Bar Chart Compare categories, especially with long labels. Product-wise sales ranking.
Line Chart Show trends over time. Monthly sales trend.
Pie or Doughnut Chart Show a small number of parts of a whole. Sales share by product.
Combo Chart Compare related measures with different scales. Sales and profit margin.
Selecting a Chart for the Business Question
What Question Should the Dashboard Answer? Compare Categories Column / Bar Show a Trend Line Chart Show a Proportion Doughnut / Pie Use the simplest chart that clearly answers the question.

7. Add Slicers for Interactive Filtering

Slicers are visual filter controls that allow users to select categories using buttons. They are especially useful for dashboards built with PivotTables and PivotCharts.

How to Add a Region Slicer
  1. Create a PivotTable from the SalesData worksheet.
  2. Click inside the PivotTable.
  3. Open PivotTable Analyze → Insert Slicer.
  4. Select Region and confirm.
  5. Move and resize the slicer on the Dashboard worksheet.
  6. Test each region button and verify the filtered figures.

If the dashboard contains several PivotTables, use Report Connections where available to connect the slicer to compatible reports. A slicer will not automatically control every unrelated chart or formula in a workbook.

Important: Confirm that KPI cards use calculations that respond to the selected filters. Ordinary SUM formulas over the entire source range do not automatically respond to a PivotTable slicer.

8. Dashboard Layout and Formatting

Arrange the dashboard so that users can identify the most important information without searching through the worksheet. Keep related information together and use consistent spacing.

Recommended Layout
  • Top: Dashboard title, reporting period, and filter controls.
  • First row: KPI cards for Sales, Profit, Orders, and Margin.
  • Middle: Sales by Region and Sales by Product charts.
  • Bottom: Trends, detailed summaries, and supporting tables.

Use readable fonts, suitable number formats, clear chart titles, and a restrained colour palette. Avoid unnecessary decoration, too many chart types, and excessive gridlines.

9. Build the Dashboard Step by Step

1
Prepare the Data

Remove unintended blank rows, check column headings, validate dates and numeric values, and convert the source range into an Excel Table if appropriate.


2
Create Summary Calculations

Calculate total sales, cost, profit, order count, and profit margin. Validate the results.


3
Create PivotTables

Summarize sales by region and product. Create additional summaries for time periods if needed.


4
Insert Charts

Choose charts that communicate the comparisons or trends required by the business question.


5
Add KPI Cards and Slicers

Display the important metrics and provide interactive controls for supported PivotTables and PivotCharts.


6
Test and Validate

Test filter behaviour, refresh the reports, compare totals with the source data, and check readability.

10. Test Dashboard Accuracy

A visually attractive dashboard is not sufficient if its calculations are incorrect. Validate the underlying numbers before sharing the workbook.

Test Expected Check
Total Sales Matches the sum of the source Sales column.
Total Profit Matches total sales minus total cost.
Order Count Matches the number of valid order records.
Region Filter Charts and connected summaries display the selected region.
New Records New source records are included after updating the source range and refreshing.
Chart Labels Categories, values, and units are clear and readable.
Dashboard Validation Process
1. Validate Source Data 2. Reconcile KPIs and Totals 3. Test Charts and Filters 4. Refresh and Approve the Dashboard Correct figures and working filters are essential for reliable reporting.

11. Common Dashboard Mistakes

Mistake Why It Is a Problem Recommended Improvement
Too many charts Important findings become difficult to identify. Keep only charts that answer meaningful questions.
Unclear chart titles Users cannot tell what is being compared. Use descriptive titles and meaningful units.
Hard-coded KPI values Values become outdated when source data changes. Link cards to formula-driven cells.
Unconnected slicers Some charts or metrics may not respond to filters. Check Report Connections and KPI calculation methods.
Unrefreshed PivotTables Reports may exclude new source records. Update source ranges and refresh reports.
Inconsistent formatting Figures become harder to compare. Use consistent currency, percentage, and date formats.

12. Practical Dashboard Assignments

Complete these assignments using the sample dataset. Try creating the dashboard yourself before comparing your output with the expected checks.

No. Assignment Expected Output
1 Create a Total Sales KPI card. ₹2,98,000
2 Create a Total Profit KPI card. ₹91,000
3 Calculate profit margin. Approximately 30.54%
4 Display total orders and average order value. 5 orders; ₹59,600 average order value
5 Create a region-wise sales chart. North, South, and West comparisons
6 Create a product-wise sales chart. Laptop, Mobile, and Tablet comparisons
7 Add a Region slicer. Connected reports filter by region
8 Test totals after adding a new record. Updated figures after refreshing the report
Final task: Arrange the KPI cards, charts, and slicer on one Dashboard worksheet. Add a title, format the figures, and verify that the totals and filter behaviour are correct.

13. Important Points

  • Keep the source data clean and use clear column headings.
  • Calculate KPIs from formulas or validated summaries.
  • Select charts according to the business question.
  • Use slicers with compatible PivotTables and PivotCharts.
  • Confirm that KPI cards respond appropriately to filters.
  • Refresh PivotTables after updating source data.
  • Use consistent number formats and readable labels.
  • Reconcile dashboard values with the source data before sharing.

14. Summary

In this module, you learned how to plan an Excel dashboard, calculate sales and profit KPIs, create KPI cards, select appropriate charts, add slicers, and arrange the dashboard for easy interpretation.

You also explored data validation, PivotTable refresh, filter testing, and common dashboard mistakes. These practices help ensure that the dashboard is both readable and reliable.

Practise the assignments using the supplied dataset. Once you can build and validate a dashboard manually, you will be better prepared to automate reporting tasks with VBA.