Souptik Roy
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.
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.
Steps:
- First, copy the values of column C to column D.
- Then, select the cells from D5 to D10.
- 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.
- As a result, you will find the latitudes just like the picture given below.
- 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.
- 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.
- Consequently, you will find the result just like the picture given below.
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.
- 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.
