Creative Column Chart that Includes Totals in Excel

In previous tutorial, we learned how to plot a clustered chart in excel. It was an Sales in Months chart of different cities. The chart that we created didn’t have total’s column. Because comparing total data with individual data is not reasonable.

However we are interested in comparing totals. One obvious solution is to plot a different chart for total only. But thats just not creative. In this article, we will learn how to creatively include totals column in chart. We will show cities columns within Total’s Column. How? Let’s see.
Create Chart With Total Columns Containing Part Columns
Here I have the data of sales done in different months in different months of year in two cities. There can be more, but Here I am just taking 2.

Now let’s make the chart in by following these steps:

1. 1. Add a helping column ‘base Line’ and assign value 0 for each month.

1. 1. Now select entire data. Go to Insert Tab? Charts? Column and Bar Chart? Clustered Column Chart.

You will see the normal clustered column chart. The totals chart is tallest in each group since it is sum of other columns.

1. 1. Select the totals column and right click. Click on ‘Change Series Chart Type’. Change Chart type dialog will open. Here you can see all series names, Delhi, Mumbai, Total and Base Line. Change chart type of Total and Base Line to line chart.

1. 1. Now select the Total line. Click on the plus sign of upper right corner of graph. Check Up/Down Bars option. You’ll see a black Bars connecting Total and Base Line nodes.

1. 1. Goto series option of total and reduce the gap width to 30%. We just want it to cover all other columns in chart.

1. 1. We don’t need the total and base line anymore. But we can’t delete it. Right click on Total line in chart and click on format data series. In Fill and Line, select No Line. Now the line has disappeared. Do the same for base line.

These black columns represent the total sales in each month. We can easily compare total sales of months but we have now lost the cities data. We want those columns too.

1. 1. Select the Down bars. Go To Format Down Bars and select gradient fill. Yes revive the artist in you. Ingredient stops keep only two stops. Choose the color you like.

1. 1. Still we can’t see the city bars. Go to Gradient stops and select the lower color. Increase the transparency to 100%.

1. 1. We have almost completed. To make it look like all sub columns reside in total columns, select the Down Bars and go to format down bars. Click on fill. In borders, select gradient line. Adjust it’s transparency to your requirement.

1. 1. Now, just remove the elements which are not required. Like, I don’t want horizontal lines, Total and base line legends. So I remove them.

1. 1. Add chart element that you want. I want to see the data labels of totals only. Right on the top center of totals and click on Add Data Label? Add Data Callouts.

1. 1. You can format the callouts to make it stand out. You can also click and drag the callouts to avoid overlapping.

It is done. Now you can compare total sale in each month and sales in different cities in each month, in one chart. You can download this chart template here.

By doing some extra formatting you can make this column chart stand out in the presentation.

So yeah, this how you can create a clustered column chart that shows totals and makes sense. We will make more creative charts in future if you want. Let me know what you think about this chart in the comments section below.

Download file:

Related Article:

Creative Column Chart that Includes Totals

Create Overlay Chart in Excel 2016

Perform Pareto Chart and Analysis in Excel

Perform Waterfall Chart in Excel

Excel Sparklines : The Tiny Charts in Cell

Speedometer (Gauge) Chart in Excel 2016

Popular Articles :

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.