0 20 40 60 80 100
1992 1993 1994 Year
Profit (£000s)
Hailguard Rainguard
0 10 20 30 40 50 60 70 80 90 100
1992 1993 1994
0 10 20 30 40 50 60 70 80 90 100
Figure 1
A supplement to Chapter 5 of Quantitative Approaches in Business Studies Components of a Chart 1
Chart types 2 Data tables 4 The Chart Wizard 5
Column Charts 7 Line charts 8 XY(Scatter Charts) 8 Line and XY Charts Compared 9
Modifying a Chart 9 Combination Chart 11
Other topics 12
Components of a Chart
Before we see how to make charts in Excel, it will be useful to know the correct terminology for the parts of a chart. This will help you understand the various dialog boxes used to make or modify a chart, and to follow instructions in the Help facility.
The left hand chart in Figure 1 shows that areas have a fill and a border. You may specify the colours for each of these separately. I am not recommending the colours used here!
They were selected to make it easy to identify various areas. The areas are:
Chart area: The entire chart shown with a blue fill and dark blue border. All borders may be formatted to change their weight (thickness) and style (solid or dotted lines).
Plot area: The area used for charting the data - white with red border. Had the plot area been formatted with fill set to “none”, it would have the same colour as the chart area. The two axes override the colour of the border at the bottom and left side.
Legend: Small box with text - yellow with purple border. The legend identifies the various data series. A chart with one data series does not need a legend.
Columns: The columns for the different data series are generally shown with
different colours. It is also possible to separately colour the columns for a single data series.
In addition to simple colours, areas may be given a colour gradient and/or a pattern such as herringbone or brickwork.
The chart on the right in Figure 1 identifies these components:
X-category-axis: The horizontal axis shown in blue. The font was also formatted blue for this axis. The small lines at right angles to the axis are tick marks.
Y-axis: The axis to the left (red) is the y-axis. The values on the axis range from 0 to 100; these can be changed. There are tick marks every 10 units; we could change this to some other value.
Secondary axis: The chart is shown with a secondary y-axis in green. A secondary-axis is useful when two data series have very different ranges of values. It is inappropriate in the current case. When a chart has a secondary y-axis, it is possible also to have a secondary x-axis.
Lines, markers: The two data series in the chart are displayed with lines and markers.
We can chose (i) lines only, (ii) markers only, (iii) lines and markers, and (iv) no lines, no markers - the data series is invisible! The lines and the markers for one data series are generally given the same colour but they can be made different. The shape of the markers may also be changed;
the colours for the foreground (border) and the background (fill) may be independently changed.
In Figure 5.18 of Quantitative Approaches in Business Studies, a jagged line is used to indicate that a chart has a y-axis that begins at a non-zero value. Unfortunately, it is not possible to do this in Excel.
Chart types
Four of the basic chart types supported by Excel are shown in Figure 2 while Figure 3 shows some enhancements to these four types.
The chart titles show the names used by Excel for these types. Note that Excel uses the term column chart for what most of us call a bar chart and bar chart for a horizontal bar chart. The line and XY(scatter) charts appear to be almost the same but there are important differences, as we will see later. For now, look at the year axes in the two charts and compare the positions of the tick marks (the scale dividers on the axis) with the markers (the solid squares on the lines.) With a line chart the markers are located between tick marks; with an XY chart they are directly above the tick marks on the axis. We can format a Line chart to align the tick marks and markers.
The bar chart has been turned into a pictogram, and the titles and axes have been given a blue font. In the column chart, a blue fill has been given to the chart area, and the gap between columns has been reduced thereby widening the columns. Data labels have been added. The line chart has been converted into an area chart with drop down lines and a data table has been added. The chart title was moved in the XY chart and large open circles replace the squares used for the markers.
A Bar Chart
0 50 100 150 200
1996 1997 1998 1999
Year
Profit (£000s)
A Column Chart
80
120
140
165
0 20 40 60 80 100 120 140 160 180
1996 1997 Year 1998 1999
Profit (£000s)
An XY Chart
0 20 40 60 80 100 120 140 160 180
1996 1997 1998 1999
Year
Profit (£000s)
An Area Chart
0 50 100 150 200
Profit (£000s)
Profit 80 120 140 165
1996 1997 1998 1999
Figure 2
A Column Chart
0 20 40 60 80 100 120 140 160 180
1996 1997Year1998 1999
Profit (£000s)
A Bar Chart
0 50 100 150 200
1996 1997 1998 1999
Year
Profit (£000s)
A Line Chart
0 20 40 60 80 100 120 140 160 180
1996 1997 1998 1999
Year
Profit (£000s)
An XY Chart
0 20 40 60 80 100 120 140 160 180
1996 1997 1998 1999
Year
Profit (£000s)
Figure 3
trucks 19%
cars 36%
bicycles 30%
buses 11%
other
4% 500
1000 800
300 100
trucks cars bicycles buses other
trucks 19%
cars 36%
bicycles 30%
buses 11%
other 4%
500
1000 800
300 100
trucks cars bicycles buses other
Figure 4
Figure 4 shows some examples of pie charts. The data for a pie chart may be displayed as the values or as percentages. We may display the category values (truck, car, etc) either next to their slices or in a legend The chart may be exploded or you may explode only those slices you wish to emphasise. The third chart (left on second row) is a 3D chart.
Excel allows you to make bar, column and pie charts three dimensional. Before you use a 3D chart, ask yourself if the 3D effect adds anything or does it hide information. The original purpose of a pie chart was to have slices whose areas were proportional to their values. Does the 3D chart preserve this feature? The fourth chart is called a doughnut chart by Excel. The doughnut chart is not limited to just one data series as is the pie chart.
Data tables
Data to be charted may be entered as columns or as rows as shown in Figure 5. If we were planning to make a bar, column or line chart from this data the values 1992, 1993, 1994 would be referred to as the category data. The other three sets of data with headings Hailguard, Rainguard and Total are called data series. However, Excel expects category data to be text data such as Monday, Tuesday, etc. We need to prevent Excel from treating the year values as numbers. The simplest way to do this is to enter them as text.
Rather than typing 1992, type ‘1992. The single quote (apostrophe) will cause the entry to be treated as text but the symbol will not be displayed in the cell.
Year Hailguard Rainguard Total Year 1992 1993 1994
1992 70 19 89 Hailguard 70 82 93
1993 82 46 128 Rainguard 19 46 68
1994 93 68 161 Total 89 128 161
Figure 5
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
A B C D E F G H
Before tax profits for 1994
Hailguard (M) £53,000 Hailguard (F) £40,000 Rainguard (M) £46,000 Rainguard (F) £20,000
Hailguard (M) 33%
Hailguard (F) 25%
Rainguard (M) 29%
Rainguard (F) 13%
Hailguard (F) 25%
Rainguard (M) 29%
Hailguard (M) 33%
Rainguard (F) 13%
Figure 6
The Chart Wizard
The process of making a chart may be somewhat oversimplified to three steps (1) enter the data, (2) select the data, and (3) click on the Chart Wizard icon on the Toolbar.
We will begin by making a pie chart similar to that in Figure 5.2 of Quantitative Approaches in Business Studies. Open a new Excel workbook and, on Sheet 1, enter the values shown in A1:B6 of Figure 6. Remember to type a single quote before each year value (‘1992) to make it text.
Select the range A3:B6 with the mouse and click the Chart Wizard icon. In Step 1 of the Chart Wizard process we select the type of chart we require. On the right side of the Step 1 dialog box (see Figure 7), click on the pie chart and on the left side click on the top sketch to select that sub-type. Experiment with the Press and Hold bar to preview your chart. Click the Next button to move to the next step.
In Step 2 (Figure 8) we can see that Excel has correctly decided that our data is in a column form. There is seldom any reason to change anything in Step 2 although occasionally options on the Series tab can be useful. Clicking the Next button will move you to Step 3.
Figure 7 Figure 8
Figure 9
Figure 10
In Step 3 (Figure 9) we will have Excel display the data label and the percentage values on the chart. It is interesting to note that Excel does not provide an option to display labels and actual values so we cannot readily make a chart the same as Figure 5.2 of Quantitative Approaches in Business Studies. You may wish to explore the other tabs on the dialog box before clicking the Next button.
In Step 4 (Figure 10) we have two choices for the location of our chart: on a separate chartsheet or on the same worksheet as the data. When you have a complex worksheet it may be convenient to place the chart on its own sheet. Having it on the worksheet has the advantage of being able to see how the chart changes when the data is altered. We will select this option before clicking the Finish button.
You may be a little disappointed with the chart you have just made. Later we see a number of ways of modifying a chart but here are three quick things to do. Click just inside the border of the chart to get eight fill handles (small black squares). Drag the chart to the position you want it. Move the cursor over one of the fill handles - the cursor changes
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
A B C D E F G H I J K L
Pretax profit for year 1992 - 1994
Year Hailguard Rainguard Total
1992 70 19 89
1993 82 46 128
1994 93 68 161
Weatherguard Profits
89
128
161
0 50 100 150 200
1992 1993 1994
Year
Profit (£000s)
0 50 100 150 200
1992 1993 1994 Year
Profit (£000s)
Rainguard Hailguard
0 20 40 60 80 100 120 140 160 180
1992 1993 1994
Year
Profit (£000s)
0 10 20 30 40 50 60 70 80 90 100
1992 1993 1994
Year
Profit (£000s)
Hailguard Rainguard
0%
10%
20%
30%
40%
50%
60%
70%
80%
90%
100%
1992 1993 1994 Year
Percentage of Profits
Rainguard Hailguard
Figure 11
shape to a two-headed arrow (6). Click and drag the mouse to resize the chart; Excel will keep the pie as a circle. Finally, right click on any of the words in the chart to cause a popup menu to appear. Use the Format Data Labels item on the menu and on the Font tab select a more appropriate size for the font. The chart is now complete. You may wish to save the workbook. Later we will change the chart to resemble the lower one in Figure 6.
Column Charts
We will make the column charts displayed as Figures 5.3 to 5.6 in Quantitative Approaches in Business Studies. The Excel results are shown in Figure 11. The data in the worksheet was extracted from Table 5.3 of the book. As we start to make the simple column chart, it becomes obvious that there is a small problem. We need to select the Years and the Totals columns but they are not side-by-side. There is a trick to selecting non-contiguous data. Select A3:A6 in the normal way and then hold down the C key while dragging the cursor over D3:D6.
With the data selected, click the Chart Wizard. In Step 1 specify a column chart using the top right hand sub-type (clustered column). Move on to Step 2 where there is nothing to do and then to Step 3. On the Titles tab enter Years for the X category title and Profit (£000s) for the Y-axis title. Move to Step 4 and set the location as the worksheet. Your chart should resemble the first one in Figure 11. Later we will modify it to look like the second chart.
Line Chart
0 10 20 30 40 50 60 70 80 90 100
1992 1993 1994
Year
Profit (£000s) Hailguard
Rainguard
Stacked Line Chart
0 20 40 60 80 100 120 140 160 180
1992 1993 1994
Year
Profit (£000s) Rainguard
Hailguard
Percentage Line Chart
0%
10%
20%
30%
40%
50%
60%
70%
80%
90%
100%
1992 1993 1994
Year
Percent of profit
Rainguard Hailguard
Figure 12
The next three charts have two data series – the Hailguard and the Rainguard data. To make a chart similar to the first one in the bottom row of the figure, select A3:C6 and start the Chart Wizard. In Step 1 select Column as the type and the second sub-type (stacked column). Continue to Step 3 where the titles may be added. The other charts may be made in the same way, selecting the appropriate sub-types.
There is a quick way to make the second two charts. Select the first column chart with the two data series and perform a Copy & Paste operation. Right click the new chart and open the Chart Type menu item. A dialog box identical to Step 1 of the Chart Wizard opens up. Select the required sub-type and click the OK button.
Line charts
By following the steps above and replacing Line chart for Column chart in Step 1 of the Chart Wizard you may quickly make line charts with the same data, as shown in Figure 12 below. When selecting the sub-type you must decide if you want just a line, just markers, or both. You also decide if you want a trend chart, a stacked chart or a percentage chart.
Just click on the Press & Hold button to check if you have opted for the right sub-type.
XY(Scatter Charts)
XY or Scatter charts are the types of charts you plotted in algebra class. Each data point has two values – an x-value and a y-value – which determine the position of the point on the chart. Actually, we may now speak of a graph if we so wished.
When the increments between successive x-values are constant, a line chart and an XY chart will be very similar. When the increments vary, a line chart would be totally inappropriate. In an XY chart we have x-values, in all other charts we have x category labels.
The reader should have no problem making the left hand XY chart from the data shown in Figure 13 using the same steps as before but selecting this time an XY chart in Step 1 of the Chart Wizard. We will shortly see how to modify the chart to appear like the second one in the figure.
1 2 3 4 5 6 7 8 9 10 11
A B C D E F G H I
Advertising Revenue 3.05 87.31 4.72 92.35 6.91 94.51 8.01 109.64 11.83 103.19
0.00 20.00 40.00 60.00 80.00 100.00 120.00
0.00 5.00 10.00 15.00 Advertising expenditure
Sales revenue
70 80 90 100 110 120
0 2 4 6 8 10 12
Advertising expenditure
Sales revenue
Figure 13
Advertising (£000)
Sales (£000,000)
80 200
100 345
110 500
230 900
300 1000
Line chart
0 200 400 600 800 1000 1200
80 100 110 230 300
Adve rtising
Sales
XY chart
0 200 400 600 800 1000 1200
80 120 160 200 240 280 Advertising
Sales
Figure 14
Line and XY Charts Compared
As we stated earlier, Line and XY charts appear to be very similar. We will now see that there is a fundamental difference. Figure 14 shows a Line and an XY chart produced from the same data. Note that the steps in the x-vales are not equal. The Line chart, however, plots them with equally spaced markers, while the XY chart places the markers at positions that relate to their x-values. The Line chart is totally misleading (at least to the casual observer who fails to note the unequal steps in the x-values) in that it suggests that sales will keep rising as more is spent on advertising. The XY chart clearly shows that there is a diminishing return as advertising expenditures increase.
The moral is: When the x-values are numeric use an XY not a Line chart.
Modifying a Chart
We will now see how we can change, or format, the various components in a chart. At the top of Figure 11 there are two similar charts. We wish to enhance the first to give the second. Right click on the chart area (i.e. just inside the outer border) to display a popup menu as shown in Figure 15 and select the Chart Options item to bring up the Chart Options dialog box. This is very similar to Step 3 of the Chart Wizard. Open the Titles tab (Figure 16) and add Weatherguard Profits as the chart title. By the way, the Excel Spell Check feature checks text in charts as well as in worksheet cells.
Figure 15
Figure 16
Figure 17 Figure 18
If you look at the menu shown in Figure 15 you will see an item called Chart Type. You can use this to open a dialog box that resembles Step 1 of the Chart Wizard and hence change the chart type should you wish to do so.
The first item in the menu is Format Chart Area. This is because we right clicked within the chart area. You could use this item to change the colours of the chart area fill and border. Experiment with this, ending with a simple column chart.
The next task is to change the appearance of the columns. Right click on any one of the columns to display a popup menu which is an abbreviated version of that shown in Figure 15. The first item is Format Data Series. Click on this and open the Patterns tab (Figure 17). Select a new colour for the column fill and for the border if you wish. Open the Options tab (Figure 18) and decrease the gap to 50. Note there is no tool for widening the column but changing the gap automatically changes the column’s width. The overlap setting has meaning only when there are two or more data series.
Compare the two charts in Figure 13; the right hand one has (i) no fill in the plot area, (ii) values on the two axis that display no decimal places, and (iii) a y-axis scale that starts at 70. The first change is readily made by right clicking the plot area, selecting Format Plot Area, and on the Pattern tab clicking in the None radio button in the Area region.
The two axes display values with two decimals because this is the format of the source data. When you right click on the x-axis and open the Format Axis dialog box you find a Number tab which in all respects is the same as the dialog used to format a cell. Specify 0 decimals. Repeat this for the y-axis.
With the Format Axis dialog box open for the y-axis, open the Scale. This is where you can specify the minimum and maximum values for the axis – we need 70 and 100. You will also see that you can set the major units to some other value in this dialog. Experiment with this and other features of the dialog, observing the effect on the chart.
If you select a legend by clicking on it, it may be dragged to any position. Similarly, the text on a pie chart may be repositioned and in this manner make the first chart in Figure 6 resemble the second. However, you must click twice – not a double click but two independent clicks. The first selects all the text (at this stage you may format all the text at once), the second selects just one label which may now be dragged to a new position.
It takes a little practice, so be patient.
Combination Chart
A combination chart has a mixture of chart types. The chart in Figure 5.15 of Quantitative Approaches in Business Studies has one data series displayed as columns and another as a line. We can create charts like this in Excel as shown by the second chart in the figure below.
We begin by making a column chart – the first chart in Figure 19. The last entry in the Age column is clearly text, so Excel does not treat this column as a data series. Right click on one of the bars of the Cumulative data columns in the chart and select the Chart Type menu item. In the resulting dialog box, specify Line type. If you have difficulty selecting the data series, temporarily change one of its values – for example, change C8's value to 50 – to enlarge one of the columns to facilitate its selection.
Right click again on the Cumulative data, select the Format Data Series item and on the Axis tab specify Secondary Y Axis. Other formatting may now be done: format the secondary axis to show 0 decimals; add a secondary axis title (right click anywhere on chart, open Chart Options item, open Titles tab); format the fonts to a smaller size (Excel 97 and 2000 seem to make the text too large for most uses), etc.
Age distribution data
Age Frequency Cumulative %
20 2 3.33%
30 8 16.67%
40 30 66.67%
60 20 100.00%
60+ 0 100.00%
0 10 20 30 40
20 30 40 60 60+
Age
Frequency
Frequency Cumulative %
0 5 10 15 20 25 30 35
20 30 40 60 60+
Age
Frequency
0%
20%
40%
60%
80%
100%
120%
Cumulative %
Frequency Cumulative %
Figure 19
Other topics
A frequently asked question is How do I handle missing data? Suppose you kept records on the number of visitors at your front office each month. However, Jack lost the data for March. You enter the data in a worksheet with no value in the cells for March and make a line chart. The line is broken into two with a gap for March. To avoid this, type =NA() in the March cell – this will display as #N/A. The chart will now join the February and April data points.
Quantitative Approaches in Business Studies concludes Chapter 5 with a mention of Excel Frequency function. For information on this and the Histogram tool see Chapter X of this supplement.
For more hints on charts, including how to make a dynamic chart, see the author’s web site Bernard Liengme