Add a Profit calculated column

Add a new column called Profit inside tblSales.

=[@Revenue]-[@Cost]

Because the data is stored as an Excel Table, the formula automatically fills the entire calculated column and extends to new rows.

Add Margin %

=IFERROR([@Profit]/[@Revenue],0)

Format the column as Percentage.

Add a Month Start column

A real date is more useful than storing month names as text.

=DATE(YEAR([@Date]),MONTH([@Date]),1)

Format the result as mmm yyyy.

This preserves chronological sorting and works cleanly in PivotTables and charts.

Create dashboard controls

On the Dashboard sheet create two selection cells:

• B3 → Selected Year
• B4 → Selected Region

Use Data Validation dropdowns rather than allowing managers to type arbitrary values.

For Region, include an All option.

Calculate Total Revenue dynamically

=LET(
    StartDate,DATE($B$3,1,1),
    EndDate,DATE($B$3+1,1,1),
    SelectedRegion,$B$4,
    IF(
        SelectedRegion="All",
        SUMIFS(
            tblSales[Revenue],
            tblSales[Date],">="&StartDate,
            tblSales[Date],"<"&EndDate
        ),
        SUMIFS(
            tblSales[Revenue],
            tblSales[Date],">="&StartDate,
            tblSales[Date],"<"&EndDate,
            tblSales[Region],SelectedRegion
        )
    )
)

LET makes longer formulas easier to understand by assigning readable names to repeated values.

The formula returns annual revenue for the selected year and optionally filters to the selected region.

Calculate Total Profit

=LET(
    StartDate,DATE($B$3,1,1),
    EndDate,DATE($B$3+1,1,1),
    SelectedRegion,$B$4,
    IF(
        SelectedRegion="All",
        SUMIFS(
            tblSales[Profit],
            tblSales[Date],">="&StartDate,
            tblSales[Date],"<"&EndDate
        ),
        SUMIFS(
            tblSales[Profit],
            tblSales[Date],">="&StartDate,
            tblSales[Date],"<"&EndDate,
            tblSales[Region],SelectedRegion
        )
    )
)

Use the same filtering pattern across all headline measures so dashboard behaviour remains consistent.

Calculate overall Margin %

Assume the Total Revenue KPI is in B8 and Total Profit is in B9.

=IFERROR(B9/B8,0)

Calculate Revenue Last Year

=LET(
    StartDate,DATE($B$3-1,1,1),
    EndDate,DATE($B$3,1,1),
    SelectedRegion,$B$4,
    IF(
        SelectedRegion="All",
        SUMIFS(
            tblSales[Revenue],
            tblSales[Date],">="&StartDate,
            tblSales[Date],"<"&EndDate
        ),
        SUMIFS(
            tblSales[Revenue],
            tblSales[Date],">="&StartDate,
            tblSales[Date],"<"&EndDate,
            tblSales[Region],SelectedRegion
        )
    )
)

Create the year-on-year change

Assume Current Revenue is B8 and Last Year Revenue is B11.

=IFERROR((B8-B11)/B11,0)

Format the result as Percentage.

Apply conditional formatting so positive and negative movements can be identified quickly.

Build the monthly trend PivotTable

Insert a PivotTable from tblSales and place it on a dedicated sheet such as PivotData.

• Rows → Month Start
• Values → Sum of Revenue
• Filters or slicers → Region

Create a line chart from this PivotTable.

Create category performance

Create another PivotTable:

• Rows → Category
• Values → Sum of Revenue
• Sort → Largest to Smallest

A horizontal bar chart is often easier to read than a pie chart when several categories exist.

Build a Top Customers view

Create a PivotTable with:

• Rows → Customer
• Values → Sum of Revenue
• Value Filter → Top 10

This makes concentration risk and important customer movements much easier to see.

Add slicers

From a PivotTable, choose:

• PivotTable Analyse
• Insert Slicer
• Choose Region, Category or another useful dimension

Use Report Connections to connect one slicer to multiple PivotTables that use the same source.

Design the one-screen management view

A strong page structure might be:

• Top row → Revenue, Profit, Margin and YoY Change
• Middle left → Monthly trend
• Middle right → Category or regional performance
• Bottom → Top customers or detailed exceptions

Remove worksheet gridlines from the Dashboard sheet and use consistent spacing, fonts and number formats.

Protect the dashboard from accidental edits

Lock calculation cells and visual-support areas while leaving intended selector cells editable.

Then use worksheet protection to reduce the risk of somebody overwriting a critical formula during a management meeting.

Test with new data

Add several new records to tblSales and confirm:

• Calculated columns extend automatically
• KPI formulas include the new rows
• PivotTables refresh correctly
• Slicers still control the correct visuals
• Date selections produce the expected totals