Filter with Excel

Somsak Chanaim

International College of Digital Innovation, CMU

July 2, 2025

Gapminder

About This Data Set

Gapminder is a sample dataset available in the gapminder package in R. It is designed for teaching and learning about data analysis, particularly for creating visualizations and performing data exploration using ggplot2 and other tools in R.

This dataset presents indicators related to economic and health metrics across various countries around the world during the period 1952–2007.

The data is provided by the Gapminder Foundation, an organization that develops learning tools about the world and sustainable development.

Variables in the Gapminder Dataset

The Gapminder dataset contains six main variables:

  1. country: The name of the country (e.g., Afghanistan, Brazil, China)

  2. continent: The continent where the country is located (Africa, Americas, Asia, Europe, Oceania)

  3. year: The year the data was recorded (1952, 1957, 1962, …, 2007)

  4. lifeExp: Life expectancy of the population in each country (unit: years)

  5. pop: Population of the country

  6. gdpPercap: GDP per capita, calculated by dividing the country’s GDP by its population (unit: US dollars)

Data Frame: Gapminer

You can download this dataset from
Google Drive
to explore and study in the upcoming topics.

Before Applying Filters in Excel

You must convert your data range into an Excel Table first:

  1. Select the data that you want to convert into a table.

  2. Go to “Insert” → “Table”.

  3. Make sure to check whether your data has headers.

  4. Click OK → Your data will be converted into a dynamic Excel Table.

Excel Shortcut to Convert Data into a Table

🖥️ Windows:

Ctrl + T

🍏 Mac:

Command (⌘) + T

How to Use the Shortcut to Convert Data into a Table

  1. Select the data range you want to convert into a table. (Press Ctrl + A (or Command + A on Mac))

  2. Press Ctrl + T (or Command + T on Mac).

  3. The Create Table dialog box will appear.

  4. Check “My table has headers” if your data includes headers.

  5. Click OK → The data will be instantly converted into a Table!

Benefits of Converting Data into a Table

  • Easily filter and sort your data

  • Apply styles and formatting automatically

  • Automatically expand when new data is added

  • Conveniently use with Pivot Tables, Charts, and Power Query

Extra Tip:

If you want to remove the table but keep the data, press Ctrl + Shift + T,
or go to Table Design → Convert to Range to convert the table back to a normal range.

Filtering in Excel

Filtering is a feature that allows you to view only specific data in your table, such as filtering by values, numbers, dates, or text. Follow the steps below:

How to Use the Filter Tool

  1. Select the Data Range

    • Click and drag to select the range of data you want to filter (make sure it includes headers for clarity).

  2. Enable the Filter

    • Go to the Data tab on the Ribbon and click Filter
      (or press Ctrl + Shift + L on Windows).

    • Small dropdown arrows will appear on the column headers.

  3. Apply Filters

    • Click the arrow in the header of the column you want to filter.

    • Choose a filter type such as:

      • Text Filters: For text-based conditions (e.g., begins with, contains, does not equal).

      • Number Filters: For numeric conditions (e.g., greater than, less than, between).

      • Date Filters: For dates (e.g., today, yesterday, this week).

    • You can also uncheck specific values to display only the data you want.

  4. Clear Filters

    • Click the arrow in the filtered column and select Clear Filter.

    • Or go to the Data tab and click Filter again to turn it off entirely.

Example: Filtering Data from the Gapminder Dataset

1. Filter Data for Countries in Asia

Steps:

  1. Click the header of the continent column.

  2. Use the filter dropdown to select only Asia.

Result:

You will see only data for countries in the Asian continent, such as Afghanistan, China, and India.

2. Filter Data for the Year 2007 Only

Steps:

  1. Click the header of the year column.

  2. Use Number Filters, select Equals, and enter 2007.

Result:

You will see only data for all countries in the year 2007.

3. Filter Countries with Life Expectancy Greater Than 70 Years

Steps:

  1. Click the header of the lifeExp column.

  2. Use Number Filters, select Greater than, and enter 70.

**Result:

You will see only countries with a life expectancy above 70 years, such as Japan and Sweden.

4. Filter Countries with GDP per Capita Greater Than $10,000

Steps:

  1. Click the header of the gdpPercap column.

  2. Use Number Filters, select Greater than, and enter 10000.

Result:

You will see only countries with high GDP per capita, such as the USA and Norway.

5. Filter Countries with a Population Between 1 and 10 Million

Steps:

  1. Click the header of the pop column.

  2. Use Number Filters, select Between, and enter the range 1,000,000 to 10,000,000.

Result:

You will see only countries with a population in that range, such as Jordan and Finland.

Practice Question

1. Which countries in Africa had a life expectancy (lifeExp) of less than 50 years in 1962?

Hint:

  • Use a filter on the continent column and select Africa.

  • Then filter the year column by selecting 1962.

  • Finally, filter the lifeExp column with the condition Less than 50.

2. Find countries with a population (pop) greater than 100 million in the year 2002

Hint:

  • Filter the year column by selecting 2002.

  • Use a filter on the pop column with the condition Greater than 100,000,000.

3. Which countries in Europe had a GDP per capita (gdpPercap) less than $5,000 in the year 1987?

Hint:

  • Filter the continent column by selecting Europe.

  • Set a filter on the year column to 1987.

  • Use a filter on the gdpPercap column with the condition Less than 5000.

4. Which countries had the highest GDP per capita (gdpPercap) in each continent in the year 1957?

Hint:

  • Filter the year column by selecting 1957.

  • Sort the gdpPercap column in descending order.

  • Use the continent filter to identify the top country in each continent.

5. Find countries with a population (pop) between 20 and 50 million in the year 1972, and a life expectancy (lifeExp) greater than 60 years

Hint:

  • Filter the year column by selecting 1972.

  • Use a filter on the pop column with the condition Between 20,000,000 and 50,000,000.

  • Then filter the lifeExp column with the condition Greater than 60.

6. Which countries in the Americas had a consistently increasing life expectancy (lifeExp) from 1962 to 1987?

Hint:

  • Filter the continent column by selecting Americas.

  • Set a filter on the year column to select the range 1962–1987.

  • Look for countries where the lifeExp value increases continuously over this period.

7. Find countries in the year 2007 with a GDP per capita (gdpPercap) greater than $20,000 and a population (pop) of less than 1 million

Hint:

  • Filter the year column by selecting 2007.

  • Use a filter on the gdpPercap column with the condition Greater than 20000.

  • Use a filter on the pop column with the condition Less than 1,000,000.

8. Which countries in Asia had a population increase of more than 50% between 1952 and 2007?

Hint:

  • Filter the continent column by selecting Asia.

  • View the population values for the years 1952 and 2007.

  • Calculate the percentage increase and filter for countries with more than a 50% increase.

9. In which years did countries from Oceania have a life expectancy (lifeExp) below 65 years?

Hint:

  • Filter the continent column by selecting Oceania.

  • Use a filter on the lifeExp column with the condition Less than 65.

  • Check the year column to identify the years that match the condition.

10. Which countries in Africa had a GDP per capita (gdpPercap) between $5,000 and $10,000 in the year 1977?

Hint:

  • Filter the year column by selecting 1977.

  • Filter the continent column by selecting Africa.

  • Use a filter on the gdpPercap column with the condition Between 5000 and 10000.