Creating Charts in Excel

Scenario: your boss wants to know how well each of his three salespeople performed in the first three months of the year.

1. Create a table for your chart

To start with, you need a table in Excel that you can use to generate your chart.

If you are working with a table in Word or Publisher, you can copy and paste the table into Excel.

For this tutorial, we will create a table of sales figures over a number of months.

1. Click on cell B2

2. Type Jan

3. Move the mouse pointer to the fill handle


fill handle

4. Drag to the right across Row 2, until the abbreviation “Mar” is displayed in the info box (the yellow box).

5. Remove the marking by clicking anywhere on the Excel worksheet.

Note:

This feature of Excel is called a Custom List. Custom lists are already set up for months (full names or abbreviations) and for days of the week (full names and abbreviations. To see this in action, try typing “Mon” or “Monday” into a cell and dragging the fill handle up, down, left or right. You can create your own custom lists if you like (go to Tools, select Options and then click the Custom Lists tab).

2. Inserting a Chart

Now you have the months in cells B2 – D2, enter the rest of the data, as shown below:

1. Click on Cell A3

2. Enter “Adam” (without the inverted commas) and press the TAB key on your keyboard (you should now have moved across to B3).

3. Enter “1000” (without the inverted commas).

4. Press Tab again

5. Continue entering data until your table looks like this:

6. Click on any cell in the table and then start the Chart Wizard by pressing the button on the toolbar:

7. You see from the top of the chart wizard that you are going to be taken through a four-step process:

In the first of these four steps, Excel wants to know what type of chart you want to create.

3. Choose your chart type

In the column on the left hand side, there is a list of Chart Types (e.g. Column, bar, line and so on)

On the right, there are sub-types where you can choose from several variations on the chart type.

Choose the standard column graph and click the Next button

4. Which DataRange?

Excel needs to know what data to include in your chart. You don’t want to make any changes here because you want Excel to include all the data that you put in your table.

Excel also wants to know if you want your chart to compare the rows or the columns.

If you select Rows, Excel will compare how each salesperson performed during each month (Jan, Feb, Mar).

If you choose Columns, Excel will produce a chart that shows how good each month was for each of the three salespeople (Adam, Connolly and Carter).

Select Columns to see this effect but then re-select Rows because we want to compare the salespeople against each other.

5. Editing the Series

If you click the Series tab, you can exclude rows or columns from the chart.

For example, if you do not want to include a particular salesperson’s data, you could remove his/her name here.

You can also add other rows or columns of data from elsewhere in your spreadsheet.

If you had selected Columns for the DataRange, you would have the choice of adding or removing a month.

Because we want to include the data for all three salespeople, don’t make any changes on this screen. Just click Next:

6. Add Information about the Chart

Your chart has to be self-explanatory. Therefore, you will want to give it a title and also to label the x (horizontal) and/or the y (vertical) axes. You will also want to display a key (legend).

1. Add a title for the chart and add a label to the y axis, as below:

2. Click on the Legend tab and move the legend to the bottom of the chart, as below:

3. Then click Next:

7. Where to Put Your Chart

You can either create the chart in your current spreadsheet or you can make a new sheet in your workbook just for the chart.

1. You want to create the chart in the current sheet, so just click OK and the wizard will create your chart.

2. Once your chart has been created, you can still edit it further if you wish. Try double-clicking the Legend, the Chart Title, the Axis Title, the chart background and the chart itself. You get dialog boxes that allow you to change things like colour schemes and fonts. You can make a real mess of your chart in this way. Choose an appearance that you think the boss would like. Hint: not like the example below:

3. One final detail – you notice that the legend isn’t wide enough and it isn’t centred. To make the legend wider, click on it and “drag” the black handles. To move the legend, click on the legend and drag.

8. Changing the Values for a Chart

Just when you thought you had the file complete, saved and ready for the boss, Miss Adam comes into the office in a very agitated state. She has made a mistake when entering the turnover values for January. She entered £4,000 instead of £1,000.

1. In the Data Table you can now change Miss Adam’s figures for January from £1,000 to £4,000.

2. You should see that Excel immediately corrects the chart.

You now realise that you are frequently going to have to go through the same process. Every month, new sales figures will need to be added to the chart and salespeople may need to be added and removed.

In fact, just as you are changing the sales figures for Miss Adam, the new figures for April land on your desk. How are you going to add these for your chart?

9. Inserting new features into an old chart

Now you are going to add an extra column of data to your data table and change the data range of your chart.

1. Go to your data table.

2. Click on cell D2

3. Click on the Fill handle (see section 1 of this tutorial) and drag to cell E2. The column heading Apr should be automatically inserted because of the Custom List.

4. Enter the sales figures for April, as below:

5. Select your chart.

6. From the Chart menu, select Source Data.

You will now be able to add to the data being displayed in the chart

7. You will notice from the dashed line around the source data, that the figures for April are not yet included in the chart. To remedy this, drag the mouse so that the whole table is highlighted, as below:

8. Click OK and you will be returned to the chart, where you will see that the figures for April are now included.

It’s a good job that Excel allows you to update charts so easily because the company has just hired a new salesperson.

10. A New Salesman

The new salesperson is one Mr E Cartman and he has already made £4,500 of sales in his first month.

You will add the new salesperson to the chart in the same way that you added a new month.

1. Go to your data table.

2. Add Cartman’s figures for April, as below:

3. Select the chart.

4. From the Chart menu, select Source Data.

5. As before, drag the dashed line around the entire table, so that Cartman’s figures are included in the data range. Then click OK to see your chart updated.

11. Sorting cells from A to Z

You can edit your data table in other ways and the chart will be updated when you do so.

For example, it might be a good idea to list the salespeople in alphabetical order.

1. Go to your data table.

2. Mark the table by dragging the mouse over the range of cells.

3. Click on the SortAscending button.

4. You should see that the list of salespeople is now sorted in alphabetical order according to the salespeople’s surnames.

5. You will see that your chart has been updated to reflect this change in the data table.

12. Changing the Chart Type

Finally, you decide to change the colour scheme and the chart type, to make the chart more visually appealing.

1. Section 9 of this tutorial described how you change the visual appearance of the chart by double-clicking on areas of the chart to bring up a Format dialog box.

2. You decide that you also want to change the chart type. You can do this by selecting Chart Type from the Chart menu.

3. Experiment with the different chart types and select one that would be suitable for your purpose.

That’s the end of this tutorial! Hopefully you have learnt a great deal about creating and updating charts in Excel.

Page 1 of 13