Geo charts and maps in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

Sales data by country used to create a Geo chart showing sales distribution on a map.

Cell	Value
A1	Country
B1	Sales
A2	USA
B2	1000
A3	Canada
B3	700
A4	Mexico
B4	400
A5	Brazil
B5	600

Formula Trace

GEO_MAP(A2:B5)

Step 1: A2:B5

Step 2: GEO_MAP([["USA", 1000], ["Canada", 700], ["Mexico", 400], ["Brazil", 600]])

Cell Reference Map

    A       B
1 Country  Sales
2  USA     1000  <-- Used for country name and sales value
3  Canada   700  <-- Used for country name and sales value
4  Mexico   400  <-- Used for country name and sales value
5  Brazil   600  <-- Used for country name and sales value

The formula references the range A2:B5 which contains country names and their sales numbers.

Result

[Geo Chart Map]
Countries colored from darkest (USA) to lightest (Mexico) based on sales values:
- USA: darkest color (1000 sales)
- Canada: medium dark (700 sales)
- Brazil: medium light (600 sales)
- Mexico: lightest color (400 sales)

The Geo chart visually shows sales distribution by coloring each country on the map according to its sales value.

Sheet Trace Quiz - 3 Questions

What does the range A2:B5 represent in the GEO_MAP formula?

A Country names and their sales values

B Only sales values

C Only country names

D Random numbers

Key Result

GEO_MAP(range) creates a map coloring regions based on numeric values in the range.

Practice

1. What is the main purpose of a Geo chart in Google Sheets?

easy

A. To calculate sums of numbers

B. To create pie charts from numerical data

C. To sort data alphabetically

D. To display data on a map based on location

Solution

1. Step 1: Understand Geo chart function

   A Geo chart visually shows data on a map using location information.

2. Step 2: Compare options

   Only To display data on a map based on location describes showing data on a map; others describe unrelated chart types or functions.

3. Final Answer:

   To display data on a map based on location -> Option D

4. Quick Check:

   Geo chart = map display [OK]

Hint: Geo charts always show data geographically on maps [OK]

Common Mistakes:

• Confusing Geo charts with pie or bar charts
• Thinking Geo charts sort data instead of mapping it

2. Which of the following is the correct way to insert a Geo chart in Google Sheets?

easy

A. Select data, then Insert > Chart, and choose Geo chart type

B. Type =GEOCHART() in a cell

C. Use Format > Geo chart

D. Right-click a cell and select Geo chart

Solution

1. Step 1: Recall Geo chart insertion method

   Geo charts are inserted via the menu: Insert > Chart, then select Geo chart type.

2. Step 2: Evaluate options

   Only Select data, then Insert > Chart, and choose Geo chart type correctly describes this process; others mention invalid methods or functions.

3. Final Answer:

   Select data, then Insert > Chart, and choose Geo chart type -> Option A

4. Quick Check:

   Insert > Chart > Geo chart = correct method [OK]

Hint: Use Insert menu to add Geo charts, not formulas [OK]

Common Mistakes:

• Trying to use a formula to create Geo charts
• Looking for Geo chart in Format menu

3. Given this data in columns A and B:
A1: Country
A2: USA
A3: Canada
B1: Sales
B2: 100
B3: 50
What will a Geo chart show when created from this data?

medium

A. A blank chart because data is incomplete

B. A bar chart comparing sales numbers

C. A map highlighting USA and Canada with sales values

D. A pie chart showing sales percentages

Solution

1. Step 1: Understand data structure

   Column A has country names, column B has sales numbers, suitable for Geo chart mapping.

2. Step 2: Predict Geo chart output

   The Geo chart will highlight USA and Canada on the map with sales values shown by color or size.

3. Final Answer:

   A map highlighting USA and Canada with sales values -> Option C

4. Quick Check:

   Country + number = Geo chart map [OK]

Hint: Geo charts need location names plus numbers to show data [OK]

Common Mistakes:

• Expecting bar or pie charts from Geo chart data
• Thinking Geo charts require more columns

4. You created a Geo chart but it shows no countries highlighted. What is the most likely cause?

medium

A. Data contains only numbers without locations

B. Location names are misspelled or unclear

C. Chart type is set to Bar chart

D. Sheet is protected and cannot display charts

Solution

1. Step 1: Identify common Geo chart issues

   Geo charts rely on correct location names; misspellings cause no highlights.

2. Step 2: Evaluate other options

   Chart type mismatch or missing locations also cause issues, but misspelled names are most common.

3. Final Answer:

   Location names are misspelled or unclear -> Option B

4. Quick Check:

   Correct location names = visible Geo chart [OK]

Hint: Check spelling of locations if Geo chart is blank [OK]

Common Mistakes:

• Ignoring location name spelling errors
• Assuming chart type is always correct

5. You want to create a Geo chart showing sales by US states. Your data has state abbreviations (e.g., CA, TX) in column A and sales numbers in column B. What should you do to ensure the Geo chart displays correctly?

hard

A. Replace state abbreviations with full state names

B. Add a column with country names next to states

C. Use only numbers without state names

D. Change chart type to Pie chart

Solution

1. Step 1: Understand Geo chart location requirements

   Geo charts often require full location names for accurate mapping, as abbreviations may not be recognized.

2. Step 2: Choose best data format

   Replacing abbreviations with full state names ensures the Geo chart can identify and highlight states correctly.

3. Final Answer:

   Replace state abbreviations with full state names -> Option A

4. Quick Check:

   Full names improve Geo chart accuracy [OK]

Hint: Use full location names, not abbreviations, for Geo charts [OK]

Common Mistakes:

• Using abbreviations that Geo chart can't recognize
• Adding unrelated columns instead of fixing names