Sparklines (LINE, BAR, COLUMN) in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What does this SPARKLINE formula display?

Given the data in cells A1:A5 as {5, 10, 15, 10, 5}, what type of sparkline will appear with this formula?

=SPARKLINE(A1:A5, {"charttype", "line"})

Google Sheets

=SPARKLINE(A1:A5, {"charttype", "line"})

A A line graph showing a peak at the middle value (15) and lower values at start and end

B A bar chart with 5 bars of equal height

C A column chart with decreasing heights from left to right

D An error because the charttype is misspelled

💡 Hint

Think about what a line sparkline does with a range of numbers.

The formula creates a line sparkline that connects the points 5, 10, 15, 10, 5, showing a peak at 15 in the middle.

📊 Formula Result

intermediate

What output does this BAR sparkline produce?

If cells B1:B4 contain {3, 6, 9, 12}, what will this formula show?

=SPARKLINE(B1:B4, {"charttype", "bar"})

Google Sheets

=SPARKLINE(B1:B4, {"charttype", "bar"})

A A line sparkline connecting points 3, 6, 9, 12

B A vertical column sparkline with 4 columns of equal height

C A blank cell because bar sparklines need numeric arrays

D A horizontal bar sparkline with 4 bars increasing in length from left to right

💡 Hint

Bar sparklines show horizontal bars representing values.

The bar sparkline draws horizontal bars for each value, so bars get longer as values increase from 3 to 12.

❓ Function Choice

advanced

Which SPARKLINE formula creates a column chart with red bars?

You want a column sparkline with red bars for data in C1:C6. Which formula is correct?

A =SPARKLINE(C1:C6, {"charttype", "column", "color", "red"})

B =SPARKLINE(C1:C6, {"charttype", "bar", "color", "red"})

C =SPARKLINE(C1:C6, {"charttype", "column", "color1", "red"})

D =SPARKLINE(C1:C6, {"charttype", "line", "color", "red"})

💡 Hint

Check the correct option name for bar color in column sparklines.

Option A uses the correct charttype 'column' and the correct option 'color' to set bar color to red.

🎯 Scenario

advanced

Troubleshoot why a sparkline shows a flat line

You entered =SPARKLINE(D1:D5, {"charttype", "line"}) but the sparkline shows a flat line even though data varies. What is the most likely cause?

A The formula needs an extra argument to show variation

B The data range D1:D5 contains text or blank cells instead of numbers

C The sparkline only works with bar charts

D The charttype 'line' is not supported in Google Sheets

💡 Hint

Sparklines need numeric data to show variation.

If the data range has text or blanks, the sparkline treats values as zero, resulting in a flat line.

❓ data_analysis

expert

Analyze the effect of missing options in SPARKLINE

Given data in E1:E7 as {2, 4, 6, 8, 6, 4, 2}, what will this formula display?

=SPARKLINE(E1:E7)

Choose the best description of the sparkline output.

Google Sheets

=SPARKLINE(E1:E7)

A A column sparkline with bars increasing then decreasing

B An error because charttype is missing

C A line sparkline showing a peak at 8 and symmetric shape

D A bar sparkline with 7 bars of equal length

💡 Hint

SPARKLINE defaults to a line chart if no charttype is given.

Without specifying charttype, SPARKLINE defaults to a line chart showing the data shape with a peak at 8.

Practice

1. What does the SPARKLINE function do in Google Sheets?

easy

A. Formats cells with colors

B. Calculates the sum of a range of numbers

C. Sorts data alphabetically

D. Creates a small chart inside a cell to show data trends

Solution

1. Step 1: Understand the purpose of SPARKLINE

   The SPARKLINE function is designed to create tiny charts inside cells to visually represent data trends.

2. Step 2: Compare with other options

   Options A, B, and C describe different functions unrelated to SPARKLINE.

3. Final Answer:

   Creates a small chart inside a cell to show data trends -> Option D

4. Quick Check:

   SPARKLINE = small chart inside cell [OK]

Hint: SPARKLINE = tiny chart inside a cell [OK]

Common Mistakes:

• Confusing SPARKLINE with SUM or SORT functions
• Thinking SPARKLINE changes cell colors
• Assuming SPARKLINE creates full-size charts

2. Which of the following is the correct syntax to create a line sparkline for data in cells A1 to A5?

easy

A. =SPARKLINE(A1:A5, {"charttype": "line"})

B. =SPARKLINE(A1:A5, {"charttype", "line"})

C. =SPARKLINE(A1:A5, {charttype = "line"})

D. =SPARKLINE(A1:A5, {charttype = line})

Solution

1. Step 1: Recall correct option syntax for SPARKLINE options

   Options must use key-value pairs with colon and quotes for strings, like {"charttype": "line"}.

2. Step 2: Check each option

   =SPARKLINE(A1:A5, {"charttype": "line"}) uses correct syntax with colon and quotes. Options A, C, and D use incorrect separators or missing quotes.

3. Final Answer:

   =SPARKLINE(A1:A5, {"charttype": "line"}) -> Option A

4. Quick Check:

   Options use colon and quotes for key-value pairs [OK]

Hint: Use colon and quotes for options in SPARKLINE [OK]

Common Mistakes:

• Using comma instead of colon in options
• Omitting quotes around option values
• Using equal sign instead of colon

3. Given the data in cells A1:A4 as 5, 10, 15, 20, what will the formula =SPARKLINE(A1:A4, {"charttype": "bar"}) display?

medium

A. A tiny line chart showing increasing trend

B. A tiny bar chart with bars increasing in height from left to right

C. A tiny column chart with bars decreasing in height

D. An error because bar chart needs more options

Solution

1. Step 1: Identify chart type and data

   The formula uses charttype "bar" with data 5, 10, 15, 20 which increases.

2. Step 2: Understand bar chart behavior in SPARKLINE

   Bar chart draws horizontal bars with length proportional to values, so bars increase left to right.

3. Final Answer:

   A tiny bar chart with bars increasing in height from left to right -> Option B

4. Quick Check:

   Bar chart = horizontal bars showing values [OK]

Hint: Bar chart shows horizontal bars sized by values [OK]

Common Mistakes:

• Confusing bar chart with line or column chart
• Thinking bars decrease instead of increase
• Assuming error due to missing options

4. The formula =SPARKLINE(A1:A5, {"charttype" "column"}) returns an error. What is the mistake?

medium

A. Column chart type is not supported

B. Wrong range reference

C. Missing colon between option name and value

D. Data range must be horizontal, not vertical

Solution

1. Step 1: Check option syntax

   The options must have a colon between key and value, like {"charttype": "column"}.

2. Step 2: Identify error cause

   The formula uses {"charttype" "column"} missing the colon, causing syntax error.

3. Final Answer:

   Missing colon between option name and value -> Option C

4. Quick Check:

   Options need colon between key and value [OK]

Hint: Options need colon between key and value [OK]

Common Mistakes:

• Omitting colon in options
• Thinking column chart is unsupported
• Assuming range orientation causes error

5. You want to show a sparkline column chart for sales data in B2:B10 but highlight the highest value bar in red. Which formula correctly applies this?

hard

A. =SPARKLINE(B2:B10, {"charttype": "column", "color": "blue", "maxcolor": "red"})

B. =SPARKLINE(B2:B10, {"charttype": "column", "color": "red", "maxcolor": "red"})

C. =SPARKLINE(B2:B10, {"charttype": "column", "color": "red", "max": MAX(B2:B10)})

D. =SPARKLINE(B2:B10, {"charttype": "bar", "maxcolor": "red"})

Solution

1. Step 1: Identify correct chart type and color options

   We want a column chart with the highest bar colored red. The option "maxcolor" sets the color for the max value bar.

2. Step 2: Check each option

   =SPARKLINE(B2:B10, {"charttype": "column", "color": "blue", "maxcolor": "red"}) sets charttype to column, default bars blue, and maxcolor red, which highlights the highest bar correctly. =SPARKLINE(B2:B10, {"charttype": "column", "color": "red", "max": MAX(B2:B10)}) incorrectly uses "max" option which is invalid. =SPARKLINE(B2:B10, {"charttype": "column", "color": "red", "maxcolor": "red"}) sets all bars red, not just max. =SPARKLINE(B2:B10, {"charttype": "bar", "maxcolor": "red"}) uses bar chart instead of column.

3. Final Answer:

   =SPARKLINE(B2:B10, {"charttype": "column", "color": "blue", "maxcolor": "red"}) -> Option A

4. Quick Check:

   Use maxcolor to highlight highest bar [OK]

Hint: Use maxcolor option to color highest bar red [OK]

Common Mistakes:

• Using color option to color all bars red
• Using invalid max option
• Choosing wrong chart type (bar instead of column)