How to build an Excel dashboard from scratch

1. See what we are going to build

The click-by-click instructions use Excel for Microsoft 365 on Windows desktop. On Mac, some ribbon names differ; the Microsoft guides at the end have platform tabs. The illustrations below are simplified examples, so you can see the important cells and controls clearly.

The report will answer three questions: How much revenue is there? How much comes from each region? How much comes from each month?

Before filtering, the answers are 247 total, East 127 / West 120, and January 107 / February 140. We will put these answers on one sheet.

These rows contain no costs, targets, or customer names. They cannot tell us profit, whether sales met a target, or how many different customers bought something. For a real project, use the requirements checklist to choose questions the data can answer.

Illustrated example

The answers from those rows

2. Make three named sheets

I frequently see people get stuck because it is not clear where data entry happens, where the calculations happen, or which parts people are meant to read. I typically suggest a very simple approach: give each job its own sheet.

1. Open a blank workbook. At the bottom, double-click the Sheet1 tab. Type Data and press Enter.
2. Click the + beside the sheet tabs to add a sheet. Double-click its tab, type Analysis, and press Enter.
3. Add one more sheet. Name it Dashboard.

Put the sales rows on Data. Put the tables that add up those rows on Analysis. Put the large total and charts on Dashboard.

A cell address tells you where to click. B2 means column B, row 2. Data!A1 means cell A1 on the Data sheet. A range such as A1:F9 means the rectangle from A1 through F9.

Illustrated example

Three sheets, three jobs

Illustrated example

B2 means column B, row 2

3. Paste the eight sales rows

Copy the table above. Click the Data tab, click A1, and paste. The six headers should fill A1:F1; the eight sales rows should fill A2:F9. On a phone, swipe the table sideways to see all six columns.

One row shows one product bought. Order A1001 bought two notebooks on one row and three pens on another. It is one order with two rows. There are eight rows but only six different order numbers.

Revenue already includes the quantity on that row. The two notebooks together cost 40. Do not multiply 40 by 2 again.

Check that the dates are real Excel dates. In an empty cell, type =ISNUMBER(B2). It should show TRUE. If it shows FALSE, re-enter the date with =DATE(2026,1,2). That means January 2, 2026. Use the matching year, month and day for the other seven dates. Delete the temporary check cell afterward.

You should see: eight sales rows. Adding F2:F9 gives 247.

Illustrated example

One order, two products

Illustrated example

The rows behind the total

4. Turn the cells into an Excel Table

An Excel Table is a named block of cells. It can grow as you add rows. This makes it easier to update the report.

1. Click A1 on Data. Hold Shift and click F9 to select all six columns and nine rows.
2. Click Insert → Table. Check that the range is $A$1:$F$9 and My table has headers is ticked. Click OK.
3. Click any cell in the new table. Open Table Design. In the Table Name box at the left, replace the current name with Sales and press Enter.

Data is the sheet name. Sales is the table name. They are two different things. Later formulas use the table name.

You should see: small filter arrows beside the headers, and Sales in the Table Name box.

Illustrated example

Create the Sales table

5. Add a Month column

We want January sales together and February sales together. Add a date for the first day of each sale’s month.

1. Click G1, immediately beside Revenue. Type Month and press Enter. The table should expand to column G. If it does not, click inside Sales, choose Table Design → Resize Table, and include A1:G9.
2. Click G2. Paste this formula and press Enter:

=DATE(YEAR([@OrderDate]),MONTH([@OrderDate]),1)

[@OrderDate] means the OrderDate value on this row. The final 1 makes the result the first day of the month. January 2 and January 17 both become January 1.

3. Select G2:G9. Press Ctrl+1, choose Custom, type mmm yyyy in Type, and click OK.

You should see: Jan 2026 in G2:G5 and Feb 2026 in G6:G9. The values are still real dates, even though the day is hidden.

If it looks different: if G3:G9 stayed empty, select G2:G9 and press Ctrl+D to copy the formula down. If you see #VALUE!, check the OrderDate cells for text instead of dates.

Illustrated example

Group dates by month

6. Add up revenue by region

A PivotTable is an Excel summary table. It groups rows, then adds up a number for each group. Here it will add all East revenue and all West revenue.

1. Click any cell in Sales. Choose Insert → PivotTable → From Table/Range if a submenu appears.
2. Check that Table/Range says Sales. Choose Existing Worksheet. In Location, type Analysis!$A$3. Leave Add this data to the Data Model unticked for this exercise. Click OK.
3. In the PivotTable Fields panel, drag Region into Rows. Drag Revenue into Values.

You should see: East 127, West 120, and Grand Total 247.

If the panel is missing, click inside the PivotTable, then click PivotTable Analyze → Field List. If Values says Count of Revenue, use its drop-down, choose Value Field Settings → Sum → OK. Count counts rows; Sum adds the revenue amounts. If Sum is still wrong, fix any Revenue cells stored as text, then choose Data → Refresh All.

With the PivotTable selected, click the PivotTable Name box on PivotTable Analyze. Type RevenueByRegion and press Enter. This name will help us find it in a list later. Microsoft’s PivotTable guide shows these controls for each platform.

Illustrated example

Place the first PivotTable

Illustrated example

Put fields in these two boxes

7. Make a monthly table and a total

Copy the first PivotTable twice. We will use one copy for the monthly chart and one for the large total.

1. Click the region PivotTable. Choose PivotTable Analyze → Select → Entire PivotTable. Press Ctrl+C, click E3 on Analysis, then press Ctrl+V.
2. Click the copy at E3. In PivotTable Fields, drag Region out of Rows. Drag Month into Rows. Leave Sum of Revenue in Values.
3. If Excel adds Years or Quarters, right-click a date in this copied PivotTable and choose Ungroup. Keep just Month in Rows. If Years or Quarters remain, drag them out of Rows. You should have one row for Jan 2026 and one for Feb 2026. To show readable labels, right-click a month, choose Field Settings → Number Format → Custom, and use mmm yyyy.
4. Name this PivotTable RevenueByMonth, using the same name box as before.
5. Copy the original region PivotTable again. Paste it at J3. Drag Region out of Rows. Leave only Sum of Revenue in Values. Name it RevenueTotal.

You should see: the monthly table shows Jan 2026 = 107 and Feb 2026 = 140. The table at J3 shows one total: 247.

To check a copy’s source, click it and open PivotTable Analyze → Change Data Source. It should say Sales. Click Cancel after checking. The filter test in step 10 confirms that all three can respond together.

Leave the empty columns between these tables. A real report may need more room as you add months or regions.

Illustrated example

Three PivotTables on Analysis

8. Put two charts on Dashboard

1. On Analysis, click the region PivotTable at A3. Choose PivotTable Analyze → PivotChart. Choose Bar → Clustered Bar → OK.
2. Click the chart’s outer border so the whole chart is selected. Press Ctrl+X. Click the Dashboard tab, click B5, and press Ctrl+V.
3. Double-click the chart title and type Revenue by region.
4. Go back to Analysis. Click the monthly PivotTable at E3. Insert another PivotChart, this time Column → Clustered Column.
5. Cut and paste that chart onto Dashboard. Drag it below the first chart, and title it Monthly revenue.

You should see: two bars labeled East and West, and two columns labeled Jan 2026 and Feb 2026.

On Mac, select the PivotTable and insert a supported chart from the Insert tab. For the Windows steps above, click inside the PivotTable first if PivotChart is missing.

Illustrated example

Revenue by region

Illustrated example

Monthly revenue

9. Show the total in a large cell

The large total must follow the Region filter we will add next. Read the number from RevenueTotal, so the total and charts use the same filter.

1. On Dashboard, click A1 and type Sales revenue · Jan–Feb 2026.
2. Click B1 and type Total revenue.
3. Click B2, paste this formula, and press Enter:

=GETPIVOTDATA("Revenue",Analysis!$J$3)

4. Keep B2 selected. On Home, set the font size to 28 and click Bold. Widen column B by dragging the right edge of the B column heading if you see ####.

You should see: 247 in B2.

If it looks different: if you see #REF!, check that J3 is inside RevenueTotal and that its value field is Revenue. You can also type = in B2, click the 247 cell in RevenueTotal, and press Enter. Excel can write GETPIVOTDATA for you when Generate GetPivotData is enabled.

SUM(Sales[Revenue]) adds all the source rows. It would stay at 247 when the PivotTable slicer selects East. That is why we use GETPIVOTDATA here. See Microsoft’s formula guide.

Once this cell works, you can optionally show the same number in a movable text box. The cell alone is enough for this lesson.

Illustrated example

B2 means column B, row 2

10. Add East and West filter buttons

A slicer is a set of buttons that filters a table. Our Region slicer needs to control all three PivotTables.

1. On Analysis, click RevenueByRegion. Choose PivotTable Analyze → Insert Slicer. Tick Region and click OK.
2. Click the slicer’s outer edge until you see resize handles around the whole object. Press Ctrl+X, click Dashboard, and press Ctrl+V. Drag it into an empty space near the top.
3. Keep its border selected. Choose Slicer → Report Connections. In some versions this is called PivotTable Connections.
4. Tick RevenueByRegion, RevenueByMonth, and RevenueTotal. Click OK.
5. Click the East button.

You should see: B2 changes to 127. The region chart shows East. The monthly chart shows January 87 and February 40. On Analysis, RevenueByRegion should show East 127, RevenueByMonth should show 87 and 40, and RevenueTotal should show 127.

Click the Clear Filter icon at the top right of the slicer. It looks like a funnel with a small X. B2 should return to 247. If one part stays unchanged, open Report Connections and check its box.

If a PivotTable is missing from the list, it may have been built from different cells or a different table. In this exercise, make the missing table by copying RevenueByRegion again, as in step 7. Update its chart or formula to use that new copy. Microsoft’s slicer guide explains the same-source requirement.

For buttons that show a date range, Excel also has a Timeline. Finish the Region test first.

Illustrated example

Tick all three PivotTables

Illustrated example

Click East. Check every result.

Illustrated example

Clear the Region filter

11. Make the report easy to read

Keep the title and total at the top. Put the Region slicer next to them. Place the two charts below. Drag the outer border of each object to move it; drag a corner handle to resize it.

Keep the chart titles visible. Make the region and month labels large enough to read. Leave space between the slicer and charts so the buttons are easy to click. You can turn off worksheet gridlines with View → Gridlines on Dashboard.

The layout guide gives more examples. Use the color guide and large-number card guide after the numbers work. If you need this on a slide, check the size with the PowerPoint instructions before finishing the design.

Illustrated example

The finished practice report

12. Add a sale and check the update

On Data, add this row directly below the table. Put these six values in A10:F10:

1. Check that the table includes row 10 and the Month formula appears in G10. If row 10 is outside the table, click inside Sales and use Table Design → Resize Table to include A1:G10.
2. On Dashboard, click the slicer’s Clear Filter icon.
3. Click Data → Refresh All. This tells the PivotTables to read the new rows.
4. Check the numbers below. Then click East and check that the total becomes 147.

If the total is still 247, check the table boundary first, then refresh again. If it is 147, clear the East filter. Use this explicit refresh step for the exercise; automatic refresh varies by version and settings.

Before giving the file to someone else, add a short note: “Paste new sales below the table on Data. Check that Month fills down. Choose Data → Refresh All. Use the Region buttons on Dashboard.”

Illustrated example

Add 20. Refresh. Check.

Common questions

Do I need VBA?

No. You used Excel’s built-in tables, charts, formulas, and slicers. No macro is needed.

Can I use my own data?

Yes. First check what each row means and what numbers you want. The template guide shows how to compare your columns with an existing workbook.

How do I change the slicer’s appearance?

Follow the slicer styling steps to change the button layout and reuse a style from another workbook.