CREATE A FILLED MAP:
Using the data provided in Tab 1) create a filled map to display Hydro-Electricity production in GWh in the year 2019. Please include the following:
1) A header that describes the data that the map is displaying
2) Add Data Labels
a. Format the Column B data in Scientific Notation – 1 decimal – (you’ll find ‘Scientific’
under the Format Cells dialog box)
3) In the ‘Format’ tab in the ribbon, Select Series “GWh” >> Format Selection and make the map projection ‘Mercator’
4) Map area should be changed to ‘World’
5) Leave Map Labels as ‘None’
6) Use the automatic chart style to apply ‘Style 3’
7) Move the chart to a new sheet – rename the sheet ‘Filled Map’
USE A PIVOT TABLE TO CREATE AN INTERACTIVE MAP:
Using the data in Tab 2) create a Filled Map by creating a Pivot Table, a ‘fake’ data environment for the
data to use to the display the map, and a separate tab with the interactive map and slicer:
1) Build the pivot table from the data provided in Tab 2), and rename the new tab to ‘PT’
2) Place Country Code in the Rows quadrant, and the LifeExpectancy in the Values quadrant of the pivot table
3) Under the Design tab, and Report Layout convert the Pivot Table to the Tabular Form
4) Remove subtotals, and grand totals from the pivot table
5) In the same row as the column header in the pivot table and in column D, type the formula:
=IF(A3 = “”,””,A3). Copy and paste the formula to row 250 in columns D and E
6) Insert the Filled Map using the range D3:E250
7) Create a new tab – rename it ‘LifeMap’
8) Move the map to the ‘LifeMap’ tab
9) Click inside the pivot table, and in the PivotTable Analyze tab find the ‘Insert Slicer’ and select
‘Continent’
10) Cut and Paste the Slicer to the LifeMap tab, and place it in the upper left corner
11) Apply the following changes to the map:
a. Re-size the map to a height of 5” and a Width of 10”
b. Add a header with an appropriate description
c. Change the Map Projection to ‘Miller’
d. Change the Map area to ‘Only regions with data’
e. Under ‘Chart Design’ Add the chart element to show data labels
12) Apply the Continent Filter to ‘Asia’
13) Change the color and style of the map to one that you find most visually appealing
