Introduction to ArcGIS for Excel functions
In the ArcGIS for Excel 2022.2, we are delighted to introduce ArcGIS for Excel functions in the product. If you have longitude and latitude coordinates in your worksheet and want to convert them into addresses, now you can work directly inside Excel to figure out the addresses of your XY locations. You can use the ArcGIS for Excel functions to determine their associated addresses within the Excel. The best part is ArcGIS for Excel functions output data is added to the new columns in your worksheet. Utilizing ArcGIS for Excel functions, you can perform geoenrichment, geocoding, and routing operations.
ARCGIS.GETADDRESS function converts longitude and latitude coordinates to a physical address. ARCGIS.GETCOORDINATES function determines the Longitude and latitude of an address.
You can use the ARCGIS.FINDROUTE function to find the total travel distance between two XY locations or use the ARCGIS.FINDROUTEBYADDRESS to find the total travel distance between two addresses.
ARCGIS.ENRICHBYPOINT function helps to find the demographic, business, landscape, etc., information within the area around a point on the map. ARCGIS.ENRICHBYGEOGRAPHY function is used to get information about the people, places, and businesses in a specific area.
Note – Before using the ArcGIS for Excel function, ensure that you are Sign-in to ArcGIS.
ARCGIS.GETADDRESS Function –
This function creates addresses from the XY location. To use this function, you have to type ‘=arcgis’ in the empty cell in your worksheet. The drop-down list will display a list of all the supported ArcGIS functions. From that drop-down list, select the ArcGIS.GETADDRESS function and function contains four longitude(X), latitude(Y), [spatial_reference], [all_data] parameters.
The first two, longitude and latitude, are mandatory parameters out of these four. All the optional parameters are in the square brackets. And for longitude and latitude parameters, enter the values manually or select cell references containing longitude and latitude values, and press enter. You will notice that addresses pertaining to longitude and latitude get added to the worksheet in the new column. To apply the function to the entire column, you have to select the cell where you have input the function, place the cursor on the bottom right corner, and drag the Fill handle tool to those cells where you want to apply the formula. You can also add a header to your ARCGIS.GETADDRESS function output. For that, you have to use another ArcGIS for Excel function – ‘ARCGIS.ADDHEADERS’. ARCGIS.ADDHEADERS function has only one mandatory parameter, and that refers to a cell reference to another ArcGIS Excel function. Go one row above the cell where you have input the ArcGIS.GETADDRESS function, type ‘=ARCGIS.ADDHEADERS’, and for the parameter, select the cell where you have inserted the ArcGIS.ADDRESS function. You will see the header is added to the newly added column, which was generated using ARCGIS.GETADDRESS function.
To convert Addresses to longitude and latitude, use the ARCGIS.GETCOORDINATES function.
ARCGIS.FINDROUTE Function –
Interested in finding the total distance and time travel between the two XY locations, ARCGIS.FINDROUTE function is the way to go! In the function for the mandatory arguments, you have to pass the XY coordinates of the starting location of the route and XY coordinates of the destination location of the route.
To find the total travel distance and time between the two addresses, use ARCGIS.FINDROUTEBADDRESS function.
ARCGIS.ENRICHBYGEOGRAPHY Function –
Curious to know more about your locations. ARCGIS.ENRICHGEOGRAPHY function allows you to answer questions about the locations you couldn’t answer with the map alone, such as the total population living around the location? What kind of people live? What is the crime index of the location? What kind of businesses are in the area? The location could be any administrative boundaries or level of geographies, for example, Districts, States, Provinces, Block Groups, Census Tracts, Census Districts, Municipalities, Zip codes, Postal codes, Departements, Statistical Areas, Regions, Subregions, Emirates, Governorates, Sectors, Prefecture, etc. You can enrich your worksheet with demographics, business, health, crime, education, landscape, and policy data variables to the new columns as contextual data on your worksheet. These data variables help better understand the area around your locations.
The below video demonstrates how the crime data variables associated with the US States are added to new columns on the worksheet using the ARCGIS.ENRICHBYGEOGRAPHY function.
To enrich your worksheet with demographic information about the people, places, and businesses from an XY location, use ARCGIS.ENRICHBYPOINT function.
ArcGIS for Excel functions also support helper functions such as ARCGIS.LISTGEOGRAPHIES, ARCGIS.LISTDATACOLLECTIONS, ARCGIS.SUGGESTADDRESS, ARCGIS.LISTTRAVELMODES: To learn more about the ArcGIS for Excel helper functions and function parameters, please check the documentation of ArcGIS for Excel functions.
Much awaited request of adding a map in Excel in Web browsers is fulfilled in ArcGIS For Excel 2022.2 release. You can now add a map in Excel for Desktop and also in Excel Online. ArcGIS for Excel 2022.2 is supported in both Windows and Mac OS. Please try the 2022.2 release yourself and provide us your feedback in the ArcGIS for Excel community channel.