Select to view content in your preferred language

ArcGIS for Excel Function Builder - Access functions from ArcGIS toolbar

1777
2
12-08-2022 01:04 PM
MrinmayeeBharadwaj
Esri Regular Contributor
2 2 1,777

This article was last updated on 12/08/2023

ArcGIS for Excel has formulas similar to Microsoft Excel 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. Once you get acclimated to all these functions, you can easily access them from the Function Builder tool on the ArcGIS for Excel toolbar. 

Note: You can use examples from the ArcGIS for Excel functions and formulas help topic to test these functions from the Function Builder. 

  1. Open an Excel sheet. 
  2. Access the ArcGIS tab. 
  3. Click the Function Builder button. 

MrinmayeeBharadwaj_0-1670532647332.png

The Function Builder opens in the Task pane, with Choose function and Set parameters tabs visible. 

MrinmayeeBharadwaj_1-1670532647344.png

4. Click the Don’t show this again checkbox to skip this welcome screen the next time you want to access the Function Builder.  

5. Click OK. 

6. The Choose function tab is enabled by default with function drop-down lists visible. 

7. If you have chosen a populated cell, or an empty cell, that cell number will be visible in the Choose function pane description, for example G9 in the screenshot below: 

MrinmayeeBharadwaj_2-1670532647345.png

8. Choose a function you wish to run from the following options:  

MrinmayeeBharadwaj_3-1670532647347.jpeg

Note: Once you choose a function, the related Set parameters tab opens immediately. After populating all parameter fields, you may choose to Add Header, or proceed with running the function by clicking Update. 

Tip: To exit a function, click the Choose function tab. This returns you to the functions drop-down lists. 

Geocoding functions: 

Parameters: 

 

 

GETADDRESS 

Longitude(X) – required 

Latitude(Y) – required 

Spatial_reference  

All_data – type this value as “TRUE”  

geocode_service

GETCOORDINATES 

Address – required 

Spatial_reference  

Match_details  

geocode_service

 

 

 

Geoenrichment functions: 

Parameters: 

 

 

ENRICHBYPOINT 

Longitude(X) – required 

Latitude(Y) – required 

Spatial_reference  

Distance  

Unit  

Travel_mode 

Data_collections 

Variables -

MrinmayeeBharadwaj_4-1670532647349.png

 

- browse through the list of data collections variables. Click Close to exit and return to the ENRICHBYPOINT function pane. 

Include_derivative_variables 

Include_point_details 

 

ENRICHBYGEOGRAPHY 

Area_id

MrinmayeeBharadwaj_5-1670532647350.png

 

– required. Browse through the list of areas. Click

MrinmayeeBharadwaj_6-1670532647352.png

 

to return to the ENRICHBYGEOGRAPHY function pane. 

Geography_level_id

MrinmayeeBharadwaj_7-1670532647354.png

 

– required. Browse through the list of countries, and their respective geography levels. Click

MrinmayeeBharadwaj_8-1670532647355.png

 

to return to the ENRICHBYGEOGRAPHY function pane. 

Data_collections 

Variables -

MrinmayeeBharadwaj_9-1670532647357.png

 

- browse through the list of data collections variables. Click Close to exit and return to the ENRICHBYPOINT function pane. 

Include_derivative_variables 

Include_geography_details 

 ENRICHBYADDRESS

 address - Required

distance - Optional

unit - Optional

travel_mode - Optional

variables - Optional

include_derivative_variables - Optional

include_point_details - Optional

 

Routing functions: 

Parameters: 

 

 

FINDROUTE 

From_longitude(X) – required 

From_latitude(Y) – required 

To_longitude(X) – required 

To_latitude(Y) – required 

Spatial_reference 

Travel_mode

MrinmayeeBharadwaj_10-1670532647358.png

 

- browse through the list of travel modes supported for routing operations. Click

MrinmayeeBharadwaj_11-1670532647360.png

 

to return to the FINDROUTE function pane. 

Unit 

Include_directions 

 

 

FINDROUTEBYADDRESS 

From_address – required 

To_address – required 

Travel_mode

MrinmayeeBharadwaj_12-1670532647360.png

 

- browse through the list of travel modes supported for routing operations. Click

MrinmayeeBharadwaj_13-1670532647361.png

 

to return to the FINDROUTE function pane. 

Unit 

Include_directions 

 

 

 

Helper functions: 

Parameters: 

 

 

SUGGESTADDRESS 

Text – required 

Max_suggestions 

Country 

geocode_service

 

 

LISTGEOGRAPHIES 

Country 

Geography_level_id

MrinmayeeBharadwaj_14-1670532647362.png

 

– required. Browse through the list of countries, and their respective geography levels. Click

MrinmayeeBharadwaj_15-1670532647363.png

 

to return to the LISTGEOGRAPHIES function pane. 

Search_term 

exact_match

 

 

LISTDATACOLLECTIONS 

Country – required 

Data_collection_id 

Search_term 

 

 

LISTTRAVELMODES 

This function has no parameters. It is already done and displaying results in your chosen cell. 

 

 

ADDHEADERS 

ARCGIS_function - required 

 

 

 

Once your Excel sheet is populated with all desired information, do one of the following to return to the map frame: 

9. Click x  

MrinmayeeBharadwaj_16-1670532647364.png

10. Click the ArcGIS for Excel icon 

MrinmayeeBharadwaj_17-1670532647366.png

11. Click Show map 

MrinmayeeBharadwaj_18-1670532647368.png

 

 

 

2 Comments