Excel dashboard exercises are hands-on projects for turning a worksheet of raw data into a clear, interactive summary. A useful practice sequence is to clean a small dataset, calculate key measures, build a PivotTable and chart, then add filters and check that the dashboard answers a real question.
What dashboard exercises teach
A dashboard is a compact view of important information, usually combining summary figures, charts and controls such as filters. Practising one develops more than chart-making: it teaches you to structure data, choose meaningful measures, check calculations and arrange results so someone can understand them quickly. A polished-looking page is not automatically a useful dashboard; the numbers need to be trustworthy and the display needs to support a decision.
Exercises are most useful when each has a question to answer. For example: Which region generated the most completed revenue? Did monthly revenue rise or fall? How does the result change when a user selects a product? These questions give you a way to judge whether the finished dashboard works, rather than merely whether it contains Excel features.
For a beginner, use one tidy table and build up in stages. Microsoft 365 for Windows includes the standard tools described below. Most ribbon commands are also available in Excel for Mac, although their locations can vary slightly; keyboard shortcuts differ, and Excel for the web has a more limited set of desktop features.
Start with a small, realistic dataset
Practice with enough rows to reveal patterns but few enough to check by hand. For example, create a sales table with columns Date, Region, Product, Units, Revenue and Status. Use eight illustrative records: January North product A, 10 units and $200, Complete; January South product B, 5 units and $150, Complete; February North product B, 8 units and $240, Complete; February South product A, 7 units and $140, Pending; March North product A, 12 units and $240, Complete; March South product B, 6 units and $180, Complete; March North product B, 4 units and $120, Pending; and April South product A, 9 units and $180, Complete.
These are made-up practice figures, not industry benchmarks. Their advantage is that you can check dashboard results yourself: completed revenue totals $1,010, and North’s completed revenue totals $680. Enter the column names once in the first row, keep one record per row, and do not insert blank rows or merged cells inside the source data. In Excel, select the range and choose Home > Format as Table, or press Ctrl+T on Windows and confirm that the table has headers. On Mac, use Command+T. A table expands more reliably when new records are added and gives PivotTables and formulas a clearly defined source.
Exercise 1: Clean and check the source
Before building charts, practise spotting inconsistencies. Check that dates are real Excel dates rather than text, that Revenue contains numbers, and that region, product and status labels use consistent spelling. A value such as “Complete ” with a trailing space can be treated differently from “Complete” by a formula or filter. Use the table filter arrows to inspect distinct-looking entries, and sort a copy of the data by date to find missing or out-of-order records.
Then create a basic quality checklist. Count the records, check for blank cells in required fields, and compare the total revenue with a manual sum or a quick worksheet calculation. If a total is unexpectedly zero, inspect the data type and spelling before changing the formula. This exercise teaches an important habit: a dashboard can display incorrect source data neatly, so validation belongs in the workflow, not just in the final design.
Exercise 2: Build KPI cards with formulas
Choose a few measures that directly answer the question behind the dashboard. For the sample table, completed revenue and completed order count are reasonable starting measures. Assume the headers are in A1:F1 and the eight records are in rows 2:9, with Revenue in E and Status in F. To total revenue where Status is Complete, enter this formula in an empty cell:
With revenue in E2:E9 and status in F2:F9, use =SUMIFS(E2:E9,F2:F9,"Complete"). The illustrative result is $1,010: it adds $200, $150, $240, $240 and $180, excluding the three pending records.
With status values in F2:F9, use =COUNTIF(F2:F9,"Complete") to count completed records. The result is 5. These formulas are useful practice because they require a clear definition of what is being counted or summed. In a real dataset, decide whether “revenue” means booked, invoiced or paid revenue before presenting it as a KPI.
For another KPI, add a target and compare actual results with it, but label the target and its source. A large number without a period, unit or definition can mislead: “$1,010” is less informative than “Completed revenue, Jan–Apr, $1,010.” Avoid filling a dashboard with measures just because they are easy to calculate.
Exercise 3: Summarize with a PivotTable
PivotTables are a fast way to group and aggregate data without writing a separate formula for each category. Select a cell in the source table and choose Insert > PivotTable. Confirm the table or range and choose where to put the PivotTable. In the PivotTable Fields pane, drag Region to Rows, Status to Filters, and Revenue to Values. If Excel shows Count of Revenue instead of Sum of Revenue, open the value field settings and choose Sum; a text-formatted revenue column can cause a count instead.
Set the Status filter to Complete. The resulting regional summary should show North at $680 and South at $330, which add to the $1,010 total. This gives you a second way to verify the formula exercise. Practise changing Region from Rows to Columns, or adding Product as a row beneath Region, and notice how the view changes while the source records remain unchanged.
For a monthly summary, place Date in Rows and Revenue in Values, then group valid date entries by month if Excel displays individual dates. Grouping options depend on the selected date field and the source must contain actual dates. Refresh the PivotTable after source values change using Data > Refresh All. New rows are easiest to include when the source is an Excel table, but check that the PivotTable’s source includes the records you added.
Exercise 4: Pick charts that clarify a pattern
Build a chart from a summarized view, not from a long list of transaction rows. A clustered column chart works well for comparing completed revenue by region or product. A line chart is usually more suitable for a sequence over time, such as monthly revenue, because the order of periods matters. Select the relevant PivotTable or summary and use Insert > Recommended Charts, then choose a chart that makes the comparison easy to read.
Practise improving the chart without adding decoration for its own sake. Give it a descriptive title, use readable category labels, and format the vertical axis as currency if the values are revenue. Keep colors consistent when the same region or status appears in several visuals. Avoid 3-D effects and excessive gridlines, which can make comparisons harder. If a chart has only one series, a legend may be unnecessary; remove it when it repeats information rather than helping interpretation.
Check the chart against its source summary. Does the order of months make sense? Are blank categories or pending records included accidentally? Does the axis start or scale make a small difference look much larger than it is? The goal is not to make every fluctuation dramatic, but to make the comparison accurate and legible.
Exercise 5: Add interactive filters
Make the PivotTable respond to a reader’s choice. Select it and choose PivotTable Analyze > Insert Slicer, then select fields such as Region, Product or Status. A slicer offers clickable buttons for filtering. If the dashboard contains multiple PivotTables based on compatible source data, select a slicer and use Slicer > Report Connections to connect it to the relevant PivotTables. Verify each connection: a slicer does not necessarily control every object automatically.
For date-based data, a timeline can be useful when supported by a PivotTable date field. Select the PivotTable and look for PivotTable Analyze > Insert Timeline. If the command is unavailable, check that the source field contains valid dates and that the selection is a PivotTable using that field. A normal worksheet filter is simpler when users only need to filter a table, while a slicer is often clearer on a presentation dashboard.
Test every control with several selections, including clearing the filter. Confirm that KPI cards, charts and summaries all update as intended. One common mistake is to show a filtered chart beside a KPI formula that still totals every row. If you mix ordinary formulas and PivotTables, make sure the formulas are designed to respect the same filter context or label their different scope clearly.
Exercise 6: Arrange and test the dashboard
Create a separate worksheet named Dashboard and keep the source data and working PivotTables on their own sheets. Put the reporting period or scope near the top, followed by a small group of KPI cards, then the main trend and comparison charts. Place slicers where users can find them without covering the visuals. Use consistent number formats and alignment, and leave enough white space to separate sections.
Useful design practice includes hiding worksheet gridlines on the dashboard sheet through View > Gridlines, but not hiding information users need to interpret the results. Give charts meaningful titles and label units. Use color carefully: if green means a favorable result in one visual and a category in another, readers may infer a relationship that is not there. Check the layout at a typical window size and with long category names, not only while zoomed in on your own screen.
Finish by testing the dashboard as if you had not built it. Ask someone to find the highest-revenue region or compare two months, and watch where they look. Confirm that every slicer works, every number has a period and unit, and a refresh does not break the layout. This review often reveals more useful improvements than adding another chart.
Common problems and ways to improve
- Charts show the wrong totals: Check whether pending records are included, whether a PivotTable is using Sum or Count, and whether filters match the KPI definition.
- A PivotTable misses new rows: Use an Excel table as the source, then refresh. If the rows still do not appear, inspect the PivotTable source range.
- Filters disagree: Confirm slicer Report Connections and test formulas separately. A normal formula may not respond to a PivotTable slicer.
- Dates group strangely: Check for text dates, blanks or invalid entries. Standardize the date column before building a monthly view.
- The dashboard feels crowded: Remove redundant visuals and keep the measures tied to the central question. More charts do not necessarily mean more insight.
To make the exercises harder, add a larger dataset, compare actual results with a clearly defined target, or let the user switch between products and regions. You can also practise documenting assumptions in a small note on the sheet. Do not make a dashboard depend on undocumented manual steps: a colleague should be able to identify the source, refresh the summaries and understand what each figure represents.
Frequently Asked Questions
What is a good first Excel dashboard exercise?
Start with one clean table, one KPI and one chart. For example, calculate completed revenue by region, verify the totals against the source, and display the regional comparison in a column chart. Add interaction only after the basic numbers are correct.
Should I use formulas or PivotTables for a dashboard?
Both are useful. Formulas are direct and flexible for a few defined KPIs; PivotTables are convenient for grouping and exploring categories. A dashboard can use both, but check that their filters and definitions agree.
How do I make an Excel dashboard interactive?
Build the relevant summaries as PivotTables, then add slicers through PivotTable Analyze > Insert Slicer. Connect slicers to compatible PivotTables with Report Connections and test the results. A slicer will not automatically control every formula or chart in a workbook.
Can I practise dashboard building without business data?
Yes. Create a small fictional dataset with dates, categories and numeric measures, and label it as illustrative. You can also use a public dataset, but check its license, definitions and data quality before sharing a dashboard based on it.
What should I learn after basic dashboard exercises?
Practise building refreshable reports from larger tables, documenting metric definitions, checking data quality and designing for a specific audience. Then explore more advanced Excel tools such as Power Query for repeatable data cleaning, when your version and data workflow support it.
Related
- Excel Compare Cell Values Between Two Sheets: Methods
- Excel Clear Print Area: What Dotted Lines Mean
- Excel Circular Reference Cannot Be Listed? How to Find It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Dashboard: Build a Useful Interactive ReportYes—you can build an interactive dashboard in Microsoft Excel by organizing data in a table, summarizing it with…
- Online Pivot Table Chart: How to Create, Read, and UseAn online pivot table chart is a visual summary built from data organized in a pivot table. In…
- Excel Line Chart Not Plotting Correctly? Fix the DataIf an Excel line chart is missing points, plotting the wrong values, or showing an unexpected axis, first…
- Excel Pie Chart: How to Change the Order of SlicesTo change the order of slices in an Excel pie chart, reorder the category and value rows in…
- Bubble Chart in Google Sheets: Setup and TipsYes—Google Sheets can make bubble charts in a web browser. Arrange each item in a row with five…
- Excel Pie Chart Not Updating? Fix the Data Range NowAn Excel pie chart usually misses new data because its source range still ends at the old last…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.