How to Write Latitude and Longitude in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, we can do a wide range of work. We can Write Latitude and Longitude in a specific format in Excel. Here, in this article, I will show you how to write latitude and longitude in Excel in two distinct ways. Hopefully, you will find interest in it. I hope the latitude and longitude writing process described in this article will help you to increase your Excel skill. Follow the steps given below. I have added the required images here also.


Download Practice Workbook

Please download the workbook to practice yourself.


2 Effective Ways to Write Latitude and Longitude in Excel

In this article, I will consider a dataset given below. The dataset has three columns and seven rows. The three columns, B, C, and D describe City, Latitude(N), and Longitude(W). The dataset ranges from C4 to D10. You can see that every latitude and longitude are in decimal points. You need to change the format of this latitude and longitude. Let’s follow the steps one by one.

Dataset of how to write latitude and longitude in excel


1. Change the Format of Cells to Write Latitude and Longitude in Excel

This is the first method of this article. I have added two new columns called Latitude and Longitude in the D and E columns. Let’s follow the procedures to write latitude and longitude in Excel. I have added the necessary pictures for your better understanding.

changing format to how to write latitude and longitude in excel

Steps:

  • First, copy the values of column C to column D.

changing format to how to write latitude and longitude in excel

  • Then, select the cells from D5 to D10.

changing format to how to write latitude and longitude in excel

  • After that, press Ctrl+1.
  • Then, a window will be opened. Select the Number option of that window.
  • Meanwhile, select the Custom option.
  • Select any custom format and edit it by dialing ### => Alt+0176=> Space => 00=>’.
  • Then, press Ok.

changing format to how to write latitude and longitude in excel

  • As a result, you will find the latitudes just like the picture given below.

changing format to how to write latitude and longitude in excel

  • Additionally, you need to follow the same procedure for longitudes.
  • First, copy the values from the E column and paste them into the F
  • Then, select the cells from F5 to F10.

changing format to how to write latitude and longitude in excel

  • Later, press Ctrl+1
  • After that, a window will be opened, and select the Number option of that window.
  • Meanwhile, select the Custom
  • Select any custom format and edit it by dialing ### => Alt+0176=> Space => 00=>’
  • Next, press Ok.

changing format to how to write latitude and longitude in excel

  • Consequently, you will find the result just like the picture given below.

changing format to how to write latitude and longitude in excel

Read More: How to Convert Address to Lat Long in Excel (2 Easy Methods)


2. Use a Formula to Write Latitude and Longitude in Excel

This is the second and the last method of this article. This is another method to write latitude and longitude in Excel. The steps are very easy. Follow the steps regarding images and you will be able to learn how to write latitude and longitude in excel. Follow the steps given below.

Steps:

  • First, select the D5 cell first.

Using Formula to how to write latitude and longitude in excel

  • Then, write down the following formula in the selected cell.
=TEXT(TRUNC(C5), "0" & CHAR(176) & " ") & TEXT(INT((ABS(C5)-INT(ABS(C5)))*60), "0' ") & TEXT(((((ABS(C5)-INT(ABS(C5)))*60)-INT((ABS(C5)-INT(ABS(C5)))*60))*60)," 0''")

Here,

  • TEXT(TRUNC(C5), “0” & CHAR(176) & ” “): The TRUNC function returns the whole number before the decimal point. The CHAR Function returns the degree mark. The Text function Reuters the degree amount.
  • TEXT(INT((ABS(C5) – INT(ABS(C5)))*60), “0′ “): this whole Text function returns the minute part of the longitude.
  • TEXT(((((ABS(C5)-INT(ABS(C5)))*60) – INT((ABS(C5) – INT(ABS(C5)))*60))*60), ” 0””): The TEXT function returns the second value of the longitude.

  • After pressing Enter, you will find the result like the picture given below. Fill-handle the formula from D5 to D10.

  • As a consequence, you will find the result given below.

  • For the longitude, write down the following formula in the F5
=TEXT(TRUNC(E5), "0" & CHAR(176) & " ") & TEXT(INT((ABS(E5)-INT(ABS(E5)))*60), "0' ") & TEXT(((((ABS(E5)-INT(ABS(E5)))*60)-INT((ABS(E5)-INT(ABS(E5)))*60))*60), " 0''")

  • Next, press the Enter button.
  • Consequently, you will get the longitude.

  • Then, copy down the formula from F5 to F10.
  • Hence, you will find the longitudes like the picture given below.

Read More: How to Create Latitude Longitude Converter in Excel (2 Examples)


Things to Remember

  • The latitude value should be in -90 to +90.
  • The longitude value should be in 0 to 180.

Conclusion

In this article, I have tried to explain how to write latitude and longitude in Excel. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website Exceldemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.


Related Articles

Souptik Roy

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo