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 skills. Follow the steps given below. I have added the required images here also.
How to Write Latitude and Longitude in Excel: 2 Effective Ways
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 is 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 column.
- Then, select the cells from F5 to F10.
- Later, press Ctrl+1 key.
- After that, a window will be opened, and 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=>’
- Next, press OK.
- Consequently, you will find the result just like the picture given below.
Read More: How to Create Latitude Longitude Converter in Excel
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 straightforward. 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 cell.
=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: Convert Latitude and Longitude to Decimal Degrees in Excel
Things to Remember
- The latitude value should be from -90 to +90.
- The longitude value should be between 0 to 180.
Download Practice Workbook
Please download the workbook to practice yourself.
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. 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
- How to Convert Lat Long to UTM in Excel
- How to Convert Degrees Minutes Seconds to Decimal Degrees in Excel
- How to Convert Degrees Decimal Minutes to Decimal Degrees in Excel
- Convert Decimal Coordinates to Degrees Minutes Seconds in Excel
- How to Convert Latitude and Longitude to Address in Excel
- How to Convert Address to Lat Long in Excel
- How to Convert ZIP Code to Latitude and Longitude in Excel
- How to Perform Reverse Geocoding in Excel
