Excel has several kinds of charts that can be used to make a graphical representation of any set of data and in this tutorial, we will employ the concepts of Pie and Bar charts and learn how to create bar of pie chart using Excel.
Generally, a chart can be said to be a graphical visualization of data, in which the data is represented by symbols, such as bars, lines, or slices in bar charts, line charts, or pie charts respectively.
Charts help users to view the results of raw data and better understand the hidden information in a complex dataset which ordinarily would not be obvious when viewed in raw form.
Based on its mode of application, its representation of data may be interpreted differently.
For example, when a chart is used in form of a diagram or graph to organize and represent a set of numerical or qualitative data, it is known as a Data chart.
Similarly, nautical charts or aeronautical charts are maps adorned with extra information for some specific purposes in both the space and marine fields.
Charts are applied in different industries (such as in the musical, medical, engineering, etc.) and are often used to ease the understanding of the relationships between parameters present in a dataset.
Certain types of charts are more useful for presenting a given dataset than others.
A pie chart for example can be converted to a Bar of Pie Chart to give details to contributions of smaller categories or slices in the column chart.
On the other hand, data that represent numbers that change over a period of time might be best shown as a line chart.
Let’s start by reviewing what a Pie and Bar chart is, then, we will explore how to create and format a bar of Pie chart in Excel.
Here’s How to Create Bar of Pie Chart in Excel:
Pie Chart
A Pie chart is used to visualize data in a circular format.
Each pie is further divided into slices which represent a proportion of the whole (Pie).
Pie charts are usually expressed in percentages and all data sets sum up to 100%.
An example of a simple Pie chart is shown below.
To create a Pie Chart, the following steps should be followed.
Step 1: Generate your dataset or you can import if already created.
Step 2: Select the data range. You will discover that the selected range is highlighted.
Step 3: Click on the Insert tab
Step 4: Navigate to the chart grouping and click on the pie icon. A drop drop-down box is displayed from which you can readily select your preferred Pie option.
Result:
Bar Chart
A Bar chart is a graphical tool used to make comparisons between a set of data.
It uses rectangular bars to represent the data where the length of the rectangular bars is proportional to the value of the data.
An example of a simple Bar chart is shown below.
To create a bar chart:
Step 1: Generate your dataset or you can import if already created.
Step 2: Select the data range. You will discover that the selected range is highlighted.
Step 3: Click on the Insert tab
Step 4: Navigate to the chart grouping and click on the bar icon. A drop drop-down box is displayed from which you can readily select your preferred bar option.
Result:
What is Bar of a Pie Chart
This is a type of pie chart with a lot of small slices representing a proportion or value of a dataset in the whole (Pie) that is aggregated into column charts (Bars).
Pie charts are useful in displaying data having a small number of categories.
However, if you have to use a pie chart with a lot of pieces of data, The Bar of Pie chart provides a way to add additional categories to the pie chart without generating a pie chart that will be too complex to read or visualize.
In this tutorial, we will learn how to create a Bar of a pie chart just like the sample shown below.
Why Use a Bar of Pie Chart
- The Bar of a Pie chart allows for more categories of data to be included and visualized.
- Percentages are calculated and displayed automatically as data labels, so there is no need of calculating individual portion size yourself.
- Visualization of each data set is at a glance.
- Important aspects of your pie chart can be highlighted using a Bar of Pie chart.
Creating a Bar of Pie Chart in Excel
Before we commence the steps, it is important to ensure that your dataset is ready.
The table below shows the number of items sold in a supermarket over a given period.
Our task today is to use the data presented to create a Bar of Pie chart.
Step 1: Highlight the entire range
Step 2: Click on the Insert tab,
Step 3: Navigate to the Chart grouping and click on the Insert Pie or Doughnut Chart icon. A drop-down box of Pie options is displayed
Step 4: Select the Bar of a Pie icon under the 2D pie category
This creates the combination as shown below.
To make it bigger or smaller, all you have to do is drag any part of the highlighted edge and drag inward or outward respectively.
Delete the ’legend’ at the bottom if it is not needed.
For our example, because we need our visual presentation to be clear and concise, we will delete the legend.
What you see is an unorganized data set represented in a Bar of Pie chart with different slices set arbitrarily by default based on the order.
If we don’t want that, we can format the Bar of the Pie chart based on our preference.
Formatting a Bar of Pie Chart
This is generally your style of customization. Whatever and however you want your Bar of a Pie chart to look like depends entirely on you.
Step 1: Right-click in the chart area. An option box is displayed.
Step 2: Select the Format Plot Area option.
A Format Plot Area box pops up. This format option allows you easily customize the chart Area.
To Format the Data Series,
Step 3: Select your data within the chart by clicking on any of your slices of interest. You will notice that everything is highlighted.
A Format Data Series box pops up.
Step 3: Navigate to Format Data Series at the top right-hand corner.
Note: Make sure that the series option is highlighted.
You will notice that there are different options available for you to split your Bar of a Pie chart.
The data can be split as thus:
Option 1: By Position
Option 2: By value
Option 3: By Percentage Value
Option 4: By customs.
All you have to do is to click on the drop-down button to select your preferred split options.
Splitting by Position
This option allows you to specify the number of positions you want to move to the column bar.
You can select as many positions as you want to be stacked on a particular slice.
Splitting by Value
This option allows you to specify the maximum values you want on the pie chart and the values less than the specified value will be moved to the stack column.
You can select the minimum values you specifically want to stack on the column bar.
Splitting by Percentage Value
This option allows you to specify the percentage proportion that needs to be retained in the pie chart and other values which do not fall under that category will be stacked on the column bar.
Splitting by Custom
This option allows you to select specific slices you want to move between the pie chart and the column bar.
You can choose your preferred slices either from the stack to the pie chart and vice versa. The first plot represents the pie chart while the second plot represents the column bar.
In this tutorial, we will be using the ‘Position’ option and the number of slices in the second plot to be 11. What this does is to stack the last 11 slices in the column chart of our Bar of a pie chart.
You can make other adjustments to either the pie chart or the column bar to get the look you desire.
Amongst the options you can adjust are as follows:
OPTION 1: ADJUST THE PLOT SIZE.
The pie chart or the column bar can be adjusted to look exactly the way we want it to look. This option allows you to specify the size of the pie chart relative to the column chart. You can either decide to make the pie chart appear bigger than the column bar or vice versa.
OPTION 2: ADJUST THE GAP WIDTH.
The gap width is the distance between the pie chart and the column chart. This option can be used to create some distance between the pie chart and the column bar.
If both figures appear clumsy, visualization may not be effective.
OPTION 3: ADJUSTING THE PIE EXPLOSION.
This option allows you to separate the slice you are stacking to the column bar from the pie chart itself. If you decide not to explode the slice out from the portion of the pie, you should leave the option at 0%. All you have to do is click on the portion of the pie you want to Explode and adjust the value you want to Explode it from the ‘series option’ menu.
How to Add Data Labels
Labeling allows you to see the actual percentage (if you want to represent your labels in percentages) of each portion or category relative to the whole (Pie). Adding data labels aids data visualization.
To add and format data labels, the following steps should be followed.
Step 1: Click anywhere on the blank area of the Bar of a Pie chart. You will see three icons appear on the chart.
Step 2: click on the plus sign which represents the chart elements settings.
When you click on the + sign, three options on the chart element will appear as follows:
- Chart title
- Data labels
- Legend
Step 3: Click the ‘data labels’ box to check it. This will display the values representing each category in the chart.
Step 4: To change the display from value to percentage, click on the arrow next to the data labels option.
Step 5: Follow the list of options from the dropdown box on the data labels and click on more options.
Step 6: you should see the Excel label options in your Excel sidebar. Make sure the label option is highlighted.
Step 7: Under the label option category, you will find a list of checkboxes to let you add whatever labels you want to add.
Step 8. To add percentages instead of value, you should make sure the percentage option in the label option category is checked. Uncheck the ‘series name’ and ‘value’.
Step 9: while still on the label options in your Excel sidebar, click on the label position category to customize the data labels. Here you can decide the position or alignment of the data labels. For example, if you check ‘outside end’ on the checklist option, the data label will appear outside the pie chart.
Step 10: You can click and drag on the highlighted slice percentage value to position it anywhere on the chart with leader lines to show where it is originating from.
Formatting Text in Charts
There are available options to format text in charts. The ‘chart title’, the ‘legend’, and ‘data labels’ can be formatted according to your preference.
To format text in charts, take the following steps:
Step 1: Click on the text to format
Step 2: Go to font tools under the home tab and format text based on your preference.
Formatting Chart Background
Data visualization is as good as chart representation.
To format a chart, navigate to the Format tab and click on it.
Then, select your preferred background from the displayed chart style group and it will automatically change to your preference.
Converting a Pie Chart to a Bar of Pie Chart
There are instances where your data set would be an already created pie chart and you will be required to convert it to a bar of a Pie chart.
In such scenarios, you could follow the following steps:
Step 1: Click anywhere on the Pie chart
Step 2: At the top of the menu bar, you will see the ‘chart tools’ displayed
Step 3: Under this ‘chart tool’, click on the ‘chart design’ tab.
Step 4: Click on the ‘change chart type’
Step 5: A dialog box will appear with a list of shapes
Step 6: Click on the pie chart on the list of items displayed in the dialog box
Step 7: Select the bar of the Pie icon.
Step 8: Follow already outlined steps to format and customize your Bar of a Pie chart.
Step 9. Click ok
This converts your Pie chart to a Bar of pie chart
Conclusion
In this tutorial, we have been able to demonstrate how to create a Bar of a Pie chart in Excel from the data set generated from the start.
We also learned how to customize and format the Bar of a Pie chart to our preference.
In addition, we showed how an existing pie chart can be converted to a Bar of a Pie chart to aid a better visualization of data.
