How to Easily Create a Bar of Pie Chart in Excel

A bar of pie chart is a specialized type of pie chart designed to enhance the visibility of minor data segments. It functions by combining the smallest proportional slices of the primary pie into a single, aggregated slice (often labeled “Other”). This aggregated slice is then exploded into a secondary, detailed bar chart, allowing analysts to distinguish between those smaller values clearly.

The primary benefit of employing this chart structure is the significant improvement in interpretability. While a standard pie chart struggles to accurately represent slices that occupy less than 5% of the total—often rendering them as nearly invisible slivers—the Bar of Pie chart provides an explicit, magnified view. This refinement makes it substantially easier to visualize and interpret the contribution of the smallest data slices without compromising the clarity of the major segments.

This comprehensive tutorial will guide you through a step-by-step example, illustrating precisely how to construct and customize this powerful visualization in Excel, resulting in a finished product similar to the example shown below:

Setting the Foundation: Data Preparation

We begin the process by organizing the raw data efficiently within the Excel spreadsheet. Accurate and well-structured data is the prerequisite for any meaningful visualization. For this example, we will utilize a dataset illustrating the total sales performance achieved by ten individual employees across a specific reporting period. This data arrangement should feature two distinct columns: one for the categorical labels (Employee Names) and one for the corresponding numerical values (Sales Totals).

It is crucial that the data selected for a Bar of Pie chart represents parts of a whole, as this chart type is inherently designed to display proportion. Ensure that all data points are positive numerical values, as negative values or zero values can lead to complications or misrepresentations in a standard proportional chart structure. This step ensures data integrity before visualization begins.

The following table illustrates the required format, detailing the sales figures for each employee. Note that the employees whose sales figures are lowest will eventually be grouped together in the secondary bar plot to provide clarity and focus on the lowest contributions to the total sum.

Once your data is entered exactly as shown, ensure that both the header rows and all data entries are included in the selection range before proceeding to the next phase. Highlighting the entire range, in this case, A1:B11, prepares the dataset for insertion into the charting engine.

Executing the Chart Insertion

With the dataset prepared, the next step involves generating the initial chart. The creation of the Bar of Pie visualization follows the standard procedure for inserting charts in Excel, with a specific selection required from the chart types menu. This ensures that Excel utilizes its built-in logic for segment aggregation correctly.

To initiate the chart creation, first, highlight the entire range of cells containing your data, including both the labels and the values (A1:B11). After selecting the data, navigate to the Insert tab located along the top ribbon interface of Excel. This tab houses all available visualization tools and chart templates.

Within the Charts section of the Insert ribbon, locate the icon representing Pie or Doughnut Charts. Clicking this icon will reveal a dropdown menu showcasing various proportional chart options. From this menu, select the option labeled Bar of Pie. This action instructs Excel to instantly render the visualization using its default grouping parameters.

Upon selection, Excel will automatically insert the chart onto your worksheet. Initially, this chart displays the main pie, representing the majority contributions, and an attached bar chart showing the details of the aggregated “Other” slice. Review the automatically generated visualization to understand which data points Excel has chosen to group together based on magnitude, which often defaults to grouping the four smallest slices.

Understanding Default Grouping Behavior

By default, Excel attempts to create a visually balanced chart by automatically identifying and grouping the smallest components that might otherwise be indistinguishable on the primary pie. In the visualization created in the previous step, Excel intelligently grouped the four smallest slices into a single aggregated segment within the main pie. This aggregated segment is then magnified and detailed in the adjacent bar chart for better visual accessibility.

While this default setting often works well, advanced analysis frequently requires precise control over which segments are grouped and how the data is split. For example, if your goal is not just visual balance but highlighting all categories that fall below a predetermined operational threshold, you must manually adjust the grouping criteria to match your analytical objectives.

The primary tool for managing this split is the Format Data Series panel. Customization is necessary to align the visualization with the specific narrative you intend to convey. To access these crucial settings, simply double-click on any element within the primary pie chart or the secondary bar chart. This action will open the Format Data Series pane on the right side of the Excel interface, providing robust options for modifying how the data is divided between the two plots.

Customizing the Split: Grouping by Position

One of the most straightforward methods for adjusting the chart split is defining the exact number of data points you wish to retain in the secondary bar plot. This method is based purely on the data’s ranking—identifying the smallest N values—regardless of the actual numerical value of the sales figures or their proportional contribution.

In the Format Data Series panel, navigate to the Series Options icon (the icon resembling three columns). Under the Split Series By dropdown menu, ensure that Position is selected. You will then see the option titled Values in second plot. This setting dictates precisely how many data points (slices) will be removed from the main pie and displayed in the bar chart.

Suppose the default setting grouped four values, but for better focus, we only want to concentrate on the two lowest-performing employees. We would change the number specified for Values in second plot to 2. This refinement immediately updates the chart, showing only the two smallest slices detailed in the bar chart, with the remaining eight slices visible in the main pie.

Upon applying this change, the resulting chart dynamically shifts its focus. The bar chart now exclusively highlights the two specific employees with the lowest sales totals, providing a sharp contrast and detailed view of the lowest performers in the dataset. This controlled use of the split position allows for highly targeted comparative analysis.

Customizing the Split: Grouping by Percentage Value

A statistically more robust method of splitting the series involves grouping all slices whose proportional contribution falls below a predetermined threshold percentage. This approach is highly effective when defining “small” based on a business metric, such as grouping all organizational units that contribute less than 10% to the total budget or revenue.

To implement percentage-based grouping, return to the Format Data Series panel. In the Split Series By dropdown menu, select the option Percentage Value. A field will appear allowing you to define the numerical percentage cutoff. All data points that constitute less than this specified percentage of the total dataset sum will be automatically moved to the secondary bar plot.

For example, we could choose to group together all slices in the pie that account for less than 10% of the total sales. Enter 10 into the corresponding input field (it often accepts the percentage without the symbol). This action allows Excel to handle the calculation automatically, ensuring that the grouping is mathematically defined rather than arbitrarily positional.

Excel will instantly calculate which slices meet this criterion and adjust the visualization accordingly. In our dataset, the total Sales values sum up to 116. Therefore, 10% of the total is 11.6. Any employee whose sales total is less than 11.6 will be automatically included in the bar chart aggregation. This method provides a dynamic and objective way to categorize minor data contributions, especially useful in enterprise reporting.

Advanced Formatting and Finalization

After defining the precise grouping mechanism, the final steps involve applying essential formatting elements to ensure the chart is professional and highly readable. The most critical element often overlooked is the addition of data labels, which provide immediate context to the viewer and eliminate the need for constant reference to the underlying data table.

To finalize the chart, click once on the chart area to select it. This action will cause several chart element icons to appear on the right side of the chart border. Click the tiny plus (+) sign, known as the Chart Elements menu. Within this menu, ensure that the checkbox next to Data Labels is selected. This action adds the corresponding value or percentage (depending on the default setting) directly to the pie chart slices and the bars, significantly enhancing clarity and reducing ambiguity in the data visualization.

For even greater refinement, you can select the Data Labels option and click the adjacent arrow to access further formatting options, such as changing the label format to display the category name, the value, or the percentage, or adjusting their position (e.g., inside end, outside end). Ensure that the chosen labels clearly distinguish between the employees and their sales totals in both the main pie and the bar plot, particularly ensuring that the aggregated “Other” slice in the main pie is clearly labeled with its total value.

Once the labels are applied and the chart title is updated to reflect the content accurately (e.g., “Employee Sales Performance Analysis”), the Bar of Pie chart is complete. This powerful visualization now clearly communicates the large components of the whole while simultaneously providing magnified detail on the smaller, often overlooked, contributions, fulfilling the intended purpose of this advanced chart type.

Other Splitting Methods Available

While grouping by Position and Percentage are the most frequently used methods, the Format Data Series panel offers two additional methods for splitting the data series, providing maximum flexibility for complex analysis scenarios. These options ensure that users can adapt the visualization to specialized reporting needs.

One alternative is Custom grouping, where you manually click and drag specific data points from the main pie chart into the secondary plot, or vice versa, based purely on visual or qualitative criteria rather than strict quantitative rules. This is particularly useful when specific categories need to be highlighted for non-numerical reasons, such as separating legacy products from new releases regardless of their current sales volume.

Another option is Value grouping, which allows the user to specify a raw value cutoff. For example, if you set the value to 15, any employee with sales less than 15 would be grouped into the bar chart automatically. This differs subtly from percentage grouping as it relies on the absolute measure rather than the proportional contribution to the total, making it ideal for standardizing comparisons across different datasets that share the same magnitude scale.

When selecting your splitting method, always consider your audience and the core message. Grouping by Percentage Value is generally preferred for technical reports as it is objective and scalable across varying data totals, whereas grouping by Position might be used for comparative analysis highlighting only the absolute lowest ranks regardless of size.

The following tutorials explain how to create other common visualizations in Excel:

• How to Create a Waterfall Chart
• Understanding and Using Treemaps
• Generating Dynamic Scatter Plots