Introduction

I attended an online seminar the other day on using Sparklines in financial modelling and towards the end of that webinar, someone asked if it were possible to have Conditional Formatting Icons included in the same cell as the Sparkline, The speaker said he did not know the answer to that but he thought it was not possible. I like a challenge and here is my answer: you CAN have a Sparkline and a Conditional Formatting Icon together in the same cell, like this, cells B14:D14:

Click the image to enlarge it

You can see the notes I have included in the worksheet that provide the small amount of guidance needed.

What I did was to create the Sparklines of the data in rows 7 to 11 for X, Y and X-Y. I set up or was given the Standard values of the three arrows I wanted to use and that relate to the Sparklines in some way.

Take a look at my Sparklines page, Sparklines 1, to see how to incorporate text and a function or formula in the same cell as a Sparkline and then, in cell B15 you will see that I have put a RANDBETWEEN() formula that generates the Standard values for X and cell B14 contains the relevant Sparkline as well as the formula =B15.

I then set the conditional format for cell B14 to include 3 Icon Arrows and I used the thresholds of >=80 for the Green arrow, >=20 for the Amber arrow and <20 for the Red arrow. I could have made that two or three or four or more arrows and that is entirely up to you. Similarly, the 80/20 percentages can be any values, which tends to mean the ones most appropriate for your example.

I could have worked on and formatted all three cells together, B14:D14 but in this case, I formatted B14 and then simply used the format painter to copy the format from B14 to C14 and then to D14. This assumes, of course, that the percentages we just discussed remain the same for them.

That is it and it should work for you too!

Here is one more page from this blog on Sparklines that you will find useful:

Sparklines 2

And here is a page on my largely empty web site that contains even more useful insights into Sparklines:

Sparklines from duncanwil.co.uk

Download the file sparklines_with_ICONS

Duncan Williamson

7th February 2019 REVISED version

%d bloggers like this: