5 Steps for adding slope line to Excel XY charts - KING OF EXCEL

Monday, August 10, 2020

5 Steps for adding slope line to Excel XY charts

5 Steps for adding slope line to Excel XY charts

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.
add slope line to xy charts

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
  1. Select Last week & this week columns and create XY chart (scatter plot). You will get this.
chart from last week & this week downloads data
  1. 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.
data for slope series
  1. (continued). Now add this slope series to the scatter plot. We will end up with this chart.
scatter plot after adding slope series
  1. 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”.
changing slope series to line
  1. Format everything. Add a chart title, space out the grid lines and format the slope line. Your chart will be almost ready.
xy chart with slope line
  1. 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.
completed 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

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).

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment