Skip To Content

ArcGIS for Excel custom functions

Microsoft Excel includes a set of functions or formulas that start with the equal sign (=) that you can use to calculate information, from simple arithmetic sums to complex population of cells. ArcGIS for Excel has similar customized formulas that you can use to geocode, geoenrich, and route operations. Each function starts with the equal sign (=) in a cell followed by the function name and the cell range it applies to.

Tip:

As you are writing the custom function in the cell, you can also select a specific cell or cells in the column or row, and the custom function automatically populates with the selection.

You can copy the examples in this topic into an Excel cell or cells to better understand how custom functions work.

The following sections describe the ArcGIS for Excel functions along with the syntax, an example, and details about what the function parameters mean.

GETADDRESS

Determine the address at a particular longitude/latitude location through reverse geocoding.

Syntax

ARCGIS.GETADDRESS(longitude(X), latitude(Y), [spatial_reference], [all_data])

Example

=ARCGIS.GETADDRESS(-70.251, 43.65808,"4326")

Parameters

ParameterDescription

longitude(X)

The longitude of the point from which to search for the closest address. This parameter is required.

latitude(Y)

The latitude of the point from which to search for the closest address. This parameter is required.

spatial reference

The spatial reference for the point from which to search for the closest address, either a WKID or WKT. To specify the latest WKID rather than the original WKID of a spatial reference, the format is "latestWkid=YOUR_WKID". This parameter is optional.

all_data

If set to TRUE, this returns not just the reverse-geocoded address name but also all address properties such as "City" or "Region". This parameter is optional.

GETCOORDINATES

Geocode a location. This returns possible locations including longitude, latitude, and, optionally, the address matched and the match score.

Syntax

ARCGIS.GETCOORDINATES(address, [spatial_reference], [country], [magic_key], [match_details])

Example

=ARCGIS.GETCOORDINATES("380 New York Street",,"USA")

Parameters

ParameterDescription

address

The location to be geocoded. This can be a street address, place-name, postal code, or POI. This parameter is required.

spatial reference

The spatial reference for the point from which to search for the closest address, either a WKID or WKT. It is used to specify the latest WKID rather than the original WKID. This parameter is optional.

country

The country to be geocoded, for example, "USA", "Canada," and so on. This parameter is optional.

magic_key

Results are retrieved quicker when the corresponding magic key to the location is specified. Use custom function ARCGIS.SUGGEST to get the magic key. This parameter is optional.

match_details

If set to TRUE, this returns not just the longitude and latitude but also the address name of the matched point and the match score. This parameter is optional.

ENRICHBYGEOGRAPHY

Get enriched demographic information about the people, places, and businesses in a specific area or within a certain distance or drive time from a location. The default distance is within a one-mile radius of the point.

Syntax

ARCGIS.ENRICHBYGEOGRAPHY(area_id, geography_type_id, [data_collections], [variables], [include_derivative_variables], [include_geography_details])

Example

=ARCGIS.ENRICHBYGEOGRAPHY("06","US.States","health")

=ARCGIS.ENRICHBYGEOGRAPHY("08019","ES.Municipalities")

Parameters

ParameterDescription

area_id

The ID of the area where demographic information will be obtained, for example, "06" for the State of California. See the LISTGEOGRAPHIES parameter section in this topic to request a list of area IDs for a particular geography level. This parameter is required.

geography_type_id

The ID of the geography level to which the area ID belongs, for example, "US.States" for the states of the United States. See the LISTGEOGRAPHIES parameter section in this topic to request a list of area IDs for a particular geography level. This parameter is required.

data_collections

Preassembled lists of attributes that will be used to enrich the input features, for example, "KeyUSFacts". The default is "KeyGlobalFacts". This parameter is optional.

variables

Specify a subset of variables to be returned from the data collections. Many comma-delimited variables can be specified. If possible, use the notation "DataCollectionId.variableId". See the LISTDATACOLLECTIONS section in this topic for a list of variable IDs. This parameter is optional.

include_derivative_variables

Derivative variables to include in the output, for example, percent, index, average, or all. This parameter is optional.

include_geography_details

Set to TRUE to include the associated geography details such as country code, geography level and area ID. This parameter is optional.

ENRICHBYPOINT

Get enriched demographic information about the people, places, and businesses in a specific area or within a certain distance or drive time from an x,y location. The default distance is within a one-mile radius of the point.

Syntax

ARCGIS.ENRICHBYPOINT(longitude(X), latitude(Y), [spatial_reference], [distance], [unit],[travel_mode],[data_collections],[variables],[include_derivative_variables], [include_point_details])

Example

=ARCGIS.ENRICHBYPOINT(-90.491,38.777,,5,"miles","driving")

Parameters

ParameterDescription

longitude (X)

The longitude of the point of interest. This parameter is required.

latitude (Y)

The latitude of the point of interest. This parameter is required.

spatial_reference

The spatial reference of the input longitude-latitude coordinates. This parameter is optional.

distance

The range as a number to compute the area for which the demographic data is found, for example, 3 for a three-mile radius. This only applies to points and polylines. This parameter is optional.

unit

The distance units. The units can be meters, kilometers, feet, yards, miles, or nautical miles. If travelMode is set, the units can also be seconds, minutes, or hours. This parameter is optional.

travel_mode

Instead of a ring buffer area, compute an area using a travel mode, for example, walking, driving, or trucking. This parameter is optional.

data_collections

Preassembled lists of attributes that will be used to enrich the input features, for example, \"KeyUSFacts\". Several comma-delimited data collections can be specified. The default is \"KeyGlobalFacts\". See the LISTDATACOLLECTIONS section in this topic for a list of data collections. This parameter is optional.

variables

Specify a subset of variables to be returned from the data collections. Many comma-delimited variables can be specified. If possible, use the notation "DataCollectionId.variableId". See the LISTDATACOLLECTIONS section in this topic for a list of variable IDs. This parameter is optional.

include_derivative_variables

Derivative variables to include in the output, for example, percent, index, average, or all. This parameter is optional.

include_point_details

Set to TRUE to include the associated point details, such as the radius and distance measurement method used to compute the relevant area around the point. This parameter is optional.

FINDROUTE

Find the total travel distance (in miles if not overridden by "units") and time (in minutes) between two x,y locations using the best route found.

Syntax

ARCGIS.FINDROUTE(from_longitude(X), from_latitude(Y), to_longitude(X), to_latitude(Y), [spatial_reference], [travel_mode], [unit], [include_directions])

Example

=ARCGIS.FINDROUTE(-77.24488778,38.92894278,-80.78300851,35.1001845,,"Driving Distance","miles",TRUE)

Parameters

ParameterDescription

from_longitude(X)

The longitude coordinates of the starting location of the route. This parameter is required.

from_latitude(Y)

The latitude coordinates of the starting location of the route. This parameter is required.

to_longitude(X)

The longitude coordinates of the destination location of the route. This parameter is required.

to_latitude(Y)

The latitude coordinates of the destination location of the route. This parameter is required.

spatial_reference

The spatial reference of the input longitude-latitude coordinates. This parameter is optional.

travel_mode

Instead of a ring buffer area, compute an area using a travel mode, for example, walking, driving, or trucking. This parameter is optional.

unit

The distance units. The units can be meters, kilometers, feet, yards, miles, or nautical miles. If travelMode is set, the units can also be seconds, minutes, or hours. This parameter is optional.

include_directions

Include all steps of the directions for the best route found, as well as the distance traveled at each step. This parameter is optional.

FINDROUTEBYADDRESS

Find the total travel distance (in miles if not overridden by "units") and time (in minutes) between two addresses using the best route found.

Syntax

ARCGIS.FINDROUTEBYADDRESS(from_address, to_address, [spatial_reference], [travel_mode], [unit], [include_directions])

Example

=ARCGIS.FINDROUTEBYADDRESS("380 New York Street Redlands CA","Downtown Riverside",,"Driving Distance","kilometers",TRUE)

Parameters

ParameterDescription

from_address

The address of the starting location of the route. This parameter is required.

to_address

The address of the destination location of the route. This parameter is required.

spatial_reference

The spatial reference of the input longitude-latitude coordinates. This parameter is optional.

travel_mode

Instead of a ring buffer area, compute an area using a travel mode, for example, walking, driving, or trucking. This parameter is optional.

unit

The distance units. The units can be meters, kilometers, feet, yards, miles, or nautical miles. If travelMode is set, the units can also be seconds, minutes, or hours. This parameter is optional.

include_directions

Include all steps of the directions for the best route found, as well as the distance traveled at each step. This parameter is optional.

LISTDATACOLLECTIONS

List the data collections available for a country.

Syntax

ARCGIS.LISTDATACOLLECTIONS(country, [data_collection_id], [search_term])

Example

=ARCGIS.LISTDATACOLLECTIONS("USA")

=ARCGIS.LISTDATACOLLECTIONS("USA","health")

=ARCGIS.LISTDATACOLLECTIONS("USA","health",2019)

=ARCGIS.LISTDATACOLLECTIONS("Spain","KeyFacts")

Parameters

ParameterDescription

country

The country name. This parameter is required.

data_collection_id

If specified, lists the available variables for the data collection. This parameter is optional.

search_term

A search term to narrow the list of data collections. This parameter is optional.

LISTGEOGRAPHIES

Return a list of available geography levels available for a country. There is a limit of 5,000 maximum number of features.

Syntax

ARCGIS.LISTGEOGRAPHIES([country], [geography_type_id], [search_term])

Example

=ARCGIS.LISTGEOGRAPHIES()

=ARCGIS.LISTGEOGRAPHIES("USA")

=ARCGIS.LISTGEOGRAPHIES("Spain","ES.Municipalities")

=ARCGIS.LISTGEOGRAPHIES("Spain","ES.Municipalities","Barcelona")

Parameters

ParameterDescription

country

If provided, returns a list of geography levels for the country, such as district, state, metro area, and postal codes. This parameter is optional.

geography_type_id

The ID of a geography level, for example, "US.States" for the states of the United States. If provided, it returns a list of areas and their IDs, such as "California" and "New York". If omitted, a list of available geography levels for the country is provided. This parameter is optional.

search_term

A search term to narrow the results. This parameter is optional.

SUGGESTADDRESS

Suggest addresses based on a text input. This can be used as an interactive autocomplete address search.

Syntax

ARCGIS.SUGGESTADDRESS(text, [max_suggestions], [country], [include_magic_key])

Example

=ARCGIS.SUGGESTADDRESS("Orange county",10)

Parameters

ParameterDescription

text

The input text entered by a user, which is used to generate a list of possible matches. This parameter is required.

max_suggestions

The maximum number of suggestions to be returned, up to 15. The default is 5. This parameter is optional.

country

If provided, returns a list of geography levels for the country, such as district, state, metro area, and postal codes. This parameter is optional.

include_magic_key

Set to TRUE to include the magic key as a second column in the result. This is an ID attribute value that links a suggestion to an address or place. This parameter is optional.

LISTTRAVELMODES

List all travel modes supported by ArcGIS Online or configured for your organization that can be used with routing functions.

Note:

This function requires no parameters.

Syntax

ARCGIS.LISTTRAVELMODES()

Example

=ARCGIS.LISTTRAVELMODES()

ADDHEADERS

Create a header row only for the output of ArcGIS for Excel custom functions: GETADDRESS, GETCOORDINATES, ENRICHBYGEOGRAPHY, ENRICHBYPOINT, FINDROUTE, FINDROUTEBYADDRESS.

Syntax

ARCGIS.ADDHEADERS(ARCGIS_function)

Parameters

ParameterDescription

ARCGIS_function

Input a cell reference to another ArcGIS for Excel function where you want to add headers. This parameter is required.