Geo charts and maps in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate Percentage for Geo Chart Data

You have a table with countries in column A and their sales numbers in column B. You want to add a column C that shows the percentage of total sales for each country to use in a Geo Chart. Which formula in cell C2 correctly calculates this percentage and can be dragged down?

Google Sheets

Country | Sales | Percentage
USA     | 100   | 
Canada  | 50    | 
Mexico  | 25    | 

A =B2/SUM(B$2:B$4)

B =B2/SUM(B2:B4)

C =B2/SUM($B$2:$B$4)

D =B2/SUM(B$2:B4)

💡 Hint

Use absolute references to fix the total sales range when copying the formula down.

The formula =B2/SUM($B$2:$B$4) uses absolute references for the total sales range, so when dragged down, the denominator stays the same, correctly calculating each country's percentage.

❓ Function Choice

intermediate

Choose the Correct Function to Format Country Names

You want to create a Geo Chart in Google Sheets, but your country names are in lowercase (e.g., "usa", "canada"). Which function should you use to convert the country names to proper case (first letter uppercase) in a new column?

A =PROPER(A2)

B =UPPER(A2)

C =CAPITALIZE(A2)

D =LOWER(A2)

💡 Hint

You want the first letter of each word capitalized, not all uppercase or lowercase.

The PROPER function capitalizes the first letter of each word, which is ideal for country names.

🎯 Scenario

advanced

Troubleshooting Geo Chart Display Issues

You created a Geo Chart in Google Sheets using country names and sales data. However, some countries are not highlighted on the map even though they have sales data. What is the most likely cause?

A The sheet is protected and prevents chart updates.

B Sales data contains zeros instead of positive numbers.

C The chart type selected is not Geo Chart.

D Country names are misspelled or not recognized by Google Sheets Geo Chart.

💡 Hint

Check if the country names match official country names exactly.

Geo Charts rely on recognized country names or codes. Misspelled or unrecognized names cause those countries not to appear highlighted.

❓ data_analysis

advanced

Analyzing Regional Sales with Geo Chart

You have sales data by US states in columns A (State) and B (Sales). You want to create a Geo Chart showing sales by state. Which data preparation step is necessary before creating the Geo Chart?

A Ensure state names are full official names (e.g., "California") or use two-letter state codes (e.g., "CA").

B Convert sales numbers to percentages of total sales.

C Sort the data alphabetically by sales amount.

D Remove any states with zero sales from the data.

💡 Hint

Geo Charts require location names or codes that Google Sheets recognizes.

Geo Charts need recognized location names or codes. Using official state names or standard two-letter codes ensures the map highlights states correctly.

🧠 Conceptual

expert

Understanding Geo Chart Color Scaling

In a Geo Chart showing sales by country, you notice that countries with very high sales overshadow countries with moderate sales, making the map hard to interpret. What is the best way to adjust the color scale to improve visibility of moderate sales?

A Increase the maximum value in the color scale to the highest sales number.

B Use a logarithmic scale for the color gradient to reduce the impact of very high values.

C Remove countries with the highest sales from the data set.

D Change the chart type to a bar chart instead of a Geo Chart.

💡 Hint

Think about how to make large differences in data less extreme visually.

Applying a logarithmic scale to the color gradient compresses large values, making moderate values more visible and improving overall map readability.