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.
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.
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 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.
- 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 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.
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.
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
- Reverse Legend Order of Stacked Bar Chart in Excel
- How to Create Bar Chart with Error Bars in Excel
- How to Sort Bar Chart in Descending Order in Excel
- Excel Bar Graph Color with Conditional Formatting
- How to Add Horizontal Line to Bar Chart in Excel
- How to Add Vertical Line to Excel Bar Chart
- How to Create Bar Chart with Target Line in Excel
- Excel Bar Chart with Line Overlay
<< Go Back to How to Edit Bar Graph in Excel | Excel Bar Chart | Excel Charts | Learn Excel