How to Create a Simple Dashboard in Excel

Creating an Excel dashboard from scratch doesn't have to be a complicated puzzle reserved for spreadsheet wizards. You can build a simple, interactive, and genuinely useful dashboard using just a handful of built-in tools. This article will walk you through transforming a basic data set into a clean, visual report that you can filter and analyze with just a few clicks.

Graphed

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.

Build Your First Agent

What is an Excel Dashboard (and Why Should You Use One)?

An Excel dashboard is a single-page report that uses charts, graphs, and tables to provide a high-level overview of your most important business metrics. Instead of digging through sprawling spreadsheets and endless rows of data, a dashboard brings your key performance indicators (KPIs) to the forefront, making them easy to understand at a glance.

There are three main benefits to using one:

• Clarity: Visuals are much easier to interpret than raw numbers. Dashboards help you quickly spot trends, identify outliers, and understand performance without getting lost in the details.
• Efficiency: Once set up, a dashboard can be refreshed with new data automatically, saving you hours of manual reporting work each week or month.
• Accessibility: They make data accessible to everyone on your team, not just the people who are comfortable working in spreadsheets. A well-designed dashboard tells a story that anyone can understand.

Getting Started: Prepare Your Data First

The success of any dashboard depends entirely on the quality of the data behind it. Before you even think about building charts, you need to ensure your data is clean, organized, and structured properly for analysis. This step will save you countless headaches down the road.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

The Golden Rule of Data Formatting

Your data should be set up in a simple, tabular format. This means your data follows these rules:

• Your data is organized in columns and rows.
• There is one single header row at the top.
• Each column has a unique, descriptive name (e.g., "Transaction Date," "Product Category," "Revenue").
• Each row represents a single, unique record (e.g., one sales transaction, one website visitor).
• There are no blank rows or columns in the middle of your dataset.
• There are no merged cells anywhere in your data.

Here’s an example of a well-formatted sales data set:

Date,Region,Product,Rep,Units Sold,Revenue 1/1/2024,North,Widget A,Smith,10,$500 1/2/2024,South,Widget B,Jones,5,$300 1/3/2024,North,Widget A,Garcia,12,$600 ...

This simple, clean structure is the perfect foundation for a powerful dashboard.

Format Your Data as an Official Excel Table

Once your data is clean, the next step is to convert it into an official Excel Table. This isn’t just about making it look nice with color banding, it turns your static range of cells into a dynamic object with powerful properties.

Why this is so important:

• Dynamic Expansion: When you add new rows of data to the bottom of a Table, it automatically expands. All your charts and dashboard components connected to that Table will update to include the new data when you refresh. No more manually adjusting cell ranges every time you add data!
• Easy Referencing: Tables use structured, easy-to-read names in formulas (e.g., Table1[Revenue]) instead of cryptic cell references (e.g., F2:F1000).

Here’s how to do it:

1. Click any single cell inside your data set.
2. Go to the Insert tab on the Excel ribbon and click Table.
3. Excel will automatically detect your data range. Ensure the "My table has headers" box is checked.
4. Click OK.

Pro tip: You can also use the keyboard shortcut Ctrl + T (or Cmd + T on Mac) to create a Table instantly. Once created, you can give your table a descriptive name in the "Table Design" tab that appears on the ribbon.

Graphed

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.

Build Your First Agent

Building Your Simple Excel Dashboard, Step-by-Step

Now for the fun part. We'll build the dashboard using PivotTables and PivotCharts. This method allows you to create interactive, sliceable visuals without writing a single formula.

Step 1: Set Up Separate Worksheets

Keeping your workbook organized is a best practice. Create three separate sheets and name them accordingly:

• Data: This is where your main Excel Table with all source data lives.
• Analysis: A working sheet where we will build all our PivotTables. We'll keep this separate from the dashboard itself to keep things clean. You can even hide this sheet when you are done.
• Dashboard: This will be the final, presentation-ready dashboard page. It will only contain the charts and slicers.

Step 2: Create PivotTables to Summarize Your Metrics

PivotTables are the engine of any great dashboard. They take your thousands of rows of data and quickly summarize them into meaningful totals that answer specific business questions.

Let's create a few PivotTables on your "Analysis" sheet.

First PivotTable: Revenue Over Time

1. Click anywhere in your Table on the "Data" sheet.
2. Go to the Insert tab and click PivotTable.
3. In the dialog box, ensure your Table is selected. For the location, choose "Existing Worksheet" and select a cell in your "Analysis" sheet (like cell A1). Click OK.
4. This opens the PivotTable Fields list.
5. Drag the "Date" field to the Rows area. Excel will nicely group the dates by month.
6. Drag the "Revenue" field to the Values area. Make sure it's shown as "Sum of Revenue."

Second PivotTable: Revenue by Region

• Instead of starting a new PivotTable from scratch, you can duplicate the existing one.
• Copy the first PivotTable and paste it in a new location on the same "Analysis" sheet.
• In the Field list, now drag the "Region" field to the Rows area, replacing "Date."

You can generate other PivotTables similarly for different metrics, by changing the rows, columns, and values fields accordingly. Now that you have your data summarized, you can begin creating charts.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Step 3: Visualize Your Data with Charts

Interactive charts will bring your dashboard to life. They allow users to quickly answer questions and draw insights. Here’s how to generate a chart:

1. Click inside your first PivotTable on the "Analysis" sheet.
2. Go to the PivotTable Analyze tab on the ribbon and click PivotChart.
3. This opens the Insert Chart dialog. Select your preferred chart type (e.g., a bar chart or column chart).

Create a chart for each of your PivotTables, placing them on the "Dashboard" sheet for easy reference. Format with clear titles or labels, such as "Monthly Revenue" or "Regional Sales". Adjust them within the layout to make the dashboard easy to read.

Step 4: Add Slicers for Interactivity

Slicers serve as interactive filters for your dashboard, allowing users to view specific segments of the data with just a click.

1. On your Dashboard sheet, click on one of your charts and go to PivotTable Analyze and then Insert Slicer.
2. Select the criteria you are interested in (such as "Region" or "Product") and click OK to add a slicer.
3. Arrange your slicers alongside your charts for ease of use.

Final Thoughts

Excel dashboards provide a powerful and flexible way to keep your key metrics in one place, enabling you to make informed decisions. Building dashboards in Excel is a fantastic skill, as the process expands understanding of data visualization while engaging directly with data analytics.

Once you get the hang of it, Graphed can further support your data storytelling by offering a platform for interactive visualizations and data sharing. Excel offers the foundation, but Graphed elevates your work to a whole new level.