How to Create a Professional Plan vs. Actual Chart in Excel

In the realm of data reporting and performance analysis, comparing planned targets against actual results is a fundamental requirement. While standard bar charts can display this data, they often lack the immediate visual impact needed for quick decision-making. A “Plan vs. Actual” chart—often visualized as a “thermometer” or “bullet” style chart—offers a much more intuitive solution.

This visualization technique allows managers and analysts to instantly see if a metric has met its goal, exceeded it, or fallen short, all within a compact space. By overlaying the “Actual” data on top of the “Plan” data, you create a cleaner, more professional report. Below is a comprehensive, step-by-step guide to mastering this chart type in Excel, ensuring your reports stand out with precision and clarity.

Step 1: Data Preparation and Selection

The foundation of any great chart is clean, structured data. Before visualizing, ensure your dataset is organized with clear headers. For this example, we will look at a simple comparison of planned sales versus actual performance across different regions or categories.

To begin, highlight the data range you wish to visualize. In our scenario, we are selecting the range A2:C6, which contains the categories, the planned figures, and the actual results.

Selecting the data range A2:C6 in Excel spreadsheet

Step 2: Initializing the Bar Chart

Once your data is selected, you need to generate the base chart. We will start with a standard Clustered Bar Chart, which places the bars side-by-side. This serves as the canvas we will later modify to create the overlay effect.

Navigate to the Insert tab on the ribbon, click on Charts, and select the 2-D Bar option, specifically the Clustered Bar.

Inserting a 2-D Clustered Bar Chart in Excel

Step 3: Formatting the “Plan” Series (Background)

The core concept of this chart is to make the “Plan” bar wider and sit in the background, while the “Actual” bar becomes narrower and sits in the foreground.

First, click on any of the bars representing the “Plan” (or Target) series in your chart. Right-click on it and select Format Data Series… from the context menu. This will open a side panel on the right of your Excel window where you can adjust specific series parameters.

Right-clicking the Plan series to access Format Data Series

To maximize the visibility of the background bar, we need to adjust the Gap Width. In the Format Data Series pane, under Series Options, change the Gap Width to 100%. This makes the bars wider, creating a substantial backdrop for our actual data.

Animation showing the process of formatting the data series in Excel

After adjusting the gap width, your chart will look denser, with the “Plan” bars taking up more vertical space.

Result of the chart after setting Gap Width to 100 percent

Step 4: The Secondary Axis Trick (Foreground)

Now comes the crucial technical step that defines this chart type. We need to move the “Actual” data to a different axis so it can overlap the “Plan” data rather than sitting next to it.

Right-click on the “Actual” data series (the other set of bars) and select Format Data Series… again.

Right-clicking the Actual series to format it

In the Format Data Series pane, look for the Series Options. You will see an option to plot the series on the Primary Axis or Secondary Axis. Select Secondary Axis.

Immediately, you will see the bars overlap. However, to create the “bar-in-bar” visual, the “Actual” bar must be thinner than the “Plan” bar. Adjust the Gap Width for this series to 300%. This significant increase narrows the bar, revealing the underlying “Plan” bar behind it.

Setting the Actual series to Secondary Axis with 300 percent Gap Width

Step 5: Cleaning and Polishing the Chart

At this stage, the chart is functional but likely cluttered with duplicate axes or unnecessary elements. For a professional look, you should simplify the design.

Since both bars share the same scale logic, you might want to remove the secondary axis labels (the numbers at the top) if they are redundant, or ensure both axes are synchronized. Delete gridlines or legends that do not add value to ensure the data stands out.

Removing unnecessary elements to clean up the chart interface

Step 6: Finalization

Finally, give your chart a descriptive title and verify the colors. A common standard is using a neutral color (like light grey) for the “Plan” and a bold, contrasting color (like blue or green) for the “Actual” to highlight performance.

The final completed Plan vs Actual chart with proper labeling

Conclusion

Mastering the “Plan vs. Actual” chart is an essential skill for any Excel user involved in reporting or dashboard creation. Unlike standard charts that force the viewer’s eye to jump back and forth between bars to compare lengths, this overlapping technique provides an instant cognitive understanding of performance gaps.

By utilizing the Secondary Axis and manipulating Gap Widths, you transform basic data into actionable insights. This method is highly adaptable and can be applied to various metrics, from sales targets to project timelines.

For those looking to deepen their data analysis skills, understanding the nuances of Excel’s charting engine is the first step toward becoming a reporting expert. Continuous practice with these advanced formatting options will significantly enhance the quality of your professional presentations.

References:

• Microsoft Excel Support: Presenting data in a chart.
• Excel Charting Best Practices: Data Visualization Guidelines.