If you are looking for the procedures to change bar chart width based on data in Excel, then this article will serve this purpose. Normally, the width of the bars in the charts remains consistent with the changes in values, but if we can change their widths according to their values then it may be quite obvious. To know the detailed steps of doing this task, let’s start with the main article.

**Table of Contents**hide

## Excel Bar Chart Width Based on Data: 4 Steps

Here, we have the following dataset containing** 4** groups of students with different heights, and each group contains various numbers of students. Using the following steps we will try to plot the bars with different widths according to the number of students.

We have used *Microsoft Excel 365 *version for this article, you can use any other version according to your convenience.

__Step-01__: Arranging Values Using Formulas to Change Bar Chart Width Based on Data

To plot the bars, firstly we will need some values to be arranged in a definite way. To accommodate these values we have added a new dataset with **5 **columns and for labeling the bars we have added a new column ** Indicator **in the first dataset.

In the first cell of the **Number **column enter **0 **as we want to have the range of the **X-axis **from **0 **to **100**.

➤ Type the following formula in cell **B11**.

`=$C$4/SUM($C$4:$C$7)*100`

Here, **$C$4 **is the number of students in the **P **group, and **$C$4:$C$7 **is the range of the students in all of the **4** groups.

**SUM($C$4:$C$7) →**the**SUM**function will add the values in the range**$C$4:$C$7**.**Output → 210**

**$C$4/SUM($C$4:$C$7) →**becomes**20/210****Output → 0.0952380952**

**$C$4/SUM($C$4:$C$7)*100 →**becomes**0952380952*100****Output → 9.52380952**

➤ Press **ENTER **and drag down the **Fill Handle **tool to copy this result to cell **B12**.

After that, we will get the same value **9.52380952 **in the two cells **B11 **and **B12**.

➤ Apply the following formula in cells **B13 **and **B14**.

`=($C$5/SUM($C$4:$C$7)*100)+$B$12`

Here, **$C$5 **is the number of students in the **Q **group, and **$C$4:$C$7 **is the range of the students in all of the **4** groups. And **$B$12 **is the number in the previous cell.

**SUM($C$4:$C$7) →**the**SUM**function will add the values in the range**$C$4:$C$7**.**Output → 210**

**$C$5/SUM($C$4:$C$7) →**becomes**50/210****Output → 0.2380952380**

**$C$5/SUM($C$4:$C$7)*100 →**turns into**2380952380*100****Output → 23.80952380**

**($C$5/SUM($C$4:$C$7)*100)+$B$12 →**becomes**80952380+9.52380952****Output → 33.333333333**

Similarly, use the following formula for the two cells **B15 **and **B16**.

`=($C$6/SUM($C$4:$C$7)*100)+$B$14`

Finally, if you apply the similar formula above in cell **B17 **for the **S **group students, then you will get the value **100 **(which was the end point of our limit).

`=($C$7/SUM($C$4:$C$7)*100)+$B$16`

➤ Now, we will copy the number of students in the **P **group in the first two cells of the **P **column.

➤ Where the values end in the **P **column (*Row 11*), copy the number of students of the **Q **group in the **Q **column for the two cells starting from *Row 12*.

Similarly, **Copy** the number of students for the rest of the groups in the other two columns. Then we will get the following dataset.

**Read More: **How to Make a Percentage Bar Graph in Excel

__Step-02__: Insert Stacked Area Chart and Format Axis

Now, we will plot the chart using the second dataset of the following figure.

➤ Select the values of the four columns – ** P**,

**,**

*Q***,**

*R***and then go to the**

*S***Insert**Tab >>

**Charts**Group >>

**Insert Line or Area Chart**Dropdown >>

**Stacked Area**Option.

Afterward, we will get the following chart.

➤ **Right-click **on the **X-axis **and then select the **Select Data **option.

Then, you will have the **Select Data Source **dialog box.

➤ Select the **Edit **option on the **Horizontal (Category) Axis Labels**.

Afterward, the **Axis label range **dialog box will appear.

➤ Select the range of the **Number **column in the **Axis label range **box and then press **OK**.

Finally, you will be taken to the **Select Data Source **dialog box again.

➤ Press **OK **here.

➤ Again **Right-click **on the **X-axis **and select the **Format Axis **option.

On the **Format Axis **pane (it will appear on the right side of your Excel sheet) select the **Date axis **from the **Axis Type **option under the ** Axis Options**.

Then, you will notice that the shape of the chart will be changed into bars of different widths.

➤ Select the **X-axis **and press the **DELETE **key.

Similarly, delete the **Legend**, **Y-axis**;

And the **Gridlines**.

Finally, we will get the following bar chart and our next task will be adding labels to the bars.

**Read More: **How to Make a Bar Graph Comparing Two Sets of Data in Excel

__Step-03__: Using Formula to Create Labels of Chart

Now, we will create our custom labels for the bars in the **Indicator **column.

➤ Type the following formula in cell **E4**.

`=B4&"("&C4&" "&"students)"`

**The Ampersand operator** will join the value in cell **B4 **with bracket, space, the value of cell **C4, **and with the text *students*.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Finally, we will have all of the labels for the bars in the chart in the **Indicator **column.

**Read More: **How to Show Number and Percentage in Excel Bar Chart

__Step-04__: Adding Labels to Excel Bar Chart Width Based on Data

In this step, we will add our created labels to each of the bars of this chart.

➤ Go to the **Insert **Tab >> **Illustrations **Group >> **Shapes **Dropdown >> choose your desired shape.

➤ Here, we selected a **rectangular box**.

After that, a plus icon will appear and we will drag it to the right side and top down to create our desired **rectangular box** in the first bar.

➤ After entering the rectangular shape, type the following formula in the **Formula Bar**.

`=$E$4`

The value in cell **$E$4 **will be linked in the box.

Then, we will have the label in the first bar, but we can change some options to make it more visible.

➤ Go to the **Shape Format **Tab >> **Shape Fill **Dropdown >> **No Fill **option.

➤ To hide the outline of the box, go to the **Shape Format **Tab >> **Shape Outline **Dropdown >> **No Outline **Option.

We can also change the text color which will match the background color.

➤ Go to the **Shape Format **Tab >> **Text Fill **Dropdown >> choose your desired color.

➤ Here, we selected **White** color.

In this way, we have changed the format of the label of our first bar.

Similarly, add and change the format of the labels of the rest of the bar.

You can also change the chart title to ** “Variation of Heights for different Number of Students”**.

**Read More: **How to Sort Bar Chart Without Sorting Data in Excel

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in each sheet on the right side. Please do it by yourself.

**Download Practice Workbook**

## Conclusion

In this article, we tried to cover the steps to change bar chart width based on data in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

**Related Articles**

- How to Make a Bar Graph with Multiple Variables in Excel
- How to Make a Bar Graph in Excel with 2 Variables
- How to Make a Bar Graph in Excel with 3 Variables
- How to Make a Bar Graph in Excel with 4 Variables
- Excel Bar Chart Side by Side with Secondary Axis
- How to Show Difference Between Two Series in Excel Bar Chart