How to create a stacked bar chart in Excel – step-by-step guide (with images)

Time to make some stunning visuals!
Last Updated on May 20, 2024
Here is an image of the Microsoft Excel logo with a gradient background. The logo features a stylized "X" in green, along with a grid pattern representing a spreadsheet. Learn how to create a stacked bar chart in Excel! The PC Guide logo is present at the top.
You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

A visual representation of your data is one of the most effective ways to interpret and derive meaning from raw data. Visual representation includes different types of graphs, charts, box plots, and scatter plots. 

In this article, we explore how to create one of the most common and valuable charts available in Excel — the stacked bar chart. You can create a stacked bar chart in 2D or 3D in Excel. We will lead you through its creation, modification, and editing so you can create your own stacked bar chart when needed. 

What is a stacked bar chart?

A stacked bar chart is a great way to compare data from two different segments, categories, or variables. 

A stacked chart has bars or columns stacked on top of one another. The bars can also have sub-elements, which are colored to show the parts of a total figure. 

How to make a stacked bar chart in Excel

Scenario on hand: We have a sales dataset in Excel. 

What we want to accomplish: In our article, we will work with a sample sales dataset to generate 4 different types of stacked bar charts in Excel. Here are the types of charts we will cover in this article:

  1. 2-D stacked bar chart
  2. 100% 2D Stacked Bar Chart
  3. 3-D Stacked Bar Chart
  4. 100% 3-D Stacked Bar Chart

Throughout this article, we will also explore how to format and edit your charts so you can improve your visual representations. 

Organizing your data

Before we get on to the different types of stacked bar charts, let’s first explore how you can organize your data. For the chart to accurately depict your raw data, it is essential to ensure that no information is missing. 

We have taken a monthly sales data comparison of three supermarkets for our demonstration. Here is how we have organized the data so it looks clean:

Spreadsheet titled "Dataset for Creating a Stacked Bar Chart in Excel" with monthly sales data for PrimeMart, Freshway, and HyperMart. Ideal to create insightful visual representations.
Dataset

2-D stacked bar chart

To insert a 2-D stacked bar chart, first select your organized data. Then follow the steps as illustrated below:

Step 1: Choose the chart type

To choose the chart type, go to Insert from the top ribbon and go to the bar and column chart options in the Charts toolbar. From the 2-D charts, select the 2-D stacked bar chart:

An Excel spreadsheet with a highlighted area showing steps to create a stacked bar chart. The "Insert" tab and the 2-D stacked bar chart type are clearly highlighted for easy guidance.
Stacked Bar

Here is how our chart comes up:

Create a stacked bar chart in Excel displaying monthly sales figures for PrimeMart, Freshway, and HyperMart from January to December. PrimeMart is blue, Freshway is orange, and HyperMart is gray, with HyperMart achieving the highest overall sales.
Chart created

Since we have 12 months of data, the months are on the y-axis, and the sales figures are on the x-axis. The bar colors indicate the supermarkets in our data. The legend shows which supermarket each color represents. 

Step 2: Inverting the chart

Let’s suppose we want the months as the bar colors. There is a clever shortcut in Excel for inverting the chart. 

When you select your inserted chart, you get an additional Chart Design tab on your ribbon. Select this to reveal the option Switch Row/Column. Simply press this button to switch how the chart is displayed:

Excel spreadsheet with a stacked bar chart comparing sales data for PrimeMart, Freshway, and HyperMart from January to December. The "Switch Row/Column" option is highlighted in the Chart Design tab, showing how to create a more insightful visual representation.
Switch Row/Column button

Here’s the chart we get when we use this option:

        Description: Create a stacked bar chart in Excel comparing monthly sales data for HyperMart, Freshway, and PrimeMart. Each bar is divided into segments representing sales from January to December.
Chart created

The bars’ many colors make it difficult to make sense of the data. To switch it back, we can use the same button again. However, this option might be useful if you don’t have many data categories. 

Step 3: Editing the chart

You might have noticed that our chart has “Chart Title” in place of a title. If we want to remove this title, we can simply click on it and press delete. However, a better option is to use the Add Chart Element option to add or remove chart elements like title, legends, horizontal lines, vertical lines, axis titles, and data labels. 

You will find this option on the far left under Chart Design:

The spreadsheet software, possibly Excel, displays a stacked bar chart alongside a data table. The "Chart Design" tab is open with various chart elements options clearly shown on the left-hand side, enabling users to easily create and customize their charts.
Chart Elements

Alternatively, you can also use the + sign on the right side of the chart to add or remove chart elements quickly:

Excel spreadsheet with a stacked bar chart. The chart elements menu is open with the Legend option highlighted. A red arrow points to a checkbox next to the Legend option indicating how to create your customized view.
Chart Elements sign

We want to add data labels to the chart so everything looks clear. We use the Add chart element option to do this. This is what our chart looks like now:

Horizontal stacked bar chart of yearly sales for PrimeMart, Freshway, and HyperMart by month. Created in Excel, it shows PrimeMart leading in sales overall, peaking in September (478) and dipping to its lowest in February (254).
Yearly Sales chart

Step 4: Formatting the chart

To change the chart’s colors and design, use the Chart Styles toolbar under Chart Design or click the paintbrush icon on the left of the chart below the + symbol:

Screenshot of an Excel spreadsheet showing a stacked bar chart titled "Chart Title", with data for multiple stores from January to December. The Chart Design tab is active in the toolbar, making it easy to create or adjust the chart as needed.
Chart Styles

100% 2D Stacked Bar Chart

The 100% stacked bar chart shows the data as a % of the total. In our case, it will show the % of sales each supermarket attained per month. As a result, the bars will not be of varying lengths. 

Step 1: Changing the chart type

If you already have a chart and want to change it to a 100% stacked bar chart, there’s an option under chart design that can help you. This option is called Change Chart Type, and from this option, we choose the 100% stacked bar chart:

Screenshot of an Excel worksheet with a chart being edited. A red arrow points to the "100% Stacked Bar" option in the "Change Chart Type" dialog box, which is currently open. This illustrates how to create a stacked bar chart in Excel for better data visualization.
Change chart type

We choose the 100% stacked bar chart. Here’s what the chart looks like:

Create an Excel stacked bar chart titled "Yearly Sales" depicting monthly sales figures for three stores: PrimeMart, Freshway, and HyperMart. Each bar represents a month with stacking segments showing sales contributions from each store.
Yearly Sales chart

Step 2: Changing the gridlines

Although the y-axis shows gridlines at every 10%, we can add more gridlines to improve the visual representation. 

For this, we go to Add Chart Element > Gridlines and add Primary Minor Vertical gridlines to get more gridlines:

Screenshot of an Excel spreadsheet displaying a Yearly Sales stacked bar chart with gridlines. The 'Chart Elements' menu is open on the left, highlighting 'Gridlines' with options for primary and minor gridlines, making it easy to create a detailed and organized chart.
Primary Minor Vertical gridlines

Here’s what our chart looks like now:

Create an Excel bar chart showing monthly sales for PrimeMart, Freshway, and HyperMart across a year. PrimeMart sales are in blue, Freshway sales in orange, and HyperMart sales in gray.
Yearly Sales updated chart

3-D Stacked Bar Chart

The 3-D stacked bar chart is similar to the 2D stacked bar chart, with a slight variance in the chart style. The bars in a 3D stacked bar chart look three-dimensional. You can find this option under Change Chart Type.

Screenshot of Excel showing the "Change Chart Type" dialog box with different chart options, including a selected "100% Stacked Bar" chart. You can easily create and customize your charts in Excel using this feature. The spreadsheet and ribbon menu are also visible in the background.
3-D Stacked Bar

You can even find this chart type under 3D when you’re first inserting the chart.

A screenshot of Microsoft Excel displaying the "Insert" tab's chart options. It shows the dropdown for bar charts, highlighting the "3-D Stacked Bar" option with a description. The Excel interface clearly presents various choices for creating visually appealing stacked bar charts.
3-D Stacked Bar option

Here’s what a 3D stacked bar chart looks like:

Excel stacked bar chart showing monthly sales for three stores (PrimeMart, Freshway, HyperMart) in a year. Each month lists sales figures for the three stores with PrimeMart in blue, Freshway in orange, and HyperMart in gray.
Yearly Sales 3-D chart

We changed the chart style for this 3D stacked bar chart, and here’s the result:

Bar chart created in Excel depicting monthly sales for PrimeMart, Freshway, and HyperMart. PrimeMart has the highest sales in most months. The stacked bar chart displays data from January to December with values up to 600.
Updated style

This is a much better representation of our data than the chart we first started with. In Excel, you have many options to modify your charts, making them look just the way you like for your presentations and accurately representing your data. 

100% 3-D Stacked Bar Chart

You can also make a 100% 3-D stacked bar chart. This is similar to the 100% stacked bar chart, just differing in style:

A screenshot of Excel with the "Change Chart Type" dialog box open, showing different chart options, including a stacked bar chart. The background contains a chart and worksheet data, demonstrating how to create visual representations of your data effortlessly.
3-D 100% Stacked Bar

Advantages of a stacked bar chart in Excel

A stacked bar chart is one of the most useful charts available in Excel. Here are some advantages of creating a stacked bar chart in Excel:

  • Excel has tons of options to create 3-D, professional-looking charts. 
  • Stacked bar charts allow you to create a data representation that can be understood easily. 
  • The chart can be copied to your presentation or reports. 
  • These charts will enable you to depict changing data over time. 
  • They can be used to illustrate a part-to-whole ratio quite effectively. 

Disadvantages of a stacked bar chart in Excel

Even though Excel allows you to create different types of stacked bar charts, there are some disadvantages to using these charts for your data:

  • Excel does not automatically calculate data percentages. The 100% stacked bar chart can only convert data labels to percentages if you use a pivot table or calculate percentages separately. 
  • If you have a lot of categories, the stacked bar chart can quickly become very cluttered. For instance, in our case, if we wished to see the month-wise performance of each supermarket, we were stuck with a cluttered representation of the data. 

Wrapping up

Our article explores how to create different types of stacked bar charts in Excel and covers the various options for editing and formatting your chart to make it more appealing and relevant to your use. 

Learn more about charts in Excel through these guides:

Abdul is a tech writer and Editor for PC Guide, specializing in all things tech, gaming, and hardware.

'),b.insertAdjacentHTML(d,e));return document.getElementById(a)};c.prototype._repeatDynamicAds=function(b){var a= [],d=this._removeRecipeCapExperiment.result&&"Recipe"===b.location?99:this.locationMaxLazySequence.get(b.location),e=b.lazy?u(d,0):0;d=b.max;var f=b.lazyMax;e=Math.max(d,0===e&&b.lazy?d+f:Math.min(Math.max(e-b.sequence+1,0),d+f));for(f=0;f=d;a.push(y(y({},b),{sequence:g,lazy:h}))}return a};c.prototype._locationEnabled=function(b){b=this._clsOptions.enabledLocations.includes(b.location);var a=this._clsOptions.disableAds&&this._clsOptions.disableAds.all|| document.body.classList.contains("adthrive-disable-all"),d=!document.body.classList.contains("adthrive-disable-content")&&!this._clsOptions.disableAds.reasons.has("content_plugin");return b&&!a&&d};return c}(),Za=function(c){function b(a,d){var e=c.call(this,a,"ClsVideoInsertion")||this;e._videoConfig=a;e._clsOptions=d;e._IN_POST_SELECTOR=".adthrive-video-player";e._WRAPPER_BAR_HEIGHT=36;e._playersAddedFromPlugin=[];d.removeVideoTitleWrapper&&(e._WRAPPER_BAR_HEIGHT=0);return e}v(b,c);b.prototype.init= function(){this._initializePlayers()};b.prototype._wrapJWPlayerWithCLS=function(a,d,e){void 0===e&&(e=0);return a.parentNode?(d=this._createGenericCLSWrapper(.5625*a.offsetWidth,d,e),a.parentNode.insertBefore(d,a),d.appendChild(a),d):null};b.prototype._createGenericCLSWrapper=function(a,d,e){var f=document.createElement("div");f.id="cls-video-container-".concat(d);f.className="adthrive";f.style.minHeight="".concat(a+e,"px");return f};b.prototype._getTitleHeight=function(a){a.innerText="Title";a.style.visibility= "hidden";document.body.appendChild(a);var d=window.getComputedStyle(a),e=parseInt(d.height,10),f=parseInt(d.marginTop,10);d=parseInt(d.marginBottom,10);document.body.removeChild(a);return Math.min(e+d+f,50)};b.prototype._initializePlayers=function(){var a=document.querySelectorAll(this._IN_POST_SELECTOR);a.length&&this._initializeRelatedPlayers(a);this._shouldRunAutoplayPlayers()&&this._determineAutoplayPlayers()};b.prototype._createStationaryRelatedPlayer=function(a,d,e){var f="mobile"===this._device? [400,225]:[640,360],g=G.Video_In_Post_ClicktoPlay_SoundOn;d&&a.mediaOrPlaylistId&&(e="".concat(a.mediaOrPlaylistId,"_").concat(e),d=this._wrapJWPlayerWithCLS(d,e),this._playersAddedFromPlugin.push(a.mediaOrPlaylistId),d&&this._clsOptions.setInjectedVideoSlots({playerId:a.playerId,playerName:g,playerSize:f,element:d,type:"stationaryRelated"}))};b.prototype._createStickyRelatedPlayer=function(a,d){var e="mobile"===this._device?[400,225]:[640,360],f=G.Video_Individual_Autoplay_SOff;this._stickyRelatedOnPage= !0;this._videoConfig.mobileStickyPlayerOnPage="mobile"===this._device;if(d&&a.position&&a.mediaId){var g=document.createElement("div");d.insertAdjacentElement(a.position,g);d=document.createElement("h3");d.style.margin="10px 0";d=this._getTitleHeight(d);d=this._wrapJWPlayerWithCLS(g,a.mediaId,this._WRAPPER_BAR_HEIGHT+d);this._playersAddedFromPlugin.push(a.mediaId);d&&this._clsOptions.setInjectedVideoSlots({playlistId:a.playlistId,playerId:a.playerId,playerSize:e,playerName:f,element:g,type:"stickyRelated"})}}; b.prototype._createPlaylistPlayer=function(a,d){var e=a.playlistId,f="mobile"===this._device?G.Video_Coll_SOff_Smartphone:G.Video_Collapse_Autoplay_SoundOff,g="mobile"===this._device?[400,225]:[640,360];this._videoConfig.mobileStickyPlayerOnPage=!0;var h=document.createElement("div");d.insertAdjacentElement(a.position,h);d=this._wrapJWPlayerWithCLS(h,e,this._WRAPPER_BAR_HEIGHT);this._playersAddedFromPlugin.push("playlist-".concat(e));d&&this._clsOptions.setInjectedVideoSlots({playlistId:a.playlistId, playerId:a.playerId,playerSize:g,playerName:f,element:h,type:"stickyPlaylist"})};b.prototype._isVideoAllowedOnPage=function(){var a=this._clsOptions.disableAds;if(a&&a.video){var d="";a.reasons.has("video_tag")?d="video tag":a.reasons.has("video_plugin")?d="video plugin":a.reasons.has("video_page")&&(d="command queue");q.error(d?"ClsVideoInsertionMigrated":"ClsVideoInsertion","isVideoAllowedOnPage",Error("DBP: Disabled by publisher via ".concat(d||"other")));return!1}return this._clsOptions.videoDisabledFromPlugin? !1:!0};return b}(function(c){function b(a,d){var e=c.call(this)||this;e._videoConfig=a;e._component=d;e._stickyRelatedOnPage=!1;e._relatedMediaIds=[];a=void 0;void 0===a&&(a=navigator.userAgent);a=/Windows NT|Macintosh/i.test(a);e._device=a?"desktop":"mobile";e._potentialPlayerMap=e.setPotentialPlayersMap();return e}v(b,c);b.prototype.setPotentialPlayersMap=function(){var a=this._videoConfig.players||[],d=this._filterPlayerMap();a=a.filter(function(e){return"stationaryRelated"===e.type&&e.enabled}); d.stationaryRelated=a;return this._potentialPlayerMap=d};b.prototype._filterPlayerMap=function(){var a=this,d=this._videoConfig.players,e={stickyRelated:[],stickyPlaylist:[],stationaryRelated:[]};return d&&d.length?d.filter(function(f){var g;return null===(g=f.devices)||void 0===g?void 0:g.includes(a._device)}).reduce(function(f,g){f[g.type]||(q.event(a._component,"constructor","Unknown Video Player Type detected",g.type),f[g.type]=[]);g.enabled&&f[g.type].push(g);return f},e):e};b.prototype._checkPlayerSelectorOnPage= function(a){var d=this;a=this._potentialPlayerMap[a].map(function(e){return{player:e,playerElement:d._getPlacementElement(e)}});return a.length?a[0]:{player:null,playerElement:null}};b.prototype._getOverrideElement=function(a,d,e){a&&d?(e=document.createElement("div"),d.insertAdjacentElement(a.position,e)):(d=this._checkPlayerSelectorOnPage("stickyPlaylist"),a=d.player,d=d.playerElement,a&&d&&(e=document.createElement("div"),d.insertAdjacentElement(a.position,e)));return e};b.prototype._shouldOverrideElement= function(a){a=a.getAttribute("override-embed");return"true"===a||"false"===a?"true"===a:this._videoConfig.relatedSettings?this._videoConfig.relatedSettings.overrideEmbedLocation:!1};b.prototype._checkPageSelector=function(a,d,e){void 0===e&&(e=[]);return a&&d&&0===e.length?("/"!==window.location.pathname&&q.event("VideoUtils","getPlacementElement",Error("PSNF: ".concat(a," does not exist on the page"))),!1):!0};b.prototype._getElementSelector=function(a,d,e){if(d&&d.length>e)return d[e];q.event("VideoUtils", "getPlacementElement",Error("ESNF: ".concat(a," does not exist on the page")));return null};b.prototype._getPlacementElement=function(a){var d=a.pageSelector,e=a.elementSelector;a=a.skip;var f=da(d),g=f.valid,h=f.elements;f=S(f,["valid","elements"]);var k=ca(e),l=k.valid,p=k.elements;k=S(k,["valid","elements"]);return""===d||g?l?this._checkPageSelector(d,g,h)?this._getElementSelector(e,p,a)||null:null:(q.error("VideoUtils","getPlacementElement",Error("".concat(e," is not a valid selector")),k),null): (q.error("VideoUtils","getPlacementElement",Error("".concat(d," is not a valid selector")),f),null)};b.prototype._getEmbeddedPlayerType=function(a){(a=a.getAttribute("data-player-type"))&&"default"!==a||(a=this._videoConfig.relatedSettings?this._videoConfig.relatedSettings.defaultPlayerType:"static");this._stickyRelatedOnPage&&(a="static");return a};b.prototype._getMediaId=function(a){return(a=a.getAttribute("data-video-id"))?(this._relatedMediaIds.push(a),a):!1};b.prototype._createRelatedPlayer= function(a,d,e,f){"collapse"===d?this._createCollapsePlayer(a,e):"static"===d&&this._createStaticPlayer(a,e,f)};b.prototype._createCollapsePlayer=function(a,d){var e=this._checkPlayerSelectorOnPage("stickyRelated"),f=e.player;e=e.playerElement;var g=f?f:this._potentialPlayerMap.stationaryRelated[0];g&&g.playerId?(this._shouldOverrideElement(d)&&(d=this._getOverrideElement(f,e,d)),d=document.querySelector("#cls-video-container-".concat(a," > div"))||d,this._createStickyRelatedPlayer(y(y({},g),{mediaId:a}), d)):q.error(this._component,"_createCollapsePlayer","No video player found")};b.prototype._createStaticPlayer=function(a,d,e){this._potentialPlayerMap.stationaryRelated.length&&this._potentialPlayerMap.stationaryRelated[0].playerId?this._createStationaryRelatedPlayer(y(y({},this._potentialPlayerMap.stationaryRelated[0]),{mediaOrPlaylistId:a}),d,e):q.error(this._component,"_createStaticPlayer","No video player found")};b.prototype._shouldRunAutoplayPlayers=function(){return this._isVideoAllowedOnPage()&& (this._potentialPlayerMap.stickyRelated.length||this._potentialPlayerMap.stickyPlaylist.length)?!0:!1};b.prototype._determineAutoplayPlayers=function(){var a=this._component,d="VideoManagerComponent"===a,e=this._config;if(this._stickyRelatedOnPage)q.event(a,"stickyRelatedOnPage",d&&{device:e&&e.context.device,isDesktop:this._device}||{});else{var f=this._checkPlayerSelectorOnPage("stickyPlaylist"),g=f.player;f=f.playerElement;g&&g.playerId&&g.playlistId&&f?this._createPlaylistPlayer(g,f):q.event(a, "noStickyPlaylist",d&&{vendor:"none",device:e&&e.context.device,isDesktop:this._device}||{})}};b.prototype._initializeRelatedPlayers=function(a){for(var d=new Map,e=0;e