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
UPDATE 23rs February 2019: as I was browsing here and there, I suddenly hit on another way of getting icons and Sparklines together in the same cell … see below.
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!
UPDATE: using Right Click … Format Cells with ASCII codes!
For this technique, look at the GIF below and make sure you download the UPDATED Excel file, also below, to see how this technique works, using ASCII symbols or characters.
Additional Materials
Here is one more page from this blog on Sparklines that you will find useful:
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-1 … if you have previously downloaded this file, note this is the latest version
This GIF shows how to enter the icons using the Format Cells utility …
Duncan Williamson
23rd February 2019 REVISED version
Excel with Excel Master 