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.

    Salons tab

    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.

    ArcGIS tab

    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 (or an ArcGIS Enterprise organizational account).

  6. On the Excel ribbon, click Show Map.

    Show Map button in the Map group on the ribbon

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

    The ArcGIS for Excel pane appears.

  8. In the ArcGIS for Excel pane, click Sign In. In the window that appears, sign in to your ArcGIS organizational account.
    Note:

    If you don't have an organizational account, you can sign up for a free trial.

    A blank map and layer list appears in the ArcGIS for Excel pane.

    Tip:

    If necessary, you can drag the edge of the pane to widen it.

    A coach mark message appears when you open a blank map for the first time.

  9. In the coach mark window, click Got it.

    The window closes, and the layers pane is now available, where you can add data from ArcGIS for Excel.

    You have not added any layers yet, so the list is empty. At the bottom of the Layers pane, the Add from Excel and Add from ArcGIS buttons allow you to add data to the map.

    Layers pane with options to add layers from Excel or ArcGIS

    The other tools in the Map group are unavailable until you create at least one map. With these tools, you can find, arrange, and synchronize maps in your worksheet.

  10. In the Layers pane, click Add from Excel.

    Add data from Excel button in the Layers pane

    The Add from Excel pane appears.

    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 Dataset - Table drop-down menu, choose Select a cell range.

    Data format cell range

  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.

    Selected range of cells

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

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

    First row contains headers checked.

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

  14. For Location types, choose Address.

    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 Country, choose Canada.
  16. If necessary, click Multiple columns and verify that the location fields were mapped correctly to the corresponding columns in your spreadsheet.

    Multiple columns 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.

  17. Click Add to map.

    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 Layer options button.

    Layer options button in the Layers pane

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

    Active layer set to the cell range A1 to H6.

    The values in the Select drawing column (size) menu 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.
  3. For Select drawing column (size), choose Revenue.

    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.

    You can further refine styling the layers by choosing a field for Select drawing column (color) and choosing the Color & Size card.

  4. For Select drawing column (color), choose Profit.

    Select drawing column (color) set to Profit.

  5. In the list of suggested styles, confirm the Color & Size style is selected.

    Color & Size style

    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 Styling pane, point to Color & Size and click Style options.

    Style options for Color & Size style card

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

  7. Point to Counts and Amounts (color) and click Style options.
  8. Scroll down to the Color ramps section. Under Color ramps, choose Greens and click the color ramp Green 1.
    Tip:

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

    Green 1 selected under the Greens option under Color ramps.

    The layer style updates.

  9. Click the back arrow 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.

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

    The layer name updates.

    Layer name is renamed salons in the Layers pane.

  11. Close the layer list.
  12. 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.

    Map window with Orleans salon circled

    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 lesson. 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.

  13. From the map tools, click the Basemap.

    Basemap on the map tools

    The Basemap pane appears.

  14. 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.

    Dark Gray Canvas basemap

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

    Map of salons with the Dark Gray Canvas basemap

  15. 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, select Layers. Click the Add from Excel button.
    Note:

    If you minimized the map window earlier, click the ArcGIS tab, click Go to Map, and choose the map to open it.

    Add data from Excel button in the Layers pane

  3. In the Add from Excel window, for Dataset, choose customers. For Location types, select Address.

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

  4. For Country, choose Canada.
  5. If necessary, click Multiple columns and verify that the location fields were mapped correctly to the corresponding columns in your spreadsheet.

    Multiple columns selected

  6. Click Add to map.
  7. Click Layer options. Confirm that for Active layer, the customers layer is selected from the drop-down menu.

    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.

  8. Continue setting the following:
    • For Select drawing column (size), confirm it is set to <None>.
    • For Select drawing column (color), choose Language.
    • Verify that Types (Unique symbols) is selected.

    Styling pane parameters set

    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. Hover over the Types (Unique symbols) style and click Style options.

    Style options for Types (Unique symbols)

    The Styling pane updates to show additional symbology configuration options.

  10. In the Styling pane, expand the Symbology section.

    The Symbology section applies styling changes to all symbols in the layer. 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. If necessary, in the Styling pane, expand the Unique values section.

    Unique values section in Styling pane

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

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

    The Edit symbol pane appears.

    Edit symbol pane

  13. In the Symbols drop-down menu, set the symbol category to Shapes and choose the blue circle. Set the symbol size to 15 pixels (px).

    Shape symbol settings

    The symbol on the map changes automatically.

  14. Click OK to close the Edit symbol pane.
  15. In the Styling pane, under the Unique values section, click the symbol for English and repeat the process to set the symbol to the red circle.
  16. Click the Back button 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.

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

    Drag salons layer to top of the list.

    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.

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

    Zoom to in the More options menu for the customers layer

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

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

    Expand the customers layer to view the legend.

    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.

  20. Close the layer list.

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

    Map of potential customers and salon locations

    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.

    Pop-up for customer location

    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 tool, click Layers. At the top of the Layers pane, select Layer options.

    The Styling pane appears.

  4. In the Styling pane, click Pop-ups.

    Pop-ups button in the Styling pane

  5. In the Pop-ups pane, for Active layer, confirm that the customers layer is selected and that Enable pop-ups is enabled.

    Active layer is set to customers and Enable pop-ups is turned on.

  6. 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.

  7. Uncheck the circles 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.

  8. Point to the Address row and click the Edit button. Type Home Address and press Enter.

    Pop-up settings pane

  9. On the map, click a customer feature near the Orleans salon to view the updated pop-up.

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

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

    Zoom to button in the updated pop-up

    The map zooms to the selected feature.

    Note:

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

  11. Close the pop-up. In the Pop-ups pane, click the Back button to return to the layer list.
  12. In the salons layer, click the More options button and click Zoom to.

    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.

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 the lesson spreadsheet SalonCustomersOttawaArea.xlsx in Excel and sign in to ArcGIS.
  2. In the ArcGIS for Excel pane, on the map toolbar, click Share map.

    Share map button

  3. In the Share map pane, enter the following:
    • 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.

    These fields are used to display information about the map on ArcGIS and are also used for searching.

  4. For Share with, check Everyone (public).

    Share map pane with parameters set

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

  5. Click Share.

    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 map pane also updates to confirm the map was successfully published and includes a link to the map.

  6. In the Share map confirmation window, click the link to view your web map.

    Link to view published web map

    Your web map item page appears in a browser.

    Web map item page

  7. 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.

In just a short amount of time, you created 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 lessons in the Learn ArcGIS Lesson Gallery.