How to Format Latitude and Longitude in Excel (Two Methods)

In this tutorial, we’ll explore two methods for formatting latitude and longitude data in an Excel spreadsheet. Let’s assume you have a dataset with three columns: City, Latitude (N), and Longitude (W). The dataset spans from cell C4 to D10, and each latitude and longitude value is in decimal format. We’ll walk through the steps to change the format of these coordinates.

Method 1 – Changing Cell Format

Copy Values from Column C to Column D:
- First, copy the latitude values from column C and paste them into column D.

- Select the cells from D5 to D10.

Adjust Cell Format:
- Press Ctrl + 1 to open the Format Cells dialog.
- In the dialog, select the Number category.
- Choose the Custom format option.
- Edit the custom format by entering: ### Alt+0176 Space 00′
- Click OK.

Result:
- The latitude values in column D will now be displayed in the desired format.

Repeat for Longitudes:
- Follow the same procedure for longitudes:
  - Copy values from column E to column F.
  - Select cells from F5 to F10.
  - Open the Format Cells dialog (Ctrl + 1).
  - Choose the Number category and select Custom.
  - Edit the custom format: ### Alt+0176 Space 00′
  - Click OK.

- Open the Format Cells dialog (Ctrl + 1).
- Choose the Number category and select Custom.
- Edit the custom format: ### Alt+0176 Space 00′
- Click OK.

Final Result:
- Your longitude values in column F will now match the desired format.

Method 2 – Using a Formula to Format Latitude and Longitude in Excel

This is the second and final method discussed in this article. It provides an alternative approach for writing latitude and longitude values in Excel. Follow the steps below to learn how to achieve this.

Select the D5 Cell:
- Begin by selecting cell D5.

Enter the Formula:
- In the selected cell (D5), enter the following formula:

=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''")

Explanation:

TEXT(TRUNC(C5), “0” & CHAR(176) & ” “): This part of the formula extracts the whole number (degree) from the latitude value.
TEXT(INT((ABS(C5) – INT(ABS(C5)))*60), “0′ “): Calculates the minute part of the longitude.
TEXT(((((ABS(C5)-INT(ABS(C5)))*60) – INT((ABS(C5) – INT(ABS(C5)))*60))*60), ” 0””):Computes the seconds value of the longitude.

Result:
- After pressing Enter, you’ll see the formatted result in cell D5.

- Use the fill handle to copy the formula down from D5 to D10.

Longitude Formula:
- For longitudes, use the following formula in cell 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''")