The following sheets all demonstrate the charting possibilities of Excel if a bit of careful formatting and data layout is used.
Some graphs rely on additional data to be calculated from the soure data for the correcting layout to be achieved.
These calculations are highlighted in yellow on the sheets.
Each sheet contains some information explaining the technique used to achieve the results.
1 Climate Chart Plots a temperature range and a rainfall range on a chart for each month.
2 Average Displays an 'average' line on a bar chart.
3 Stacked Bar Draws multiple bar charts onto a single chart to allow direct comparison.
4 Stacked Line Draws multiple line charts onto a single chart to allow direct comparison.
5 Scroll For very long data series, allows the user to scroll through the data.
6 Grading Changes the colours of portions of the bars to show targets or grades which have been reeched.
7 Pyramid Draws a population pyramid.
8 Volatility Displays the daily rate of change of the price of a product expressed as a percentage, as well as the actual price.
9 Map Allows values to be plotted onto a map at different locations.
10 Gantt Chart 1 Draws a Gantt chart with the bars for different phases of the project in different colours.
11 Gantt Chart 2 Draws a Gantt chart with the bars in different colours to represent the person responsible for each activity.
12 Histogram Plots a Histogram after calculating the distribution of a set of values.
13 3 Types Draws an area, bar, and line all on the same graph. Shows the correlation of river flow to rainfall and temperature.
14 Negative Colours Bars below the x-axis are a different colour from those above the axis.
15 Overlap With two data series representing consumption and production, and the area is highlighted where there is a deficit.
16 Stock A complex stock chart with six data series - open, close, hi, lo, volume, and market index.
matting and data layout is used.
g layout to be achieved.
e, as well as the actual price.
nsible for each activity.
ow to rainfall and temperature.
hlighted where there is a deficit.
Temperature Rainfall
Min Max Min Max
Climate Chart Rainfall and Temperature Ranges
140
M
Min Temp. ax Temp. Rainfall Range
Jan 5 6 30 45 120
Feb 6 8 37 75
Mar 8 12 45 80 100
Apr 11 16 35 55
Rainfall mm
80
May 15 20 30 45
Jun 19 26 20 35
60
Jul 21 27 0 15
Aug 20 25 0 20 40
Sep 14 20 15 30
Oct 11 15 25 50 20
Nov 8 10 30 60
Dec 6 7 35 53 0
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov
Titles appear in a hidden row so the titles
on the graph can be different from those
on the data grid.
mperature Ranges
30
25
20
Temperature C
Rainfall Range
15 Min Temp.
Max Temp.
10
5
0
Nov Dec
Sales Average
David 19 38.33
Alice 47 38.33 100
Sarah 34 38.33 90
James 63 38.33 80
Martin 14 38.33 70
Jenny 53 38.33 60
Sales
50
Average
40
Average is calculated and 30
created as a dummy second 20
series with the same value for all
entries. 10
0
David Alice Sarah James Martin Jenny
Sales
Average
Profit Sales Costs MAX Profit dummy1 Sales dummy2 Costs
13 5 7 25 13 12 5 20 7
15 7 8 15 10 7 18 8
16 8 10 16 9 8 17 10
16 9 11 16 9 9 16 11
15 11 13 15 10 11 14 13
12 11 17 12 13 11 14 17
9 12 18 9 16 12 13 18 The actual values to display remain
10 14 19 10 15 14 11 19 unchanged. Only need to calculate the
12 16 17 12 13 16 9 17 'dummy' values to appear between
15 19 18 15 10 19 6 18 each of the real data series.
19 22 21 19 6 22 3 21
20 23 22 20 5 23 2 22
Costs
Sales
Profit
1 2 3 4 5 6 7 8 9 10 11 12
al values to display remain
ed. Only need to calculate the
values to appear between
he real data series.
0 1 2
Profit Sales Costs MAX Profit Sales Costs
13 5 13 25 13 30 63
13 7 11 13 32 61
16 8 8 16 33 58
18 9 11 18 34 61
15 11 13 15 36 63
12 11 17 12 36 67
9 12 18 9 37 68
10 17 19 10 42 69 The actual values to display are
12 16 17 12 41 67 increased depending on where they
18 19 18 18 44 68 are to appear, i.e the bottom lin ehas
no increase. The middle line increases
19 22 21 19 47 71
by 25 which is the seperating value,
20 23 22 20 48 72 and the top line is increased by 50
18 20 19 18 45 69 (25*2).
17 17 16 17 42 66
17 13 14 17 38 64
Profit
Sales
Costs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Date Units Start 6
7/1/2006 34
7/2/2006 37 Date Units
7/3/2006 39 0 7/6/2006 52
7/4/2006 40 1 7/7/2006 55
7/5/2006 43 2 7/8/2006 54
7/6/2006 52 3 7/9/2006 49 Unit Sales
7/7/2006 55 4 7/10/2006 41
7/8/2006 54 5 7/11/2006 32 80
7/9/2006 49 6 7/12/2006 24 70
7/10/2006 41 7 7/13/2006 19
7/11/2006 32 8 7/14/2006 17 60
7/12/2006 24 9 7/15/2006 16 50
7/13/2006 19 10 7/16/2006 18
7/14/2006 17 11 7/17/2006 21 40
7/15/2006 16 12 7/18/2006 25 30
7/16/2006 18
20
7/17/2006 21
7/18/2006 25 This is the data to be graphed, 10
7/19/2006 29 and is retrieved from the main
data grid to the left, with an 0
7/20/2006 33
06 Jul
08 Jul
09 Jul
10 Jul
12 Jul
13 Jul
14 Jul
07 Jul
11 Jul
offest based on the value of the
7/21/2006 35 scrollbar.
7/22/2006 35
7/23/2006 36
7/24/2006 38
7/25/2006 41
7/26/2006 45
7/27/2006 53
7/28/2006 59
7/29/2006 63
7/30/2006 57
7/31/2006 43
14 Jul
15 Jul
16 Jul
17 Jul
18 Jul
Top Grade 50
Bottom Grade 25 The actual values to be graphed are split into 3
series from the single piece of data, and then
MIDDLE TOP
BOTTOM graphed as a stacked bar chart.
Dave 23 Dave 23 0 0
John 47 John 25 22 0
Sarah 34 Sarah 25 9 0
Alice 63 Alice 25 25 13
Martin 14 Martin 14 0 0
Jenny 53 Jenny 25 25 3
70
60
50
TOP
40
MIDDLE
30
BOTTOM
20
10
0
Dave John Sarah Alice Martin Jenny
Population
Percentage
Population Pyramid
Male Female Male Female
90+ 0 1 90+ 0 1
80-89 1 1 80-89 -1 1 90+
70-79 2 2 70-79 -2 2
80-89
Age Bands
60-69 3 4 60-69 -3 4
50-59 5 5 50-59 -5 5 70-79
40-49 4 5 40-49 -4 5
30-39 4 7 30-39 -4 7 60-69
20-29 8 8 20-29 -8 8
50-59
10-19 9 10 10-19 -9 10
0-9 11 10 0-9 -11 10 40-49
Total 47 53
30-39
20-29
10-19
The Male data is converted into a 0-9
negative figure.
-15 -5 5
Both series are then drawn on a
stacked bar chart where the vertical
axis has been moved to the centre by
specifying the max and min values of
the x axis.
lation Pyramid
Male
Female
15
Date Price Change Rise Fall
7/1/2006 473 0.00 0.00 0.00
7/2/2006 467 -1.27 0.00 1.27
7/3/2006 430 -7.92 0.00 7.92 Price and Daily Change
7/4/2006 442 2.79 2.79 0.00
7/5/2006 480 8.60 8.60 0.00 25.00
7/6/2006 484 0.83 0.83 0.00
7/7/2006 501 3.51 3.51 0.00
20.00
7/8/2006 497 -0.80 0.00 0.80
7/9/2006 403 -18.91 0.00 18.91
Percent Change
7/10/2006 390 -3.23 0.00 3.23 15.00
7/11/2006 388 -0.51 0.00 0.51
7/12/2006 359 -7.47 0.00 7.47
7/13/2006 324 -9.75 0.00 9.75 10.00
7/14/2006 390 20.37 20.37 0.00
7/15/2006 473 21.28 21.28 0.00
5.00
7/16/2006 398 -15.86 0.00 15.86
7/17/2006 430 8.04 8.04 0.00
7/18/2006 442 2.79 2.79 0.00 0.00
01 Jul
03 Jul
05 Jul
07 Jul
09 Jul
11 Jul
13 Jul
15 Jul
7/19/2006 480 8.60 8.60 0.00
7/20/2006 501 4.38 4.38 0.00
7/21/2006 501 0.00 0.00 0.00
7/22/2006 497 -0.80 0.00 0.80 Date
7/23/2006 403 -18.91 0.00 18.91
7/24/2006 390 -3.23 0.00 3.23
7/25/2006 388 -0.51 0.00 0.51 The 'Change' value is calculated from the current and previous days
7/26/2006 359 -7.47 0.00 7.47 values.
7/27/2006 324 -9.75 0.00 9.75
7/28/2006 390 20.37 20.37 0.00 This change is then converted into 2 data series (one for a rise and one
for a fall) so they can be plotted on the same chart but with different
7/29/2006 395 1.28 1.28 0.00
colours - by using a stacked bar chart.
7/30/2006 423 7.09 7.09 0.00
7/31/2006 437 3.31 3.31 0.00
Price and Daily Change
550
500
450
Price
400
350
300
250
17 Jul
19 Jul
21 Jul
23 Jul
25 Jul
27 Jul
29 Jul
31 Jul
Fall
Date Rise
Price
ated from the current and previous days
data series (one for a rise and one
ed on the same chart but with different
Population (millions)
Country x y Population
France 25 24 61
Germany 40 40 82
Russia 80 55 143
UK 14 43 60
The coordinates of the locations are UK
actually data values. Germany
Each location is a separate data
series so its name can appear on the
map. France
Russia
activity 0 1 2 3
Phase Activity Start Duration 1 2
PhasePhasePhase 3
The activity data is split into different
1 Proposal 1 3 Proposal 1 3 0 0 series (per phase) to allow it to be
1 Analysis 3 3 Analysis 3 3 0 0 plotted as a stacked bar with a
1 Design 4 5 Design 4 5 0 0 different colour for each phase.
1 Prototype 4 2 Prototype 4 2 0 0
2 Detailed Design 9 4 Detailed Design 9 0 4 0
2 Development 9 6 Development 9 0 6 0
2 Test 13 4 Test 13 0 4 0
3 User Training 17 5 User Training 17 0 0 5
3 Installation 17 2 Installation 17 0 0 2
3 Review 22 4 Review 22 0 0 4
0 5 10 15 20 25 30
Proposal
Analysis
Design
Prototype
Detailed Design
Phase 1
Development
Phase 2
Test
User Training
Installation
Review
The activity data is split into different
series (per phase) to allow it to be
plotted as a stacked bar with a
different colour for each phase.
Person Activity Start Duration Peter Allan John Sarah
Peter Proposal 1 3 Proposal 1 3 0 0 0
Allan Analysis 3 3 Analysis 3 0 3 0 0
Sarah Design 4 5 Design 4 0 0 0 5
John Prototype 4 2 Prototype 4 0 0 2 0
Peter Detailed Design 9 4 Detailed Design 9 4 0 0 0
Sarah Development 9 6 Development 9 0 0 0 6
Allan Test 13 4 Test 13 0 4 0 0
Sarah User Training 17 5 User Training 17 0 0 0 5
John Installation 17 2 Installation 17 0 0 2 0
Peter Review 22 4 Review 22 4 0 0 0
0 5 10 15 20 25 30
Proposal
Analysis
Design
Prototype
Detailed Design
Development
Test
User Training
Installation
Review
The activity data is split into different
series (per person) to allow it to be
plotted as a stacked bar with a
different colour for each person.
30
Peter
Allan
John
Values max min 0 Distribution
36 Distribution calculation is 0 9 5 0-9 5
30 performed using the COUNTIF 10 19 7 10 - 19 2
8 function to count the values in each 20 29 10 20 - 29 3
25 range. 30 39 18 30 - 39 8
15 40 49 23 40 - 49 5
69 50 59 25 50 - 59 2
38 60 69 28 60 - 69 3
35 70 79 29 70 - 79 1
38 80 89 31 80 - 89 2
11 90 99 34 90 - 99 3
42
84
48
98 9
32
63 8
20
7
51
7 6
41
30 5
2 4
92
90 3
63
2
21
38 1
52
9 0
0-9 10 - 19 20 - 29 30 - 39 40 - 49 50 - 59 60 - 69 70 - 79 80 - 89 90 - 99
80
43
9
73
44
Changing River Flow due to Temperature and R
Day Temperature Rainfall Flow Flow
20
1 15 0 3.08 30
2 17 16 4.11 MAX TEMP 40 18
3 18 0 4.62 19 45 16
4 16 0 3.08 30 14
Temperature C
5 14 7 2.57 MAX FLOW 25
12
6 14 23 5.14 185 50
7 12 25 7.70 75 10
8 13 37 10.78 105 8
9 15 35 12.84 125 6
10 16 28 15.92 155
4
11 16 14 19.00 185
12 18 12 18.49 180 2
13 19 0 13.86 135 0
14 18 15 12.84 125 1 2 3 4 5 6 7
Days
A third data series with a different numerical range
from the others can be plotted on the graph by
recalculating representative values that fit within the
range of those already being plotted.
Changing River Flow due to Temperature and Rainfall
40
35
30
25
Rainfall mm
Flow
20 Rainfall
15 Temperature
10
5
0
7 8 9 10 11 12 13 14
Days
Austria 5 6
Britain 2
France -1 4
Germany 3
2
Ireland 0
Negative values are given a different colour
Italy -5 0 option 'Invert if Negative' for the data series
Norway 1
Spain 3 -2
-4
-6
Germany
Italy
Norway
France
Ireland
Spain
Austria
Britain
egative values are given a different colour by selecting the
tion 'Invert if Negative' for the data series formatting.
Consumption Production ConsumptionDeficitProduction
16 30 16 0 14 0 Production, Consumption, and Deficit
18 28 18 0 10 0 35
17 24 17 0 7 0
18 22 18 0 4 0 30
19 19 19 0 0 0
25
18 14 14 4 0 0
17 12 12 5 0 0
20
16 12 12 4 0 0
16 13 13 3 0 0 15
14 14 14 0 0 0
13 18 13 0 5 0 10
14 21 14 0 7 0
16 22 16 0 6 0 5
17 21 17 0 4 0
17 20 17 0 3 0 0
18 18 18 0 0 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
19 15 15 4 0 0
19 14 14 5 0 0
17 14 14 3 0 0
15 15 15 0 0 0
11 17 11 0 6 0 Deficit is calculated and displayed in an additional data series.
9 19 9 0 10 0 Where there is no deficit, this series contains a 0
8 20 8 0 12 0 displayed.
7 21 7 0 14 0
7 23 7 0 16 0
8 25 8 0 17 0
10 26 10 0 16 0
Production, Consumption, and Deficit
Production
Deficit
Consumption
15 16 17 18 19 20 21 22 23 24 25 26 27
and displayed in an additional data series.
eficit, this series contains a 0 value so it is not
Open Hi Lo Close Index Volume Volume is recalculated to a representative
Open Hi/Lo Close
Market Index Volume Volume value whose values fall within in the range of
109 115 95 100 893 14,190 MAX VOL 300.9 those already to be displayed on the second
scale.
100 109 100 109 1,003 25,258 47300 535.6
109 109 88 89 904 26,015 551.7 This allows series with vastly different
89 98 56 62 673 37,840 802.4 numerical values to be displayed on the same
62 63 30 51 652 33,110 MAX INDEX 702.1 chart.
51 56 51 55 580 9,460 1003 200.6
55 55 31 34 591 47,300 1003.0
34 46 28 46 545 23,650 501.5
46 73 46 67 703 23,650 501.5
67 77 65 69 732 10,879 230.7
69 78 67 77 709 8,703 184.5
77 77 31 44 508 15,136 321.0
44 56 42 47 474 4,966 105.3 140
47 74 46 65 585 19,393 411.2
65 64 65 67 589 6,700 142.1 120
67 71 67 71 593 4,870 103.3
100
71 78 67 75 605 8,305 176.1
75 88 73 86 672 7,800 165.4
80
86 109 86 99 714 16,320 346.1
99 99 86 88 735 22,400 475.0 60
88 89 84 84 718 2,100 44.5
84 91 82 89 721 4,608 97.7 40
89 89 73 77 696 13,220 280.3
77 79 71 75 671 8,900 188.7 20
75 93 64 83 698 26,700 566.2
0
1 2 3 4 5 6 7 8 9 10 11 12 13
ulated to a representative
es fall within in the range of
be displayed on the second
to be displayed on the same
Stock Price
1200.0
1000.0
800.0 Volume
Hi/Lo
600.0
Market Index
400.0
200.0
0.0
13 14 15 16 17 18 19 20 21 22 23 24 25