Create an Overlapping Bar Chart in Excel

An overlapping bar chart is a powerful and specialized type of visualization that is utilized when the user needs to compare two distinct quantitative values that both correspond to the exact same categorical variable. Unlike standard clustered bar charts, which place bars side-by-side, the overlapping structure allows one series to partially or fully obscure the other, providing an immediate visual comparison of magnitude and difference within a single category space. This technique is particularly effective in scenarios where one metric acts as a target or baseline (the background bar) and the other represents the actual outcome (the foreground bar), facilitating quick insight into variances.

This comprehensive tutorial serves as a detailed guide, providing a precise, step-by-step methodology for generating this complex visualization within Excel. We will focus on creating a visually effective overlapping bar chart that effectively displays the comparison between predicted scores versus actual points scored across various professional basketball players. This example demonstrates how to manipulate bar chart series settings to achieve the required overlapping effect and enhance the clarity of your data presentation.

The final result of this process, which clearly highlights the relationship between the two datasets, will resemble the following detailed graphic:

We will now commence the technical process, beginning with the organization and structuring of the necessary raw data.

Preparation: Setting Up Your Data in Excel

The foundation of any effective visualization is correctly structured input data. For an overlapping bar chart, it is critical to organize your categories and associated values into adjacent columns within the spreadsheet. This setup ensures that when the chart is initially created, the corresponding data points for comparison (Predicted and Actual) are linked correctly under the categorical labels (Player Name).

For this specific demonstration, we utilize a dataset comparing predicted points versus actual performance for a selection of athletes. The dataset requires three distinct columns: the categorical variable (Player), the first quantitative series (Predicted Points), and the second quantitative series (Actual Points). The order of the quantitative series is important, as it often dictates which series will naturally be drawn first, setting the stage for the overlapping effect we aim to achieve.

Please structure your Excel sheet to reflect the following organization, ensuring that the labels are consistent and the numerical values are entered accurately:

This simple table arrangement allows Excel to correctly interpret the selected range as a series of comparison points when generating the initial chart visualization. Once this data structure is in place, we can move forward to inserting the initial chart object.

Initial Chart Creation: Inserting the Clustered Bar Chart

The creation process begins with selecting the desired data range and inserting a standard two-dimensional clustered bar chart. Although our ultimate goal is an overlapping chart, the clustered bar format provides the necessary foundational structure upon which the specialized settings will be applied.

To execute this step, first ensure the entire dataset, including the header row (A1) down to the last data point (C7), is highlighted. Once selected, navigate to the Insert tab located on the primary Excel ribbon. Within the Charts section of the ribbon, locate and click the icon corresponding to the Clustered Bar chart type. This option is typically found under the bar chart grouping (often depicted as horizontal bars).

Upon selection, Excel will immediately generate a default visualization. This initial chart will display the Predicted and Actual points as distinct sets of bars, grouped side-by-side for each player category. This clustered arrangement confirms that the data has been read correctly, but it does not yet achieve the desired overlapping visual effect. The resulting initial graph will look like this:

The next step is crucial for transforming this standard visualization into the specialized overlapping format by modifying the chart type and axis assignments.

Advanced Configuration: Transforming to a Combo Chart

To introduce the concept of overlap, we must modify the chart type from a simple clustered bar to a composite or Combo Chart. A Combo Chart allows for the simultaneous use of two or more chart types (though in this case, we keep both as bars) and, more importantly, facilitates the use of a secondary vertical axis, which is the technical mechanism required for forcing the series to align spatially rather than being separated.

Begin by right-clicking directly on any bar within the currently displayed chart. From the contextual menu that appears, select the option labeled Change Series Chart Type. This action will open the comprehensive Change Chart Type dialog box, which allows for granular control over the data series visualization.

Within the dialog box, you must navigate to the list of chart templates on the left-hand side and select Combo. This selection changes the display to show configurations for multiple series, giving you the ability to assign separate chart types and axis locations for each data series (Actual and Predicted). While both series should remain set to the Clustered Bar type for a horizontal overlapping chart, the critical action occurs in the subsequent step of defining axis assignment.

Crucial Adjustment: Utilizing the Secondary Axis

The technical key to creating the overlap lies in forcing the two data series to plot against different axes, specifically by assigning one series to the Primary Axis and the other to the Secondary Axis. Even though both axes represent the same unit (points scored), assigning separate axes tells Excel to treat the plot locations independently but overlay them on the same physical space. This is a common workaround used in Excel for dual-axis comparisons and forcing overlaps.

Within the Change Chart Type window, examine the options beneath the chart preview. You will see columns for the Actual series and the Predicted series. We need to assign the secondary axis to one of these series. For this demonstration, we will assign the Predicted series to the Secondary Axis. Locate the checkbox under the Secondary Axis column corresponding to the Predicted series name and ensure it is checked.

Once you confirm this setting by clicking OK, the visualization immediately updates. Since both series are now mapped onto the same categorical space but plotted relative to their respective (though identical) axis scales, the bars for predicted and actual points will now align and overlap directly for each player. This successfully transitions the chart from clustered to overlapped, producing a visualization where bars share the same vertical track, although their width still needs adjustment for better distinction.

Enhancing Visibility: Fine-Tuning the Bar Width

While the previous step established the geometric overlap, the default bar widths often result in one series completely obscuring the other, or making the background series barely visible. To create a clear, meaningful overlapping bar chart, the front-most series must be intentionally made thinner, or conversely, the back-most series must be made wider, allowing both data points to be simultaneously legible. We aim to adjust the width of the background bars (which are based on the Actual values in this setup) so that the foreground bars (Predicted values) are fully visible within them.

The primary control for bar thickness relative to the plot area is the Gap Width setting. Gap Width defines the amount of space between categories. A smaller Gap Width results in thicker bars, whereas a larger Gap Width results in thinner bars. To make the bars for the Actual values significantly wider, we must reduce their associated Gap Width percentage.

To access this setting, double-click on any bar representing the Actual values (the orange bars in the example). This action opens the Format Data Series panel on the right side of your screen. Locate the options pertaining to series width, specifically the Gap Width field. The default value is typically 150% or higher.

Adjust the value for the Gap Width to a much lower percentage, such as 60%. This reduction dramatically increases the thickness of the Actual bars, pushing them forward into the category space:

By making this adjustment to the Gap Width of the background series, the bars become broad enough to fully encompass the Predicted series bars, ensuring that both data points for each player are visible side-by-side or layered, providing the desired visual comparison:

Refinement and Aesthetics: Customizing the Final Visual

Data visualization effectiveness is heavily reliant on aesthetics. Even after the technical requirements for overlapping are met, the chart must be customized to ensure maximum clarity and readability for the audience. Customization involves optimizing colors, ensuring appropriate labels, adding a clear chart title, and refining axis labels.

Specifically, consider modifying the colors of the bars. A common best practice is to use a muted or lighter color for the background series (Actual) and a brighter, contrasting color for the foreground series (Predicted) to draw attention to the comparison point. Additionally, ensure the chart title is descriptive and concise, clearly stating what the viewer is seeing (e.g., “Predicted vs. Actual Points Scored”). Furthermore, the legend must be positioned clearly and accurately reflect the series names.

If you wish to remove the secondary axis scale that was created in Step 3, you can click on that vertical axis, press Delete, and the chart structure will remain intact while simplifying the visual field. The final customized chart should look professional and immediately convey the relationship between the two key metrics, as shown in our fully refined example:

Conclusion and Next Steps

Mastering the creation of an overlapping bar chart in Excel demonstrates an advanced understanding of chart customization and data representation techniques. By leveraging the Combo Chart type, employing the secondary axis assignment to force positional alignment, and precisely controlling the Gap Width property, you can create highly effective visualizations for comparative data analysis.

This approach is widely applicable beyond sports statistics, useful for tracking budget variance, performance metrics against targets, or comparing baseline vs. experimental results across any categorized dataset. For those interested in exploring further specialized bar chart types that enhance data storytelling, consider diving into related visualizations:

• How to Create a Diverging Stacked Bar Chart in Excel

By applying these detailed steps, you ensure your data is not only accurately plotted but also presented in a manner that maximizes insight and analytical efficiency.