Convert Latitude and Longitude

Nowadays, GPS localization is in common usage. Some apps use decimal format (48.85833), others return the coordinates in degrees, minutes and seconds (48°51'29.99'').

In this article I will show you how to convert one format to the other and vice-versa.

How to write a coordinate in Excel

GPS coordinates are generally written using the symbols ° (degrees), ' (minutes)'' (seconds)


But if you keep your coordinate as a string, no calculation is possible. So it will be impossible to convert the GPS coordinates ⛔⛔⛔

What is the best way to write a GPS coordinate?

Firstly,  use the following format for your GPS coordinates - hours:minutes:seconds. Each part of the coordinate should be delimited with a colon ":"


The default cell display could be different in function of the version of Excel that you are using. It can either a display the GPS coordinates as a date or a time.

Don't worry, we will format the display in 2 steps 😉😎😃

Insert a negative time

The standard GPS coordinate is

  • positive for East and North
  • negative for West and South

Unfortunately, Excel doesn't accept a negative time, so you need to wrap it in double quotes as though it is text. Then put a minus sign before the opening quote.


But the cell content is displayed as a string of #

Excel doesn't like negative time. But you can correct this by checking one option in  Excel.

  • Go to the menu File>Options
  • Select Advanced
  • At the bottom of the list of options, check the option "Use 1904 date system"

Customize the time format

Now, let's customize the format of our cell

  • Open the Format cell dialog box (Ctrl + 1)
  • Select the option Custom
  • In the Type box, enter this code exactly as it is displayed below


The hour between square brackets is compulsory to display a value over 23 hours.

Convert coordinates from DMS to decimal

So, because we have written the coordinate as a number, we just have to multiply the cell by 24 (24 hours in a day).

And the the conversion is done 😍😍😍


Change the format of the cell range D3:E7 to General.

Convert coordinates from decimal to DMS

And the other way round, to convert a decimal value in degrees, minutes, seconds, simply divide the value by 24.
= B3/24

Related posts

Permanent link to this article:

1 comment

    • rajeshwar pilli on 02/06/2018 at 11:07
    • Reply

    kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.

Leave a Reply

Your email address will not be published.