Microsoft Excel For Data Processing

Prepared by: iMMAP www.immap.org Year 2023

 Microsoft Excel For Data Processing

Contents
1. Introduction ................................................................................................................... 6

2. Drop-down List .............................................................................................................. 7

2.1. Create a Drop-down List ......................................................................................... 7

2.2. Allow Other Entries ................................................................................................. 8

2.3. Add/Remove Items ................................................................................................. 9

2.4. Dynamic Drop-down List........................................................................................ 10

2.5. Remove a Drop-down List ...................................................................................... 11

3. Dependent Drop-down Lists ......................................................................................... 12

4. Interacting with Duplicate ............................................................................................ 15

4.1. Prevent Duplicate Entries ...................................................................................... 15

4.2. Find Duplicates ...................................................................................................... 18

4.3. Triplicates .............................................................................................................. 19

4.4. Duplicate Rows ...................................................................................................... 21

4.5. Remove Duplicates ................................................................................................ 21

5. Highlight patterns and values with conditional formatting .......................................... 24

5.1. Highlight Cells Rules ............................................................................................. 24

5.2. Clear Rules ............................................................................................................ 26

5.3. Top/Bottom .......................................................................................................... 26

5.4. Conditional Formatting with Formulas.................................................................. 27

6. Convert Decimal Coordinates to Degrees Minutes Seconds in Excel ........................... 30

6.1. Suitable Methods to Convert Decimal Coordinates to Degrees Minutes Seconds in

Excel 30

6.1.1. Using Text to Columns Feature ...................................................................... 30

6.2. Combination of TEXT and MOD Functions ............................................................ 38

Page |2
 Microsoft Excel For Data Processing

7. How to Convert Degrees Minutes Seconds to Decimal Degrees in Excel..................... 39

7.1. Apply a Formula to Convert Degrees Minutes Seconds to Decimal Degrees in Excel
39

Page |3
 Microsoft Excel For Data Processing

Table of Figures

Figure 1: Creating a Dropdown list. ....................................................................................... 7

Figure 2: Entering Values for List ......................................................................................... 8

Figure 3: Entering Value manually ........................................................................................ 8

Figure 4: List Restriction...................................................................................................... 9

Figure 5: Remove list restriction. ......................................................................................... 9

Figure 6:Adding / Removing items in the list. ...................................................................... 10

Figure 7: Dynamic Dropdown list ......................................................................................... 11

Figure 8: Remove a Drop-down List .................................................................................... 11

Figure 9: Creating Dropdown list. ........................................................................................ 12

Figure 10:Creating Name list. .............................................................................................. 13

Figure 11: Creating Name list 2 part. .................................................................................... 13

Figure 12: Created Dependent Dropdown list. ..................................................................... 14

Figure 13: Navigate to data validation. ................................................................................. 15

Figure 14: Data validation process. ...................................................................................... 16

Figure 15: Applied prevention .............................................................................................. 17

Figure 16: Customized Entry alert message. ....................................................................... 17

Figure 17: Navigating to Find duplicate. ............................................................................... 18

Figure 18: Highlight duplicate values. .................................................................................. 19

Figure 19: Highlighted unique value. .................................................................................... 19

Figure 20: Navigating to define rule. .................................................................................. 20

Figure 21: Defining Rules to find duplicate rows .................................................................. 21

Page |4
 Microsoft Excel For Data Processing

Figure 22: Removing duplicate values. ............................................................................... 22

Figure 23: Highlighted and Removed duplicate values. ...................................................... 22

Figure 24: More tips in removing duplicate......................................................................... 23

Figure 25: More tips to remove duplicate value 2. .............................................................. 24

Figure 26: Navigating to conditional formatting. ................................................................ 25

Figure 27: More tips of conditional formatting. .................................................................. 25

Figure 28: Clearing defined rule. ........................................................................................ 26

Figure 29: More tips on conditional formatting 2. ............................................................... 27

Figure 30: Conditional formatting formula. ........................................................................ 28

Figure 31: Conditional formatting Formula 2....................................................................... 29

Figure 32: Steps to convert DD to DMS ................................................................................ 31

Figure 33: Formula to convert DD DMS. .............................................................................. 33

Figure 34: Result of DD to DMS ........................................................................................... 38

Figure 35: Combination of TEXT and MOD Functions ......................................................... 38

Figure 36: Steps to Convert DMS to DD............................................................................... 40

Figure 37: Process of Converting DMS to DD ....................................................................... 41

Figure 38: Result of DMS to DMS in separate column. ......................................................... 41

Figure 39: The result of converting DMS to DD. .................................................................. 42

Page |5
 Microsoft Excel For Data Processing

1. Introduction
Microsoft Excel is part of the Microsoft Office suite of software products. It is a spreadsheet
that not only enables data to be stored in a tabular form i.e. rows and columns, but it also
features calculation functionalities, graphing tools, pivot tables, and so much more.

For this article, we will primarily focus on how to work with data in Excel, perform basic
checks and data cleaning, data visualization, and create dashboards.

The truth is most data has at least a few data quality problems. The data may have been
collected recently or came from an application. You’d have good reason to check its quality
before proceeding.

Data with quality issues can often operate just fine in its native application. It could be a
duplicate record that nobody ever accesses, they might not even know it's there. The other
reason is that most application data is looked at a small sliver at a time. One account or
customer at a time. Rarely does anyone export the entire dataset and look at it in aggregate.
Over time, duplicate and inaccurate records build up and are rarely purged (Inzata, 2019).

Poor data quality is the kryptonite of good reporting and credible analytics. If your data isn’t
of adequate quality, at worst you won’t be able to proceed any further. At best, others may
question your conclusions if you can’t show the right attention to data quality.

Here in this manual, we are going to cover some topics which is useful for data processing
in Microsoft Excel beside that in the last part of this manual we will follow up on methods of
converting Geographical coordinate to each other using Excel formula.

Page |6
 Microsoft Excel For Data Processing

2. Data Validation
In Microsoft Excel, the data validation feature helps you control what can be entered in your
worksheet (Easy, 2023). For example, you can:

- create a drop-down list of items in a cell.

- restrict entries, such as a date range or whole numbers only.
- create custom rules for what can be entered.

2.1. Drop-down List

Drop-down lists in Excel are helpful if you want to be sure that users select an item from a
list, instead of typing their own values and it will prevent spelling mistakes.

2.2. Create a Drop-down List

To create a drop-down list in Excel, execute the following steps.

1. On the second sheet, type the items you want to appear in the drop-down list.

Note: if you don't want users to access the items on Sheet2, you can hide Sheet2. To
achieve this, right-click on the sheet tab of Sheet2 and click on Hide.

2. On the first sheet, select cell B1.

3. On the Data tab, in the Data Tools group, click Data
Validation.
The 'Data Validation dialog box appears.
4. In the Allow box, click List.
5. Click in the Source box and select range A on
Sheet2.

Figure 1: Creating a Dropdown list.

Page |7
 Microsoft Excel For Data Processing

6. Click OK.

Figure 2: Entering Values for List

Note: to copy/paste a drop-down list, select the cell with the drop-down list and press
CTRL + c, select another cell and press CTRL + v.
7. You can also type the items directly into the Source box, instead of using a range
reference.

Figure 3: Entering Value manually

Note: this makes your drop-down list case sensitive. For example, if a user types yes, an
error alert will be displayed.

2.3. Allow Other Entries

You can also create a drop-down list in Excel that allows other entries.

Page |8
 Microsoft Excel For Data Processing

1. First, if you type a value that is not in the list, Excel shows an error alert.

Figure 4: List Restriction

To allow other entries, execute the following steps.

2. On the Data tab, in the Data Tools group, click Data Validation.

The 'Data Validation dialog box appears.

3. On the Error Alert tab, uncheck 'Show error alert after invalid data is entered'.

4. Click OK.

5. You can now enter a value that is not

on the list.

Figure 5: Remove list restriction.

2.4. Add/Remove Items

You can add or remove items from a drop-down list in Excel without opening the 'Data
Validation dialog box and changing the range reference. This saves time.

1. To add an item to a drop-down list, go to the items and select an item.

2. Right click, and then click Insert.

Page |9
 Microsoft Excel For Data Processing

3. Select "Shift cells down" and click OK.

Figure 6:Adding / Removing items in the list.

Note: Excel automatically changed the range reference from Sheet2!$A$2:$A$10 to

=Sheet2!$A$2:$A$12. You can check this by opening the 'Data Validation' dialog box.

4. To remove an item from a drop-down list, at step 2, click Delete, select "Shift cells
up" and click OK.

2.5. Dynamic Drop-down List

You can also use a formula that updates your drop-down list automatically when you add an
item to the end of the list.

1. On the first sheet, select cell B1.

2. On the Data tab, in the Data Tools group, click Data Validation.
3. The 'Data Validation' dialog box appears.
4. In the Allow box, click List.

5. Click in the Source box and enter the formula:

=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A),1)

6. Click OK.
7. On the second sheet, simply add a new item to the end of the list.

Explanation: the OFFSET function takes 5 arguments. Reference: Sheet2!$A$1, rows to

offset: 0, columns to offset: 0, height: COUNTA(Sheet2!$A:$A) and width: 1.
COUNTA(Sheet2!$A:$A) counts the number of values in column A on Sheet2 that are
not empty. When you add an item to the list on Sheet2,

P a g e | 10
 Microsoft Excel For Data Processing

COUNTA(Sheet2!$A:$A)increases. As a result, the range returned by the OFFSET

function expands and the drop-down list will be updated.

Figure 7: Dynamic Dropdown list

2.6. Remove a Drop-down List

To remove a drop-down list in Excel, execute the following steps.

1. Select the cell from the drop-down list.

2. On the Data tab, in the Data Tools group, click Data Validation.
3. The 'Data Validation' dialog box appears.
4. Click Clear All.

Figure 8: Remove a Drop-down List

Note: to remove all other drop-down lists with the same settings, check "Apply these
changes to all other cells with the same settings" before you click on Clear All.

5. Click OK.

P a g e | 11
 Microsoft Excel For Data Processing

3. Dependent Drop-down Lists

Want to learn even more about drop-down lists in Excel? Learn how to create dependent
drop-down lists.

- For example, if the user selects Kabul from a first drop-down list.
- A second drop-down list contains the districts items.
- But if the user selects Balkh from the first drop-down list, the second drop-
down list contains the Balkh districts.

To create these dependent drop-down lists, execute the following steps.

1. On the second sheet, create the following named ranges.

2. Create Name range.
3. Select All the districts of 3 provinces.

Figure 9: Creating Dropdown list.

P a g e | 12
 Microsoft Excel For Data Processing

4. From Formula menu > Create from Selection.

Figure 10:Creating Name list.

5. On the next sheet / in the current sheet select cell G1,8.

6. On the Data tab, in the Data Tools group, click Data Validation. Then Ok.

Figure 11: Creating Name list 2 part.

7. Next, select cell H1, 8.

8. In the Allow box, click List.
9. Click in the Source box and type =INDIRECT($B$1).
10. Click OK.

P a g e | 13
 Microsoft Excel For Data Processing

Result:

Figure 12: Created Dependent Dropdown list.

P a g e | 14
 Microsoft Excel For Data Processing

4. Interacting with Duplicate

Multiple copies of the same records take a toll on the computation and storage but may also
produce skewed or incorrect insights when they go undetected. One of the key problems
could be human error — someone simply entering the data multiple times by accident — or it
can be an algorithm that has gone wrong.

A solution suggested for this problem is called “data deduplication”. This is a blend of human
insight, data processing, and algorithms to help identify potential duplicates based on
likelihood scores and common sense to identify where records look like a close match (Easy,
2023).

4.1. Prevent Duplicate Entries

To prevent duplicate values from being entered into Excel, use data validation and the
COUNTIF function.

1. Select the range you want to enter data ex: A2:A20.

2. On the Data tab, in the Data Tools group, click Data Validation.

Figure 13: Navigate to data validation.

3. In the Allow list, click Custom.

4. In the Formula box, enter the formula shown below and click OK.
=COUNTIF($A$1:$A$29,A2) = 1

P a g e | 15
 Microsoft Excel For Data Processing

Figure 14: Data validation process.

Explanation: The COUNTIF function takes two arguments. =COUNTIF($A$2:$A$20,A2)

counts the number of values in the range A2:A20 that are equal to the value in cell A2. This
value may only occur once (=1) since we don't want duplicate entries. Because we selected
the range A2:A20 before we clicked on Data Validation, Excel automatically copies the
formula to the other cells. Notice how we created an absolute reference ($A$2:$A$20) to fix
this reference (Easy, 2023).

5. To check this, select cell A3 and click Data Validation.

As you can see, this function counts the number of values in the range A2:A20 that are equal
to the value in cell A3. Again, this value may only occur once (=1) since we don't want duplicate
entries.

6. Enter a duplicate invoice number.

P a g e | 16
 Microsoft Excel For Data Processing

Result. Excel shows an error alert. You've already entered that invoice number.

Figure 15: Applied prevention

Note: to enter an input message and error alert message, go to the Input Message and Error Alert
tab.

Figure 16: Customized Entry alert message.

P a g e | 17
 Microsoft Excel For Data Processing

4.2. Find Duplicates

This example teaches you how to find duplicate values (or triplicates) and how to find
duplicate rows in Excel.

To find and highlight duplicate values in

Excel, execute the following steps.

1. Select the range A1:C10.

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Highlight Cells Rules, Duplicate Values.

Figure 17: Navigating to Find duplicate.

4. Select a formatting style and click OK.

P a g e | 18
 Microsoft Excel For Data Processing

Result. Excel highlights the duplicate names.

Figure 18: Highlight duplicate values.

Note: select Unique from the first drop-down list to highlight the unique names.

Figure 19: Highlighted unique value.

4.3. Triplicates
By default, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), etc. (see previous
image). Execute the following steps to highlight triplicates only.

1. First, clarify the previous conditional formatting rule.

2. Select the range A1:C10.

3. On the Home tab, in the Styles group, click Conditional Formatting.

4. Click New Rule.

5. Select 'Use a formula to determine which cells to format'.

6. Enter the formula =COUNTIF($A$1:$C$10,A1)=3

P a g e | 19
 Microsoft Excel For Data Processing

7. Select a formatting style and click OK.

Figure 20: Navigating to define rule.

Result. Excel highlights the triplicate names.

Explanation: =COUNTIF ($A$1:$C$10,A1) counts the

number of names in the range A1:C10 that are equal
to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3,
Excel formats cell A1. Always write the formula for
the upper-left cell in the selected range (A1:C10).
Excel automatically copies the formula to the other
cells. Thus, cell A2 contains the formula
=COUNTIF($A$1:$C$10,A2)=3, cell A3
=COUNTIF($A$1:$C$10,A3)=3, etc. Notice how we created an absolute reference
($A$1:$C$10) to fix this reference.

Note: you can use any formula you like. For example, use this formula
=COUNTIF($A$1:$C$10,A1)>3 to highlight names that occur more than 3 times.

P a g e | 20
 Microsoft Excel For Data Processing

4.4. Duplicate Rows

To find and highlight duplicate rows in Excel, use COUNTIFS (with the letter S at the end)
instead of COUNTIF.

1. Select the range A1:C10.

2. On the Home tab, in the Styles group, click
Conditional Formatting.
3. Click New Rule.
4. Select 'Use a formula to determine which cells to format'.
5. Enter the formula =COUNTIFS(Animals,$A1,Continents,$B1,Countries,$C1)>1
6. Select a formatting style and click OK.

Figure 21: Defining Rules to find duplicate rows

4.5. Remove Duplicates

This example teaches you how to remove duplicates in Excel.

1. Click any single cell inside the data set.

P a g e | 21
 Microsoft Excel For Data Processing

2. On the Data tab, in the Data Tools group, click Remove Duplicates.

The following dialog box appears.

3. Leave all check boxes checked and click OK.

Figure 22: Removing duplicate values.

Result. Excel removes all identical rows (blue) except for the first identical row found
(yellow).

Figure 23: Highlighted and Removed duplicate values.

P a g e | 22
 Microsoft Excel For Data Processing

To remove rows with the same values in certain columns, execute the following steps.
4. For example, remove rows with the same Last Name and Country.
5. Check Last Name and Country and click OK.

Result. Excel removes all rows with the same Last Name and Country (blue) except for the
first instances found (yellow).

Figure 24: More tips in removing duplicate.

Let's look at one more cool Excel feature that removes duplicates. You can use the Advanced
Filter to extract unique rows (or unique values in a column).
7. On the Data tab, in the Sort & Filter group, click Advanced.
8. The Advanced Filter dialog box appears.
9. Click Copy to another location.
10. Click in the List range box and select the range A1:A17 (see images below).

P a g e | 23
 Microsoft Excel For Data Processing

11. 9. Click in the Copy to box and select cell F1 (see images below).
12. 10. Check Unique records only.
13. Click OK.
Result. Excel removes all duplicate last names and sends the result to column F.

Figure 25: More tips to remove duplicate value 2.

5. Highlight patterns and values with conditional formatting.

Conditional formatting makes it easy to highlight certain values or make particular cells easy
to identify. This changes the appearance of a cell range based on a condition (or criteria).
You can use conditional formatting to highlight cells that contain values which meet a
certain condition. Or you can format a whole cell range and vary the exact format as the value
of each cell varies (Easy, 2023).

Temperature information with 3-color scale conditional formatting applied.

5.1. Highlight Cells Rules

To highlight cells that are greater than a value, execute the following steps.

P a g e | 24
 Microsoft Excel For Data Processing

1. Select the range A2:A11.

2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click Highlight Cells Rules, Greater Than.
4. Enter the value 80 and select a formatting style.
5. Click OK.

Figure 26: Navigating to conditional formatting.

Result. Excel highlights the cells that are greater than 80.

Note: you can also use this category (see step 3) to highlight cells that are less than a value,
between two values, equal to a value, cells that contain specific text, dates (today, last week,
next month, etc.), duplicates or unique values.

Result: highlighted the values between 20 – 85.

Figure 27: More tips of conditional formatting.

P a g e | 25
 Microsoft Excel For Data Processing

5.2. Clear Rules

To clear a conditional formatting rule, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click Clear Rules, Clear Rules from Selected Cells.

Figure 28: Clearing defined rule.

5.3. Top/Bottom
To highlight cells that are above average, execute the following steps.

1. Select the range A1:A10.

2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click Top/Bottom Rules, Above Average.
4. Select a formatting style.
5. Click OK.

P a g e | 26
 Microsoft Excel For Data Processing

Figure 29: More tips on conditional formatting 2.

Result. Excel calculates the average (42.5) and formats the cells that are above this
average.
Note: you can also use this category (see step 3) to highlight the top n items, the top n
percent, the bottom n items, the bottom n percent, or cells that are below average.

5.4. Conditional Formatting with Formulas

Take your Excel skills to the next level and use a formula to determine which cells to format.
Formulas that apply conditional formatting must evaluate to TRUE or FALSE.

1. Select the range A1:E5.

2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Click New Rule.
4. Select 'Use a formula to determine which cells to format'.
5. Enter the formula =ISODD(A1)

P a g e | 27
 Microsoft Excel For Data Processing

6. Select a formatting style and click OK.

Figure 30: Conditional formatting formula.

Result. Excel highlights all odd numbers.

Explanation: always write the formula for the upper-left cell in the selected range. Excel
automatically copies the formula to the other cells. Thus, cell A2 contains the formula
=ISODD(A2), cell A3 contains the formula =ISODD(A3), etc.

Here's another example.

1. Select the range A2:D7.

2. Repeat steps 2-4 above.
3. Enter the formula =$C2="USA"
4. Select a formatting style and click OK.

P a g e | 28
 Microsoft Excel For Data Processing

Result. Excel highlights all USA orders.

Figure 31: Conditional formatting Formula 2.

Explanation: we fixed the reference to column C by placing a $ symbol in front of the column
letter ($C2). As a result, cell B2, C2 and cell D2 also contain the formula =$C2="USA", cell A3,
B3, C3 and D3 contain the formula =$C3="USA", etc.

P a g e | 29
 Microsoft Excel For Data Processing

6. Convert Decimal Degree Coordinates to Degrees Minutes Seconds

in Excel
Sometimes you have some decimal coordinates in your Excel spreadsheet, and you need to
convert them to degrees minutes, or seconds in Excel. In Microsoft Excel, you can easily
convert them using VBA code and several Excel functions. This article will show how to
convert decimal coordinates to degrees, minutes, and seconds in Excel. I think you will find
this article informative and gain lots of knowledge regarding the topic (Demy E. , 2022).

6.1. Suitable Methods to Convert Decimal Coordinates to Degrees Minutes

Seconds in Excel
To convert decimal coordinates to degrees, minutes, and seconds in Excel, there are some
different and effective methods to use. All these methods are easy to use. To convert the
decimal coordinates, we would like to utilize several Excel functions and commands. We will
also use two different VBA codes to solve the problem. To show all the methods properly, we
take a dataset that includes several decimal coordinates.

6.1.1. Using Text to Columns Feature

Our first method is based on using the text-to-column feature. In this method, we would like
to convert decimal degrees into degrees, minutes, and seconds. In every case, we will utilize
text to column feature. To understand the method carefully, follow the steps.

1- First, take a dataset that includes several columns including decimal degrees,
minutes, seconds, and degrees.

P a g e | 30
 Microsoft Excel For Data Processing

2- Copy the range of data cells B paste it into column C.

3- Then, select the range of cells C.

4- After that, go to the Data tab on the ribbon.

5- Then, select the Text to Columns option from the Data Tools group.

6- Then, the Convert Text to Column Wizard dialog box will appear where you need to
finish 3 steps.
7- In the first step, select the Fixed
Width option.
8- Then, select Next.
1

Figure 32: Steps to convert DD to DMS

2

P a g e | 31
 Microsoft Excel For Data Processing

9- In the 2nd step, click on the point after 34. It will create a line.
10- Then, select Next.

11- In the third step, you will see the numbers before the line turns black.
12- Then, click on Finish.

13- As you have data in column C, you need to replace it.

14- Click on OK.

P a g e | 32
 Microsoft Excel For Data Processing

15- There we have the degrees from the decimal degrees. See the screenshot.
16- Remove the available date from Column D.

17- Then, select cell D.

18- Write down the following formula. =(B5-C5)*60

Figure 33: Formula to convert DD DMS.

19- Press Enter to apply the formula.

20-Press Enter to apply the formula.

P a g e | 33
 Microsoft Excel For Data Processing

21- After that, copy the range of cells D.

22-Then, go to the Home tab on the ribbon.
23- Select the Paste drop-down option from the Clipboard group.
24-The Paste Special dialog box will appear.
25- Then, select Values from the Paste section.
26- Finally, click on OK.

P a g e | 34
 Microsoft Excel For Data Processing

27- It will convert the formula into values.

28- It is necessary because the Text to Column feature takes the formula instead of the
value if there is any formula.
29- Then, select the range of cells D.

30- After that, go to the Data tab on the ribbon.

31- Then, select the Text to Columns option from the Data Tools group.

32- Then, the Convert Text to Column Wizard dialog box will appear where you need to
finish 3 steps.
33- In the first step, select the Fixed Width option.
34- Then, select Next.
35- In the 2nd step, click on the point after 32. It will create a line.
36- Then, select Next.
37- In the third step, you will see the numbers before the line turns black.
38- Then, click on Finish.

P a g e | 35
 Microsoft Excel For Data Processing

35, 36
33, 34

37, 38

39- There we have the Minutes from the decimal degrees. See the screenshot.

P a g e | 36
 Microsoft Excel For Data Processing

40- There we have the Minutes from the decimal degrees. See the screenshot.
41- Then, create a new column beside the Minutes column.
42-After that, cut the values from the Seconds column and paste them into the newly
created Fraction column.

43- Then, select cell F.

44-Write down the following formula. =E5*60
45- Press Enter to apply the formula.
46- Then, drag the Fill Handle icon down the column.

47- This is the required conversion of decimal coordinates to degrees minutes and seconds
in Excel.
48- Delete Fraction column from the table.
49- Do the same process to convert Longitude DD to DMS.

P a g e | 37
 Microsoft Excel For Data Processing

Figure 34: Result of DD to DMS

6.2. Combination of TEXT and MOD Functions

Our next method is based on using the combination of TEXT and MOD functions. In this
method, we will utilize the MOD function to get the value. Then, using this return value, we
would like to utilize the TEXT function to convert decimal coordinates to degree minutes and
seconds in Excel. To understand the method properly, follow the steps (Demy E. , 2022).

1- First, select the cell that you want to convert.

2- Then, write down the following formula in the formula box.
3- Use the formula
4- Press Enter to apply the formula.
5- After that, drag the Fill Handle icon down the column.
6- This is the required conversion of decimal coordinates to degrees minutes and
seconds in Excel.

Figure 35: Combination of TEXT and MOD Functions

P a g e | 38
 Microsoft Excel For Data Processing

🔎 Breakdown of the Formula:

=TEXT(MOD(B2,360)/24,”[h]\°mm’ss\”””): First, we use the MOD function which returns a

remainder after a number is divided by a divisor. Here, the decimal degree is B2 and is
divided by 360. Then, divide the remainder by 24. The returned value will go under the
TEXT function. Here, it takes the value and then, we must set the text format. Finally, it
returns the value into the given format (Demy E. , 2022).

7. How to Convert Degrees Minutes Seconds to Decimal Degrees in

Excel
To denote angular dimensions, degrees, minutes, and seconds are commonly used (DMS).
Because one-minute equals 60 seconds and one degree equals 60 minutes, you may
describe angular observations in degrees and decimal fractions of degrees to simplify
mathematical calculations. This article explains some customized functions to convert
degrees, minutes, seconds to decimal degrees in Excel (Demy, ExcelDemy.com, 2022).

7.1. Apply a Formula to Convert Degrees Minutes Seconds to Decimal

Degrees in Excel
We’ll start by using a formula to convert degrees, minutes, and seconds to decimal format.
To do so, we must first remove the degrees, minutes, and seconds symbols before using the
formula. Follow the instructions below to do so.

1- First, copy the degree (°) from data for next step use.
2- Select the cells you want to convert.
3- Click on the Data menu.
4- Then, select the Text to Columns.

P a g e | 39
 Microsoft Excel For Data Processing

5- Choose the Delimited.

6- After that, click on Next.
7- From the options, click on the Other.
8- Paste the degree (°).
9- Then, click on Next.

Therefore, you see that degrees are separated, and the symbols are removed.

Figure 36: Steps to Convert DMS to DD

10- To remove the minutes (’) and Seconds (“) symbols, repeat the previous steps after
copying the minutes (’) (“).
11- Simply, paste the minutes (’) (“) symbols in the box.
12- Then, click on Next.

P a g e | 40
 Microsoft Excel For Data Processing

13- As before, click on the Finish button.

Minutes Seconds

Figure 37: Process of Converting DMS to DD

As a result, the degrees will be separated into degrees, minutes, and seconds without the
symbols.

Figure 38: Result of DMS to DMS in separate column.

14- Type the formula in cell F5 to convert the degrees, minutes, and seconds into decimal
degrees. (=C2+D2/60+E2/3600).

P a g e | 41
 Microsoft Excel For Data Processing

15- Press Enter to see the conversion.

16- Use the AutoFill tool to autofill the next cells.

Figure 39: The result of converting DMS to DD.

P a g e | 42
 Microsoft Excel For Data Processing

8. References
1. Demy, E. (2022). ExcelDemy.com.

2. Demy, E. (2022). ExcelDemy.com.

3. Easy, E. (2023, 6 12). Excel tutorial on the net.

4. Inzata, A. (2019). The Ultimate Guid to Ms. Excel for Data Processing. DSM Media.

P a g e | 43
Microsoft Excel For Data Processing

www.immap.org

P a g e | 44