Sunflower Intelligence
Business Analytics & Data Science

B03 · Introductory

Excel for Business Analytics

10 lessons

B03 · Module 4, lesson 2 · Lesson 10 of 10

Dashboards that answer questions

A dashboard is one page that tells its readers where things stand. Learn to start from the audience’s questions, give every number a comparison, lay the page out so the most important measure comes first, build it on tables, formulas and PivotTables so it refreshes, and add the documentation and checks that make it trustworthy.

About 70 minutes

Before you start

You will be able to

  • Identify a dashboard’s audience and questions, and choose the measures that answer them, each with a definition, a comparison and an owner.
  • Design a one-page layout that puts the most important measure first, with consistent scales, formats and labels and a fitting display for each question.
  • Build dashboard figures with SUMIFS, COUNTIFS, AVERAGEIFS and XLOOKUP driven by a drop-down cell, and with PivotTables, slicers and timelines on one table of data.
  • Document a dashboard’s sources, definitions and refresh date, and add reconciliation checks that prove it agrees with its data.
  • Evaluate a dashboard for common failures and accessibility problems, and plan how it is shared and kept under version control.

Review first

Fictional case A fictional coffee business founded by Ana Lim. It starts as a catering and barista-training service, opens its own cafés and an online shop for beans, and plans a canned cold brew.

The idea

Fictional case

It is the first working day of January 2027. Lantern’s catering business held 76 events in 2026 and invoiced $62,320 for them, from 18 clients. Ana Lim reviews catering every month, and Lantern’s accountant looks at it every quarter. Until now each review has begun with an hour of filtering the events list. Ana wants one page that answers her standing questions as soon as she opens it, refreshes from the events list in minutes, and is trustworthy enough to send to the accountant.

A dashboard answers someone’s questions

B01 defined a dashboard as a single screen or page showing a small set of KPIs, each with its current value, target, comparison and trend, for the people who act on them. Stephen Few, a consultant who has written widely on dashboard design, described it as a “single-screen display of the most important information needed to do a job”. The words that matter are most important and a job: a dashboard is designed for particular readers and the decisions they make, not to show everything the data contain.

So design starts with the audience and their questions, not with the data. Ana decides staffing, pricing and which clients to chase; the accountant checks that catering is on plan and that the figures agree with the books. Their standing questions are:

  1. Are we on plan this month, and for the year so far?
  2. Is the business growing from month to month?
  3. What kinds of event bring in the revenue?
  4. Is the typical job holding its size?
  5. How much do we depend on a few clients?

From questions to measures

Each question becomes a measure, and each measure is written down with its exact definition, the comparison that gives it meaning and its owner, in the key performance indicator dictionary that B01 introduced. Writing the definitions forces decisions: is revenue counted when an event happens or when it is invoiced? Which month does Meridian Partners’ two-day conference belong to?

Ana’s catering dashboard: measures and definitions (fictional plan and ceiling)
MeasureDefinitionCompared withOwner
RevenueSum of invoices for events held in the month; a two-day event counts in the month it startsThe month’s plan; last monthAna
EventsNumber of events held in the monthLast monthAna
Average invoiceRevenue ÷ events for the monthLast month; the year so farAna
Revenue by event typeSum of invoices by type, year to dateShare of the year’s revenueAna
Top-four client shareRevenue from the four largest clients ÷ total revenue, year to dateA ceiling of 50%Ana
Revenue, year to dateSum of invoices since March 2026Plan to dateThe accountant

The last measure but one deserves a word. Client concentration is the share of revenue that comes from the largest few clients. In 2026 Lantern’s top four clients brought in $29,420 of the $62,320, 47.2%: the loss of one of them would hurt. Ana sets a ceiling of 50%, a guardrail in B01’s sense, which the dashboard watches.

A number needs a comparison

Few’s blunt test for any number on a dashboard is “compared to what?”. A tile reading “Revenue $10,120” can’t be judged on its own. Three comparisons are standard: a target or plan, the previous period, and the same period last year, which removes the seasons. Each is a baseline in B01’s sense. Lantern began catering in March 2026, so it has no last year yet; its plan and the previous month have to do.

With them, December’s figure tells a story: $10,120 was the best month of the year, $1,330 more than November, yet $880 below the month’s plan of $11,000 (92.0%). Show the comparison as a gap or a share, as B01’s KPI lesson did, and say which way is good in words, not only in colour.

Layout: the most important first

Readers of left-to-right languages start at the top left, and many stop once they have what they came for. Put the headline numbers there, the explanations below, and the detail last. The UK Government Analysis Function’s guidance calls this an inverted pyramid, “with your most important headline content up front”, and asks dashboards to minimise scrolling. Ordering a page by position, size and emphasis in this way is its visual hierarchy.

Ana’s catering dashboard for December 2026, as a wireframe. Headline tiles first, then the trend and the mix, then the detail and the notes. The bars are drawn to scale from the events data.

Read the wireframe in its own order. The tiles answer “are we on plan?” in a second; the column chart answers “is it growing?” and shows the quiet summer; the type bars and the client table answer the questions about mix and dependence. The notes say what the numbers mean, where they came from and whether they add up. Nothing on the page is there for decoration.

In depth

A display for each question

The charts lesson matched charts to questions; a dashboard applies that rule to a whole page.

  • Headline measures are tiles: the number, its comparison, and a word or symbol for the direction (“below plan”, ▼). Use the same layout and format for every tile.
  • Trends are columns or a line along a time axis. Monthly revenue against plan suits columns with the plan as a marker, so the bars stay the main message; columns start at zero.
  • Composition and ranking are sorted bars. Five event types compare far better as bars than as slices; keep pies, if at all, for two or three parts of a whole.
  • A few items with details, such as top clients with their revenue and events, are a small sorted table, with the rest grouped as “others” if they are shown at all.

Avoid what spends space without informing. Gauges and dials take room for one number and add no comparison of their own; 3-D effects distort sizes; a second vertical axis invites false comparisons; and decoration such as shading, pictures and borders competes with the data. Few’s review of failed dashboards singles out gauges and decoration in the same way.

Keep everything consistent so that differences mean something. One money format throughout (whole dollars on a summary page), one colour for revenue in every chart, the event types always in the same order, and the same axis range for charts that are meant to be compared side by side. A reader who has to work out each chart afresh will misread one of them.

Building it so it refreshes

A dashboard that takes an hour to update will be out of date. Build it in three layers: the data, untouched, in one Excel table (Events) that grows as rows are added; the calculations, as formulas and PivotTables that read the table; and the page, which only displays results. When January’s events are added, formulas that refer to the table’s columns pick them up at once, and PivotTables pick them up when they are refreshed (the PivotTables lesson shows how).

The tiles are formulas driven by one input cell, the month the reader chooses. Each formula reads the month from that cell and builds a date window from it, so a single change updates the whole page:

The tile formulas (events in A2:D35: date, client, type, invoice; plan in G2:H4; revenue by month in G7:H9)
CellShowsFormula
F1Month chosen (drop-down: October, November or December)12
F2First day of that month=DATE(2026,F1,1)
F3Revenue=SUMIFS($D$2:$D$35,$A$2:$A$35,">="&F2,$A$2:$A$35,"<="&EOMONTH(F2,0))
F4Events=COUNTIFS($A$2:$A$35,">="&F2,$A$2:$A$35,"<="&EOMONTH(F2,0))
F5Average invoice=F3/F4
F6Plan=XLOOKUP(F2,G2:G4,H2:H4)
F7Revenue less plan=F3-F6
F8Share of plan=F3/F6
F9Largest invoice=MAXIFS($D$2:$D$35,$A$2:$A$35,">="&F2,$A$2:$A$35,"<="&EOMONTH(F2,0))
H7Revenue for the month in G7 (filled to H9)=SUMIFS($D$2:$D$35,$A$2:$A$35,">="&G7,$A$2:$A$35,"<="&EOMONTH(G7,0))
F10Check: the months add up to the data=IF(ROUND(SUM(H7:H9)-SUM(D2:D35),2)=0,"OK","CHECK")

In the full workbook the ranges are the table’s columns, such as Events[invoice], so they grow with the data. The third widget under Explore is this sheet.

The average invoice is the month’s revenue divided by its events, not an average of other averages; the challenging example shows why. The charts sit on PivotTables built from the same table, so every figure on the page comes from one source.

Interactivity: drop-downs, slicers and timelines

A drop-down list lets the reader choose what the formulas show without typing. In Excel it is data validation: select the cell, then Data › Data Validation, set Allow to List and give the Source. If the list itself is an Excel table, the drop-down updates as items are added or removed. The Error Alert tab’s Stop style refuses anything typed that isn’t on the list, so a reader can’t type a month the formulas can’t read (pasted values still get through, as the tables lesson warns).

A slicer is a panel of buttons that filters a table or a PivotTable and shows which items are selected. Click inside the table or PivotTable and choose Insert › Slicer; select one item or several, and use Clear Filter to show them all again. One slicer can drive several PivotTables through Report Connections, but only PivotTables that share the same data source, which is another reason to build them all on one table. A timeline does the same job for dates in PivotTables: a slider that selects years, quarters, months or days (PivotTable Analyze › Insert Timeline).

Documentation and checks

A dashboard is trusted only if its readers know what the numbers mean and can see that they add up. A short notes panel does the first job: the source and how it was extracted; the period covered (the Analysis Function’s guidance is to make the dates always clear); the date the data were refreshed, typed in when it happens rather than calculated with TODAY(), which would show the date the file was opened; the definitions, or a link to the KPI dictionary; and the owner. When something changes, a dated change note says what and why.

A reconciliation check does the second. It compares a total on the page with the same total computed another way and flags any difference: the ten monthly revenues (March to December, in J2:J11) should add up to the total of the Events table, so should the five event types, and both should match the $62,320 of catering revenue in Lantern’s books (F02); the events should number 76. A check cell such as =IF(ROUND(SUM(J2:J11)-SUM(Events[invoice]),2)=0,"OK","CHECK") sits beside the figures it protects. Rounding to the cent stops the tiny binary differences of the cells-and-formulas lesson from raising false alarms.

Common failures

  • Too much. Eleven charts make the important three hard to find. Each measure must answer a question someone acts on.
  • No comparison. Numbers without a plan, last period or last year can’t be judged.
  • Decoration. Gauges, 3-D and pictures spend space and attention without informing.
  • Stale data. A page with no refresh date invites decisions on old figures; timeliness is part of data quality (B01).
  • Inconsistent scales and formats. Cents in one chart and thousands in another, or axes that differ without warning.
  • Colour alone. Red and green with no words or symbols fail readers who can’t tell them apart.
  • Drifting definitions. A measure quietly redefined breaks its own history; the KPI dictionary and a change note prevent it.

Accessibility

Microsoft’s guidance for accessible spreadsheets applies directly. Give every chart alt text that states what it shows and its main message, and clear titles and axis labels. Name each sheet descriptively and remove blank sheets. Avoid merged cells and blank rows and columns, which confuse screen readers. Turn on header rows in tables.

Keep a high contrast ratio between text and background, and never let colour carry meaning alone. Run Review › Check Accessibility before sharing.

Even so, dashboards are hard to make fully accessible. The Analysis Function notes that they rarely meet every accessibility requirement and advises publishing the data in another format as well, and in the UK public-sector dashboards must meet WCAG 2.2 at level AA. For Ana that means sending the accountant the underlying table alongside the page.

Sharing and version control

Readers should not be able to break the page. Send a PDF or a read-only copy, or protect the formula and data areas and leave only the drop-down cells open. Keep one master workbook with one owner. If it is stored in OneDrive or SharePoint, Version history lets you open an earlier version and restore it (a personal Microsoft account keeps the last 25 versions); record what changed in a change log sheet, dated, so that a number that moves between two versions can be explained.

Formulas

Gap to plan and share of plan

Gap=A−P,Share of plan=AP×100\text{Gap} = A - P, \qquad \text{Share of plan} = \dfrac{A}{P} \times 100
AA
Actual value for the periodunits of the measure
PP
Planned (target) value for the same periodunits of the measure

For a measure that is itself a percentage, the gap is in percentage points (B01).

Change on the previous period

%Δ=At−At−1At−1×100\%\Delta = \dfrac{A_t - A_{t-1}}{A_{t-1}} \times 100
%Δ\%\Delta
Percentage change on the previous period%
AtA_t
Value this periodunits of the measure
At−1A_{t-1}
Value in the previous periodunits of the measure

Average invoice from totals

Iˉ=∑mRm∑mNm\bar{I} = \dfrac{\sum_{m} R_m}{\sum_{m} N_m}
Iˉ\bar{I}
Average invoice over the months$ per event
RmR_m
Revenue in month m$
NmN_m
Events in month mevents

Assumes. Each month counts in proportion to its number of events.

The plain average of the monthly averages weights a 3-event month as heavily as a 13-event month, and is wrong unless every month has the same number of events.

Top-k client share

Ck=∑c∈top kRcR×100C_k = \dfrac{\sum_{c \in \text{top } k} R_c}{R} \times 100
CkC_k
Share of revenue from the k largest clients%
kk
Number of clients counted (Ana uses 4)clients
RcR_c
Revenue from client c over the period$
RR
Total revenue over the period$

Worked examples

A first example: December’s headline tiles

Fictional figures

December 2026: revenue $10,120 from 13 events; plan $11,000. November: revenue $8,790 from 11 events. Year to date (March to December): revenue $62,320 against a plan of $60,000. Calculate what the four tiles should show.

  1. Revenue against plan: gap 10,120−11,000=−88010{,}120 - 11{,}000 = -880 dollars; share 10,120÷11,000×100≈92.0%10{,}120 \div 11{,}000 \times 100 \approx 92.0\%. Against November: 10,120−8,790=1,33010{,}120 - 8{,}790 = 1{,}330 dollars, 1,330÷8,790×100≈15.1%1{,}330 \div 8{,}790 \times 100 \approx 15.1\% up.
  2. Events: 13 against November’s 11, up 2.
  3. Average invoice: 10,120÷13≈778.4610{,}120 \div 13 \approx 778.46 dollars in December against 8,790÷11≈799.098{,}790 \div 11 \approx 799.09 in November, down $20.63 or 2.6%.
  4. Year to date: 62,320÷60,000×100≈103.9%62{,}320 \div 60{,}000 \times 100 \approx 103.9\% of plan, $2,320 ahead.

Answer. Revenue $10,120: 92.0% of plan ($880 below), up 15.1% on November. Events 13, up 2. Average invoice $778.46, down 2.6% on November’s $799.09. Year to date $62,320: 103.9% of plan, $2,320 ahead.

What it means. December was the busiest month and still missed its plan, because the plan expected a bigger jump than came, and the jobs were slightly smaller on average. The year as a whole beat its plan. Each tile needs both its comparisons to tell that story; a single “$10,120” tells none of it.

A harder example: The average invoice that wasn’t

Fictional figures

A draft of the dashboard shows “average invoice, 2026: $872.23”, calculated as the average of the ten monthly average invoices. Monthly events ranged from 3 (March) to 13 (December), and March included Meridian Partners’ two-day conference, invoiced at $4,200. Find the right figure, explain the difference and decide how the average should be shown by event type.

  1. The year’s average invoice comes from totals: 62,320÷76=82062{,}320 \div 76 = 820 dollars.
  2. The draft weights each month equally. March’s average was 5,600÷3≈1,866.675{,}600 \div 3 \approx 1{,}866.67 dollars because one of its three events was the $4,200 conference; in the average of averages that one month counts as much as December’s 13 events, pulling the result up by $52.23.
  3. The same outlier distorts the type averages: conferences average 14,540÷13≈1,118.4614{,}540 \div 13 \approx 1{,}118.46 dollars, but without the two-day conference the other 12 average (14,540−4,200)÷12≈861.67(14{,}540 - 4{,}200) \div 12 \approx 861.67 dollars.
  4. Decide. Show averages computed from totals, and footnote the conference (it is genuine, so it stays in the revenue), or show the median invoice, $790 for the year, beside the mean. B02 treated the same choice for outliers.

Answer. The year’s average invoice is $820 (total revenue ÷ total events), not $872.23. Conferences average $1,118.46 with the two-day event and $861.67 without it: show the first with a footnote, or add the median.

What it means. An average of averages is right only when every group is the same size. Dashboards are full of rates and averages by month, type and client, so the rule “divide totals, don’t average averages” matters on almost every page.

Applied: a business decision: From first draft to one page

Fictional figures

Ana’s first draft had eleven charts: a 3-D pie of revenue by all 18 clients, a gauge for the month’s revenue, a pie of events by type, two tables of every event, and more. Months above and below plan were coloured green and red with no labels, revenue was shown to the cent in some charts and in thousands in others, and nothing said when the data had been refreshed. Redesign it for Ana and the accountant.

  1. Start from the questions (on plan? growing? which types? job size? dependence on clients?) and keep only the displays that answer them: four tiles, the monthly column chart with plan markers, the sorted bars by type and a small top-clients table.
  2. Replace the gauge with the number and its comparisons ($10,120: 92.0% of plan, up 15.1% on November), and the 18-slice 3-D pie with the top four clients and their share, grouping the rest.
  3. Add the concentration measure the draft lacked: 29,420÷62,320≈47.2%29{,}420 \div 62{,}320 \approx 47.2\% of revenue from four clients, under Ana’s 50% ceiling but close, and Meridian’s 14.4% includes the conference, which may not recur.
  4. Make the page trustworthy: one money format (whole dollars), “above” and “below” in words beside any colour, the period and refresh date in the notes, and check cells showing that the months and the types both total $62,320, the catering revenue in Lantern’s books.
  5. Build it to refresh: the Events table as the single source, tile formulas reading a drop-down month cell, PivotTables for the charts, and one slicer for event type connected to both PivotTables, kept away from the formula-driven tiles.

Answer. One page with four tiles (revenue against plan and last month; events; average invoice; revenue year to date against plan), a monthly column chart with plan markers, sorted bars by event type, a top-four client table with their 47.2% share, and a notes panel with definitions, source, period, refresh date and reconciliation checks.

What it means. The redesign removed more than it added. What it added, the comparisons, the concentration measure and the checks, is what turns a collection of charts into a tool for decisions.

Explore

Revenue by month against Ana’s plan. Which months beat the plan? Is December a good month or a bad one? Decide what a tile for December should say.

Loading the interactive…

The PivotTable behind the type chart. Filter by Month (as a slicer would), change the rows to Client to find the top clients, and switch to Average to see the conference outlier.

Loading the interactive…

One month of the dashboard, built from the October to December events. Pick a month from the drop-down and watch every tile follow; use Show formulas to read the date windows and the check in F10.

Loading the interactive…

Common mistakes

  • “Put everything on it, in case someone asks.”

    Each extra chart makes the important ones harder to find. Keep the measures the readers act on; leave the rest in the workbook, one click away.

  • “Red is bad and green is good; everyone can see that.”

    Not everyone can tell red from green, and a printed page in grey loses both. Say “below plan” or use a symbol as well as the colour.

  • “The slicer filtered the table, so the SUMIFS tiles changed.”

    They didn’t. SUMIFS and COUNTIFS add every row that meets their criteria, hidden or not; only SUBTOTAL ignores rows a filter excludes. Drive formula tiles from a drop-down cell, or use PivotTables with the slicer.

  • “The average of the monthly averages is the year’s average.”

    Only if every month has the same number of events. Divide total revenue by total events: $62,320 ÷ 76 = $820, not $872.23.

  • “=TODAY() shows readers the data are current.”

    TODAY() shows the date the file is opened, so it makes old figures look new. Type the date the data were refreshed, and state the period covered.

  • “A gauge makes it look like a real dashboard.”

    A dial takes the space of several numbers to show one, and its scale ends are arbitrary. The number with its comparison says more in less space.

Guided practice

Work through these with hints when you need them. Every hint and every worked solution is there to be used.

Loading four questions…

On your own

No hints this time. Answer, check, and read the feedback; the solution appears once you have answered or if you ask for it. Your first answers count toward mastery.

Loading six questions…

In practice

Dashboards are among the most common things analysts build, and the same design decisions recur everywhere. Three fictional examples show the range.

A hotel group’s weekly page. The operations director of three hotels reviews occupancy, average room rate and revenue per available room every Monday, each against budget and the same week last year. The page also shows the next four weeks’ bookings already made, a leading indicator, and a single exceptions list of hotels more than five points below budget.

A charity’s fundraising page. The trustees see income year to date against the plan, the share from regular donors (their guardrail against relying on one-off appeals) and the cost of raising each dollar. A notes panel explains that legacy gifts are counted when they are confirmed, not when they are promised, after a year in which a promised gift that never arrived flattered the figures.

A gym’s membership page. The manager tracks members, joiners and leavers by month, with a drop-down for the site. A reconciliation check compares the membership system’s count with the payments system’s every month; when the two disagreed, the dashboard found a batch of failed card payments before the accountant did.

Fictional case

At her January review Ana reads the page in a minute. The year beat its plan, but the fourth quarter came in at $27,900 against a plan of $28,000, and December missed by $880. The type bars show meetings bringing the most revenue and launches the least. The client table worries her most: four clients bring 47.2% of the revenue, close to her 50% ceiling. She sets two actions for the first quarter, winning three new clients and applying the new hourly rule from the what-if lesson to long events, and adds both to the change log so that March’s page can show whether they worked.

A dashboard is the end of an analysis, not the start: it is where the cleaned data, the formulas, the PivotTables and the charts of this course meet the people who decide. Its quality is measured by the decisions it makes easier, which is why every choice on the page, from the measures to the date in the corner, should be traceable to a question someone asked.

Knowledge check

Six questions, one attempt each, no hints. Score 80% or more to complete the lesson; you can take it again at any time.

Loading six questions…

Summary

  • A dashboard is one page of the few measures its audience acts on; design starts from the readers and their questions, not from the data.
  • Write each measure down with its definition, comparison and owner; client concentration (the top clients’ share of revenue) is a measure of risk worth watching.
  • Every number needs a comparison: a plan or target, the previous period, or the same period last year.
  • Put the most important measure first (top left, an inverted pyramid), choose the display from the question, and keep formats, colours and scales consistent; avoid gauges, 3-D and decoration.
  • Build on one Excel table: formula tiles driven by a drop-down cell and date windows, PivotTables refreshed from the table, slicers and timelines connected to PivotTables with the same data source.
  • Slicers and filters don’t change SUMIFS results; SUBTOTAL ignores filtered-out rows. Use one mechanism per part of the page.
  • Document the source, period, refresh date and definitions, and add reconciliation checks that prove the page agrees with its data.
  • Make it accessible (alt text, names, contrast, no colour-only meaning), share a protected or read-only copy, and keep versions and a change log.

Key formulas

Terms introduced

Client concentration
The share of revenue that comes from a business’s largest few clients; a high share means the loss of one client would hurt badly.
Visual hierarchy
The ordering of a page by position, size and emphasis so that readers see the most important information first.
Drop-down list
A cell limited by data validation to the values in a list, which the user picks from an arrow beside the cell; in a dashboard it lets the reader choose what the formulas show.
Timeline
An Excel filter for the date fields of PivotTables: a slider that selects years, quarters, months or days.
Reconciliation check
A formula that compares a total shown in a report with the same total calculated another way, such as from the source data or the accounts, and flags any difference.
Year to date
The period from the start of the year, or of the series, to the end of the latest complete period. Abbreviated YTD.

Sources

  1. Few, S. (2007). Why Most Dashboards Fail. Perceptual Edge. (Copy hosted for a UC Berkeley School of Information course.) blogs.ischool.berkeley.edu/i247s13/files/2013/02/WhyMostDash…
  2. Government Analysis Function (2026). “Data visualisation: testing dashboards for design and accessibility” and “Data visualisation: building and managing dashboards” (analysisfunction.civilservice.gov.uk/policy-store/data-visualisation-building-and-managing-dashboards/). UK Government Statistical Service. analysisfunction.civilservice.gov.uk/policy-store/data-visua…
  3. Microsoft. “Use slicers to filter data” and “Create a PivotTable timeline to filter dates”. Microsoft Support. support.microsoft.com/office/249f966b-a9d5-4b0f-b31a-1265178…
  4. Microsoft. “Create a drop-down list”. Microsoft Support. support.microsoft.com/office/7693307a-59ef-400a-b769-c5402dc…
  5. Microsoft. “SUBTOTAL function”. Microsoft Support. support.microsoft.com/en-us/excel/subtotal-function
  6. Microsoft. “Accessibility best practices with Excel spreadsheets”. Microsoft Support. support.microsoft.com/en-us/office/accessibility-best-practi…
  7. Microsoft. “View previous versions of Office files”. Microsoft Support. support.microsoft.com/topic/5c1e076f-a9c9-41b8-8ace-f77b9642…