Excel Small Multiples And Sparklines: Fun With Dynamic Arrays, Let, Lambda And Image

Last week I presented at the Nashville Modern Excel user group and before my session started Will Harvey, the organiser, did a quick run-through of what’s new in the Power Platform. He highlighted a service that piqued my interest called QuickChart which has a new Power Automate connector. QuickChart have an API that you can pass your data to and which will return an image of that data in a chart; they also have a free tier so you can play with it easily. It’s pretty cool (although this isn’t a formal endorsement) – try opening the following URL in your browser:

https://quickchart.io/chart?c={type:’bar’,data:{labels:[‘Q1′,’Q2′,’Q3′,’Q4′], datasets:[{label:’Users’,data:[50,60,70,180]},{label:’Revenue’,data:[100,200,300,400]}]}}

It made me think – can you use this with the new Excel Image function? And indeed you can. For example, here’s the URL above used from the Image function:

=IMAGE("https://quickchart.io/chart?c={type:%27bar%27,data:{labels:[%27Q1%27,%27Q2%27,%27Q3%27,%27Q4%27],%20datasets:[{label:%27Users%27,data:[50,60,70,180]},{label:%27Revenue%27,data:[100,200,300,400]}]}}",,1)

What could this be used for? Excel has had sparklines, mini charts that fit inside a cell, for a while but they’re not dynamic: the number of sparklines on a worksheet is fixed. I realised that by using QuickChart with dynamic array functions you can create dynamic arrays that return sparklines, giving you sparkline small multiples. Helpfully QuickChart explicitly support sparklines, for example:

https://quickchart.io/chart?c={type:’sparkline’,data:{datasets:[{data:[140,60,274,370,199]}]}}

Here’s a simple worked example. Let’s say you have a table on your worksheet called Sales that looks like this:

Here’s an Excel function, defined using the Lambda function, that displays a sparkline for a particular country and product combination:

=
LAMBDA(
product,
country,
IFERROR(
LET(
filterrows, FILTER(Sales, (Sales[Product] = product) * (Sales[Country] = country)), 
choosesales, CHOOSECOLS(filterrows, 4), 
concat, TEXTJOIN(",", FALSE, choosesales), 
IMAGE("https://quickchart.io/chart?c={type:'sparkline',data:{datasets:[{data:[" & concat & "]}]}}", , 1)), 
"")
)

By the way, this is exactly the kind of formula that the Advanced Formula Environment makes much easier to write; Mynda Treacy has a great video on it here.

This is what the function does:

• It takes two parameters, product and country
• The function definition is broken down into steps using the Let function
• The filterrows step filters the Sales table using the Filter function to get just the rows for the specified product and country
• The choosesales step uses the ChooseCols function to get the column that contains the sales values
• The concat step uses the TextJoin function to turn these sales values into a comma delimited list of values
• Finally, this list of values is injected into a call to the QuickChart API using the Image function
• The whole thing is wrapped in IfError to prevent any error values appearing, for example if a product or country value was passed to the function that didn’t appear in the Sales table

This function can now be called from a formula like so:

=GetGraph("Apples", "UK")

But we want something more dynamic. Consider the following:

Cell B3 contains a dynamic array formula using the Unique function to return all the distinct country names in the Sales table:

=UNIQUE(Sales[Country])

Cell C2 contains a similar formula but gets the distinct product names and uses the ToRow function to return these names in different rows:

=TOROW(UNIQUE(Sales[Product], FALSE))

Finally cell C3 uses the MakeArray function to call the GetGraph for each combination of country and product:

=MAKEARRAY(
ROWS(B3#),
COLUMNS(C2#),
LAMBDA(r,c,
GetGraph(INDEX(C2#,1,c),INDEX(B3#,r))
)
)

Here’s the output:

The good thing about this is that if new products or countries are added to the Sales table, like so:

…new columns and rows automatically appear to display them:

You’d probably need to get a paid subscription to QuickChart if you wanted to do this in the real world, but for me the point of all this is how much more the amazing new functionality added to Excel in the past few years (dynamic arrays, Image, Let, Lambda, the Advanced Formula Environment etc) allows you to do. Excel is almost as exciting as Power BI these days!