Chapter Three 1

Using Excel for Business Applications

Chapter Three

Drawing Graphs Using Excel

Graphs are visual presentations of worksheet data. In Excel, graphs are known as
charts. Excel enables you to make a variety of graph types. Graphs are linked to the
worksheet data and are automatically updated when the data is changed. Graphs are
more powerful in presenting information than using tables of data. Graphical
presentation makes it easy for your audience to comprehend comparisons, patterns,
and trends in data.

For effective presentation, graphs need to be carefully constructed; otherwise, they

may end up presenting data relationships that are meaningless or misleading in a very
neat and appealing manner.

The following basic rules must be followed when constructing a graph:

1. Choose a suitable graph type for the types of data you have.
2. Keep the graph as simple as possible.
3. Clearly annotate your graph with a title, axis labels (x and y axis) and a
legend.

Table 3.1 Commonly Used Graph Types

Graph Type Use Example
Columns Are suitable for showing changes over a period of
time or to illustrate comparisons among individual
items.
Bar Is suitable for showing comparisons between items.
It is similar to column graph but puts less emphasis
on time flow than column graph.
Line Is suitable for showing trends in data over a period
of time, at even intervals. Line graphs emphasise the
rate of change over time rather than the magnitude
of change.
Pie Is used to show the proportional size of items that
make up a whole. It always contains only one data
series.
Area Is used to emphasise the magnitude of change
relative to time. It also shows the relationship of
parts to a whole by displaying the sum of the plotted
values.

@ Mgaya KV University of Botswana

 Chapter Three 2
Using Excel for Business Applications

Doughnut Similar to pie graph, the doughnut is used to show

relationship of parts to a whole. Unlike pie graphs
more than one variable can be used. Each different
series is represented as a ring.

To start with all the icons which are used to draw a graph are found in the charts
group of insert tab.

Figure 1

Activity 31. Drawing a Basic Graph

Enter the data shown below in your worksheet

Figure 2

We would like to use the data to draw a graph about crime incidences of a certain city
for the month of May 2009. The data renders it very easy to draw a basic graph.

Select the data that will be used to create the graph by highlighting cell range A3:F7
Click the Insert tab and then select Column from the Charts group.
Select 3-D Clustered Column as your type of column graph. The result will be a
column graph shown below.

@ Mgaya KV University of Botswana

 Chapter Three 3
Using Excel for Business Applications

Figure 3

Note that from the selection we did, Excel has managed to pick up the Horizontal
(category) Axis labels [ North, West, etc.] as well as the legends [Carjacking,
Mugging, etc.]. What is remaining is for us to insert the graph titles: Chart Title,
Primary Horizontal Axis title and the Primary Vertical Axis title. We will use
the Labels group of the Layout tab to insert the titles.

Inserting the Chart Title

Click the edge graph to select it. A selected graph will have some dots on its corners
and edges. Select the Layout tab.

Figure 4

From the Labels group click the down arrow of the Chart Title and select Above the
Chart option as shown in the diagram below.

@ Mgaya KV University of Botswana

 Chapter Three 4
Using Excel for Business Applications

Figure 5

A Chart Title box will appear above the graph. While the title selected the words:
Crime Report (May 2009): NB: You can click and replace the words Chart Title as
suggested to you by Excel, or while the Chart title remains selected you can type and
enter the title in the Formula bar.

Figure 6
To enter the Horizontal Axis title click the down arrow of the Axis Titles, select
Primary Horizontal Axis Title and Title Below Axis as shown in the diagram below.

An Axis Title box will appear replace the words Axis Title with the words: City
Area.
To enter the Vertical Axis title click the down arrow of the Axis Titles, select
Vertical Horizontal Axis Title and Rotated Title.
Type and enter the following as the vertical titles: Number of Incidences. Your
finished graph should look like the one shown below.

@ Mgaya KV University of Botswana

 Chapter Three 5
Using Excel for Business Applications

Figure 7

Activity 3.3 Drawing a Graph From Data That Has Gaps

Enter the data into your worksheet as shown in Fig.. below. We would like to draw a
column graph that will show the number of children who finished secondary school in
four provinces in the years 1990 to 1995.

Figure 8

Select the data that will be used to create the graph by highlighting cell range A5:G8.
Click the down arrow on the Column icon from the Charts group of the Insert tab.
Excel will show you the various types of column graphs which you can use. Select
clustered cylinder from the Cylinder group. Your result should be like that shown in
Fig…

@ Mgaya KV University of Botswana

 Chapter Three 6
Using Excel for Business Applications

Figure 9

Figure 10
The Ribbon will now show two more tabs, Layout and Design.

Our next task is to complete the basic graph by inserting the Title, the Horizontal
Axis (X-Axis) title and the Vertical Axis (Y-Axis) titles.

Click the edge graph to select. A selected graph will have some dots on its corners
and edges. Select the Layout tab.

Entering the Labels

Using the Labels group of the Layout tab you can enter the Chart Title, Y- and X-
Axes Titles, the Legend, Data Labels and Chart Data Table

@ Mgaya KV University of Botswana

 Chapter Three 7
Using Excel for Business Applications

From the Labels group click the down arrow of the Chart Title and select Above the
Chart option as shown in the diagram below.

A Chart Title box will appear above the graph. Replace the words Chart Title
with the words: Number of Children Finishing Secondary School (1990-1995)

To enter the Horizontal Axis title click the down arrow of the Axis Titles, select
Primary Horizontal Axis Title and Title Below Axis.

An Axis Title box will appear, Click it and replace the words Axis Title with the
words: Years.
To enter the Vertical Axis title click the down arrow of the Axis Titles, select
Vertical Horizontal Axis Title and Rotated Title.
Type and enter the following as the vertical titles: Number of Children

Entering the Horizontal (Category) axis Labels

Entering of the Horizontal (category) axis labels in this example is more
involving. These could not be entered straight away because of two reasons.
Firstly there is a data gap in the range B5:G5. Secondly, the horizontal (category)
axis labels which are found in the range B3:G3 have been entered as numbers and
not labels. This means that Excel will take them as data and not labels.

This forces us to use the following process to enter the Horizontal (category)
axis labels:

Click the Chart

On the Design tab click Select Data from the Data group. A Select Data Source

Figure 11
window will appear as shown below.

@ Mgaya KV University of Botswana

 Chapter Three 8
Using Excel for Business Applications

Click the Edit button being pointed to by the arrow in the diagram. An Axis Labels
dialog window will appear prompting you to enter the data range for the axis labels.

Figure 12
Our data range in this case is B3:G3. Do not enter this range but click in the entry box
and then click and highlight cell ranges B3:G3. Excel will enter the cell range as
=Sheet1!$B$3:$G$3.
Click to exit the Axis Labels dialog box

Click OK to exit the Select Data Source window. You should end up with a graph
shown below.

Figure 13

A Quicker Way to Insert the Various Graph Titles

In this example we will use Quick Layouts option of the Design tab to help enter the

@ Mgaya KV University of Botswana

 Chapter Three 9
Using Excel for Business Applications

three graph titles: Chart Title, X-Axis Title and Y-Axis Title.

Activity 3.2 Using Quick Layouts To Insert Graph Titles

Use the data that we used in Activity 3.1
Select the data that will be used to create the graph by highlighting cell range
A3:F7
Click the Insert tab and then select Column from the Charts group.
Select 3-D Clustered Column as your type of column graph.
Click to select the graph if it is not already selected
Click the Design tab. From the Quick Layout option click the down arrow and select
Option 9. A graph like the one shown in Fig.. will appear. Note that Excel has

Figure 14
inserted its own titles. What you need to do now is to change them into proper titles.
So, to change the the main title for the graph click the Chart Title and change it to
Crime Report (May 2009). Do the same for the X-Axis and Y-Axis titles.

Resizing, Moving and Deleting a Graph

Resizing

After you finish drawing a graph it may not look nice because it is either too small or
some headings have eaten away part of your graph area, See Fig ...One way of
increasing the visibility of the graph is to reduce the size of the some of the titles,
especially the Chart title.

A second method which can be used in combination with the first one is to resize the
graph. There are two methods of resizing a graph. To use the first method, click the
graph’s borders to select it. A selected graph will have dots on its corners and sides.
These are called handles and there eight of them. Handles on the corners will resize
the chart proportionally while handles along the lines will stretch the graph out of
proportion. To resize the graph using these handles click and drag the mouse to
reduce or to enlarge the graph.
@ Mgaya KV University of Botswana
 Chapter Three 10
Using Excel for Business Applications

Figure 15

The second method is to use the size button in the format tab, normally found to the
extreme right of the Format tab. There are two ways of using it. If you click the down
arrow a small Size window will appear which enables you to quickly adjust the height
and width of the graph in centimeters. Alternatively, on that same small window click
the Size and Properties window launcher and the window shown below will
appear. Using this window you can increase or reduce the size of the graph by using
percentages and by putting a tick on the Lock aspect ratio option you can guarantee
that the height and width aspect of the graph remain the same during resizing.

Figure 16

@ Mgaya KV University of Botswana

 Chapter Three 11
Using Excel for Business Applications

Moving a Graph
Sometimes the graph may be hiding some data below it and you would like it to
appear somewhere else within the worksheet. To move the graph to a different
position within the worksheet first put the mouse pointer on the edge of the graph
until the pointer turns into four arrows. Click and drag the graph to the place where
you want it to appear within the worksheet.

Deleting a Graph
To delete a graph, first click to select it. When handles appear on the graph, press the
Delete key.

Drawing a Graph from Non-Contiguous Data

Activity 3.1 showed how to draw a graph from data which is contiguous i.e. cell range
A2:F7. But you may want to draw a graph from cell ranges that are non-adjacent. For
example, from Fig 1.4 we may want to draw a graph that will show number of
children finishing school in only two provinces, East and North.

You can draw the graph directly without re-organising the data. The key to achieving
that is the Ctrl key. What you need to do is first to press and hold down the Ctrl and
then select by highlighting the various data ranges that you want to include in the
graph.

Activity 3.2 Drawing a Graph from Non-Contiguous Data

Figure 17
In this Activity we will draw a graph using non-contiguous cells from Fig 17. Only
the first few steps will be shown because the other steps are the same as those of
Activity 3.1. We will draw a graph that will show the number of children who
finished secondary school from two provinces, East and North.
Press and hold down the Ctrl key.
From the data shown in Fig 17 highlight cell range A5:G5.
Highlight cell range A7:G7. Your worksheet now should be the same as Fig
xxx, if not, repeat the procedure above.
Continue with the other procedures of drawing a graph as narrated in Activity
xxx Your finished graph should be the same as that shown in Fig 18

@ Mgaya KV University of Botswana

 Chapter Three 12
Using Excel for Business Applications

Figure 18

Modifying a Graph
After drawing a graph you may find that you wish to make modifications which might
involve adding or editing titles, inserting or editing legends, renaming, adding and
deleting data series. In this section, we will be modifying a graph as shown in Fig:
[Link] us first create the graph.
 Enter the data shown bellow in your worksheet.

Figure 19

 Highlight cell range A6:G8. It is not a mistake, Please do not highlight A5:G8
 Click the Insert tab and then select Column from the Charts group.
 Select 3-D Clustered Column as your type of column graph.
 You should get a graph that is not well annotated like that of Fig. 20

We would like to modify the graph shown in Figure 20

@ Mgaya KV University of Botswana

 Chapter Three 13
Using Excel for Business Applications

2500

2000

1500 South
North
1000
West

500

0
1 2 3 4 5 6

Figure 20

Adding Data Series to a Graph

The graph in Fig. 20 does not include data for the East province. Let us add it now.

Activity 3.3 Adding Data Series to a Graph.

 Click the graph to select it. Grab the graph and take it aside to expose the
worksheet data. You can even reduce its size by using the Size and Properties
window (remember to use percentages and put a check on Lock aspect ration
option).
 Click the Design tab.
 From the Data option click Select Data. A Select Data Source window shown in
Fig xxx will appear. We want to add a fourth series called East.

@ Mgaya KV University of Botswana

 Chapter Three 14
Using Excel for Business Applications

Figure 21
 On the left side of the window [Legend Entries (series)] click the Add button
and an Edit Series window shown in Fig... will appear. This window
will enable us to enter the series name and the series values.

Figure 22

 To enter the series name first click in the Series name box and then click cell A5
from your worksheet.
 To enter the series values first click and delete everything in the Series values box
and then from the worksheet highlight the data series for east province, B5:G5.
See Fig xxx.
 Click OK to finish entering the new series.
 Click OK to exit the Select Data Source window.
 Use the procedures narrated in Activity xxx to insert The Graph Title, X-Axis
Title and Y-Axis Title
 Use the procedures followed in Activity …. to enter the Horizontal (Category)
Axis Labels

@ Mgaya KV University of Botswana

 Chapter Three 15
Using Excel for Business Applications

Removing a Series from the Graph

Just as you can add data series to a graph, you can also delete some data series from
the graph. You have two options to delete a data series:

Option 1:
 Click to select the graph
 Click one of the bars or lines which form part of the series you want to delete (The
whole line or all bars for the series will appear chosen (eg. all bars representing
the series will have bubbles at the bottom and the top as shown in Fig 23).

Figure 23

 Press the Delete Key to delete the series.

Option 2:
 Click to select the graph
 Click Select Data from the Data group of the Design tab. A Select Data Source
window will appear
 On the left hand side there is a window that shows legends of the data e.g. South
North, West, and East.
 To delete the North series click the entry as shown in Fig 23 and then press the
Remove button shown above the data series.
 Click Ok to finish the deletion process.

@ Mgaya KV University of Botswana

 Chapter Three 16
Using Excel for Business Applications

Figure 24

Changing Graph Type

After drawing one type of graph e.g. column you can easily change it to another type
such as a line graph. But Excel puts some restrictions on changes that you can make
between three dimensional (3-D) and two dimensional (2-D) graphs.

 To quickly change a graph, from one type to another select the graph and then
click the mouse button.
 From the context menu select Change Chart Type
 Select the chart type you want and then click OK

Creating Combination Graphs

We can easily create a graph that combines different graph types. Combination graphs are
very useful for comparing different types of data such as number of tourists and
revenues received, units sold and net income and so on. Drawing combination
graphs would normally involve using a secondary axis. Primarily this is so because
the graphs will involve two different types of measurements such as litres of fuel
used and kilometres covered or units sold and revenue received. Another reason is
that with combination graphs you may find that the other series may not be clearly
seen on the graph because of the huge differences in the amount of units of
measure.

@ Mgaya KV University of Botswana

 Chapter Three 17
Using Excel for Business Applications

Activity ..
 Enter the data given in Fig 25
 Highlight cell range A3:C9 and draw a column graph.

Figure 25

Activity xxx will produce a graph shown in Fig. 26. Because of the huge differences
in the numbers used in the two data series, the number of tax payers cannot be easily
read from the graph. In such situations, it is better to use a combination graph.

Figure 26

Activity xxx. Drawing a Combination Graph

In this Activity we will use the data given in Fig 25 to draw a combination graph
 Use the data shown in Fig25
 Highlight data range A3:C9
@ Mgaya KV University of Botswana
 Chapter Three 18
Using Excel for Business Applications

 Select Insert and then Column from the Charts group.

 Select Clustered Column from the 2-D column group. This will produce a graph
like that shown in Fig xxx above.
 Click to select the Revenue Received series and make sure that its bars have got
some blue circles.
 Click the mouse’s right button and select Change Series Chart Type. This will
bring up a Change Chart Type window. From the Line group select Line and
then click OK
 Click to select the line graph you created above.

Figure 27
Now we need to create a secondary axis for the data series
 Click the Layout tab and from the Current Selection group select Format
Selection. A Format Data Series window will appear as shown in Fig 27.
 Click the Secondary Axis radio button and then click Close. You will have a
graph like that shown in Fig xxx below. What we need to do now is to label it
properly.

 First let us move the legend so that Legend to the bottom of the graph. Click to
select the graph. Click the Layout tab and then from the Labels group click the
Legend down arrow and select Show Legend at Bottom.
 Click to select the graph again. Click the Layout tab and then Labels group click
the Chart Title arrow and select the Above Chart option. Enter the title as:
Revenue Collection Performance.
 From the Labels group click the down arrow of the Axis Titles and select
Primary Vertical Axis Title followed by Rotated Title. Enter the primary
vertical axis title as: Number of Tax payers (‘000).

@ Mgaya KV University of Botswana

 Chapter Three 19
Using Excel for Business Applications

 From the Labels group click the down arrow of the Axis Titles and select
Secondary Vertical Axis Title followed by Rotated Title. Enter the secondary
vertical axis title as: Revenue Collected (‘000,000).

Figure 28

The final graph will look like that shown in Fig 29

Figure 29

Drawing and Formatting Pie Graphs

Pie graphs are probably the most frequently used types of graphs. They are used to
show the component parts of a whole as such only one series of data is used. Pie
graphs are very common business but not in scientific literature because it is argued
that it is more difficult for comparisons to be made between the sizes of items in a
chart when area is used instead of length.

@ Mgaya KV University of Botswana

 Chapter Three 20
Using Excel for Business Applications

It is suggested that pie graphs should not be used to represent more than six
component parts or when the components are almost of the same size..

Activity….Drawing Pie Graphs

In this activity we will draw a pie graph to show the total number of children who
finished secondary school between 1990 and 1995 from the four provinces from the
following data.

Figure 30
 In cell range H5:H8 add the total number of children who finished secondary
school from each province from 1990 to 1995.
 Select cell range A5:A8. Press the Ctrl key and select cell range H5:H8.
 Select the Insert tab from the Charts group click the down arrow and select
Pie. A pie graph will be drawn with legend.
 To insert the title for the graph first click to select it. Select the Design tab.
From the Charts Layout group click the down arrow of the Quick Layout
and select option 6. Excel would suggest the title as Chart Title. This needs to
be changed. Click the Chart Title and change it to: Number of Children
Who Finished Secondary School (1990-1995)

Figure 31
The finished graph will look like the one shown in Fig 31

@ Mgaya KV University of Botswana

 Chapter Three 21
Using Excel for Business Applications

Formatting Pie Graphs

Although a pie graph shares many features with other types of graphs such as column

Figure 32
graphs it has got some unique elements. In the following activities we will format the
data series, data points and data labels.

Formatting the Data Series

Changing the Angle of the Data Series

In this activity we will change the angle at which the slices of the pie graph appear
(i.e. rotating the pie graph). For example, in Fig 32 the West region is at the top of the
pie. You might want another region to be shown at the top. Fig33 shows a pie graph
viewed from different angles.

Figure 33

@ Mgaya KV University of Botswana

 Chapter Three 22
Using Excel for Business Applications

 Double click the data series i.e. all the pie slices. To get that click at the centre
of the pie chart and Excel will put one circle at the centre of the graph and one
each at the edge of each slice.
 Click the right button off your mouse and select Format Data Series from the
context menu. A Format Data Series window shown in Figxxx will appear.
Make sure that the Series Options is selected on the left section of the
window.
 On the right column of the window there are three sections. The first section
from the top, Angle of first slice enables you to change the positions of the

Figure 34
slices on the pie graph.

 To finish this exercise it is good if you can have a full view of both, the pie
graph and the Format Data Series window. Move the slider on the Angle of
first slice between No Rotation (0) and Full Rotation (360) and observe how
the slices get repositioned.

Exploding Slices from a Pie Graph

Excel allows you to explode one, some or all the slices from a pie chart. Pulling a

Figure 35

@ Mgaya KV University of Botswana

 Chapter Three 23
Using Excel for Business Applications

slice away from the pack is used to draw audience to a particular data item on the pie
graph. The most straight forward method of exploding a slice or slices from a pie
chart is to do the following:
 To pull out a single slide click to select the slice you want to pull out. If in this
process all the slices get selected click again on the slice you want to pull out
until that slice gets demarcated with three circles.
 Click and drag away the selected slice as shown in the left diagram in Fig 35
 To explode all the slices click to select all slices.
 Drag away the slices

Formatting Data Labels

To Format the data labels click to select the any of the graph’s data labels and then
right click to invoke the context menu. From the Context menu select Format Data

Figure 37
Labels. A Format Data Labels dialog box like that shown in Fig 36 will come up.
From this window, under Label Options, you can change what the label contains, the
label position and the separator. For example, the graph in Fig38 has a category name,

Figure 36

@ Mgaya KV University of Botswana

 Chapter Three 24
Using Excel for Business Applications

value instead of percentage and the data labels are positioned outside the graph area.
You can try on other formats from that Format Data Label window.

Formatting the Data Point (The Slices)

In this activity we will change the default format of a slice. To change the default
format of a slice you select the slice first.

Figure 38

Printing of Graphs
Excel provides two options of printing graphs, with the data and without the data
1. Printing with the data:
 Click anywhere within the worksheet to be printed but in the graph area
 Click the Office button and select Print and then Print Preview to get an idea
how the printed output will look like.
 If you are satisfied with the expected output click Print, else you can click
Page Setup to open the Page Setup dialog box from which you can make
several alterations.
2. Printing with the graph alone:
 Click inside the graph you want to print
 Click the Office button and select Print and then Print Preview to get an idea
how the printed output will look like.
 If you are satisfied with the expected output click Print, else you can click
Page Setup to open the Page Setup dialog box from which you can make
several alterations.

@ Mgaya KV University of Botswana