fbpx
Search
Close this search box.

Add slope line to XY charts

Share

Facebook
Twitter
LinkedIn

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

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.

More charting principles

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Leave a Reply