Find Locations Fast with ArcGIS Functions in Microsoft Excel

Jordan_Brasher · ‎03-24-2023

Do you want to generate a list of locations by address, such as competitor’s stores, and put them on a map? Using ArcGIS custom functions in Excel make it easy!

Why use ArcGIS for Excel to find locations?

Put simply, it’s more efficient. You don’t need to scour the web for third party data or manually build your own location list. You can generate a list of locations in seconds and put places on a map in a snap without leaving the familiar Excel environment.

The second reason to use ArcGIS for Excel is formatting. Data you download from a third party may not be structured appropriately for mapping. Location lists generated in ArcGIS for Excel don’t need to be sliced and diced. They are ready for mapping without time- and labor-intensive data restructuring.

What are ArcGIS functions for Excel?

Most people who work with data in Excel have used a function or formula. Functions are preset formulas that begin with an equal sign and help perform mathematical, statistical, and logical operations. For example, =sum and =count are some of the most used functions in Excel.

As of 2022, there are now ArcGIS-specific (i.e., custom) functions in Excel that you can use to geocode or Geoenrich data and perform basic spatial analysis such as finding the best route or calculating drive time. ArcGIS custom formulas all begin with =arcgis. In what follows, I describe how to generate a list of locations with the =arcgis.suggestaddress function and prepare them for mapping with the =arcgis.getcoordinates and =arcgis.addheaders functions.

For a more comprehensive overview of ArcGIS custom functions in Excel, check out this blog post and help documentation. Note that using ArcGIS functions does consume ArcGIS Online credits and requires the user to be logged into an ArcGIS organizational account.

Traditional Formulas versus the ArcGIS Function Builder

Traditional functions or formulas are equations you type directly into an Excel cell. These work well for shorter formulas, but longer ones, or those that require inserting parameters from different Excel worksheets, can become complicated. You must type the syntax perfectly or the formula won’t work (i.e., using quotation marks around words, separating parameters with commas). One way to make the user experience friendlier for ArcGIS functions is using the built-in function builder under the ArcGIS tab in Excel.

Tip: For instructions on how to download the ArcGIS for Excel add-in, see my previous post or visit the Get ArcGIS for Excel Help documentation.

The function builder allows you to enter the parameters for the function without worrying about the formula’s syntax being correct. You can type in the cell numbers and choose options from pick lists of available parameters to build your function.

In the example below, the function =ARCGIS.ENRICHBYPOINT(C2, D2, , 1, "Miles", , "shopping", "MP08006a_B") produces the same result as entering the parameters in the function builder.

Scenario: Coffee Competitor Analysis

Now, let’s imagine a scenario. A coffee company wants to build a new store in Des Moines, Iowa. The company wants to generate a list of coffee competitors' locations for its competitor analysis.

Find Locations with the =arcgis.suggestaddress function

To do this, open a blank Excel sheet and type the name, city, and state of the three competitors into separate cells (i.e., Store 1, City, State).

Now, use the text in these cells in an =arcgis.suggestaddress formula (as shown below) to search for the addresses of these stores.

The formula used to generate the addresses looks something like this:

=ARCGIS.SUGGESTADDRESS(C2, 100). 100 corresponds to the maximum suggestions parameter to limit the number of search results returned. Note that if you don’t enter a number for this parameter, the function will return only 5 results by default to limit the number of credits used.

Tip: Copy and Paste123 the cells into a new column or sheet to separate the values from the array that the formula automatically groups them into.

Perform your own QC

Next, you’ll need to perform your own quality control (QC) on the results returned. For example, some of the results for one store returned addresses in Urbandale and Pleasant Hill, Iowa, technically outside the Des Moines, Iowa city limits in a nearby suburb. Do you want to include this data in your competitor analysis? It’s up to you to decide.

Put places on a map using the =arcgis.getcoordinates function

Next, you want to turn these rows in the Excel sheet into places you can put on a map. There’s a function for that!

I’ll delete the contents of the cells where the store name, city, and state were typed and click on cell B2 to begin generating the longitude and latitude coordinates for the addresses in our new list.

Then, I’ll start with =ARCGIS.GETCOORDINATES(A2) to get the coordinates for the address in cell A2. This generates the longitude and latitude coordinates for that address in column B and C respectively. Then, simply drag the corner of the cell to the bottom of the list to automatically generate the coordinates for the rest of the list.

Add column headers using the =arcgis.addheaders function

Finally, you need to add some column headers to your coordinate columns so they can be geocoded. This one is extra simple and requires using =arcgis.addheaders in cell B1. The formula reads the data you just created and generates the column headers automatically.

You now have location data you can put on a map in Excel. If you wanted to add coffee consumption behavior data to these store locations, you could do so with =arcgis.enrichbypoint or add your own data.

The final step to take is to turn your Excel sheet into a table and add it to a map for visualization, which I explain how to do in my previous blog post.

Happy mapping! Enjoy finding your locations fast and share your experiences using ArcGIS Excel functions in the replies at the bottom of this post.

Resources List