Skip To Content

ArcGIS for Excel custom functions

Note:

You must sign into ArcGIS to access the custom ArcGIS for Excel 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])

Parameters

ParameterDescriptionParameter required

longitude(X)

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

Yes

latitude(Y)

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

Yes

spatial reference

The spatial reference for the point location. This can be either a WKID (number) or WKT (text). This parameter is optional.

For a list of valid WKID values, see Projected coordinate systems and Geographic coordinate systems. The default is 4326 (WGS84)

No

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." The default is FALSE.

No

Function example

=ARCGIS.GETADDRESS(-70.251, 43.65808)

Result

Address

Standard Baking, 75 Commercial St, Portland, ME, 04101, USA

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

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

Result

Address MatchedFull Address NameShort Address NameAddress TypeType Place NameStreet NumberStreet NameBlockSectorNeighborhoodDistrictCityMetropolitan AreaSub-regionRegionRegion AbbreviationTerritoryPostal CodePostal Code ExtensionCountryCountry Code

Standard Baking

Standard Baking, 75 Commercial St, Portland, ME, 04101, USA

Standard Baking

POI

Bakery

Standard Baking

75

75 Commercial St.

Portland

Cumberland county

Maine

ME

04101

United States

USA

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

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], [match_details])

Parameters

ParameterDescriptionParameter required

address

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

Yes

spatial reference

The spatial reference for the point location. This can be either a WKID (number) or WKT (text). This parameter is optional.

For a list of valid WKID values, see Projected coordinate systems and Geographic coordinate systems. The default is 4326 (WGS84).

No

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. Default is FALSE.

No

Function example

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

Result

Longitude (X)Latitude (Y)

-117.19479

34.057265

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

=ARCGIS.GETCOORDINATES("380 New York Street, Redlands, CA, USA", 4326 , TRUE)

Result

Longitude (X)Latitude (Y)AddressMatch ScoreGeolocatorAddress type

-117.1948

34.057265

380 New York St, Redlands, CA, 92373, USA

100

World

PointAddress

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

ENRICHBYGEOGRAPHY

Get information about the people, places, and businesses in specific named administrative boundaries, or levels of geography, of a map such as districts, states and postal codes.

Syntax

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

Parameters

ParameterDescriptionParameter required

area_id

The FIPS ID of the area where demographic information will be obtained. Run the LISTGEOGRAPHIES function to request a list of area IDs for a particular geography level. For example, "06" is for the State of California.

Yes

geography_level_id

The ID of the geography level to which the area ID belongs. Run the LISTGEOGRAPHIES function to request a list of area IDs for a particular geography level, or check the list of geography levels here. For example, "US.States" is for the states of the USA.

Yes

data_collections

Preassembled lists of attributes that will be used to enrich the input features. This parameter is optional.

Preassembled lists of attributes that will be used to enrich the input features. Run the LISTDATACOLLECTIONS function to request a list of data collections. An example is "KeyUSFacts, KeyGlobalFacts." The default is "KeyGlobalFacts."

No

variables

A subset of variables to be returned from the data collections. Run the LISTDATACOLLECTIONS function to request a list of variables, or check the list of variables here. An example is "KeyGlobalFacts.TOTPOP, AGE.MALE5." If left blank, all variables of the data collection are requested.

No

include_derivative_variables

Derivative variables to include in the output. This can be "percent," "index," "average," or "all." If left blank, no derivative variable will be included.

No

include_geography_details

If set to TRUE, this will include the associated geography details, such as country code, geography level, and area ID. The default is FALSE.

No

Function example

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

Result

Total PopulationTotal HouseholdsAverage Household SizeMale PopulationFemale Population

39770476

13570050

2.86

19767455

20003021

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

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

Result

2020 Total Household Income Per Year 2020 Average Household Income Per Year 2020 Average Household Income Per Month 2020 Average Annual Income Per Capita 2020 Average Monthly Income Per Capita

1.99E + 10

29898.9

2491.57

11938.06

994.84

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

=ARCGIS.ENRICHBYGEOGRAPHY("08019","ES.Municipalities", , "KeyGlobalFacts.AVGHHSZ, IncomeTotalsAIS.NINCHA")

Result

Average Household Size 2020 Average Household Income Per Year

2.5

29898.9

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

=ARCGIS.ENRICHBYGEOGRAPHY("06","US.States","health", "X8001_X, X8021_X", "index", TRUE)

Result

Standard geography level Standard geography name Standard geography ID Country code 2022 Health Care 2022 Health Care: Index 2022 Dental Services 2022 Dental Services: Index

U.S. States

California

06

US

1.08894E+11

113

7.37E+09

119

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

ENRICHBYPOINT

Get enriched demographical 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.ENRICHBYPOINT(longitude(X), latitude(Y), [spatial_reference], [distance], [unit],[travel_mode],[data_collections],[variables],[include_derivative_variables], [include_point_details])

Parameters

ParameterDescriptionParameter required

longitude (X)

The longitude of the point of interest.

Yes

latitude (Y)

The latitude of the point of interest.

Yes

spatial_reference

The spatial reference for the point location. This can be either a WKID (number) or WKT (text). This parameter is optional.

For a list of valid WKID values, see Projected coordinate systems and Geographic coordinate systems. The default is 4326 (WGS84).

No

distance

The radius or distance from the point, which will be used to compute the ring buffer area for the demographical data. For example, use 3 for a three-mile radius. The default is 1.

No

unit

The unit of measurement of the "distance" parameter. This can be "meters," "kilometers," "feet," "yards," "miles," or "nautical miles." If the "travel_mode" parameter is set, this can also be "seconds," "minutes," or "hours." The default is "miles."

No

travel_mode

If this is set, it will compute an area using travel distance from the point of interest. This can be "walking," "driving," or "trucking." If left blank, a ring buffer area will be computed.

No

data_collections

Preassembled lists of attributes that will be used to enrich the input features. This parameter is optional.

Preassembled lists of attributes that will be used to enrich the input features. Run the LISTDATACOLLECTIONS function to request a list of data collections. For example, use "KeyUSFacts, KeyGlobalFacts." The default is "KeyGlobalFacts."

No

variables

A subset of variables to be returned from the data collections. Run the LISTDATACOLLECTIONS function to request a list of variables, or check the list of variables here. For example, "KeyGlobalFacts.TOTPOP, AGE.MALE5." If left blank, all variables of the data collection are requested.

No

include_derivative_variables

Derivative variables to include in the output. This can be "percent," "index," "average," or "all." If left blank, no derivative variable will be included.

No

include_point_details

If set to TRUE, this will include the associated point details, such as the radius and distance measurement method used to compute the relevant area around the point. The default is FALSE.

No

Function example

=ARCGIS.ENRICHBYPOINT(-90.491, 38.777)

Result

Total PopulationTotal HouseholdsAverage Household SizeMale PopulationFemale Population

11464

4860

1.98

5745

5719

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

=ARCGIS.ENRICHBYPOINT(-90.491, 38.777, 4326, 5, "miles", "driving", "health", "X8001_X, X8021_X", "index", TRUE)

Result

Country CodeArea TypeBuffer Units AliasBuffer Radii2022 Healthcare2022 Healthcare: Index2022 Dental Services2022 Dental Services: Index

US

NetworkServiceArea

Drive Distance Miles

5

240971277

99

15463985

99

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

FINDROUTE

Find the total travel distance (in miles if not overridden by "unit") and time (in minutes) between two 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])

Parameters

ParameterDescriptionParameter required

from_longitude(X)

The longitude coordinates of the starting location of the route.

Yes

from_latitude(Y)

The latitude coordinates of the starting location of the route.

Yes

to_longitude(X)

The longitude coordinates of the destination location of the route.

Yes

to_latitude(Y)

The latitude coordinates of the destination location of the route.

Yes

spatial_reference

The spatial reference for the point location. This can be either a WKID (number) or WKT (text). This parameter is optional.

For a list of valid WKID values, see Projected coordinate systems and Geographic coordinate systems. The default is 4326 (WGS84).

No

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.

The travel mode for the route. For example, use "Driving Distance" or "Walking Time." Run the LISTTRAVELMODES function to request a list of supported travel modes. If left blank, this will use the default travel mode specified in your ArcGIS organization settings.

Learn more about travel modes

No

unit

The unit of measurement used for the travel distance. This can be "miles," "kilometers," "meters," "decimeters," "centimeters," "millimeters," "feet," "inches," "points," "yards," "decimalDegrees," or "nauticalMiles." The default is "miles."

No

include_directions

If set to TRUE, this will include the routing directions to the destination, as well as the time and distance travelled at each step.Default is FALSE.

No

Function example

=ARCGIS.FINDROUTE(-77.24488778, 38.92894278, -80.78300851, 35.1001845)

Result

Distance Travelled (Miles)Time Travelled (Minutes)

391.3764

401.6001295

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

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

Result

Distance Travelled (Miles)Time Travelled (Minutes)

Total Distance/Time Travelled

382.7767

408.1581

Start at Location 1

0

0

Go East

0.03108

0.300162

Turn right on Springbank Ln

0.087211

0.421049

Finish at Location 2, on the left

0

0

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, [travel_mode], [unit], [include_directions])

Parameters

ParameterDescriptionParameter required

from_address

The address of the starting location of the route.

Yes

to_address

The address of the destination location of the route.

Yes

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.

The travel mode for the route. For example, use "Driving Distance" or "Walking Time." Run the LISTTRAVELMODES function to request a list of supported travel modes. If left blank, this will use the default travel mode specified in your ArcGIS organization settings.

Learn more about travel modes

No

unit

The unit of measurement used for the travel distance. This can be "miles," "kilometers," "meters," "decimeters," "centimeters," "millimeters," "feet," "inches," "points," "yards," "decimalDegrees," or "nauticalMiles." The default is "miles."

No

include_directions

If set to TRUE, this will include the routing directions to the destination, as well as the time and distance travelled at each step.Default is FALSE.

No

Function example

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

Result

Distance Travelled (Miles)Time Travelled (Minutes)

14.32662

20.73435

Note:

The actual result of this function excludes the header. Run the ADDHEADERS function to request the headers.

Function example

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

Result

Distance Travelled (Kilometers)Time Travelled (Minutes)

Total Distance/Time Travelled

22.9899

22.40177

Start at Location 1

0

0

Go Southwest

0.092545

0.555303

At the traffic light, turn right on University Ave

0.427128

0.672165

Finish at Location 2, on the left

0

0

LISTDATACOLLECTIONS

List the data collections available for a country.

Syntax

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

Parameters

ParameterDescriptionParameter required

country

The source country of the data collections. For example, use "United Kingdom." Run the LISTGEOGRAPHIES function to request a list of countries.

Yes

data_collection_id

The ID of a data collection. For example, use "KeyFacts." If specified, all variables of that data collection are listed. If left blank, all data collections for the specified country are listed.

No

search_term

A search term that narrows down the results by matching the search term with the data collection names and descriptions, or variable names and descriptions. For example, use "fast food."

No

Function example

=ARCGIS.LISTDATACOLLECTIONS("USA")

Result

IDDescriptionExample Data

1yearincrements

The 1 Year Increments data collection includes population counts by 1-year age breaks up to age 84 for 2022 and 2027. The collection also includes 2010 population by single year increments up to age 21. The data tables include age for total population and age by gender. This collection contains data from Esri's 2022/2027 Updated Demographic database and Esri's Census 2010 database.

2022 Population Age <1, 2022 Population Age 1, 2022 Population Age 2

5yearincrements

The 5 Year Increments data collection includes population counts by 5-year age breaks for 2022, 2027, and 2010. The data tables include age for the total population and age by gender. This collection contains data from Esri's 2022/2027 Updated Demographic database, American Community Survey (ACS) database, and Census 2010 database.

2022 Population Age 0-4, 2022 Population Age 5-9, 2022 Population Age 10-14

Age

This data collection includes age range data for males and females used for data pyramid info-graphics. This collection contains Esri 2022 Updated Demographic data.

  • 2022 Males Age 0-4
  • 2022 Males Age 5-9
  • 2022 Males Age 10-14

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

Result

IDNameDescription

REL65HI2OC

2020 Pop 65+: Other Health Ins Combos REL (ACS 5-Yr)

2020 Population 65+: Other Health Ins Combinations REL (ACS 5-Yr)

ACSCIVNINS

2020 Civ Noninstitutionalized Population (ACS 5-Yr)

2020 Civilian Noninstitutionalized Population (ACS 5-Yr)

MOECIVNINS

2020 Civ Noninstitutionalized Population MOE (ACS 5-Yr)

2020 Civilian Noninstitutionalized Population MOE (ACS 5-Yr)

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

Result

IDNameDescription

ACSCIVNI0

2020 Civ Noninstitutionalized Pop <19 (ACS 5-Yr)

2020 Civilian Noninstitutionalized Pop <19 (ACS 5-Yr)

ACS0ONEHI

2020 Pop <19: 1 Type of Health Ins (ACS 5-Yr)

2020 Population <19: One Type of Health Insurance (ACS 5-Yr)

MOE0ONEHI

2020 Pop <19: 1 Type of Health Ins MOE (ACS 5-Yr)

2020 Population <19: One Type of Health Insurance MOE (ACS 5-Yr)

LISTGEOGRAPHIES

Depending on input parameters, displays a list of countries, a list of geography levels (for example, districts or provinces) of a country, or a list of the area IDs of a geography level. Note that the maximum limit is 5,000 list items.

Syntax

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

Parameters

ParameterDescriptionParameter required

country

The source country of the data collections. For example, use "United Kingdom." If specified, all geography levels for that country are listed. If left blank, all countries and territories are listed.

No

geography_level_id

The ID of a geography level. For example, use "US.States" for the states of the USA. If specified, all areas of a geography level, such as "California," and their FIPS IDs are listed. If left blank, the results will depend on the "country" parameter.

No

search_term

A search term that narrows down the results by matching the search term with the geography names.

No

Function example

=ARCGIS.LISTGEOGRAPHIES()

Result

CountryCountry Code

Albania

AL

Algeria

DZ

Andorra

AD

Angola

AO

Anguilla

AI

=ARCGIS.LISTGEOGRAPHIES("USA")

Result

IDName

US.WholeUSA

Entire Country

US.States

States

US.DMA

DMAs

US.CD

Congressional Districts

US.CBSA

CBSAs

US.Counties

Counties

US.CSD

County Subdivisions

US.ZIP5

Zip Codes

US.Places

Cities and Towns (Places)

US.Tracts

Census Tracts

US.BlockGroups

Block Groups

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

Result

IDNameMajor Geography Subdivision

01001

Alegría-Dulantzi

País Vasco

01002

Amurrio

País Vasco

01003

Aramaio

País Vasco

01004

Artziniega

País Vasco

01006

Armiñón

País Vasco

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

IDNameMajor Geography Subdivision

08019

Barcelona

Cataluña

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])

Parameters

ParameterDescriptionParameter required

text

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

Yes

max_suggestions

The maximum number of suggestions to be returned. If this number is greater than 15, the suggestions will be made by the geocoding operation, which may consume more credits and may lead to different results. The default is 5.

No

country

Limits the results to a particular country. For example, use "United Kingdom." Run ARCGIS.LISTGEOGRAPHIES to request a list of countries.

No

Function example

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

Result

Orange County, CA, USA

Orange County, FL, USA

Orange County, IN, USA

Orange County, NC, USA

Orange County, NY, USA

Orange County, TX, USA

Orange County, VA, USA

Orange County, VT, USA

Orange County Great Park, Irvine, CA, USA

Orange County Airport, 500 Dunn Rd, Montgomery, NY, 12549, USA

LISTTRAVELMODES

List all travel modes of the route service supported by ArcGIS Online or configured for your organization.

Note:

This function requires no parameters. For a similar example, see Microsoft RAND function.

Syntax

ARCGIS.LISTTRAVELMODES()

Function example

=ARCGIS.LISTTRAVELMODES()

Result

NameDescriptionDetails

Driving Time

Models the movement of cars and other similar small automobiles, such as pickup trucks, and finds solutions that optimize travel time. Travel obeys one-way roads, avoids illegal turns, and follows other rules that are specific to cars. When you specify a start time, dynamic travel speeds based on traffic are used where it is available.

Avoid Carpool Roads; Avoid Express Lanes; Avoid Gates; Avoid Private Roads; Avoid Unpaved Roads; Driving an Automobile; Roads Under Construction Prohibited; Through Traffic Prohibited; Vehicle Maximum Speed (km/h): TravelTime 0

Walking Time

Follows paths and roads that allow pedestrian traffic and finds solutions that optimize travel time. The walking speed is set to 5 kilometers per hour.

Avoid Private Roads; Avoid Roads Unsuitable for Pedestrians; Preferred for Pedestrians; Walking Speed (km/h): WalkTime 5; Walking

Walking Distance

Follows paths and roads that allow pedestrian traffic and finds solutions that optimize travel distance.

Avoid Private Roads; Avoid Roads Unsuitable for Pedestrians; Preferred for Pedestrians; Walking Speed (km/h): WalkTime 5; Walking

ADDHEADERS

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

Note:

This function requires only one parameter.

Syntax

ARCGIS.ADDHEADERS(ARCGIS_function)

Parameters

ParameterDescriptionParameter required

ARCGIS_function

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

Yes

Function example

=ARCGIS.ADDHEADERS(A2)

This example will work if you have another function in cell A2. For example, if cell A2 contains the function =ARCGIS.GETCOORDINATES("380 New York Street, Redlands, CA, USA").

Result

Longitude (X)

Latitude (Y)