Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Tracking Dashboard in Excel

Cody Schneider••8 min read

Tired of sifting through endless rows of data just to understand what’s happening in your business? An Excel tracking dashboard can transform a busy spreadsheet into a clear, visual command center for your projects, sales, or marketing campaigns. This guide breaks down exactly how to build a dynamic, professional-looking dashboard from scratch, even if you’re not an Excel power user.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Start with Clean, Structured Data

Before you build anything, you need a solid foundation. The single most important step in creating a great dashboard is organizing your source data properly. A messy, inconsistent dataset will bring your dashboard project to a halt.

Your goal is to format your data in a simple, tabular format where:

  • Each row represents a single record (e.g., one sale, one website visit, one project task).
  • Each column represents a specific attribute of that record (e.g., date, product name, region, status).
  • The first row is a clear, unique header for each column.
  • There are no merged cells or blank rows/columns within your data range.

Let’s use a simple sales tracking dataset as an example. An ideal structure would look like this:

+------------+-----------+--------------+--------------+-------------+
|    Date    |  Region   |   Product    | Units Sold   | Revenue     |
+------------+-----------+--------------+--------------+-------------+
| 01/05/2024 | North     | Product A    |      10      | $500.00     |
| 01/06/2024 | West      | Product B    |      5       | $750.00     |
| 01/07/2024 | North     | Product C    |      12      | $360.00     |
| 01/08/2024 | South     | Product A    |      8       | $400.00     |
   ...and so on...
+------------+-----------+--------------+--------------+-------------+

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

The Magic of Formatting as a Table

Once your data is clean, select any cell within it and press Ctrl + T (or go to Insert > Table on the ribbon). This one simple step converts your range into an official Excel Table, which has several huge advantages:

  • It's dynamic. When you add new rows of data to the bottom, the Table automatically expands to include them. This means your dashboard can be refreshed with one click, without you ever having to manually update cell ranges.
  • Readability. Tables have built-in formatting options and filtered dropdowns that make your source data easier to read and manage.
  • Clarity. Give your Table a meaningful name. Click anywhere in your Table, go to the "Table Design" tab that appears, and type a new name in the "Table Name" box (e.g., "SalesData"). This makes creating charts and summaries much easier later.

Summarize Your Data with PivotTables

PivotTables are the engine of your Excel dashboard. They take your thousands of rows of raw data and effortlessly summarize them without you needing to write a single complex formula. Think of them as quick and powerful report generators.

Let's create a few PivotTables from our "SalesData" Table to power our future charts.

  1. Click anywhere inside your data Table.
  2. Go to the "Insert" tab and click "PivotTable."
  3. Excel will automatically select your entire Table. Just ensure "New Worksheet" is selected and click OK. This keeps your summaries separate from your raw data.

A new sheet will appear with a PivotTable placeholder. On the right, you'll see the "PivotTable Fields" pane. This is where you build your summary. Let's create our first report: Revenue by Region.

Drag and drop the fields like this:

  • Drag the "Region" field into the Rows area.
  • Drag the "Revenue" field into the Values area.

Instantly, Excel calculates the total revenue for each region. It's that simple! Rename this worksheet to something clear, like "Pivot_RevenueByRegion".

Repeat this process on new worksheets to create a few more summaries that you want to visualize. For example:

  • Units Sold by Product: "Product" in Rows, "Units Sold" in Values.
  • Monthly Revenue Trend: "Date" in Rows, "Revenue" in Values. (Excel will automatically group the dates by months, which is incredibly helpful).

Pro Tip: Give each PivotTable a unique, descriptive name. Click on a PivotTable, go to the "PivotTable Analyze" tab, click "PivotTable Name" on the left, and change it from "PivotTable1" to something like "pt_RevenueByRegion". This will save you a headache later when connecting filters.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Bring Your Data to Life with Charts

With your summaries ready, it's time for the fun part: visualizing the data. We'll use PivotCharts, which are charts linked directly to your PivotTables. When the PivotTable data updates, the chart updates automatically.

Go to your first PivotTable worksheet ("Pivot_RevenueByRegion"):

  1. Click anywhere inside the PivotTable.
  2. Go to the "PivotTable Analyze" tab.
  3. Click on "PivotChart."
  4. Choose a chart that best represents the data. For comparing revenue across different regions, a Clustered Bar or Column chart works perfectly. Click OK.

Excel will create a chart. It might look a little cluttered at first, but we can clean it up:

  • Remove Field Buttons: Right-click on one of the gray field buttons (like "Sum of Revenue") on the chart and select "Hide All Field Buttons on Chart." This instantly gives it a cleaner look.
  • Add a Title: Click on "Chart Title" and give it a clear, descriptive name like "Total Revenue by Region."
  • Refine the Design: You can right-click any chart element - like the bars, axis labels, or background - to format it, change colors, or remove elements you don’t need (like the legend, in this case).

Repeat this process for your other PivotTables. Choose the right chart for the story you want to tell:

  • For Monthly Revenue Trend, a Line Chart is perfect for showing changes over time.
  • For Units Sold by Product, a Pie Chart or Donut Chart can quickly show the contribution of each product to the total.

Assemble Your Dashboard

Now it’s time to bring all your visuals together into a single view.

  1. Create a new, blank worksheet and name it "Dashboard".
  2. For a clean canvas, go to the "View" tab and uncheck the box for "Gridlines."
  3. Go to one of your PivotChart sheets. Click on the chart you want to move, press Ctrl + X to cut it.
  4. Go back to your "Dashboard" sheet and press Ctrl + V to paste it.
  5. Repeat this for all your charts, moving them to the dashboard sheet.
  6. Arrange the charts in a logical layout. You can resize and align them to create a balanced, professional-looking report. Place your most important chart or metric at the top left, as that’s where people naturally look first.

Make Your Dashboard Interactive with Slicers

This is the step that elevates your report from a static image to a dynamic tool. Slicers are user-friendly filter buttons that allow you (or anyone you share the dashboard with) to filter the data and see the charts update in real-time.

Let's add a slicer to filter our entire dashboard by Region.

  1. Click on any one of your charts on the dashboard.
  2. Go to the "PivotChart Analyze" tab and click "Insert Slicer."
  3. A dialog box will appear with all your data fields. Check the box next to "Region" and click OK.

A slicer "menu" for Region will appear. If you click on "North," the chart you selected will update to show only data for the North region. But what about the other charts? This is the crucial final step: connecting one slicer to all your charts.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Connect Your Slicer to All Charts

  1. Right-click on your new slicer and select "Report Connections."
  2. You'll see a list of all the PivotTables in your workbook (this is why we named them!).
  3. Check the box next to every PivotTable that you want this slicer to control.
  4. Click OK.

Now, when you click a region on the slicer, every chart on your dashboard will instantly filter to show data just for that region. You can add more slicers for other fields, like "Product" or even a "Timeline" (a special slicer for dates), to create a powerful, fully interactive analysis tool.

Keeping Your Dashboard Data Fresh

The beauty of this setup is how easy it is to update. When you get new data - say, a new week of sales - you don't have to rebuild anything.

  1. Go to your source data sheet (the one with the blue Excel Table).
  2. Paste your new rows of data directly below the last row of the table. The Table will automatically expand to include it.
  3. Now, go to your dashboard, click the "Data" tab on the main Excel ribbon, and click "Refresh All."

That's it. Every PivotTable re-calculates, and every chart updates with the latest information. Your dashboard is now a living, repeatable report that saves you hours of manual work.

Final Thoughts

Building a tracking dashboard in Excel boils down to a few core steps: clean your data and format it as a Table, use PivotTables to create summaries, use PivotCharts to visualize those summaries, and connect them all with Slicers for interactivity. It transforms a static spreadsheet into an insightful tool for better decision-making.

While Excel is a powerful tool, you can see it still involves many manual steps, from setting up PivotTables to connecting each filter. We’ve spent countless hours in that exact workflow, which is exactly why we built Graphed. We wanted to eliminate the process of manually pulling data, creating summaries, and building charts. Instead, you can simply connect your data sources (like Google Analytics or Shopify) and ask in plain English for the dashboard you need – like "Show me my revenue by product for the last 90 days" – and get a live, interactive dashboard built for you in seconds.

Related Articles