An Excel dashboard sample is a one-page summary that turns a structured data table into a quick view of performance. A practical sales version can show total revenue, sales versus target, monthly trends, and results by region, with charts and filters that let readers explore the data.
What the sample dashboard shows
Imagine a sales team that wants to review four transactions across two months. Its source table contains Date, Region, Product, Units, Revenue, and Target columns. A dashboard based on that data could place three headline metrics across the top, a monthly revenue chart beneath them, and a region-by-region comparison beside the chart. A date or region filter can make the view more useful without changing the underlying records.
For a small illustrative dataset, January has $2,000 in revenue against a $1,900 target, and February has $2,340 against a $2,200 target. Together, the four transactions total $4,340 in revenue and $4,100 in target. The dashboard can therefore communicate both the overall result and the pattern behind it: revenue exceeds target in both months, while February contributes more revenue.
The sample is a design pattern, not a ready-made workbook or a guarantee that any particular company should use these metrics. A useful dashboard answers a defined business question. A sales manager may care about revenue and quota attainment; an operations team may instead track orders completed, late shipments, and open work.
Choose metrics that answer a question
Start by deciding what a reader should be able to understand in a few seconds. For a sales dashboard, a reasonable set might include revenue for the selected period, the target for that period, and the percentage of target achieved. A trend chart then adds context by showing whether the result is rising, falling, or uneven.
- Revenue: the total value of sales in the selected period.
- Target: the comparable goal for the same period and scope.
- Attainment: actual revenue divided by target, displayed as a percentage.
- Trend: revenue over time, using months or weeks appropriate to the reporting cycle.
- Breakdown: a comparison by a useful category, such as region, product, or salesperson.
Define each metric before building the visuals. For example, decide whether revenue means gross sales or sales after returns, and whether a target is assigned by month, salesperson, or region. If the definitions are unclear, two people can read the same dashboard and draw different conclusions. Avoid adding metrics merely because they are easy to calculate; each one should support a decision or prompt a useful question.
Prepare the source data
Keep the source records in a tidy table, with one row per transaction and one field per column. Use a single header row with clear, unique names. Do not insert blank rows between records, merge cells in the data, or use subtotal rows inside the transaction list. Those features may look organized on a printed sheet but can interfere with sorting, filtering, formulas, and PivotTables.
Dates should be real Excel dates rather than text that only looks like a date. Revenue and target values should be numeric, with currency formatting applied to the cells rather than typed into each value. Use consistent names for categories: North and north should not be treated as intentionally different regions. If the source comes from several files, standardize column names and categories before combining it.
Select the data and choose Insert > Table in Excel for Microsoft 365 on Windows. Confirm that the table has headers. A table can expand as new rows are added, which is helpful for a growing source list. Give the table a descriptive name on the Table Design tab, such as SalesData. A PivotTable based on that table still needs to be refreshed after source changes so that its summary reflects new records.
Build the dashboard in a sensible order
Build the data and calculations before spending time on appearance. One practical sequence is:
- Make a copy of the source workbook and check that the fields and values are consistent.
- Convert the source range to a table and confirm that dates and amounts have the correct data types.
- Create a summary with formulas or a PivotTable. Summarize by month and by the categories readers need to compare.
- Create the charts from the summary rather than charting the raw transaction rows without a clear purpose.
- Add headline metrics and filters, then arrange the pieces so that the most important result is easiest to see.
- Check the result against the source totals, try each filter, and confirm the reporting period and units are visible.
For a PivotTable, select a cell in the source table and choose Insert > PivotTable. Put a date field in Rows and a numeric field such as Revenue in Values. If Excel groups dates automatically, check that the grouping matches the intended reporting period. Add Region or Product to Columns or Filters to make the comparison. Create a PivotChart from the PivotTable when an interactive chart that follows the summary is useful.
Keep the raw data, summaries, and dashboard visually separate, often on separate worksheets. That makes it easier to trace a displayed number to its source and reduces the risk of someone overwriting a transaction while adjusting the dashboard.
Use formulas or PivotTables appropriately
For a compact sample or a fixed report, formulas can be transparent and easy to inspect. With the four illustrative records in rows 2 through 5, Revenue in column E, and Target in column F, the total revenue formula is =SUM(E2:E5). It returns 4340. The corresponding total target formula is =SUM(F2:F5), which returns 4100. Format the result cells as currency if the dashboard reports dollars.
With Revenue in E2:E5 and Target in F2:F5 for those same four records, the attainment formula is =IFERROR(SUM(E2:E5)/SUM(F2:F5),0). It returns about 1.0585, or 105.85% when formatted as a percentage. IFERROR prevents a divide-by-zero error if the target total is zero, but displaying zero in that situation can conceal missing or invalid target data. For a production report, consider making the zero-target condition visible rather than treating it as ordinary performance.
PivotTables are often more convenient when readers need to summarize many records by several categories or change the view with filters. Formulas are useful when the layout is highly controlled or when the calculations need to be visible in specific cells. Either approach can be correct; the important test is whether totals reconcile to the source and whether the workbook is maintainable by its intended users.
Choose charts that make comparisons clear
Use a line chart for a time trend when the order of dates matters. A clustered column chart is a straightforward choice for comparing monthly totals or regions. A bar chart can be easier to read when category names are long. A pie chart is generally less helpful when readers need to compare many categories or small differences; direct labels or bars make those comparisons easier.
Keep the chart tied to a clear question. A chart titled “Revenue by Month” is more informative than “Chart 1,” and its axis should make the scale and units clear. Avoid three-dimensional effects and unnecessary decoration, which can make values harder to compare. If the dashboard has a date filter, show the selected period somewhere on the page so a screenshot does not lose important context.
Use consistent number formats: currency for money, whole numbers for units, and percentages for rates. Do not use color alone to communicate good or bad performance; labels, symbols, or explanatory text help readers who cannot distinguish the colors and make the meaning clearer in print.
Add filters and make the layout readable
Slicers provide clickable filters for PivotTables and PivotCharts. Select a PivotTable, then use PivotTable Analyze > Insert Slicer and choose fields such as Region or Product. If several PivotTables should respond to the same slicer, use Slicer > Report Connections to connect it where compatible. A slicer only controls the PivotTables to which it is connected; do not assume it filters every calculation on the sheet.
Place the most important measures at the top, followed by trends and detailed comparisons. Leave enough space around charts and labels, align objects to a simple grid, and use a restrained set of colors. Freeze panes are usually unnecessary on a presentation-style dashboard, but a separate data sheet may benefit from them. Keep the dashboard usable at the screen size where it will normally be read, and check whether it prints legibly if people need paper copies.
For accessibility and maintainability, use descriptive sheet names, meaningful chart titles, and clear labels. Avoid hiding the meaning of a metric in a color key that is far from the chart. If a number depends on a particular definition or time window, add a short note near it.
Common problems and how to avoid them
- Totals do not match: Check for text-formatted numbers, duplicate records, missing rows, or an incorrect date range. Reconcile the dashboard total to a direct total from the source.
- New rows are missing: Confirm that the source is an Excel table and refresh PivotTables after adding records. A chart based on a fixed cell range may also need its source range updated.
- Dates group incorrectly: Look for dates stored as text or inconsistent date values. Correct the source rather than hiding the problem with a chart label.
- A filter changes one chart but not another: Check whether the slicer is connected to each relevant PivotTable through Report Connections.
- The dashboard looks busy: Remove visuals that do not answer a decision question. A few well-labeled metrics and charts are usually easier to interpret than a crowded page of small objects.
Another common misconception is that a dashboard automatically updates every time its source changes. A formula linked to an expanding table may recalculate, but PivotTables commonly require refresh, and external connections can have their own refresh settings. Document what users should refresh and verify the workbook after updating its data.
Sharing, maintenance, and alternatives
Before sharing, check that the workbook opens with the intended dashboard sheet visible, that filters start in a sensible state, and that no confidential source fields are exposed unnecessarily. If the workbook uses external data connections, confirm that recipients have access and understand how to refresh them. A static PDF can be appropriate for a fixed report, but it will not preserve the interactive filtering of the workbook.
Excel is a practical choice for a small or medium-sized report that needs flexible analysis and is already maintained in spreadsheets. If many people need simultaneous access, governed data definitions, automated refresh, or a large volume of records, a dedicated reporting platform or database-backed workflow may be more suitable. The right choice depends on data volume, permissions, refresh needs, and who will maintain the report—not simply on how polished the sample looks.
Frequently Asked Questions
Where can I get a free Excel dashboard sample?
Microsoft provides downloadable Excel templates through its template library, and other organizations publish workbooks as examples. Check the source, the required Excel version, and whether the sample contains formulas, PivotTables, or external connections before using it. Treat sample figures as demonstration data, not business results.
Can I make an Excel dashboard without a PivotTable?
Yes. You can use formulas, Excel tables, and charts to build a dashboard. PivotTables are helpful for flexible summaries and interactive filtering, but they are not required for every report.
How do I make a dashboard update when I add data?
Store new records in the source Excel table, then update dependent summaries. Formula-based results may recalculate automatically, while PivotTables generally need a refresh. Test the complete update process before relying on it for regular reporting.
What should a basic sales dashboard include?
A useful starting point is revenue, target, attainment, a time trend, and one relevant breakdown such as region or product. Include only measures with clear definitions and a purpose for the intended reader.
Can I use the same Excel dashboard on Mac or the web?
Many workbook features are available across Excel for Windows, Mac, and the web, but ribbon locations and feature support can differ. If the dashboard relies on PivotTable options, slicers, or external connections, check the recipient’s Excel version and test the workbook in that environment.
Related
- Power Query Split Cell Into Rows: Steps and Examples
- Power Query Editor: How to Split Text into Multiple Columns
- Power Query Union Tables: How to Append Queries
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Google Sheets Chart Editor: Create and Customize ChartsThe Google Sheets Chart editor is the sidebar for creating and changing charts in a spreadsheet. Select the…
- Excel Pivot Table Date Filters: How to Use Them WellTo filter dates in an Excel PivotTable, open the drop-down beside the date field in the Rows or…
- Excel Pivot Table: Remove Row TotalsTo remove the bottom grand-total row in an Excel PivotTable, select the table and choose PivotTable Design >…
- Excel Histogram Function: Charts, Bins, and FREQUENCYExcel does not have a worksheet function named HISTOGRAM. To make a histogram in Microsoft 365 for Windows,…
- Excel Waterfall Chart Final Column: Set It as a TotalIn Excel for Microsoft 365, select the chart’s final data point, right-click it, and choose Set as Total.…
- Excel Chart Line of Best Fit: How to Add and Read ItTo add a line of best fit in Excel for Microsoft 365 on Windows, select a chart, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.