Add slope line to XY charts

editor
April 22, 2020
Articles
0 Comments

Slope line is very useful for spotting which values have changed from two sets. You can add a slope line to XY chart (scatter plot) using simple techniques. In this post, learn how to add them.

Steps for adding slope line to Excel XY charts

Imagine you are analyzing app downloads for two weeks. Your data looks like this. You want to quickly visualize which apps have been downloaded more this week.

sample data for excel chart with slope line

Select Last week & this week columns and create XY chart (scatter plot). You will get this.

chart from last week & this week downloads data

In a blank range, create slope series like this. The starting values will be 0,0. Ending values can be manually typed or you can use MAX() formula to generate them from data.

(continued). Now add this slope series to the scatter plot. We will end up with this chart.

Change the slope series to “Scatter with straight line” type of chart. To do this, right click on the orange dots (slope series) and select “Change series chart type”.

Format everything. Add a chart title, space out the grid lines and format the slope line. Your chart will be almost ready.

Bonus step: You can add a text box to the chart and type in “more this week” in it and place it above the line. This will help in reading the data better. Here is our final XY chart with slope line.

Slope line in XY charts – When to use them & tips

Slope line is a great way to enhance the readability of certain types of scatter plots. Consider adding them whenever you are visualizing data from two time periods or two similar situations. Examples are,

Last year vs. This year sales
Male vs. Female customers by stores
Failure rate in machinery before and after maintenance
Test scores of students in two different subjects

Tips for working with slope line:

Always annotate the axis (use either axis titles or put text boxes near them)
Consider highlighting few of the points on charts based on the value. For example, we can highlight apps that have significantly change in downloads. Like this:

slope line chart with important points highlighted

Here is a tip on how to highlight important points on a chart.

Download sample workbook

Click here to download sample workbook with slope line example. It also shows all the steps needed to create these charts.

Adding slope line to scatter plots – video

I created a video explaining slope line technique. You can also see my quarantine look (full beard + nearly crazy hair). See it below or head to my YouTube channel.

Other tips to make your charts pop

If you are looking for a place to polish your charting game, you’ve come to right place. Check out below examples to learn something unique, useful and uplifting. Click on image to view the relevant tip.