How to Make a Comparison Chart in Excel

Comparing performance metrics is at the heart of data analysis, but staring at tables of numbers can make your eyes glaze over. Excel’s charts turn those numbers into a clear, visual story, making it easy to spot trends and outliers at a glance. We’ll show you exactly how to create effective comparison charts in Excel, step-by-step.

Why Use a Comparison Chart in Excel?

Before jumping into the “how,” it’s helpful to understand the “why.” When you're trying to compare two or more sets of data - like sales figures from different quarters, website traffic from various marketing channels, or project completion rates across teams - a chart just works better than a table. Here’s why:

• Instant Insights: A bar chart instantly shows you which product sold the most. A line chart immediately reveals if your growth is accelerating or slowing down. Your brain processes these visual cues much faster than it can compare rows of numbers.
• Spotting Trends and Patterns: Is one region consistently outperforming others? Did a specific marketing campaign cause a spike in user sign-ups across the board? Charts make it easy to see these relationships and patterns that are often hidden in raw data.
• Clear Communication: When you need to present your findings to your team, a client, or a manager, a well-designed chart tells the story for you. It simplifies complex information, making your point clear, concise, and persuasive without needing a lengthy explanation.
• Highlighting Differences: The core purpose of a comparison chart is to show how things differ. Whether you're highlighting a massive success or a concerning dip, a visual representation makes the scale of that difference obvious and impactful.

Choosing the Right Chart for Your Comparison

Excel offers a whole menu of chart types, and choosing the right one is key to making your comparison clear. Using the wrong chart can obscure your message or even misrepresent the data. Here are the best options for comparison and when to use them.

Clustered Column or Bar Chart

This is your workhorse for direct comparisons between different categories. It places bars or columns representing different data series side-by-side, making for a very intuitive apples-to-apples comparison.

• Best for: Comparing distinct items against each other. For example: Product sales by region, ad spend vs. revenue by campaign, or customer satisfaction scores for different product features.
• When to use a column chart (vertical bars): Use it when you have fewer categories to compare. The vertical format works well for showing changes over time, although a line chart is often better for that.
• When to use a bar chart (horizontal bars): This is perfect when you have long category labels that would get squished or awkwardly angled on a column chart's horizontal axis. It provides plenty of readable space.

Line Chart

Line charts are the undisputed champion for comparing performance over a continuous period of time. Each data series gets its own line, allowing you to easily track their progress together.

• Best for: Showing trends and changes over time for multiple series. For example: Comparing website traffic from Google vs. Facebook over the last six months, tracking monthly recurring revenue (MRR) for two different subscription plans, or monitoring a stock's price over a year versus a competitor's.
• Key takeaway: If your horizontal axis involves dates, times, months, or years, a line chart is almost always the right choice.

Stacked Column or Bar Chart

This chart is a bit different. Instead of placing bars side-by-side, it stacks them on top of one another. This is useful when you want to compare the total value across categories while also understanding the composition of that total.

• Best for: Comparing the total while also showing its parts. For example: Comparing total quarterly sales across different years, where each bar's stacks represent sales from different product lines. You can see how the total has changed and also which product line is contributing more or less each quarter.
• A word of caution: While great for showing the total, it can be tricky to make precise comparisons between the middle segments of each stack. The lowest segment (starting from the zero baseline) is easy to compare, but the others aren't.

Step-by-Step Guide: Making a Comparison Chart in Excel

Let's walk through creating the most common comparison chart: a clustered column chart. We'll use a simple dataset comparing the quarterly sales for two different products.

Step 1: Get Your Data Ready

Properly organizing your data is the most important step. A poorly structured table will confuse Excel and give you a useless chart. For a comparison chart, keep it simple and clean.

Arrange your data in a table with your categories in the first column and the series you want to compare in the next columns. Let's imagine you sell two products: "Widgets" and "Gadgets."

Your table should look something like this:

This structure is crystal clear. Excel understands that "Widgets" and "Gadgets" are your items, and "Q1 Sales" and "Q2 Sales" are the metrics you want to compare for each.

Step 2: Select Your Data

Click and drag your cursor to select the entire table you just created, including the column and row headers. In our example, you would select the range from the top-left cell (the blank one or one labeled "Product") down to the bottom-right cell with the Q2 Sales figure for Gadgets.

Step 3: Insert Your Chart

With your data selected, go to the Excel ribbon at the top of the screen:

1. Click on the Insert tab.
2. In the Charts section, you’ll see icons for different chart types. Hovering over them will show you a preview of how your data will look.
3. Click the icon for "Column or Bar Chart."
4. From the dropdown menu, select the first option under "2-D Column," which is the Clustered Column chart.

Excel will instantly generate the chart and place it on your worksheet. Voila! You now have a visual comparison of your product sales.

Step 4: Customize and Clean Up Your Chart

The default chart is functional, but a little customization makes it professional and easier to read.

• Add a Chart Title: The default title is probably just "Chart Title." Click on it and give it a descriptive name, like "Q1 vs. Q2 Sales Performance."
• Label Your Axes: Your chart might not automatically label the vertical (value) axis. You can add one by clicking the + icon next to the chart and checking the box for "Axis Titles." Then, edit the new title to something like "Sales Revenue ($)."
• Adjust the Legend: The legend (showing which color represents Q1 vs. Q2) is usually placed correctly, but you can click and drag it to the top, bottom, or side for better placement.
• Modify Colors: Don't like the default blue and orange? Right-click on one of the bars, select "Format Data Series," and use the "Fill" option (paint bucket icon) to change a series's color to match your brand or presentation.

Tips for Better Comparison Charts

Once you've mastered the basics, here are a few extra tips to make your comparison charts even more effective.

• Keep It Simple: Avoid 3D effects, shadows, and distracting background patterns. The goal is clarity, and these embellishments often just add visual noise that makes the chart harder to read.
• Sort Your Data: If you're comparing multiple categories in a bar chart (e.g., sales across 10 different products), sort the data from highest to lowest before creating the chart. This instantly reveals top and bottom performers without forcing the viewer to scan back and forth.
• Use Data Labels Wisely: You can add the exact numerical value to each bar or point by clicking the + icon next to your chart and selecting "Data Labels." This can be helpful but can also create clutter if you have too many bars. Use it when displaying the exact numbers is important.
• Consider a Combo Chart: What if you want to compare two different types of metrics, like sales volume (in units) and revenue (in dollars)? For this, a combo chart is perfect. You can find it under Insert > Charts > Combo. This allows you to represent one series as columns and the other as a line on a separate, secondary axis.

Final Thoughts

Creating a comparison chart in Excel is a straightforward process once your data is properly structured. The most critical step is choosing the right chart type to match your data and the story you want to tell - a clustered column for direct categorical comparisons and a line chart for tracking trends over time. With a bit of customization, you can turn any dataset into a clear and compelling visual.

While making one-off charts in Excel is simple enough, the real challenge arises when you have to do it every single week. Pulling fresh data from platforms like Google Analytics, Shopify, or Salesforce, cleaning it up, and recreating the same reports is a manual process that consumes valuable time. That’s the exact friction we built Graphed to eliminate. Instead of spending hours in spreadsheets, you can connect your data sources once and use plain English to build live, interactive dashboards that update automatically. You just ask, "Show me a bar chart comparing website traffic from Google vs. Facebook for the last quarter," and the chart appears instantly, giving you back the time to focus on strategy, not just reporting.