Creating Charts in Excel (3)

5 Photos

We learn from the last chart that the overall crime rate has increased in the past two years. What if we want to know whether the figures of various types of crimes are rising too? Fortunately, the figures of all types of crimes for two years have been recorded in our Excel spreadsheet. This time I would like to show you how to change the "Data range" of the chart to incorporate relevant statistical figures for comparison of each type of crime between 2002 (January to October) and 2003 (January to October).

Change "Data Range"

1. Move the mouse pointer to the blank place of the chart and then right click to open shortcut menu (see Figure 1).


Figure 1

2. Choose the "Source Data" option. The "Source Data" dialogue box will appear (see Figure 2).


Figure 2

3. Change "=Eng!$B$4:$D$6" to "=Eng!$B$4:$D$5, Eng!$B$9:$D$13" in the "Data Range" field.

Here I would like to explain some of the elements in the expressions as follows:

* "=" denotes what the "Data Range" represents.

* "Eng" is the "Worksheet" we are working on.

* "$B$4" and "$D$6" mean cells B4 and D6 respectively.

Therefore, the entire expression "=Eng!$B$4:$D$6" stands for the cell range from cells B4 to D6 in the "English" worksheet. Whereas the expression "=Eng!$B$4:$D$5, Eng!$B$9:$D$13" stands for the cell range from cells B4 to D5 and the cell range from cells B9 to D13 in the "English" worksheet.

Apart from using the above method to change the "Data Range", we can also achieve the same result by using the mouse. First click the small button next to the "Data Range" field and you will see the minimized "Source Data" dialogue box (see Figure 3).


Figure 3

Now press and hold the left button of the mouse and drag it to extend the dotted area in the worksheet to select the cell range from cells B4 to D5. With the key pressed, do the same to select the cell range from cells B9 to D13 (see Figure 4).


Figure 4

4. Press the key to return to the "Source Data" dialogue box.

5. Click the "OK" button to get the result below (see Figure 5)


Figure 5

Although the new chart remains as a column chart, it now consists of totally different statistical figures. In addition, it shows the trend of each type of crime.

"Sharing IT as it applies to your daily life."
(E-mail address: ITB_ForceByte_Editor@police.gov.hk)

<<Back to Features>> <<Back to Top>>