How to Calculate Euclidean Distance in Excel (2 Effective Methods)

The Euclidean distance is calculated for two vector points on a plane. However, the Euclidean distance is the distance between two locations in coordinate geometry. It is calculated based on the Pythagoras theorem. You can use some built-in functions to calculate the Euclidean distance in Excel quite easily. In this article, I will show you how to calculate Euclidean distance in Excel with ease.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


What Is Euclidean Distance?

Euclidean distance is the distance between two vector points on a plane. For example, A & B are two distinct points on a plane. The three-dimensional coordinates of these two points are A(X1, Y1, Z1) and B(X2, Y2, Z2) respectively. Then the Euclidean distance, AB between A & B is the difference between the coordinates of the A & B. And the direction of the distance will be from A to B.


Euclidean Distance Formula

If A(X1, Y1, Z1) and B(X2, Y2, Z2) are two vector points on a plane. Then the Euclidean distance between A & B can be calculated from the following formula:

AB= √((X1−X2 )^2+(Y1−Y2 )^2+(Z1−Z2 )^2))

In this formula,

  • X1, Y1, and Z1 are the coordinates of A.
  • X2, Y2, and Z2 are the coordinates of B.

Euclidean Distance Formula


2 Methods to Calculate Euclidean Distance in Excel

I will use the following dataset to show you to calculate the Euclidean distance between two points in Excel. In the dataset, you will find 3 columns, Axis, Point A, and Point B. Point A and Point B columns contain the corresponding coordinate values of the two points whose Euclidean distance we are about to calculate.

Dataset to Calculate Euclidean Distance in Excel


1. Using SUM & SQRT Functions to Calculate Euclidean Distance

At first, I will use two functions, the SUM, and the SQRT to calculate the Euclidean distance between Point A and Point B.

The syntax of the formula is,

=SQRT(SUM((number1,[number2],...))^2)

In this case, instead of using distinct numbers inside the SUM function, I will use the range of numbers. Thus, the formula will be so compact to use.

Now follow the steps below to calculate the Euclidean distance.

❶ First of all, insert the following formula in cell D9.

=SQRT(SUM((C5:C7-D5:D7))^2)

Formula Breakdown

  • C5:C7: This range refers to the coordinates of point A.
  • D5:D7: This range refers to the coordinates of point B.
  • SUM((C5:C7-D5:D7))^2: The SUM function sums up the square of the differences between the coordinates of point A and point B. If I expand this part, it becomes SUM((C5-D5)^2+(C6-D6)^2+(C7-D7)^2).
  • Finally, the SQRT function returns the square root of the value returned by SUM((C5:C7-D5:D7))^2.

Using SUM & SQRT Functions to Calculate Euclidean Distance

❷ Now press the ENTER button.

After that, you will get the Euclidean distance between point A and point B in cell D9. Here, the calculated Euclidean distance is 34.

Calculate Euclidean Distance in Excel

Read More: How to Calculate Distance Between Two Coordinates in Excel (2 Methods)


2. Calculate Euclidean Distance Using SQRT & SUMXMY2 Functions

In the previous formula, I used the SUM function to distinctly calculate the square of the difference between the coordinates of point A and point B. Well, this method is not compulsory when Excel has a built-in function called SUMXMY2; which does the exact same job.

In this method, I will show you how to calculate the Euclidean distance between two points using the SQRT & SUMXMY2 functions.

So the syntax of the formula is:

=SQRT(SUMXMY2(array_x, array_y))

So, in this formula, you just need to insert the coordinates of point A and point B respectively. The formula itself will calculate the rest.

Now follow the steps below to calculate the Euclidean distance between two points.

❶ At first, insert the following formula in cell D9.

=SQRT(SUMXMY2(C5:C7,D5:D7))

Formula Breakdown

  • C5:C7: This range refers to the coordinates of point A.
  • D5:D7: This range refers to the coordinates of point B.
  • SUMXMY2(C5:C7,D5:D7): The SUMXMY2 function sums up the square of the differences between the coordinates of point A and point B. If I expand this part, it becomes (C5-D5)^2+(C6-D6)^2+(C7-D7)^2.
  • Finally, the SQRT function returns the square root of the value returned by SUMXMY2(C5:C7,D5:D7).

Calculate Euclidean Distance in Excel Using SQRT & SUMXMY2 Functions

❷ Now press the ENTER button.

After that, you will get the Euclidean distance between point A and point B in cell D9. Here, the calculated Euclidean distance is 34.78505426.

Read More: How to Calculate Distance between Two GPS Coordinates in Excel


Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article.


Conclusion

To sum up, we have discussed 2 ways to calculate Euclidean distance in Excel. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo