In this lecture, I'd like to show you how you can create a non-standard Column chart or an info chart in excel like this one some time ago Udemy published a very informative and interesting report that had this chart in it which grabbed my attention And I was like wow, I really want to make this in excel That's what I'm going to show you how to do in this lecture. The thing I like about this chart is that it's informative, It's also visually appealing and it's different to what people are used to seeing - so they pay more attention Now this chart.... This is something that people are used to seeing. Some of you see this every single day It is visually effective because it brings attention to the key chart elements that need emphasis, but if I put this side-by-side Which one grabs your attention more? For me personally - this one definitely grabbed my attention. I wanted to create it in excel in a way that is also dynamic So it saves you time whenever new data comes in. Now the highlighted part the part that's conditionally formatted, is something that I also wanted to make dynamic depending on the condition. In this example, I have some random data and the number of users per weekday that are logged into the system And I want to highlight the weekday that has the maximum number of users logged in So if I get new Data and Tuesday happens to be 600 everything will update automatically First I'll show you how to get this dynamic conditional formatting effect And then I'm going to show you how you can bring shapes in charts And finally how to tweak them for optimal effect because we don't want to have stretched out shapes So let's start with the conditional formatting part This is the data table which we're going to use to create our amazing chart We have the number of users per weekday, [and] we're going to start off by inserting a very basic Column chart Now let's just make [some] minor adjustments. So remove gridlines remove axis, add data labels Let's Decrease the Gap width so that they're closer together. Let's go with 70% I'm going to update the title so that basically equals this Take away the border. So I have this in [my] quick access toolbar [and] that's the outline if you don't have it you can get to it by Going to format and removing the shape outline from here, so while I'm here. Let's also change the shape fill To grey. That's the basic chart now I want to conditionally format this so that the weekday that has the most users becomes automatically another color if I were to do this manually so here Thursday has 500 right I could click again on it and Change the shape fill of only this one Category to green but it's not dynamic because if Tuesday is 600 This is going to remain green and that's not so what I want to happen. Is that this one becomes green and this one Thursday turns back to grey Now here's the key that whenever you want to Conditionally format anything in your charts you need to add a new data series What I'm going to do is add a data series that basically consists of only one Bar and that bar is only going to pop up whenever I have the maximum so basically everything else is going to be zero but only There where the maximum number is I'm going to have let's say a green bar. I need a new series The series is going to have zeros and that maximum number, but that maximum number has to pop up adjacent to the Category, so [my] formula is going to be if this equals the Max of this data set I'm going to press F4 to fix it because I want to copy it down if That's the case then give me back this number, so if that's a maximum otherwise give me zero And let's copy this down so that's my max series, so I'm going to add this to my chart by going to select Data add That's the name of the series always always include the series name in your chart otherwise you're going to mix them up and That's that so it's plotting it beside it But that's something I can [easily] [change] So I'm going to go here to series option make sure that I select [the] whole series and I'm going to change the fill color first to a green and Now we're going to go here and for series overlap you see there are minus 27 That's why there's a bit of space between them. I'm going to change it to a hundred percent So now this one is on top of the other series The order of it is basically defined by this [see] if I push this one down See the gray is covering the green if I push it back up The Green is covering the gray but for the other [datasets] is all zero That's why this gray is the only one that's being covered by the green This is fully dynamic because if I change Tuesday to 600 - it jumps. So that's the first step now How do I bring shapes to the chart, so how do I change the shape of this to something else? So let me just take a copy of this and paste it here From insert shapes I can pick a shape [that] I want I Want to have the one that is a bit rounded at the edges That's this one Just draw it out here. You can turn it around and change the color to my grey The Shape Outline to nothing - just take it away That's the shape let's say that I want to use all I have to do to replace these bars with this one is to go Control C. On [my] shape click on the bar Make sure the entire series is selected so also the one behind this green one and go control V To get the green one [I] have to add that [to] the second series because remember I have two series right so if I want my second series to be green and this shape I Have to add that [to] the series as well okay, so I'm going to change this to [the] green and [CTRl] C Click on the maximum series. That's the one that's highlighted and go control V so now I've added shape to chart, and it should be dynamic, [so] let's go and change Tuesday back to 300 [ok] jumps back as well, but you see what the problem is here for the data sets that are bigger So here for 500 the shape is really Pointy and here, it's not So basically it gets stretched out like this For bigger data sets and becomes like this for smaller data sets That's something that I want to avoid to do that. I need to [use] a trick and the trick is to Basically to split each of these data sets so the 400 to two data sets The top Data set is going to be identical for [all] of them And that's going to be let's say this shape and the bottom data set is going to be a shape that doesn't mind being stretched out, and that shape is a rectangle to [add] 2 data series to this I could [use] a stacked Column [chart] That means I just have to change my data set a little bit. I'm going to copy this part Paste it here Let's not make you too complex for the beginning I'm just going to do the gray series first and then at the last that we're going to add the green series to it Okay, so I need to have two sacks Let's call them stack one and Stack two That too is going to be a number That's common to all of them right [so] that can [be] the minimum of this data set I'm going to fix it [and] now depending on my data set I could adjust this even more I could go - If I find that this doesn't really represent my shape quite. Well, it makes it squash that I can change this [number] that's the size of my top stack now my bottom stack is basically going to be my Original value [minus] my top stack I'm going to copy that down Okay, so let's insert this chart. I'm going to highlight this hold down control highlight this and Insert a column chart, but now a stacked column chart So this is our top stack they all have the same size so we don't have the problem of having our getting stretched out You're just adding the title taking these away and let's reduce the [gap] [flip] again I'm going to reduce it even more here. Let's go to [fifty] Okay, so I need two shades. That's my shape number one My shape number two is going to be the rectangle down here Let me copy this first and click on this paste That looks good [so] [I] can either bring in a rectangle [shape] here But since this already seems in sync with this one, I can just change the color of this to the same color Now what about the data labels? Well, I have two stacks here right in this top stack I'm going to highlight them right mouse click and add data labels to it Let's make them stand out make them white Bold and let's get them to be bigger But the data label is not showing what I want to show right is showing the size of this stack So that's not what we want. [I] want to show basically the value from cells and the value that I want to show is this So if you have excel 2013 or excel 2016 you can do what I'm doing right now If you have an older version of excel like excel 2010 and seven what you have to do is click on each of these data labels and connect them with a formula to your data Okay, but it's doable it. Just takes a bit longer Take away the border of this one want to do is bring in the Axis labels [on] top of each of these bars so I can remove the axis completely How do I do that because that's not a part of the axis options? You always have to leave the x-Axis on the bottom you can't bring the labels up there But you can use a trick and the trick is remember we had a stack right and the bottom stack can also have data labels so I can activate them and I can adjust their positioning so that they're on the bottom inside base And now all I have to do is do the same trick and use value from cells, but now instead of showing actual numbers I'm going to show my axis labels and Remove these two so again let's adjust the formatting That looks really nice. It's very harmonized so as a next step. I'm going to get my conditional formatting in there What I have to do is exactly what we did in the first step here where we added a max series We need to have another stacked Column chart in here That's going to cover our gray snack if that stack happens to be the maximum So let's call it stack one Max and back to Max I'm just going to move this a little bit out of the [way] Okay formula is the same we need to check if this number equals the Max of this series press F4 Then now not show the number but show the value of Stack one otherwise show zero That's for Thursday now. I'm going to just copy this press escape Paste this here, and just move this one to stack two because my east condition is basically the same So now I bet I have my series. I need to bring it in my chart right mouse Click select Data and Don't forget to series name series values of my first [pack] is this Add again or you can see it popped up right on top So that's my second stack for the max, and that's the series values so press [enter] Like that so that looks a bit funny, but basically it's a stacked chart right so whatever I'm adding to it It's going to add it as a separate snack on top. I don't want it to add on top I actually want it to cover it So now layering this by 100% is not going to work in a facts Column chart Right because the series overlap is 100% what I can do instead is to Change them to only these two stacks to the secondary axis So just came back to the change chart type because I can do them quickly here So that's the fact one [max] and stack two max see now it covered it And the good thing about this is that this is a mac series that I'm putting on the secondary axis [I] have that same max on the primary axis I don't have the problem [of] my access my secondary and my primary isis not being in sync They are going to be in sync. [so] [now] I can remove this and For this well the green that's the green that I had I think Is just double check should be this one now all I need is To change the tip of that I'm going to cTRl C this to this shape So that's not too much the whole stack to Mac series is highlighted and I'm going to paste it there that looks nice So let's test this I'm going to change Monday to 500 as well Let's see what happens well now I have two maximums and now let's change Tuesday to 600 that one becomes green That does take some steps But you just do [the] [setup] once and you can use it on different data sets Let me just bring this up, and we can do a quick direct comparison with our first chart This compare to this is in conspicuous trade this one really stands out the first time I saw it I was really attracted to it, and then I was like wow. It's actually easy to read as well So that's how you can set it up in excel Now remember you can use this on any set of data that you want I suggest that you don't overuse this type of chart use them with care in special reports and in dashboards if you like this type of excel Tutorials make sure to subscribe so you don't miss out a more advanced excel technique that I'm going to share with you This technique is a part of my upcoming - book course that's coming out on [unity] in about two months time So do check [it] out because it's going to have a lot of neat techniques in there
