How to Create Data in Excel

Building a powerful, interactive dashboard doesn't require expensive, complicated software. You can create a surprisingly dynamic and insightful one using a tool you already have: Microsoft Excel. This guide will walk you through the process step-by-step, from organizing your raw data to creating interactive charts and filters.

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 Bother)?

An Excel dashboard is a one-page summary that gives you a high-level view of your most important metrics and key performance indicators (KPIs). Instead of digging through messy spreadsheets or static reports, a dashboard uses charts, graphs, and tables to visualize performance, making it easy to spot trends and make informed decisions quickly. Think of it as the cockpit for your business, project, or department.

Why build one? Because they:

• Save Time: Once set up, they update automatically when you add new data. No more remaking the same weekly report from scratch.
• Improve Clarity: Visuals are easier to understand than rows and rows of numbers. A dashboard tells a story at a glance.
• Enable Better Decisions: By having all your critical KPIs in one place, you can see the big picture, identify what's working, and pinpoint problems before they get out of hand.

Step 1: Get Your Data Ready for Analysis

The success of your dashboard lives and dies with the quality of your data. Before you build a single chart, you need to make sure your source data is clean, organized, and properly formatted. This is the most important step, and spending a little extra time here will save you massive headaches later.

Organize Your Raw Data

Your raw data should be in a simple tabular format. Each column should represent a distinct field (like Date, Region, Sales Rep, Revenue), and each row should represent a single record (like one sale).

A few crucial rules for your data set:

• No Blank Rows or Columns: Make sure there are no entirely empty rows or columns cutting through your dataset.
• Consistent Column Headers: Each column needs a unique, descriptive header in the very first row.
• Proper Formatting: Ensure dates are formatted as dates, numbers are formatted as numbers (not text), and text fields are consistent (e.g., "New York" is always spelled the same way, not sometimes "NY").

Pro tip: Keep your raw data on one worksheet and build your dashboard on another. This separation keeps things organized and prevents anyone from accidentally deleting or changing your source information.

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

Turn Your Data into an Excel Table

This is a simple but powerful trick. Converting your data range into an official Excel Table makes your dashboard dynamic. When you add new rows of data to your table, your dashboard will automatically include it upon refreshing.

To do this:

1. Click anywhere inside your data set.
2. Go to the Insert tab on the Excel ribbon and click Table, or just press the shortcut Ctrl + T.
3. Excel will automatically detect your data range. Make sure the box for "My table has headers" is checked, and click OK.

Your data range will now be formatted with colored bands, and you'll see a new "Table Design" tab appear when you click inside it. You can rename your table here (e.g., from "Table1" to "SalesData") to make it easier to reference.

Step 2: Define Your Key Metrics (KPIs)

Your dashboard shouldn't show everything, it should show what is most important. Before you start building, ask yourself: What key questions does this dashboard need to answer at a glance? Who is the audience, and what do they care about?

For example:

• For a Sales Dashboard: You might want to track Total Revenue, Revenue by Region, Deals in the Pipeline by Stage, and Sales Rep Leaderboard.
• For a Marketing Dashboard: You might focus on Website Sessions, Conversion Rate, Cost Per Lead, and Top Performing Channels.

Write these KPIs down. This list will be your roadmap for what to build next.

Step 3: Build Your Analysis with PivotTables

PivotTables are the engine of your interactive dashboard. They do all the heavy lifting of summarizing, aggregating, and calculating your data so you don't have to fiddle with complex formulas. We will use PivotTables to perform all the calculations needed for our charts and KPIs.

It's best practice to create a new worksheet specifically for all your PivotTables. Let's call it "Pivot Sheet." This keeps your dashboard sheet clean and focused purely on visualization.

Creating Your First PivotTable

Let's say one of our KPIs is to see total sales by region. Here's how to create a PivotTable for that:

1. Click on any cell within your data table that you created in Step 1.
2. Go to the Insert tab and click PivotTable.
3. In the popup window, Excel should have already selected your table ("SalesData" or whatever you named it). Under "Choose where you want the PivotTable to be placed," select Existing Worksheet and then click a cell in your newly created "Pivot Sheet." Click OK.
4. The PivotTable Fields pane will appear on the right side of your screen. This is where you build the summary. To see sales by region:

Instantly, you have a summary table showing the total revenue for each region. You'll create a separate PivotTable summary for each chart or KPI you plan to display on your dashboard.

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

Step 4: Create Your Dashboard Visualizations

Now it's time to turn those PivotTable summaries into digestible visuals. This is where your dashboard starts to come to life.

Head over to your main dashboard sheet. This is where we'll assemble all the charts and KPI cards.

Creating Charts from PivotTables (PivotCharts)

Let's turn our "Sales by Region" PivotTable into a bar chart.

1. Click anywhere inside the "Sales by Region" PivotTable you just created on your "Pivot Sheet."
2. Go to the PivotTable Analyze tab on the ribbon and click PivotChart.
3. Choose a chart-type that best represents your data. For comparing categories like regions, a Column or Bar chart works well. Select one and click OK.

A chart will appear. You can clean it up by right-clicking on the grey field buttons (like "Sum of Revenue") and selecting "Hide all field buttons on chart." Cut and paste this chart (Ctrl + X, Ctrl + V) onto your main dashboard sheet.

Repeat this process for your other KPIs. For time-based data like a monthly sales trend, a Line chart is typically the best choice. For showing parts of a whole, like leads by marketing channel, a Pie or Donut chart can work (but use them sparingly, as they are hard to read with more than a few categories).

Creating KPI "Cards"

For single-value KPIs, like total revenue, you can't use a chart. Instead, we can create what's often called a "card" or big-number display.

1. First, make a simple PivotTable that shows only the grand total for revenue.
2. Go to your dashboard sheet. On the Insert tab, click Text Box.
3. Draw a text box where you want your KPI card to be.
4. Do not type in the text box. Instead, click on the text box border, then click into the Formula Bar at the top of Excel.
5. Type an equals sign (=) in the Formula Bar.
6. Switch to your "Pivot Sheet" and click on the cell containing the grand total revenue you want to display. Press Enter.

The text box is now dynamically linked to that PivotTable cell! You can format the text inside (make it larger, change the color) and add another smaller text box underneath to label it "Total Revenue."

Step 5: Design and Organize Your Dashboard Layout

With all your charts and KPI cards on the dashboard sheet, it's time to arrange them in a logical and visually appealing way.

• Create a Grid: A little design polish goes a long way. Go to the View tab and uncheck "Gridlines" to create a clean white canvas.
• Visual Hierarchy: Place your most important, high-level KPIs (like your big number cards) at the top or top-left, as that’s where people naturally look first.
• Consistent Branding: Use a consistent color palette and font style across all your charts. You can set up a custom color scheme under the Page Layout > Colors menu.
• Clear Titles: Give every chart a clear, descriptive title. "Monthly Revenue Trend" is much better than "Chart1."
• Alignment: Use Excel's alignment tools (under the Shape Format tab when selecting multiple objects) to perfectly align your charts and cards for a professional look.

Step 6: Make it Interactive with Slicers and Timelines

This is where the magic happens. Slicers and Timelines are user-friendly filters that allow anyone viewing the dashboard to drill down into the data without needing to know anything about PivotTables.

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

Adding Slicers

Let's add a slicer that allows us to filter the entire dashboard by region.

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

A slicer menu for Region will appear. Right now, it only controls the PivotTable you had selected. To make it control all the visuals, we need to connect it:

1. Right-click the slicer and choose Report Connections...
2. In the popup window, check the boxes for all of the PivotTables you've created on your "Pivot Sheet." Click OK.

Now, when you click a region on the slicer, your entire dashboard will filter to show data just for that region!

Adding Timelines for Dates

A timeline is a special type of slicer just for dates. The steps are similar: click a PivotTable, go to PivotTable Analyze > Insert Timeline, and select your date field. Then, connect it to all your other PivotTables using "Report Connections." This creates an awesome draggable filter for switching between months, quarters, or years.

Refreshing Your Dashboard

Remember, your dashboard is powered by your source data. When you add new data to your Excel Table, go to the Data tab and click Refresh All. Every PivotTable, chart, and KPI card on your dashboard will update instantly.

Final Thoughts

Excel is a fantastic tool for creating powerful, custom dashboards that bring your data to life. By structuring your data correctly, leveraging PivotTables as your analytics engine, and adding interactive elements like slicers, you can build a professional-grade reporting tool that serves you and your team week after week.

As you build more complex dashboards that pull from various sources like Google Analytics, your CRM, or ad platforms, the manual process of downloading CSVs and refreshing Excel can become time-consuming. This is where automation helps. At Graphed you can connect directly to all your data sources in one-click, and you can build the exact dashboard you need simply by describing it in plain English. For those situations where you need answers quickly without the manual setup in Excel, we can get you there in seconds.