How to Make a Comparison Chart in Excel with AI

Manually creating comparison charts in Excel has always involved the tedious ritual of selecting data, navigating menus, and tweaking settings. But what if you could just tell Excel what you want to see? That's the power of using AI to build your visualizations. This guide will walk you through exactly how to create effective comparison charts in Excel using simple, conversational language, saving you time and helping you find insights faster.

Why a Comparison Chart Is Your Go-To Visual

Before we jump into the "how," let's quickly touch on the "why." A comparison chart does exactly what its name suggests: it helps you compare values across different categories or over time. It visually represents relationships in your data, making it far easier to understand performance than staring at a wall of numbers.

You can use them for just about anything, including:

Comparing sales figures for different products side-by-side.
Tracking marketing campaign performance (e.g., clicks, conversions) month-over-month.
Visualizing website traffic from different channels like Organic Search, Social, and Paid.
Contrasting budget vs. actual spending for various departments.

The most common and effective comparison charts are column charts, bar charts, and line charts. They're simple, universally understood, and excellent at revealing trends, spotting top performers, and highlighting areas that need attention. The goal isn't to create something complex, it's to get a clear answer to a business question.

Getting Your Data Ready for AI

The saying "garbage in, garbage out" has never been more true than when working with AI. For Excel's AI, known as Copilot, to understand your request, it needs clean, well-structured data. Thankfully, this doesn't require complex data science skills - just good spreadsheet habits.

Keep Your Table Simple & Tidy

AI works best with data that's organized in a simple, predictable table format. Follow these rules to set yourself up for success:

Use Clear Headers: Give each column a unique and descriptive header in the first row. Use terms like "Month," "Total Sales," or "Ad Spend."
One Row Per Record: Each row below the header should represent a single entry (e.g., one month's data, one product's performance).
Avoid Merged Cells: Merged cells are confusing for both humans and AI. Keep your grid structure consistent.
Consistent Formatting: Ensure numbers are formatted as numbers and dates are formatted as dates. Don't mix text and numbers in the same column (except for the header).

A Quick Example Data Set

Let's use a simple data set to guide our examples. Imagine we're a small e-commerce store comparing the performance of two popular products over the first quarter.

Here’s what our data looks like in an Excel sheet:

Month, Product A Sales, Product B SalesJanuary, 1500, 1200February, 1800, 2100March, 1650, 2400April, 2000, 2300

This data is clean, clearly labeled, and perfect for creating a comparison chart.

The Manual Method: A Quick Refresher

To appreciate the efficiency of AI, it helps to remember the "old" way. Creating a comparison chart manually in Excel usually involves these steps:

Select your data: Drag your cursor to highlight the entire data range, including the headers.
Navigate to 'Insert': Click the "Insert" tab on the Excel ribbon.
Choose Your Chart: In the 'Charts' section, you can click "Recommended Charts" to see Excel's suggestions or choose a specific type like a Clustered Column or a 2-D Bar Chart.
Customize: From there, you might adjust the title, add data labels, change colors, and format the axes until it tells the story you want.

This process isn't incredibly difficult, but it involves several clicks and can feel clumsy, especially when you need to make quick adjustments or create multiple charts. Now, let's see how much faster it is with AI.

Let's Build: Creating a Comparison Chart with Excel Copilot

Copilot for Microsoft 365 is the AI assistant built directly into Excel. It uses natural language processing to understand your requests and perform tasks for you, including generating charts instantly. If your organization has it enabled, you'll see the Copilot icon in your ribbon.

Step 1: Format Your Data as a Table

While not always strictly necessary, Copilot works best when your data is formatted as an official Excel Table. This gives your data structure and helps the AI understand the distinct boundaries of your dataset.

To do this, simply click anywhere inside your data range and press Ctrl + T on your keyboard (or go to the 'Insert' tab and click 'Table'). A small dialog box will pop up, check the box for "My table has headers" and click OK. You'll see your data get a nice style refresh.

Step 2: Launch Copilot

With your table selected, click the 'Copilot' button on the 'Home' tab of the Excel ribbon. This will open a chat pane on the right side of your screen, ready for your instructions.

Step 3: Ask for Your Chart in Plain English

This is where the magic happens. Instead of clicking through menus, you just type what you want to see. Think about how you would describe the chart to a team member. You can be direct and specific.

Using our sample data, here are a few prompts you could try:

"Create a column chart comparing Product A Sales and Product B Sales."
"Show a side-by-side bar chart of sales for both products each month."
"Generate a line chart plotting the sales trends for Product A and Product B."

After typing your prompt and hitting 'Send', Copilot will analyze your table and instantly generate the requested chart, often inserting it directly into your workbook.

Step 4: Refine Your Chart with Follow-Up Commands

The first chart is rarely your final draft. With Copilot, you don't have to go back to the format menus to make tweaks. You can continue the conversation to refine the visualization.

Here are some examples of follow-up prompts:

Changing the type: "Change this to a smoothed line chart."
Adding details: "Add data labels to the top of each bar."
Adjusting titles: "Update the chart title to 'Q1 and April Sales Performance'."
Modifying colors: "Make the bars for Product B green."

Copilot will modify the existing chart based on each new command, making the iteration process incredibly fast and intuitive.

Going Deeper: From Chart Creation to Instant Analysis

The real advantage of AI isn't just making charts faster - it's getting answers from your data faster. Copilot's capabilities extend beyond visualization. You can also use it to ask direct questions about the data in your table.

Querying Your Data Directly

After building your chart, you might have specific questions. Just ask them directly in the chat pane.

"Which month had the highest sales growth for Product B?"
"In which month was the sales gap between the two products the largest?"
"Calculate the total sales for each product."

Copilot can answer these questions with text-based summaries or even create new helpful summaries of your data, taking you from raw numbers to actionable insights in seconds.

Limitations and Better Ways Forward

While AI in Excel is a huge step forward, it's important to approach it with realistic expectations. Copilot is most effective when working with a single, clean table of data that already exists inside Excel. Getting the data into that state is often the biggest challenge.

The most time-consuming part of analysis is rarely the chart-making itself, it's the manual labor of exporting data from different sources - Google Analytics, Shopify, Salesforce, Facebook Ads - and wrangling it into a usable format in a spreadsheet. This process is repetitive, prone to errors, and your data is outdated the moment you export it.

If you're spending hours every week stitching together CSV files before you can even start analyzing, then you need an automation layer that brings your data together automatically.

Final Thoughts

Excel's built-in AI fundamentally changes the "how" of creating comparison charts. It transforms the process from a series of manual clicks into a simple conversation, allowing you to build and refine visuals at the speed of thought. By learning how to structure your data and write clear prompts, you can spend less time wrangling charts and more time understanding what they tell you.

While AI in Excel is great for analyzing data you already have in a sheet, we’ve found the bigger reporting challenge is pulling data from all your different tools in the first place. That’s why we created Graphed - to automate the tedious data collection process. Instead of downloading CSVs from Google Analytics, Facebook Ads, Shopify, and Salesforce by hand, you can connect them to Graphed in seconds. From there, just ask questions in plain English to build real-time, interactive dashboards that update automatically. This way, your reports are never stale, and everyone on your team can see the latest insights without waiting for a manual update.