Category: mapping

Put your finger on a globe and drag it across. Do you end up pointing at a horizontal or vertical line? These geographic coordinates are invisible on the actual planet yet used on maps and globes everywhere to pinpoint specific locations. However, sometimes these very helpful lines are in an unhelpful format. Whether you’d rather your latitude and longitude coordinates be in decimal degrees (DD) or degrees, minutes, and seconds (DMS), Excel makes it easy—no math required.

We’ll cover exactly how to convert the coordinates into decimals (or go from decimals to degrees) in a matter of steps. But first, you’ll want to ensure latitude and longitude are in individual columns.

Move Your Coordinates

Before you can convert your coordinate pair into the format you want, you’ll need to ensure latitude and longitude, along with the degrees, minutes, and seconds of each are in separate Excel columns. Why can’t we do this manually? Imagine you have 200+ rows of coordinate pairs. You need a way to select an entire column and separate its components at once.

One of the easiest methods is Excel’s ‘Text to Columns’. The tool enables you to identify a point in your text that Excel can separate for you. Let’s see how this popular Excel tool helps us manage our coordinates.

Use Excel’s Text to Columns to Separate Lat and Long

The first thing we’ll want to do is separate the coordinates into different cells. A geography reminder: latitude typically comes first, followed by longitude). To get started:

1. Select the cells you’d like to separate
2. Navigate to Excel’s “Data” menu
3. Select “Text to Columns…”
4. In Step 1 of the Text Wizard, identify whether you want to separate your data by Delimited characters (commas, tabs, etc between your data) or Fixed width (moving lines between the fields)
5. Step 2 is where you’ll determine the specific part of your text to separate (the Space in our example)
6. In most cases, you can click finish and be done

Amend your headings to better fit your updated data and the result is separated latitude and longitude coordinates. Now let’s apply the same steps to separate the degrees and minutes (and seconds if you have them!) of each coordinate.

Use the Same Tool to Isolate Degrees, Minutes, and Seconds

Now that we have latitude and longitude in different cells, we can separate our data even further. We’ll want to divide the coordinates into individual columns for degrees, minutes, and seconds. This will come in handy for converting into decimals later.

Let’s get started:

1. Optional As we delve deeper into Text to Columns, you may find it helpful to copy the character(s) already in your spreadsheet (like °) instead of scouring the web or your Excel menu for the Special Characters we often see with coordinates
2. Select your desired cells (either your new latitude or longitude column), ensuring there are a few columns to the right of either
3. Once again, navigate to Excel’s “Data” menu
4. Select “Text to Columns…”
5. Choose between the two data types in Step 1 of the Wizard
6. Determine what character or space to separate the degree (° in our example) in Step 2
7. Click Finish

Now that we have degrees set, we’ll do the same steps above to separate the minutes (′) and would repeat if our coordinates contained seconds.

There we have it! Simply replace the headings with something more apt and you’re good to move onto the next step: conversion. You can also discover even more Excel skills in our Simplify Complicated Data in Excel Spreadsheets post.

Convert Latitude and Longitude to Decimals in Excel

With your data in separate columns, there’s nothing to stop you from converting your coordinates to decimals. As noted in our Latitude and Longitude in Excel: Calculate Distance, Convert Degrees, and Geocode Addresses post, the formula for doing so is:

=Degree+Minute/60+Second/3600

To use it in Excel, follow the steps outlined here:

1. Add a heading (such as Decimal) to a blank column
2. Copy and paste the formula above into a cell under your new heading
3. Change the degree, minutes, and second placeholders in the formula to the corresponding cell number (i.e. A2)
4. Drag or copy the formula down the rest of the column

There you have it! Your DMS coordinates are now in a decimal format. But what if you want to transform decimal coordinates back to DMS? We’ll go over that as well.

Excel Decimal Degrees to DMS

If you need to reverse the change from DMS to decimal or you started with unwanted decimals, here’s how to go about it.

1. Select the decimal column
2. Navigate to Excel’s “Data” menu
3. Select “Text to Columns…”
4. Opt for Fixed width in Step 1 of the Text Wizard
5. Add a line before the decimal point
6. Finish

The first column now houses your degrees. As for minutes, take your remaining decimal and multiply it by 60 in a new column. Drag down to the rest of the cells.

If you were working with seconds, you would once again take only the decimal of the minutes and multiply it by 60 in a new column. You could then use =CONCATENATE to put it all together.

We’ve gone back and forth between degrees, minutes, seconds, and decimals. While we could continue to do this all day long thanks to Excel’s tools, there’s a better use of our time.

Visualize Your Lat + Long Points

After converting latitude and longitude to your desired format, your points are sitting pretty in your spreadsheet. But there may be a better place for your data.

With a custom map, you can visualize your latitude and longitude data and filter out what you want to see at any given moment. Take a look for yourself on our example map below.

View Top 100 Stores of 2019 in a full screen map

Zoom in and out of your locations with a double click of your mouse. Different colored markers represent the various values of your data. Get started with better data visualization today at batchgeo.com.

We all love our cats, dogs, and hamsters. But what about the animals we usually only see on the Discovery Channel? In-person viewing of wildlife can help educate and motivate animal lovers in conservation efforts. This is one of the goals of zoos, and in the United States, there are 439 of them.

But where in the country are the most zoos located? Ten U.S. states (and even some cities!) have many more than others, which means you might be able to visit two in one day. But not all zoos are the same. Zoos can be diverse in the type of animals they keep. See the familiar and unfamiliar zoo names for yourself on the map below.

View U.S. Zoos in a full screen map

The data for the map comes from Wikipedia’s list of zoos in the US. It includes some of the largest zoos in the US, including zoos of various types: safari and animal theme parks, aviaries, butterfly or other insect zoos, reptile centers, and petting zoos, as well as wildlife sanctuaries and nature reserves. Filter by the type of zoo that most interests you or stay tuned for where they’re located.

Most Zoos Are in These 10 States

While 439 U.S. zoos may seem like a lot, they’re not all evenly distributed across the country. Several states have at least 25 times the number of zoos than other states. Even select cities have more zoos than some states.

According to Wikipedia, Florida is home to the most zoos in the U.S. Thirty-one animal theme parks, jungles, and animal museums are located across the Sunshine State. Four of those 31 zoos are located in a single Florida city: Miami. There are also three in Tampa.

Additionally, both Texas and California have plenty of zoos to offer residents and visitors alike. Texas has 26 while 25 are located in California. Two more states have at least 20: Wisconsin (22)—where the city of Wisconsin Dells is home to three—and New York (20). Speaking of New York, five zoos are located in N.Y.C., though they’re all in different neighborhoods. Brooklyn, Manhattan, Queens, Staten Island, and The Bronx each have their own zoo.

On the other end of the spectrum, the entire state of Wyoming has just one zoo: the Kindness Ranch Animal Sanctuary. The state has no official zoo. Other states like Vermont and Rhode Island have only two. But what kinds of zoos are we talking about? Let’s take a look.

Safaris, Wildlife Sanctuaries, & Other Zoo Types

All of the 439 zoos contain animals and all are open to visitors (at least pre-pandemic). But some are marketed towards lovers of specific animals. For example, a safari zoo would attract more elephant aficionados than a farm zoo. Horse fans may prefer the farm option which is why we assigned a type to each zoo.

• Zoo – 175
• Park – 87
• Wildlife sanctuary – 47
• Museum, conservation, or nature reserve – 25
• Farm or petting zoo – 23
• Aviary, butterfly, or other insect zoo – 19
• Safari – 18
• Reptile or marine center – 17
• Lions, tigers, & bears – 14
• Animal theme park – 14

Of course, most are general city zoos (175). Of these, 15 are located in Texas. As for the types of zoos that appear the least, there are 14 animal theme parks. Four can be found in Florida: Busch Gardens Tampa Bay, Disney’s Animal Kingdom (in Bay Lake), Orlando’s Gatorland, and Lion Country Safari in Loxahatchee.

The rest of the animal theme parks are on a one-per-state basis and none can be found in the same city. But just because that’s the case with these types of zoos, doesn’t mean it applies to them all.

Hit Two Zoos in One Day

Several cities have multiple zoos. For example, Miami is home to Zoo Miami, Jungle Island, Monkey Jungle, and the Zoological Wildlife Foundation. Other cities like Wisconsin Dells and St. Louis also have many zoo options. To determine if you can visit two on the same day, do more than just eyeball how close they are. You can calculate distances between two addresses or points, (in this case, zoos) on your BatchGeo maps.

To do so, click the ruler in the top left corner of the map and select the measuring tool. From here, draw a line to each point and BatchGeo will let you know how far they are apart. For example, Miami’s Jungle Island is a little over 18 miles from Zoo Miami in the same city. These benefits are available to BatchGeo Pro users in Advanced Mode. Get started today at batchgeo.com.

Vacationers flock to the Florida shores or beautiful beaches of Hawaii because they love the sea. But the ocean is not always such a calm place, as proven by international shipwrecks. Ships in the night need precautions to keep them safe. Of course, lighthouses fall into this category and the tallest among them are a sight to see in addition to the visibility they provide to passing ships

In the United States, the tallest beacons stand between 16 and 210 feet tall. So let’s highlight the 10 tallest in the entire country along with the five states with the most lights (and the least), as shown on the map below.

View Tallest lighthouse in the United States in a full screen map

We gathered the data from this List of tallest lighthouses in the United States to see how they dot the U.S. coasts (except for the one in Summersville Lake, West Virginia!). You can read on for more information about the tallest of the tall below.

The 10 Tallest Beacons

Lighthouse height is of the utmost importance. The lights must be high enough for mariners to see before they reach danger. But how tall are lighthouses? Take the Statue of Liberty. The 305-foot (93 m) national monument was actually an operating lighthouse between 1886 and 1901 after a light was installed in the torch and around its feet.

While no official beacon in the U.S. rises to quite that height, several come close. The 10 tallest among them all stand above 150 feet, which you’ll see below and on the map when you filter by Height (ft).

1. Cape Hatteras Light (210 feet)
2. Cape Charles Light (191)
3. Ponce de Leon Inlet Light (175)
4. Absecon Light (171)
5. Cape Lookout Light (169)
6. Fire Island Light (168)
7. St. Augustine Light (165)
8. Cape Henry Light (164)
9. Barnegat Light (163)
10. Navassa Island Light (162)

So what is the tallest lighthouse in the United States? That’s the Cape Hatteras Light (210 ft.). Located in the Outer Banks of Buxton, North Carolina, the beacon is 19 feet more than even the next tallest light.

Also of note: eight of the 10 tallest are located in the Eastern U.S. Even more specifically, six reside in the Southeastern region of the country. The Cape Charles Light (the second tallest beacon at 191 ft.) and Cape Henry Light (164 ft.) are of Virginia while the previously discussed Cape Hatteras Light along with Cape Lookout Light (169 ft.) were built in North Carolina. Also in the Southwest are Florida’s Ponce de Leon Inlet (175 ft.) and St. Augustine Lights (165 ft.). New Jersey’s Absecon Light (171 ft.) and Barnegat Light (163 ft.) round out the last of the tallest lighthouses concentrated in the East.

Check out Where Are the World’s Tallest Buildings? or take a closer look at where these lights are located.

Five Locations Loaded With Lighthouses

North Carolina and other Southeastern states may be where the top 10 tallest of the tall reside, but where are the majority of the 240+ tallest beacons located? Below are the states with more than 10 uber-tall lighthouses.

• Michigan (110 lighthouses)
• California (30)
• Washington (16)
• Florida (15)
• Alaska (12)

Michigan is the home of more than 45% of the U.S.’s tallest lighthouses and the northern region of the Great Lake State like Mackinaw and Cheboygan is where they’re most concentrated. After Michigan, California has the most, followed by Washington, Florida, and Alaska. North Carolina, Oregon, Maryland, and Maine also have more than five tall lights, though it’s still a feat when compared to states with none or just one.

Less Light Locations

On the other hand, what are some places that have fewer beacons? As the 243 tallest lighthouses are spread across just 25 U.S. states, half have none. It’s easy to see which states are without on the map (pretty much every state that’s not on the water). All the more interesting: the states (on the coasts) with only one.

These single-lighthouse states include Connecticut, Delaware, Hawaii, and Rhode Island. The New London Harbor Light (89 ft) is in CT. The Marcus Hook Light is along the Delaware River and stands 100 feet tall. Hawaii’s sole light (138 ft) is on the north side of Moloka’i while the 45-foot Beavertail Light is located in Little Rhody.

Additionally, the non-coastal states of Illinois, Minnesota, and West Virginia each only have a single lighthouse.

The last location with just one beacon is Navassa Island, a small uninhabited island in the Caribbean Sea. Now let’s switch gears to a better way of viewing your many map markers (like 243 of the tallest lighthouses).

Summarize Multiple Markers Below

Sometimes you find yourself with a lot of data squished together location-wise. While a map of your data is a much better place to store and visualize location information than a spreadsheet, you may run into marker overload. For example, there’re 243 tall lighthouses on the map, but they’re concentrated close together on the country’s coasts. It can be hard to get an idea of individual markers without zooming in and losing the big picture.

This is why a way to see an overview of what’s below is invaluable. With map clustering, which is automatically enabled on maps with 300+ locations, there’s no need to zoom or squint. Markers that are near each other are replaced with another icon to represent multiple markers. You’ll be able to see either the number of markers the new icon contains or the average or sum of specific data. You can adjust the data settings and also manually enable it on less crowded maps.

Explore how icons that summarize multiple markers help you better understand your data at batchgeo.com.