Chapter 3 Excel 2007/2010 Charts

How to Sort Data

Section three of this course is really all about charts. Later, you'll see how to create a variety of charts and chart styles with Excel. Microsoft have really revamped chart creation from Excel 2007 onwards. If you've ever used previous versions of the software, you'll appreciate how easy it is to produce impressive results.
First, though, we'll tackle the subject of how to sort data. The two subjects are not really related, but the data going in to our charts is a good opportunity to learn about this important topic.

Sorting Data in Excel 2007 to 2013

To make a start, you need to create the spreadsheet below. You don't need to use the same colours as ours, but reproduce the data and the headings exactly as they are in this one:
The Data to Sort in Excel 2007
Our spreadsheet is all about the viewing figures for the two main TV channels in the UK. The data is a bit old, but that's not important. As long as we have some nice information to sort, that's what matters.
The viewing figures for ITV have been sorted, from the highest first to the lowest last. The BBC1 figures are still waiting to be sorted. Let's see how to do that now.

Descending Sort in Excel 2007 to 2013

We want to sort the BBC1 viewing figures in the same way that the ITV figures have been sorted. We'll put the highest programme first and the lowest last. This is called a Descending Sort. If you do it the other way round, it's known as an Ascending Sort.
The first thing to do is to highlight the information that you want to sort. In your spreadsheet, highlight cells A5 to B14. The crucial thing to remember when you want to sort data in Excel is to include the text as well as the numbers. If you don't, you'll end up with a spreadsheet where the numbers don't relate to the information, which could spell disaster in bigger spreadsheets!
Your highlighted spreadsheet, though, should look like this one:
Select the Data to Sort
To sort your BBC 1 viewing figures, do the following:
  • From the Excel tabs at the top of the screen, click Data:
The Data Panel in Excel 2007
  • From the Sort & Filter panel, click Sort
  • A dialogue box appears:
The Sort dialogue box in Excel 2007
The Sort By drop-down list seems empty. Click the down arrow to reveal the columns you selected:
The Sort-By drop down list
We want to sort this by the values in the Millions column. So select Millions from the Sort by list.
Sort On is OK for us - it has Values. But click to see the options in the drop down list:
The Sort-On options
Values is the one you'll use the most. Once we have a Sort By and Sort On option selected, we can then move on to the Order.
Click the down arrow to see the options on the Order list:
The Sort Order options
Select Largest to Smallest. Your Sort dialogue box should then look like this:
What your Sort dialogue box should look like
If you clicked OK, your data would be sorted. But the level buttons at the top can come in handy. If two items in your data have the same numbers, then you can specify what to sort by next. For example, if we have two programmes that have 6.3 million viewers, we could specify that the names of the programmes be sorted alphabetically.
To do this, click the Add Level button, and you'll see some additional choices appear. You'll see the same lists as the Sort By box. If you select Column A, and then Descending, Excel will do an alphabetical sort if two items have the same viewing figures.
Additional sort options in Excel 2007
In the image above, we've added a "Then By" part, just in case there is a tie. You don't have to do this, as we have no numbers that are the same. Click OK to sort your data, though.
If everything went well, your sorted data should look like this:
The sorted data
 But that's all we need to do for the sort. You can move on to creating your first chart in Excel.

Create an Excel Chart

We’re now going to create a chart from our BBC1 Viewing figures. If you haven't yet completed the sorting tutorial, go back one page and follow along with the lesson. You'll then have a some sorted viewing figures to create a chart from.
When our chart is finished, though, it will look like this:
The Completed Excel 2007 Chart
A little later, you'll see how to improve on this basic chart.

To start making your chart, highlight the BBC1 programmes, and the viewing figures. If you have just finished the sorting section, this data should still be highlighted, and look like this:
The Selected Data for the Chart
With your programmes and the viewing figures highlighted, do this:
  • From the tabs on the Excel Ribbon, click on Insert
  • Locate the Charts panel. It looks like this in Excel 2007:
The Charts panel is on the Insert menu in Excel 2007
In later versions of Excel, the Charts panel looks like this:
Charts panel in Excel 2013
For this first one, we'll create a Column Chart. So, in Excel 2007, click the down arrow on the Columnitem of the Chart Panel. You'll see a list of available charts to choose from. Select the first one, the chart highlighted below (2D Column):
Available charts in Excel 2007
The Column drop down list in later versions of Excel looks like this:
Column charts in Excel 2010 and 2013
When you make your selection, a new chart appears on the same spreadsheet that you have open. The chart should look the same as the one at the top if this page.
But notice that the Excel Ribbon has changed. The design menu is selected, along with options for Chart Layouts:
Chart Layouts
In Excel 2013, you'll see these layouts on the left, in the Chart Layouts panel, under Quick Layouts:
Also on the Design Ribbon, you'll see options for Chart styles:
Chart Styles

You'll see how to use these later. For now, your chart may be covering your viewing figures.

How to Move and Resize a Chart

You might find that your chart from the previous lesson is covering your data. In the image below, our chart is overlapping the ITV data. To move it, hold your mouse over the chart until your cursor changes shape: (We found that the best place for your mouse is over the dots in Excel 2007, as we had problems moving a chart when the cursor was anywhere else! Moving charts in later versions of Excel is easier.)
Moving a Chart in Excel 2007
Press and hold down the mouse button when your cursor looks like the one in the image above, and then drag your chart to a new location. In the image below, we've placed the chart below the data.
The chart has been moved below the data
You can also place your chart in a different worksheet. To do this in Excel 2007, right click anywhere on your chart. From the menu, select Move Chart:
Select Move Chart from the menu
In Excel 2010 and 2013 there is a Location panel to the right of Chart Styles. Click theMove Chart item:
In all versions, you'll then get a dialogue box popping up:
The Move Chart dialogue box
If you want your chart in a new worksheet, select the first option. Then delete the text "Chart1" from the textbox, and then type a name of your own.
If you look along the bottom of Excel , you'll see Sheet1, Sheet2, and Sheet3. Your data is in Sheet1. If you click the drop down list to the right of Object in on the dialogue box above, you'll see the other worksheets you have open. You can select one from the list and click OK. But for this first chart, leave it in Sheet1.

How to Resize an Excel Chart

You can resize a chart, and any elements on it, by moving your mouse over the sizing handles. For the chart itself, the sizing handles are the dots around the edges of the chart in Excel 2007:
Resize a Chart in Excel 2007
In later versions, the sizing handles are white squares:
When your mouse changes shape to a double-headed arrow, hold down your left mouse button. Then drag to a new location. You can resize using the corners, or the edges..

Chart Styles and Chart Layouts

You can easily change the Style of your chart. If you can't see the Styles, click anywhere on your chart to select it, and you should see the Ribbon change. The Styles will look like this in Excel 2007:
Chart Styles
In later versions of Excel, your Chart Styles will look like this:
The Chart Styles panel in Excel 2013
Click on any chart style, and your chart will change. To see more styles, click the arrows to the right of the Chart Styles panel:
Click the Styles arrow
You'll then see a drop down sheet of new styles (Excel 2007):
Chart Styles in Excel 2007
And here's the Styles in Excel 2013:
Chart Styles in Excel 2013
Work your way through the Styles, and click on each one in turn. Watch what happens to your chart when you select a style.

Chart Layouts

You can also change the layout of your chart in the same way. Locate the Chart Layout panel on theDesign tab of the Excel Ribbon bar. It looks like this in Excel 2007:
Chart Layouts
In later versions you may have to click the Quick Layout option on the Chart Layouts panel:
Click the down arrow to the right of the Chart Layouts panel to see the available layouts you can choose from:
Available Chart Layouts
Again, click on each one in turn and see what happens to your chart. In the image below, we've gone for Layout 10:
A different chart Layout

Changing the Chart Type - 2D Bar Charts

You can change the type of chart, as well. Instead of having a 2D column chart, as above, you can have a 2D bar chart. To change the chart type, locate the Type panel on the Excel Ribbon bar (you need to have your chart selected to see it):
The chart Type panel        
Then click Change Chart Type. You'll see a dialogue box appear. This one is from Excel 2007:
Change Chart Type
The dialogue box looks slightly different in Excel 2013:
Select Bar from the list on the left of the dialogue box, and click on the first Bar chart (Clustered Bar). Click OK to see your chart change:
A 2D Bar Chart
You can experiment with the types of chart in the dialogue box. But reset it to Bar chart, as above.

The Chart Title and Series Title

Your chart from the previous section should now look like this:
A 2D Bar Chart in Excel 2007
Once you have your chart in place, there are plenty of formatting options in Excel. In the chart above, for example, the title says "Chart Title". And there's a not terribly descriptive orange square that says "Series 1" (your bars may be blue). We'll see how to change that in a moment. But first, the Chart Title. (If you don't have a title in Excel 2010, select the first layout in the Chart Layouts panel.)

How to Change the Chart Title

To change the title of your chart, click on the title to select it:
Select the Chart Title
The circles surrounding the title tell you that it is selected. Once the title is selected, click on the letter "C" of Chart. Hold your left mouse button down and highlight the two words, as in the image below:
Highlight the text
Once your title is highlighted, you can change it by simply typing a new one:
Type a new title
While the title is highlighted, you can select a different font and font size, if you want (on the Home panel in the Excel Ribbon at the top.)
To deselect the title, click anywhere outside of it.

Formatting a Series Title

To change the Series 1 text on the Chart heading to something more descriptive, select the title as you did above:
The Series Title
Make sure the circles are there, and then right click. You should see the following menu appear in Excel 2007:
Edit Data Source menu
Click on "Edit data source". Alternatively, click the Edit data source item on the Datapanel on the Excel 2007 Ribbon:
Data Panel
For Excel 2010 and 2013 users, your menu looks like this:
Edit Data Source in Excel 2010
The item to click on the menu above will say Select Data instead of Edit Data Source.
In both versions you should then see the following dialogue box appear.
The Edit Data Source dialogue box
The Chart Data Range at the top of the dialogue box is highlighting the cells A5 to B14. This is the data we selected for the chart. Below this there is an area forLegend Entries (Series) and Horizontal Axis Labels. We'll see more of these later. For now though, we just want to change Series 1 into something more descriptive.
So click on Series 1 to highlight it. Then click the Edit button, as in the image below:
Legend Entries
When you click the Edit button, you'll see a new dialogue box appear - Edit Series. It should look like this:
Edit Series
Notice the cells being referenced in the Series name area. They are cells A5 to B14. These same cells are also highlighted on the spreadsheet:
The cells for the Series
Click on the BBC title instead, the one on Row 3 above. Your Edit Series dialogue box will have changed. The Series Name area will now say A3 (amongst all those dollars):
The cell reference has changed for Series name
Click OK to get back to your Edit Data Source dialogue box. The Series legend will now say BBC:
The Legend Entries list has changed
Click OK to return to your spreadsheet. But look what's happened to the chart. TheSeries 1 has gone. Next to the orange square, we now have BBC 1:
The Series Legend now says BBC1

We'll meet these boxes again when we create a chart from scratch. For now, let's see some more formatting option you can do with an Excel chart.

The Chart Layout Panels

In the previous part of this lesson on charts, you saw how to format a chart with various dialogue boxes.
You can also format your charts using the menu items on the Excel Ribbon bar, at the top of the screen. With your chart selected, click the Layout menu (Not Excel 2013. See below for your Layout options). You should see this:
Left side of the Layout panels
Right side of the Layout panels
The Layout menu is a bit big for this page, so we've split it in two. But the chart Layout panel is split into a number of different sections (six in our version), and allows you to change the information in the chart.
For Excel 2013 users, your Layout options are on the Design tab still, on the far left, just under the File menu:
Chart Layout options in Excel 2013
Click Add Chart Element to see th efollowing drop down list:
Chart Layout list in Excel 2013
For all versions of Excel, The first thing you may want to do is to give your chart a name.
To change the name of your chart in Excel versions 2007 and 2010, locate theProperties panel on the Layout menu:
The Properties panel
Highlight the default name in the textbox and type a new one:
Type a new Name for your chart
If you now click away from your chart, and then click back on it, you'll notice the name of the chart change:
The name of the chart has changed
For Excel 2013 users, locate the Name box just below Chart Layouts:
Change the name of a chart in Excel 2013
Highlight the default Chart 1. Type a new name (BBC 1) and press enter:
Excel 2013 changed chart name

The Labels Panel in Excel 2007/2010

The Labels panel on the Layout menu lets you format the titles and legends on your chart. Here it is:
The Label Panel
Or this for Excel 2013 users:
Chart Layout list in Excel 2013
The first one is Chart Title. Click the arrow to see the options:
The Chart Title Menu
Chart Title options, Excel 2013
Click each item on the menu in turn to see what they do. Then click More Title Options. The following dialogue box will appear (Excel 2010 has more options. Excel 2013 users, see below for your options.):
Format Chart Title in Excel 2007
As you can see, there are options to change the Fill, Line, Line Style, Shadow, 3-D format, and Alignment. Play around with the options on the dialogue box to see what they do. The only thing you're changing here is the Chart Tile. Click Close when you're done. If you don't like what you see, click the undo arrow at the top of Excel.

Formatting Chart Titles in Excel 2013

Excel 2013 users won't see a dialogue box. Instead, you'll see a panel appear on the right of the screen. This one:
Format Chart Title, Excel 2013
Click an arrow to see further options. Here are the options for Fill:
Chart Fill option, Excel 2013
You can also click the icons at the top. There are three of them: a paint bucket, an Hexagon, and a resize symbol.
Click the Hexagon and you'll see these options:
Chart Title icons, Excel 2013
Click the Rezise symbol and the options will change to these:
Resize a chart, Excel 2013
As well as the three symbols, you can click the Text Options, you to the right of Title Options at the top. You'll then see even more options, this time to change the text itself:
Chart title Text Options, Excel 2013
Play around with all and see how they work.

Change the Axis Title in Excel 2007/2010

The next item on the Labels panel is the Axis Title. Click the down arrow to see the options:
Axis Titles
At the moment, our chart has no Axis Title. It just has numbers running across the bottom. Someone looking at the chart won't know what the numbers represent. Here's what our Chart looks like at the moment:
Our Chart
To add an Axis title in Excel 2007 and 2010, click on Primary Horizontal Axis Title. From the sub menu, click Title Below Axis.
Title Below Axis
In Excel 2013, select Primary Horizontal on the Axis Titles menu of Add Chart Elements:
Axis titles, Excel 2013
When you click Title Below Axis or Primary Horizontal, a new title will be added to the chart:
A New Axis Title added
Highlight the default text, and type your own:
Highlight the text
Click away from the chart to see what it looks like:
Our New Axis Title
We now have some explanation for what the numbers represent. You can add a Vertical Axis, as well. Click on Primary Vertical Axis Title and see how it works.

Chart Legend

The Chart's Legend is this one:
The Chart Legend
At the moment, our Legend is on the right of the chart. But you can move this. Click the Legend item on the Layout panel to see the various options:
The various Legend options
Axis legend, Excel 2013
Click an option on the menu and watch what happens to your Legend. You should see it move around your chart.

Adding Data Labels to an Excel Chart

A Data Label is information overlaid on the chart bars. In our chart below, we have numbers overlaid on the orange bars:
Data Labels are on the bars in this chart
You can format these Data Labels. Click the Data Labels item on the Labels panel to see the following options:
The Data Labels options
Data labels, Excel 2013
The one wat we have at the moment is Inside Edge. Click on Outside End and your Data Labels will look like this:
Data Labels - Outside End
You can also see the options if you click More Data Label Options from the menu. You'll then see this dialogue box (In Excel 2013, again, you won't see a dialogue box. Instead, you'll see the Format panel appear on the right of your screen. The same options as below will be available, however.):
Format Data Labels
Again, play around with the options to see what they do. The first two, Label Options and Number, are the ones you'll probably use most often.

The Format Chart Panel

In the previous lesson, you saw how to use the Layout panels to change the layout of the chart itself. The Format panels allow you to create some great looking charts with just a few mouse clicks.

Click on your chart to select it, and then click the Format menu at the top of the Excel Ribbon. You should see this long menu, split in two here:
Left side of the Format Panels
Right side of the Format Panels
Using the various Format Panels on the Excel Ribbon, we'll format our chart from this:
Our Current Excel 2007 Chart
To this:
What our chart will look like
OK, it may look a bit gaudy! But at least it's lively. You can create a chart like this quite easily:
  • First, click on your chart to highlight it
  • Click the Format menu on the Excel Ribbon
  • Locate the Shape Styles panel:
Shape Styles
Click the down arrow on the right of the panel to see the available styles (there might not be so many styles in Excel 2013, so you my have to select a different colour):
Available Chart Styles
When you move your mouse over a style, your chart will change automatically. But you won't be able to see the full effect until you click away from the chart. We went for Style 28, the one that's highlighted in the image above. You get the rounded corners, the drop shadow and the colour fill.

Create your own Chart Style in Excel

You can create all that yourself, though. If you want to create your own style, try the following:
Fill your chart with a colour by clicking the down arrow on Shape Fill on the Shape Styles panel:
Shape Fill
Colour Options
Select a colour from the list. Or click "More Fill Colors". Once your chart has a colour, you can liven it up a bit.
Still on the same menu, click on Gradient. The sub menu appears:
Gradient Options
We went for one of the Dark Variations.
Next, you can spruce up the text on your chart. Locate the WordArt Styles panel:
WordArt Styles
Click the Text Fill button to see the available colours:
Text Fill
Once you have the chart background and text formatted the way you want it, you can add some rounded corners, and a bit of drop shadow. You can apply both of those from the Format Chart Area dialogue box. Here's how.
To bring up the Format Chart Area dialogue box, click the Format Selection button on the Current Selection panel:
Format Selection
You'll then see the following dialogue box appea (If you're using Excel 2013, you'll see a panel appear on the right of your screen instead of a dialogue box):
Format Chart Area
To get rounded corners, click on Line in Excel 2007. You'll then see the following options:
Rounded Corners
In Excel 2010, you'll have a Border Styles menu on the left. Click that to see the Rounded Corners option. For Excel 2013, click the Border category to exapand it. The Rounded Corners options is at the bottom:
Chart rouned corners in Exel 2013
Put a tick in the box for Rounded Corners.
To get a Shadow for your chart, click the Shadow option on the left of your dialogue box. The options will change to these:
Add a Shadow to a Chart
For Excel 2013 users, click the Hexagon symbol at the top, just to the right of th epaint bucket:
Adding shadows to a chart in Excel 2013
Click the Presets button to see a list of pre-made shadows:
Shadow Presets
Select the one you like. Then click Close on the dialogue box. Your chart will then have rounded corners and a drop shadow.
Our Finished 2D Bar Chart
OK, you should now a very smart chart. Playing around with the various options on the Format Chart Area dialogue box can really bring an Excel chart to life!

How to Create a Pie Chart in Excel

Pie charts are quite easy to create in Excel 2007 and Excel 2010. In case you're not sure what a Pie Chart is, here's the basic one you'll be creating. Later, you'll add some formatting to this:
An Excel 2007 Pie Chart

To make a start, you need to highlight some data. If you've been following along with the previous tutorials, then you'll have some viewing figures data. You've created a 2D chart with the BBC data. This time we'll use the ITV data. If you don't have this data, create the following simple spreadsheet. The cells to use are D4 to E14:
The Data for the Excel 2007 Pie Chart
  • Click inside cell E4 and change "Millions" to ITV, if you already have the data from a previous lesson
  • Highlight the cells D4 to E14
  • Click the Insert menu at the top of Excel
  • Locate the Chart panel, and the Pie item:
The Charts Panel
In Excel 2013, the Pie chart is harder to spot. But it's highlighted in green in the image below:
Pie Chart icon in Excel 2013
Click the down arrow and select the first Pie chart:
Available Pie Charts in Excel 2007
  • A new Pie chart is inserted
  • Move your new pie chart by dragging it to a new location
  • Notice how all the segments of the pie chart are the same colour in Excel 2007:
A Default Pie Chart
To get different colours, make sure that your chart is selected and locate the Chart Style panel:
Chart Styles in Excel 2007
Click the down arrow to the right of the Chart Style panel to reveal the available styles :
Available Pie Chart Styles
We've gone for the second one, Style two. If you haven't got this style, select a similar one, such as style 4 in Excel 2013. The chart will then look like this (your labels may well be at the bottom, though, depending on which version of Excel you have):
Style Two Pie Chart

But it looks pretty good for just a few mouse clicks! We can still do a bit more to it, though. Now, you'll see how to add the viewing figures to the pie chart segments.

Add Data Labels to a Pie Chart

In the previous tutorial, you created an Excel Pie Chart that looks something like this:
An Excel 2007 Pie Chart

At the moment, though, there's no information about what each segment represents. We're going to add the numbers from our ITV viewing figures. These ones:
The Data for the Excel 2007 Pie Chart
To add the numbers from our E column (the viewing figures), left click on the pie chart itself to select it:
The Selected Pie Chart
The chart is selected when you can see all those blue circles surrounding it. Now right click the chart. You should get the following menu:
The Right Click Menu
Data labels in Excel 2013
From the menu, select Add Data Labels. New data labels will then appear on your chart:
Data Labels added to the Pie Chart
The values are in percentages in Excel 2007, however. To change this, right click your chart again. From the menu, select Format Data Labels:
Format Data Labels
When you click Format Data Labels , you should get a dialogue box. This one:
The Format Data Labels dialogue box
If there's a tick in Percentage, untick this and select Value:
Select the Value option
Your chart will then have the correct numbers:
The correct Viewing Figures are now on the chart segments
Overall, the chart looks OK. But we can add some formatting to it. 

How to Format Pie Chart segments

From the previous lesson, your Pie Chart segements look like this:
Pie Chart Segements
You can change the colour of each slice of your pie chart, and even move a slice. Let's change the colours first.

Change the Colour of a Pie Chart Segement

Left click on the pie chart itself to select it:
The Selected Pie Chart
It is selected when you can see those round handles. Now left click on one of the segments to select just that individual slice. It's a little bit tricky, but if you do it right your pie chart should look like this:
An Individual segement is selected
In the image above, only the 10.99 segment is selected. You should see round circles surrounding just that segment. Now right click your segment and, from the menu that appears, select Format Data Point:
Select Format Data Point from the menu
You should see the following dialogue box appears (you'll see a panel appear on the right of your screen in Excel 2013. The same options will be available as the ones below, however):
The Format Data Point dialogue box
Click on Fill from the options on the left (Excel 2013 user should click the paint bucket icon at the top, then click to expand the Fill option). The dialogue box changes to this:
Fill a Data Point
There are quite a lot of options to experiment with. But select the Solid Fill option:
Solid Fill Data Point
Now click the colour picker, and choose a new colour for the segment:
Colour Options
We've gone for a dark orange colour, but select any colour you like.

Move a Pie Chart Segement in Excel

To move the slice that you've just coloured, click back on Series Options from the options on the left:
Click back on Series Options
In Excel 2013, click the three bars icon:
Series points in Excel 2013
Set the Point Explosion slider to about 30%
Point Explosion is set to 30 percent
Now click the Close button. Your chart should look something like this one:
The Finished Excel 2007 Pie Chart
Change the rest of the slices in exactly the same way.

Create a 2D Line Chart in Excel

For this last chart, we'll compare the viewing figures of BBC1 and ITV. A line chart is better for this type of data. The chart we'll create looks like this:
The Finished Excel 2007 Line Chart

We're comparing how many hours per week a person watches BBC1 with how many hours they watch ITV. You'll need some data, of course. Start a new spreadsheet and enter the same data as below:
The Data for the Line Chart
Once you have your spreadsheet data, highlight the cells A3 to H5. Now click Insert from the Excel Ribbon bar. Locate the Charts panel, and click on Other Charts. From the menu, select All Chart Types (In Excel 2013, there is no Other Charts option. See below for what you should do):
Select All Chart Types
In Excel 2013, click the Recommended Charts item instead of Other Charts:
Recommended Charts item  in Excel 2013
When you click All Chart Types, you'll get a dialogue box popping up (Excel 2013 users shoudl click the All Charts tab on their dialogue box):
The Create Chart dialogue box
From the dialogue box, the left hand side shows all the chart templates. Click on Line. Select the first Line chart, the one highlighted in the image above. Click OK and Excel will insert your chart. It should look like this:
A Default Line Chart
The chart looks a bit plain, at the moment. You can change the colour of the lines for BBC and ITV. Locate the Chart Styles panel on the Design menu:
Chart Styles
Click the down arrow on the right of the Chart Styles panel to reveal the available styles:
Available Line Chart Styles
We've gone for the first one, top left. When you select a style, your chart will change:
A Line Chart Style has been applied
The lines are more distinct now. The dates at the bottom don't look too impressive, though!

Format Axis Titles

From the previous lesson, your 2D Excel Line Chart should look like this:
Our Excel Line Chart
To format the dates on the bottom Axis, click on them with your left mouse button. With the dates Axis selected, right click. You should see this menu:
Format Axis
Select Format Axis from the menu, and you'll see the following dialogue box appear (Excel 2013 users will see a panel appear on the right of the screen, instead of a dialogue box):
The Format Axis dialogue box
Under Axis Type, select Text Axis:
Axis Type - Text Axis
Your dates should end up in the middle. (Our version of Excel was a little buggy. We had to click Date axis, then click back on Text axis to get the dates in the middle.)
Dates are now in the middle

Adding an Axis Title

To add an Axis label at the top of your chart, if you have Excel 2007 or Excel 2010, click the Layout menu at the top of Excel. Then locate the Labels panel:
Labels Panel
Click on Chart Title. From the menu, select Above Chart:
Chart Title - Above Chart
If you have Excel 2013, however, stay on the Design Ribbon and locate the Chart Layouts panel on the left, just under the File menu. Click Add Chart Element, thenChart Title > Above Chart:
Chart Titles in Excel 2013
You will then see a default title appear at the top of the chart. Highlight the text, and type a title of your own:
An Axis Title has ben added


Add a Left Axis

We now need to add an Axis for the numbers running up the left of the chart. The numbers are the hours per week that people watch each channel - 0 to 6.
From the Labels menu still, select Axis Titles > Primary Vertical Axis Title > Rotated Title:
Rotated Title
Axis titles in Excel 2013
(Excel users won't have Rotated Title option - the title will roate by itself.)
This will add a title like the following one:
A default left Axis
Highlight the default title and type Hours. You can move the title to the left by clicking and dragging. This is a little tricky, though! Use the Zoom tool at the bottom of Excel to zoom in on your target:
Excel 2007 Zoom Tool
Move the Axis in to position:
Moving the Axis
When you're done, your chart should now look like this one:
Your Line Chart
Spruce it up a bit by adding a bit of fill colour, rounded edges, and shadow. You've already done this previously, so we won't go through it again. When you're done, it may look like ours:
The Finished Excel 2007 Line Chart
And that's it for line charts. If you've been following along from the beginning, you should now have some impressive Excel chart skills.

Predicting future values with Excel Charts

Excel can help you make predictions about future values, or help you spot a linear trend. What we'll do in this section is set up something called a Trendline. We'll use an X, Y Scatter chart for this. We'll take a look at future income predictions based on what was earned in previous years. If you're a bit confused, don't worry: it will all become clear as we go along.
Type the following headings into cells A1 to C1:
Year Years since 2006 Income
Format the cells, if you prefer. Your spreadsheet will then look like this:
Cell Headings
Enter the years 2006 to 2019 into cells A2 to A15:
Year Values in the A Column
As an X axis for our chart, we can have the years since 2006. These values will be used in a later formula. In Cells B2 to B15 enter the values 0 to 13:
The B Column
We now need some income values for the years 2006 to 2013. This is income that has actually been earned, rather than income that might be earned in the future. We'll then use this hard data to predict future values. Enter some income values, then, into cells C2 to C9. We made up the following values:
Income values added to the C Column
We're now ready to insert an X, Y Scatter chart.
Highlight the cells B1 to C9:
Cells B1 to B9 highlighted
This will be the data for our chart.
From the top of Excel, click on the Insert ribbon. From the Charts panel, locate and click on the Scatter charter icon. The icon looks like this:
Excel's Scatter Chart icon
Select the first item to get a chart with just dots:
Various Scatter Charts in Excel
(If you can't see the icon above, click on Recommended Charts. Switch to the All Charts tab, then select X Y Scatter).
A new chart will then appear on your spreadsheet. It should look like this:
A Scatter chart added to  an Excel spreadsheet
The figures along the bottom, the X Axis, are our years since 2006. The figures on the Y Axis are our income values. The first dot, the one on the far left, tells us that we made just over 12000 at Year 0, (Year 0 is 2006). At Year 1 (2007) we made just under 16000. At Year 2 (2008) we made just over 14000, and so on.
All these dots seem to form a loose line going up from the left. You could add a line yourself using the Shapes item on the Illustrations panel. What you'll then have done is to create a linear regression.
Rather than add the line ourselves, however, Excel can add the line for us. Not only that, it can give us the formula it used to create the line. We can use that formula to predict future incomes.
Click on your chart to highlight it. You should see three icons appear on the right, in Excel 2013. (See below for Excel 2007 and Excel 2010.) Click on the Plus symbol, and put a check in the box for Trendline:
The Trendline option in excel 2013
When you check Trendline, you should see a line appear on your chart:
An Excel chart with a Trendline
To get the line in Excel 2007 and 2010, select your chart then click on the Layouttab. From the Analysis panel, click the Trendline option. From the Trendline menu, select Linear Trendline.
The line represents Excel's best fit for a linear regression. It's trying to put as many as the dots as it can as close to the line as possible.
To see the equation Excel used, click on the Plus symbol again (Excel 2013). Then click on the arrow to the right of Trendline. A new menu appears. Select More Options at the bottom:
More Trendline Options
You should see a panel open on the right of Excel, like the one in the next image.
For Excel 2007 and 2010 users, Click the Layout tab again. Then click theTrendline on the Analysis panel. From the Trendline menu this time, select More Trendline Options. You'll then see a dialogue box with options the same as the ones in the image below.
The Format Trendline dialogue panel in Excel 2013
The Trendline option we've chosen is Linear. Have a look at the bottom, and check the box next to Display Equation on chart.
When you check the box you should the following equation appear on your chart:
y = 564.88x + 13604
This is something called the Slope-Intercept Equation. If you remember you Math lessons from school, the equation is usually written like this (the "b" at the end may be a different letter, depending on where in the world you were taught Math):
y = mx + b
In this formula, the letter "m" is the slope (gradient) of the line, and the letter "b" is the first value on the y axis. The x is a value on the X-Axis. Once you have the slope of the line, a value for the X-Axis, and the starting point of the line, you can extend the line, and work out other values on it. This will be the letter "y" in the equation.
Excel has already worked out two values for us, the "m" and the "b". The "m" (the slope) is 564.88 and the "b" is an income value of 13604.
To work out the y values we just need an "x". The "x" for us will be those "Years since 2006" in our B column.
Click inside cell C10 on your spreadsheet, then. Enter the following:
=564.88 * B10 + 13604
Press the enter key and you should find that Excel comes up with a value of 18123.04. This is the predicated income for the year 2014. Use Autofill for the cells B11 to B15. The rest of the predicted values will then be filled in:
Future values added with  the Slope-Intercept Equation
So Excel is predicting we'll earn 18123.04 in 2014. By 2019, it's predicting we'll earn 20947.44.


Sparklines are mini graphs that you can add to cells in your spreadsheet. They were introduced in Excel 2010, and look like this:
Cell A8 showing a Sparkline graph
The graph in the cell A8 shows the scores that Lisa achieved in her tests. You can quickly see that Lisa's scores are going up all the time, with no dips.
To add Sparklines to your own spreadsheets, start with some data. (You need more than one value, otherwise you'll just have a dot.) In a new Excel spreadsheet, type the following exam scores:
The data for the spreadsheet
Change the height of row 8 to create more room for the Sparklines. To do this, you can simply move your mouse to just below the number 8 on the left of Excel. When your mouse pointer changes shape, hold down the left mouse button. Keep it held down and drag to a new height.
If you want an exact value for the height, click inside of any cell on row 8. Locate theCells panel on the Home ribbon at the top of Excel:
The cells panel on the Excel ribbon
From the Cells panel, click on Format. From the Format menu, select Row Height:
Items on the Format menu in Excel
When you select Row Height, you'll see a small dialogue box appear. Type in a new value for the height and click OK:
The Row Height dialogue box
Row 8 on your spreadsheet should now look like this:
Row Height changed in Excel

Adding a Sparkline to a Spreadsheet Cell

To add a Sparkline, click inside of cell A8. Now select the Insert ribbon from the top of Excel. From the Insert ribbon, locate the Sparklines panel:
The Sparklines panel on the Excel ribbon
You can see that there are three options: Line, Column, and Win/Loss. The first two are the most used options. Click on Line and you'll see a dialogue box appear. This one:
Create Sparklines dialogue box
The first text box is where you specify your Data Range. Click inside this box and enter A1:A7. For the second text box, Location Range, you specify where on your spreadsheet you want your Sparkline to appear. We want the Sparkline to appear in cell A8. The dollar signs before the A and 8 mean it will be an Absolute cell reference, as opposed to a Relative cell reference. (We go into this in more detail in a later tutorial, here: Absolute Cell References.) If you leave the Location Range box blank then your Sparkline will appear in the currently selected cell.
Your Create Sparklines dialogue box should look like this:
Sparklines dialogue box - Data Range
Don't worry if your Location Range text box is blank, though. As long as your Data Range box is filled in you can click OK.
When you do click OK, your A8 cell will look like this:
A Sparkline added to cell A8
You can use AutoFill for the other exam scores. Move your mouse pointer to the bottom right of cell A8 until the pointer turns into a black cross. Hold down your left mouse button. Keep it held down and drag to cell D8. You should see all four cells fill with Sparkline charts:
sparklines in cells A8 to D8
You can liven up your sparklines by added markers and changing line colours. We'll do that in the next part.