Hi, this is Wayne again with a topic “Excel In-Cell Charts”.
In this excel video, i want to show you how to create in cell charts in microsoft excel. These are amazing, very useful and obviously take up much less space than your typical excel chart. There actually are a few different ways to create in-cell charts i’ll focus on two. If you’ve watched all my videos, you probably already know about how to use conditional formatting to create some in-cell charts charts that can be seen from within a cell instead of floating. On top of many cells, so let’s look at how you could do that and for this example, i’m using a spreadsheet that lists the total mileage driven by every employee in a particular company, and you can get a copy of this practice spreadsheet in the description below. So i hope you look at that download it and use it so to use conditional formatting to create in cell charts. The first thing i need to do is select the column where i want those in cell charts to appear. In this case column, i it selects the entire column, and now i can go to the home tab home ribbon. In the styles group, i can click on conditional, formatting and there’s an option for data bars.
You can see, there’s gradient, fill options and, as i put my mouse on the options you can see, previews of what it’ll look like in microsoft, excel there’s also solid fills i’m going to go with gradient fill. I think that looks good and i can just click to apply that and now i have basically a bar chart inside of the cells in excel. So this is one example of in cell charts. I’M going to undo that by holding ctrl and tapping the z on the keyboard.
I just want to point out that, in addition to the options i’ve already shown, you can also go down here to more rules and make some adjustments. So i could pick a different color if i want to, and i could change the bar direction. I could go from right to left instead of left to right, there’s just lots of different options for creating your in-cell charts this way through conditional formatting.
So that’s a wonderful option that we have, and i love that option, but in some ways this next method of adding in cell charts is even better. Instead of just showing a simple chart that shows the total mileage. I would really like to see a chart that shows the trend.
What’S the trend for katina here? Is she driving more less over time, staying basically the same? What are the trends for these employees? So let’s look at how to do that. I’Ll click here in cell h3, in this case and i’ll go to insert here on the insert ribbon, you should find a sparklines group. Now sparklines are available in some of the newer versions of microsoft excel. So if you’re using an older version of it, you may not see that, but if you do have it take a look, you can go to line and a dialog box or a pop-up box appears, and here i can select the data range that i would like This line chart to track. I could type the range here, but the easiest way to do this is just to click, this arrow and then click and drag to select the range that i want to track. The trend for now that it’s selected, i can click this arrow again.
The range that i selected is here and then just click, ok and i get a beautiful sparkline in cell chart, looks great and i can easily and quickly see that katina’s use of her personal vehicle is trending down a little bit. Okay. Now i need to gear up for doing those same steps again for epiphania and for everyone else.
That’S an employee of the company right. Well, actually, no, you don’t have to do those steps over and over for each person instead simply click on the first sparkline. That’S been created and i can now go to the lower right corner.
You can see, hopefully the green square. That’S there, that’s the auto, fill handle or sometimes called the fill, handle i’ll just put my mouse pointer on that little green square click and hold the click and then pull down all the way down. As far as i need to go and then release the mouse button, and i get that same beautiful sparkline look showing the trend for each of the employees. Now that i’ve added the sparklines you’ll notice that i have a new tab and a new ribbon that has appeared at the top of excel. As long as i have the sparkline selected it’ll appear, it’s got all sorts of great options. I can edit the data in this case.
I don’t want to do that. I have the exact data that i want to track. I could switch from a line chart to a column chart it still kind of shows the trend, but it’s a little harder to understand. In my opinion same with win loss, it doesn’t really convey the information very well, so i’ll stick with a line chart.
Next we have some really neat options. I can have those sparklines show even more information. I can show the high point and the low point. So look closely and i’ll zoom in a little bit, so you can see that better. So now the high point and low point of each sparkline is marked by a dot and that will help draw attention to it.
I can also choose to show negative points. Markers of each change – or i could identify the last point and the first point so each of those options can be very powerful to draw attention to key parts of the incel chart. Next, we can adjust the style of the sparkline or in-cell chart, so i can make it black and white. I can change it to blue. If i click here, i get all of the different style options that are available.
Some of these are more colorful than others. It just depends on what you want and how you want to convey the information. There are even more sparkline color options here and marker options over to the right. You can switch the axis of the chart if it makes sense to do so. You can also clear the spark lines if you want.
I don’t want to do that, and there are some other options as well, i’m going to zoom out, so you can get a good look at the two different kinds of in-cell charts that i’ve shown you how to make. I think these look great thanks for watching. I hope you found the tutorial to be helpful. If you did please like follow and subscribe and when you do subscribe, click the bell so you’ll be notified. When i post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll see a link to that in the description below you can also buy channel merch if you’re interested so take a look at my merch shelf below The video .