How To Split A Stacked Chart In Excel

How to create AND split a stacked chart in Excel

Amongst the many charts available in Excel, some of the most popular are column charts, and the main variants being clustered and stacked. We’ll look at how to split a stacked chart in Excel, and to do this let’s start by creating a basic column chart.

See Excel courses near me.

Creating a column chart

In this example we are looking at regional sales data.

Sample sales data

Let’s create a clustered column chart of the above data.

Step 1 Select the range of data (as above, which will be A1:B4).

Step 2 Now select chart type, and “Clustered Column” from Charts options on the Insert ribbon.

a basic clustered column in Excel

Step 3 The following chart is now created.

regional sales in an Excel clustered column

This is all well and good we can compare regional sales performance, but it’s not so easy to compare how well each region performed against total sales. That’s where stacked column charts come into their own, let’s see how to build one.

Creating a stacked column chart

Step 1 Simply select the above chart then choose “Change Chart Type” from the Design ribbon.

change an Excel chart tyoe

Step 2 In the “Change Chart Type” dialogue box just choose the “Stacked Column” option as below.

changing an Excel chart into clustered chart

We now have a single stacked column chart for regional sales.

Excel stacked chart for sales regions

How to split a stacked chart in Excel

Now supposing we want to compare North & South as a stacked column against West and East as a stacked column. This can be achieved by splitting the above stacked chart.

Step 1 We need to change the layout of our data. So in this case we are going to select the two cells containing the “West” and “East” sales figures, then move them one column to the right. See below.

preparing Excel data for split stacked chart

Step 2 Now select the new range of data, in this case A1:C4, and as before when creating our original column chart but this time selecting the “stacked column” option. We end up with the following.

how to split a stacked chart in Excel

We now have our regional sales in a split stacked column chart.

By learning how to split a stacked chart in Excel you can now visualise data in a new and useful way.

More Excel chart related information:

How do you add a piechart and a bar / column chart on one worksheet?
Read more

How do I create a Floating Column Chart in Excel?
Read more

Excel 2013: 3 new ways to customise your charts

Present your data in a column chart

Using the Total cell style in Excel

Trying to review a spreadsheet with unformatted data can be a real eye sore, but by using the Total cell style in Excel you can quickly give your data more context.

An example of using the Total cell style in Excel

Below we have a list of sales figures for several sales reps in a travel company. In just a few steps by applying the Total style helps the “Total” column of sales figures stand out.

Total cell style in Excel
Using a couple of Excel styles can make all the difference

1. Select the range of cells you wish to format. Goto the Home tab, then the Styles group. Then click on the “More” button the more button in Excel to expand the group. (for Excel 2013 & 2010 click on Cell Styles button, see the second screenshot below)

Using the Total cell style in Excel 2007
How to select Total option from style section
Using the Total cell style in Excel 2010
Selecting Cell Styles in Excel 2013 and 2010

2. Now within the Style options, under the section, “Titles and Headings” just select the Total option. The selected range of cells will now display the Total formatting.

In the example above, we also repeated these two steps for the row with “John”, and with one further twist, also included an additional style option under “Themed Cell Styles” which was to select a colour.

Tip: If you are experimenting with different styles you may wish to remove a cell style, to do this simply select the range of cells you wish to “reset”, then go to the Style group (within the Home tab) and select Normal from the “Normal, Bad, Good, Neutral” section.

how to remove a style in Excel
To remove a style, just hit the Normal button

What we have covered here is a simple demonstration of how using styles can lead to more engaging and professional-looking spreadsheets. You can even create your own custom cell styles which may contain multiple formatting options and can be a real time saver when dealing with similar spreadsheets.

Found this useful? Learn more about Excel cell styles:-

  • How To Use The Cell Styles Functionality In Excel 2010. Read more
  • How do you group a selection of styles on a workbook? Read more
  • A comprehensive guide to applying, creating and removing cell styles