Get started with ArcGIS for Excel

ArcGIS for Excel adds a geographic component to the data analysis capabilities of Microsoft Office. In this tutorial, you'll create a map in Microsoft Excel based on a spreadsheet of potential customers for a beauty salon in Ottawa, Canada. By putting information about the salon locations and customers on a map, you can perform a visual analysis of leads generated at the Women's Show, a major consumer event for women in Canada.

This tutorial was last tested on July 5, 2024.

View final result

Requirements

• ArcGIS organizational account with a Publisher, Facilitator, or Administrator role (see options for software access)
• Microsoft 365
• ArcGIS for Excel
• 15 credits

Get started with ArcGIS for Excel

Map business locations

You'll create a map using an Excel spreadsheet so that you can quickly visualize the salon data. You'll start by plotting salon locations on the map and styling these points according to revenue so you can easily see how each salon is doing. Next, you'll add a layer to the map to show the locations of potential new customers whose contact information you collected at the Women's Show. You'll style the layer to highlight key information about the potential customers. Finally, you'll configure pop-ups to display detailed information about each feature on the map.

To begin, you'll create a map from a spreadsheet of salon data. Then you'll modify the salon symbols to style them according to revenue.

1. Download the SalonCustomersOttawaArea.xlsx Excel spreadsheet.
2. Go to Microsoft Excel Online. If necessary, sign in to your Microsoft 365 account.

   The page for Microsoft Excel Online appears.

3. In your computer's file explorer application, locate the SalonCustomersOttawaArea.xlsx spreadsheet you downloaded and drag it into the Microsoft Excel Online page to upload the file.

   When the upload is complete, the spreadsheet automatically opens.

   The spreadsheet contains two tabs—one for each worksheet. The salons tab has information about the salons, and the new customers tab has all the contact information that you collected at the Women's Show.

4. If necessary, at the bottom of the page, click the salons tab to make it the active worksheet.

   

   Each salon location has its own row that contains fields for the salon name, manager's name, salon address, city, province, and phone number, as well as the salon's revenue and profits for the current year. To add data to a map, it must contain at least one location-based attribute, such as address data or longitude and latitude values. You'll use the address fields to plot the salon locations on the map.

5. On the Excel ribbon, click the ArcGIS tab to display the ArcGIS tools.

   

   Note:

   If you don't see the ArcGIS tab, download and install ArcGIS for Excel. If you've already installed ArcGIS for Excel, see Welcome to ArcGIS for Excel or Frequently asked questions to find a possible solution.

   Before you can use the tools, you must first sign in to an ArcGIS organizational account.

6. On the Excel ribbon, click Show Map.

   

7. If the Welcome to ArcGIS for Excel pane appears, click Get Started to continue.

   The ArcGIS for Excel pane appears.

8. Sign in to your ArcGIS organizational account.

   Note:

   If you don't have an organizational account, see options for software access.

9. From the map tools, click the Layers button.

   

   You have not added any layers yet, so the list is empty. When you click the Add button, you can choose where you want to add data from: Excel or an ArcGIS organization.

10. In the Layers pane, click Excel.

    

    The Add layer pane shows the Excel tab selected.

    

    ArcGIS for Excel provides two ways of adding data from Excel: from a table or a cell range. You will add the salon information as a range of cells in the spreadsheet. The first row in the salons spreadsheet contains headers for the columns. You want to preserve these as attribute field names, which will help you style the map later.

11. In the Data drop-down menu, choose Select cell(s) directly on the sheet.

    

    For Data, the option changes to Select cell(s).

12. In the salons worksheet, select the range of cells from cell A1 to cell H6.

    Tip:

    You can click the first cell in the range and press the Shift key while you click the last cell in the range.

    

    The selected cells are surrounded by a green border and the cell range appears under Data.

13. In the ArcGIS for Excel pane, in the Add layer pane's Excel tab, confirm that the First row contains headers box is checked.

    

    Next, you'll specify the mapping options and confirm the location information for the salons.

14. For Location types, choose or confirm that Address is selected.

    

    This field indicates the type of location-based data that you're providing, such as street addresses or latitude-longitude coordinates. In this case, the selected cells contain address data. When you create a layer from addresses, corresponding locations are determined using the default locator set by your organization's administrator.

    Note:

    Geocoding addresses uses ArcGIS credits. To learn more, see Understand credits.

    Several of the mapping options are automatically selected depending on the default settings of your ArcGIS organization and the data in your spreadsheet. However, you must still confirm that the location information is appropriate for your map. For example, the salons are in Canada. If the default country for your account is the United States, you must choose the correct country (Canada) to ensure that your map displays the features properly.

15. Under Location types, for Region, choose Canada. For Address field, verify that Multiple is selected.

    

    Note:

    The salons spreadsheet doesn't have a column with values for Postal or Country Code. The None value for these columns is acceptable because the software has enough location information to properly map the salons without these values.

16. Click Add.

    The salon locations appear on the map.

Style the layer

Next, you'll choose a style for visualizing this data in the map. ArcGIS for Excel provides many options for styling the data that you use to create a layer.

1. Click the Symbology tab.

   

   For the Active layer, the cell range shows salons!A1:H6, which are the cells you selected.

2. For Active layer, confirm that the cell range is salons!A1:H6.

   

3. Under Active layer, click Add field.

   

   Additional drop-down menus appear.

4. Click the drop-down menu that says None and choose Revenue.

   

   The available values in the that you can symbolize by correspond to one of the column headers in your data. You choose a column to determine which attribute the symbols should represent. In this case, you want to visually compare the revenue of salons in Ottawa.

   The map updates.

   

   When you choose the column, the style that is typically most suitable for the data is selected by default and applied to the map. You'll apply a style that symbolizes features with graduated circles, which are useful for comparing records on the map.

   Note:

   For more information about the other styles, see Change a layer's style. Further, you can further refine a layer's symbology on the Style options tab

5. Click Add field and in the field drop-down menu, choose Profit.

   

   Notice that for Symbol type, Color and Size is selected.

   

   The ArcGIS for Excel pane shows a map of the Ottawa, Canada, area. The map displays the salon locations where the graduated sizes of the circles represent the amount of revenue the location has reported, and the color of the symbol represents the amount of profit the location has reported, with the darker color representing higher values. The combination of color and size symbology gives you a sense of how salon revenue and profit are distributed in the region.

   Next, you will further customize the symbology by choosing a different color ramp.

6. In the Layers pane, click the Style options tab.

   

   Additional style options appear, allowing you to edit the size or color of the layer style.

7. Click the Color ramps drop-down menu. For All color ramps, click the drop-down menu and choose Greens. Click the color ramp Green 1.

   Tip:

   To see the name of a color ramp, point to the color ramp.

   

   The layer style updates.

8. Click the Layers tab to return to the layer list.

   The map layer that contains the salons is named according to the cell range that you chose in the worksheet. You'll provide a more meaningful layer name here.

9. In the layer list, double-click the salons!A1:H6 layer to edit the text. Type salons and press Enter.

   The layer name updates.

   

10. Close the Layers pane.
11. If you don't see five green symbols on the map, resize the map window and drag the map to pan across until you see all the features on the layer.

    Tip:

    The symbol farthest east is small, so it may be difficult to see at first. You can also zoom the map by using the navigation tools in the corner or scrolling with your mouse wheel.

    

    The salon in Orleans (to the northeast) is generating the lowest revenue of the five locations, but one of the highest profits, which means there are opportunities for expanding your customer base at this location. It is the newest salon—the one for which you may want to send promotional packages to potential new customers.

    The map tools, displayed as a column of expandable icons at the side of the map, contain tools for working with the map and data; you'll use some of these throughout this tutorial. The map tools also include tools that you can use to search for places of interest, select features on the map, and change the basemap.

    The salons layer displays over a basemap, which provides context for your locations. In an ArcGIS organization, the administrator chooses the default basemap, but you can change it for your map. You want to draw attention to the data that you're adding to the map, so you'll choose a simple basemap.

12. From the map tools, click the Basemap.

    

    The Basemap pane appears.

13. Click Dark Gray Canvas.

    Note:

    Your organization may use different basemaps, depending on how your administrator has configured your organization's settings. If you don't see Dark Gray Canvas, choose another simple basemap that provides enough contrast with the salons layer. If your map already uses this basemap, close the Basemap pane.

    

    A high-contrast basemap is used to minimize background distraction.

    

14. Close the Basemap pane.

Now that you've located your salons on a map and chosen a neutral basemap, you're ready to add the potential new customers to the map.

Map potential customer addresses

Next, you'll add a layer to the map that shows the locations of the potential new customers who provided their contact information at the Women's Show. This data is in the recommended format of a Microsoft Excel table.

1. Click the new customers tab to make it the active worksheet.

   The customer information is contained in an Excel table. When you add a layer to the map from a table, new rows or columns that get added later are automatically included in the map. When you add a layer to the map from a range of cells, as you did in the previous section, adding rows or columns will not update the map.

2. From the map tools, click Layers and click the Add button.

   Because you already added data from Excel earlier, when you click add, that option is already selected.

3. In the Add layer window, for Data, choose new customers!customers or verify that it is selected. For Location types, choose or verify Address.

   

   As before, you'll confirm that the location information is appropriate for your map.

4. For Region, choose Canada.
5. If necessary, for Address field, click Multiple and click Add.

   

   The layer is styled by Location (Single symbol) by default. This styling is helpful for seeing where customers are distributed in the region, and for analyzing customers' proximity to a salon. However, the worksheet contains additional information that is important to visualize. Ottawa is a bilingual city, so you'll style the layer by the customers' preferred language using the Types (Unique symbols) style.

6. Rename the layer to customers and click it to select the layer.

   

7. Click the Symbology tab and click Add field. From the Size drop-down menu, choose Color.

   

8. Click the None drop-down menu and choose Language.

   

   When you choose this field, the Symbol type automatically is set to Types.

   

   In this style, features are drawn with various symbols to represent each value in the drawing column you selected. Now you'll change the default symbol style, as it is similar to the style used for salons.

9. Click the Style options tab.
10. Expand the Unique values section.

    

    The section shows how each value is styled and a count of how many features are in each category.

    When using the Types (Unique symbols) style, the Symbology section shows basic options to change the shape only, and advanced options to change the outline only. To change the fill of the symbols or to use different symbols for the Types (Unique symbols) style, you must change the symbols for each unique value.

11. In the Unique values section, click the symbol for French.

    

12. For Shape, click the drop-down menu and choose the blue circle.

    

13. Set the symbol size to 15 pixels (px).

    

    The symbol on the map changes automatically. Next, you will change the symbol for the English language features.

14. Click the symbol for English and repeat the process to set the symbol to the red circle with a size of 15.
15. Click the Layers tab to return to the layer list.

    The symbols for the customers layer obscure the symbols for the salons layer, so you'll move the salons layer above the customers layer.

16. In the layer list, click and drag the salons layer above the customers layer.

    

    The salons layer moves above the customers layer. On the map, the green salon symbols appear on top of the blue and red customer symbols.

17. In the layer list, for the customers layer, click the More options button and click Zoom to.

    

    The map zooms to the full extent of the selected layer so you can see all its features.

18. In the Layers pane, for the customers layer, click the Expand button.

    

    The layer legend is now visible in the Layers pane. The red circles represent customers who prefer speaking English and the blue circles represent customers who prefer speaking French.

19. Close the layer list.

    Now you can see the distribution of potential customers who surround each salon location by their language preference.

    

    Your map now shows the locations of the salons and the locations of potential customers. The layer styling clearly identifies the customers' preferred language for receiving marketing communications. The salon location in Orleans currently has the smallest amount of revenue. This map provides a visual estimate that about half the customers near the Orleans salon prefer speaking French. This can inform any promotional campaigns you might introduce to the area to best meet your customers' needs.

Configure pop-ups

Next, you'll configure pop-ups for the customers layer. Pop-ups appear when you click a feature on the map; they contain descriptive information about the features in each layer on the map. The attribute information that displays in a pop-up comes from the columns and rows in your data.

1. Click a customer location on the map to open its pop-up.

   

   The basic configuration of a pop-up is just what you see: a list of fields and values associated with a layer. The information should be familiar to you because it comes from the table.

   You can change the way this information is presented in pop-ups on this layer. You'll add a header and specify which attributes to display.

2. Close the pop-up.
3. On the map tools, click Layers. At the top of the Layers pane, click the Layer properties tab and expand Pop-ups.
   
4. For Title field, choose Name.

   

   Now you'll specify which attributes to include in the pop-up. Because you're using the Name field as the pop-up's title, you don't need this information to be repeated in the pop-up. Also, it's unnecessary to show the city and province names, which are the same for each location. The basemap labels tell anyone using the map that this is Ottawa, Ontario.

5. Click the drop-down menu where the fields are listed and uncheck the boxes next to Name, City, and Province.

   

   A field has both a name and an alias. An alias is a display name that replaces the field name in the pop-up. By default, the alias and field names match, but you can change the alias to something more informative or familiar.

6. Close the Layers pane so you can see the map.
7. On the map, click a customer feature to view the updated pop-up.

   

   Note:

   Your pop-up may be different, depending on the feature you clicked.

   The pop-up displays the attributes that you specified and also contains several tools that allow you to work with the selected feature.

8. In the pop-up, click the Zoom to button.

   

   The map zooms to the selected feature.

   Note:

   To learn more about pop-ups, see Configure and display pop-ups.

9. Close the pop-up, open the Layers pane, and zoom to the salons layer.

   The map zooms to the full extent of the selected layer so all its features display in the map window.

   Since you are working in Microsoft Excel Online, your file is automatically saved.

   You have configured pop-ups for the customers layer to display specific information about each customer.

Share your map

Now that you have a map that allows you to visually explore customer prospects for your salons, you'll share your map on ArcGIS Online to present your results.

Before you create your marketing presentation, you'll share your map as a web map on customers. Sharing a map on customers is a quick way to share information with others in or outside of your organization. When you share a map, a web map is created in customers. Once you publish the map, you can share a link of the map for others to view your results.

1. If necessary, open SalonCustomersOttawaArea.xlsx in Excel and sign in to ArcGIS.
2. In the ArcGIS for Excel pane, on the map toolbar, click Share | Export.

   

3. In the Share map pane, enter the following information:
   • For Title, type Orleans Salon Promo, followed by your name or initials.
   • For Tags, type Orleans, Salon, Promo.
   • For Summary, type Promo for potential customers for Orleans salon.
   • For Share with, check Everyone (public).

   

   Alternatively, you can choose to share only within your organization, or to any groups to which you belong.

4. Click Share and click Yes on the message about non unique identifiers.

   Note:

   You may get a message about non-unique identifiers. This just indicated that each row doesn't have a unique ID, which is fine for this.

   Once the map has successfully published, a confirmation message appears at the bottom of the map to indicate that the map has been shared. The Share | Export pane also updates to confirm the map was successfully published and includes a link to the map.

5. In the Share | Export confirmation window, click Open.

   

   Note:

   Clicking Open will open the item details page for the web map. Clicking Copy link will copy the link to the item details page for the web map to the clipboard.

   Your web map item page appears in a browser.

   

   Note:

   In the Share | Export pane, you can also click the Export button and export the map to several image formats so you can add it to a worksheet for their reports.

6. Sign in to your ArcGIS Online account and click Open in Map Viewer.

   Note:

   Depending on your organizational and user settings, you may have opened Map Viewer Classic. ArcGIS Online offers two map viewers for viewing, using, and creating maps. For more information on the map viewers available and which to use, please see this FAQ.

7. On the map, click a feature to open a pop-up.

   

   The pop-up appears as you created it in ArcGIS for Excel. You can add other layers to this map and share it as an app.

In this tutorial, you used ArcGIS for Excel to create and configure a map that shows the locations of hundreds of potential customers. The map allows you to visually analyze the potential customers' proximity to your salons and highlights their preferred language for marketing communications. Finally, you created a visually compelling web map to present your analysis using ArcGIS for Excel and your ArcGIS organization.

You can find more tutorials in the tutorial gallery.

Acknowledgements

• This data was fabricated for this tutorial's purposes.
• Card graphic created with an image by Adam Winger from Unsplash.
• Dark Gray Canvas map sources: Esri, HERE, Garmin, FAO, NOAA, USGS, OpenStreetMap contributors, and the GIS User Community