Category: mapping

If you have a long list of geographic coordinates to work with, a Microsoft Excel spreadsheet is sure to be useful. There are three basic Excel tools that can work for you, no matter how you want to manipulate your geographic coordinates. You’ll need to know how to calculate the distance between two latitude and longitude points, how to convert latitude and longitude data to decimal degrees, and finally, how to geocode latitudes and longitudes.

How to Calculate the Distance Between Two Coordinates in Excel

In an example of how to calculate the distance between two coordinates in Excel, we’ll seek to measure the great circle distance. We’ll note that latitude and longitude are denoted in degrees, minutes and seconds.

Convert Latitude and Longitude to Decimal Degrees in Excel

Everything, including something as complicated as degrees, minutes, and seconds, can be converted into decimals. The degrees part remains the same, but minutes and seconds need to be converted into their percentage of a degree and combined. There are 60 minutes in a degree and 60 seconds in a minute (which means 3,600 seconds in a degree). Therefore, divide minutes by 60 and seconds by 3,600.

The overall formula:

Total Degrees (in the decimal form) = Deg + [Mins / 60] + [Seconds / 3600]

…So About That Distance

Now that you have the latitude and longitude values in decimals, you just face the equation for the nautical miles between Timbuktu and Casablanca, noted underneath.

Nautical Miles= ACOS [(sin(Lat_place_1*PI()/180)*sin(Lat_place_2*PI()/180)+

cos(Lat_place_1*PI()/180)*cos(Lat_place_2*PI()/180)*

cos(Lon_place_2*PI()/180-Lon_place_1*PI()/180)) ] *3443.8985

That three thousand number, in the end, is the radius of Earth, in, Nautical Miles. Even if you were to substitute it with the radius of a sphere, assuming the Earth is spherical, at 3437.7468 NM, you will not be near the real, accurate distance.

Geocode Latitude and Longitude in Excel

Geocoding is the conversion of street addresses to latitude and longitude data that can be mapped. Now, there are two ways to geocode latitude and longitude from an Excel spreadsheet: the hard way and the easy way.

We’ll cover the hard way first, which is what you’ll need if you require the coordinates to live in your Excel document. You’ll need to write the Visual Basic (VBA) script to make a call to an external geocoder, or find the code elsewhere, such as this GitHub repo. There are a number of geocoder APIs and some geocoders are faster than others.

The Google Maps Geocoding API is a common choice and this is the API we’ll call in the easy option. However, it also can work directly within Excel. Armed with the code you’ve written or discovered, here are the steps to deploy geocoding within Excel.

Step 1: Find the Developer tab in your version of Excel.

Step 2: Look for the “Visual Basic” tab. This opens the development window.

Step 3: Insert —> Module. The new module will hold your function.

Step 4: Here is where you copy and paste your code.

Step 5: We need to add references now. So, Tools —> References. Make sure to check the Microsoft XML v6.0. CLICK OK.

Step 6: Press CTRL / CMD + S. Save as type —> Excel Add-In. Now you get to name your function. The extension has to be .xlam. Time to close all Excel files.

Step 7: Open a new file. File —> Options —> Add-Inns. In the Manage box, choose Excel-Add Ins.

Step 8: Press Go. Choose your function in the window that appears by ticking in the box. Press OK.

Step 9: VOILA. Now you could just use the new function you created, like any other function.

After you are done retrieving the values, copy and paste them elsewhere, off of the sheet with the calculations. Then delete the function calls because otherwise when you reopen this file, the Google API will start to be pinged again. That will eat up several minutes before the file is open. To optimize Google resource usage, take a 10-second break between addresses.

That’s a lot of steps, and assumes you’ve found some code to try out. Next, we’ll see the easier way to geocode your Excel data. It’s fast and reliable, but it won’t import the coordinates into your Excel file. On the other hand, the excel geocoding tool is copy-paste simple and gets you an interactive map.

Create a Map with Excel

There are a number of ways to create maps with Excel data. Perhaps the easiest is to just copy and paste your spreadsheet data into our map-making tool. Doing so turns your Excel document into a beautiful, interactive map like the one below.

View Example Latitude/Longitude Map in a full screen map

To make your own map based on latitude and longitude coordinates, separate the coordinates into their own columns within your spreadsheet.

Then, select and copy the rows and columns of your spreadsheet (Ctrl+C command or Cmd+C on Mac) and navigate to batchgeo.com. Paste (Ctrl+V or Cmd+V on Mac) in your data, and you’re done!

Whether you want to make a latitude and longitude map or create your own virtual wedding guide for your guests, BatchGeo provides a free and easy way to make beautiful web maps that are ready to be saved and shared.

Store locators have been a staple of the Internet almost since its inception, but they’re still difficult to build yourself. Set that programming lesson aside and see a quick way to create a locator for multiple locations of a store front, business, office (or anything for that matter) for your website. At BatchGeo, we’ve updated our store locator creator to provide an even easier way for your visitors to find the locations they seek.

If you try to create something like this map yourself, you’ll spend a lot of time reading tutorials and learning to write code for the Google Maps API. Instead, we let you copy and paste a list of addresses and we do the rest.

Enable Store Locator Mode on Your Map

Use our store locator tool to create a map with all the features you’d want in a Google Map. The tool will quickly geocode every address, turning each into coordinates to plot on a map. Then we create marker icons for every location. You can include the name for every store and any other data (such as the telephone number, hours, and more) in your spreadsheet. When you click a location, you can see the additional information about each place.

You can tune your map with various options, but here is the fastest way to create a store location map:

1. Copy and paste your addresses into the map data tool
2. Click “Map Now” to begin geocoding
3. Click “Save & Continue”
4. Select the “Store Locator” option and click “Save Map”

Now you’ll have a map that shows all your store locations visually, as well as in a browsable list to the left of the map. Your visitors can click on a map marker icon or any name in the list to open a box with additional data. Or, they can use the search at the top to find their nearest location.

Embed Your Store Locator on Your Website

Your store locator map takes minutes to create versus hours or weeks if you wrote the code yourself. You can send visitors to its unique web URL to quickly find nearby locations. Even better, make the map part of your website by embedding it like we did at the top of this page.

Again, it’s as easy as copy and paste. You’ll just need to locate the Embed Code while editing your map:

1. Find the email we sent with your link to edit your map
2. Click on the embed code field and copy the contents
3. Paste the embed code into your website HTML or content management system

The embed code will look something like this:

<p><iframe src="https://batchgeo.com/map/tesla-dealers" frameborder="0" width="100%" height="550" style="border:1px solid #aaa;"></iframe></p><p><small>View <a href="https://batchgeo.com/map/tesla-dealers">Tesla Dealerships and Galleries</a> in a full screen map</small></p>

When this code is embedded in your website, you will have a fully functional, interactive store locator map.

Use a Store Locator in Data View Mode

While the store locator allows users to browse locations by name, as well as on the map, you may prefer to only provide a visual representation. To remove the list from the left side of your store locator, you can switch the map to Data View mode. Even better, your map will remain functional as a store locator. Your visitors can click the marker icons and search for locations.

Use these steps to create a store location map without the list:

1. Copy and paste your addresses into the map data tool
2. Click “Map Now” to begin geocoding
3. Click “Save & Continue”
4. Select the “Data View” option and click “Save Map”

If you’ve previously created your map in Store Locator mode, you can simply switch its mode. Edit your map (look in your email for the special link) and toggle between Store Locator mode and Data View mode.

Add a Search Box Outside Your Map

While every store locator map made with BatchGeo comes with its own search functionality, you may prefer to include a search box elsewhere on your site. Similar to embedding your map, we provide the HTML needed to create a search form that will open directly in your map.

You’ll just need to locate the Locator Code while editing your map:

1. Find the email we sent with your link to edit your map
2. Click on the locator code field and copy the contents
3. Paste the embed code into your website HTML or content management system

The form code will look something like this:

&lt;form action=&quot;https://batchgeo.com/map/"><input type=&quot;hidden&quot; name=&quot;i&quot; value=&quot;tesla-dealers&quot;&gt;&lt;label&gt;Search for nearest location &lt;input type=&quot;text&quot; name=&quot;q&quot; value=&quot;&quot;&gt;&lt;/label&gt;&lt;input type=&quot;submit&quot; value=&quot;Search&quot;&gt;&lt;/form&gt;

When your visitor submits this form, it will open in your store locator with the search pre-populated.

Create a Map of Your Retailers, Sellers, and Other Locations

Store locators are a great way to share all kinds of locations. Even if you don’t operate a chain of stores yourself, you may have retailers who carry your merchandise. You can create a map of retailers and sellers and enable Store Locator mode to make it easily searchable.

BatchGeo will map any list of locations. Create a map today!

Pumpkins may be popular in October, but you probably have yet to see a 2,000-pound pumpkin. We certainly hadn’t until we mapped the 170 largest pumpkins ever grown. Visualize where in the world these massive pumpkins sprouted their first roots, where the growers who produced five or more of the largest pumpkins ever are from, and where top pumpkins with the same weight were produced.

View Largest Pumpkins in a full screen map

Finding the perfect data isn’t always easy. Unlike our maps of the presidential nominating convention locations and the national animals of every country, we weren’t satisfied with the single datasets available of the largest pumpkins of all time & where they were grown. Instead of settling for mediocre data, we combined two data sets. The first is from GiantPumpkin.com and the second from the Backyard Gardener. So if you find yourself dying to make a map but can’t find the perfect data, combine two datasets in one spreadsheet. Just make sure to put common data in the same column and remove any data that isn’t shared between the two datasets.

Oh My Gourd: The Top Ten Largest Pumpkins Ever Grown

The top ten largest pumpkins ever grown all weigh well over 2,200 pounds. That’s nearly two times the size of an adult grizzly bear! Who grew these gargantuan gourds, where, and what is the exact weight? Find out below.

1. 2,624.6 pounds grown by Mathias Willemijns of Belgium
2. 2,528 pounds grown by Steve Geddes of New Hampshire
3. 2,469 pounds grown by Steve Daletas of Oregon
4. 2,433.9 pounds grown by Ian and Stuart Paton of England
5. 2,416.5 pounds grown by Karl Haist of New York
6. 2.363 pounds grown by Joel Holland of Washington
7. 2.323.7 pounds grown by Beni Meier of Switzerland
8. 2,283 pounds grown by John Barlow/Jacobus of Wisconsin
9. 2,269.4 pounds grown by Ian and Stuart Paton of England
10. 2,261 pounds grown by Dick Wallace of Rhode Island

The largest pumpkin ever grown was done so by Mathias Willemijns of Deurle, Belgium. Willemijns’s #1 pumpkin is 96.6 pounds more than the runner-up. You’ll find this pumpkin connoisseur more than once on the map. He grew five out of the six largest Belgian pumpkins on the list and all rank within the top 50. Plus, Willemijns is just 24. His #1 ranking pumpkin claimed its title in 2016 and hasn’t been beat yet. Though not from lack of trying.

In 2018, two growers attempted to overthrow Willemijns’s #1 pumpkin (unsuccessfully) with their own massive fruits. Or are they considered vegetables? The current second and third place pumpkins of all time were both grown by Americans named Steve. Steve Geddes of Boscawen, New Hampshire grew a 2,528-pound pumpkin (#2) and Steve Daletas of Pleasant Hill, Oregon’s third-place 2,469-pound pumpkin made headlines that very same year.

The top ten largest pumpkins also have representation from Lymington, England, Clarence Center New York, Sumner, Washington, Pfugen, Switzerland, Gays Mills, Wisconsin, and Greene, Rhode Island.

Grow Big or Go Home: Growers With Many of the Largest Pumpkins

We mentioned Mathias Willemijns of Belgium has multiple pumpkins on the list of the 170 largest pumpkins of all time. However, he’s not the only grower with many of the largest pumpkins, nor does he even have the most. The following growers carved names for themselves among the top pumpkin growers in the world. Previously discussed Steve Daletas of Pleasant Hill, Oregon, Karl Haist of Clarence Center, New York, and brothers Ian and Stuart Paton of Lymington, England have each grown eight super-sized pumpkins.

As we’ve mentioned, one of Daletas’s pumpkins is currently the third-largest ever grown. This bad boy was 2,469 pounds. His other seven pumpkins range between his smallest of 1,969 pounds (ranked #80) and his second-largest of 2,170 pounds (#15). Like Daletas, Karl Haist’s eight pumpkins are all ranked in the top 100. His most notable gourd is #5, weighing in at a whopping 2,416.5 pounds. His next seven pumpkins tip the scales anywhere between 1,962 pounds and 2,027 pounds.

Brothers Ian and Stuart Paton, who have been growing pumpkins since they were 11 and run a flower nursery together, have eight pumpkins on the list. Their largest is #4 in the world at 2.433.9 pounds. They also hold the #9 spot. Though aside from two top ten pumpkins, their rankings vary greater than those of Daletas and Haist. The Paton brothers’ smallest pumpkin barely makes the top 100 at #99. Pumpkins grown by these brothers range between 1,945.6 pounds and 2,269.4 pounds. They’ve said their goal is to hold the world record one day, and they’re not too far off. The brothers noted that the first pumpkin they grew was just 54 pounds. They now watch their massive gourds grow by at least 60 pounds. each day. Watch out Willemijns, these brothers are coming for your spot!

Also worth mentioning are Dick Wallace and Josiah Brandt. These two growers are moving up in the ranks one pumpkin at a time. They each currently have five on the list, the same quantity as Willemijns.

Size Matters: 13 Pumpkins That Tied

Size matters in the pumpkin industry. One-tenth of a pound could make the difference between growing the #43 largest pumpkin in the world or being tied for the #43 largest pumpkin in the world. This is exactly the case for two familiar growers from the top ten: Ian and Stuart Paton and Mathias Willemijns. In 2016, Willemijns grew a 2,048.1 pound pumpkin. In 2017, Ian and Stuart Paton grew a pumpkin of the exact same size, right down to the .1th pound. Also tied are Bavaria’s Mario Weishaeupl and Pennsylvania’s Quinn Werner. Both of their pumpkins, Werner’s grown in 2015 and Weishaeupl’s from 2018 were 2020.5 pounds, making them tie for 51st place.

Two more familiar growers, Karl Haist and Steve Daletas are tied for the 62nd largest pumpkin ever grown. Both pumpkins weigh 2003 pounds. Two pumpkins are also tied for #63 at 2002 pounds. These belong to California’s John Hawkley and Washington’s Cindy Tobeck. Tied for #77 are two 1971.5-pound pumpkins grown by Wisconsin’s John Barlow and New York’s Andy Wolf. Last but not least is #80’s three-way tie, once again between Karl Haist, Steve Daletas, and… Karl Haist? Yes, Haist tied himself, along with Daletas (who he’s also tied with for #62). In regards to the #80 spot, Haist grew a 1969-pound pumpkin in 2014. Then, in 2015, Daletas also grew a 1969-pound pumpkin. A year later, in 2016, Haist grew another 1969-pound pumpkin. Way to give ‘em pumpkin to talk about, Haist!

---

You’ve just seen how easy it is to visualize data like the largest pumpkins when displayed in map-form. Learn more about the different ways of visualizing your data with our post on Excel data visualization. It contains examples of how to create basic data visualizations like bar and column charts, line graphs, and pie charts, as well as advanced excel data visualizations such as combined charts and graphs, stacked charts, and even more maps.