Creative Column Chart that Includes Totals

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.
001
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.
002
Now let’s make the chart in by following these steps:

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

  3. Now select entire data. Go to Insert Tab? Charts? Column and Bar Chart? Clustered Column Chart.
  4. 004
    You will see the normal clustered column chart. The totals chart is tallest in each group since it is sum of other columns.
    005

  5. 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.
  6. 006

  7. 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.
  8. 007

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

  11. 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.
  12. 009
    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.

  13. 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.
  14. 0010

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

  17. 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.
  18. 0012

  19. 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.
  20. 0013

  21. 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.
  22. 0014

  23. You can format the callouts to make it stand out. You can also click and drag the callouts to avoid overlapping.
  24. 0015
    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.

    0016
    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.
    Related Article:

    Create Overlay Chart in Excel 2016
    https://www.exceltip.com/tips/how-to-create-a-pareto-chart-in-microsoft-excel.html>Pareto Chart and Analysis
    Waterfall Chart
    Excel Sparklines : The Tiny Charts in Cell
    Speedometer (Gauge) Chart in Excel 2016

    Popular Articles:
    The VLOOKUP Function in Excel
    COUNTIF in Excel 2016
    How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube