How to Create Stacked Bar Chart for Multiple Series in Excel

We can store various information in MS Excel. And then, perform many different operations on them. Sometimes, we have to present a certain result or survey outputs using Excel Charts. This is especially effective for viewers to analyze and grasp the whole concept clearly. In Excel, it’s easy to insert Stacked Bar Charts by selecting some data range. But, things can get complicated if you’ve to do it for multiple series. In this article, we’ll show you the step-by-step procedures to create a Stacked Bar Chart for Multiple Series in Excel.

Table of Contents hide

Download Practice Workbook

Step by Step Procedures to Create Stacked Bar Chart for Multiple Series in Excel

STEP 1: Input Data

STEP 2: Rearrange Data

STEP 3: Create Stacked Bar Chart for Multiple Series

Final Output

Conclusion

Download Practice Workbook

Download the following workbook to practice by yourself.

Stacked Bar Chart.xlsx

Step by Step Procedures to Create Stacked Bar Chart for Multiple Series in Excel

Creating a Stacked Bar Chart for Multiple Series helps us to understand certain datasets very clearly. They also offer a comparative view of our data values. So, this bar chart can benefit a lot of business companies. They will be able to know which area of a project needs to be improved. Therefore, go through the below steps carefully to create a Stacked Bar Chart for Multiple Series in Excel.

STEP 1: Input Data

To create any chart, we need to have some data values. In this example, we’ll input a dataset about 4 products and their sales permanence in 2 quarters. Here, we’ll have the Actual amount and also the Target amount.

First, create the headers for the products and the sales amounts in different quarters.
Then, type the Product names.
After that, insert the precise Sales amounts in the respective cells.

STEP 2: Rearrange Data

In this step, we’ll edit and modify the dataset. But, we’ll keep the original data. Now, follow the below process to perform the task.

Firstly, select the range B4:F8.
Next, press the Ctrl and C keys at the same time to copy it.
Subsequently, click cell B10.
Afterward, apply the Paste Link feature from the Paste Options.
You’ll get this option from the context menu (mouse right-click).
Thus, whenever you update the original dataset, the following data values will get updated automatically.

Consequently, insert 2 blank rows as shown below.
To do that, right-click on the row headers and press Insert.

Now, move the Q2 Actual and Q2 Target values to the following blank cells.
See the below picture for a better understanding.

Lastly, insert a blank row under the dataset header.
Also, enter another blank row at the end of the dataset.
The following image demonstrates the process.

Similar Readings

STEP 3: Create Stacked Bar Chart for Multiple Series

Finally, we have to create the Stacked Bar Chart for Multiple Series. Hence, learn the below process to carry out the operation.

Select the range B10:F23 at first.

Then, go to the Insert tab.
Click the drop-down icon shown in the below image from the Chart section.
After that, choose the Stacked Bar option.

As a result, you’ll get a Stacked Bar chart.
The following figure is the result.

However, we want to remove the width present between the bars of each product.
For that purpose, click any series in the chart and press the Ctrl and 1 keys simultaneously.
Consequently, you’ll get the Format Data Point pane.
Type 0 in the Gap Width box.

The Product legend is unnecessary here.
So, click the Product legend and press Delete.
Moreover, to make the chart more vibrant, click the legends present in the chart under the X-axis.
Choose desired colors.
Thus, it’ll return the following chart.

Final Output

At last, delete the Y-axis labels as they are not required in the bar charts.
Hence, your desired Stacked Bar chart is ready to demonstrate.
The topmost bar is for Oven, followed by Fridge, TV, and AC.

Conclusion

Henceforth, you will be able to create a Stacked Bar Chart for Multiple Series in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.