Exceljet

Quick, clean, and to the point

Distance formula

Excel formula: Distance formula
Generic formula 
=SQRT((x2-x1)^2+(y2-y1)^2)
Summary 

To compute the length of a 2D line given the coordinates of two points on the line, you can use the distance formula, adapted for Excel's formula syntax. In the example shown, the formula in G5, copied down, is:

=SQRT((D5-B5)^2+(E5-C5)^2)

where the coordinates of the two points are given in columns B through E.

Explanation 

The length of a line can be calculated with the distance formula, which looks like this:

 

Distance is the square root of the change in x squared plus the change in y squared, where two points are given in the form (x1, y1) and (x2, y2). The distance formula is an example of the Pythagorean Theorem applied, where the change in x and the change in y correspond to the two sides of a right triangle, and the hypotenuse is the distance being computed.

In Excel, the distance formula can be written with the exponent operator (^) and the SQRT function like this:

=SQRT((D5-B5)^2+(E5-C5)^2)

Following Excel's order of operations, the change in x and the change in y is calculated, then squared, and the two results are added together and delivered to the SQRT function, which returns the square root of the sum as a final result:

=SQRT((D5-B5)^2+(E5-C5)^2)
=SQRT((6)^2+(8)^2)
=SQRT(36+64)
=SQRT(100)
=10

The POWER function can also be used instead of the exponent operator (^) like this:

=SQRT(POWER(D5-B5,2)+POWER(E5-C5,2))

with the same result.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.