Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Business Dashboard in Excel

Cody Schneider••8 min read

Tired of drowning in spreadsheets and raw data? You can turn that static information into a dynamic, at-a-glance view of your business performance right inside of Excel. This guide will walk you through, step-by-step, how to create a powerful and interactive business dashboard from scratch.

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

First Things First: Planning Your Dashboard

Before you even click "Insert Chart," a little planning will save you a ton of time. A great dashboard isn’t just about showing numbers, it's about answering key questions quickly and clearly. Skipping this step is like trying to build a house without a blueprint - it usually ends in a mess.

Who Is the Dashboard For?

First, ask yourself: who will be using this dashboard and what do they care about? The metrics a sales manager needs are vastly different from what a CEO or a marketing lead wants to see.

  • For a CEO: A high-level overview of business health. Think total revenue, profit margin, customer acquisition cost (CAC), and customer lifetime value (CLV).
  • For a Sales Manager: Team and individual performance. Focus on metrics like deals closed, conversion rates by stage, sales pipeline value, and sales cycle length.
  • For a Marketing Lead: Campaign performance and ROI. You’ll want to track cost per click (CPC), cost per acquisition (CPA), return on ad spend (ROAS), and website lead conversion rates.

Defining the audience and their primary questions tells you exactly what data to include and, just as importantly, what to leave out.

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.

Gather and Prepare Your Raw Data

Your dashboard is only as good as the data feeding it. For Excel to work its magic, your data needs to be clean, organized, and properly formatted as a table. This is the single most important step.

Think of your data source as a simple database. Here are the ground rules:

  • One Header Row: Your table should have a single, unique header for each column (e.g., "Date," "Product SKU," "Sales Rep," "Sale Amount").
  • No Blank Rows or Columns: Keep your data contiguous. Your table should be a solid block of information without empty rows or columns breaking it up.
  • Consistent Data Types: Make sure a column meant for dates contains only dates, a column for currency contains only numbers, and so on.
  • Format as a Table: Once your data is clean, click anywhere inside of it and press Ctrl + T (or go to Home > Format as Table). This turns your data range into a named object that automatically expands as you add new rows, which is essential for easily refreshing your dashboard later.

Here’s an example of a good, clean sales data table:

Date | Sales Rep | Region | Product | Units Sold | Sale Amount

2023-01-05 | Anna | North | Gadget Pro | 10 | $500.00 2023-01-05 | Brian | South | Widget Max | 5 | $375.00 2023-01-06 | Anna | North | Widget Max | 8 | $600.00 ...and so on...

The Engine: Summarizing Data with PivotTables

PivotTables are the horsepower behind any great Excel dashboard. They are incredible tools that let you summarize, group, count, and average thousands of rows of data in just a few clicks - without writing a single formula. We’ll use PivotTables to do all the heavy lifting for our charts.

For best practice, keep your raw data, your PivotTables, and your final dashboard on separate worksheets. Let’s create a new sheet called "PivotTables."

Creating Your First PivotTable

Let's say we want to summarize total sales by region.

  1. Click anywhere inside your cleaned-up and formatted data table.
  2. Go to the Insert tab on the ribbon and click PivotTable.
  3. Excel will automatically select your table. In the dialog box, choose "Existing Worksheet" and use the selector to pick a cell on your "PivotTables" sheet. Click OK.
  4. You’ll now see the empty PivotTable and the PivotTable Fields pane on the right. This is your control panel.
  5. To show sales by region, simply drag the "Region" field into the Rows area and drag the "Sale Amount" field into the Values area.

Just like that, Excel instantly summarizes your data. You can now repeat this process to create separate PivotTables for every single metric and chart you planned to include on your dashboard. For example:

  • Sales Over Time: Drag "Date" to Rows and "Sale Amount" to Values.
  • Sales by Sales Rep: Drag "Sales Rep" to Rows and "Sale Amount" to Values.
  • Units Sold by Product: Drag "Product" to Rows and "Units Sold" to Values.
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

Visualization: Bringing Your Data to Life with Charts

Now that your data is summarized, it’s time to visualize it. Charts make it easy to spot trends, compare values, and understand performance at a glance. We will create all of our charts directly from our PivotTables.

  1. Create a new worksheet and name it Dashboard. This clean sheet will be our final canvas.
  2. Go back to your transactional raw data tab Data. Using tables, create a PivotTable as explained above. From here (this is crucial!), create PivotCharts from your PivotTables, not from your raw data.
  3. Click on the PivotTable you want to visualize (e.g., the Sales by Region table).
  4. Go to the PivotTable Analyze tab and click PivotChart.
  5. Choose the chart type that best represents your data. For comparisons like "Sales by Region," a column or bar chart works perfectly. For a time-series like "Sales Over Time," a line chart is best.

Once you’ve created a PivotChart, cut it (Ctrl + X) and paste it (Ctrl + V) onto your Dashboard sheet. Repeat this for all your metrics until your dashboard has all its key visual components.

Dashboard Design Best Practices

A functional dashboard should be clean and easy to read. Clutter is the enemy.

  • Remove Chart Noise: Get rid of elements that don’t add value. Right-click on your chart's gray field buttons (like "Sum of Sale Amount") and select "Hide All Field Buttons on Chart." You can also remove gridlines and unnecessary axes if the data labels on your chart are clear enough.
  • Logical Layout: Place your most important, high-level KPIs in the top-left corner, as that’s where the eye naturally looks first. Group related charts together.
  • Use Clear Titles: Rename your charts with simple, descriptive titles like "Total Sales by Region" instead of the default titles.
  • Consistent Colors: Use your brand colors or a consistent color palette to give your dashboard a professional and cohesive look.

Making It Interactive with Slicers and Timelines

This is where your dashboard goes from a static report to a dynamic, interactive tool. Slicers are user-friendly filter buttons that make it incredibly easy for anyone to drill down into the data.

Adding Slicers to Filter Your Data

Instead of manually changing filters on each PivotTable, a single slicer can control multiple charts at once.

  1. Click on any one of your PivotCharts 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 boxes for the fields you want to filter by - "Region" and "Sales Rept" are great candidates. Click OK.
  4. The slicers will appear on your sheet. You can move and resize them to fit your layout. Now, when you click a button on the slicer (e.g., the "North" region), the chart it's connected to will automatically update.

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 One Slicer to All Charts

By default, a slicer only controls the chart it was created from. Let's fix that.

  1. Right-click on a slicer and select Report Connections.
  2. In the pop-up menu, you’ll see a list of all the PivotTables in your workbook. Check the boxes for every PivotTable that you want this slicer to control.
  3. Repeat this for each slicer.

Now, when you click "North" on your Region slicer, all of your charts - Sales over Time, Sales by Product, etc. - will instantly filter to show data only for the North region. For date filtering, you can insert a Timeline (found right next to the Slicer button), which provides an easy-to-use slider for filtering by year, quarter, month, or day.

Final Thoughts

By thoughtfully planning your metrics, organizing your raw data, and using powerhouse tools like PivotTables, PivotCharts, and Slicers, you can transform a flat spreadsheet into an interactive dashboard. This tool not only gives you a clear snapshot of your business but also empowers you to explore your data and find answers to deeper questions on the fly.

Building a great Excel dashboard is a powerful skill, but often the most frustrating part is the manual work: constantly downloading CSVs, cleaning the data, and ensuring everything is up to date. We built Graphed to automate that entire process. You simply connect your data sources like Google Analytics, Shopify, or Salesforce, and describe the dashboard you need in plain English. Your dashboard is ready in seconds, pulls live data automatically, and lets you focus on the insights - not the manual setup.

Related Articles