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 a Grand Total to a Bar Chart in Excel.


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

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.


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 Change Bar Chart Color Based on Category in Excel


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 Color Bar Chart by Category in Excel


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: Excel Add Line to Bar 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


Download Practice Workbook

Download the following workbook to practice by yourself.


Conclusion

Henceforth, you will be able to Add the Grand Total to the 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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to How to Edit Bar Graph in Excel | Excel Bar Chart | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo