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

Interactive Dashboards Introduction in Excel

Learn how to transform raw Excel data into an interactive dashboard using KPIs, charts, filters, and summary reports.

1. What Is an Excel Dashboard?

An Excel dashboard is a visual summary of important business data. It helps users understand performance, compare results, identify trends, and make decisions without reading thousands of rows.

For example, a training institute can use a dashboard to track course-wise admissions, fees collected, pending fees, and monthly admission trends.

Sample Excel Dashboard Layout
CIIT Training Institute Dashboard Total Admissions 120 Fees Collected ₹6.5L Pending Fees ₹1.2L Admissions by Course 30 45 22 Java SQL Python Monthly Admissions Trend Jan Feb Mar Apr May Jun

Illustrative dashboard layout. The values and charts are sample data.

Remember: A dashboard is the presentation layer. Its numbers should come from accurate source data, formulas, PivotTables, or other reliable calculations.

2. Why Do We Use Dashboards?

Feature Purpose Example
KPI Cards Display important numbers clearly. Total admissions and fees collected.
Charts Compare categories and identify trends. Course-wise admission comparison.
Slicers Filter supported PivotTables and PivotCharts interactively. View admissions for a selected course.
Timelines Filter date-based PivotTable reports. View admissions for a selected month or period.
Conditional Formatting Highlight values based on rules. Highlight pending fees above ₹10,000.

3. Prepare Sample Data

Create an Excel worksheet named AdmissionData. Enter the following data in cells A1:F11.

AdmissionID StudentName Course AdmissionDate FeesPaid PendingFees
101SamadhanJava01-Jan-2026150005000
102SamikshaSQL05-Jan-2026120003000
103PradnyaPython12-Feb-2026180002000
104OmkarJava20-Feb-20261000010000
105NehaSQL03-Mar-2026140001000
106SamadhanPython15-Mar-2026160004000
107SamikshaJava02-Apr-2026130007000
108PradnyaSQL10-Apr-2026170003000
109OmkarPython05-May-2026110009000
110NehaJava18-May-2026190001000
Important: Format AdmissionDate as a real Excel date, not as text. Format FeesPaid and PendingFees as currency or numbers. Keep the column headings unique and avoid blank rows inside the dataset.

Convert the data into an Excel Table:

  1. Select any cell inside the dataset.
  2. Press Ctrl + T.
  3. Enable My table has headers.
  4. Click OK.
  5. Open the Table Design tab and set the table name to tblAdmissions.

4. Create Dashboard KPI Cards

KPI cards show key figures at the top of a dashboard. Create a new worksheet named Dashboard and enter these formulas in the indicated cells.

Cell KPI Formula Expected Result
B2 Total Admissions =COUNTA(tblAdmissions[AdmissionID]) 10
D2 Total Fees Collected =SUM(tblAdmissions[FeesPaid]) ₹145,000
F2 Total Pending Fees =SUM(tblAdmissions[PendingFees]) ₹45,000
H2 Average Fees Paid =AVERAGE(tblAdmissions[FeesPaid]) ₹14,500
How to make KPI cards visually clear
  1. Keep a descriptive heading above each KPI value.
  2. Use a large, bold font for the value.
  3. Apply a consistent color palette and alignment.
  4. Use currency formatting for fee-related KPIs.
  5. Keep enough spacing so the dashboard remains readable.
Excel Formula — Course-wise Admissions
=COUNTIF(tblAdmissions[Course],A6)

If cell A6 contains Java, this formula returns the number of Java admissions. Copy it down for SQL and Python, with the corresponding course names in column A.

5. Create Charts for the Dashboard

A. Course-wise Admissions Chart
  1. Create a small summary table containing Course and Admissions.
  2. Use COUNTIF to calculate admissions for each course.
  3. Select the summary table.
  4. Go to Insert → Column or Bar Chart.
  5. Add the chart title Admissions by Course.
  6. Move the chart to the Dashboard worksheet if required.
Course Admissions Total Fees Paid
Java457000
SQL343000
Python345000
B. Monthly Admissions Trend
  1. Create a summary table with Month and Admissions.
  2. Use the first and last dates of each month to count admissions.
  3. Insert a Line Chart using the monthly summary.
  4. Set the chart title to Monthly Admissions Trend.
Excel Formula — Monthly Admissions
=COUNTIFS(tblAdmissions[AdmissionDate],">="&A6,tblAdmissions[AdmissionDate],"<"&EDATE(A6,1))

Here, A6 must contain the first day of the required month as a real Excel date. For example, enter 01-Jan-2026 in A6 and format it as mmm-yyyy. Copy the formula down after entering the first day of each following month.

Chart selection tip: Use column charts for category comparisons and line charts for trends over time. Avoid unnecessary 3-D effects and excessive colors.

6. Make the Dashboard Interactive

A dashboard becomes interactive when users can select filters and explore the underlying data without manually editing the source.

Tool How It Helps Example
PivotTable Summarizes large datasets. Count admissions by course.
PivotChart Visualizes PivotTable summaries. Compare course admissions.
Slicer Provides clickable category filters. Filter by course.
Timeline Provides a visual date filter. Filter by admission month.
Data Validation Restricts or guides user input. Select a course from a dropdown.
Add a Slicer to a PivotTable
  1. Select a cell inside the PivotTable.
  2. Go to PivotTable Analyze → Insert Slicer.
  3. Select the Course field and click OK.
  4. Click a course button to filter the PivotTable.
  5. Use the clear-filter control to remove the selection.

To filter multiple PivotTables from one slicer, use Report Connections or PivotTable Connections and select compatible PivotTables that share the appropriate data source.

Add a Timeline
  1. Select a PivotTable that contains the AdmissionDate field.
  2. Choose PivotTable Analyze → Insert Timeline.
  3. Select AdmissionDate and click OK.
  4. Choose the required month, quarter, or year using the timeline controls.
Important: Slicers and timelines do not automatically control every formula or ordinary chart. Their effect depends on the connected PivotTables, PivotCharts, and report connections. Plan the dashboard data source before connecting filters.

7. Dashboard Design Best Practices

  • Keep source data separate from the Dashboard worksheet.
  • Place the most important KPIs near the top.
  • Use clear titles, meaningful labels, and consistent number formats.
  • Choose chart types that match the data being presented.
  • Use a limited and consistent color palette.
  • Remove unnecessary gridlines and decorative elements.
  • Test slicers, timelines, formulas, and chart connections.
  • Refresh PivotTables after source data changes.
  • Ensure totals reconcile with the underlying dataset.

8. Practical Exercises

Task 1: KPI Cards

Create KPI cards for Total Admissions, Total Fees Collected, Total Pending Fees, and Average Fees Paid.

Task 2: Course Analysis

Create a course-wise summary table and a column chart comparing Java, SQL, and Python admissions.

Task 3: Monthly Trend

Create a monthly admission summary and display it using a line chart.

Task 4: Interactive Filtering

Create a PivotTable and PivotChart, add a Course slicer, and verify that selecting a course updates the connected report.

Task 5: Final Dashboard

Arrange the KPI cards, course chart, monthly trend, slicer, and timeline on one worksheet. Verify that all totals are correct.

Important Points to Remember

  • Use clean, structured source data with unique column headings.
  • Use Excel Tables to make expanding datasets easier to manage.
  • Use formulas or PivotTables to calculate KPIs accurately.
  • Use charts to explain the data, not merely decorate the worksheet.
  • Test every interactive control before sharing the dashboard.
  • Keep a clear distinction between source data, calculations, and presentation.

Summary

An Excel dashboard combines key performance indicators, charts, summary tables, and interactive controls into one clear report.

By using Excel Tables, formulas, PivotTables, PivotCharts, slicers, and timelines, you can build dashboards that help users explore data and understand business performance. Accurate source data, suitable charts, and properly connected filters are essential for a reliable dashboard.