Pivot Tables 1 - Free Excel Practice Online

This pivot table exercise uses a table of country development indicators: access to electricity, GDP, international tourism and military expenditure. You download the practice workbook and build the answers in Excel itself, using the PivotTable Fields pane on the right instead of writing formulas.

Each step changes one thing: switch the indicator in the filter, sort the values from largest to smallest, then swap the year from 2018 to 2000 and ask again which country comes first or last. By the end you can filter, sort and re-slice a pivot table without rebuilding it.

What you'll practice in this Pivot Tables 1 lesson

1. Download this file to get started with pivot tables!Look at all that data on development indicators! It would be easier to analyze with a pivot table.Click the "Insert" tab, then click "Pivot Table" and click "ok" on the Create Pivot Table dialog box.Note: No Excel? You can do this lesson for free in Google Sheets (File, Import, then upload the file) or in Excel for the web with a free Microsoft account. Both have pivot tables and filters, though the menus look a little different.Press Check Answer to continue.
2. The PivotTable Fields pane is on the right side. Not there? Click any cell inside the pivot table.Its top half lists the columns of the file: Country Name, Indicator Name, 2018 and the rest. Its bottom half has four boxes: Filters, Columns, Rows and Values. You fill a box by dragging a name from the list down into it.Drag Country Name to the Rows box.Drag 2018 to the Values box.If it says Count of 2018 and every number is 1, click Count of 2018, choose Value Field Settings, pick Sum and press OK. Excel counts instead of adding when a column has empty cells, and this one has some.Drag Indicator Name to the Filters box.In Google Sheets the same four boxes are in the pivot table editor on the right: press Add next to Rows, Values or Filters and pick the column.Press Enter to continue.
3. At the top of the pivot table, select the Indicator Name drop-down and choose Access to Electricity. Right click on one of the numbers in the 2018 column (not on a country name), click Sort, then sort smallest to largest. Which country had 11.02425194 for 2018?
4. Switch the indicator name to GDP. For countries where GDP is not blank, which country's GDP is the lowest in 2018?
5. Switch the indicator name to International Tourism and sort largest to smallest. Which country had the most visitors in 2018?
6. Switch the indicator name to Military Expenditure and sort largest to smallest. Which country had the largest percentage of military expenditure in 2018?
7. In the PivotTable Fields pane, un-check the box for 2018 and check the box for 2000. Sort from largest to smallest Which country had the largest percentage of military expenditure in 2000?
8. Change the Indicator Name to Access to Electricity and sort from smallest to largest.In which country did 67.3% of the population have access to electricity in 2000?
9. In which country did 77.5% of the population have access to electricity in 2005?
10. In which country did 99.8% of the population have access to electricity in 2005?

Pivot Tables 1 practice - FAQ