How to Group Countries into Continents in Excel

Q: Does Excel have a built-in CONTINENT function?

No. Excel has no CONTINENT() function — tutorials that show one are mistaken. Use a mapping table with XLOOKUP, VLOOKUP, or SWITCH. The Geography linked data type (Data → Geography in Microsoft 365) returns attributes like population and capital, but you should not assume it provides a clean continent field.

To group countries by continent in Excel, build a small mapping table that lists each country next to its continent, then add a continent column to your data with a lookup formula — XLOOKUP in Microsoft 365 and Excel 2021, or VLOOKUP in older versions. Once every row has a continent, you can sort, filter, or drop the data into a PivotTable to summarize each region.

Excel has no built-in CONTINENT() function, despite what some older tutorials claim. The reliable approach is a reference table you control, paired with a lookup. This guide walks through every method — XLOOKUP, VLOOKUP, SWITCH for short lists, PivotTable grouping, and Power Query — plus a copyable starter table and a troubleshooting section for the #N/A errors that trip people up.

Start with a country-to-continent mapping table

Every method here depends on one thing: a clean lookup table that pairs each country name with its continent. Put it on its own sheet (call it Mapping) so it stays out of the way of your working data. Two columns are all you need — Country and Continent.

Here is a small starter you can copy straight into a sheet. Expand it to cover the countries in your dataset (there are roughly 195 recognized countries, so a complete table is manageable):

Country	Continent
United States	North America
Canada	North America
Mexico	North America
Brazil	South America
Argentina	South America
United Kingdom	Europe
Germany	Europe
France	Europe
Spain	Europe
Nigeria	Africa
Egypt	Africa
Kenya	Africa
China	Asia
India	Asia
Japan	Asia
Australia	Oceania
New Zealand	Oceania

Two decisions matter up front. First, pick a continent model and use it consistently: the 7-continent model splits the Americas into North and South America, while some datasets merge them into one. Mix labels and your PivotTable shows “North America” and “Americas” as separate groups. Second, transcontinental countries like Russia, Turkey, and Kazakhstan span two continents — assign each to one continent here to keep lookups one-to-one.

Convert the range into a proper Excel Table (select it and press Ctrl+T on Windows, Cmd+T on Mac). A Table gives you structured references like Mapping[Country] that auto-expand when you add rows, so formulas never need adjusting later.

Method 1: XLOOKUP (Microsoft 365 and Excel 2021)

XLOOKUP is the cleanest way to pull a continent across both Windows and Mac, as long as your version supports it. With your data countries in column A starting at A2 and the mapping Table named Mapping, the formula is:

=XLOOKUP(A2, Mapping[Country], Mapping[Continent], "Not found")

The first argument is the country you’re looking up, the second is the column to search, the third is the column to return, and the fourth is the value shown when there’s no match — far friendlier than a raw #N/A. Enter it in B2 and either copy it down or let it spill if your country list is a dynamic array.

XLOOKUP searches top-to-bottom and doesn’t care about sort order, which makes it more forgiving than VLOOKUP. For a full breakdown of its arguments and match modes, see our guide on how to use the XLOOKUP function. If you’re weighing it against the older approaches, XLOOKUP vs VLOOKUP vs INDEX MATCH compares them side by side.

Method 2: VLOOKUP (Excel 2019 and earlier)

If you’re on Excel 2019, 2016, or an older build that lacks XLOOKUP, use VLOOKUP. It works on every version on both platforms. The key rule: the lookup column (Country) must be the leftmost column of your mapping range, and Continent must sit to its right.

=VLOOKUP(A2, Mapping, 2, FALSE)

Here A2 is the country to find, Mapping is the two-column range, 2 is the column index to return (Continent), and FALSE forces an exact match — non-negotiable for names, since an approximate match would return wrong continents. Wrap it in IFERROR to replace #N/A with something readable:

=IFERROR(VLOOKUP(A2, Mapping, 2, FALSE), "Not found")

VLOOKUP is covered in depth in how to use the VLOOKUP function in Excel. If you’d rather avoid the leftmost-column restriction without upgrading, INDEX MATCH does the same job and lets the country and continent columns sit in any order.

Method 3: SWITCH for short, fixed lists

When you only have a handful of distinct countries and don’t want to maintain a separate table, SWITCH keeps the logic inline in one cell:

=SWITCH(A2, "United States", "North America", "Brazil", "South America", "France", "Europe", "Unknown")

SWITCH compares A2 against each value in turn and returns the result that follows the first match; the final lone argument (“Unknown”) is the default when nothing matches. It’s readable for a dozen entries but becomes unwieldy fast — past 15 or 20 countries, a mapping table and XLOOKUP is easier to maintain and audit. SWITCH is available in Excel 2019, 2021, and Microsoft 365 on both Windows and Mac. See how to use the SWITCH function for the full syntax and a comparison with nested IF.

Method 4: Group by continent in a PivotTable

Once every row has a continent column, summarizing by region takes seconds. Select any cell in your data, go to the Insert tab, and choose PivotTable (this is in the same place on Windows and Mac, though the Mac field pane looks slightly different). Drag the Continent field into the Rows area and drag Country into the Values area — it will default to “Count of Country,” giving you the number of countries per continent. Add any numeric field (population, revenue, sales) to Values and switch its summary to Sum to total it per continent instead.

A PivotTable groups dynamically: change a continent label in your source data, click Refresh, and the groups update. For the full workflow, walk through our complete guide to Excel PivotTables, or the shorter how to use an Excel PivotTable if you just need the basics.

Method 5: Merge with a mapping table in Power Query

For large or recurring datasets, Power Query (the Get & Transform tools on the Data tab) is the most robust option because the join is repeatable — just refresh. It’s built into Excel 2016 and later on Windows and is available in recent Microsoft 365 builds on Mac, though the Mac editor has historically lagged Windows in features.

Load both your data and your mapping table as queries (Data → From Table/Range with each selected). In the editor, select your data query, choose Home → Merge Queries, pick the mapping query, click the Country column in each to set the join key, and use a Left Outer join so every original row is kept. Expand the new column to bring in Continent, then Close & Load. Unmatched countries show null, making mismatches easy to spot and fix.

A reliable merge depends on clean, consistent text on both sides. If your source data is messy, run it through the steps in our clean messy data checklist first.

A note on the Geography linked data type

Excel’s Geography data type (Data tab → Geography, available in Microsoft 365) converts a cell containing a country name into a rich, linked record. Click the map icon in the cell or use =A2.Field to pull attributes like population, capital, area, and currency.

Be cautious: the fields Geography exposes vary and change over time, and a clean, dedicated “Continent” field is not something you should assume is available. If a continent or region attribute does appear for your data, use it; if not — which is common — fall back to a mapping table, which gives you full control over the labels anyway. Geography also needs an internet connection and a Microsoft 365 subscription, so the lookup-table methods stay the portable choice.

Troubleshooting #N/A and mismatch errors

The single most common problem is a lookup returning #N/A even though the country is “obviously” in your mapping table. Almost always it’s a text-matching issue:

Trailing or leading spaces. “France ” (with a trailing space) won’t match “France”. Clean both sides with TRIM, e.g. =XLOOKUP(TRIM(A2), Mapping[Country], Mapping[Continent], "Not found"). Our guide on removing spaces from text covers TRIM and the trickier non-breaking spaces that TRIM alone misses.
Name variants. “USA,” “U.S.,” and “United States” are three different strings to Excel. Standardize on one spelling per country, and add common aliases as extra rows in the mapping table if your source data is inconsistent.
Hidden characters. Data pasted from the web often carries non-breaking spaces (character 160) that look like normal spaces but don’t match. Strip them with =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
Case sensitivity. Good news: XLOOKUP, VLOOKUP, and SWITCH are all case-insensitive by default, so “france” matches “France.” You don’t need to normalize case for these functions.
Approximate match left on. With VLOOKUP, always pass FALSE (exact match). Omitting it defaults to approximate matching, which silently returns the wrong continent.

To prevent mismatches before they happen, drive your source entries from a dropdown built off the mapping table’s country column. Setting up data validation with an in-cell drop-down list means users pick from the exact spellings you support, so the lookup never misses.

Frequently Asked Questions

How do I group countries by continent in Excel?

Add a continent column to your data using a lookup against a country-to-continent mapping table — XLOOKUP in Microsoft 365 or 2021, VLOOKUP in older versions. Once each row has a continent, build a PivotTable with Continent in Rows to group and count countries per region, or just sort and filter on the new column.

How do I map a country to a continent in Excel?

Create a two-column table pairing each country with its continent, convert it to an Excel Table (Ctrl+T / Cmd+T) so references auto-expand, then use =XLOOKUP(A2, Mapping[Country], Mapping[Continent], "Not found"). On Excel 2019 and earlier, use =VLOOKUP(A2, Mapping, 2, FALSE) with the country in the leftmost column.

How do I add a continent column from a country name?

Put a lookup formula in a new column next to your country names. With a mapping Table named Mapping, enter =XLOOKUP(A2, Mapping[Country], Mapping[Continent], "Not found") in the first data row and copy it down. For short, fixed lists you can skip the table and use SWITCH to map each country inline.

Does Excel have a built-in CONTINENT function?

No. Excel has no CONTINENT() function — tutorials that show one are mistaken. Use a mapping table with XLOOKUP, VLOOKUP, or SWITCH. The Geography linked data type (Data → Geography in Microsoft 365) returns attributes like population and capital, but you should not assume it provides a clean continent field.

Why does my lookup return #N/A for a country that exists?

Usually a text mismatch: trailing spaces, name variants like “USA” vs “United States,” or hidden non-breaking characters from pasted data. Wrap the lookup value in TRIM, or use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) to strip non-breaking spaces, and standardize country spellings between your data and the mapping table.

What’s the best way to group countries by continent for a large dataset?

Use Power Query: load your data and a mapping table as queries, then Merge Queries on the Country column with a Left Outer join and expand the Continent field. The merge is repeatable on refresh, keeps every original row, and surfaces unmatched countries as null so you can fix the mapping table quickly.