Advanced Chart Tricks for Dynamic Interactive Charts | Alan Murray | Skillshare

Advanced Chart Tricks for Dynamic Interactive Charts

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
10 Lessons (1h 7m)
    • 1. Introduction

    • 2. Create Dynamic Data Range Easily

    • 3. Drop Down Menu to Select a Chart

    • 4. Get Creative with a Dynamic Chart Title

    • 5. Highlight the Max and Min Values Differently

    • 6. Create a Scrollable Chart

    • 7. Use Cell Values for Data Labels to Show what You want

    • 8. Rolling Chart for the Last 6 Months Only

    • 9. Visualise Actuals v Targets

    • 10. Using Checkboxes to Show/Hide Data


About This Class


In this class, we will explore advanced chart tricks to create dynamic and interactive charts.

Truly get the most out of your data presentation by getting creative and introducing advanced chart tricks.

We begin by making a charts dynamic. As new data is added our charts will automatically expand to accept the new data. We can also make our chart titles and labels more descriptive by using cell values and formulas.

Using form controls with our charts is covered in the class to make our charts interactive to users. As they choose options from a list, check a box, or drag a scroll bar - it can change how or what data is displayed in our charts.

We will also highlight key values conditionally. We can change the colour of values that are performing above the target, or are among the largest, or below average - whatever condition we would like.

So, enrol in the class and let's start learning advanced chart techniques.

See you on the inside.


1. Introduction: Hello and welcome to this class on advanced charts tricks where we will create dynamic and interactive charts. We will be covering various charting tricks to truly get the most from your data and create effective presentations using charts. Now we will be covering quite a bit will begin by looking at creating dynamic data ranges. Rather than basing our chart on a static range of cells, we will be doing it so that as new rows or columns or added, our chart will automatically grow on expand. And except this new information, we will also looking getting dynamic with their titles in our labels. Rather than keeping no static, we will look at how we can use cell values and how we can add formulas into our labels to provide new information and to be more descriptive with their data, we will create role in charts how we can show the last six values on the last three values really useful when we're trying to prevent a present. Things like the last six months or the last at 10 weeks worth of information on that will roll as new data is added to our list, will bring inform controls this will add the interactivity to a chart. So our user our our audience off our reports. They can interact with the charts with check boxes and scroll bars and drop down lists. And as they interact, this is going to change how the data is presented or even show and hide specific data. Depending on the user's requirements and their desires. We would then look at highlighting key information conditionally. So how can we change the color off the maximum and minimum values, or only those that hit target or those that have improved? Whatever we want to present, we can do? Once we can create these conditions and choose the way that we want presented, we can highlight this information so it's clear it to the person reading the report to see what it is that they need to see. And this is really what charts are all about. That effective presentation of data. If you're not making it easy for them to see that, then you're destroying the purpose off these visualizations. So we've got a lot to get fruit. It's very exciting, these some awesome chart tricks to take you to another level, so let's get into it in role now, and I will see you on the inside 2. Create Dynamic Data Range Easily: hello. And in this lesson we will look at the easiest way to create a dynamic data range for your charts, and that is to format your data as a table. So let me provide a demonstration of exactly what I mean for highlight this strange of cells and screen. And if I insert a chart, I'll just go for you. Typical standard CLUSTERED column chart on Move that to the Side for one moment. Not gonna worry about what the chart looks like or does it just want a bigger space? Let me imagine Della now have another month's worth of data to include or that I have additional products that we're dealing with? Let's say I have some bagels that I'm selling a bit different to the other products. £50,000 worth. Then the chart does not update to cover these this fresh data. I could adjust easily of a click on the chart kind of justice range, but it's not. Automatic is not dynamic in the way that we may want it to be on our reports or dashboards and what not. So let me undo some of those steps. I just went for it. Let me get rid of that charge and let's click inside air range of cells and on the home tab , format them as a table. Now we've got a multitude of styles here to pick form. We can even create our own style, but I don't want to worry too much about that at the moment. The purpose of this video is to show how it makes your range dynamic. There is lots and lots. I repeat lots you can do with this feature in Excel. If you don't use this feature of this is new to, I encourage you to read up more about it is quite a major player in excel, especially in the more modern versions. I'm going to choose one. For now, it will double check. My range of that was incorrect. I could highlight it and click OK. And yes, it applies a format in Yes, it brings in filters and various other advantages to this. The only other thing I was going to show you before actually correct this chart on the far left of the designs have they provide with a few useful features on it. The 1st 1 is to name your table So I just made this one sows since now named wasn't necessary to do this. But Icahn credit table that name in it. I'm sorry it's beyond me and let's insert a charts. It's gonna go to insert my chart now notice. I just click inside it, not even bothering to highlight it on assumes that Excel just assumes I'm looking at table . It is where the relationship between those cells same charts before not too impressive. Let's add sort of data. Source in goes April. Charts picks it up. Can you see that? Purported the legend down their picks it up Type in a value it appears. Put in a new one. Put some biscuits in this time on, let's say 12,000. Picking all this fresh data up as it is added or however, is brought him so using that table feature is the simplest way that you are going to get a dynamic data source for your charts on also for your pivot tables and anything else that you may wish to use later on in this course or other points. In this course, we do cover creating dynamic data ranges using four meters, such as the index function, and you can get a more versatile range with that rather than the whole table. And we also look at how we can use the offset function as well. Cover that in this chart section to create some dynamism of a chart. So there are other techniques, and there are reasons for the other techniques, but this is the simplest way. 3. Drop Down Menu to Select a Chart: hello and welcome to this lesson where we will create a chart that will change, dependent on use of selection, so want to provide a drop down list that will be a drop down list off these countries on screen. And when the use of makes a selection theater pro PRI it chart is shown, so is dependent on that. This will be happening on a separate sheet, and I'm just going to use the classic data validation drop down list here. Let's go straight in and get this done. So on the other sheet, I'm going to insert this in so you to hear. And it's gonna quickly Crichton this drop down list so that we can get on with the charting skills. So just choose a list. I'm just going to simply refer it to the list of countries on the other sheet. Now, in this chart, we do talk about creating dynamic drop down list, using the index function and all that kind of stuff. Dynamic ranges for charts, and they could be included here, but keeping things simple for the moment. They are the country's 80 to a 13. So when I click OK, I have a drop down list off countries. I just choose Germany for now on. That should let me move that down one cell and you'll see why in a moment. Because next up I want to bring these months headers across. I was going to do a simple copy and paste for that. Let's bring them in. Here is the headers for this chart that is to come. And then I need some data to be shown in the appropriate place. And this data will be the data for Germany or for whatever country is selected in that list . There are many ways that we can bring across this dependent or this dynamic data. We could be right invested if functions there are functions such a switch on is one could choose, which is very popular when you're using it with form controls. Although this is no a real form control. This is just a data validation ist What I'm actually going to do is use one of the variety of look up techniques we have. And I just want to use the classic V look up that could do this job quite comfortably. Wire overcook it. And the reason I left row one blank Here is I just want to go into there and start entering some index values. I'm going to use this for the column index, so imagining that I'll have it go off your let's syriza cross. We have many columns along the top. I mean, I've only got six seven, including the country there. But if you got many columns that once again various techniques from making that dynamic saving you having to type morning, this would be one of them. Just setting up these little helper cells now in sale Be three. Let me go in and put in a villa cut function. Let me zoom this in somewhat Just you can see exactly what I'm doing here. This be look up function will look for the country in So are you free there that we make that absolute so that when we copy the formula, it does not move Comma brings me onto table or ray That is the range On the other sheet here I don't need the headers. Let me go from Australia down to that loss figure and make that absolute So for taught my comma Let me just move back to the other sheet, which is not really something I should do right now, but I want you to see the formula on screen. So let me just delete that sheet name that's been entered because I clicked that sheet and I'm going to refer to Selby one. That's what I want us to see. So I really don't need that sheet name there. I'm going to delete that. I could just typed be one from the other sheet that it is local to the formula here. I don't need the sheet name. I'm going to kick that relative. So when we copy V, look up into see free D Free, a free that will simply move into C one d one e one and will return the index value off the column that return the value from so called in two is Jan countries one Janice to 53 so on . Let me put one more calmer after that, and I'll type folks on the end, or some of you may type zero and close bracket on this true return of value for Germany there, And if a copy it across and apply some formatting all off the Germany values. Let me just go and check this out. 28 50 34. Popeye was generate 28 50 34. And if I choose different countries, this will create that dynamic a fix. That's really what we're after, because the rest of the job here is simply a case of highlighting that range of cells and uncertain your chart. Do you want to go for simple column chart? Here, let me start to zoom out Is that now? Looks a bit ridiculous. Zoomed in as heavy is that you got us move underneath and we have ourselves a dynamic chart . I choose a different country. This is changing. You see the chart title also changing as a result of this absolutely fantastic technique that will wow audiences when they can interact with a chart in this manner. But so simple to set up using either logical or look up functions that you may already be familiar. Where for you'll find out in this course, and then things like data validation lists and a little bit of ingenuity with numbers, and it just crane the chart. Now what if the charges It's nice basic, but you can import your chart in skills on top of that and absolutely make an astounding charts or uses. It would just be wondering, Where did you get the skills from? That first row can obviously be hidden. If you're not a fan of showing that stuff in various ways, it doesn't even have to be above the column headers. It could be in a separate she entirely or hidden at the way. Please don't think that has to be on screen, but that is how we can create a dependant chart based on user selection. 4. Get Creative with a Dynamic Chart Title: hello and welcome to this lesson where we start to get creative without chart titles. Now we're following on here with the same spread she used in the lesson on creating the drop down list to select a chart. So this kind of a follow on from that and at the moment I have a chart title which is already dynamic because it's using the data from that drop down list, which is quite clever in itself. But I just return in the name of the country selected now what I would also like is the total sowles to be presented there. So I concede a cell was each month Vikan compared them against each other. Load a good info, the habit also presenting what the total off that is. And I would like that in the title. So what I'm going to do is write a formula in a cell to build up the text that I would like in the title. Then I'm going to link the title to it now. Typically, this would be written on a separate spread, she or in a cell quite well hidden and out the way. But for the purpose of this video. We don't necessarily have to get that smart, and I'm just going to write it right here in So I five. So it's very visible to us as we as we watch this video progress, let me zoom in as well. So it is extra visible to us as I progress now. Start of unequal sign. I am about to write a formula and we're going to begin by reference in the country selected , which is so a free I'm then going to put in a 1% the concatenation operator. I want to join onto that some text, some guns, open up my double quotes, put in the space and just type the word south. So at the moment is going to say Spain's Spain in space sounds. I didn't think that for a tongue twister and closing double quotes in goes the 1% now to get extra creative here. What I would like to bring in is a new line, so we're going to use the char function, which allows us to access characters, and I want to access character number 10. You can always pop to Google to find out what a bunch of these are what the associate ID number is for a specific type of character. But Number 10 is a line feed. We can use that to start a new line, really good to use within things like Khan Captain eight functions so that you can break into new lines. That such as I need right here. Close bracket in goes another m percent because now I want to find the total South. Now I want to be able to apply some format into that some right in a string. Here I'm writing a series of text, so the fat on bringing a numeric value in a total I'm going to need to format it that still display as text. So I need the text function really useful function. You are a big family dysfunction that's going to ask me for the value to start with. And sympathy just referred to sail with this point. But for me on its addy up, so in goes a some function, and I'm just going to total Row two. That story, right? Three is where the numbers are. Beg your pardon. Close that bracket, said a Some he's done putting my coma, and it's prompting me for the how to present it, how to format it now. I would like it formatted as money as pounds sterling as it is for May. I would also like the 1000 separator, a comma as we use in the UK and as you can see in my cells there. But I'm not interesting decimals, a bit like they're displaying in the cells as well. You don't need that degree of detail. So I'm going to open up some inverted comments and double quotes because I am about to type text. Former In Goes my Countess symbol pound sign zero to indicate display the number A comma that is my 1000 separator on another zero to indicate to continue to display numbers past that point of ways, for example, you see so G free 51,075 Without that zero that would just come back with 51,000. Show some zeros after that close and doubling double quotes. I'm not interested in decimals, but I could've put full stop that's all that small not interested in this these kind of size of values closed bracket for the text function, so I don't know if you're watching this have done formulas like that before? I don't know that was a bit too much. I'll go for it at a fair pace because it's the advance tricks course. But here we have done some concatenation off a cell value, some text on a char function for a lion feed and then the text function to present a total in a numeric y you. But still maintaining it is a text format. When a press enter, it doesn't look so pretty inside the cell. You do not see the line feed in there at the moment are you? Just see that there's no space. Go to space between Spain and South, but not at anything else. You can see the full Matt off the currency looks exactly what I wanted, but when I go and select my chart title, if I cook on the edge of that chart title or clicking the formula bar above and top equals to kick up a formula and I reference the cell that we just write that use, that's I five here to repeat myself can easily bred different sheet you notice with Ciarcia media, right? The chart Sorry, the sheet name in anyhow because of how common it is for people to move charts. When I press it, enter my keyboard. It looks a lot better in the set in the chart title than it does in the cell. You now see that line break have got Spain sells on the total, but more importantly, that we come out of that for a little bit. If I change the country to Sweden, Sweden Selves and their total, but change it to France front Selves and their total, a completely interactive chart that now also has a dynamic chart title featuring the text or wanted to display on also performing some calculations. So it could be really good to get creative with your labels, not just your titles, be other labels as well to present the data in the way that you will, that somebody else wants it presented, providing that information in a very clever way. 5. Highlight the Max and Min Values Differently: hello and welcome to this lesson on highlighting the Max and Min values of a chart. So I wanted to do at least one demonstration, maybe, and have one to come off. How you can apply conditional format into a chart as a lot of people may refer to it. I get asked a lot of my training, how you conduce some kind of automatic coloring off data, Siri's and this kind of stuff. So that's what I want to demonstrate. But I'm especially talking the maximum and a minimum figure, especially in this kind of example, actually, of Italy at the moment. The Jan. March and May figures are very similar in this chart, so you can be quite hard to see what want actually the minimum there you could misread that . This is kind of example is making clear seven of Max. Actually, it is a good example. I may come back to this now we're going to move my charts out to the side very similar spreadsheets of what have been used in already in the previous lessons from the initial drop down list video, but I'm moving it set aside now. I want to use these two rows rows for five. It doesn't have to be needs to. Rosie. You could inferior. Put him somewhere else. But I'm going to use these toe. Identify the max, and you have one to identify the mean values and bring them into the chart. So let me zoom in because a formula is about to take place. And in Selby four, I'm going to write an it function. I don't want to taste if the value in Selby free so the current months value. I don't know that is equal to the maximum value in that range. At ease that the maximum value so tight my max function in and highlight the range of sales . And Mike absolute close bracket, so is be free, equal to the maximum value in that ranch putting. My comma wants to know the value of true what to do if it's the maximum value with ease, the maximum value I want to display again. Reason I'm doing this will become clear very shortly comma. Where is not the maximum value where it's not the maximum value I'm going to put in the n I function to show an I, but press enter and copy that across the maximum value he showed there really is there. The others show, and I are not interested in former to net. That will not be a factor of what I'm doing really here. I just want to use it to change the color of that column as it's the maximum one now with a hash in a era, which I brought him of that function there. The charts will not display hash values. So if I get my table to show hash values, I know those are not shown only the maximum one. His son. Now let me do this again for a minimum value. So another if function same as before. Really testing if sale be free is equal to the minimum value in that range. Highlight the range. Fix it up if it is equal to the minimum, display the value if it's not and I function so this time as our copy it across that will show the minimum. So it appears in May here, not interested in the others. They will not be charted because off the hash in a function there is the former new wrote Equal to Men. Repeat the value of wasn't a function, so this is having no effect in a moment on the chart scrolled out to see that again. But if a click on my chart, that's because it's not including its figures in its charting by putting them directly underneath the range, just a case of expanding it to bring those in nice and easy. And now it takes effect. You see the other two columns appear in the chart. Two different data. Siri's different colors straight away. So now we'll be, since you got three columns side by side. But because of the hash values, big gaps to where they were not necessary, like a pool and January there, I'm going to double click one off the data. Siri's Let me double click the orange one, a k a. The max value one that will open up the formatting settings on the right hand side. If you're not taken straight to the column, icon off the format data. Serious dialogue. You want to get to this section because that's where the Siri's overlap setting is, and I won't overlap. Order data. Siri's 100%. Instead of showing three columns side by side, overlap them completely for presente and close that down. That is the effect that's taking shape now. So he appears like the February and May. Figures have changed color automatically, which, really, they haven't got free Siris of data overlapping. But that's the effect it has. So it's showing the blue one as well, which is behind the orange one. And if I was to change country off, this would take effect as the UK So the worst March best was I begin choose Germany investors in my worst was in Jan and just in case the color so quite close, the cunt and the columns are quite close. The colors make it very clear now. Obviously, you can come in here and change those. I can click on my Orange column. I could maybe used to format menu to change colors and go for a nice green indicate nowadays. Good news. I can click on my gray one change color, maybe going for a top of red because it z not good in this scenario, to have such a low value, so you can choose the colors which you want you want for each data, Siri's off their continued to blue ones as well. But the key thing is used to show using these techniques a basic function just to make a decision. Show the value or hash, and I and you can bring in traditional coloring to your charts. It's a fantastic visual effect. I don't think that you just limited to Maxim in here, very mind. You could have any condition you wanted. It's just a case of getting the condition right, getting the logic riot, and you can create this effect for whatever it is that you're trying to visualize in your charts. 6. Create a Scrollable Chart: hello and welcome to this lesson where we will create a scroll herbal chart. So I've got a similar spreadsheet to what we used when we used the drop down list to select a chart, dissect a certain country and therefore a chart, and that dropped down Mr. Still in I free here. But what I have changed are the data in the headers. The months and of also increased the amount of data on the first sheet surrounding the just being six months. There's now actually 19 months off data, so I gotta countries in column A they're all the way along to call him t 19 months, and I don't want to try and plot all of that onto the chart at one given time. I only want to show six months at a time. And underneath the chart, the plan is to put scroll Bar so that a user can scroll through time looking at the time period and for the country that they are interested in. So it's a very dynamic chart. Now they can choose the country from a list and then even scroll to a period of time only shown six at any given time I've still got the V look, ups and stuff from that previous lesson in row free. Now, let's stop by moving the charts of the right hand side, which is not really that necessary. It makes me feel good. And we're going to insert that scroll bar to stop it now, uh, to insert a scroll. Bar control, we need the developer tab on your ribbon if you do not have a developer tab at the moment, Right, mouse, click on the ribbon, customized the ribbon and check the developer box on the right hand side. Once you've done that and clicked, okay, you can click on the insert bottom half way along and the scroll bar control in the second row there. Click on that's crowbar control and draw the scroll bar onto your sheet. No, it doesn't matter if you don't get it. Perfect. First time, you can always adjust it later, but that is my attempt. That is my scuba underneath the chart there. Now, the next step is going to be too right. Mouse, Click that scroll bar control and format the control because there are some initial settings that we need to apply now starting off, we have the maximum value. I'm happy to current. I'm happy for the minimum being zero. That's gonna be important shortly. Just note the minimum value was zero. The maximum value is going to be 13. Now, let me explain that I have 19 months of data and I'm showing six months at a time. The six plus 13 is 19. The maximum have 19 6 at a time. That leaves 13 others on what they set in his four. Instead it when people scroll them for the chart. At some point, it will stop. There's no point scrolled in past. The amount of time we have are needed to stop at December 2017 in mine, which is the 19 months. So six plus 13 is that 19 months. The incremental change will be one that is to change when people click the arrow off the scroll bar change one month at a time, the page change. I'm going to change it to five. Is it gonna lodge who number? But that is how many months it changes. If they just press that the scroll bar. It's not the arrow, but a bit of space. Empty space from Scroll Bar. I'm sure you've done that before yourselves. Disco balls? That's how many months that would move. Now the so link. That's an important one. I'm going to choose a five off this sheet now. Once again. Typically, that would be in a different. She, too, would be hidden somehow for the purposes of learning. I'm putting it right in their face so that we can see exactly what's going on. But don't think that has to be somewhere on your report, which, ideally, Britain's. If I just click somewhere on the sheet to activate the scroll bar, we have a function in school bar. If I press my error to school fruit, you notice how a five the value changes, said a minimum of zero. A scroll back Congo. Any lesson? Zero. But if you scroll up that we go through the months through, we go for we go for here we go until we get to 13 which is the maximum that I set. So describe was not doing anything yet have been useful that we do have a scroll while that can be used, and it is changing the index value in cell fires now that we have this crowbar functioning . We need to put in the formulas that are going to fetch the correct data, the crypt labels and the correct values for a chart. And I was going to use the index function for this. The index function will be perfect to look up and fetch back the appropriate data. So let me zoom in on Selby to and start with these labels these months labels I'm gonna put in equals Index. And that will prompt me for the array. On that is on the chart data sheet. That is the array off labels, the month labels along the top. Let me highlight all of those. I'm going to press F four to fix that range, put in a comma and then go back to the other sheet, which is not really advisable. But I wanted to come back here so you can see exactly what I'm doing. I'm just gonna delete that sheet reference now, So we have couldn't be to t essentially off the chart data sheet next to ask me for the row number to return. From what I've only highlighted one row theon. Could be one is gonna be one comma. Now the column number that's the important one on the columns are fetch is going to be used by a scroll bar index here. No, a five. And fix that. But they're not going to have to plus one. On top of that, I might explain this once of God it in there. Let me put a plus in But Robert and Plus in one, I'm going to a plus. Selby. One. The cell above, just like I used it to be. Look up previously. What to use it again Here. Index. I never want to put a closing bracket. I'm just going to remove the sheet references from close to sell references so we look like that. Have got away five plus L B one for presenter. At the moment, that is not quite accurate. Let me explain a little bit. Why you do you mount? Let's bring this crowbar away back to the start to remember. The minimum value is zero. I'm asking it to look in this range of values starting from here, so that's column one No. Zero. So it's always one mawr than what this crowbar says. If that is being clear, so when it's right at the start School bus zero Are you want the first month, Scotty on one? It says one. That means you want second month. It is a waste plus one. At the moment. I've got a two here. Let me change these values at the top. Shoot that across feel Siri's. So now along the top of got 1 to 6. And if I copy that formula across, we should have what we need. So looking at range one row are you five plus one. Next one is a five plus to a focus free, a focus for because of the value above. I do hope that's making sense. Let me scroll through this bar. You see, It's June to November, char and spread. She scored across July to December, scrolled across August to check. Now the values aren't changing yet because I haven't done those yet. They're gonna be happening now. Let me scroll without back to the start. I will begin to pick on those values. So it's another index function for the values. Let me start in Selby free, which companies is Italy, which is a little bit silly and this time is going to be an index match function. Because not only do I need to return a column number, which will be the same as the previous example, that technique, but I need to get the row number on the row. Number is dependent on the country they chose. So in the other sheet, I need to look down here, fetch the appropriate row, but then also fetched the appropriate column because of a scroll bar. This sounds like fun. Yes, that means there, You mean here we go. Selby three index function on this time, the array is going to be the whole table. Now I'm going to avoid the labels. So money interested in values I don't need to highlight the labels. They're not helpful to me. There will be two across the T 13. You should be Oh, make the absolute putting my comma and returned back to the other sheet just so we can see what is going on so far. So this is what we have. I have the reference to be to t 13 off the chart data sheet. It's now time to get the road. This is where the match function comes into play. What is the match function looking for? It's looking for the country and from a free. Then you fix that reference and remove the sheet reference, which is harmless but unnecessary. Whatever they choose from there. That's what I'm looking for, Comma. Look up awry over to the other sheet, the list off countries slipped them all. My kid. Absolute comma Zero for an exact match. Close that bracket and I put in a calmer to move on. Let me go back to the other sheet to recap every now and then. I believe that she reference a cat. So here we are. The match function was inserted. Look for the country they choose in the list of countries A two to a 13 exact match. That comma after that bracket now puts us on to the third and final index argument off the column number that is the same as the previous example. It's going to be a five. You make the absolute plus, Selby one. I'm gonna type in B one to avoid that she reference low bracket. So what? Press enter and copy this across someone. I've got all my absolute references working that should now return the right values nearby Zoo mount so you can see this. So we've got em. You choose a country earlier on issues Canada. So I've got Canada and at the moment, index value zero might start a scroll bar. June. Let's look at the other sheets. Canada for June 45 9 to 8. Next one's 26. Next 1 69 Here we go. 45 down to white, 26 69 by scroll on a couple months. So here we've got 16. Line is the 1st 1 now in its value changed two plus 1/3 column. Furred economies, August 69. So this is now working on a drop down list of a country and also the scrolling off this crowbar. How cool is that? That a user, if I pressed the page number in here, jumps by five and 6 to 11 just back from 11 to 6 jumps back 6 to 1. We asked it to change it. Five values at a time. Somebody can scroll for time. Look at a time period. A one allows us to show a lot of information in a small space on six values at a time. But I've got 19 months of values on. I'm not sure how many countries are had 12 countries, all only one shot just by making it specific to the user, allow them to choose what he wants on a chart changes and adapts to show them the values they're interested in. 7. Use Cell Values for Data Labels to Show what You want: Hello. And in this lesson, we are going to look at a great chart trick that a lot of people do not seem to know is possible on that is to use cell values for your chart labels. So you may notice. Setting column D I've got some variants figures here similar to how we have spoken about it in the custom format section of this course. Now, in that section, I'll talk about how you can display these symbols, etcetera by using some clever formatting techniques. We're here. What have actually done is used it used in a formula. Um, I could have done the formatting way that is possible with labels also, but I felt this was better in this example. It uses some stuff that we don't necessarily covering this course like a text function Stephanie worth something for you guys to check out in your own time if you're not familiar with that one already, but that displays a number as text, but in a numeric form has been used here to show the variants as a percentage that despite it been stored as text and I've got a both positive and negative there have taken arrow symbol out the symbol directory and aerial followed kind of arrow symbol and just a simple Cutler functions. Really, That sort of is it's displayed this stuff positive, negative values with a symbol. What I'm going to do is create a chart and because why did know so dont not look close. I'm going to highlight my product. Values highlights my this week values holding down the control key there to highlight both non adjacent ranges. There must be equal heights. They are. I'm going to insert a column chart. Here it is. Looks wonderful. I let me quickly type in a chart title this weeks caos cells like a fantastic title. And then what I'm going to do is bring in these labels. Let me get rid of some of this stuff, actually as well. Ah, yes. Keep saying that. What in this little bit so with this chart selected and let's move to the side somewhat on the design tab at char element data labels, mawr data label options, you might take a different approach to this that however you get this label options up on the right is what we're gunning for. But the fault puts the weeks values in there. There is an option, no value from sales. Now. It's only been out in recent versions, so if you do not have that, you may want to look at some other techniques for doing this. I believe it came out in 2016. So is very recent. I'm It was a little bit more cumbersome in older versions. It typically had to use each label individually. Rather, this built in feature, which is why a lot of people are not familiar with it because it is fairly new to the game , this simplicity in doing it, it was always possible. Please don't misquote me there, but not like this. If I take that value from cells box parts of a range, our highlight, the sale was. Want to include in a range and simply click OK when I come get rid of value. Don't really want both. Close it down Asia is that I am able to show values from other sales as the label and I've decided to incorporate a symbol one stuff here because I think that looks very nice. In addition to know in the values in comparison, I can see a variance to last week as well. If I was to double click a label to bring up those formatting options again right now, I wasn't going to going to this right now. But I wanna mention there's a number section at the bottom here, and there are format options in here. So we talk about custom formatting on this course. So if you're not too familiar with this kind of stuff, we do cover some very clever examples, and you can come in here and apply additional format in to these values if you wish to take things at step further or to do them in a different way. This lesson was all about showing cell values as your labels. 8. Rolling Chart for the Last 6 Months Only: hello and welcome to this lesson where we look at creating a rolling chart. So I have some dates in column I and some sows figures in column B. But I would only like to display the last six months, and I want that to keep role in so always remains current. It's always the lost six months wherever that is, even when data is added to this range. So what we're going to do is create two dynamic ranges and where you're then going to get out chart to use those for its values and for its labels. Now the function of wanted to use here is one chord offset. I really, really useful function in Excel. It's a great way of creating these dynamite ranges and to create some dynamism within your formulas. Also, one thing to bear in mind with Offset is that it is a volatile function, meaning it will always calculate it calculates all the time, even when the cells it requires on the ones involved in the calculation. They're not the ones that were changed, so these types of functions can add more weight. Your spreadsheets, they can slow it down quicker so that is something to bear in mind. But really, it's the best one for the job in the exact type of thing we're doing right now. So I'm using ifs, offset function. This is what it looks like. The initial reference. Let's start by looking at how we can grab the values. Now. I want to start from Selby one on the click on Selby one. Now when we get to putting this into a named range, which I'm not actually doing right now, I'm going to show the formula on screen in the cell, then copy and paste it into the name. The name Drains will want the sheet name in there as well, so it's actually a little bit easier here by just going type of scene. So I'm going to type role in chart. Just to be clear, that's the name off this sheet on, because role in charts has gotta spice in its name. I will need these single apostrophes. They're sure to be around the sheet. If there's a space in its name, it's commission Mark. I'll put be one in as an absolute reference, Sylvester start in reference for the values as the first cell now rose. This indicates how many rows we need to move. Now. What I'm trying to do here is I'm trying to fund the last value. So go to bring in a account function of this point, and I'm just going to count the values in column B. C. Let me reference rolling charts again. That sheet just typing that in on absolute reference to column. Well, then put my commerce. So I was So you know, my come my closing bracket first to close off the count function, then with my comma, it will ask how many columns to move under? Put zero. I'm in the right column. Another common now puts you into the height and width for the range. And this is one of the reasons that I'm picking. Offset. How is much easier is to do it in this function. Now the height going to put minus six to move six cells back from the point that you found comma, the width will be 11 column white. So this function I am using to grab the last six values from Colin Bay on without going to feed that into the chart. In due course, let me put my close bracket there and take a copy off dysfunction. Try that again. I'll press enter that that's gonna complain that May, but I'm not too interested. We'll come back into defined name on the formulas tab. No going to create a name called Chart Values. No spices in named Ranges and Paste. That formula into the refers to area I can click. OK, and I'll have myself my first dynamic named range for the last six cell values. Now the next offset function will grab the labels for the charts. And as a reference, I'm actually going to use chart values named Ranges. My reference so that when asked about Rose up zero. But when asked about columns I could put minus one. And I'm not going to worry about height and whipped this time because the chart values named Ryan Jodi has that some precedent to offset fernet known range one column to the left . So it's only going to grab a range of equal size, literally one to the left column I years. It's known for us right now. That's exactly what we want. Take a copy, press enter. It's gonna complain, but I'm just doing that sort of formulas a still on screen in case of made a mistake and I can go and change them. Define name this crater name. I'm going to call it chart labels and o paste that formula into that range and click. OK, so we've got two named Rangers set up, which we believe to be grabbing the last six states last six South's. We now need to create a chart and get a chart to use them. Some gigantic click anywhere on insert eye line graph, but you choose to relevant charts for your purpose. That's not going to show anything because it's not using any data. We'll go straight for the select data button on the design type. So ask Justo, adding some data and in style with this legend area. This ad data Siri's goingto adding there, and this is where I'm going to pull the chart values now to start with the serious name that's in Selby one that's south. But as for the serious values are need to reference my name deranged Kuchar values now because we're in chart, we have the Renta, the shape of night even know named Ranges work across sheets and I mentioned it in the name drain just well, we have to put it here with charts so rolled in charge again in the usual way exclamation mark on the end, then chart values. It's going to take a copy of that so that when I click OK, that picks up that data. And I just wanted to click OK for this for the moment and show you on screen one. It's picking up the right data that's working fine. And two. I wanted to show exactly what I wrote. It's quite hard to see inside that box. So that's what a poor rolling chart chart values the named range. Okay, let me come back. Select the chart because we're not finished yet and select data on design. Now it's over to the labels area to edit, and the label range is role in charts. Get those the post trees, exclamation mark chart labels, which I believe is what I called the other one. Click OK, labels come in, click. OK, so there we have it. You can see visually how it shows you that is grabbing the last six values and dates from that range. But here's the really important thing. This is a role in charge. It is dynamic. So if I was to click in the next cell down, let me copy these two down. So now we'll get a June 2017 and I put in a figure. 41,000. The chart picks it up. Now it goes from Gen to June. Where's previously it was December 16 to May 17. It is rolled in with the fresh data always showing in this scenario the last six months. But as you can imagine, this is easily adapted going back to the original offset function and the reason I wanted to keep you on screen with this figure here. I mean, these are months. There could be days or weeks or hours for yourself. Here, there months that I could easily change at U minus free or minus 12. Lost 12 months. 18. It is simply a role in chart is not restricted to six months 9. Visualise Actuals v Targets: hello and in this lesson, or wanted to look at visualizing targets against actuals. And this is going to be a similar example. Toe how we highlighted the maximum in values. But I wanted a secondary example to support this idea off conditional formatting in charts . So I've got a target value in column B in a South value in column C on This chart has already been set up to chart the South figures for each individual, but I'd like the color of the column to change. If they hit target, it's it have all got different targets. So in column day, let's put in a formula museum in here. Move much, are at the way a little bit. It's going to be another if function simple. If function. I want to know if the sows the caffee made is greater than or equal to the target that she was set coma. If it is, repeat her cell's figure coma. If it is not, and I you function so similar to what you would have seen in a previous lesson. If you've seen that one about hard light in the maximum in values by press, enter and copy that down. It only shows the values for those who met their target who exceeded their target. Now we can bring my chart back there, be zoom out a bit on this spreadsheet and get my chart also include that data. So it brings in that second column. I can double click the column and overlap the data Siri's and then maybe changed a color off those condoms to maybe a nice green indicating some kind of good news, some kind of progress. We got the right day to serious here yet okay, we got. And that is another example off kind of applying conditional formatting to a chart to visualize some kind of metric. And I wanted to show this example another very common example, trying to show those that improve from last year or those have hit some kind of a target. There's a Mr Deadline wherever your criteria might be. So now conceded four cells people. So not only has Kim been the best South person, but she also here target. But your people that Sue did not necessarily perform as well looking against the others. But she did hit a target on this chart now shows us that 10. Using Checkboxes to Show/Hide Data: Hello. And in this lesson it is time to look at another use of a form control to make shots interactive and dynamic. And this time I want to use the check box control, and I want to use it to show how you can show and hide data on a chart. And I was going to pick on the target value here. So in a previous lesson, we looked at changing the color of a column. If it's hit target that there's no representation for what that target value WAAS now want is to be optional to be shown. So let me just move my chart a bit out the way for a moment. So I've got a bit of space here for formula and a little bit of space above for a fun control. I've got a developer tab open. If not, you need to show that right now. Just right. Click the ribbon and customize it to bring that developer Tabin on with it. In there, we could insert a check box control, and we click on that on in drawer somewhere on my shapes. Click or draw it in and I'll go in and change the text value there to on a show target venues. They were just wide of that common box. We can actually see this and then I'm going to right, mouse click and former control. So just like we did with the scroll Bar Control, I need to set a selling. There are other questions here, but I'm just interested in selling, I repeat would have said on some of the other lessons this stuff would typically be hidden this cell link. But for this demonstration, I'm going to choose J one so you can easily see what's happening on screen. So when I click OK, I have a checkbook control. And if I ticket, it's is true. NJ one liver and check it. It's his folks in J one. So now I've got a check box to change of value in a link cell unknown. Me to formula is going to be a simple if function here, let me come out so consume you go. It's going to be a simple if function, and I'm going to test J one now. This one of those rare situations where you don't need any logical symbols I don't need to know is that equal to this. Is it greater than this because the cell value is true or folks? So just referring to it is sufficient here. I'll put those in dollar signs. Absolute and comma. What do you do? If it is true? Show the target value comma. If not, and I function to essentially hide that data. Do not chart that data press enter. Copy it down. They all say hash in A because the box is not ticked. If I take the cheque books, that's his True values are shown. Uncheck it. Hit him again. Now let's come out of here and select her chart because now we need those figures in our charts. So if I now resize the date arranged to include that new column, it's in because they will say, Hashanah, you're not going to see anything. If I take the show Target values box, you do see stuff, and at the moment you can see the kind of overlapping Let me until that on the right smells click I e day to Siri's so that I can see the option to change Siri's chart type that would open up this window and I can see the three Siri's that I have the South figures, the auto coloring and the new target figures in gray there, and I'm going to change that from a cluster column to a line graph. So now click. OK, it will now show is a line not shown at the moment because it's turned off. I take the books. 18 comes. So what I've got here is just one check box. You can obviously have mawr off these so that people can pick and choose what data they shone. A chart I am Ticket Data's hidden. My ticket. It comes back here are representing a line that shows the targets that each one had kind of combo chart. Example. There are so many examples at their own reasons why you can do this kind of stuff. To hide particular customers or countries or two. Showing high trend lines is a typical example, and you can put this into action to create dynamic interactive charts perfectly reports perfect few dashboards. They look so impressive when they're done. The little of it is putting simple techniques together, like it functions and night form controls on a little bit of formatting