Formula Errors

How to Convert DMS to Decimal Coordinates with Positive or Negative Signs in Excel

Amos Gikunda Oct 12, 2026 869 views

Question details

The user needs an Excel formula to convert geographical coordinates from degrees, minutes, and seconds (DMS) into decimal degrees, while automatically applying a positive or negative sign based on the cardinal direction (N, E, S, W).

How to Convert DMS Coordinates to Decimal Degrees with Positive or Negative Signs in Excel

Product: Excel
Device & OS: not provided
Scenario: Converting geographic location data into a standardized decimal format for mapping software or spatial data analysis.
Observed behavior: The coordinate components (degrees, minutes, seconds, and direction) are separated, requiring a unified mathematical and logical formula to compute the signed decimal equivalent.

Before you start

Ensure your coordinate data is organized into separate, adjacent columns for degrees, minutes, seconds, and the cardinal direction letter (N, S, E, W) before writing the formula.

Solution 1Recommended

Use an IF and OR Formula to Calculate and Sign the Coordinates

Apply a combined IF and OR formula to calculate the decimal degrees and automatically assign a negative sign if the specified direction is South (S) or West (W).

To convert DMS to decimal degrees, you must divide the minutes by 60 and the seconds by 3600, then add them to the degrees. By wrapping this calculation in an IF function, Excel can check the direction column and invert the sign for southern and western coordinates.

Prepare your data columns

Set up your worksheet so that Degrees are in column B, Minutes in column C, Seconds in column D, and the Direction (N, S, E, or W) in column E. Assume data starts in row 2.

Select the destination cell

Click on the cell where you want the final decimal degree coordinate to be displayed (for example, F2).

Enter the conversion formula

Type the following formula into the formula bar: =IF(OR(E2="S",E2="W"),-(B2+C2/60+D2/3600),B2+C2/60+D2/3600)

Apply and fill down

Press Enter to calculate the result. The value will be positive for North/East and negative for South/West. Double-click or drag the fill handle in the bottom right corner of the cell to apply the formula to the rest of your dataset.

Use an IF and OR Formula to Calculate and Sign the Coordinates

Formula Customization: If your data is located in different columns, simply change the cell references (B2, C2, D2, E2) in the formula to match your actual data layout.

Advanced Spreadsheet Features

Process Coordinate Data Efficiently in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas like IF and OR, allowing you to convert DMS geographical data to decimal degrees just as you would in Microsoft Excel.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your geographic coordinates.
2. Verify data layout: Ensure your degrees, minutes, seconds, and directions are neatly separated into individual columns.
3. Enter the logic formula: Select the target cell and input the exact same =IF(OR(...)) formula used in Excel.
4. Batch process data: Press Enter, then drag the cell's fill handle down to convert all rows instantly.

100% compatible with Microsoft Excel formulas and functions.Easily process large batches of geographical data with drag-to-fill features.Free, lightweight, and fast alternative for data analysis and mapping preparations.

microsoft office alternative - wps office

Frequently Asked Questions

Can I extract the DMS values if they are all lumped together in a single cell?

Yes, you can use the 'Text to Columns' feature under the Data tab to split a single string of coordinates into separate columns based on spaces, commas, or specific symbols before applying the conversion formula.

Why is my formula returning a #VALUE! error?

A #VALUE! error usually occurs if there is hidden text or extra spaces in your number columns. Ensure that your degrees, minutes, and seconds columns contain strictly numeric data, and that the direction column does not have leading or trailing spaces.

How do I convert decimal degrees back to DMS format?

To convert decimals back, use the INT function to extract the whole degrees. Then multiply the decimal remainder by 60 to find the minutes (using INT again for the whole minutes), and multiply the new remainder by 60 to find the seconds.

Excel & Data Problems