Transcripts for the above video clip:

Excel 2007 Graphs Horizontal and Vertical Axis

A critical area in most graphs are the two axes, because they are very important in describing what is happening. So we need to understand what can be done with these axes. 

Let’s first look first at the vertical or Y axis – if I right click on it and I go to Format Axis and a whole range of options appears. So let’s work it systematically. The first thing you’ll see that Excel does, is that when it looks at the data, it decides what the minimum is going to be, what the maximum is going to be and the gaps in between.  But all these options are available to you – so you’ll see the axis options - at the moment it is set – the minimum is set to Auto so it will guess what it is.  We can say, no, please fix it, and for example, let’s say please go to  -1000. If you watch the graph when I close it, you’ll see it forces in that number.  Going back here – perhaps the maximum as well, I am going to make that 3000. And you’ll see now your graph is static – it will always, no matter what happens to these numbers,  that will be the axis- and that is quite useful, especially if you want to compare graphs side by side and not have the scale changing here continuously.  Just right clicking on it again- you can see we can also play with the major units.  Those are these – the grid lines at the moment are split in gaps of 500. Maybe we feel that is too big and we want it to be 250 so I’ll just say Close and you’ll see we have just got more items.  Right click again, Format Axis, we have minor units as well.

One of the things you can do as well is reverse the order of these numbers. At the moment it is fairly logical – starts at the lowest number, goes to the highest number.  But perhaps, for whatever reason, you would like to have it in reverse order – when I click on it, you’ll see the order is reversed.  We’ll just switch it off and go back to the normal sort order.  

Another option here is to use a log scale. Now before we do this, because it doesn’t handle negative numbers or zeros well I am just going to change this to a 1 – let’s just get back to the axis. Now notice the way this works, because we have got a set scale, there are quite big movements here.  What the log scale allows you to do, is to compare things a little bit more closely because of the difference in size it is  actually expressed on the scale. So you’ll see that this number here, which is actually 184, compared to this which is 1000 – so a lot closer together but without the log scale. So it just allows you to compare like for like and you perhaps get a better view – especially where you have got very small numbers and very big numbers.  I am just going to go back quickly, to what we originally had – go back here  - to Format Axis.

Another option here is the Display units. Excel is displaying the number as it sees it here.  But we can say Please can you display the graph with units of hundreds, thousands, 10000’s etc   so let’s try  hundreds, when I click, you’ll see we have now got 30 hundred for the 3000.  Perhaps you want to use thousands  and you’ll see you have got a little item here which you can customize as well. You’ll see it says Show Display units label on chart – if I switch it off the thousands disappear, if I switch it on the thousands appear.  So you can specify what this is going to be – maybe millions.  Let’s go back to none.

What you can also control are these little tick marks – can you see the way the grid line goes and then it goes outside the axis – a little tick mark. We can say these major tick mark types appear- maybe you want None – so when I click, notice that all those tick marks went away. Perhaps you want them inside – now this will only appear if you feed in the grid lines – outside we have already seen- and Cross – which would be outside and Inside.  So I am going to go back to just Outside. The minor tick marks – if you had minor grid lines – you have got the same set of options.

What is quite nice as well is the Axis Label. At the moment, you can see, this label appears next to the axis. But there are situations, and you will see them later where you don’t want that. You want it to be High or  Low or None.  If I say None, it is fairly obvious, it disappears.  If I say High – you’ll see it actually moves to the other side.  And if I say Low, you’ll see it comes here. But these particular aspects will become more useful and more apparent later where you see data crossing over- where the axis actually has data represented within it.

The last thing you can do  is effect where the horizontal axis crosses – so where must this line cross the vertical axis. Automatic – generally it will cross at zero. We can specify – we can say the axis value must cross at:- at the moment it is zero, but let’s say it must cross at -1000.  When I close, you’ll see what happens is the axis label has moved to the bottom and the cross point happens there.  Let’s go back here. You can change this to be higher or we can say Please cross at the maximum axis value – by default it will generally be  zero of something we customize. If I say maximum, you’ll see it now goes to the top – it appears at the top and all the labels appear  there. Let’s just go back there. Let’s just go back to Automatic, which, generally, in our opinion, is the correct view.  So that’s the axis options.

Let’s now look at the Number options.  The number options controls the formatting of the numbers on these axes. So you’ll see this is pretty much the standard formatting options. You can format them into Number Format with a number of decimals, currency, accounting, date, time – all these various  options- pretty much standard. What is interesting, though, is you can see one of the options here is Linked to Source. At the moment, because it is ticked, any formatting that happens here will be pulled through into the graph. If you untick it, what will happen is, if you make changes here, it won’t  come into the graph. The graph will maintain the formatting that you have expressed here.  So we will just leave that on.

Looking at the other options. The Fill – the same as the other areas – you can define how you want it to be filled. Give it various colours – you can see what happens- have a gradient fill, picture fill, automatic (which is what we prefer).

Similar with the Line Colour- all the various options exist. The line Style – if you want a dash,  how do you handle corners, etc. A shadow – same standard features. 3D Format – if you want to make it look a little bit special. And the last one is Alignment, where you can specify how it will be aligned so let’s go and let’s say- let’s play with this – see if there is any impact here, no real impact there, because the information is not big enough. For the text direction – let’s say we will rotate it or 90⁰ for example, you’ll see in this case it is a bit of a mess. We’ll go back to horizontal and let’s do a custom one and you’ll see I can gently change the shape, which may be a little bit easier.  Again, depending on what is active –  these items might now be available to you to re-size the shape to fit the text and to play with the internal margins. That covers the Vertical Axis.

Now you mustn’t forget, we have looked at the Vertical Axis. The horizontal axis, as you may expect, will be very similar. So I am going to right click and say Format Axis. But there are some unique items. So these items the number, the fill, the  line colour, style, alignment etc are all the same but under Axis options, you have got a couple of new ones. 

So you’ll see the first thing here says the Interval between tick marks.  Here are your tick marks If I change that, maybe to 2 – what you’ll notice is when it is active, now  the tick mark appears only every second data item. The interval between labels – notice you have got January, February, March, which ties up with that. Normally if I go to Automatic, at the moment it is the same, I can specify it – so I can say Please do a similar thing  - go into levels of two- just click here and what you’ll see now, maybe because of size, it only shows you every second label, perhaps just to fit it in.  As much as possible I prefer to have every label shown, so you’ll see that shows there.  You can see you can also do the categories in reverse order so we have got January, February, March, etc. When I click Reverse Order, you’ll see it goes backwards and it moves the axis around. We can switch if off again, so if you want to achieve that. The label distance from the axis – so you’ll see this is 100 points from the axis. Let’s go and make it 500 points and all you’ll see is it has moved it a bit further away.  I am going to put it back.

The axis type is quite important. Excel checks  to see what you have given it in data.  And you can tell it  automatically choose it based on what you see in the data or you can force it to be a text axis or date axis.   And this will give you some capabilities at a later stage. In terms of tick marks – you will see they are currently Outside, I click and I say go Inside and you’ll notice they are now pointing upwards. The minor tick marks – if you had minor tick marks, minor grid lines – these would appear here – you need  to specify them as well.

And here is a useful feature – at the moment, your axis labels are next to the axis which means you’ll see that April kind of overlaps the data because it has gone next to them. I can click on Next to Axis and let’s put it Low – when I put it Low, what it does is moves the axis out of the graph Say yes as long as its close to the relevant points so at least it now can be seen.  Depending on what you want, you can also go High which puts it at the top. We are going to go Low. Similarly, to horizontal axis you can decide where the vertical axis is going to cross – is it automatic, in which case it is on the left hand side or  maybe at a category number. At the moment that is number 1 – let’s try and get it to about here – I am going to say  Please cross at number 3. Just click on one of these. What you’ll see is that the axis has now moved and it is crossing over here, which is maybe something you want -perhaps show some data to the left of the axis and to the right of the axis.  Or you can say at the maximum category in which case the axis  is actually sitting on the other side – on that side there. Go back to Automatic.

You can also decide where must this information appear, is it between the tick marks – let’s just switch our tick marks back so we can see each of them. So here are our tick marks and our data appears between them. We can say Please put it on the tick mark and you’ll see now the points are on the actual tick marks.  So we will go back there. So between these two axes, they are highly customizable.