# 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''")`

• Press Enter.

• Copy the formula from F5 to F10 to calculate the remaining longitudes.

Your longitude values will now match the desired format, as shown in the example picture below.

## Things to Remember

• Latitude:
• Ranges from -90 (south pole) to +90 (north pole).
• Longitude:
• Should fall within 0 to 180 (east or west).

<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF