This lesson is exclusive to members

Microsoft Excel 2016 Bootcamp - Zero to Hero Training

Creating graphs

Daniel Walter Scott || VIDEO: 1 of 24

Download Exercise Files Download Completed Files

You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

In this video, we're going to look at making charts, adding different labels, and we'll also look at doing some of these in-cell graphs as well. So let's go and do that now.

So we're going to start with this spreadsheet, it's called 'Charts & Graphs'. You can open that from your exercise files. And what we're going to do is, we're going to create a graph. You'll often be creating graphs from data like this where there's more than one field.

So what we want to do is decide what's going to go into this. I'm going to decide its column 'B' that its going to go in. So just clicked on 'B' up the top there. Now I need to pick some other parts. In this case, let's say I just want to pick 'Total Sales'. So I need to select him, and just this option here. So I hold down 'Control' on my keyboard. And I click on 'G' at the top here, in the column heading. And it selects both of these columns. So that's how you can decide what data goes into your chart.

Then we're going to go into 'Insert'. And there's different options up the top here. A good way to get    started is this one that says 'Recommended Charts'. Especially if you're not sure, you're like, "How am I going to show this thing?" Click on that. It gives you, often, really good advice on what can go into it.

In this case, this one here, 'Clustered Column'. You can go into 'All Charts', and then just see what it looks like, as a Pie chart. Kind of working, but maybe too much detail in that one. It gives you a little preview of the actual data which is really cool, and you can work through it. I am going to go back to 'Recommended Charts'. I'm going to use this one here. Column, click 'OK'.

To get it bigger, you can grab any of these edges here. I'm going to make this one nice and big. To move it--- be careful not to drag the inside parts. If I start dragging this inside part, it kind of works but I can often drag bits with end of the chart around. So I'm going to 'undo' that. So when you're moving the whole chart, grab the outer edge. That ensures you get the whole chart coming along with this.

Next thing we want to do is, we got to decide-- it's given us the right kind of title, I guess, everything that I want. I can go and adjust them. I can double click where it says 'Total Sales'. And I can make this anything I like. This might be 'Toy Sales'. So you can just go over the top. It's kind of giving you some place holder stuff. If it doesn't, say you don't have a title up here, or it's missing some part that you want,what you can do here-- I'm going to close it down. If you click off, click back on, and on the outside, you get these three little options here.

This first one at the top here, 'Chart Elements' allows you to turn things on and off, so you might decide that "I want a legend," because it's missing, you can see, there's one legend that appeared. Let's say it doesn't-- 'Axis Titles'. You can see along here, this might be profits for-- this might be, you know, 'May 2018'. You can add all the different elements using this '+' button here.

Now to change the styling of this, there's a kind of a generic way, and then there's more specific ways. The generic way is, if you have your chart selected, grab the outside, make sure you're on 'Design'. You can see there are some pre-made designs along here. I'm just hovering above them, not even clicking them. Just giving me some different style ideas. And how it might be displayed. Say that you pick one, okay, you can go through that.

You can also pick 'Change Colors'. Say you want to change the color of the bars-- all the colors, click on 'Change Colors'. 'Change Color' is a bit weird. I don't often use this one because it changes the group of colors. There's like a whole mix up here. I want to pick specific colors rather than this. So these are the generic changes along the top here. Kind of overall big changes.

Let's say I just want to make some smaller changes. Like, I want to change the color of this bar, maybe make it a bit whiter. So what I'm going to do is, I'm going to select on it. And you end up double clickingthe parts you want to change. I'm going to double click this bar. And this little option on the side here opens up. And you've got three parts, so I've got my 'Fill', I've got ‘Effects’, and this last part here where we can play around with this series option.

This first one here, I want to change the 'Fill'. Let's go to 'Automatic'. I want to say, I want to give it a 'Solid Fill' of-- And see here, it says 'Color'. Drop that down, I can pick some of the paint colors, some of the standard colors.

For me, say I've got a corporate color that I want to use. I'm going to 'More Colors', and I need to type the RGB value. If you have no idea what your company's RGB value is, you might have to reach out to your marketing department, or your designer, or somebody to work out what your RGB value is. Just type it in there.

What if you just want a pretty color? You can just drag this target around. Pick it. I'm not sure what color it is. Click 'OK'. I've done it for one of them. What we might do is, instead of doing it for just one, I can click off, and click on this one. I've kind of clicked on all of them. If you double click them, like I said, you can change them individually. But if I click off, and just click on one of these guys, I can go to 'Fill', I can go to 'Solid Fill', and because I've mixed that color already, it's done recent colors. So it's done through the whole lot.

Another thing I might do is, with these bars here, with them all selected, you can see, they're all highlighted here. Move on to here where it says 'Series Options', and 'Width Gap' - I end up doing this quite a bit - I like the gap between to be smaller, just, I want a fuller graph. So, generic stuff along the top, and start double clicking on things to open up this side window here to make more specific changes.

Couple of other things before we leave is, with your graph selected, move over to 'Design', and you cango through and 'Change Chart Type'. Say that this column's not working, and the bar's going to be there for you. Click on 'Bar', and we're going to run it this way.

The other thing you might do is-- you might find that the axis are just along the wrong way, it would be better to have these guys along the top here, and these down the bottom. So you can have it selected, and there's one here that says 'Switch Rows/Columns'. This kind of flips it up the other way. So it depends on how it's looking, you might have to play around with this 'Switch Rows/Columns'.

Another thing before we go-- I'm just going to move this out of the way. Remember, I'm grabbing the edge of it. Sometimes it's just nice to have not a full graph, but just a nice little visual. So what we can do is, I've selected it off. And I'm going to select this. Hold 'Shift'. Grab the bottom one. So I've selected all of this in this column, and this little option up here, called the 'Quick Analysis'. So I click on 'Quick Analysis'. There's some cool stuff it does, you can see, a bar chart, it's actually putting a chart inside the cell as a little bit of a visual aid for the data.

Same with the color here, you can start to see by far that top one, that 'Paw Patrol' sales are by far a lot more than the rest of them. And there's different options here, the 'Top 10%'. Some nice little bits that go inside of here. So I'm going to leave my data bars on, they're kind of helpful.

So that is it for creating graphs. We'll look in the next one how to create some different graphs. And we'll export them for Word, PowerPoint, InDesign, and Illustrator. So let's go and do that now.