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

Dataset Overview

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 the latitude values from column C and paste them into column D.

Select the cells from D5 to D10.

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.

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

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.

Your longitude values in column F will now match the desired format.

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

Select cell D5.

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.

Press Enter and you’ll see the formatted result in cell D5.

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

For longitudes, enter 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''")