Open In App

Creating Actual Vs Target Chart In Excel With Floating Markers

Improve
Improve
Like Article
Like
Save
Share
Report

Excel is known to be a powerful tool for data visualization, comparison, storage, and management which can handle large amounts of data. We know that data can be visualized and compared using different kinds of plots and charts in Excel such as line charts, bar charts, etc. The tool is used to get insights from data using formulas and functions. It is primarily used by Accounting professionals to get insights into financial data, but it can be used by anyone for different purposes. Let’s see how to create an Actual vs Target chart using floating markers:

For demonstration, let’s use the below table values, which have actual sales and target sales for different years,

Dataset

 

Actual vs Target Chart in Excel – Target Values as Bars

An Actual vs. Target chart is a tool that is used to compare two sets of data: the actual values achieved and the target values to be reached. This type of chart is widely used in situations, such as assessing sales performance, tracking revenue targets, and many more. There are various kinds of chats in Excel for data visualization, It makes it easy to identify areas where goals are exceeded or met, providing valuable insights for strategic planning and tracking for goals.

How to Create a Regular Chart in Excel (Column Chart)

Step 1: Select the Data

Selecting-data

 

Step 2: Click on the ‘Insert’ tab and Select the chart to be created

Selecting-chart

 

Step 3: Preview Column Chart

Column-chart-created

 

Step 4: Right-click and Select Format Data Series

Selecting Format Data Series

 

Step 5: Select “Secondary Axis” in the Plot Series options

Format Data Series

 

Step 6: Preview Chart

Plotting series on the secondary axis

 

Step 7: Select any Target Value bars, right-click, and select ‘Format Data Series

Step 8: Go to Format Data Series and Decrease or Increase the Gap Width

In the ‘Format Data Series’ pane, lower the Gap Width value, you can increase the width of the bars to make it wider than normal or can make them narrow as per your need, Here we’ve made the Gap width value higher to decrease the width of the bars

adjusting the gap width

 

Step 9: Press the Delete key after clicking on the numbers of the secondary axis on the right side of the chart

This will give your chart a clean look. 

Deleting the secondary axis

 

Output

Now your chart is ready. After deleting the secondary axis, you will get the below as the final output.

 final-output

 

Actual vs Target Chart in Excel – Target Values as Marker Lines

To get started, first, create a Column chart by replicating the same steps from Step 1 to Step 3 we’ve seen in the previous example, Then follow the Steps mentioned below:

Step 1: Right-click on any bar and select the ‘change series chart type’ option.

Selecting-change-series-chart-type

 

Step 2: In the change chart dialogue box, select the Combo category and then select the drop-down of target sales and choose the line marker chart.

Choose-line-marker-chart

 

Step 5: Press OK. The chart will appear like the one given below.

Chart-created

 

Step 6: Right-click the line and select the ‘Format Data Series’ option.

Selecting-format-data-series-option

 

Step 7: From the dialogue box, under the Line section, select the ‘No line’ option.

Selecting-no-line-option

 

Step 8: Under the Marker section, select the Built-in option and choose the marker ‘-‘(dash) type, and set the size according to the choice.

Marker-options

Output

Output

The actual sales are shown as blue bars and the target sales are shown as orange markers.

These two charts covered in this tutorial are the ones that I prefer when I have to display actual and target values in an Excel chart. I advise you to read more of our articles on Excel if you want to understand more. I appreciate your time and look forward to hearing from you soon!

FAQs on Actual Vs Target Chart in Excel

Q1: What is the Actual and Target Chart in Excel?

Answer: 

The Actual Vs Target chart is a visual representation of the comparison between the actual values( which represent the real data or results that have been obtained) and target values(which represent the desired or expected values).

It helps you to analyze the performance of a particular data. It has data in two sets: the actual values achieved and the target values set as goals. 

Q2: How can I add data labels showing the variance between actual and target values?

Answer: 

To add data labels displaying the variance between actual and target values, Follow the below steps:

Step 1: Calculate the variance in a separate column and then use that column as the data label for the chart.

Step 2: Right-click on the data series, select “Add Data Labels “, and then format the labels to show the variance values. 

Q3: How customization of the Actual and Target chart can be done?

Answer:

There are many customization options present for charts in excel. 

Right click on different elemnets of the chart to access formatting options, or you can use the Chart design and formta options available in the Excel Ribbon.

You can modify the Chart title, axis labels, legend, colors, fonts, and othe rformatting elements on the basis of your needs.

Q4: Can Excel update the data Actual vs target chart automatically?

Answer: 

Yes, Excel will automatically update your data in the actual vs target chart if your values are based on the formulas or linked to cells. If you make any changes in the data, you can see the latest changes in the chart.

Q5: How Actual vs Target Chart can be created in Excel?

Answer:

Follow the below steps to create an Actual vs Target Chart in Excel:

Step 1:  Select the entire data set

Step 2: Go to the Insert tab 

Step 3: Click on the ‘ Clustered Column Chart’ icon in the Charts group.

Step 4: Click on any of the bars in the chart inserted in the worksheet for Target values 

Step 5: Right-click on the selected bar, and click on the ‘Change Series Chart Type’ from the menu.

Step 6: Now Select the Line Chart with Markers. This will change the target values bars into a line with markers.



Last Updated : 06 Dec, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads