Part of the free Module 11: Excel Dashboards · Lesson 10 of 10 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.
Excel dashboard design is the practice of laying out a small number of KPIs, charts and controls on one screen so a reader can answer three to five business questions in under a minute. A good design decides the questions first, then the numbers, then the visuals, and only then the colours. This lesson gives you the rules that every project in this course follows.
The three layers of an Excel dashboard
Before you draw a single chart, separate the workbook into three layers. Every dashboard in this course, from the Quick Dashboard to the Project Management Dashboard, uses this split. It keeps the dashboard sheet free of raw numbers and makes the file easy to refresh.
| Layer | Sheet name | What it holds | Rule |
|---|---|---|---|
| Data | Data | One flat table, one row per record, real dates | Nobody types here except to paste a fresh export |
| Calculation | Calc or Support | SUMIFS, INDEX and MATCH, pivot tables, control link cells | Every number the dashboard shows is computed here |
| Dashboard | Dashboard | Cards, charts, slicers, Form Controls, titles | Only links to the calculation layer, no hard-coded values |
If a visual needs a number that does not exist yet, add it to the calculation sheet rather than writing a formula inside the chart’s source range on the dashboard. The next lesson covers the data and calculation layers in detail.
Start with the questions and the audience
A dashboard is not a place to show everything you have. Write down who will read it and the questions they ask every week. A sales manager wants to know whether the team is on target, which region is behind and what the trend looks like. A finance controller wants variance against budget. The same data produces two different dashboards.
- Name the audience in one line: “Regional sales managers, reviewed every Monday”.
- List the three to five questions they need answered. If you have more than seven, split the dashboard into two pages.
- For each question, choose one KPI that answers it. One question, one number.
- Decide the filters the reader needs: month, region, product. Each filter becomes a control.
- Only now open Excel.
How to choose KPIs for a dashboard
A KPI earns its place when a reader can act on it. Revenue, target achievement, growth against last year and the top or bottom performers are actionable. Row counts and averages of averages usually are not. Pair each KPI with the question it answers and the visual that shows it best.
| KPI | Question it answers | Best visual |
|---|---|---|
| Total sales, month to date | Where are we right now? | KPI card with variance against target |
| Target achievement percent | Are we on track? | Gauge or progress circle, or a card with an arrow |
| Sales by month | What is the trend? | Line chart, twelve or thirteen months |
| Sales by product | Which products sell? | Column chart, sorted largest to smallest |
| Sales by region | Who is ahead and who is behind? | Horizontal bar chart, sorted |
| Share of channel | How is the total split? | Doughnut with a maximum of four segments |
| Single percentage such as service level | How full is the container? | Waffle chart or a filled card |
Cards show a single number. Lines show change over time. Columns and bars compare categories. Doughnuts and waffles show part of a whole. Gauges show one value against a scale. If a KPI does not fit one of these, question whether it belongs on the dashboard.
Dashboard layout rules in Excel
Readers scan a screen in an F pattern: across the top, then down the left side. Put the most important number top-left and the least important detail bottom-right. Build the layout on a grid of equal-sized blocks so cards and charts line up.
- Fit one screen. Design for 1366 by 768 pixels at 100 percent zoom. That is roughly columns A to Z and rows 1 to 35 at default sizes. Nobody should scroll to find a KPI.
- Top row is for cards. Four to six KPI cards across the top, each the same width and height.
- Middle row is for trends and comparisons. The line chart goes left, the category chart right.
- Bottom row is for detail. A small table, a ranked list or the filters.
- Group related items. Put the month selector next to the charts it drives, not in a far corner.
- Align everything. Select the shapes or charts, then use Shape Format > Align > Align Top and Distribute Horizontally. Hold Alt while dragging to snap objects to cell borders.
- Turn gridlines off. View > Show, untick Gridlines and Headings. A white or light grey background makes the cards stand out.
- Leave white space. One blank column between blocks is enough. Do not fill every cell.
Colour rules that keep a dashboard readable
Colour should carry meaning, not decoration. The Sales Dashboard in this course uses one dark header, grey context and a single accent, and it reads clearly even in a printed handout.
- Grey is the default. Axis labels, gridlines, past months and unselected categories are mid-grey.
- One accent colour. Use it for the selected month, the current year or the KPI the reader asked about. When everything is bright, nothing stands out.
- Red and green mean bad and good. Never use them for categories such as regions. Pair them with an arrow or a plus and minus sign so colour-blind readers get the same message.
- Three to four colours maximum. Background, text, grey and one accent. A second accent only if the dashboard compares two series, such as actual against target.
- Check contrast. Dark text on light cards, or white text on dark cards. Avoid mid-tone text on mid-tone fills.
- Dark or light theme. Light themes print well and suit finance and operations reports. Dark themes look good on a screen in a meeting room but waste ink and hide thin lines. Pick one per workbook and keep it.
Typography and number formats
Use one font family throughout, usually the theme font such as Calibri, Aptos or Segoe UI. Three sizes are enough: 10 for labels and axis text, 12 for table body and chart titles, 18 to 24 for the big number on a KPI card. Bold is for the value, not the label.
Numbers on cards should be short. A card that reads 1,284,930 takes longer to read than 1.28M. Apply a custom number format to the linked cell, or wrap the value in TEXT.
| Value | Custom format or formula | Displays |
|---|---|---|
| 1284930 | #,##0,"K" |
1,285K |
| 1284930 | #,##0.00,,"M" |
1.28M |
| 0.0873 | 0.0% |
8.7% |
| -0.052 | +0.0%;-0.0%;0.0% |
-5.2% |
| 1284930 in a text box | =TEXT(Calc!B2,"#,##0,")&"K" |
1,285K |
Keep percentages to one decimal place and currency to whole units unless the audience needs pence. Right-align numbers in tables and left-align text. The TEXT function page lists more format codes.
Which chart to use
Choose the chart from the question, never from the chart gallery. This table covers almost every case you will meet on a business dashboard.
| Question | Use | Avoid |
|---|---|---|
| How has it changed over time? | Line chart, or column chart for under eight periods | Pie, area with many series |
| How do categories compare? | Sorted column or bar chart | Radar, 3-D column |
| What is the share of the whole? | Doughnut with two to four segments, or a 100 percent stacked bar | Pie with more than five slices, exploded pie |
| Actual against target? | Combo chart of columns plus a target line, or a bullet chart | Two separate charts, secondary axis without labels |
| One value on a scale? | Gauge, progress circle or a card with an arrow | A single bar with no scale |
| Trend inside a card? | Sparkline in the cell beside the value | A full chart per card |
Worked example
A regional sales manager wants a one-page dashboard she can read in a Monday meeting. Her questions are: what did we sell this month, are we on target, is the trend improving, which products and regions drive the number, and can I filter by month and region. That gives four cards, two charts, one table and two controls. The wireframe is a three-row grid on the Dashboard sheet.
| Row | Cells | Content | Source formula or feature |
|---|---|---|---|
| Title bar | B2:Y3 | Dashboard name, selected month, last refreshed date | ="Sales Dashboard - "&TEXT(Calc!B1,"mmmm yyyy") |
| Row 1 | B5:Y9 | Four cards: Sales MTD, Target achievement, Orders, Average order value | =SUMIFS(Data[Amount],Data[Month],Calc!$B$1) and =C5/Calc!B3 |
| Row 2 | B11:M24 and O11:Y24 | Left: twelve-month sales line. Right: sales by product, sorted columns | Line feed from a SUMIFS row per month, product feed from a pivot or SUMIFS per product |
| Row 3 | B26:M34 and O26:Y34 | Left: region table with sales, target and variance. Right: month combo box and region slicer | Table via =SUMIFS per region, combo box cell link in Calc!B1 |
Suppose the Calc sheet holds these three product totals for the selected month.
| Product | Sales | Target | Variance |
|---|---|---|---|
| Laptops | 428,500 | 400,000 | +7.1% |
| Monitors | 212,300 | 250,000 | -15.1% |
| Accessories | 96,800 | 90,000 | +7.6% |
The Sales MTD card shows 738K with the format #,##0,"K". The Target achievement card shows 99.7% in grey with a small downward arrow, because 738,000 against a 740,000 target is below 100 percent. The product column chart is sorted so Laptops is first, and Monitors is the only bar coloured red because its variance is negative: the chart uses two helper series, =IF(D2<0,B2,NA()) for the red series and =IF(D2>=0,B2,NA()) for the grey one. Everything else on the page is grey with one blue accent for the selected month on the line chart.
Tips and common mistakes
- Remove chart junk. Delete chart borders, background fills, major gridlines you do not need and legends with one series. Every pixel should carry data.
- Never use 3-D charts. Perspective distorts the values and the front bars hide the back ones.
- No pies with eight slices. Readers cannot compare angles. Use a sorted bar chart instead.
- Label secondary axes. A dual-axis chart without axis titles misleads. If the two series are on different scales, consider two small charts.
- Do not rely on red and green alone. Around eight percent of men cannot tell them apart. Add arrows, signs or labels.
- Add a data source note. A small grey line at the bottom: source system, refresh date, owner. It answers the first question any executive asks.
- Test at 100 percent zoom on a laptop. A dashboard that looks fine on a 27-inch monitor often needs scrolling on a 14-inch screen.
Design review checklist
| Area | Check | Pass when |
|---|---|---|
| Purpose | Audience and questions written down | Every visual maps to one listed question |
| Structure | Data, Calc and Dashboard sheets exist | No raw data or typed numbers on the Dashboard sheet |
| Layout | Fits one screen at 100 percent on 1366 by 768 | No vertical or horizontal scrolling |
| Hierarchy | Most important KPI is top-left | A reader names the headline number in five seconds |
| Alignment | Cards and charts share edges and sizes | Align tools used, gridlines off |
| Colour | Grey plus one accent, red and green only for status | No more than four colours on the page |
| Charts | Type matches the question | No 3-D, no pies over five slices, no unlabelled dual axis |
| Numbers | Short formats, one decimal on percentages | No card longer than seven characters |
| Controls | Filters sit next to what they filter | Every control changes at least one visual |
| Trust | Source, refresh date and owner shown | Note visible without scrolling |
Practice exercise
- Pick any project in this course, for example the Performance Dashboard, and write down the audience and the five questions it answers. Score it against the review checklist above.
- On a blank sheet, wireframe a dashboard for your own data as a three-row grid using cell borders only: four cards, two charts, one table and one control. Do not add data yet.
- Take an existing chart and reduce it to one accent colour and grey. Remove the border, background and any gridline that does not help. Compare before and after.
- Format a cell holding 2,481,730 so it reads 2.48M, then build a card text box that shows it with a plus or minus variance below.
- Replace a pie chart with more than five slices in any workbook with a sorted bar chart and note how the reading time changes.
Key takeaways
- Decide the audience and three to five questions before opening Excel; each question becomes one KPI.
- Keep data, calculation and dashboard on separate sheets so the dashboard only links to computed cells.
- Most important number top-left, cards on top, trends in the middle, detail and filters at the bottom, all on one screen.
- Grey for context, one accent for focus, red and green only for bad and good, never more than four colours.
- One font, three sizes, short number formats such as
#,##0,"K"and one-decimal percentages. - Choose the chart from the question: line for time, bar for comparison, doughnut for share, gauge for one value on a scale.
Related lessons
- Excel Dashboards course hub
- Prepare data for an Excel dashboard
- KPI cards and Form Controls in Excel
- Excel dashboard checklist: test, protect and publish
- Sales Dashboard in Excel
- Zonal Dashboard in Excel
- C-SAT Dashboard in Excel
- Basic Charts course
- SUMIFS function
- Microsoft Support: TEXT function and format codes
Frequently asked questions
What makes a good Excel dashboard?
A good Excel dashboard answers a defined set of questions for a named audience on one screen. It separates data, calculation and presentation, shows the most important KPI top-left, uses grey with one accent colour, matches each chart type to its question and displays a source and refresh date so the reader can trust the numbers.
How many KPIs should a dashboard have?
Between three and seven. Fewer than three and a single chart would do; more than seven and readers stop scanning. If stakeholders ask for more, build a second page or add a drill-down table below the cards rather than shrinking every visual to fit.
What size should an Excel dashboard be?
Design for the smallest screen it will be viewed on, usually a laptop at 1366 by 768 pixels at 100 percent zoom. That is about columns A to Z and rows 1 to 35 at default widths. Turn gridlines and headings off, and check the layout with View > Page Layout if it will be printed.
Which colours should I use in an Excel dashboard?
Use a light or dark background, one text colour, mid-grey for context and a single accent colour for the item you want the reader to notice. Reserve red and green for bad and good status and pair them with arrows or signs. Keep the total to four colours so the page stays calm.
Should I use a pie chart on a dashboard?
Only for two to four segments where one part clearly dominates. For anything with more slices, a sorted bar chart is faster to read and more accurate. A doughnut with the total in the centre is the dashboard-friendly version of a pie, and a waffle chart works well for a single percentage.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.