Sparklines in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What does this sparkline formula display?

Given the data in cells A1:A5 as 10, 20, 15, 25, 30, what is the output of the formula =SPARKLINE(A1:A5, {"charttype","line"}) in cell B1?

A A small line chart showing a rising trend from 10 to 30

B A bar chart with bars of equal height

C A pie chart representing the values

D An error message because SPARKLINE does not accept ranges

💡 Hint

Think about what a line sparkline shows for a series of numbers.

The SPARKLINE function with charttype 'line' creates a small line chart showing the trend of the data in the range. Since the values increase overall from 10 to 30, the line rises.

❓ Function Choice

intermediate

Which formula creates a column sparkline?

You want to create a column sparkline for data in cells B2:B6. Which formula will do this correctly?

A =SPARKLINE(B2:B6, "column")

B =SPARKLINE(B2:B6, {"type","line"})

C =SPARKLINE(B2:B6, {"charttype","column"})

D =SPARKLINE(B2:B6, {"chart","column"})

💡 Hint

Check the correct option name for chart type in SPARKLINE options.

The correct option to specify chart type is "charttype" with the value "column" inside an array. Option C uses the correct syntax.

🎯 Scenario

advanced

How to highlight the highest value in a sparkline?

You have sales data in C1:C10 and want to create a line sparkline in D1 that highlights the highest value point. Which option should you add to the SPARKLINE formula?

A {"highpoint", TRUE}

B {"maxpoint", TRUE}

C {"highlightmax", TRUE}

D {"peak", TRUE}

💡 Hint

Look for the option name that matches highlighting the highest point.

The option "highpoint" set to TRUE highlights the highest value in the sparkline. Other option names are invalid and cause errors or no effect.

📊 Formula Result

advanced

What error occurs with this sparkline formula?

What error will this formula produce? =SPARKLINE(A1:A5, {"charttype","pie"})

A Displays a pie chart sparkline correctly

B #VALUE! error because pie chart is not supported in SPARKLINE

C #REF! error due to invalid range

D No error, but shows a blank cell

💡 Hint

Check which chart types SPARKLINE supports.

SPARKLINE supports only line, column, and winloss chart types. Pie chart is not supported and causes a #VALUE! error.

❓ data_analysis

expert

Analyzing multiple sparklines for trends

You have monthly sales data for 3 products in columns A, B, and C (rows 1 to 12). You create sparklines in D1, E1, and F1 for each product's data. Which formula correctly creates sparklines for all three products in one step using array formula?

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

B =SPARKLINE(A1:A12, {"charttype","line"}) & SPARKLINE(B1:B12, {"charttype","line"}) & SPARKLINE(C1:C12, {"charttype","line"})

C =SPARKLINE(A1:A12, {"charttype","line"}), SPARKLINE(B1:B12, {"charttype","line"}), SPARKLINE(C1:C12, {"charttype","line"})

D =ARRAYFORMULA(SPARKLINE(A1:C12, {"charttype","line"}))

💡 Hint

Think about how to apply SPARKLINE to multiple columns at once.

Using ARRAYFORMULA with SPARKLINE allows creating sparklines for each column in the range separately. Option D tries to create one sparkline for all data, which is invalid. Options B and C are invalid syntax.