Graphed LogoGraphed
Login

How to Remove Geography Data Type in Excel

Cody Schneider•

Excel’s Geography data type is a clever feature for turning simple text, like the name of a country or city, into a rich object full of useful information. But sometimes, you need to turn that smart data back into plain, simple text. This article walks you through exactly why and how to remove the Geography data type from your cells.

What Exactly is the Geography Data Type in Excel?

Before we remove it, let's quickly recap what the Geography data type is. Part of Excel’s "Linked Data Types" feature, it automatically connects text entries like "Canada," "California," or "Paris" to an online data source (Microsoft Bing). Once converted, your plain text becomes a smart object identified by a small map icon.

This allows you to instantly pull related real-world data into your spreadsheet. For example, if you convert "Germany" into a Geography data type, you can easily add new columns for its:

  • Population

  • Capital city (Berlin)

  • GDP

  • Largest city

  • Land area

  • And much more...

This automated data enrichment is incredibly useful for building reports, dashboards, and analyses without having to manually look up every piece of information on the web.

Why Would You Need to Remove the Geography Data Type?

As powerful as this feature is, there are several practical reasons why you might want to convert these smart objects back to basic text:

  • Spreadsheet Performance: Linked Data Types are constantly connected to an online source. In large worksheets with hundreds or thousands of these data points, this live connection can slow down calculations and make your file feel sluggish. Converting them to text removes the connection and can improve performance.

  • Data Mismatches or Ambiguity: Excel occasionally gets it wrong. For instance, it might interpret "WA" as Washington State when you meant Western Australia. Reverting to text allows you to correct it or use a more specific name.

  • Sharing & Compatibility: If you send your workbook to someone using an older version of Excel, or a different program like Google Sheets, the Geography data type may not function correctly, leading to display issues or errors. Simple text is universally compatible.

  • Creating a Static Report: Because the data is live, population or GDP figures might update over time. If you need to "freeze" your report for a specific point in time (like a quarterly review), converting the data to text makes it static.

  • Simpler Data Manipulation: Sometimes, you just want to work with the text itself – maybe for text-based functions like CONCATENATE or simple sorting – without the extra properties of a data object getting in the way.

How to Remove the Geography Data Type (3 Methods)

Removing the Geography data type is straightforward. Here are three methods, from the most common to one for more advanced automation.

Method 1: The Right-Click "Convert to Text" Menu

This is the most direct and intended way to remove the data type. It works perfectly for a single cell, a selection of cells, or an entire column.

  1. Select the Cell(s): Click and drag to highlight the cells containing the Geography data type you want to change. You'll see the small map icon next to the text in each cell.

  2. Right-Click: Right-click anywhere within your selection to open the context menu.

  3. Navigate to Data Type: In the menu that appears, hover in the middle section and find the "Data Type" option.

  4. Click "Convert to Text": A sub-menu will fly out. Select "Convert to Text."

Instantly, the map icons will disappear from your selected cells, and the data will be converted to plain text. All links to the online data source are severed.

Method 2: The Copy & Paste Special (Values) Trick

If you're a long-time Excel user, you might default to this classic trick for stripping all formatting and special properties from cells. It works well here, too, especially if you want to create a clean copy of your list elsewhere in the sheet.

  1. Select and Copy: Highlight the cells with the Geography data type and copy them by pressing Ctrl + C (or Cmd + C on Mac).

  2. Choose a Destination: Click on the cell where you want your new plain text list to start. This can be a new location or you can paste over the original selection.

  3. Open Paste Special: Right-click on the destination cell, and in the context menu, select "Paste Special." Alternatively, use the popular keyboard shortcut Alt + E + S + V.

  4. Select "Values": In the Paste Special dialog box, choose "Values" under the "Paste" section and click "OK."

This action pastes only the underlying text value of the cell ("Canada") without any of the attached data type information. Your new cells are plain text from the start.

Method 3: A Simple VBA Macro for Automation

If you find yourself needing to remove data types across many different files or as part of a recurring cleanup process, a VBA macro can automate the task in one click. This is an advanced option but is surprisingly simple to set up.

How to Create the Macro:

  1. Open the VBA Editor: Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in Excel.

  2. Insert a New Module: In the editor menu, go to Insert > Module. This opens a blank code window.

  3. Paste the Code: Copy and paste the following code snippet into the module window.

  1. Close the Editor: Close the VBA editor by clicking the 'X' to return to your worksheet.

How to Run the Macro:

  1. Select the cells with the Geography data type you want to convert.

  2. Open the Macro dialog box by pressing Alt + F8.

  3. Choose "ConvertSelectedToText" from the list and click "Run."

The macro will instantly run the "Convert to Text" command on your entire selection.

Important After-effect: Watch Out for Formula Errors

After converting a cell from a Geography data type back to text, something important happens to any formulas that relied on it.

For instance, if cell A2 contained "France" as a Geography type, you might have used a formula like =A2.Population in another cell to display its population. Once you convert A2 back to plain text, that formula will break and show a #FIELD! or #REF! error because the ".Population" field no longer exists.

If you need to preserve the data you've already pulled (like the population and capital city), simply copy those columns and use "Paste Special > Values" on them before you remove the Geography data type. This will convert the formulas into static text or numbers, safeguarding your report's data.

Final Thoughts

Excel's Geography data type is a fantastic tool for quick, data-rich analysis. But knowing how to revert it back to text is essential for managing performance, ensuring compatibility, and finalizing your reports. Whether you use a simple right-click, the classic paste-special trick, or a handy VBA macro, you have full control over your data's format and function.

Wrangling spreadsheet data like this is a common part of analysis, but it isn't always the most efficient use of time. At Graphed we handle the technical headaches automatically. We connect you directly to your sources - like Google Analytics, Shopify, QuickBooks, and Facebook Ads - and let you build live dashboards simply by describing what you want to see. Instead of cleaning cells one by one, you just ask questions and get instant, always-up-to-date visualizations.