How to Create a Dashboard in Excel with AI

Building a dashboard in Excel used to mean hours spent wrangling formulas, creating pivot tables from scratch, and manually designing charts. But with AI now built directly into Excel, you can go from raw data to a dynamic, interactive dashboard in a fraction of the time. This guide will walk you through exactly how to leverage Excel's AI features to create insightful dashboards, even if you’re not a spreadsheet expert.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First Things First: Prep Your Data for AI

Before you let any AI loose on your spreadsheet, you need to make sure your data is clean and structured. An AI tool is only as good as the data it analyzes, and a messy dataset will lead to confusing or incorrect results. Think of this as setting the table before you serve a meal - it's a crucial step for a good outcome.

Here’s a simple checklist to get your data into shape:

• Be Consistent: Ensure your naming conventions are uniform. "USA," "U.S.A.," and "United States" may mean the same thing to you, but they are three distinct categories to an AI. Pick one format and stick with it for all your data points like countries, product names, or sales regions.
• No Empty Columns or Rows: Your data should be in a single, contiguous block. Don't leave entirely blank rows or columns in the middle of your dataset, as Excel might think that’s the end of your data range.
• Proper Headers: Give each column a unique, descriptive header. AI uses these headers to understand the context of the data underneath. A header like "Sales Q1" is much more useful than "Column_1."
• Fill Blank Cells: Spotty data can be problematic. If a cell is blank because its value is zero, enter a "0". If the data is missing, decide on a consistent way to represent that, like "N/A." Having gaps can throw off calculations and charts.

The Most Important Step: Format as a Table

Once your data is clean, you need to format it as an official Excel Table. This is non-negotiable for working with AI features. It transforms your static range of cells into a structured object that AI can easily understand and work with.

Here's how to do it:

1. Click anywhere inside your data range.
2. Go to the Insert tab on the Ribbon.
3. Click Table. (Or use the keyboard shortcut Ctrl + T on Windows / Cmd + T on Mac).
4. A small dialog box will pop up, confirming the range of your data. Make sure the "My table has headers" box is checked.
5. Click OK.

Your data range will now be formatted with alternating colors and filter buttons on the headers. More importantly, Excel now recognizes this as a structured table, which unlocks its most powerful AI capabilities.

Method 1: Using Excel’s “Analyze Data” Feature

The "Analyze Data" feature (formerly known as "Ideas") is Excel’s built-in AI data analyst. It automatically scans your table to find patterns, trends, and outliers, then suggests charts and PivotTables you can use to visualize them. It’s the fastest way to get initial insights without creating anything from scratch.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide to Using Analyze Data

1. Select Your Table

Start by clicking on any single cell inside your formatted Excel Table. You don't need to highlight the whole thing, Excel knows where the table begins and ends.

2. Open the Analyze Data Pane

Navigate to the Home tab on the Ribbon. On the far right, you’ll see a button with a lightning bolt icon labeled Analyze Data. Click on it. A new pane will open on the right side of your screen. This is the AI's workspace. It will take a few seconds to process your data, and then it will populate the pane with suggestions.

3. Review the AI-Powered Suggestions

The Analyze Data pane is filled with ready-made visualizations based on what it thinks is most interesting about your data. You’ll see things like:

• Bar charts showing sales by product category.
• Line charts illustrating trends over time.
• Pivot tables summarizing key metrics.

For example, if you have a sales dataset, it might automatically generate a chart titled "Sales by Region" or note that "Sales for the 'Electronics' category are noticeably higher." Each suggestion is a fully formed piece of analysis.

4. Add Charts to Your Dashboard

When you see a chart or pivot table you like, simply hover over it and click the + Insert button. Excel will automatically create a new worksheet and place the chart or PivotChart there, perfectly formatted. You can do this for several different suggestions to create a collection of visuals. These will form the building blocks of your dashboard.

Ask Questions in Natural Language

Perhaps the most powerful part of Analyze Data is the query box at the top of the pane that says, "Ask a question about your data." Here, you can type questions in plain English, just like you would with a human data analyst.

Try asking things like:

• "What were the total sales for each marketing channel last month?"
• "Show profit by product as a pie chart"
• "Which 5 states had the highest revenue?"
• "Compare sales versus profit for 2023"

Excel's AI will interpret your question and generate the appropriate visual or answer on the fly. If you ask for a specific chart type, like a pie chart or a line graph, it will do its best to create it for you. This lets you move beyond the initial suggestions and dig deeper into questions specific to your business needs.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Leveraging Microsoft Copilot in Excel (For Advanced AI)

If you have access to Microsoft 365 Copilot, you have an even more powerful AI assistant embedded in Excel. While Analyze Data suggests insights from existing data, Copilot can help you create, transform, and analyze it with more complex conversational prompts.

The Copilot icon appears on the right side of the Ribbon. Clicking it opens a chat pane where you can give it instructions. It works best once your data is formatted as a table.

How to Use Copilot for Dashboards

Copilot excels at multi-step tasks that save you immense time. You can ask it to do things that would normally require several manual steps.

Here are a few example prompts:

• Creating complex analyses: "Create a PivotTable on a new sheet that summarizes revenue and profit by both Region and Product Category."
• Generating Formulas: "Add a new column called 'Profit Margin' and calculate it by dividing the 'Profit' column by the 'Revenue' column for each row."
• Building visuals directly: "Generate a bar chart that shows customer acquisition cost by channel, and sort it from highest to lowest."
• Data highlighting: "Highlight all sales numbers below $1,000 in red."

Think of Copilot as a proactive partner. You guide it with your questions and requests, and it executes the clicks, formulas, and chart creations for you. While newer and not yet available to everyone, it represents the next evolution of AI-powered analysis within spreadsheets.

Assembling Your AI-Generated Dashboard

Once you've used Analyze Data or Copilot to generate a handful of essential charts, the final step is to bring them all together into a central dashboard.

1. Create a New “Dashboard” Sheet

Add a new worksheet to your workbook and name it something intuitive like "Dashboard" or "Overview." This will be the home for all your visualizations.

2. Copy and Paste Your Charts

Go to the worksheets where Excel placed your AI-generated charts. Select a chart, copy it (Ctrl + C), navigate to your Dashboard sheet, and paste it (Ctrl + V). Repeat this for all the visuals you want to include.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

3. Arrange the Layout

Arrange the charts logically on your Dashboard sheet. A common best practice is to place the most important, high-level metrics (like total revenue or key performance indicators) at the top left. More detailed or granular charts can be placed below or to the right. Leave some white space between elements so the dashboard doesn't feel cluttered and is easy to read. You can also use the formatting options under the Shape Format tab to align your charts perfectly.

4. Add Slicers for Interactivity

Slicers are user-friendly buttons that let you filter the data across your dashboard without having to use cumbersome filter dropdowns. They make your dashboard truly interactive.

To add a slicer:

1. Click on any PivotChart on your dashboard.
2. Go to the PivotChart Analyze tab.
3. Click Insert Slicer.
4. A dialog box will appear with a list of your data columns. Check the box for the field you want to filter by, like "Region," "Year," or "Product Category."
5. Click OK.

To make one slicer control multiple charts, right-click the slicer and select Report Connections. In the pop-up, check the boxes for all the PivotCharts you want that slicer to control. Now, clicking a button like "USA" on your Region slicer will instantly update all connected charts to show data for only the USA.

Final Thoughts

Excel's built-in AI tools completely change the game for data analysis and reporting. By transforming your clean data into an Excel Table and using features like Analyze Data or Copilot, you can skip the tedious manual work and move straight to uncovering valuable insights and assembling a professional, interactive dashboard.

While Excel's AI is powerful, digging into insights can get complex, especially once you start pulling in data from multiple business tools like Google Analytics, Shopify, Facebook Ads, and your CRM. We're building Graphed to solve this very problem by putting a true AI data analyst on your team. We connect all your marketing and sales data sources in one place, so you simply ask questions in plain English - like "show me which campaigns are driving the most revenue" or "build a sales performance dashboard for this quarter" - and get interactive, live-updating dashboards in seconds.