Learning to Create Overlapping Bar Charts in Microsoft Excel

An overlapping bar chart is a highly effective, advanced form of data visualization designed specifically to compare two distinct quantitative values or data series corresponding to the exact same category on a single axis. This sophisticated technique is invaluable when striving to illustrate the relationship, discrepancy, or degree of overlap between primary and secondary metrics, providing immediate, powerful visual contrast.

This comprehensive tutorial provides a detailed, step-by-step walkthrough detailing how to expertly utilize Microsoft Excel to generate this specialized chart type. We will demonstrate this process by visualizing the predicted versus the actual points scored by various professional basketball players, offering a crystal-clear visual comparison between initial expectation and final performance.

The goal is to transform raw numerical data into an insightful graphical representation. The final professional output we aim to achieve will look exactly like the chart displayed below, showcasing the immense power of effective visualization in modern data analysis:

Let’s begin the essential process of structuring our data and initiating the chart creation.

Step 1: Preparing and Structuring the Essential Dataset

The foundation of any robust and accurate chart is meticulously structured data. Before we can proceed with the graphical representation, we must organize all relevant metrics into a clean, columnar format within our Excel worksheet. For this specific demonstration, we require three clearly defined columns: Player Name (Categories), Predicted Points (Series 1), and Actual Points (Series 2). Ensure that your data is clearly labeled in the header row for Excel to correctly interpret the series.

Specifically, we will input the following dataset, which meticulously details the predicted versus actual scoring performance for six athletes. Accurate and precise data entry is absolutely critical for the success and reliability of the subsequent visualization steps.

Once the data has been correctly entered into the sheet, typically spanning a contiguous range like A1 through C7 (inclusive of the necessary header row), you are fully prepared to initiate the chart insertion phase.

Step 2: Initial Insertion of the Standard Clustered Bar Chart

The next step involves initiating the chart creation process using Excel’s standard charting tools. Start by carefully highlighting the entire cell range containing your structured data, specifically the range A1:C7. This comprehensive selection ensures that Excel recognizes both the two distinct data series and the corresponding category labels (the Player Names).

Navigate to the Insert tab located on the top navigation ribbon interface. Within the designated Charts group, locate and click on the icon representing the Clustered Bar chart type. This immediate action generates a preliminary horizontal bar chart, treating both the Predicted and Actual point values as separate, adjacent series.

Upon initial insertion, a standard horizontal bar chart will appear on your screen, visually separating the two data metrics side-by-side. While this preliminary chart is fundamentally functional, it does not yet achieve the desired overlapping effect needed for direct, simultaneous comparison. We must now modify its structure.

Step 3: Converting to a Combo Chart and Utilizing the Secondary Axis

Achieving the required overlapping visualization demands a crucial modification: we must change the chart type and assign one of the data series to a secondary plotting axis. This structural maneuver is critical, as it forces both series to occupy the exact same graphical space.

Begin this conversion process by right-clicking directly on any bar within the chart area. From the context menu that appears immediately, select the option labeled Change Series Chart Type. This action will open the comprehensive customization dialogue box.

In the subsequent dialogue window, ensure you select the Combo chart category from the list displayed on the left pane. The pivotal step here is synchronizing the axes: locate the series name Predicted in the table at the bottom of the window, and check the box associated with the Secondary Axis column. This action compels Excel to plot the Predicted series on a separate, hidden axis, resulting in the bars occupying the identical graphical space as the Primary Axis series (Actual).

After confirming the changes by clicking OK, the bars representing the Predicted and Actual values will now be successfully superimposed, or overlapping, for each basketball player category. This constitutes a major structural achievement, though the immediate visual clarity often needs further refinement, as the current configuration usually results in the inner bar being partially or fully obscured by the outer bar:

Step 4: Enhancing Visibility by Manipulating Gap Width

The previous step successfully overlaid the bars, but they still maintain the default thickness, leading to the common issue where the smaller series is unintentionally hidden by the larger one. To achieve the desired effect—where both bars are simultaneously and clearly visible—we must strategically increase the apparent width of the background series, which in our case is the Actual values, by modifying the crucial Gap Width property.

To begin formatting this series, double-click directly on any of the orange bars (representing the Actual points). This intuitive action will launch the comprehensive Format Data Series panel on the right side of your Excel screen. Under the Series Options tab, navigate to the Gap Width setting. This specific setting controls the spacing between categories; therefore, by significantly reducing this percentage, you effectively increase the physical width of the bars within that category.

For a clean, high-impact overlapping visualization suitable for direct comparison, we recommend changing the Gap Width value to a much lower percentage, typically around 60% or less:

Reducing the Gap Width dramatically increases the physical size of the bars plotted on the Primary Axis. By making the outer series (Actual) much wider, the inner series (Predicted) becomes fully visible and sits cleanly within the bounds of the outer bar. This technique creates a highly effective visualization that clearly shows the relative difference and alignment between the two metrics for every player category:

Step 5: Applying Final Customization and Professional Styling

The chart is now structurally complete and fully overlapping, achieving the core visualization goal. The final stage involves applying critical customization to vastly enhance readability, aesthetic appeal, and overall impact. Effective chart styling ensures that your audience can swiftly interpret the visualized data analysis and focus immediately on the key insights, rather than being distracted by Excel’s default colors or visual clutter.

Consider the following essential adjustments to optimize your finished visualization for professional presentation:

• Adjusting the transparency or the fill color of the outer bar (Actual) to ensure the inner bar (Predicted) stands out distinctly and achieves the desired layered effect.
• Removing unnecessary chart elements, such as default gridlines or redundant axis labels, to effectively declutter the visual field and improve focus.
• Adding a descriptive and concise chart title, along with precise data labels for specific values, ensuring numerical accuracy is immediately apparent.
• Applying professional, consistent color palettes or corporate themes to standardize the look and feel of the graph across multiple reports.

By applying these thoughtful design choices, you can achieve a polished, professional result that maximizes the communicative impact of your specialized overlapping bar chart:

Additional Resources for Advanced Excel Visualization

Mastering specialized visualization techniques within Microsoft Excel can significantly elevate your data presentation and reporting skills. For those looking to explore other complex chart types or advanced operational methods, the following resources provide valuable guidance on extending your charting capabilities beyond the basics:

• How to Create a Diverging Stacked Bar Chart in Excel
• Explore techniques for creating stacked column charts for comparative analysis using different data structures.