Actual vs Target Chart in Excel: A Simple Step-by-Step
Written by Kasper Langmann
Mastering the art of creating an Actual vs Target chart in Excel can significantly enhance your data analysis and presentation skills. This chart type is a powerful tool for visualizing performance metrics, helping to compare actual results against set targets. In this guide, we will walk you through a simple step-by-step process to create an Actual vs Target chart in Excel.
Understanding the Actual vs Target Chart
The Actual vs Target chart, also known as a Variance chart, is a type of chart that displays both actual and target data in a single view. It is commonly used in business and project management to track performance and identify gaps between the actual and expected outcomes.
This chart type is beneficial in various scenarios, such as sales performance tracking, budgeting, project progress monitoring, and more. By visually representing the difference between the actual and target values, it allows for a quick and easy understanding of performance levels.
Excel does not have a built-in Actual vs Target chart type. However, with a little creativity and knowledge of Excel features, you can create one easily. The process involves creating a combination chart with a bar chart (for actual values) and a line chart (for target values).
Preparing Your Data
Before we dive into the process of creating the chart, it’s essential to prepare your data. For an Actual vs Target chart, you need at least two data series – one for the actual values and one for the target values. These data series should correspond to the same categories or time periods.
For example, if you are tracking monthly sales performance, you should have actual sales data and target sales data for each month. It’s also helpful to have your data sorted in ascending or descending order of categories or time periods for better visualization.
Once your data is ready, you can proceed to create the Actual vs Target chart. Here’s a step-by-step guide to help you through the process.
Creating an Actual vs Target Chart in Excel
Step 1: Insert a Combo Chart
Start by selecting your data, including the headers. Then, go to the ‘Insert’ tab on the Excel ribbon and click on the ‘Insert Combo Chart’ button in the ‘Charts’ group. This will open a dropdown menu with different combo chart types. Select the ‘Clustered Column – Line’ chart type.
This will insert a combo chart in your worksheet with two data series. The actual values will be represented by the columns, and the target values will be represented by the line.
Step 2: Customize the Chart
After inserting the chart, you might want to customize it to suit your preferences. You can change the chart title, add data labels, adjust the axis scales, and more. To do this, simply select the chart element you want to customize and use the ‘Format’ tab on the Excel ribbon.
For example, to change the chart title, select the chart title and type your preferred title in the formula bar. To add data labels, select the data series and click on the ‘Add Data Labels’ button in the ‘Labels’ group on the ‘Format’ tab.
Step 3: Format the Target Line
To make the target line stand out, you can format it differently from the actual bars. Select the target line, go to the ‘Format’ tab, and use the ‘Shape Outline’ dropdown to change the line color. You can also adjust the line weight and style to make it more prominent.
With these steps, you should have a basic Actual vs Target chart. However, you can further enhance the chart with advanced formatting options.
Advanced Formatting Options
Excel offers a plethora of formatting options to enhance your charts. For an Actual vs Target chart, you might want to consider the following options:
Adding a Trendline
A trendline can help visualize the overall trend of your actual values. To add a trendline, select the actual bars, go to the ‘Format’ tab, and click on the ‘Add Trendline’ button in the ‘Analysis’ group. You can choose from different trendline types, such as linear, exponential, and polynomial, depending on your data.
You can highlight the variance between the actual and target values using conditional formatting. To do this, you need to create a third data series for the variance and apply a color scale conditional formatting to it. This will color the variance bars based on their values, making it easy to spot high and low variances.
Adding a Secondary Axis
If your actual and target values have different scales, you might want to add a secondary axis for the target values. To do this, select the target line, go to the ‘Format’ tab, and click on the ‘Format Selection’ button in the ‘Current Selection’ group. Then, check the ‘Secondary Axis’ option in the ‘Series Options’ pane.
Creating an Actual vs Target chart in Excel might seem complex at first, but with practice, it becomes a straightforward process. This chart type is a powerful tool for visualizing performance metrics, helping you make informed decisions based on your data.