Skip to content

Email Newsletter Challenge 12

This week’s challenge will test your knowledge of charts. Specifically, did you know that you can create a geographical heat map in Excel and Google Sheets? If not, you’re about to learn how!

Download the spreadsheet challenge using the links below.

Challenge

Your goal is to create a geographical heat map using the data provided to you in the spreadsheet challenge.

The solution is different for Excel and Google Sheets, so be sure to follow the corresponding solution below.

Solution – Excel

To solve this problem in Excel, the first thing you want to do is convert each state to a geography data type. (This is a newer feature in Excel. If you are using an older version of Excel, this may not be available to you.)

Start by selecting all of the state names. Then go to Data > Convert to Geography.

Select all the state names which should now be geography values, and click the Insert Data box that shows up just above the dataset.

Then select Population to get the population for each state.

Once you have the population for each state, select the data and go to Insert > Maps > Filled Map.

This creates the heat map for you. All that’s left is to change or delete the chart title and then you’re done!

Solution – Google Sheets

For the Google Sheets solution, you will see that the population numbers have been included for each state. So your first step is to select all of the data, and then go to Insert > Chart.

In the chart editor on the right, change the chart type to Geo Chart.

Next, click on Customize and under Geo, select United States for the region.

Finally, select a light blue for Min. Select a slightly darker blue for Mid. And select a dark blue for Max.

     

You now have your geographical heat map in Google Sheets!

Thanks for challenging yourself this week, and I hope you had fun and learned something new.

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top