When dealing with Microsoft Excel worksheets, there are times you may have to add geographical data into your worksheet. Luckily, Microsoft Excel allows you to do this via a few simple steps. All you have to do is convert your data into the Geography data type. Doing this will enable you to link your data to an online data source.

This article will take you through the processes of turning your data into Geography data types, whether you have a free Microsoft account or a Microsoft 365 account.

Geography Data Type

When entering your geographical data into your Microsoft Excel worksheets, you want to keep it as simple as possible. What better way to do this than to use a geographical map chart? Thanks to the Microsoft Excel built-in Geography data feature, you don't need professional help to develop an elaborate map chart.

Once you apply the data types into your data, they create links to information in the cloud. By linking your data within the worksheet to cloud data, you gain access to an unlimited scope of data valuable to your project.

Things You Should Know

Here are some essential tips you should be aware of when using this tool:

  • Before using these data types, you should also check on your language preferences. Be sure to add English, Spanish, French, Italian, or German into your language settings in order for them to work.
  • If you type in your data and Excel displays a question mark (?) instead, you should know that it could not match your values to anything on the cloud. In this case, you may want to double-check and ensure that you’ve spelled all your words correctly before proceeding.
  • You can click on the question mark (?) icon whenever Excel can't find a match for your data to manually search for the appropriate data. By typing keywords into the pop-up window, you can also manually find your matches.
  • Adding a title or a legend to your map chart will help you make your chat more understandable by your worksheet users. Excel allows you to format your chart maps as you please.
  • Another helpful tool is the Chart Styles button, which allows you to apply different formatting features to your chart while making it much more attractive.
  • Lastly, you can resize your chart whenever you want. You could also move it around to various locations within your worksheet, or even paste it onto a different worksheet if you're going to replicate its contents.

If you are curious about using the in-built map links in Excel, here is a guideline on successfully creating a map link.

  1. Type in your text into their respective cells. Ensure that your data is geographic. That means that it should either be a country, territory, province, or city in each of the required cells in your worksheet.
  2. Click on the cells containing the data to select them.
  3. If you wish to have an easier time working on your data, generate a professional table that will help you manipulate your data. You can do this by clicking on Insert, then on Table.
  4. Click on the Data tab > Geography.
    Stocks and geography data type options
  5. Excel will automatically convert your data into a Geography data type if there is a match between your data in the cells and the data in online sources.
    Changed data types in States cells
  6. Click on one or more cells that contain the Geography data type.
  7. Click on the Insert Data button.
  8. To extract more information, click on a field name. For instance, clicking on Population in the Geography data type will give you further data on the field.
    Different field name options
  9. To add more fields to your worksheet, click again on the Insert Data button and follow the process above.
  10. You can then update the data to more current versions by right-clicking the cell that contains your Geography data, selecting Data Type > Refresh. This process will refresh all the linked data in the worksheet.
    Refresh data type options

Creating Your Custom Values

After inserting your relevant data types and data links, you can opt to add custom values based on your worksheet and the kind of data you deal with. If, for instance, you want to create values of the number of hotels in a city, you can easily do it by creating your custom value.

Unlinking Your Data Cells

If you want to unlink your data cells from the online cloud sources, the process is pretty simple.

  1. Click on a cell that contains the linked data type.
  2. Copy it by pressing Ctrl+C.
  3. Press CTRL+ALT+V.
  4. Choose the Values option.
  5. This process will paste all your copied values into a different cell without allowing the Refresh feature's functionality.

Switching Out Your Data

You can also switch the data type in your worksheets whenever you want. To do this;

  1. Right-click on the particular cell whose data you want to change.
  2. Click on the Data Type option.
  3. Click on Change.
    Data selector pane for changing data types
  4. Clicking on Change will prompt a pane on the right.
  5. Use the pane to search for your desired data.
  6. Click on Select once you find the appropriate information you intend to use.
  7. The information will then appear in place of your initial data.

Removing Data Types

While working on your worksheets, you may decide to eliminate the data types that you have already inserted. Here is a guide on how to remove data types from your worksheet.

  1. Right-click on the cell containing the data type.
  2. Select the Data Type option.
  3. Click on Convert to Text.
    Data type options refresh, change,convert to text
  4. Your computer will then convert your data type to regular texts. Your cell no longer has any connection to the online cloud.
  5. Note that you will lose all the data initially extracted from the data type once you do this. In their place, you will notice an error message (#FIELD!).
    <img src=#FIELD error message cells with changed data types

Advantages of Using Data Types

  • Saves your time by automatically extracting data and values from online sources. This way, you do not have to enter multiple values into your worksheet manually.
  • They categorize your data based on online matches. This gives you a broader scope of data compared to your manual searches.
  • They increase the accuracy of your values. Automated processes often yield higher accuracy levels compared to manual options.
  • They allow easy updating of the relevant fields. Your updates are just a click away. All you have to do is click on the refresh button.

Get the Best Out of Excel

If you have never used Excel's built-in Geography feature, it's high time you did! Use it today and save yourself plenty of time while keeping your data up-to-date. Here is a list of other unique Excel features you may find useful. With Excel's in-built features, you hardly need additional apps or software to process your data!