Create a Column Chart with Multiple Targets and Highlights in Excel

In one of our posts, we created a column chart with multiple targets. What if we need to highlight the columns that have achieved the set targets? This blog addresses that.

Consider a sample data of departments and a measure with their respective target values:

Multiple Targets and Highlights sample data

Our goal is to create a column chart with target lines for each of these values & to highlight only those columns where the target is achieved.

Multiple Targets and Highlights final chart
Multiple Targets and Highlights

Let’s get started.

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Step 01:

Let’s create a table for this data by selecting all the data and CTRL + T.

Multiple Targets and Highlights select data

Step 02:

Select all the data and insert a clustered column chart.

Multiple Targets and Highlights clustered column

This creates a default column chart with two series:

Multiple Targets and Highlights(a)

Step 03:

Right-click on the Target series, and select “Change Series Chart Type” to a Line with Markers as shown:

Multiple Targets and Highlights change series types

This creates a chart that 

Multiple Targets and Highlights change type

is a combination of columns and a line with markers:

Multiple Targets and Highlights with markers

Step 04:

To modify the line to a bar, right-click on the line, go to Format Data series, and make the following modifications:

i. Under Fill & Line, edit the Line option to no line:

Multiple Targets and Highlights fill & line

ii. Then in the Marker Option, choose a built-in option of a line and increase size accordingly, assign a color in the fill section as shown.

Multiple Targets and Highlights marker option

iii. Click on the columns, in the Format pane, and modify the Fill color according to your needs.

Multiple Targets and Highlights fill color

Step 05:

It’s time to bring in the highlights to this chart. For this, add a new “Above” series, which has the formula as shown below:

=IF([@ACTUAL]>=[@TARGET],[@ACTUAL],NA())

The above column contains only values that have reached the targets or more.

With this, the data will be:

Multiple Targets and Highlights new data

Step 06:

Now, we’ll add this new series to our chart. For this, follow the steps below:

i. Right-click on the chart, go to Select Data

Multiple Targets and Highlights select data

ii. Add the “Above” series as shown:

Multiple Targets and Highlights add series

Multiple Targets and Highlights series column

iii. Right-click on the chart again, go to Change Chart Type

Multiple Targets and Highlights change chart type

iv. Change the chart type of the Above series to a Clustered column

Multiple Targets and Highlights change to clustered column

v. From the format pane, choose the Above series, and under the series option, increase series overlap to 100%

Multiple Targets and Highlights series overlap

vi. From fill & line, change the fill color as needed

Multiple Targets and Highlights fill color

With this, the chart looks something like this:

Step 07:

Now, it is time for some final formatting

i. Modify the gridline color to make it less apparent by

(1) Choose Vertical (Value) Axis Major Gridlines from the format pane drop-down

Multiple Targets and Highlights  Axis major gridline

(2) format gridlines and change the color:

Multiple Targets and Highlights gridline color

ii. Add Data labels to the Actual columns (1) choose the “Actual” column from the drop-down, a “+” icon appears to the top right of the chart:

Multiple Targets and Highlights data labels to actual column

iii. Similarly, click on the lines and use the “+” to add data labels for the targets as well

Multiple Targets and Highlights data labels target column

iv. Add a suitable Chart title and format it using the Home ribbon

Multiple Targets and Highlights chart title

v. Click and position the legends on the top; delete the Actual legend

Multiple Targets and Highlights legend position

vi. Click on the chart and format the chart border as shown

Multiple Targets and Highlights chart border

With this, the column chart with highlighted above the target columns is ready!

Multiple Targets and Highlights final chart
Multiple Targets and Highlights

Check our 1-page, downloadable illustrative guide explaining all the steps for a quick reference.

If you have any feedback or suggestions, please post them in the comments below.

To get our FREE downloadable Illustrative Guide for about 32 unique Column Charts, enter your email here!

Leave a Reply

Your email address will not be published. Required fields are marked *