Month Click on Topic

Jun-03 Conditional Format 2

May-03 Controls
Apr-03 Blanks
Mar-03 Protection
Feb-03 Format Text
Jan-03 Conditional Format
Dec-02 Look Up
Nov-02 Camera
Oct-02 Index
Sep-02 Text Functions
Aug-02 Subtotals
Jul-02 Unique Records
sheet 1
An updated version of this file, with a new Excel tip, is e-mailed each month
by CMA Spreadsheet Solutions.
If you'd like to receive a regular free copy, please contact us at colin@cma-
ss.biz or telephone 01438 212680
trwd 5430 573 166
dqrr 9145 428 485
rayr 4764 738 825
areq 7432 338 432
erqs 3418 833 4
yryy 6466 895 423
rtta 4558 657 912
awse 7283 73 63
ayrd 4046 703 50
ewry 3246 286 102
aawt 7725 647 41
sdta 8957 129 496
erqq 3412 335 213
eywt 3625 962 485
edrw 3942 365 969
tyrs 5648 449 212
wawa 2727 821 486
drss 9488 903 626
ayrd 7649 881 716
tdyw 5962 953 12
tsed 5840 667 182
wqqr 2115 131 743
wtaq 2571 913 531
ayqe 7614 777 288
rqes 4138 601 94
rsrw 4842 260 472
este 3854 689 140
edrd 3949 763 282
yqyy 6167 508 755
saqq 8711 458 464
qdaq 1971 157 334
deaq 9371 588 555
yyqt 6616 604 629
sdye 8963 21 30
CMA Spreadsheet Solutions
If you've got a large data table it's
often worth highlighting alternate
lines to make it more readable
1. Using the mouse, highlight the range c6
to h39. Or click anywhere in the table and
then click on <ctrl>, <shift> and <8>
simultaneously.
2. On the main menu bar, click on
<Format>, then <Conditional Formatting>.
Then change the drop down list to show
"Formula Is" in place of "Cell Value Is".
Then, in the long blank space, enter
=MOD(ROW( ),2)=0
3. Then click on the <Format> button, then
select the <Patterns> tab, then choose a
colour. Finally click <OK> twice.
4. As an alternative you could change the
font (click on the <Font> tab rather than
<Patterns>), or you could highlight every
third row by changing the number 2 in the
formula to a number 3
5. To remove the highlighting, click on
<Format> then <Conditional Formatting>
then <Delete>. Put a tick against <Condition
1> then click <OK> twice
250 212
825 77
891 512
93 899
948 261
149 789
189 345
853 976
966 366
312 865
854 278
695 680
259 842
206 476
871 731
31 975
923 732
773 559
30 54
367 923
11 284
901 755
318 316
808 418
738 611
749 32
218 896
348 362
743 630
345 683
163 844
32 196
479 948
58 827
CMA Spreadsheet Solutions
On this sheet we're looking at two
ways of controlling the data that
goes into a cell
1. The control to the right of this text box is a
"Combo Box". In this case it allows the user
to select any of the names that appear in the
list from cell k11 to k22 by clicking on the
downward-pointing arrowhead. The output
of the control is the position in the list that
the name occupies, and in this case the
output is placed in cell k8. In cell k9, we use
the "Index" function to turn this back into as
name.
2. The range of cell containing the list, and
the output cell, are both specified in the
following way. Right click on the control,
then click on <Format Control>. Click on the
<Control> tab, and you'll see that the input
range has been set as k11 to k22. The cell
link is where the output appears, in this case
cell k8
3. The control to the right of this text box is a
"Scroll Bar". Here it's being used to place a
particular number in cell k34. The range has
been set from 0 to 100 in steps of 5. Again,
right click on the control, then click on
<Format Control> and on the <Control> tab.
You'll see, among other things, the
maximum and minimum vales, and the
linked cell.
4. How do you add controls to a worksheet?
By clicking on the <Forms> toolbar, then
drawing the control on your worksheet.
If your <Forms> toolbar isn't visible, click on
<View>, then <Toolbars> then click on
<Forms>
8
Nothern Ireland
East
South West
North Scotland
North East
Central Scotland
South East
London
Nothern Ireland
Midlands
Yorkshire
Wales
North West
5
1-Jan-03
3-Jan-03
12-Jan-03
15-Jan-03
21-Jan-03
26-Jan-03
30-Jan-03
2-Feb-03
4-Feb-03
5-Feb-03
13-Feb-03
22-Feb-03
26-Feb-03
7-Mar-03
8-Mar-03
12-Mar-03
21-Mar-03
24-Mar-03
30-Mar-03
9-Apr-03
14-Apr-03
20-Apr-03
21-Apr-03
25-Apr-03
26-Apr-03
27-Apr-03
2-May-03
8-May-03
8-May-03
18-May-03
22-May-03
25-May-03
3-Jun-03
9-Jun-03
13-Jun-03
15-Jun-03
21-Jun-03
25-Jun-03
5-Jul-03
9-Jul-03
13-Jul-03
15-Jul-03
CMA Spreadsheet Solutions
On this sheet we're looking at ways of dealing with blank
cells. In one case, we just want to delete them
other case we want to copy down the value from the cell
immediately above the blank.
1. Column D contains a list of dates
some blank rows. To get rid of these, you
could highlight each in turn and delete them.
But this takes time with a long list!
An alternative is to highlight the entire range
(from D5 to D117) . Then click on <Edit>,
<Go To>, <Special...>, <Blanks> and <OK>.

2. Then, click on <Edit>, <Delete>, <Shift
cells up> and <OK>. The blanks have
disappeared.
3. Now click on the undo button
<Undo Delete> - to put the blanks back in.
The next step is to fill the blank cells with the
date immediately above them.
4. Highlight the entire range again, and click
on <Edit>, <Go To>, <Special>, <Blanks>,
and <OK>. Now click on the "Equals" key,
then the "Up arrow" key, and finally the "Ctrl"
and "Enter" keys together.
27-Jul-03
5-Aug-03
6-Aug-03
7-Aug-03
12-Aug-03
18-Aug-03
23-Aug-03
26-Aug-03
28-Aug-03
30-Aug-03
3-Sep-03
4-Sep-03
12-Sep-03
20-Sep-03
25-Sep-03
29-Sep-03
30-Sep-03
2-Oct-03
10-Oct-03
13-Oct-03
19-Oct-03
23-Oct-03
1-Nov-03
8-Nov-03
17-Nov-03
17-Nov-03
26-Nov-03
28-Nov-03
28-Nov-03
5-Dec-03
13-Dec-03
15-Dec-03
23-Dec-03
28-Dec-03
5-Jan-04
13-Jan-04
13-Jan-04
22-Jan-04
24-Jan-04
26-Jan-04
4-Feb-04
4-Feb-04
11-Feb-04
18-Feb-04
27-Feb-04
4-Mar-04
5-Mar-04
14-Mar-04
23-Mar-04
23-Mar-04
28-Mar-04
28-Mar-04
6-Apr-04
11-Apr-04
On this sheet we're looking at ways of dealing with blank
cells. In one case, we just want to delete them - in the
other case we want to copy down the value from the cell
. Column D contains a list of dates - with
some blank rows. To get rid of these, you
could highlight each in turn and delete them.
But this takes time with a long list!
An alternative is to highlight the entire range
(from D5 to D117) . Then click on <Edit>,
<Go To>, <Special...>, <Blanks> and <OK>.
. Then, click on <Edit>, <Delete>, <Shift
cells up> and <OK>. The blanks have
. Now click on the undo button - or <Edit>,
to put the blanks back in.
The next step is to fill the blank cells with the
date immediately above them.
. Highlight the entire range again, and click
on <Edit>, <Go To>, <Special>, <Blanks>,
and <OK>. Now click on the "Equals" key,
then the "Up arrow" key, and finally the "Ctrl"
and "Enter" keys together.
Region
Total
Sales m
Product
X Sales
m
X sales
as % of
total
East 4.7 2.8 59.6
South West 4.7 2.9 61.7
North Scotland 4.3 3 69.8
North East 6.5 5.2 80.0
Central Scotland 8.9 6.2 69.7
South East 9.8 6.7 68.4
London 15.6 10.8 69.2
Nothern Ireland 3.4 2.5 73.5
Midlands 9.6 6.3 65.6
Yorkshire 7.7 5.4 70.1
Wales 8.1 5.2 64.2
North West 9.1 5.9 64.8
CMA Spreadsheet Solutions
On this sheet we're looking at ways of
protecting cell entries from being over-
written - and of hiding formulae
1. A data table has been set up in the
range b10 to e22. Our objective is to
allow users to change the values in
the range d11 to d22 - but not any
other entries on this worksheet. We
also want to hide the formula that
appears in cells e11 to e22

2. Firstly, mark off the cells d11 to
d22 using the left mouse button.
Then, on the menu bar, click on
<Format>, <Cells>, <Protection> and
remove the tick from the "Locked"
box. Then click on <OK>
3. Secondly, mark off cells e11 to e22
and on the menu bar click on
<Format>, <Cells> and <Protection>.
Now click in the "Hidden" box so that
a tick appears
4. Finally, click on<Tools>,
<Protection> and <Protect Sheet>.
Don't bother to enter a password
then click on <OK>
5. You'll now find that you can't
change any of the entries on the
sheet - except the values in column
D. You'll also see the formulae in
column E aren't displayed on the
menu bar. To "unprotect", click on
<Tools>, <Protection>, <Unprotect
Sheet>
. A data table has been set up in the
range b10 to e22. Our objective is to
allow users to change the values in
but not any
other entries on this worksheet. We
also want to hide the formula that
appears in cells e11 to e22
. Firstly, mark off the cells d11 to
d22 using the left mouse button.
Then, on the menu bar, click on
<Format>, <Cells>, <Protection> and
remove the tick from the "Locked"

. Secondly, mark off cells e11 to e22
and on the menu bar click on
<Format>, <Cells> and <Protection>.
Now click in the "Hidden" box so that
. Finally, click on<Tools>,
<Protection> and <Protect Sheet>.
Don't bother to enter a password -
. You'll now find that you can't
change any of the entries on the
except the values in column
D. You'll also see the formulae in
column E aren't displayed on the
menu bar. To "unprotect", click on
<Tools>, <Protection>, <Unprotect
Too much text to fit
Eastenders
CMA Spreadsheet Solutions
On this sheet we're looking at
ways of formatting text within a
cell - and across several cells
1. A string of text has been entered in
cell E9. To get the text to wrap, click
on the cell then on
<Format>,<Cells>,<Alignment>, and
check <Wrap Text>
2. Alternatively, you can click on the
cell, then click in the text where it's
displayed on the formula bar. Position
the cursor to where you'd like a new line
to start, and click on <Alt> and <Enter>
3. To merge cells together, and
centre text within them, block off
the range of cells (for example
E16 to G16) using the left mouse
button. Then click on the "Merge
and Centre" button - a letter "a"
with two arrows - which should
be on your formatting toolbar.
4. You can also merge vertically. Undo previous
merging by clicking on the cell, then on <Format>,
<Cells>, <Alignment>, then uncheck the <Merge
Cells> box. Now block off the range E16 to E21 then
click on the "Merge and Centre" button. Now click on
<Format>,<Cells>,<Alignment> and change the
degree setting to 90. Finally ensure that both the
horizontal and vertical text alignment boxes are set
to "Center"
. Alternatively, you can click on the
cell, then click in the text where it's
displayed on the formula bar. Position
the cursor to where you'd like a new line
to start, and click on <Alt> and <Enter>
. You can also merge vertically. Undo previous
merging by clicking on the cell, then on <Format>,
<Cells>, <Alignment>, then uncheck the <Merge
Cells> box. Now block off the range E16 to E21 then
click on the "Merge and Centre" button. Now click on
<Format>,<Cells>,<Alignment> and change the
degree setting to 90. Finally ensure that both the
horizontal and vertical text alignment boxes are set
CMA Spreadsheet Solutions
Target: 70
Minimum: 65
Total
Sales m
Product X
Sales m
X sales as %
of total
East 4.7 2.8 59.1
South West 4.7 2.9 62.4
North Scotland 4.3 2.9 67.6
North East 6.5 5.2 79.9
Central Scotland 8.9 6.2 70.1
South East 9.8 6.7 68.3
London 15.6 10.8 68.8
Nothern Ireland 3.4 2.5 74.1
Midlands 9.6 6.3 66.3
Yorkshire 7.7 5.4 69.5
Wales 8.1 5.2 64.7
North West 9.1 5.9 64.9
This table shows some sales
figures by region
1. In cells B2 and B3, we've entered
a target for the "X sales
percentage", and a minimum
acceptable figure
2. The range D7 to D19 is
"conditionally formatted" by clicking
on Format then Conditional
Formatting on the menu bar.
Values that are above the target
are shown in a bold blue font; those
below the minimum have pink
shading. Changing the values in
cells B2 and B3 instantly affects the
formatting of the "X sales
percentage"
1. In cells B2 and B3, we've entered
percentage", and a minimum
2. The range D7 to D19 is
"conditionally formatted" by clicking
Conditional
on the menu bar.
Values that are above the target
are shown in a bold blue font; those
below the minimum have pink
shading. Changing the values in
cells B2 and B3 instantly affects the
formatting of the "X sales
CMA Spreadsheet Solutions
Total
Sales m
Product
X Sales
m
X sales as
% of total Region
Conversion
Factor
East 4.7 2.8 59.1 London 68.8
South West 4.7 2.9 62.4
North Scotland 4.3 2.9 67.6
North East 6.5 5.2 79.9
Central Scotland 8.9 6.2 70.1
South East 9.8 6.7 68.3
London 15.6 10.8 68.8
Nothern Ireland 3.4 2.5 74.1
Midlands 9.6 6.3 66.3
Yorkshire 7.7 5.4 69.5
Wales 8.1 5.2 64.7
North West 9.1 5.9 64.9
1. In this cell, enter the name
of one of the regions from
column A
2. The Vlookup function
has been entered in this
cell. This looks up the
text that's been entered
in cell F7 within the
range A7 to D19. It then
chooses the value in the
fourth column of this
range on the same row
This table shows some sales
figures by region
CMA Spreadsheet Solutions
Total
Sales m
Product
X Sales
m
X sales as
% of total
East 4.7 2.8 59.1
South West 4.7 2.9 62.4
North Scotland 4.3 2.9 67.6
North East 6.5 5.2 79.9
Central Scotland 8.9 6.2 70.1
South East 9.8 6.7 68.3
London 15.6 10.8 68.8
Nothern Ireland 3.4 2.5 74.1
Midlands 9.6 6.3 66.3
Yorkshire 7.7 5.4 69.5
Wales 8.1 5.2 64.7
North West 9.1 5.9 64.9
This worksheet contains a section
of the "Look Up" worksheet which
has been placed here by using the
camera tool. If the values in this
section on the "LookUp" sheet are
changed - they also change here.
1. You can add the camera icon
to a toolbar by clicking on View
- Toolbars - Customize, then
choosing the Command tab.
Scroll down in the Categories
window and click on "Tools".
Then in the right hand window,
scroll down and click on the
Camera icon. Using the left
mouse button, drag this icon on
to a toolbar.
2. Now go to one of the other
worksheets in this file, mark off a
range then click on the Camera
icon that you've just installed.
Then go to any other worksheet,
and draw a box using the left
mouse button.
Total
Sales m
Product
X Sales
m
X sales as
% of total
East 4.7 2.8 59.1
South West 4.7 2.9 62.4
North Scotland 4.3 2.9 67.6
North East 6.5 5.2 79.9
Central Scotland 8.9 6.2 70.1
South East 9.8 6.7 68.3
London 15.6 10.8 68.8
Nothern Ireland 3.4 2.5 74.1
Midlands 9.6 6.3 66.3
Yorkshire 7.7 5.4 69.5
Wales 8.1 5.2 64.7
North West 9.1 5.9 64.9
CMA Spreadsheet Solutions
East 6 South East South East
South West
North Scotland
North East
Central Scotland
South East
London Midlands 9
Nothern Ireland
Midlands
Yorkshire
Wales
North West
This sheet shows how the Index
and Match functions are used -
and also an example of a named
1. The Index formula is
contained in cell C7. In this
case, the formula uses the
value in cell B7 and then
looks up the corresponding
value in a range. So if, for
example, this cell contains
the value 4, the result of the
formula is the 4th value in
the specified range (in this
2. The range A7 to A19 has
been named "Regions"
using Insert/Name/Define on
the menu bar. The range
name can now be used in
the formula
3. The Match function works the other way
round. Here we enter a value from a list (in
cell D13), and the function shows its
position in the list. The zero entered at the
end of the formula means that it will look
for the first exact match
The range A7 to A19 has
been named "Regions"
using Insert/Name/Define on
the menu bar. The range
name can now be used in

CMA Spreadsheet Solutions
Text Formula Result
London Tonight =LEFT(A8,8) London T
Carlton Weather =RIGHT(A10,4) ther
Travel News =MID(A12,5,4) el N
ITV Evening News =LEN(A15) 16
Weather =FIND("t",A17) 4
Emmerdale =A19&" "&A17 Emmerdale Weather
coronation street =PROPER(A21) Coronation Street
This sheet looks at seven of Excel's
text functions
1. The eight leftmost characters (inc spaces) in
cell A8
2. The four rightmost characters (inc spaces)
in cell A10
3. The middle four characters (inc spaces) in
cell A12, starting in position five
4. The length of cell A15
5. The position of the letter "t" in cell A17
6. The combination (concatenation) of cells
A19 and A17- with a space between them
7. Capitalizing the first letter of each word
The eight leftmost characters (inc spaces) in
The four rightmost characters (inc spaces)
The middle four characters (inc spaces) in
cell A12, starting in position five
The length of cell A15
The position of the letter "t" in cell A17
The combination (concatenation) of cells
with a space between them
. Capitalizing the first letter of each word
CMA Spreadsheet Solutions
Customer Manufacturer Price Rating
1 Apple 1461 3
2 Apple 538 5
3 IBM 1293 4
4 Apple 900 3
5 Apple 1181 4
6 Apple 1348 4
7 Dell 889 4
8 Compaq 649 1
9 Dell 1132 3
10 Dell 613 1
11 IBM 585 2
12 IBM 700 4
13 IBM 713 5
14 Apple 512 5
15 Apple 1144 4
16 Apple 777 5
17 IBM 1432 2
18 IBM 599 3
19 IBM 735 1
20 IBM 1295 5
21 Apple 616 5
22 Compaq 736 5
23 Dell 1233 2
24 Apple 1098 4
25 Apple 1496 2
26 Dell 1193 2
27 Dell 614 1
28 Compaq 1222 3
29 Compaq 1009 4
30 Dell 1163 4
31 Dell 1163 3
32 IBM 1228 3
33 Apple 1233 1
34 Compaq 1452 1
35 Apple 925 1
36 Apple 1168 3
37 IBM 1426 3
38 Apple 1227 2
39 Compaq 649 5
40 Apple 1472 4
41 Compaq 1271 3
42 Dell 562 4
43 IBM 1220 3
44 Compaq 658 3
45 IBM 1228 1
46 Apple 881 1
47 Compaq 1199 3
48 Apple 1074 3
This sheet uses some of the database tools
with in Excel. The table to the left lists the
make of computer bought by 50 customers
along with the price they paid and the rating
they gave it. All the figures are completely
fictitious!
1. Our task is to calculate the total amount of
money spent on each make (IBM, Apple,
Compaq and Dell) and the average rating
given to each of them. Firstly, we sort the list
by clicking an any of the cells in the range, for
example B12, then click on <Data>, <Sort>
and choose "Manufacturer" in the first box.
Then click on OK
2. Now, with the cursor still on one of the cell
sin the table, click on <Data>, <Subtotals>,
then at each change in Manufacturer
function Sum and add subtotal to Price
Then click on OK. To remove the subtotals,
click on <Data>,<Subtotals>, <Remove All>
3. Now repeat stage 2, but this time use
function Average and add subtotal to
By clicking on the numbers 1 or 2 that are
located near cell A1, you can show just the
totals
This sheet uses some of the database tools
with in Excel. The table to the left lists the
make of computer bought by 50 customers
along with the price they paid and the rating
they gave it. All the figures are completely
Our task is to calculate the total amount of
money spent on each make (IBM, Apple,
Compaq and Dell) and the average rating
given to each of them. Firstly, we sort the list
by clicking an any of the cells in the range, for
example B12, then click on <Data>, <Sort>
and choose "Manufacturer" in the first box.
Now, with the cursor still on one of the cell
sin the table, click on <Data>, <Subtotals>,
Manufacturer use
and add subtotal to Price .
Then click on OK. To remove the subtotals,
click on <Data>,<Subtotals>, <Remove All>
Now repeat stage 2, but this time use
and add subtotal to Rating.
By clicking on the numbers 1 or 2 that are
located near cell A1, you can show just the
CMA Spreadsheet Solutions
Manufacturer
Apple
Apple
IBM
Apple
Apple
Apple
Dell
Compaq
Dell
Dell
IBM
IBM
IBM
Apple
Apple
Apple
IBM
IBM
IBM
IBM
Apple
Compaq
Dell
Apple
Apple
Dell
Dell
Compaq
Compaq
Dell
Dell
IBM
Apple
Compaq
Apple
Apple
IBM
Apple
Compaq
Apple
Compaq
Dell
IBM
Compaq
IBM
Apple
Compaq
Apple
Quite often, you'll want to identify the just the
different entries in a long list. In column A
we've got a list of computer manufacturers
with each company name appearing several
times. To create a simplified list, with each
company just appearing once, go through
the following steps.......
1. Click on <Data> in the main menu, then on
<Filter> then on <Advanced Filter>. Click on
<Copy to another location> and in the <List
Range> box enter A3:A51
In the <Copy To> box, enter B3, then select
<Unique records only> and click on <OK>
2. You should now see just the four company
names appearing in the range B4:B7