Excel Intermediate Lesson 12: Project – Build a GST Sales Dashboard

Excel Intermediate Lesson 12: Project - Build a GST Sales Dashboard 1

📎 This article includes 1 downloadable practice file ↓

⏱ 2 min read

📘 Excel Intermediate Course · Lesson 12 of 12

Advertisement
In this article
  1. 1. Prepare the data
  2. 2. KPI cards
  3. 3. Charts
  4. 4. Finishing touches
  5. What next?
  6. Practice

Time to put the course together. You’ll build a one-page dashboard that a sales head could open on Monday morning, using only what you’ve learned.

1. Prepare the data

  • Convert Sales to a Table (Ctrl+T, name it SalesT), lesson 1.
  • Add columns: FY Qtr (lesson 6) and GST = [@Amount]*XLOOKUP([@Code],Master[Code],Master[GST %]), lesson 3.

2. KPI cards

Card Formula
Taxable value =SUM(SalesT[Amount])
GST =SUM(SalesT[GST])
Collection rate =SUMIFS(SalesT[Amount],SalesT[Status],"Paid")/SUM(SalesT[Amount])
Overdue =SUMIFS(SalesT[Amount],SalesT[Status],"Overdue")
Top region =LET(r,UNIQUE(SalesT[Region]),s,SUMIFS(SalesT[Amount],SalesT[Region],r),INDEX(r,MATCH(MAX(s),s,0)))

Put each in a big bold cell with a light fill and a label above. Format amounts in lakhs with the custom format [>=100000]##\,##\,##0;##,##0.

3. Charts

  • A PivotTable of Amount by Month (grouped dates) > PivotChart > Column.
  • A second pivot by Region > Doughnut or Bar.
  • Add a Region slicer and a Date timeline; connect both to both pivots (Report Connections).

4. Finishing touches

  • Turn off gridlines (View > Gridlines) on the dashboard sheet.
  • Conditional formatting: red if collection rate is below 75% (lesson 11).
  • Protect the sheet, leaving only the slicers clickable.
💡 Check every KPI against the practice file’s numbers. If your top region or best month differs, a pivot filter or a fixed range is usually to blame.

What next?

You now have the core of intermediate Excel. Natural next steps: automate the monthly import with Power Query, build the same report in Power BI, or automate formatting with the VBA course.

Practice

Download this lesson’s workbook below. The practice file has the 8 dashboard numbers to check your work against. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.

📎 Practice files for this article

  • 📗
    Lesson 12 project workbookSales register + product master, 8 dashboard numbers with checks.
    ⬇ XLSX · 20 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

Keep reading

ExcelExcel Intermediate Lesson 6: Business Dates (WORKDAY, NETWORKDAYS, EOMONTH, FY Quarters)ExcelExcel Intermediate Lesson 2: SUMIFS, COUNTIFS and AVERAGEIFS ReportsExcelBank Statement Lesson 7: Combine Monthly Statements With Power Query
Advertisement
✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong