How to Create Bar of Pie Chart in Excel?

Pie charts in Excel provide a great way to visualize categorical data as part of a whole.

By one look at a pie chart, one can tell how much a category contributes to the entire group.

A bar of pie chart lets us go one step further and helps us visualize pie charts that are a little more complex.

In this tutorial, we will look at what a Bar of the pie chart is, how it helps visualize data and how to create one in Excel.

What is a Bar of Pie Chart?

A pie chart is the best option when you want to visualize portions of a small number of categories (around 2-5).

However, as the number of categories increases, the pie chart starts becoming too complex to read.

A ‘Bar of pie chart’ can be quite useful in such cases, as it lets you move the smaller slices of the pie to a separate stacked bar chart.

This simplifies the pie, while the viewer can still see what the smaller sizes comprise of.

For example, in the chart shown below, the larger slices are visible in the main pie, while the smaller ones are grouped together into a single pie and then projected onto a bar chart, where you can still see the individual portions.

Bar of pie chart in Excel

Benefits of Using a Bar of Pie Chart in Excel

By separating the smaller slices from the main pie, the Bar of pie chart lets you handle more categories in a pie chart, thereby simplifying a complex pie.

You can also use this as a tool to highlight the important portions of your pie chart.

The Bar of Pie chart is quite flexible, in that you can adjust the number of slices that you want to move from the main pie to the bar.

Besides this, the Bar of pie chart in Excel calculates and displays percentages of each category automatically as data labels, so you don’t need to worry about calculating the portion sizes yourself.

How to Create a Bar of Pie Chart

Let’s say you have the following list of employees along with their total sales. 

Dataset for creating bar of pie chart

To visualize how much each employee has contributed to the total company sales, we can use a pie chart.

Pie chart of total sales

However, as you can see from the above image, the pie chart would not look very intuitive and it would be difficult to perceive who the largest contributors were.

As such, a Bar of pie chart would be a more appropriate visualization tool in this case. Let us see how we can use a ‘Bar of pie‘ chart to visualize our data:

  1. Select the range of cells containing the data (cells A1:B7 in our case)
  2. From the Insert tab, select the drop down arrow next to ‘Insert Pie or Doughnut Chart’. You should find this in the ‘Charts’ group.
Insert Pie or Doughnut Chart
  1. From the dropdown menu that appears, select the Bar of Pie option (under the 2-D Pie category).
select the Bar of Pie option
  1. This will display a Bar of Pie chart that represents your selected data.
Bar of Pie chart

Note: By default, the chart displays three data points in the stacked bar, unless you specify the number.

You will notice that the chart does not look neat and organized.

For example, the chart shows Paula Lee’s portion within the pie, although her portion is quite small.

Moreover, it takes John Smith’s portion out which is quite sufficiently large.

Intuitively, it would make more sense to have the smaller portions displayed in the stacked bar, leaving the larger portions in the pie.

Besides this, the chart does not have labels to show the percentage contribution of each employee.

Therefore, we need to customize the chart further to suit our requirements.

Also read: How to Overlay Graphs in Excel

How to Customize a Bar of Pie Chart

Excel lets us add our own customizations to the Bar of Pie chart.

For example, it lets us specify how we want the portions to get split between the pie and the stacked bar.

It also lets us specify whether we want to display data labels, what data labels we want to be displayed as well as what formatting and styling we want to apply to the labels.

Besides this, it lets us edit the chart legend, the title formatting, and much more.

Let us try to customize our chart to bring it more in line with our requirements.

Rearranging the Splitting of Portions

The first thing we want to do is split the portions by percentage.

In other words, we want the larger portions to remain in the pie and move the smaller portions to the stacked bar.

For this follow the steps outlined below:

  1. Right-click on any slice of the pie chart.
  2. Select ‘Format Data Series’ from the context menu that appears.
Click on Format Data Series
  1. This will open the ‘Format Data Series’ sidebar to the right of the Excel window.
  2. Make sure the ‘Series Options’ tab is selected, as shown below:
Select the series option in the tab
  1. You will see a list of Series options below the ‘Series Options’ category.
  2. Click on the ‘Split by Series’ dropdown. You will see 4 options to split your portions:
Select custom from the drop down
  1. Position – This option lets you specify the number of positions that you want to move to the stacked chart.
  2. Value – This option lets you specify the maximum values that will be displayed in the pie chart. Values less than this will be moved to the stacked bar.
  3. Percentage value  – This option lets you specify the minimum percentage for portions to be moved to the stacked chart. In other words, if a slice has a smaller percentage in the pie than what is specified, then the  slice needs to be moved to the stacked bar.
  4. Custom – This option lets you hand-pick the slices that you want to move between the stacked bar and the pie chart.

Since we want to split the portions so that the smaller portions move to the stacked bar, we can select the ‘Percentage Value’ option, and then enter the value 10% as input for the ‘Values less than’ field. 

Specify the percentage value

This will make sure that the smaller three portions (those that contribute 10% or less to the whole) are moved to the stacked bar chart, while the larger three portions are displayed in the pie chart:

Smaller portions less than 10% are stacked

How to Split Series by ‘Custom’ Settings

If you would rather hand-pick and select which portions you want to display in the pie and which ones you want to move to the stacked bar, you could select the ‘Custom’ option instead, in step 6.

Once you select that option you can move your slices between the two charts. Double click on any slice (either in the pie or in the stacked bar) that you want to move.

You will see a new option appear under the Series Options.

This option, which says ‘Point Belongs to’ lets you select where you want the currently selected slice to go. 

Point Belongs to option in Excel

Let’s say you double-clicked on Paula Lee’s portion.

You want to move it to the bar chart, so click on the dropdown next to ‘Point belongs to’ and select ‘Second plot’.

Select Second plot from the drop down

Similarly, to move a portion from the stacked bar to the pie, simply select the ‘First plot’ option.

Select First Plot option

Do this for every point that you want to move between the two plots.

Note: Here, the ‘First plot’ refers to the pie chart, while the ‘Second plot’ refers to the stacked bar chart.

Bar of Pie Chart

Additional Bar of Pie Settings

Under the Series Options of step 6, you will also find some additional settings specific to ‘Bar of pie’ charts.

These are:

  • Pie explosion – This setting lets you detach the slices of the pie. You can move the slider up or down to increase or decrease the spaces between each slice.
  • Gap Width – This setting lets you set the distance between the pie and the stacked bar chart.
  • Second Plot Size – This setting lets you specify the size of the stacked bar relative to the pie.
Additional options for Pie chart in Excel

Adding Data Labels

To be able to see the actual percentage of each portion/ category, adding data labels would be quite helpful.

To add and format data labels to portions in your Bar of pie chart, follow the steps below:

  1. Click anywhere on the blank area of the chart.
  2. You will see three icons appear to the right side of the chart, as shown below:
Options to add data labels in pie chart
  1. Click on the plus icon (‘+’) , which represents the Chart Elements settings.
  2. Check the box next to ‘Data Labels’.
Select Data lables option
  1. This will display the values corresponding to each category in the chart. But we want to see percentages, not values. So hover over the Data Labels option.
  2. You should see an arrow appear next to it. Click on this arrow.
Select the position of the data labels
  1. This will show a list of options for customizing your data labels. Click on ‘More Options’.
More Data labels options
  1. You should now see label options in your Excel sidebar.
  2. Under the Label Contains category, you will find a list of checkboxes to let you add whatever labels you want to add to your pie portions.
  3. We only want to see the percentages. So make sure the ‘Percentage’ option is checked and the ‘Series Name’ and ‘Value’ options are unchecked.
  4. Under the Label Position category, you can also customize the position of your data labels.

Formatting the Text

You can also format the text in your chart, for example, the chart title, the data labels, and the Legend.

This requires you to only select the text and then format it as you need to from the Font tools under the Home tab.

Also read: How to Create Bubble Chart in Excel

How to Convert a Pie Chart to a Bar of Pie Chart

If you’ve already created a Pie chart and now want to convert it to a Bar of pie chart instead, here are the steps you can follow:

  1. Click anywhere on the chart
  2. You will see a new menu item displayed in the main menu that says ‘Chart Tools’.
  3. Under Chart Tools, select the ‘Design’ tab.
Click on Design tab
  1. Click on the ‘Change Chart Type’ button (which is in the ‘Type’ group).
Click on Change Chart Type
  1. This will display the ‘Change Chart Type’ dialog box.
  2. Make sure that the ‘All Charts’ tab is selected and the ‘Pie’ category is also selected.
Select Pie chart
  1. At the top of the ‘All Charts’ tab, you will see icons for different pie chart types.
  2. Select the ‘Bar of pie’ icon.
Select the ‘Bar of pie’ icon.
  1. Click OK.

Your chart should now get changed to a Bar of Pie chart.

You can continue to customize the chart as we explained in the previous sections of this tutorial.

In this tutorial, we showed you how to create a ‘Bar of pie’ chart and how to customize it according to your requirements.

We also showed you how you can convert a regular Pie chart to a Bar of pie chart if you need to.

We hope this was useful.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment