Create:

Dynamic_Sales_Report.xlsx

Source Dataset

Order ID	Date	Employee	City	Product	Category	Qty	Sales
O101	01-Sep-26	Amit	Sirsa	Laptop	Electronics	2	120000
O102	02-Sep-26	Neha	Hisar	Mouse	Accessories	10	5000
O103	03-Sep-26	Rahul	Sirsa	Laptop	Electronics	3	180000
O104	04-Sep-26	Priya	Fatehabad	Keyboard	Accessories	8	6400
O105	05-Sep-26	Amit	Sirsa	Monitor	Electronics	4	34000
O106	06-Sep-26	Simran	Hisar	Webcam	Accessories	4	10000
O107	07-Sep-26	Rahul	Sirsa	Mouse	Accessories	15	7500
O108	08-Sep-26	Priya	Fatehabad	Laptop	Electronics	1	60000
O109	09-Sep-26	Amit	Hisar	Monitor	Electronics	2	17000
O110	10-Sep-26	Neha	Sirsa	Keyboard	Accessories	6	4800

Project Tasks

1. Create Excel Table

Convert the data into:

Table Name: SalesData

2. Create Dynamic City List

=UNIQUE(SalesData[City])

3. Create City Selection

Create a drop-down containing the unique cities.

4. Create Dynamic Sales Report

If selected city is in K2:

=FILTER(SalesData,SalesData[City]=K2,”No Records Found”)

5. Create Highest-Sales Report

=SORTBY(SalesData,SalesData[Sales],-1)

6. Create Product List

=UNIQUE(SalesData[Product])

7. Create Dynamic KPIs

Total Sales

=SUM(SalesData[Sales])

Total Quantity

=SUM(SalesData[Qty])

Total Orders

=COUNTA(SalesData[Order ID])

Unique Products

=COUNTA(UNIQUE(SalesData[Product]))

8. Dynamic Dashboard

Create:

TOTAL SALES | TOTAL ORDERS | TOTAL QUANTITY | UNIQUE PRODUCTS

Then add:

• Dynamic City Report
• Product Analysis
• Sales Chart
• Selected City indicator
• Conditional Formatting

9. Test the Dynamic System

Add a new sales record.

Then change the selected city.

Verify that:

• The report updates
• Totals update
• New records are included
• Charts/data change appropriately