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.

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,