Partners,,,, russianxnxx

Encircle Data Points in a Line Chart 1

Here is a quick trick to encircle specific data points in your line chart


Let’s start with this data

Encircle Data Points in a Line Chart 2





--> 15 years of Random Data




Before we jump on to creating a line chart, lets setup some workings that will help us encircle the data points in the line chart


Creating a Dummy

Encircle Data Points in a Line Chart 3










In the column next to Random Data, we are going to create a Dummy

  1. Select the entire column
  2. Choose Data Validation from the Data Tab
  3. Set up a list with 1,0
  4. This will allow you to create a drop down with 1 and 0 (zero)

The reason : Now think of this dummy as toggle (1,0)

  1. Selecting 0 would take off the value from the chart
  2. Selecting 1 would encircle the data point in the chart


Let’s attach the Dummy with the Data

Encircle Data Points in a Line Chart 4










This simple formula will connect the Dummy with the Random Data

=IF(Dummy = 0, NA(), Dummy * Random Data)

The logic is simple : If the Dummy is 0 (or empty) then we want N/A# else we multiply the dummy with the data. Since 1 multiplied by the data point will result in the same number so the dummy works like a toggle


Time for a Line Chart

Encircle Data Points in a Line Chart 5

Create a simple line chart with years on the horizontal axis and random metric plotted as a line


Attaching Encircled Points (data) into our Line Chart

Encircle Data Points in a Line Chart 6

Here are the steps

  1. Add the Encircled Points Data to the Line Chart
  2. Change the chart type of Encircled Points Series from a Line Chart to a Line Chart with Markers
  3. Now remove the line (Show No outline) and retain only the Markers

If you have got till here you know where I am headed! The only thing remaining is customizing the markers to Circles


Customizing the Markers – Dots into Circles

Encircle Data Points in a Line Chart 7 --> Create a circle from Shapes in the Insert Tab. Note a few things

  1. The size of the circle created will be replicated exactly to the Chart. So create the exact size that you want in your chart. A smaller one!
  2. The outline color can be any (mine is red), but the inside color has to be none (i.e. No Fill)

Now that we have the circle, all we have to do is to replace the dots with the circles

Encircle Data Points in a Line Chart 8

  1. Copy the circle (that we have created)
  2. Click on the Encircled Points Data Series and Paste it (use the shortcut Ctrl V)
  3. The markers will now appear as circles
  4. You can also add data labels if you want

Now when you change your Dummy toggle, the encircling effect will take place! 😎

    ⬇️ Pop in your Name & Email to get the file!


    More Interesting Charts

    1. Customize highlighting any data series
    2. Create a Map Chart to Plot Cities on a Map
    3. Place legends at the end of the line chart


    Topics that I write about...