Explanation
Dashboard automation means designing the workbook so that reports update with minimal manual work.
Automation Methods
- Excel Tables
- PivotTables
- PivotCharts
- Slicers
- Dynamic formulas
- Power Query
- Refresh All
- Named Ranges
- Dynamic Arrays
Example Workflow
Raw Data → Power Query → Clean Table → PivotTable → PivotChart → Slicer → Dashboard
When the source data changes:
Refresh → Updated Dashboard
Practice
Add new records to the source dataset and use:
Data → Refresh All
Verify that the dashboard updates.
PROJECT 1 — SALES DASHBOARD
Create:
Sales_Dashboard.xlsx
KPIs
- Total Sales
- Total Orders
- Total Quantity
- Average Sales
Charts
- Monthly Sales
- Product Sales
- City Sales
- Employee Sales
Filters
- City
- Product
- Category
- Employee
Dashboard Title
=”Sales Dashboard – “&B2
PROJECT 2 — HR DASHBOARD
Dataset
|
Employee |
Department |
City |
Salary |
Attendance % |
Performance % |
|
Amit |
Sales |
Sirsa |
35000 |
96% |
92% |
|
Neha |
HR |
Hisar |
40000 |
94% |
88% |
|
Rahul |
IT |
Sirsa |
55000 |
98% |
95% |
|
Priya |
Sales |
Fatehabad |
38000 |
91% |
85% |
|
Simran |
IT |
Hisar |
50000 |
97% |
90% |
Dashboard KPIs
- Total Employees
- Average Salary
- Average Attendance
- Average Performance
Charts
- Employees by Department
- Salary by Department
- Performance Comparison
- Attendance Analysis
PROJECT 3 — STUDENT PERFORMANCE DASHBOARD
KPIs
- Total Students
- Average Marks
- Highest Marks
- Pass Percentage
Charts
- Subject-wise Average
- Student Performance
- Grade Distribution
- Pass/Fail Analysis
Filters
- Class
- Section
- Subject
PROJECT 4 — EXPENSE DASHBOARD
KPIs
- Total Expenses
- Monthly Expense
- Highest Expense Category
- Average Monthly Expense
Charts
- Category-wise Expenses
- Monthly Expense Trend
- Payment Method Analysis
Filters
- Month
- Category
- Payment Method
PROJECT 5 — INVENTORY DASHBOARD
KPIs
- Total Products
- Total Stock
- Stock Value
- Low Stock Items
Charts
- Category-wise Stock
- Stock Value
- Top Products
- Low Stock Analysis
Conditional Formatting
Highlight low-stock products automatically.
Example:
=E2<10
FINAL DASHBOARD PROJECT STRUCTURE
By the end of this module, create five dashboard projects:
|
Project |
Main KPIs |
Main Visuals |
|
Sales Dashboard |
Sales, Orders, Quantity |
Sales & Product Charts |
|
HR Dashboard |
Employees, Salary, Attendance |
HR Analysis |
|
Student Dashboard |
Marks, Pass % |
Performance Charts |
|
Expense Dashboard |
Expenses, Categories |
Expense Trends |
|
Inventory Dashboard |
Stock, Value, Low Stock |
Inventory Analysis |