Count the matching orders
How many Online orders came from East?
Formula: =SUMIFS(D2:D6,A2:A6,F2,B2:B6,G2) → 16
Hands-on Excel exercises
Excel Dashboard Practice
Practice building an Excel dashboard with live KPI formulas, cleanup, PivotTables, charts, validation, and a guided sales dashboard project.
A dashboard is a decision surface, not a collection of colorful charts. Start by naming the audience, the question each KPI answers, and the grain of the source data. The live spreadsheet below uses SUMIFS to calculate a filtered KPI from transaction rows. Change a criterion and watch the number respond—the same dependency sits beneath many dashboard cards.
The linked Excel dashboard practice covers source cleanup, validation, conditional totals, PivotTables, chart choice, advanced charts, PivotCharts, and a full sales dashboard project. Build the calculations before polishing the layout. Keep raw data separate from analysis and presentation, use consistent date and category fields, and make filter state visible. Every KPI should be traceable to a range, formula, pivot, or query that another person can inspect.
The worked answers show how to define a KPI, choose a chart for the comparison, and audit the final result. A dashboard is finished when the numbers survive refresh and edge cases, not when the colors match. For role-specific context, continue with the finance, sales and marketing, or data analyst path; each uses reporting skills for a different set of business decisions.
Your turn
Your turn: in H2, calculate Online revenue for East using the criteria in F2 and G2.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Channel | Region | Revenue | Orders | Channel | Region | ||
| 2 | Online | East | 1200 | 12 | Online | East | ||
| 3 | Retail | East | 850 | 7 | ||||
| 4 | Online | West | 900 | 9 | ||||
| 5 | Partner | East | 600 | 5 | ||||
| 6 | Online | East | 400 | 4 |
Not yet solved.
Answer: =SUMIFS(C2:C6,A2:A6,F2,B2:B6,G2)
Related exercises
Open a task in its lesson.
- In G7, add up G2:G6. Now SUM sees the numbers.
- Make a dropdown list of statuses in C2:C7, using F2:F4 as the source.
- In F3, count how many deals in Chicago are worth more than $1,000.
- Build a PivotTable: the sum of sales by rep, with the result in F1.
- Build a line chart of revenue and expenses together (A1:C7).
- Build a combo chart from A1:C7: revenue as columns, orders as a line.
- Build a PivotTable in F1: the sum of revenue by category.
- Make a dropdown list of months in F1, using H2:H7 as the source, and pick "March".
- Build a doughnut chart from E8:F11 and switch the month in F1: the chart changes.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Total all East revenue
What is total revenue for East across every channel?
Formula: =SUMIF(B2:B6,"East",C2:C6) → 3050
Frequently asked questions
What should an Excel dashboard include?
Include only KPIs and charts tied to the audience’s decisions, plus a visible period, filter state, and enough context to interpret change.
Should I use formulas or PivotTables?
Either can work. Formulas suit controlled KPI layouts; PivotTables are strong for grouped exploration. Many dashboards combine both.
How many charts should a dashboard have?
There is no fixed number. Use the smallest set that answers the decisions clearly and leaves room for labels and context.
How do I check dashboard accuracy?
Trace every KPI to source data, compare key totals with an independent method, test filters and dates, and verify refresh with new rows.