Home // Video Tutorials // 3 - 6 (Intermediate) // 4 // Create and Design Ribbon for Excel charts

Create and Design Ribbon for Excel charts

How to use the Create and Design Ribbon in Excel to enhance your graphs and charts.

Create and Design Ribbon for Excel charts

The best way to understand graphs is just to get started. So let’s start and create our first graph – so here we have got some data, the months and the bills. It is as simple as we highlight the area and under the “ Insert “ Tab, you’ll see there are a number of Chart Items. For now we will just choose one, we will go to Column, click on it and all the different kinds of column charts appear. For simplicity’s sake, we are just going to click on this 2D column chart and when I click it, the chart is created.

A couple of observations – you’ll see Excel has made some assumptions about this data and included it in the chart – so it has got its axis levels – you’ll see it has decided that the dates belong down here, etc. You’ll also note that when you click on a chart a new item appears in the ribbon called Chart Tools and you’ll see it consists of the Design, Layout and Format. We are going to go through these in detail but you’ll note that when you click on a cell outside the chart, it disappears and to get it back you click on the chart and it reappears.

Let’s now work through this ribbon to understand what the buttons do. So starting at the far left here, you’ll see the first button says Change Chart Type. At the moment we have a column chart, if I click on that you’ll see we get the choice to change to any type of chart we want. So, for example, let me change to a Line Chart and when I say OK, you’ll see the chart changes. If I want I can go back and we’ll get to understand the different chart types later. Another option we have here is Save as Template. I will explain that later, but basically what it allows you to do, is to create a template of the chart you want and then whenever you need it you can access it via Change Chart Type.

The next set of buttons relate to the data and you’ll see the first button says Switch Row and Column. Basically what it is doing is that Excel, because of the data setup had decided that the months belong on the horizontal axes and the amounts belong on the vertical axes. If we click Switch Row and Column, you’ll see what it does – it has now changed so that the horizontal axis now represents the bill in total and the vertical still has the numbers. But now in Legend it actually gives us the breakdown of the months. It is quite useful, when you create a chart, just to click this button and see which one is a better representation of what you are trying to achieve.

The next button is the Select Data button. When you set up your chart, you selected some data and created it. If you want to make some sort of a change, and you click Select Data, you’ll see you get a couple of options. The first thing it tells you where the chart data is – so if you want to change that you can change it. You also have this button here saying Switch Row and Column, which does exactly the same thing as this button.

You’ll see what you can do as well is for the Legend entries, you can Add, Edit or Remove an entry. So at the moment we only have one, the bill – if I say Edit, it shows me where it is getting the Series name from – so you’ll see it is coming from there and where the Values are coming from – from there. If you need to edit it, you just make the change you want. You can either type them in or else link them to the spreadsheet. You can Add – we will show you a little bit later how to do that, and if needs be you can remove. If you have a number of items here, you can use these arrows to move them up and down and decide in what order they should appear.

You can also decide on what the horizontal axis will be – at the moment, it is looking at this column of data, if I edit, you’ll see it is telling you – the X axis – that is where it is getting its information from. If you want to change it, you just make the change here.

You will also note that there is an option at the bottom here called Hidden and Empty Cells. If I click on it, what it allows you to do is to decide what must the chart do if it discovers that one of these points is empty. Must it show it as a gap, so literally there will just be nothing here, or must it show it as a zero, in which case, if you had a Line chart it would be going up, plummet down and then start up again. You’ll see you have also got an option of Show Data in Hidden Rows and Columns. Say OK – we are happy with all of this. There we go.

One of the major benefits of Excel 2007 in that a lot of work has been done on the visual display of the data. With that in mind, you’ll see the Chart Layout allows you to choose particular chart layouts. So if I click here, you’ll see there are a whole lot of pre-defined chart layouts. Now everyone of these you could create on your own but this is just a nice shortcut way to get there. So just looking at this – that one looks fairly nice – if I click on it, you’ll see it is formatted in a certain way – we have got a data table here. Maybe I prefer this one here and you’ll see the formatting changes. What you can use these for, is to get it closer to what you want to achieve and then edit only the parts you want to differentiate from. But for now I am just going to go back to the standard chart.

You’ll also notice that there’s a whole bunch of new Chart Styles. What this means is that you can choose any one of these colour combinations to show your chart as. For example, let’s do this one here and you’ll see with one click you can make a really professional looking chart with a little bit of shading, 3D, look and feel, nice colours, etc. You can see any possible combinations of these you can do – let’s just look at a few more. OK, you can see the various options available to you. All that this allows you to do, is to get it looking a little bit more professional – a little bit more quickly. And when you have it close to what you want it to look like, you can then make various adjustments. Again I am just going to go back to our standard version.

The last button to look at is something called Move Chart. What you’ll see is if we click on the chart and we click Move Chart – at this stage what we can do is decide – the chart is sitting here in the sheet Graph Elements- we are going to move it around and put it into any one of the other sheets. Or else we can create a new sheet called Chart 2 – so when I click here and I say OK what you’ll see has happened is the chart has moved out of that sheet into its own sheet. If you want to go back there again- go the Move Chart – put it as Object In – there- say OK and you’ll see it puts it back in there. So it just gives you the ability to move your chart around.