How To Find Slope Of A Line On Excel

Wednesday, July 11, 2012
Peltier Technical Services, Inc., Copyright © 2021, All rights reserved.

Slope Graphs

Edward Tufte introduced Gradient Graphs inThe Visual Display of Quantitative Data (p. 158):

Slopegraphs compare changes over time for a listing of nouns located on an ordinal or interval scale. The table-graphic higher up organizes data for viewing in several directions. When read vertically, the chart ranks fifteen countries past government tax collections in 1970 and again in 1979, with the names spaced in proportion to the percentages. Across the columns, the paired comparisons show how the numbers changed betwixt 1970 and 1979. The slopes are also compared by reading down the drove of lines, and lines of unusual slope stand out from the overall upward pattern. The data shown is both integrated and separated: integrated through its connected content, separated in that the eye follows several unlike and uncluttered paths in looking over the data.

This tutorial will prove how to make a slope graph in Excel.

When to Utilize Slope Graphs

The Economist used a pair of donut charts to bear witness changing pre-tax profits among banks from 2007 to 2011 in Banking concern Profits Caput E. This is non a very effective approach.

The Economist's Donut Chart Showing Changing Bank Pre-Tax Profits

In Arrow Charts and Other Alternatives to Multiple Pie Charts on the Forbes mag web site, Naomi Robbins introduced Arrow Charts as a replacement for double pie charts (and double donuts are at to the lowest degree as bad). I wrote a tutorial on my web log that showed How to Brand Pointer Charts in Excel. The technique results in a much more effective chart:

Arrow chart showing changing bank pre-tax profit.

Naomi showed a slope graph in her Forbes blog postal service. You can read more about gradient graphs in Slopegraphs for comparison gradients: Slopegraph theory and exercise on the message board on Edward Tufte's spider web site, and in Edward Tufte's "Slopegraphs" on Charlie Park'southward blog.

Making a Slope Graph

Hither is the data for our gradient graph. Dissimilar the arrow chart, we practice not need to brand any adjustments to the information prior to building the chart.

Select the data range (A4:C10) and insert a line chart without markers.

Excel assumes the series are in columns because there are many more than rows of information. On the Nautical chart Tools > Design tab, click on Switch Row/Column.

Hide the vertical axis past setting Major and Minor Tick Marks and Tick Labels to None, and select No Line for line color.

Format the horizontal axis, and change the Position Centrality setting to On Tick marks. Yous might too want to apply a lighter gray than the default for the centrality line.

Delete the legend, brand the chart taller, and format the series lines so they are thinner (1.5 pt wide rather than the default 2.25 pt). At that place is still plenty of white space to the left of the chart where the legend had been.

Select a serial, and add information labels. here I used the default label (which is the value) in the Right position.

Select the kickoff label (single click to select all the labels, then single click again to select the i label), and format it and so information technology is in the Left position. As well color the label font to match the line color. For text I similar to utilise i tile darker in the Excel colour palette.

Add together and format labels as above for the residual of the series.

Reposition any overlapping labels. Single click twice on a label, then you can elevate it into position. The chart is wider because I stretched it to the left to fill the likewise-wide margin I began with.

Insert text boxes from the Chart Tools > Layout tab. Type the names of the regions, color the text to lucifer the data labels, and position the text boxes side by side to the labels.

Finally, add a chart title, and the slope graph is finished.

I generally adopt my labels to the right of the chart, but a lot of these labels are clustered at the bottom of the 2011 data. It might be better to position the region labels to the left of the chart.

The slope graph shows that while most regions saw little change, Asia Pacific had a large increase and Western Europe had a similarly large decrease. The pointer nautical chart likewise showed this, but it was not easy to see in the original double donut.

Slope Chart with Data Labels
Funny Conclusions from a Dual Pie Chart

Gradient Charts in Peltier Tech Charts for Excel

This tutorial shows how to create Slope Charts, including the data layout needed, and the detailed combination of nautical chart serial, chart types, and formatting required. This transmission process takes fourth dimension, is prone to error, and becomes boring.

I take createdPeltier Tech Charts for Excel to create Gradient Charts (and many other custom charts) automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of nautical chart type and format. This is a commercial product, tested on thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.