An Excel dashboard is a one-page summary built from pivot tables, charts and a few key numbers, which updates when the data changes. It is a common interview task and a practical skill in many analyst roles.
The example is a sales dashboard for a fictional home-furnishing brand, CasaCraft, with columns Date, Region, Store, Category, Units and Revenue.
Step 1: Decide the questions
Write them down before touching Excel. For example:
- How much revenue this month, versus last month?
- What is the monthly trend?
- Which regions and categories drive revenue?
- Which stores are underperforming?
Every element on the dashboard should answer one of these.
Step 2: Prepare the data
- Put raw data on its own sheet.
- Clean it: one header row, no blanks in key columns, consistent text, real dates.
- Convert it to a Table (Ctrl + T) and name it, for example
SalesData. - Add helper columns if needed, such as Month (
=TEXT([@Date], "yyyy-mm")).
Step 3: Build pivot tables on a separate sheet
Create one pivot per question:
- Revenue by Month (for the trend).
- Revenue by Region.
- Revenue by Category.
- Revenue by Store, sorted.
Name each pivot (PivotTable Analyze โ PivotTable Name) so they are easy to manage.
Step 4: Create the charts
- Trend: a line chart from the month pivot.
- Region and category: bar charts, sorted largest to smallest.
- Stores: a bar chart of the bottom few, or a table with conditional formatting.
Remove clutter: delete gridlines you do not need, shorten axis labels, and give each chart a title that states the point.
Step 5: Add KPI cells
At the top of the dashboard, show headline numbers with comparisons:
This month revenue: =SUMIFS(SalesData[Revenue], SalesData[Month], CurrentMonth)
Last month revenue: =SUMIFS(SalesData[Revenue], SalesData[Month], PreviousMonth)
Change: =IFERROR(ThisMonth/LastMonth - 1, "")Format change with conditional formatting (green up, red down) or arrow symbols.
Step 6: Add slicers
Select any pivot โ Insert Slicer โ Region and Category. Then use Report Connections to link each slicer to every pivot, so one click filters the whole dashboard. Add a Timeline for dates.
Step 7: Lay out the page
- KPIs across the top.
- Trend chart large in the middle.
- Breakdowns below or beside it.
- Slicers on the left or top.
- Align everything to the grid and keep spacing even.
- Hide gridlines (View โ uncheck Gridlines) for a clean look.
Step 8: Make it refreshable
When new data is added to the Table, Data โ Refresh All updates every pivot and chart. Test it by adding a few rows.
Common mistakes
- Too many charts and colours.
- Pie charts with many slices.
- Numbers without comparisons.
- Hard-coded values that do not update.
Going further
When a dashboard needs many data sources, larger data or sharing online, Power BI is the natural next step. The same design principles apply; read the dashboard design tips. For real business data to build on, try a live project.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: