Why date handling is common in business in Excel - Dashboard Impact

Dashboard Mode - Why date handling is common in business

Business Question

How can businesses use dates to track sales over time and understand trends?

Sample Data

Date	Sales	Region
2024-01-01	100	East
2024-01-02	150	West
2024-01-03	200	East
2024-01-04	130	North
2024-01-05	170	West
2024-01-06	160	East
2024-01-07	180	North

Dashboard Components

• Total Sales: =SUM(B2:B8) shows total sales for the week. Result: 1090
• Average Daily Sales: =AVERAGE(B2:B8) calculates average sales per day. Result: 155.71
• Sales by Region: A summary table using =SUMIFS(B2:B8,C2:C8,"East") and similar formulas for other regions:
  East: =SUMIFS(B2:B8,C2:C8,"East") = 460
  West: =SUMIFS(B2:B8,C2:C8,"West") = 320
  North: =SUMIFS(B2:B8,C2:C8,"North") = 310
• Sales Trend Chart: A line chart plotting Date (X-axis) vs Sales (Y-axis) to see sales changes over days.
• Day of Week Analysis: Extract day names with =TEXT(A2,"dddd") and summarize sales by day to find best sales days.

Dashboard Layout

+----------------------+----------------------+
| Total Sales (KPI)    | Average Daily Sales   |
|                      | (KPI)                |
+----------------------+----------------------+
| Sales by Region Table                      |
|                                            |
+--------------------------------------------+
| Sales Trend Chart (Line chart)             |
+--------------------------------------------+
| Day of Week Sales Summary                   |
+--------------------------------------------+

Interactivity

Add a date filter (e.g., select a date range). When you change the date range, all components update to show sales only for those dates. For example, total sales, average sales, sales by region, and the sales trend chart all reflect the selected dates.

Also, a region filter can be added to focus on sales from one region. This updates the total, average, and trend chart accordingly.

Self Check

If you add a filter to show only sales from the East region, which components update?

• Total Sales
• Average Daily Sales
• Sales Trend Chart
• Day of Week Sales Summary
• Sales by Region Table (will show only East region data)

Key Result

A sales dashboard showing total, average, regional sales, and trends over dates to explain why date handling is key in business.