How to Make a Stacked Bar Chart in Excel with ChatGPT

Turning raw data into a clear visual story is one of the most powerful features of Microsoft Excel, and the stacked bar chart is a perfect example. This guide will walk you through how to create a stacked bar chart in Excel and demonstrate how you can use ChatGPT as an intelligent assistant to speed up the process, from getting instructions to writing automation scripts.

Graphed

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.

Build Your First Agent

What Exactly Is a Stacked Bar Chart?

A stacked bar chart is a type of graph that uses bars to compare parts to the whole across different categories. Each bar represents a total, while the segments within that bar show the contribution of different sub-categories. Imagine you're tracking quarterly sales, a standard bar chart can show you the total sales for each quarter. A stacked bar chart, however, can show you that same quarterly total, but also break it down by which product lines contributed to those sales.

This "part-to-whole" comparison makes it incredibly useful for understanding composition and distribution.

When to Use a Stacked Bar Chart

Stacked bar charts shine when you need to show the breakdown of a total. They work best when the sum of the parts is as important as the individual parts themselves. Here are a few common scenarios where a stacked bar chart is the right choice:

• Sales Performance: Comparing total sales across different regions, with each bar segmented by product category (e.g., Electronics, Apparel, Home Goods).
• Marketing Analytics: Visualizing monthly website traffic, with each month's bar broken down by source (e.g., Organic Search, Social Media, Paid Ads, Direct).
• Project Management: Showing the number of tasks completed by a team each week, segmented by project or task type (e.g., Bug Fix, New Feature, Documentation).
• Financial Budgeting: Breaking down departmental spending per quarter to see how categories like salaries, marketing, and R&D contribute to the total budget.

Stacked Bar vs. 100% Stacked Bar: What's the Difference?

As you explore charts in Excel, you'll see two options: the "Stacked Bar Chart" and the "100% Stacked Bar Chart." While they sound similar, their purpose is slightly different.

• A Stacked Bar Chart displays the actual values of each part. This is great for seeing both the total value for a category (the total length of the bar) and the magnitude of its components.
• A 100% Stacked Bar Chart shows the percentage contribution of each part, making every bar the same length (100%). This chart is perfect when you want to compare the proportions across categories, rather than the absolute totals. For example, you could use it to see if the percentage of web traffic from social media is growing over time, regardless of the total traffic numbers.

For this tutorial, we will focus on the standard stacked bar chart.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Preparing Your Data for a Stacked Bar Chart in Excel

A beautiful chart starts with well-organized data. Before you even think about clicking the "Insert" tab, make sure your data is structured in a simple, grid-like format. Excel needs this structure to understand how to build your chart correctly.

Your data should be arranged in a table with:

• Categories on the Y-axis: The items you want to compare (e.g., Quarters, Regions, Months) should be listed in the first column.
• Sub-categories on the X-axis: The segments that make up each bar (e.g., Product Lines, Traffic Sources, Project Types) should be listed as unique column headers.
• Numerical Values: The cells within the table should contain the quantitative data for each category and sub-category. Avoid mixing text and numbers in this area.

Here’s a clear example of well-structured data for tracking quarterly regional sales:

Example Data Table:

(In this sample table, 'Quarter' is our main category, and each 'Region' will be a colored segment on the stacked bars.)

A little-known tip: By structuring your data this way first, you allow Excel to automatically create the chart with minimal adjustments. Trying to make a chart from messy or unorganized data often leads to frustration and a lot of manual tweaking.

How to Create a Stacked Bar Chart in Excel (The Standard Way)

Once your data is neatly arranged, creating the chart takes just a few clicks. Follow these steps to generate your first stacked bar chart.

Step 1: Select Your Data

Click and drag your mouse to select the entire data range you want to visualize. Be sure to include the row and column headers - Excel is smart enough to use them as labels and the chart legend.

Step 2: Navigate to the Insert Tab

At the top of the Excel ribbon, click on the Insert tab. This is where you'll find all of Excel's charting tools.

Step 3: Choose the Bar Chart Option

In the "Charts" section, find and click the icon that looks like a bar chart, labeled "Insert Column or Bar Chart." A dropdown menu will appear with several chart types.

Step 4: Select "Stacked Bar"

In the "2-D Bar" section of the dropdown menu, hover over the second option. A tooltip will appear confirming it is the "Stacked Bar" chart. Click on it.

Excel will instantly generate a stacked bar chart and place it on your worksheet. It automatically assigns different colors to your sub-categories ("Regions" in our example) and creates a legend to explain what each color represents.

Step 5: Customize Your Chart (Optional)

Once the chart is created, you can customize it to make it more readable. Click on the chart, and two new tabs, "Chart Design" and "Format," will appear on the ribbon. You can use these to:

• Add a chart title by clicking on the default "Chart Title" text.
• Add data labels by clicking the "+" icon on the right of the chart to open Chart Elements.
• Change the color scheme using the "Change Colors" option in the Chart Design tab.

Using ChatGPT to Guide Your Excel Charting

The manual method is straightforward, but what if you forget a step, want a specific customization, or aren’t sure which chart to use? This is where ChatGPT becomes your personal data assistant. Instead of hunting through menus or reading documentation, you can just ask.

Using ChatGPT for Excel tasks removes the guesswork and makes advanced features more accessible.

Graphed

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.

Build Your First Agent

Prompt Example 1: Basic Instructions

Let's say you have your data ready but need a reminder on how to create the chart. You can give ChatGPT a simple prompt like:

Give me simple, step-by-step instructions on how to create a stacked bar chart in Excel. My data has quarters (Q1, Q2, Q3, Q4) in column A and sales regions (North, South, West) in columns B, C, and D.

ChatGPT will provide a clear, easy-to-follow numbered list that walks you through the exact process we covered above, tailored to your specific data layout. It takes all the mental energy out of a task, leaving you free to focus on the insights.

Prompt Example 2: Customization Guidance

Now, let's say your chart is created but looks a bit plain. You can ask ChatGPT for specific formatting help:

I have a stacked bar chart in Excel. How can I add the total value at the end of each bar? I also want to move the legend to the top.

Instead of you clicking around randomly, ChatGPT will provide precise instructions, likely directing you to the "Chart Elements" menu ("+") to add data labels and then showing you how to format them correctly or move the legend. It converts a potentially frustrating hunt into a simple, conversational Q&A.

Advanced: Generating VBA Code with ChatGPT to Automate Chart Creation

For those who find themselves creating the same report every week or month, the manual process can get repetitive. This is where Excel's VBA (Visual Basic for Applications) comes in. VBA allows you to write scripts that automate almost any action in Excel, including chart creation.

The problem? Most people don't know how to write VBA code. But with ChatGPT, you don't have to. You can describe the task you want to automate, and it will write the VBA code for you.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

Crafting a Prompt for VBA Code

To get the best result, your prompt needs to be specific. Tell ChatGPT exactly what you want the script to do, including the data range, sheet name, chart type, and any formatting details.

Powerful prompt example:

Write an Excel VBA macro that creates a stacked bar chart. The source data is in "Sheet1" in the range A1:D5. The categories are in the first column. The macro should place the new chart on the same sheet. Give the chart the title "Quarterly Regional Sales."

ChatGPT will then generate a block of VBA code similar to this:

Sub CreateStackedBarChart()
'Declare variables
Dim ws As Worksheet
Dim chartRange As Range
Dim chrt As ChartObject

'Set worksheet and data range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set chartRange = ws.Range("A1:D5")

'Create the chart object
Set chrt = ws.ChartObjects.Add( _
    Left:=chartRange.Left + chartRange.Width + 50, _
    Top:=chartRange.Top, _
    Width:=400, _
    Height:=250)
    
'Configure the chart
With chrt.Chart
    .SetSourceData Source:=chartRange
    .ChartType = xlBarStacked
    .HasTitle = True
    .ChartTitle.Text = "Quarterly Regional Sales"
    .HasLegend = True
    .Legend.Position = xlBottom
End With

End Sub

How to Use the Generated VBA Code

1. Press Alt + F11 in Excel to open the VBA Editor.
2. In the VBA Editor, go to Insert > Module. A new blank code window will appear.
3. Copy the code generated by ChatGPT and paste it into the module window.
4. Close the VBA Editor.
5. Back in Excel, press Alt + F8 to open the Macro dialog box. You should see "CreateStackedBarChart" in the list.
6. Select it and click Run.

Instantly, a perfectly formatted stacked bar chart will appear on your sheet. You can connect this macro to a button on your worksheet to create your report with a single click, saving you valuable time every reporting cycle.

Final Thoughts

Creating a stacked bar chart in Excel is a fundamental skill for anyone working with data, and using an AI tool like ChatGPT can smooth out the learning curve and even automate repetitive work. By moving from manual creation to guided instructions and finally to VBA automation, you can transform how you handle data visualization tasks.

While this streamlines reporting within Excel, the next logical step is to automate the entire process from data connection to visualization. At our company, we designed Graphed to do exactly that. Instead of exporting data to spreadsheets, you connect your business platforms - like Salesforce, Google Analytics, or Shopify - and use plain English to build real-time, interactive dashboards. It's a faster way to get the insights you need without the tedious manual work.