 If we want to quickly and dynamically color code our data to give a visual impression of what it's like we might use something like conditional formatting we have data bars color scales highlighting cellsESIN so on really useful unfortunately those aren't available in graphs and charts. Kind of frustrating but well there we are it's not designed for like a thing so we can hack around it around it so I've got one here where the highest number here is 80 the lowest number is 16 and they're highlighted in green and red respectively so if I make a quick edit to this sheet it'll refresh and the numbers have changed these numbers are just being generated by a random function here the third one 91 is highest and the lowest one is 15 the second one make another change it changes one more time 24 is now the lowest one so this is picking up what is the highest what is the lowest and it's colouring the bar appropriately maybe there's some use for this as an accessibility note don't rely only on colouring for highlighting these kind of things you want to do it through multiple modes so what I've done is put a the rank order on a little data bar here it's just basically a data label so let's open up a new sheet and try to replicate this kind of thing oops maybe zoomed in a bit too far there so I'm just going to put in some random-ish numbers I'm not going to I'm not going to put it in with the RAND function otherwise every time we update the cell and this will just refresh and that will get really annoying for the example so I'm just going to leave these numbers that don't mean anything I've set it myself there I'm just going to label that column value I'm just going to set cell style to the heading here to it you know we can tell that it's the value so ideally when we insert the bar chart here I want number five the fifth value should be red the fourth value that being highest should be green but we can't do that there's no conditional formatting natively so what we're going to do to solve this is we're going to find the smallest value we're going to find the main block of the data that isn't smallest or largest and then also the largest so if I find what my smallest value is that's 123 my highest value is 764 that looks about right 165 or 66 234 all right so now if I highlight all of these and insert my bar graph well I've got two options I can either stack them side by side or clustering them together or stack them on top of each other now what's happened here is because some of these data series are blank we're not really stacking anything up so if I put 100 there and 100 there you can see they've appeared above and below it but that needs to be blank so because it's blank what we've got is the highest number is coloured in one thing so I'm going to go ahead and just turn that to be green because that's what I wanted and my lowest one I'm going to set that to be red it doesn't matter pick pick reasonable colors this is just traffic like like colors and the rest of it is that orange in the middle so what we've got is something that looks like that move across a little bit but we need a way of filling this in dynamically so this is the result that we want uh how we're going to code that up so what I'm going to start with is putting in a rank and for this we want the rank function uh not rank average rank dot eq is what we want average will start averaging numbers and you'll get a rank of 2.5 or something but here we go rank it just basically the difference between them is what happens if there's a tie so we want a rank and we want to rank all of these numbers according to the reference which is the same set of numbers so in reality most of those um tend to be the same input output and we respond uh when we return that it's going to spill down and what we find is that rank number one the highest one here 764 rank number six the lowest one is 123 I can also change that from order to ascending and it swaps it around so that the lowest one is now one all you need to do is be aware of which way around you put it so am I going to detect what the smallest number is so if I delete this column I'm going to use an if statement and I'm going to test if the rank and I'm going to put a hash just to select all of our ranks and so it's going to spill down and test all of them if it is equal to the maximum value with that again f2 the hash for the dinocrage so now if this number is equal to the highest one so i'm going to find the highest one is six so hopefully at number six this will trigger is true am going to return the value insert that as a range it should pick up the right one and if it's false i'm going to leave it completely blank and there we go that works when i've that's blank blank blank blank 123 perfect so i'm now just going to copy that and put it into the largest column and instead of max here i'm going to swap that for min but otherwise it's completely the same and i'm going to spill from error because i've got that 764 free typed into there so if I delete that it spills down correctly so the question is now how are we going to get this one because there are two things that I need to now do uh i want to make sure that this is not the minimum nor the maximum so i'm going to start with an if statement and i'm going to do if well if my rank press the hash there uh is different to the max f2 hash now i want that to be and uh if it's different to the min so i'm just going to free type that in there just so i want both of these statements to be true so we can if we want wrap that in and function put my little comma here uh but it's not really going to properly uh work well it doesn't really work at all um and that's because well the dynamic ranges don't play well with the added or logical functions unfortunately so i'm going to just make sure i've got these wrapped in some brackets so that they are definitely with each other and i'm going to put a multiply in between so if this is evaluated as a true or false it also evaluates as a zero or a one uh and that will be zero or one so if they are both true they're both one they'll come to one because i've multiplied them that's a nice way of getting around that particular problem i don't know whether it's a feature or a bug that the ands and the was don't work nicely with dynamic ranges but there is a work around at least so my value is true well i'm going to return the values if false blanket and they all and behold we've got that that works uh we've got these numbers being copied across where both of our statements are true they're different to the max and the min value and that is pretty much it that is how we are going to dynamically create uh a graph so let me just change the numbers here what if this was 55 we can see it updates that's now the lowest ranked one it's going to be turned red there it's appearing in the smallest column if this one is now maybe a thousand it's going to appear in the largest column it's going to appear here green so we can always add more things to it i suppose the last thing to cover is what i do for these data labels here because these are not quite uh data labels i'll show you if you add data labels to this data labels you can see you get numbers uh there are a bit all over the place and you kind of have to uh maybe delete them or hack around with them and stuff so what i did for that was i created a new column and i just put it let's just say um i'll do it i'll do 0.1 times the maximum value right so so what i heard was uh that i'll just have to lock that reference off for a second so everything is like 100 and what i added was added this as another data series so i'll add these labels the values were 100 and the reason that i did this should become obvious if you say change that you want this to be about 10 percent you want to move to about a tenth of the way up and then i went to chart design and changed the chart type to a combo these are really powerful for uh hacking things together uh so instead of the largest i want that to be the stacked column i want these to all be stacked columns i don't know why it always changes it but those are all the stacked columns as normal and then i wanted this to be a scatter so what i've now got are basically data points with some data labels on format those data labels to contain just a value from the cells which are my values so now i've got these numbers here or i can change it and select ranges put it is as the rank so that's what that is there and what i also did if i go insert um my illustrations icons this always takes a while to upload if i'm showing off if i look up maybe chart or some kind of thing like this i did that um made that really small change the graphics fill to white control x that and then managed to select the the data points hang on whoops select the data points and paste that in uh and you get an icon to go with it that you could put next to it um so that requires a little bit of faffing around with where you want to put the dot or i think it was some creative cropping and uncropping to set offset it to the site that i did there um but that's mostly just messing around and faffing more than anything that actually involves learning a new skill so i'll leave that one there you can now just delete this it's kind of getting in the way because i haven't put the effort into it um and if i just change the numbers you can see the data will just dynamically update just like a conditionally formatted uh table but now there's a chart object