How to Create Bar Charts in Excel with Multiple Data

Creating a bar chart with a single set of data in Excel is simple enough, but things can get a little tricky when you need to compare multiple data series at once. This article provides a clear, step-by-step guide to building bar charts in Excel that effectively visualize and compare multiple sets of data. We'll cover everything from structuring your data correctly to customizing your chart to tell a compelling story.

Why Use a Bar Chart for Multiple Data Sets?

Bar charts are one of the most effective ways to compare different items or categories. When you add multiple data series, their power multiplies. You can track performance across different time periods, compare segments against each other, or show how different products stack up in various markets.

Consider these common scenarios:

• Sales Performance: Comparing the quarterly sales figures for your top three products.

• Marketing Analytics: Showing website traffic from different sources (e.g., Organic Search, Social Media, Direct) over the last six months.

• Survey Results: Visualizing how different age groups (e.g., 18-25, 26-35, 36-45) responded to a survey question.

• Financial Reporting: Displaying revenue vs. profit for several business units side-by-side.

In each case, a bar chart with multiple data sets lets you see both the performance of individual categories and the relationship between them at a single glance. You can quickly spot trends, identify outliers, and understand the bigger picture without getting lost in a sea of numbers.

First, Prepare Your Data Correctly

Before you even think about clicking the 'Insert Chart' button, your success depends on how you structure your data. Excel is powerful, but it needs a clean, logical table to understand what you want to visualize. A few minutes spent organizing your data will save you a massive headache later.

For a multiple-series bar chart, your table should be formatted like this:

• Your primary categories should be listed down the first column (Column A). These will become the labels along your vertical (Y) axis. Examples: Product Names, Months, Campaign Names.

• The headers for your different data series should be listed across the first row (Row 1). These will be explained in your chart's legend. Examples: Quarter 1, Quarter 2, 2022, 2023, USA, Canada.

• The numerical values should fill the cells where the rows and columns intersect.

Here’s a sample data layout for "Quarterly Social Media Follower Growth" across different platforms. Notice how it's clean, simple, and easy for both you and Excel to read.

Correct Data Structure:

Stick to this format. Avoid empty rows or columns within your data, and don't include totals or summaries in your selection - those can skew the chart.

How to Create a Clustered Bar Chart (Side-by-Side Comparison)

A clustered bar chart is your go-to option for direct, side-by-side comparisons. Each category (like "Instagram") will have a group of bars, with each bar representing a different data series (like "Q1 Growth," "Q2 Growth," etc.). This is perfect for seeing which quarter performed best for a specific platform.

Step 1: Select Your Data Range

Click on the top-left cell of your data table (in our example, the cell containing "Platform") and drag your mouse to select the entire table, including the row and column headers. Having the headers selected is important, as Excel will use them to automatically label your axes and legend.

Step 2: Insert the Bar Chart

With your data selected, navigate to the Insert tab on Excel's ribbon. In the Charts section, you'll see an icon for Insert Column or Bar Chart. Click it.

Step 3: Choose the Clustered Bar Chart

A dropdown menu will appear with several chart options. Under the 2-D Bar section, select the very first option, which is Clustered Bar. You can also choose a "3-D Clustered Bar" if you prefer that aesthetic, but 2-D charts are generally easier to read accurately.

Excel will instantly generate a chart and place it on your worksheet. In our example, you'll see the platforms (Instagram, TikTok, etc.) listed on the vertical Y-axis and the follower numbers on the horizontal X-axis. Each platform will have four colored bars next to it, and a legend at the bottom will tell you that the blue bar represents Q1, orange represents Q2, and so on.

How to Create a Stacked Bar Chart (Part-to-Whole Comparison)

What if you're less interested in comparing quarters side-by-side and more interested in the total yearly growth for each platform, broken down by quarter? That's where a stacked bar chart comes in. It combines the bars for each category into a single bar, with colored segments representing each data series.

Step 1: Select Your Data

The process starts the same way. Select the entire data table including the headers.

Step 2: Insert the Stacked Bar Chart

Go back to the Insert tab > Insert Column or Bar Chart.

Step 3: Choose the Stacked Bar Chart

In the dropdown menu, under the 2-D Bar section, select the second option, Stacked Bar. Once you click it, Excel will create the chart.

You’ll now see a single, long bar for each social media platform. The total length of the bar represents the total growth for the entire year, and the different colored segments show how much each quarter contributed to that total. This is great for understanding composition. There's also a "100% Stacked Bar Chart," which makes each bar the same length and shows the relative percentage of each component - useful for comparing proportions when the totals are very different.

A Quick Fix: The "Switch Row/Column" Button

Sometimes, Excel gets confused and organizes the chart differently than you intended. It might plot your quarters as the main categories and group your platforms under each one. This isn't wrong, but it might not be the story you want to tell.

Fixing this is incredibly easy. Click on your chart to select it, which will bring up the Chart Design tab. On that tab, you'll find a button called Switch Row/Column. Click it once, and Excel will instantly flip the data, putting your platforms back on the Y-axis and your quarters in the legend. This is one of the most useful buttons you'll find when working with Excel charts.

Customizing Your Chart for Clarity and Impact

Excel’s default charts are functional, but you should never stop there. A few thoughtful customizations can turn a boring chart into a powerful and persuasive communication tool.

Select your chart, and you'll see a green plus sign (+) appear on the top right. This is the Chart Elements shortcut, and it’s your command center for customization.

Add Essential Chart Elements

• Chart Title: Your chart must have a clear, descriptive title. Click the checkbox and replace "Chart Title" with something specific, like "Quarterly Follower Growth by Social Media Platform."

• Axis Titles: This is non-negotiable for clarity. Check this box to add titles for both the horizontal and vertical axes. Label the horizontal axis "New Followers" and the vertical axis "Platform" so your audience knows exactly what they are looking at.

• Data Labels: Sometimes it's helpful to see the exact value of each bar without having to guess from the axis. Checking 'Data Labels' will place the numerical value on each bar. Use this with care - on a cluttered chart, it can look messy.

• Legend: Excel usually adds this automatically, but you can use this option to move it to the top, left, bottom, or right for better placement.

Improve Your Chart's Visuals

• Colors and Styles: In the Chart Design tab, you can quickly apply different pre-built design styles and color palettes. Try a few to see what works best. For more control, right-click on a single series (e.g., all the blue "Q1" bars) and select Format Data Series. From the Fill & Line menu (the paint bucket icon), you can choose any color you want. This is great for aligning reports with your brand colors.

• Adjusting the Axis: If all your values are very large, you might have a lot of empty space on the left side of your chart. Right-click on the horizontal axis and select Format Axis. A pane will open, allowing you to manually set the minimum and maximum bounds. For instance, if all your values are above 500, you could set the minimum bound to 500 to zoom in on the variation. Just be careful, as starting an axis at a value other than zero can sometimes be misleading.

• Reduce Clutter: Think about what you can remove. Do you really need the gridlines? If not, uncheck them in the Chart Elements menu. A cleaner, more minimalist look can often have a bigger impact.

Final Thoughts

Creating clustered and stacked bar charts in Excel to compare multiple data series is a fundamental skill for anyone working with data. Once you have a well-organized data table, the process is quick and intuitive, allowing you to tell a complex story in a simple, visual format that anyone can understand.

While mastering spreadsheets is valuable, the process of manually exporting CSVs from a dozen different platforms and wrangling them in Excel is often the most time-consuming part of reporting. At Graphed, we automate that entire process. You can connect your marketing and sales tools like Google Analytics, Shopify, and Facebook Ads in seconds, and then use simple, natural language to build the dashboards you need. Instead of wrestling with pivot tables, you can just ask, "Show me a bar chart of my top 5 traffic sources by month for the last year," and get an interactive, real-time dashboard instantly.