Easily Compare Your Actual and Target KPI / Metrics Values with Effective Actual vs Target Chart Excel (Free Download)
NEW: Quickly Convert your KPIs and Metrics into Powerful Dashboard Reports with 1-Minute Excel Dashboard (No Excel Skills Required). Find out how other managers take advantage of this fill-in-the blank approach.. Learn More Here
Actual vs Target Chart Excel – Free Step-by-Step Tutorial and Templates:
Comparing actual values against predefined targets is a very common requirement in creating business charts and dashboard reports. Every manager is responsible for its own budgets, goals and objectives and of course targets to be met.
There are countless ways and approaches to be used to visualize the comparison between two values. Sometimes individual charts can be used for each value. In other cases, the two values can be displayed on a single chart.
These two ways are useful when we need to not only compare values, but also track the trends of these values overtime. For, example simple line chart or column chart in Excel can be used for this purpose.
On the other hand, sometimes we need to compare values at a single point in time. For example, we can compare last month actual revenue vs last month target sales. In addition, we might want to do that for more than one metrics or multiple key performance indicators (KPIs).
Now we can use two different approaches:
A. We can create a report with multiple charts such as one chart for each KPI / metric.
B. We can build a single chart and display multiple metrics. With just a little simple tricks we’ll create an effective Actual vs Target chart for comparison report and analysis with Excel.
In this example we are using a single chart to represent 7 different KPIs on one Excel chart and here is the data used for this example:
- The first column lists the names of the KPIs we need to report.
- Next, we use the second column to enter the actual numbers.
- Finally we use the third column to enter the target values for each KPI.
Now we’ll create a simple column chart in Excel by selecting all cells in the range shown in the data figure above. Here is what the initial Excel chart will look like:
Now we can select any of the target columns and change the chart type for the target values from column chart to XY (Scatter) chart type.
Next we can double click any of the markers we see and change the marker style from default type to horizontal line.
Next we can double click the actual value columns and use the options tab to change the overlap value from 0 to 100 which creates the following chart:
You can download this Excel chart here
We have created the chart and finally we can make some quick visual changes like formatting, colors and size to match our reporting needs.