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.
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)*100Here, $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$12Here, $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, S and then go to the 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$4The 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
