Build a GST sales dashboard
A dashboard is not a pile of charts. It is one screen that answers the three or four questions the business asks every month: how much did we sell, where, to whom, and what changed. This lesson builds a monthly GST sales dashboard from a cleaned invoice register.
Structure the workbook in three layers
| Sheet | Contents | Visible to users |
|---|---|---|
Data |
The raw register as an Excel Table named Sales |
No (hidden) |
Calc |
Pivot tables and helper formulas | No (hidden) |
Dashboard |
Charts, KPI cards, slicers | Yes |
Keeping calculations off the dashboard sheet means you can rearrange the visuals without breaking anything.
KPI cards
A KPI card is just a cell with a large font and a clean number format. Compute the value in Calc, then link it:
=SUM(Sales[Taxable])
=SUMIFS(Sales[Taxable], Sales[Date], ">="&EOMONTH(TODAY(),-2)+1,
Sales[Date], "<="&EOMONTH(TODAY(),-1))
="vs last month: " & TEXT(Calc!B2/Calc!B3-1, "+0.0%;-0.0%")
Indian reports read better in lakh and crore. Apply this custom number format (Ctrl+1 → Custom) so a single cell scales itself:
[>=10000000]0.00,,,"Cr";[>=100000]0.00,,"L";#,##0
A number without a comparison is noise, so every card should carry a second line: versus last month, versus target, or versus the same month last year.
Choose charts that match the message
| Question | Chart |
|---|---|
| How is revenue trending? | Line chart by month |
| Which states sell most? | Horizontal bar, sorted descending |
| Who are the top customers? | Bar chart of the top 10 only |
| Revenue versus margin percentage | Combo chart, margin on the secondary axis |
| Sales mix by GST rate | Stacked column, three or four segments maximum |
| Trend inside a table row | Sparkline (Insert → Sparklines → Line) |
Build each chart from a pivot table, sort the bars, delete gridlines and the legend when a single series is shown, and write the insight in the title: "Rajasthan contributed 41% of Q1 revenue" tells the reader more than "Sales by state".
Slicers and timelines
Insert a slicer (PivotTable Analyze → Insert Slicer) for State and a timeline for Date. By default a slicer controls only the pivot it was created from. Right-click it, choose Report Connections, and tick every pivot table that should respond — this is what makes the whole dashboard move together. Slicers only connect pivots that share a source, so build all of them from the same Table or Data Model.
Finishing touches
- Turn off gridlines, headings and the formula bar (View tab) for a clean canvas.
- Use one accent colour plus greys; colour should mean something, not decorate.
- Freeze the KPI row, and fit everything to one screen at 100% zoom.
- Protect the Dashboard sheet, leaving slicers unlocked so users can filter but not overwrite.
- Add a small note with the data refresh date:
="Updated " & TEXT(NOW(),"dd-mmm-yyyy hh:mm").
Common mistakes
- Referring to a pivot cell directly and getting a
GETPIVOTDATAformula that breaks when the pivot layout changes; switch it off under PivotTable Analyze → Options → Generate GetPivotData. - Pie charts with twelve slices.
- Dual axes used for two unrelated units, which invites false conclusions.
- A dashboard nobody can refresh because the source path points to your own desktop.
Key takeaways
- Separate data, calculations and presentation into different sheets.
- Every number needs a comparison; every chart needs a message in its title.
- Slicers must be connected to all pivots to behave as one dashboard.
Practice
Using a year of GST invoice data, build a Dashboard sheet with four KPI cards (taxable value, GST collected, invoice count, average invoice value, each with a month-on-month change), a monthly trend line, a sorted bar of the top 10 customers and a state-wise bar. Add a State slicer and a Date timeline, connect both to every pivot, and format all amounts in lakh and crore.