Excel Dashboard Practice Exercises for Real-World Skills

Charts & pivot tables · Updated November 19, 2026

Excel dashboard practice exercises should take you from a clean data table to a useful, interactive summary: calculate key measures, build PivotTables and charts, add filters, and check that the results are accurate. A small sales dataset is enough to practise the whole process in Excel for Microsoft 365 on Windows; the exercises below build from basic summaries to a finished dashboard.

Start With a Small, Clear Dataset

Dashboards are only as dependable as their source data. For practice, create a worksheet called Sales Data with these columns: Date, Region, Product, Units, and Sales. Enter eight transactions, using the following values in order:

  • Jan 3, North, A, 10 units, $500
  • Jan 5, South, B, 8 units, $400
  • Jan 9, North, B, 6 units, $360
  • Jan 12, East, A, 12 units, $600
  • Jan 18, South, A, 5 units, $250
  • Feb 2, North, A, 7 units, $350
  • Feb 7, East, B, 9 units, $540
  • Feb 14, South, B, 10 units, $500

Use actual Excel dates, not text that merely looks like a date. Select the data and choose Insert > Table; confirm that the table has headers. On Windows, Ctrl+T also opens the Create Table dialog. A table expands when new rows are added and gives PivotTables and formulas a more dependable source than a fixed range. Give the table a descriptive name, such as SalesData, using Table Design > Table Name.

This deliberately small example has $3,500 in sales across eight transactions. Its compact size makes it easy to verify calculations by hand; later, you can add rows or use a larger practice file without changing the questions the dashboard should answer.

Exercise 1: Clean and Validate the Source

Before building visuals, practise checking the data. Confirm that every row has a date, region, product, unit count, and sales value. Look for blank cells, inconsistent labels such as North and N. in the same region column, and values stored as text. Sort the table by Date and filter each column to inspect its distinct entries. Format Date as a date, Units as a whole number, and Sales as currency or a number with a consistent decimal setting.

Then make a simple reconciliation check. With the sales values in E2:E9, enter =SUM(E2:E9) in a blank cell. The result should be 3500. With those same values in E2:E9, enter =AVERAGE(E2:E9); the result should be 437.5, or $437.50 if formatted as currency. These checks give you a reference for the PivotTable totals you will create next. If your result differs, inspect the source range and look for omitted rows, text-formatted amounts, or accidental duplicates.

Exercise 2: Build a KPI Summary

Create a separate worksheet named Dashboard. Add a small set of headline measures, often called key performance indicators (KPIs), such as total sales, transaction count, and average sale. With the eight sales values in E2:E9, =COUNTA(E2:E9) returns 8 transactions. The total and average formulas from the previous exercise can be linked to the dashboard instead of re-entered, so the dashboard remains connected to your source calculation.

For a goal-attainment KPI, add a small target table with month labels and goals: January $2,000 and February $1,500. The January sales total is $2,110 and February is $1,390, so the combined goal is $3,500 and the example meets 100% of its combined target. With actual sales in E2:E9 and the two goals in I2:I3, enter =IFERROR(SUM(E2:E9)/SUM(I2:I3),0). The result is 1, which you can format as 100%. This illustrates a useful habit: define what a KPI means and what period it covers before displaying it. A percentage without a stated target or date range can mislead.

Keep the KPI labels explicit: “Sales, Jan–Feb” is more informative than “Total,” and “Goal attainment” should identify which goal is used. For a real business dashboard, use approved targets and definitions rather than treating this exercise’s illustrative values as benchmarks.

Exercise 3: Summarize With PivotTables

A PivotTable lets you group and aggregate source records without writing a separate formula for every category. Click a cell in SalesData, then choose Insert > PivotTable and place the PivotTable on a new worksheet. In the PivotTable Fields pane, drag Region to Rows and Sales to Values. Check that the value field is summarized by Sum, not Count; if needed, open its value-field settings and select Sum.

The expected regional totals are North $1,210, South $1,150, and East $1,140. Their sum is $3,500. Create a second PivotTable with Product in Rows and Sales in Values: Product A should total $1,700 and Product B $1,800. Make a third view with Date in Rows and Sales in Values. Excel may group date fields automatically; if it does not, right-click a date in the PivotTable, choose Group, and select Months. January should total $2,110 and February $1,390.

These summaries are valuable exercises because they expose errors as well as patterns. If the regional totals do not add to the KPI, check whether a row is missing from the PivotTable source or whether a filter is active. When source data changes, refresh the PivotTable with Data > Refresh All. Excel tables generally include rows added to the table as the source expands, but existing PivotTables still need refreshing to show new records.

Exercise 4: Choose Charts That Answer Questions

Make one chart for each distinct question rather than repeating the same information in several forms. For a comparison of sales by region, select the regional PivotTable and choose Insert > PivotChart; a clustered column chart is a reasonable starting point. For the January-to-February change, use a line chart or a column chart based on the monthly summary. A line chart is most useful when the horizontal axis represents an ordered time series with enough periods to reveal a trend; two months alone provide only a limited comparison.

Practise improving each chart with a descriptive title, readable category labels, and consistent currency formatting. Remove a legend if there is only one series and it adds no information. Avoid 3-D effects and decorative elements that make values harder to compare. A pie chart can show parts of a whole, but it becomes difficult to read with many categories or similarly sized slices; a bar chart is often clearer for comparing several regions or products.

Try a challenge: make the same regional comparison as a bar chart, then write one sentence that states what the chart supports and one thing it cannot establish. The chart shows the relative sales totals in this sample. It does not explain why one region sold more, nor does it show whether the difference is meaningful over a longer period.

Exercise 5: Add Filters and Interactivity

Add a slicer so a viewer can filter the summary by Product or Region. Select a PivotTable, then choose PivotTable Analyze > Insert Slicer and select a field. A slicer presents clickable buttons and makes the active filter more visible than a hidden dropdown. You can add more than one slicer, but each one should support a useful question rather than add clutter.

To let a slicer control more than one PivotTable, select the slicer and use the Report Connections command (called PivotTable Connections in some Excel interfaces). The PivotTables generally need to share the same source and PivotTable cache for connections to be available. Test the controls: select Product A, confirm that the relevant summaries change, clear the selection, and verify that the full totals return. If a chart does not respond, check its PivotTable connection instead of assuming the slicer is broken.

For a more advanced variation, create a date-based PivotTable and try a timeline if the command is available for the selected PivotTable. A timeline is designed to filter dates by time period. If your dashboard uses ordinary charts rather than PivotCharts, remember that a slicer connected to a PivotTable does not automatically filter unrelated charts or formulas.

Exercise 6: Arrange and Check the Dashboard

Put the KPI cards near the top, followed by the most important chart and then supporting detail. Align objects, use a restrained number of colors, and leave enough space for labels and slicers. Give the dashboard a clear heading and state the data period. Freeze panes or hide irrelevant worksheet gridlines only if doing so makes the dashboard easier to read; do not hide source data or filters in a way that makes the workbook difficult to maintain.

Run a short quality check before sharing:

  • Do KPI totals match the independently checked source total?
  • Does each chart have a clear title, readable labels, and sensible number formatting?
  • Do slicers filter the intended PivotTables, and can the viewer restore the full view?
  • Are dates and currency values consistent, and is the reporting period visible?
  • Does the dashboard still work after adding a test row to the Excel table and refreshing?

Also check usability without relying on color alone. A red-versus-green distinction may be hard for some viewers to interpret, so pair colors with labels or values. If the workbook will be used by others, explain how to refresh it and identify the worksheet containing the underlying records.

How to Choose the Right Practice Exercise

Choose an exercise based on the skill you need, not on how elaborate the final dashboard looks. If you are new to Excel, begin with tables, sorting, filters, and a SUM check. If you already know formulas, practise PivotTables, grouping dates, and verifying that totals reconcile. If you can build summaries confidently, add slicers, layout decisions, and a short written interpretation of the results.

  • For formula practice: calculate totals, averages, counts, and target attainment, then compare them with a PivotTable.
  • For chart practice: choose a chart for a specific comparison or time pattern and explain the limits of what it shows.
  • For dashboard practice: combine a few KPIs, charts, and slicers, then test the workbook with different selections.
  • For portfolio practice: use a clearly described public or fictional dataset, document assumptions, and show the question the dashboard answers.

A common misconception is that a dashboard is simply a page full of charts. A useful dashboard is a compact interface for answering defined questions. It needs trustworthy data, appropriate summaries, legible visuals, and enough context for people to interpret the numbers correctly. More charts do not necessarily make it more useful.

Frequently Asked Questions

What is a good dataset for practising Excel dashboards?
Use a tidy table with one record per row and clear fields such as date, category, location, quantity, and value. A small sales or service dataset works well because it supports totals, category comparisons, and time summaries. Avoid starting with merged cells, subtotals embedded in the data, or multiple unrelated tables on one sheet.

Can I practise dashboard building without real company data?
Yes. A small fictional dataset is sufficient for learning calculations, PivotTables, chart choices, and slicers. Clearly label invented values as examples, and do not present them as actual business results. Public datasets can add realistic scale, but review their definitions, date coverage, and usage terms before relying on them.

Should I use formulas or PivotTables for a dashboard?
Both are useful. Formulas work well for fixed KPIs and calculations you want to control directly; PivotTables are convenient for grouping and comparing categories. A practical exercise is to calculate the same total both ways and reconcile the results. The best choice depends on the question, how the data changes, and who must maintain the workbook.

Why does a PivotChart or slicer not update?
Refresh the PivotTable after source changes using Data > Refresh All, then check the source range and any active filters. If a slicer should control several summaries, confirm that the PivotTables can share the slicer through Report Connections. Ordinary charts and standalone formulas may not respond to a PivotTable slicer unless they are connected to the filtered output.

Can I do these exercises in Excel for Mac or Excel for the web?
Most core tasks, including tables, PivotTables, and charts, are available in current Excel versions, but interface locations and some features can differ. On Mac, use the ribbon or menus when a Windows shortcut does not apply. Excel for the web also has feature limitations compared with the Microsoft 365 desktop app, so use the desktop version if a specific PivotTable or connection command is unavailable.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.