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.
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.
- 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.
- Next, go to Insert ➤ Insert Column or Bar Chart ➤ 2-D Stacked 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.
- 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.
- 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.
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.
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.
