How to view geography information in Excel

Excel 365
Excel for Microsoft 365 offers a useful feature for using statistical information about geographic regions. Unfortunately, not all the information provided is up to date, such as population figures or tax rates. However, some data, such as region codes, is useful. You can easily use this information in an Excel spreadsheet:
Geography information in Excel 365

To add the actual and updatable information about geographic regions into the Excel spreadsheet, do the following:

Select the Geography Data Type

   1.   Type the names of geographical regions such as countries or cities.

   2.   Select these cells.

For example:

Geographic regions in Excel 365

   3.   On the Data tab, in the Data Types group, select Geography from the gallery by doing one of the following:

  • Click the More button, then choose Geography:
    Geographic Data Type using More button in Excel 365
  • Click the Down button and choose Geography:
    Geographic Data Type using Down button in Excel 365

Excel will try to convert the selected text to the Geography data type. If the transformation was successful (Excel found in the Microsoft online source a matching geographical data for the value in the cell), the cell data have the geography icon Geography icon in Excel 365:

Geography data in Excel 365

Notes:

  1. If you select multiple data cells, Excel treats them as the same type. Thus, Excel understands all the data selected in this case as countries despite the fact that Georgia is not only a country but also a US state and counties in some states.
  2. Suppose you define such records (in this example, the cell with Georgia) as a geographic data type separately from the rest of the cells. In that case, Excel cannot find the corresponding inventory record in Microsoft online sources:
    Geography data with question icon in Excel 365

    It can happen due to spelling mistakes, and Excel needs your help interpreting the geographical region correctly. In this case, click on the question icon Question icon in Excel 365 to open the Data Selector pane:

Data Selector pane in Excel 365
  • Modify the name in the field on the top if there is a spelling error, and click the find icon Data Type find icon in Excel 365.
  • Select the appropriate data if there are several records for this geographical region.

Add the geographical information

   4.   Select one or more cells with the Geography data type (with the geography icon Geography icon in Excel 365) and click the Insert Data button Insert Data button in Excel 365:

Geography Data Type columns in Excel 365

From the drop-down list, select the data that you want to add to the spreadsheet.

   5.   Click the Insert Data button again and add more data columns.

For example:

Geography information in Excel 365

Notes:

  1. To see all of the fields available for a geographical region, click the geography icon Geography icon in Excel 365 or select the cell and press Ctrl+Shift+F5:
    Available geographical region information in Excel 365

    The information provided differs for different geographical objects: countries, cities, counties, etc.

  2. To refresh data type, do one of the following:
    • On the Data tab, in the group Queries & Connections, click the Refresh All button:
      Refresh All in Excel 365
    • Right-click the geography data (cells with geography icon Geography icon in Excel 365) and choose Data Type -> Refresh in the popup menu:
      Refresh Data Type in popup menu Excel 365

    This command refreshes all data types in the workbook (stock, currencies, geography, etc.), not just the current spreadsheet!

  3. To change the Geography data, right-click on the cell with this data type you want to change (cells with icon Geography icon in Excel 365) and choose Data Type -> Change... in the popup menu.

    This command opens the Data Selector pane to type or select another data (see how to work in this pane above).

    The Change... command in the popup menu is available if you select only one Geography data element.

  4. To fix or change the Geography data type to the regular text, right-click on the the data that you want to fix (cells with geography icon Geography icon in Excel 365), then choose Data Type -> Convert to Text in the popup menu.

    This command converts the Geography data type to the regular text, and all dependent data won't be shown anymore:

    Not Geography data type in Excel 365

    To save all dependent data, before converting the Geography data type, do the following:

    1. Select all dependent data that you want to save and copy it to the Clipboard (by clicking the Copy button on the Home tab, in the Clipboard group, or by pressing Ctrl+C).
    2. On the Home tab, in the Clipboard group, click Paste, and then select one of the Paste Values buttons:
      Paste Values in Excel 365
      • Paste Values in Excel 365  Values (V) pastes the results of formulas. The destination for the copy can be a new or the original range. In the latter case, Excel replaces the original formulas with their current values
      • Paste Values and Number Formatting in Excel 365  Values & Number Formatting (A) pastes the results of formulas, plus the number formatting
      • Paste Values and Source Formatting in Excel 365  Values & Source Formatting (E) pastes the results of formulas, plus all formatting.
  5. To work with geography information, you need online Internet access and an appropriate Excel version of Microsoft 365.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.