Facebook Ads Reporting: Convert Country Code to Country Name Excel/Google Sheets

When exporting country data from your Facebook ads account, you don’t get simple country names in your reports. Meta uses the ISO 3166-1 alpha 2 codes (I know, catchy). For example ‘GB’ for the United Kingdom. And if you want clear and accurate data to analyse, this causes a bit of a nightmare, or in my case, a literal headache.

But after a few hours of googling and Excel formulas later, I found a fix. So, I’m here to save you that precious time with a simple guide on how to quickly convert country codes to country names in excel and google sheets

In this post

The Issue

We are currently running Facebook and Instagram lead ads for a client in well over a hundred different countries. I needed a report that showed exactly how many leads were coming from each country and the spend per country. But upon exporting this file from Facebook, instead of country names I was seeing country codes.

Some of the country codes were obvious and some weren’t. This data was going to inform some important decisions around optimisation in the account so they had to be correct. Plus, it was important that the report was as user-friendly as possible to share with the client.

The thought of having to manually change every country code to a country name filled me with dread. BUT – after some research and trial and error, I found a way to do it in just a few steps.

How to Convert Country Codes to Country Names

Here are the step-by-step instructions on converting country codes to country names, using a simple VLOOKUP formula. The below instructions can be followed if you’re using either Microsoft Excel or Google Sheets. In my case, I used Google Sheets.

Step 1 – Export the Report from Facebook

Once you’ve set up your report in Facebook with all the metrics you need and split by country, export the report. Then open it up in either Microsoft Excel or Google Sheets.

Step 2 – Create a New Tab with Country Names

You’ll need to create a source of the countries for the VLOOKUP formula to refer to. So, create a second tab in your spreadsheet and paste in the below (make sure to keep the country code in the first column and the country name in the second column):

List of country codes and country names👇

AF Afghanistan
AL Albania
DZ Algeria
AS American Samoa
AD Andorra
AO Angola
AI Anguilla
AQ Antarctica
AG Antigua and Barbuda
AR Argentina
AM Armenia
AW Aruba
AU Australia
AT Austria
AZ Azerbaijan
BS Bahamas
BH Bahrain
BD Bangladesh
BB Barbados
BY Belarus
BE Belgium
BZ Belize
BJ Benin
BM Bermuda
BT Bhutan
BO Bolivia, Plurinational State of
BO Bolivia
BA Bosnia and Herzegovina
BW Botswana
BV Bouvet Island
BR Brazil
IO British Indian Ocean Territory
BN Brunei Darussalam
BN Brunei
BG Bulgaria
BF Burkina Faso
BI Burundi
KH Cambodia
CM Cameroon
CA Canada
CV Cape Verde
KY Cayman Islands
CF Central African Republic
TD Chad
CL Chile
CN China
CX Christmas Island
CC Cocos (Keeling) Islands
CO Colombia
KM Comoros
CG Congo
CD Congo, the Democratic Republic of the
CK Cook Islands
CR Costa Rica
CI Côte d’Ivoire
CI Ivory Coast
HR Croatia
CU Cuba
CY Cyprus
CZ Czech Republic
DK Denmark
DJ Djibouti
DM Dominica
DO Dominican Republic
EC Ecuador
EG Egypt
SV El Salvador
GQ Equatorial Guinea
ER Eritrea
EE Estonia
ET Ethiopia
FK Falkland Islands (Malvinas)
FO Faroe Islands
FJ Fiji
FI Finland
FR France
GF French Guiana
PF French Polynesia
TF French Southern Territories
GA Gabon
GM Gambia
GE Georgia
DE Germany
GH Ghana
GI Gibraltar
GR Greece
GL Greenland
GD Grenada
GP Guadeloupe
GU Guam
GT Guatemala
GG Guernsey
GN Guinea
GW Guinea-Bissau
GY Guyana
HT Haiti
HM Heard Island and McDonald Islands
VA Holy See (Vatican City State)
HN Honduras
HK Hong Kong
HU Hungary
IS Iceland
IN India
ID Indonesia
IR Iran, Islamic Republic of
IQ Iraq
IE Ireland
IM Isle of Man
IL Israel
IT Italy
JM Jamaica
JP Japan
JE Jersey
JO Jordan
KZ Kazakhstan
KE Kenya
KI Kiribati
KP Korea, Democratic People’s Republic of
KR Korea, Republic of
KR South Korea
KW Kuwait
KG Kyrgyzstan
LA Lao People’s Democratic Republic
LV Latvia
LB Lebanon
LS Lesotho
LR Liberia
LY Libyan Arab Jamahiriya
LY Libya
LI Liechtenstein
LT Lithuania
LU Luxembourg
MO Macao
MK Macedonia, the former Yugoslav Republic of
MG Madagascar
MW Malawi
MY Malaysia
MV Maldives
ML Mali
MT Malta
MH Marshall Islands
MQ Martinique
MR Mauritania
MU Mauritius
YT Mayotte
MX Mexico
FM Micronesia, Federated States of
MD Moldova, Republic of
MC Monaco
MN Mongolia
ME Montenegro
MS Montserrat
MA Morocco
MZ Mozambique
MM Myanmar
MM Burma
NA Namibia
NR Nauru
NP Nepal
NL Netherlands
AN Netherlands Antilles
NC New Caledonia
NZ New Zealand
NI Nicaragua
NE Niger
NG Nigeria
NU Niue
NF Norfolk Island
MP Northern Mariana Islands
NO Norway
OM Oman
PK Pakistan
PW Palau
PS Palestinian Territory, Occupied
PA Panama
PG Papua New Guinea
PY Paraguay
PE Peru
PH Philippines
PN Pitcairn
PL Poland
PT Portugal
PR Puerto Rico
QA Qatar
RE Réunion
RO Romania
RU Russian Federation
RU Russia
RW Rwanda
SH Saint Helena, Ascension and Tristan da Cunha
KN Saint Kitts and Nevis
LC Saint Lucia
PM Saint Pierre and Miquelon
VC Saint Vincent and the Grenadines
WS Samoa
SM San Marino
ST Sao Tome and Principe
SA Saudi Arabia
SN Senegal
RS Serbia
SC Seychelles
SL Sierra Leone
SG Singapore
SK Slovakia
SI Slovenia
SB Solomon Islands
SO Somalia
ZA South Africa
GS South Georgia and the South Sandwich Islands
SS South Sudan
ES Spain
LK Sri Lanka
SD Sudan
SR Suriname
SJ Svalbard and Jan Mayen
SZ Swaziland
SE Sweden
CH Switzerland
SY Syrian Arab Republic
TW Taiwan, Province of China
TW Taiwan
TJ Tajikistan
TZ Tanzania, United Republic of
TH Thailand
TL Timor-Leste
TG Togo
TK Tokelau
TO Tonga
TT Trinidad and Tobago
TN Tunisia
TR Turkey
TM Turkmenistan
TC Turks and Caicos Islands
TV Tuvalu
UG Uganda
UA Ukraine
AE United Arab Emirates
GB United Kingdom
US United States
UM United States Minor Outlying Islands
UY Uruguay
UZ Uzbekistan
VU Vanuatu
VE Venezuela, Bolivarian Republic of
VE Venezuela
VN Viet Nam
VN Vietnam
VG Virgin Islands, British
VI Virgin Islands, U.S.
WF Wallis and Futuna
EH Western Sahara
YE Yemen
ZM Zambia
ZW Zimbabwe

Step 3 – Use VLOOKUP to Convert the Country Codes

Head back to the first tab of your spreadsheet and insert a new column for your country names. In the first empty cell in this column, start with the below formula:

=VLOOKUP(search_key, range, index, [is_sorted])

You’ll then need to amend this formula based on your spreadsheet layout. I’ve explained each element below:

  • Search_Key – In place of this, enter in the cell number of the country code you want to convert. For example, looking at our sheet below, I entered in B4.
  • Range – In place of this, enter in the name of your second tab (where you entered in the source data) and the range of columns that should be searched to find the country name. For example, if you look at our sheet below, I entered Sheet1!A:B.
  • Index – In place of this, you’ll need the exact column number within that range that includes the country name. For us, this is ‘2’ because Column B is the second column in the range. 

  • Is Sorted – This can be used if the data is sorted in some way. Ours isn’t, so here we entered ‘false’. 

Once you’ve entered in all those elements, your formula should look something like this:

=VLOOKUP(B4, Sheet1!A:B, 2, False)

Click enter, and voila! That empty cell should now show the correct country name.

Step 4 – Apply to All Cells

Once you’re happy that the formula in the first cell worked, Google Sheets or Microsoft Excel may suggest implementing this formula to the rest of the cells below it – in which case you can click yes.

Otherwise you can click on the bottom right hand corner of the cell and drag it across to all of the empty cells below to replicate.

And You’re Done!

I hope this guide saved you that all-important time of googling and testing formulas to convert country codes to country names.

Now you can easily analyse the country data from your Facebook ads and share this in accessible reports.

Next up...