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

Get FREE Advanced Excel Exercises with Solutions!

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.


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


How to Calculate Euclidean Distance in Excel: 2 Methods

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


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.


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.


Download Practice Workbook

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


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.


<< Go Back to Distance | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo