How to Add Grand Total to Bar Chart in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we insert various bar or column charts into an excel worksheet. The charts or graphs help users to better understand and analyze certain datasets. However, adding a grand total to charts may seem a complicated process. This article will show you the step-by-step procedures to Add Grand Total to Bar Chart in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


Step by Step Procedures to Add Grand Total to Bar Chart in Excel

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains 4 different products and their sales in 3 quarters. Here, we need to determine the total sales of each product. In this article, we’ll show how to add that grand total to the bar chart which we’ll insert later on. Therefore, go through the below steps carefully to perform the task.

add grand total to bar chart excel


STEP 1: Input Spacing and Grand Total Columns to Dataset

  • In our first step, we’ll input 2 extra columns into our dataset.
  • The first column is Spacing as shown below.
  • In this column, we’ll insert the same big number (10,000) for every product compared to other sales amounts.

Input Spacing and Grand Total Columns to Dataset

  • Then, in the Grand Total column, we’ll add the sales to find the total.
  • For that purpose, select cell G5.
  • Type the formula:
=SUM(C5:E5)
  • Press Enter.
  • After that, use AutoFill to get the sales summation of other products.

Read More: How to Use Excel Formula to Calculate Percentage of Grand Total


STEP 2: Insert Bar Chart in Excel

  • Now, select the range B4:F8.

Insert Bar Chart in Excel

  • Next, go to Insert ➤ Insert Column or Bar Chart ➤ 2-D Stacked Bar Chart.

insert 2-D bar chart

  • As a result, you’ll get the bar chart.
  • Click the chart.
  • Subsequently, select Chart Design ➤ Switch Row/Column.

  • Thus, it’ll return the following chart.

Read More: How to Make Subtotal and Grand Total in Excel (4 Methods)


STEP 3: Modify Bar Chart to Add Grand Total

  • We’ll modify the chart to add the grand total in this step.
  • In this regard, right-click on the Quarter 1 sales portion in the chart.
  • Then, press Add Data Labels in the pop-out Context Menu.
  • Repeat the steps for other portions in the chart.

Modify Bar Chart to Add Grand Total

  • Hence, you’ll see the data labels associated with the respective potion in the chart.
  • See the below figure for a better understanding.

  • Afterward, right-click on the Spacing data label (10,000).
  • Press Format Data Labels in the Context Menu.

  • Consequently, you’ll see the Format Data Labels pane on the right side.
  • There, choose Inside Base as the Label Position.
  • Next, check the box for Value From Cells in the Label Options.

  • A dialog box will appear.
  • Choose the Grand Total range under the Select Data Label Range.

choose grand total range

  • Press OK.
  • As a result, you’ll get the chart as demonstrated below.

  • Now, right-click on the Spacing portion.
  • Subsequently, choose No Fill in the Fill drop-down.

  • Moreover, delete the y-axis lines.
  • Then, delete the Spacing legend.

Read More: How to Show Grand Total with Secondary Axis in Pivot Chart


STEP 4: Change Spacing Column Values

  • Our next task is to change the Spacing column values.
  • Type 0 for every product in the Spacing column.

Change Spacing Column Values

Read More: How to Add Grand Total to Stacked Column Pivot Chart


Final Output

  • At last, we’ll carry out some final operations to make our chart more presentable.
  • Select the Grand Total data labels.
  • Modify them as your requirement to make them pop out in the chart.
  • Finally, our bar chart with the grand total is ready to be displayed.
  • Look at the following chart which is our final output.

Final Output


Conclusion

Henceforth, you will be able to Add Grand Total to Bar Chart 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.


Related Articles

Aung Shine
Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo