Hi, this is Wayne again with a topic “Excel Sparklines – Charts within Cells”.
In this Excel video, I will show you how to use the Excel sparklines feature to create very Nifty charts right inside of cells. Let’S get started so here I have a spreadsheet that shows a list of students and their test results on a standardized test and it’s basically the same content being tested over time four times throughout the year and I’d like to see how their progress is going and Of course, I could scan through this and make some judgments that way, but I’d like to have a visual representation here at the right that shows the trend to do this. All I have to do is go here to cell H2 and click on it and then up here on the ribbon, I’m going to click on the insert Tab and there’s some great chart options here, but notice this little group here, the sparklines group, I’m going to Go ahead and click on line and at this point Excel wants me to show it what data I want a trend line for. So if we look here at Albert scores, you can see that his grade level is listed. His student number – that’s not so important when it comes to the trend line right.
What I really care about is the scores that you see here, so I’m going to go ahead and click on D2 and I’ll hold the click and I’ll just drag across to get all four test scores, and you can see that this dialog box updates showing D2 Through G2 that looks good and I’ll go ahead and click OK and look at that. I get a beautiful trend line right inside of H2. This doesn’t require a separate chart that I have to scroll over to see or that I have to place. Maybe over my data, there’s no difficulty at all being able to see what the trend line is for Albert scores.
Now at this point, do I need to do all of that work again for each of the other students? No I don’t. All I have to do is go to cell H2 and you can see in the lower right corner of H2 there’s a little green square, that’s the excel, fill handle or autofill handle and I’ll click and hold my click on that little square and I’ll just pull Down all the way to the bottom of this data release the mouse button, and you can see that the same option is extended all the way down the spreadsheet. So now, at a glance I can see that Debbie had a rough time during the middle of the year, but she recovered late. I can see that era just progressed dramatically, so did Griselda and Sylvester and others, and I can see also which students struggled more toward the end.
So this is a great feature now at this point, look what I can do. I can click on one of these sparkline trend lines, let’s say griseldus and I could type, maybe something like great improvement with an exclamation point. I tap enter on the keyboard and I’ll expand column H by double clicking between H and I so that it fits in the cell. So now I have a comment on top of or behind that trend line. Now, if it makes it harder to see, maybe you don’t want to do that, but I just want you to know that it is possible to type and still will use that cell, even though there’s a spark line in the cell. Now, because I have sparklines in this spreadsheet notice that Excel gives me an extra tab here on the ribbon. So if I click on sparkline, I have some great options. I could edit the data that I used to create the sparkline. In this case. I don’t want to do that. I’M happy with the data that was selected. I could switch from a line chart to a column chart.
You can see that that may or may not be easier to read. It might make more sense with other data. In my case, I think the typical line option is perfect. I could switch to win loss, it looks like those are all considered wins.
Basically, so I think I’ll stick with Lion. If I want to, I can have Excel Mark the high point for each student and the low point also negative points in this case. That doesn’t make sense. I could Mark the first point and the last point now, if I choose to mark all of these, all I get is a DOT at just about every point in the trend line, so you want to use this Jude.
Obviously, I think I’ll stick with high point and low Point. Next, I can choose the style for my Excel sparklines. Maybe I want to use the color green, maybe black. Maybe I want the high points and low points to be in different colors.
You can work with those options here in the style group. If you don’t want to have to work with these visuals, you can just go straight here to sparkling color and select it and marker color and select that, and we have even some additional options here at the right where you can try changing the axis. Putting in some custom values, Etc, so as you use Excel, especially with data that may change over time. I think test.
Scores is a great example, but it could also be sales first quarter through fourth quarter sales or monthly sales. It could be site traffic for website. Whatever it might be, but any data that changes over time you might think of sparklines, and I hope that you will and that you’ll try it out and see. If it’s helpful to you now, sparkline planes can be used for other things too, but at a minimum. I hope you’ll think of it when you think of changes to data over time, thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow And subscribe, and when you do click the bell and you’ll be notified. When I post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you could support me through my patreon account or by buying Channel merch and you’ll, see information about those options in the description below the video.
Speaking of patreon, I want to give a quick shout out to my five dollar patreon supporters, there’s only a few of you but you’re fantastic, and I really appreciate all that you have done to support me and my channel over the months. I used to have a few more patreon supporters at the five dollar level, but that’s okay. I really appreciate those of you that are still at that level and I have several others that are supporting me at the one dollar level or the two dollar level. I really appreciate you as well.
Thank you so much and I would invite others if you want to help support the channel, so we can put out more videos and dedicate more time to this on my weekends and evening things please consider becoming a patreon supporter. Thank you. .