(navigation image)
Home American Libraries | Canadian Libraries | Universal Library | Community Texts | Project Gutenberg | Children's Library | Biodiversity Heritage Library | Additional Collections
Search: Advanced Search
Anonymous User (login or join us)
Upload
See other formats

Full text of "Microsoft Excel 2003 Tips'N Tricks"

Datasheet 

Copyright 2008 Prophet Technologies Pty Ltd 
ISBN 978-0-9804922-0-0 

This work is licenced under the Creative Commons Attribution-Noncommercial-Share 

Alike 2.5 Australia License. To view a copy of this licence, visit 

http://creativecommons.Org/licenses/by-nc-sa/2.5/au/ 

or, (b) send a letter to Creative Commons, 1 71 2nd Street, Suite 300, San Francisco, 

California, 941 05, USA. Please contact the publisher if you wish to request alternative 

licence arrangements. 

To contact the author send email to support@msexcel2003.com or write to 
PC Box 70, St Leonards, NSW 1590 Australia 



Trademarks 

All brand names, product names and registered trademarks are owned by their 
respective holders. 

Tricks'n Tips is a trademark licensed to Prophet Technologies Pty Ltd. Microsoft and 
Microsoft Excel 2003 are either registered trademarks or trandemarks of the Microsoft 
Corporation. Adobe Acrobat Reader are trademarks of Adobe Systems 
Incorporated. 

References to companies and products in this document are for the purpose of 
illustration not endorsement. This work and the author are not affiliated or endorsed 
by the Microsoft Corporation. 

Notice of Liability 

The information in this work is distributed on an 'As-ls" basis, without warranty. While 
precaution has been taken in the preparation of this work, the author shall have no 
liability to any person or entity with respect to any loss or damage caused or alleged 
to have caused directly or indirectly by the instructions contained in this work. 

Use of monetary figures in this work is for illustrative purposes only and does not 
represent financial advice in any way. Names in this work are purely fictional and 
used for illustrative purposes only. Descriptions of betting and gambling are for 
illustrative purposes and should not be relied upon nor do they represent an 
endorsement of gambling. 

All examples provided in this work are complete fiction. 

All formulas and calculations described in this work can not be relied on. 



Viewing Suggestion 

This electronic document has been designed for viewing directly with Adobe 
Acrobat Reader or printing to an ISO A5 size booklet. 

Cover Photo Amatuer photo taken at the site of the The Twelve Apostles 
located along the Great Ocean Road in Australia. 



> 





LU 





«/} 




> 









How do I create a series of numbers? 



Automatic Series Completion 

There are many times when you want to create a series of numbers or dates 
across a row or column. You can create a series manually by typing 1, 2, 3, ... into 
their respective cells or by using a formula such as C4+1, C5 + 1, etc. 

Microsoft Excel provides a convenient shortcut to creating a series, simply by 
starting the first and second cells in the series, selecting the two cells and then 
dragging selection area out to the required set of cells. 

Scenario - Creating a series of years 

Consider a cash flow table that summarises the income and expenses over a 
series of years as shown below. 





A B 


C 


D 


E F G 




1 

2 


Example - Yearly Cashfl 


ow Table 




3 


Yearly Cashflow ' ' 




1 1 




4 
5 


Income $ 45,000 


$ 45,000 


$ 45,000 


$ 45,000 ^ $ 45,000 $ 45,000 




Expenses $ 38,299 


$ 39,234 


$ 40,134 


$ 42,999 1 $ 43,087 $ 44,933 




6 


Net $ 6701 $ 5766 $ 4,866 $ 2,001 $ 1,913 $ 67 




7 













Step 1: Enter the first two years of the series 

To create a series we need to tell Excel what the series is. In our example Cell B3 
is "01/01/2000" and C3 is "01/01/2001" as shown below. Note that the cell 
formatting in this example is set so that only the year is shown. 





A 


B 


C D E F G 


1 

2 
3 


Example -Yearly Cashfl 


OW Table 


Yearly Cashflow 


2000 


2001 1 1 1 1 


4 
5 


Income 


$ 45,000 


$ 45,000 $ 45,000 $ 45,000 $ 45,000 $ 45,000 


Expenses 


$ 38,299 


$ 39,234 $ 40,134 ' $ 42,999 ' $ 43,087 $ 44,933 


6 


Net $ 6,701 $ 5,766 $ 4,866 $ 2,001 $ 1,913 $ 67 ' 



ricro&off Excel 2003 Tricks'n T«ps 



Step 2: Select the cells that show first and second elements of the series 

Click-and-drag with the mouse to select cells B3 and C3. 





A 


B i C J D E F G 


1 

2 
3 


Example -Yearly Cashflow Table 


Y^iiily Cashflow 


2000 2001 1 


4 


Income 


$ 45,000 1 $ 45,000 | $ 45,000 | $ 45,000 $ 45,000 j $ 45,000 


5 


Expenses $ 38,299 $ 39,234 $ 40J34 $ 42,999 ' $ 43,087 ' $ 44,933 


6 


Net $ 6,701 $ 5,766 | $ 4,866 | $ 2,001 $ 1,913 $ 67 



Step 3: Click-and-drag the bottom-left corner of the cell and drag to 
complete the series 

Use the mouse to click-and-hold the bottom-left corner of cell C3. 

The drag the selection out until G3. The screen should look similar to below. 



B 



D 



Example - Yearly Cashflow Table 






Yejily Cashflow | 2 | 2001 _ 

Income $ 45iiiiu | $ 45,0007$ ■^5,000 $ 45,000 1$ 45,000 j J^ogsJ"-"-^ 

Expenses $ io,2yd $ 39,234 $ 40,134 $ 42,999 $ 43,087 $ 44,933 



Net 



$ 6,701 $ 5,766 I $ 4,866 | $ 2,001 $ 1,913 $ 



67 



Once the series region is selected then release the mouse button. 

Finish: Excel will automatically complete the series for the selection. 





A B 


C D E F G 


1 

2 
3 


Example -Yearly Cashfl 


ow Table 


Yearly Cashflow 2000 2001 2002 ' 2003 2004 2005 


4 


Income $ 45,000 | $ 45,000 | $ 45,000 $ 45,000 | $ 45,000 | $ 45,000 


5 


Expenses $ 38,299 $ 39,234 $ 40,134 $ 42,999 $ 43,087 $ 44,933 


6 


Net $ 6,701 $ 5,766 , $ 4,866 $ 2,001 $ 1,913 $ 67 



How do I see all the formulas on a worksheet? 



View Formulae In Cells 

Sometimes it is handy to view (and print) the formulae in all the cells in a 
spreadsheet without having to manually enter each cell. Being able to view all 
the formulae makes it easier to debug our own spreadsheets as well as making it 
simpler to understand other people's spreadsheets. 

Scenario - Viewing the formulae in a table. 

You may have a table that contains a list of names (Column A), their net wealth 
(Column B) and the number of years they have been working (Column C). Behind 
the scenes the spreadsheet calculates the amount of money each person made 
(Column D) and the average amount made across our sample selection (Cell D19). 





A 


B 


C 


D 




1 


Example - How to View Formulas in All Cells 




2 












3 


Name 


Net 
Worth 


Years 
Worhed 


Made Per 
Year 




4 

5 

6 

7 

8 

9 

10 

11 


Person 1 


$191.2M 


18 


$ 10.62 M/yr 




Person 2 


$179.4M 


25 


$7.17 M/yr 




Person 3 


$171.3M 


33 


$5.19 M/^r 




Person 4 


$138.7M 


27 


$5.14 M/iT 




Person 5 


$143.3M 


38 


$3.77M/yr 




Person 6 


$181.7M 


15 


$12.11 M/yr 




Person 7 


$113.5M 


45 


$2.52M/^r 




Person 8 


$157.3M 


38 


$4.14 M/iT 




12 


Person 9 


$173.3M 


14 


$ 12.38 M/yr 




13 


Person 10 


$176.6M 


28 


$6.31 M/yr 




14 


Person 11 


$119.1M 


43 


$2.77M/^r 




15 
16 
17 
IS 


Person 12 


$199.4M 


41 


$4.86^^/1^ 




Person 13 


$192.8M 


36 


$5.35M/yr 




Person 14 


$155.1 M 


36 


$4.31 M/yr 














19 




Average Amount = 
1 


$6.19 M/iT 


— 



ricro&off Excel 2003 Tricks'n T«ps 



Step 1: Press the Ctrl and ~ keys on the keyboard at the same time to 
view. 

Pressing the Ctrl and ~ keys shows the fornnulae in every cell on the worksheet. 
You are now able to view the underlying logic of this spreadsheet. 





A 


B 


C 


D 1 


1 


Example -How to Vie 




2 










3 


Name 


Net Worth 


Years Worhed 


Made Per Year 


4 


Person 1 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) =B4/C4 


5 


Person 2 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) =B5/C5 


6 


Person 3 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) =B6/C6 


7 


Person 4 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) =B7/C7 


8 


Person 5 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) =B80 


9 


Person 6 


=(RAND0*1OO-h1OO) 


=INT(RANDO*40-h10) =B9/C9 


10 


Person 7 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) =B10/C10 


11 


Person 8 


=(RAMD0*1OO+1OO) 


=INT(RANDO*40+10) 


=B11/C11 


12 


Person 9 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) 


=B12/C12 


13 


Person 10 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) 


=B13/C13 


14 


Person 11 


=(RAND0*1OO-h1OO) 


=INT(RANDO*40-h10) 


=B14/C14 


15 


Person 12 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) 


=B15/C15 


16 


Person 13 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) 


=B16/C16 


17 


Person 14 


=(RAND0*1OO+1OO) 


=INT(RANDO*40+10) 


=B17/C17 


IS 










19 






Average Amount = 


=AVERAGE(D4:D17) 



Helping Note: In this current view you can print this page out to your printer. 
Step 2: Press the Ctrl and ~ keys to return to the normal view. 



How do I highlight a cell based on special conditions? 

Conditional Formatting 

There are many occasions where you nnay want to have Excel change the 
fornnatting of a cell and its contents depending on the value in the cell. This could 
be handy to highlight sections of your spreadsheet based on different scenarios. 

Scenario - Bank Account Balance 

You nnay have a cell that contains the total balance of all your bank accounts and 
it gets highlighted in yellow if the balance falls below $0. 

The figure below shows the spreadsheet with a "Net Value" > $0.00. The 
fornnatting of this cell is in its nornnal fornnat. 





A 


B 


C 


D 


E 


1 


Example - Conditional Formatting 


2 
3 










Banh AccDunis 








4 


Savings Account 1 


$2,100 






5 


Savings Account2 


$1,500 






6 




Credit Card 1 


$0 






7 




Credit Card 2 


-$200 






8 




Credit Card 3 


-$1,800 






9 




Net Value = 


$1,600 






10 













For financial security we want Excel to highlight the "Net Value" (cell C9) when 
the net value amount drops below $0.00. 

Step 1: Select the cell we want to apply conditional formatting to 





A 


B ' C J 


D 


E J 


1 


Example - Conditional Formatting 


2 










3 


Banh AccDunis 








4 




Savings Account 1 


$2,100 






5 




Savings Account2 


$1,500 






6 




Credit Card 1 


$0 






7 




Credit Card 2 


-$200 






8 




Credit Card 3 


-$1,800 






9 




Net Valuer 


$1,600 






10 




1 1 





ricro&off Excel 2003 Tricks'n T«ps 



Format Tools Data Window 



']j^ Cells... 
Row 
Column 
Sheet 



Ctrl+1 



I 



AutoFormat. 



Conditional Formatting. 



Style. 



Step 2: Select the Conditional 
Formatting menu item as shown right 



Step 3: Tell Excel what the special formatting conditions are 



Conditional Formatting 



Add >> Delete... [ 



OK 



' Condition i 












1 




Cell Value Is {^ 




less than 


;^ 




^ 




















Preview of format to use 
when condition is true: 


No Format Set 










Format,., 










.^ 















J Cancel 



Step 4: Press the 
Format button to 
set the format of 
the cell when this 
condition is met 



Format Cells 



OK 



? X 



Font II Border Patterns 


Cell shading 
Color: 


I 






No Color 












■DDHHHHI 


) 


^ -n-i-i- 


■■DDHHHE 






Pattern; 


J 














Clear 











Cancel 



step 5: Press OK to accept the conditions and conditional cell formatting 

In the Format Cells dialog box, press the OK button to accept the conditional cell 
fornnat. 

In the Conditional Fornnatting dialog box, press the OK button apply the 
conditions. 

Let us assunne that we went out shopping that day and spent $2,000 using Credit 
Card 1 and updated cell C6 to reflect this change. As you can see, the Net Value 
has dropped below $0 and the cell autonnatically highlights this. 





A 


B 


C 


D 


E 


1 


Example - Conditional Formatting 


2 










3 


Banh AccDunis 








4 


Savings Account 1 


$2,100 






5 
6 




Savings Account2 


$1,500 






Credit Card 1 


-$2,000 






7 


Credit Card 2 


-$200 






8 




Credit Card 3 


-$1,800 






9 
10 




Net Value = 


-$400 

















riicrosoft Excel 2003 Trick&'n Tips 



How do I create an IF formula? 



IF Statements 

Normally we calculate the value of a cell based on the value in another cell, for 
exannple nnultiply a value by 4 and put that answer in another cell. Occasionally 
we want to calculate the value of a cell only if a certain criteria is nnet. 

Scenario - Applying an automatic discount on an invoice. 

In this exannple we want to create an invoice that autonnatically applies a 10% 
discount if the original value of the order exceeds $100. 

The figure below shows a sannple invoice spreadsheet. Each line (Lines 4, 5, 6 
and 7) is individually tallied and then subtotalled in Cell E9. 





A 


B 


C D 


E 


1 

2 
3 


ExampI 


e - Automatic Invoice Discount 


System 


Order Des 


cription 


Unii Price Ctjauntily 


Cost 


4 


Item1 


Ball Point Pens 


$0.35 100 


$35.00 


5 


Item 2 


Whiteboard markers 


$2.95 2 


$5.90 


6 
7 
S 


Items 


Executive Pens 


$9.95 5 


$49.75 


Item 4 


Executive Gift Sets 


$35.95 5 


$179.75 




9 






Subtotal = 


$270.40 


10 

•u. 






Discount^ 






Totals 

t 


$270.40 



Step 1: Select 
the cell that 
will contain 
the conditional 
calculation (IF 
statement). 

Click on the ElO 
cell. 





A 


B 


C D 


E ■ 


1 


ExampI 


e -Automatic Invoice Discount ! 


System 


2 
3 


Order Description 


Unit Price Q^untitj^ 


Cost 


4 


Item1 


Ball Point Pens 


$0.35 100 


$35.00 


5 


Item 2 


Whiteboard markers 


$2.95 2 


$5.90 


6 


Item 3 


Executive Pens 


$9.95 5 


$49.75 


7 


Item 4 


Executive Gift Sets 


$35.95 5 


$179.75 


8 




9 






Subtotal = 


$270.40 


10 






Discount=f 


1 


11 






Total = 


$270.40 



8 



step 2: Select the Paste Function Menu item 
as shown right 



Insert Format Tools Dc 



Cells... 

Rows 

Columns 

Worksheet 

Chart... 

Symbol... 



Page Break 



jG: Function. 



1 



Name 
Comment 



Picture 
Diagram... 
Object... 
Hyperlink. , 



Ctrl+K 



Step 3: Select the IF statement from the Paste Function selection box 



Insert Function 



Search for a function: 



Type a brief description of what you want to do and then 
click Go 



Or select a category: Logical 
Select a function: 



^ 



Go 



AND 
FALSE 



NOT 

OR 

TRUE 



IF(logical_testpValueJf_truepValueJf_false) 

Checks whether a condition is metj and returns one value if TRUE^ and 
another value if FALSE. 



Help on this function 



OK 



Cancel 



In the Paste Function dialog box, select "Logical" fronn the Function Category and 
"IF" fronn the Function Nanne then press the OK button. 



ricro&off Excel 2003 Tricks'n T«ps 



step 4: Enter the conditional formula into the Paste Function helper 



A 



B 



D 



Example -Automatic Invoice Discount System 



Order Description 



Unit Price Qauntity 



Cost 



Item1 Ball Point Pens 



$0.35 100 



$35.00 



Item 2 Whiteboard markers $2.95 2 $5.90 

Items Executive Pens $9.95 5 $49.75 

Item 4 Executive Gilt Sete $35.95 5 $179.75 



11 



Subtotal $270.40 



Discount^ 



%*Ea0) 



Totals $270.40 



12 



Function Arguments 



I 



IF 




LDgical_test 

Valuejfjrue 
Valuejfjalse 


E9>100 \^ = TRUE 


-10%*E9 ^=-27.04 


^=0 







= -27.04 
Checks whether a condition is met^ and returns one value if TRUEj and another value if 
FALSE. 



Value_if_false is the value that is returned if Logical_test is FALSE. If omitted^ FALSE is 
returned. 



Formula result = 
Help on this function 



-$27.04 



OK 



Cancel 



Helping Note: You can nnove the helper box around the screen by click-and- 
dragging anywhere in the grey area of the box. 

The "Logical_test" field deternnines what special conditions we want to apply. In 
this case, the special condition is when the subtotal field (Cell E9) is greater than 
$100.00 (100). 



10 



In the circumstances where the Logical_test is "True" then the "Value_if_true" 
field is applied. In this case the subtotal field (E9) exceeds $100.00 (E9=$270.40) 
so the Logical_test is true and we want to apply a discount of 10% to the subtotal 
annount (fornnula = -10% * E9). 

In the circunnstances where the Logical_test is "False" then the "Value_if_false" 
field is applied. In our fornnula we want a discount of $0.00 applied when the 
subtotal field (E9) is less than or equal to $100.00. 

Press the OK button when you are satisfied that the fornnula is correct. 

In our exannple the subtotal value is $270.40, which exceeds the $100.00 
threshold so a 10% discount is applied. 





A 


B 


C D 


E . 


1 


ExampI 


e -Automatic Invoice Discount 


System 


2 
3 


Order Des 


cription 


1 
Unit Price Qauntity 


Cost' 


4 
5 
6 
7 
8 


Itemi 


Ball Point Pens 


$0.35 100 


$35.00 


Item 2 


Whiteboard mariners 


$2.95 2 


$5.90 


Items 


Executive Pens 


$9.95 5 


$49.75 


Item 4 


Executive Gift Sets 


$35.95 5 


$179.75 




9 






Subtotal = 


$270.40 


10 






Discount = 


-$27.04 


11 






Total = 


$243.36 



If for Sonne reason we decided not to order any Executive Gift Sets (D7=0) then 
the subtotal does not exceed the $100.00 threshold and no discount is applied. 





A 


B 


C D 


E 1 


1 

2 
3 


ExampI 


e -Automatic Ir 


■voice Discount 


System 


Order Des 


cription 


Unit Price Qauntity 


Cost 


4 
5 
6 


Itemi 


Ball Point Pens 


$0.35 100 


$35.00 


Item 2 


Whiteboard markers 


$2.95 2 


$5.90 


Items 


Executive Pens 


$9.95 5 


$49.75 


7 
8 


Item 4 


Executive Gift Sets 


$35.95 


$0.00 




9 






Subtotal = 


$90.65 


10 






Discount = 


$0.00 


11 






Total = 

1 


$90.65 



11 



riicrosoft Excel 2003 Trick&'n Tips 



How do I count the cells that only meet my criteria? 

COUNTIF Statements 

Occasionally you might have a list of items and you want to count how many 
times certain criterion is met. For example you might want to count how many 
David's there are in a list of Christian names or count how many purchases 
exceeded $100.00. 

Scenario - Count how many "fails'' occurred in a list of pass/fail 
exam results 

in this example we have a list of students and their pass/fail results of a recent 
exam as shown below and we want to count how many students passed the exam. 





A 


BCD 


1 


Example - H< 


ow many students passed? 


2 












3 


Student Name 


Exam Result 








4 


Maty 


FAIL 








5 


Patricia 


PASS 








6 


Linda 


PASS 








7 


Barbara 


FAIL 








8 


Elizabeth 


FAIL 








9 


Jennifer 


FAIL 








10 


Maria 


PASS 








11 


Susan 


PASS 








12 


Margaret 


PASS 








13 


Dorothy 


FAIL 








14 












15 


Number of PASS 










16 


Number of FAIL 











12 



step 1: Select the cell we want Excel to return the number of PASS 
marks in. 

Use the mouse to click on Cell B15 to select the cell. 





A 


B 


C 


D 




1 


Example - How many students passed? 


2 
3 












Student Name Exam Result 








4 

5 

6 

7 

8 

9 

10 

11 

12 

13 

14 

15 


Mary 


FAIL 








Patricia 


PASS 








Linda 


PASS 








Barbara 


FAIL 








Elizabeth 


FAIL 








Jennifer 


FAIL 








Maria 


PASS 








Susan 


PASS 








Margaret 


PASS 








Dorotiiy 


FAIL 


















Number of PASS 










16 


Number of FAIL 











Step 2: Select the Paste Function 
menu item as shown right 



Insert Format Tools Dc 




Name 
2 Comment 



Picture 



Object... 
^ Hyperlink. 



Ctrl+K 



13 



ricro&off Excel 2003 Tricks'n T«ps 



Step 3: Select the COUNTIF statement from the Paste Function selection 
box. 

In the Paste Function dialog box, select "Statistical" fronn the Function Category 
and "COUNTIF" fronn the Function Nanne then press the OK button. 



Insert Function 



Search for a function: 



Type a brief description of what you want to do and then 
click Go 



Or select a category: 
Select a function: 



Statistical 



j^ 



mm 



Go 



CORREL 
COUNT 
COUNTA 
COU NTBLANK 

COVAR 
CRITBINOM 



s 



COUINTIF(rangepCriteria) 

Counts the number of cells within a range that meet the given condition. 



IHelp on this function 



OK 



Cancel 



14 



step 4: Enter the required information into the Paste Function helper 





A 


B 


C 


D 


E 


F 


c 


1 


Example - How many students passed? 








2 
















3 


Student Name 


Exam Result 












4 
5 


Mary 


FAIL 












Patricia 


PASS 












6 

7 


Linda 


PASS 












Barbara 


FAIL 












S 


Elizabeth 


FAIL 












9 


Jenniter 


FAIL 












10 


IVlaria 


PASS 












11 
12 
13 


Susan 


PASS 












IVlargaret 


PASS 












Dorotliy 


FAIL 












14 
15 
















Number of PASS 


13;PASS") 












16 


Number of FAIL 














17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
















Function Arguments X 


t^r^i iMTTr 1 


LUUN 1 ir 

Range 
Criteria 






B4;B13 ^=-("FAIL' 


j"PA5S";"PAS 


■■PASS"! ^="PA55" 






J 


= 5 
Counts the number of cells within a range that meet the given condition, 

Criteria is the condition in the form of a numberj expression^ or text that defines 
which cells will be counted. 


Formula result = 5 
Help on this function 




OK Cancel 













Helping Note : You can nnove the helper box around the screen by click-and- 
dragging anywhere in the grey area of the box. 

The "Range" field deternnines the range of cells Excel will look to perfornn the 
count in. We can either use the nnouse to select the range or nnanually type the 
cell references directly into the text box. In our exannple the cell range starts at 
B4 and goes down to B13. 



15 



ricro&off Excel 2003 Tricks'n T«ps 



Helping Note : Cell ranges can be a selection of cells, a colunnn, row or box of 
cells, or a pre-defined nanned range. Cell references can be of any type including 
"relative" (eg B4), "absolute" (eg $B$4), nnixed reference (eg B$4), and nanned. 

The "Criteria" is a conditional statennent that is sinnilar to the conditional 
statennent in the IF statennent. In our case we want to count the nunnber of cells 
with "PASS" in the identified range. Rennennber that text fields are a special sort 
of field and need to start and end with inverted connnnas. 

Press the OK button when you are satisfied that the fornnula is correct. 

Step 5: Repeat step 4 to count the number of fails. 

Select Cell 816. 

Use the Paste Fornnula function and select "Statistical" fronn the function category 
list and "COUNTIF" from the function list. 

Follow Step 4 above nnaking the Range = B4:B13 and the Criteria = "FAIL'. 

Press the OK button when you are satisfied that the fornnula is correct. 



15 
16 


Number of PASS 


5 










Number of FAIL 


313;FAIL") 










17 


1 











Function Arguments 



COUNTIF 


) 


Range 
Criteria 


B4:B13 


^]= {"FAIL"; "PASS"; "PAS 




"FAIL" 53= "FAIL" 







= 5 
Counts the number of cells within a range that meet the given condition. 



Criteria is the condition in the Porm oP a number^ expression^ or text that defines 
which cells will be counted, 



Formula result = 5 

IHelp on this function 



OK 



Cancel 



16 



Finish: The spreadsheet now counts the number of passes and fails from 
the list. 

The spreadsheet will now look similar to the figure below. 





A 


B 


C 


D 


1 


Example - How many students passed? 


2 










3 


Student Name 


Exam Result 






4 


Mary 


FAIL 






5 


Patricia 


PASS 






6 


Linda 


PASS 






7 


Barbara 


FAIL 






8 


Elizabeth 


FAIL 






9 


Jennifer 


FAIL 






10 


Maria 


PASS 






11 


Susan 


PASS 






12 
13 
14 


Margaret 


PASS 






Dorotliy 


FAIL 














15 


Number of PASS 


5 






16 


Number of FAIL 


5 







17 



riicrosoft Excel 2003 Trick&'n Tips 



How do I sum a set of numbers that meet mv criteria? 

SUMIF Statements 

SUMIF is a function that allows you to add together values fronn a list if a 
nonninated criteria is nnet. For exannple, you nnight want to total the annount of 
hours worked by full-tinne ennployees out of a general list containing full-tinne, 
part-tinne and casual ennployees. 

Scenario - Calculate the winnings from a list of sports bets. 

In this exannple we have a spreadsheet that lists the bets placed on who will win 
various tennis nnatches on the weekend (Colunnn A), the annount bet on that 
nnatch (Colunnn B), the odds of winning (Colunnn C), and the potential winnings 
(Colunnn D). At the end of the weekend we will enter the results of each nnatch 
and we want Excel to autonnatically calculate how nnuch our winnings are. 





A 


B 


C 


D E 


: F 


1 

2 


Example - Sports Betting 


Results 


3 


Player to Win 1 Amount 1 Betting 1 Potential 1 

Bet Odds Winnings 


1 Match 1 
Result 


4 


Player 1 $ 10 , 4:1 , $ 40 ^ 


Won 


5 


Player 2 


$ 20 


4:1 


$ 80 


Won 


6 


Player 3 


$ 10 


6:1 


$ 60 


Won 


7 


Player 4 


$ 25 


4:1 


$ 100 


Won 


8 


Player 5 


$ 10 


10:1 


$ 100 


Lost 


9 


Player 6 


$ 5 


8:1 


$ 40 


Lost 


10 
11 
12 


Player 7 


$ 20 


2:1 


$ 40 


Lost 


Player 3 


$ 15 


8:1 


$ 120 


Lost 












13 


Total Amount Bet = 




Total Amount Won = 





Step 1: Select the cell that will contain the winnings results 

Use the nnouse to click on Cell F13 to select the cell. 



18 



step 2: Select the Paste Function 
menu item as shown right 



Insert Format Tools D^ 



Cells... 
Rows 
Columns 
Worksheet 
[ffi^ Chart... 
Symbol... 



Page Break 



jfir Function. 



Name 

r~rirrinrifarih 



Step 3: Select the SUMIF statement from the Paste Function selection 
box. 

In the Paste Function dialog box, select "Math & Trig" fronn the Function Category 
and "SUMIF" fronn the Function Nanne then press the OK button. 



Insert Function 


3a 


Search Por a function: 








Type a brief description of what you want to do and then 
click Go 




Go 








Or select a category: 


Math S; Trig ^ 




Se 


;lect a function: 








SUBTOTAL A 
SUM ~ 


SUMPRODUCT 

SUMSQ 

SUMX2MV2 

SUM:^2PY2 .vj 




5UI^IF(rangepCriteriapSum_range) 

Adds the cells specified by a given condition or criteria. 


Help on this function 


OK 




Cancel 















19 



ricro&off Excel 2003 Tricks'n T«ps 



Step 4: Enter the required information into the Paste Function helper. 

Helping Note : You can nnove the helper box around the screen by click-and- 
dragging anywhere in the grey area of the box. 

The "Range" field deternnines the range of cells that Excel will exannine to assess 
the criteria. We can either use the nnouse to select the range or nnanually type the 
cell references directly into the text box. In our exannple the range starts at cell 
F4 and goes down to cell Fll. 

The "Criteria" is a conditional statennent that is sinnilar to the conditional 
statennent in the IF statennent. In our case we want to select all bets where the 
tennis player "Won" the nnatch. In our exannple type in "Won" into the Criteria text 
box. 

The "Sunn_range" 
field tells Excel 
which cells to add 
when the criteria is 
nnet for each cell in 
the range. To put it 
in another way, we 
want to add 
together the 
potential winnings 
(Colunnn D) for 
each match "Won" 
in Colunnn F. In our 
exannple we can 
either use the 
nnouse to select 
the range or 
nnanually type in 
D4:D11 into the 
Sunn_range text 
box. 



Press the OK 
button when you 
are satisfied that 
the fornnula is 
correct. 





A 


B 


C 


D [ 


E F 


G 


1 

2 


Example - Sports Betting Results 






3 


Player to Win 


Amount 
Bet 


Betting 
Odds 


Potential 1 
Winnings 

:T 4o' 


1 IVIatcli 
Result 




4 


Player 1 $ 10 


4:1 


Won 




5 
6 


Player 2 


$ 20 


4:1 


$ 80 


Won 




Player 3 


$ 10 


6:1 


$ 60 


Won 




7 
8 
9 
10 
11 
12 
13 


Player 4 


$ 25 


4:1 


$ 100 


Won 




Player 5 


$ 10 


10:1 


$ 100 


Lost 




Player 6 


$ 5 


8:1 


$ 40 


Lost 




Player 7 


$ 20 


2:1 


$ 40 


Lost 




Player 8 


$ 15 


8:1 


$ 120 


Lost 
















Total Amount Bet = 




Total Amount Won = 


= D4:D11) 












F u nctio n A rgume nts X 


i-\ 


MTI~ 




1 


dU 


mu 






1 




Range F4:F11 




^=-("Wc 


)n";" Won";" Won 




Criteria "Won" 




=k.]="WQr 


i" 




Sunn_range D4;Dll| 




5D=^40j 


30j60jlOO;100;' 












Adds the cells specified by a given condition 
Sum range are tlie actual cells to su 


or criteria, 
Tfi, If omittedj 


= 280 
the cells in range are used, 


Fornnula result = $ 
HelD on this function 


280 




OK Cancel ] 











20 



step 5: Complete the spreadsheet by summing the amount bet. 



Now that we know how much we have won on our better we want to connplete the 
spreadsheet by showing how nnuch nnoney we originally bet. 



Step 5a: Select 
the cell which we 
want to calculate 
the total betting 
amount 

Click on Cell B13 as 
shown below. 





A B C D E 


1 

2 
3 


Example - Sports Betting Results 


Player to Win 


Amount 1 Betting 1 Potential 1 
Bet Odds Winnings 


1 


4 


Player 1 $ 10 4:1 $ 40 , 




5 


Player 2 


$ 20 


4:1 


$ 80 




6 


Player 3 


$ 10 


6:1 


$ 60 




7 


Player 4 


$ 25 


4:1 


$ 100 




8 


Player 5 


$ 10 


10:1 


$ 100 




9 


Player 6 


$ 5 


8:1 


$ 40 




10 

11 


Player 7 


$ 20 


2:1 


$ 40 




Player B 


$ 15 


8:1 


$ 120 




12 
13 












Total Amount Bet = 




Total Amount Won = 

1 


J 



Step 5b: Click on the AutoSum button 



By clicking on the 
AutoSunn button you 
can get MS-Excel to 
autonnatically calculate 
the colunnn above it. 
Use this function with 
caution and be sure to 
check the range of cells 
it has autonnatically 
selected. Once you have 
confirnned that the 
range is accurate then 
press the Enter key to 
confirnn the fornnula. 





A 


B 


C 


D E 


1 

2 


Example - Sports Betting 


Results 


3 


Player to Win 


Amount 
Bet 


Betting ' Potential 
Odds Winnings 


4 
5 
6 
7 
8 


Playerl U 10| 4:1 , $ 40 


Player 2 


$ 20 


4:1 


$ SO 


Player 3 


$ 10 


6:1 


$ 60 


Player 4 \% 25 


4:1 


$ 100 


Player 5 


$ 10 


10:1 


$ 100 


9 


Player 6 


$ 5 


8:1 


$ 40 


10 


Player 7 ! $ 20 


2:1 


$ 40 


11 


Player 3 


$ 15 


8:1 


$ 120 


12 


i J 




13 


Total Amount Bet= =SUM(|Sg|3||gi Amount Won = 


14 




5UM(numberlj [number2]j ,.,) 



21 



ricro&off Excel 2003 Tricks'n T«ps 



Finish: The spreadsheet now automatically calculates the winnings 
based on the match results. 

The spreadsheet will now look amazingly sinnilar to the figure below. 





A 


B 


C 


D E 


E F 


1 

2 


Example - Sports Betting Results 


3 


Player to Win 


Amount 
Bet 


Betting Potential Match 
Odds Winnings Result 


4 


Player 1 


$ 10 


4:1 


$ 40 


Won 


5 


Player 2 


$ 20 


4:1 


$ 80 


Won 


6 


Player 3 


$ 10 


6:1 


$ 60 


Won 


7 


Player 4 


$ 25 


4:1 


$ 100 


Won 


8 
9 


Player 5 


$ 10 


10:1 


$ 100 


Lost 


Player 6 


$ 5 


8:1 


$ 40 


Lost 


10 


Player 7 


$ 20 


2:1 


$ 40 


Lost 


11 


Player 8 


$ 15 


8:1 


$ 120 


Lost 


12 












13 


Total Amount Bet = 


$ 115 


Total Amount Won = 


$ 280 



22 



How do I hide some of my worksheets? 

Hiding and Unhiding Worksheets 

Sometimes it is useful to hide worl<sheets from the normal spreadsheet view. 
Commonly we hide sheets that contain lookup tables, default values and 
constants that we want in a separate spreadsheet but don't want it to be printed 
or viewed in normal use. 

Scenario - Hiding a worlcsheet from normal view 

Our sample spreadsheet contains a computer parts price list. We may want to 
have other worksheets to refer to this price list but we don't want the price list 
open for viewing and printing every time we open our workbook. 

Step 1: Select the worksheet we want to hide from view. 

Select the worksheet we want to hide from view by clicking on the worksheet tab 
located at the bottom of the worksheet. 





A 


B 


c 


1 


Example - Hiding a price list from view 




2 
3 


1 




MyHampers Pty Limited Price List 




4 


1 




5 


Reference Description 


Unit Price 


6 


CB1 


Kwackei^s Chocolate Box (Small) 


$ 10.00 


7 


CB2 


Kwacker's Chocolate Box (Medium) 


$ 22.00 


S 
9 
10 
11 


CB3 


Kwacker's Chocolate Box (Large) 


$ 65.00 


BD1 


Chocolate Dream Basket 


$ 120.00 


BD2 


Chocolate Addictions 


$ 67.00 


BD3 


Supreme Chocoholic's Gitt Box 


$ 115.00 


12 


CHI 


Death By Chess 


$ 52.00 


13 


CH2 


Bonus Chocolate Gift 


$ 16.00 


14 


CHS 


Christmas Tower Gift Box 


$ 40.00 


15 


CH4 


Easter Tower Gift Box 


$ 50.00 


16 








HNI^IwKHideV 


Vorksheet EKample / Show Formulas / Conditio 


nal Formatting 



23 



riicro5oft Excel 2003 Trkk&'n Tips 



step 2: Select "Format | Sheet | Hide'' from the menu. 

Go to the top of the Excel window and click on "Fornnat" and then highlight 
"Sheet", wait and then click on the "Hide" nnenu. 



Format 


Tools Data Window 


Help 


^^^^1 


lir 


Cells... Ctrl+l 
Row ► 
Column ► 




1^ E '^Ui 


langes . . . End Review . . . 1 


t OA * -t-o -00 1 ^= 


Sheet > 


Rename 


1 AutoFormat... 

Conditional Formatting. . . 
Style... 


Hide 


i 


Unhide... 


Background... 
Tab Color... 









Finish: Worlcsheet is now hidden from normal view. 

The worksheet is now hidden fronn view. Any references to this worksheet will still 
work however the user will not be able to select the worksheet fronn the tabs at 
the bottonn of the workbook. You will have to "unhide" the worksheet to be able 
to view and nnake changes. 

Scenario - Unhiding a worksheet for normal view 

After we have hidden the worksheet fronn Nornnal view we nnay want to nnake 
changes to it. The nornnal nnethod of doing this is to unhide the spreadsheet and 
place it back into nornnal view. 



Step 1: Select "Format | Sheet | Unhide'' from the menu 



Go to the top of the Excel 
window and click on 
"Fornnat", then highlight 
"Sheet", wait and then click 
on the "Unhide" nnenu. 



Format Tools Data Window 


Help ^^^^^g 


^ Cells... Ctrl+l 
Row ► 
Column ► 


-\^^ ^^iii, 


langes . . . End Review . . . 1 


4 OA * +;9 -00 1 ^= 


Sheet > 


Rename 
Hide 


m AutoFormat... 

Conditional Formatting... 
j| Style... 


Unhide... 


Background... 
J lab Color... 





24 



step 2: Select worksheet to unhide. 

Select the worksheet name from the Unhide dialog box. In our case we want to 
select the worksheet nanned "Hide Worksheet Exannple". 



Unhide ^ 




Unhide sheet: 


1 


ISIflSMISIllEtl^J^jIdlEinni^H 




R 


1 


1^ 


J 










OK 


Cancel 











Press the OK button to unhide the worksheet. 

Finish: Worlcsheet is now visible in the normal view. 

The worksheet can now be seen in the nornnal view and selected using the tabs 
at the bottonn of the workbook. 





A 


B 


C 




1 

2 


Example - Hiding a price list from view , 


1 






3 

4 


MyHampers Pty Limited Price List 












5 


Reference Description 


Unit Price 




6 


CB1 


Kwackei^s Chocolate Box (Small) 


$ 10.00 




7 


CB2 


Kwacket's Chocolate Box (Medium) 


% 22.00 




8 


CB3 


Kwacket's Chocolate Box (Large) 


$ 65.00 




9 


BD1 


Chocolate Dream Basket 


$ 120.00 




10 


BD2 


Chocolate Addictions 


$ 67.00 




11 


BD3 


Supreme Chocoholic's Gift Box 


$ 115.00 




12 


CHI 


Death By Chess 


% 52.00 




13 
14 
15 


CH2 


Bonus Chocolate Gift 


$ 16.00 




CHS 


Christmas Tower Gift Box 


$ 40.00 




CH4 


Easter Tower Gift Box 


$ 50.00 




HG 










N N 1 ► 1 H l\Hide Worksheet EKample / Shovv Formulas / Conditional Formatting 



25 



ri icrosof t Excel 



Tricks^n Tip5 



How do I look up answers in another table? 

LOOKUP Function 

It is often useful to create a spreadsheet where the user simply enters sonne 
infornnation and Excel then looks up the required infornnation and returns the 
result. 

Scenario - Determining the exam standard for students 

This exannple has a list of students (Colunnn A) and their exann results (Colunnn B) 
entered by the user. We then want Excel to look at that result and deternnine if it 
is a fail, pass or distinction standard. 





A 


B 


C 




1 

2 


Example - Lookup Exam Standards 




3 
4 
5 
6 
7 
8 


Student 


Result Standard 




Paul 


40% 






Greg 


40% 






Peter 


37% 






Sam 


69% 






Penelope 


20% 






9 


Rima 


83% 






10 


Annabelle 


26% 















Step 1: Select the Cell you want the result to be in 

Use the mouse pointer and click on the cell where you want the result to be 
shown. 

Click on Cell C4. 





A 


B 


C 


1 

2 
3 


Example - Lookup Exam Standards 




Student 


Result Standard 




4 

5 
6 

7 


Paul 


40% 






Greg 


40% 






Peter 


37% 






Sam 


69% 






8 
9 


Penelope 


20% 






Rima 


83% 






10 


Annabelle 


26% 







26 



step 2: Select the Paste Function 
menu item as shown right 



Insert 


Format lools Dc 


^ 


1 Cells... 
\ Rows 
i Columns 
1 Worksheet 
= Chart... 
1 Symbol... 


I Page Break 


J&r Function... 




i Name ► 
i Comment 


1 Picture ► 

i Diagram... 

1 Object... 

1 Hyperlink... Ctrl+K 



Step 3: Select the LOOKUP statement from the Select a Function 
selection box 



In the Paste 
Function dialog 
box, select 
"Lookup & 
Reference" from 
the Function 
Category and 
"LOOKUP" from 
the Function Name. 

Press the OK 
button to confirm 
your selection. 



Insert Function 



Search for a function: 



Type a brief description of what you want to do and then 
click Go 



Or select a category: 
Select a function: 



Lookup St Reference 



I INDEX 
INDIRECT 



MATCH 
OFFSET 
ROW 
ROWS 



? X 



Go 



3 



LOOKUP(...) 

Looks up a value either from a one-row or one-column range or from an 
array. Provided for backward compatibility. 



Help on this function 



OK 



Cancel 



27 



ri icrosof t Excel 



Tricks^n Tip5 



step 4: Select the desired LOOKUP function from the 
Arguments'' dialog box. 



'Select 



Microsoft Excel will present you with a "Select Arguments" dialog box. This is 
because the LOOKUP function can be used in different ways. We need to tell Excel 
how we want to use the LOOKUP function so it can present us with the nnost 
useful Paste Function helper for our needs. 

In our exannple we want to select the three-argunnent helper. Click on 
"lookup_valueJookup_vector,result_vector" row. 



Select Arguments 



LOOKUP 

This function lias multiple argument lists. Please select one of them. 

Arguments: 



llookiD value. lookiD 


vfirhnr.rfi'^iill- vfirhnr ^M 


lookjup_value^ array 


- 










Help on this function 


1 OK 




Cancel 



Press the OK button to confirnn your selection. 

Step 5: Enter the required information into the Paste Function helper. 

Helping Note: You can nnove the helper box around the screen by click-and- 
dragging anywhere in the grey area of the box. 

The "Lookup_value" deternnines which cell Excel will look in to deternnine the 
search value. We can either use the nnouse to select the cell or nnanually type the 
cell reference directly into the text box. Note that this is a single cell. In our 
exannple we want to use that lines result cell (Cell B4) as the lookup value. 

The "Lookup_vector" is the range of values that Excel connpares the 
"Lookup_value" to. We always start with an "{" bracket, then the lowest nunnber, 
next nunnber, next nunnber until we reach the nnininnunn value of the highest 
group, then end the list with a "}" bracket. For our exannple we enter 
"{0,0.5,0.75}" into the "Lookup_vector" text box. 

There are a nunnber of rules that the Lookup function uses. 

Rule 1: In the cases where the "Lookup_value" is snnaller than the lowest value in 
the "Lookup_vector", the result will by "#N/A error". 

Rule 2: If an exact nnatch is not nnade then the result will be the largest value in 
"Lookup_vector" that is less than or equal to the "lookup_value". 

Helping Note: Microsoft Excel stores percentages as a decinnal (0.00) nunnber. 
Exannple results of 0% to 100% equate to a decinnal range of 0.00 to 1.00. 



28 



The "Result_vector" is the range of values that Excel will look at to return the 
result. If "Lookup_value" is the sanne as the third itenn in the "Lookup_vector" list 
then Excel will return the third itenn in the "Result_vector" list. For our exannple, 
we enter the three grades that will be returned, {"Fail"/Tass"/'Distinction"}. 





A 


B 


C 


D 


E 




1 

2 
3 


Example - Lookup Exam Standards 




Student 


Result ' Standard 


4 
5 


Paul 


40% 


itinction"}) 


Greg 


45% 






6 


Peter 


37% 






7 
8 
9 
10 


Sam 


69% 






Penelope 


20% 






Rima 


83% 






Annabelle 


26% 






Function Arguments 


y 


1 r- 


■.j^-.ij'i in 1 


LU^jrur 






LDDkup_value B4 ['\:]=Ci.4 




Lookup_vector {0,0.5,0. 75} \-] ={0,0.5,0.75} | | 




ResulLvector ^"Fail";'Pass";'distinction"> ^^J = ■{"Fail";'Pass";'distin( | 


L 




J 1 


= "Fail" 
Looks up a value either Prom a one-row or one-column range or Prom an array. Provided Por 
backward compatibility. 

Result_vector is a range that contains only one row or column, the same size as 
Lookjup_vector. 


Formula result = Fail 




Help on this Punction 


p OK Cancel 











Press the OK button to confirnn your selection. 



29 



ricrosoft Excel 



Tricks^n T«p5 



Step 7: Copy the equation to all the required result cells. 

Use the mouse and click on Cell 
C4 to select the equation we 
want to copy. 





A 


B 


C 




1 

2 
3 


Example - Lookup Exam Standards 




Student 


Result Stfindfird 




4 

5 


Paul 


3% 


Fail 




Greg 


90% 






6 

7 


Peter 


31% 






Sam 


19% 






S 


Penelope 


25% 






9 
10 


Rima 


59% 






Annabelle 


52% 







Copy the cell by either 

Method 1) pressing Ctrl and C at the sanne tinne, or 

Method 2) pressing the copy button 



-J\ 



in the toolbar. 



Select the range of cells from 
C5 to CIO. You can do this 
with the mouse by click-and- 
holding the mouse on cell C5 
and the drag-and-release the 
mouse over cell CIO. The 
screen will look similar to 
figure to the right. 





A 


B 


C ' 


1 

2 
3 
4 
5 


Example -Lookup 


Exam Standards 


Student 


Result 


Standard 


Paul 


87% 


Distinction 


Greg 


58% 




6 


Peter 


91% 




7 


Sam 


60% 




8 


Penelope 


10% 




9 


Rima 


32% 




ID 


Annabelle 


36% 


^ 



Now paste the cell infornnation into the highlighted range. You can do this by 
either 

Method 1) pressing Ctrl and V at the sanne tinne, or 

in the toolbar. 



Method 2) pressing the paste button ^ 



30 



Finish: The standard will be shown as soon as the results are entered by 
the user. 

The spreadsheet will now look similar to the figure below. 





A 


B 


C 


1 

2 


Example - Lookup Exam Standards 


3 


Student 


Result Standard 


4 


Paul 


40% 


Fail 


5 


Greg 


45% 


Fail 


6 


Peter 


37% 


Fail 


7 


Sam 


69% 


Pass 


S 


Penelope 


20% 


Fail 
Distinction 


9 


Rima 


83% 


10 


Annabelle 


26% 


Fail 



31 



ri icrosof t Excel 



Tricks^n Tip5 



How do I freeze headings so I don't lose them when I 
scroll around? 



Freeze Panes 

MS-Excel is a useful tool for presenting data on a page. There connes a point 
where the data being presented does fit on the screen and we have to scroll 
down and across to see the infornnation. If you scroll down enough then it 
beconnes difficult to know what headings relate to each colunnn. The sanne applies 
to row headings when we scroll right. The way to go about this is to tell MS-Excel 
to freeze the headings so that when you scroll off the page you can still see the 
headings. This is called "Freeze Pane". 

Scenario - Formatting a large table to make it easy to read 

You nnay have a large tinnesheet systenn that lists the people involved in a project 
down the left-hand side and the hours worked across the page. We want to 
fornnat the page so that the user can scroll left and right, as well as up and down 
and still see the weeks headings and the teann nnennbers nanne. 

The figure below shows the spreadsheet in a nornnal fornnat. With this default 
view, the use would not be able to scroll right and still see the resource nanne 
connected to each week. 



G Microsoft Excel - Examples.xls 






SEE 


iSJ File Edit View Insert Fornnat lools Data Window Help 


Type a question for help ^ _ [5 


X 


ijiJiauii.jai^aiA^.-'/i'-'7' 1 


^. s -n iillS-^ ioo^/» 


'©1 




: J *a^ Q h5 *2J 1 \1 ^3 1 ^ >*] |J 1 r. Reply with Change; 


;... End Review... H 








j_Arial Narrow ^ 1\ ^ B I U S = S^|$7c 


* -^.0 .DO 1 ^= i= 
> ' .00 -Sr.O 1 *r- =r- 


\m-^ 


•A-i 




E43 - f^ 40 












A 


B 


C 


D 


E 


F 


G 


H 


A 


1 


Example - How to always view the headings 










r 


2 
















3 


Week Ending 






■^1 


lO Resource 6-Jan-06 13-Jan-06 20-Jan-06 


27-Jan-06 3-Feb-06 


10-Feb-06 


17-Feb-06 M 


5 
6 
7 
8 
9 
10 
11 
12 


Mike 


40 


40 


40 


40 


40 


40 


40 


ts 


Kelly 


40 


40 


40 


40 


40 


40 


40 


Nancy 


40 


40 


40 


40 


40 


40 


40 




Richard 


36 


40 


20 


40 


40 


20 


40 


Kerry 


40 


40 


40 


40 


40 


40 


40 


Jim 


40 


40 


40 


40 


40 


40 


40 


Zahi 


40 


40 


40 


40 


40 


40 


40 


Bill 


40 


40 


40 


40 


40 


40 


40 


.Inhn 


4n 


4n 


4n 


4n 


4n 


4n 


4n 





32 



step 1: Select the first cell which contains data 

For the freeze pane function to work, you need to tell MS-Excel what is a heading 
and what is data. To do this you nnust select the very first cell which contains data 
in it. Excel assunnes that everything above and to the left of it is a heading for the 
table. 

Click on Cell B5 as shown below. 





A 


B 


C 


D 


E 


1 


Example - How to always view the headings 




2 












3 




Week Ending 






4 wWtlBSIS^^^^^^^S^mi^MlSiM/mSiMWS/mkMmi^ 


5 
6 


Mike 


40 


40 


40 


40 


Kelly 


40 


40 


40 


40 


7 


Nancy 


40 


40 


40 


40 


8 


Richard 


36 


40 


20 


40 


9 


Kerry 


40 


40 


40 


40 


10 


Jim 


40 


40 


40 


40 


11 

12 
13 


Zahi 


40 


40 


40 


40 


Bill 


40 


40 


40 


40 


John 


40 


40 


40 


40 



Step 2: Select "Window | Freeze 
Panes'' menu item from the menu 



Window Help 



Type a 



New Window 

Arrange... 

Compare Side by Side witii, 

Hide 

Unhide, ■■ 



Split 



Freeze Panes 



I v^ I lExamples.xls 



33 



ricrosoft Excel 



Tricks^n T«p5 



Finish: The Worlcsheet will now have black lines signifying the location 
of the heading 

The row and column headings are now frozen. This allows the user to scroll all the 
way to the right and all the way down the bottonn of the table and be still able to 
view the resource nannes and weeks worked. 





A 


L 


M 


N 





P 


Q 


R 


1 


Example - How to alw: 
















2 


















3 


















O Resource 17-Mar-06 


24-Mar-06 


31-Mar-06 


7-Apr-06 


14-Apr-06 


21-Apr-06 


28-Apr-06 


10 


Jim 


40 


40 


40 


40 


40 


40 


40 


11 


Zahi 


40 


40 


40 


40 


40 


40 


40 


12 
13 


Bill 


40 


40 


40 


40 


40 


40 


40 


John 


40 


40 


40 


40 


40 


40 


40 


14 
15 


Ma 


40 


40 


40 


40 


40 


40 


40 


Jim 


40 


40 


10 


40 


40 


40 


40 


16 
17 


Steven 


40 


40 


40 


40 


40 


40 


40 


Jacques 


40 


40 


40 


40 


40 


40 


40 


18 


Andrew 


40 


40 


40 


40 


40 


40 


40 


19 
20 
21 


Jimmy 


40 


40 


40 


40 


40 


40 


40 


Geoffrey 


40 


40 


40 


40 


40 


40 


40 


Muqtada 


40 


40 


40 


40 


40 


40 


40 


22 


Ellen 


40 


40 


40 


40 


40 


40 


40 


23 


Hugo 


40 


40 


40 


40 


40 


40 


40 


24 


George 


40 


40 


40 


40 


40 


40 


40 



Helping Note : If you want to unfreeze the colunnns and row headings then go to 
"Window I UnFreeze Panes". This will take the table back to its default state. 

Helping Note : You can freeze just the top rows by selecting the first row with data 
in it and then using the freeze function. This nnethod does not freeze and 
colunnns. The sanne principle can be used to freeze colunnns but not rows. 



34 



How do I make mv Worksheet tabs colourful? 

Adding Colour to Worksheets 

It is often nice to add a touch of colour to your worksheets. As a new function to 
MS-Excel 2003, you are now able to easily colour individual worksheet tabs. For 
exannple, you nnay want to colour a sunnnnary worksheet blue to highlight its 
innportance to the user. 

Scenario - Creating excitement on a blanic worlcsheet 

Our sannple spreadsheet has a blank worksheet with a dull gray tab. In this 
exannple we will change the colour fronn gray to a nnore exciting colour. 

Step 1: Select the worksheet tab we want to change the colour of 

Select the worksheet tab we want to change the colour of by clicking on the 
worksheet tab located at the bottonn of the worksheet. 



32 
















33 
















34 
















H ^ ► H 1/ LOOKUP Function / VLOOKUP Table Function ' 


'i. Coloured Tab / 



Step 2: Select the change Tab Colour dialog box 









I Insert... 
1 Delete 

Rename 

Move or Copy... 
J Select All Sheets 






































1 Tab Color... 


^ 


V i-. 1 J 


T^L 


^ View Code 


/.L-uiuureu idu 


/ 



Method 2: Select "Format | 
Sheet I Tab Color..." fronn the 
nnenu 



Method 1: Right-Click on the 
worksheet tab we want to change 
the colour of, as shown below. 



Format 


Tools Data Window 


Help^^^^^^^ 


Jf^ Cells... Ctrl+1 
Row ► 
Column > 


-\i^^-nii 


langes . . . End Review . . . 1 


t 0/^ , +;0 .00 1 ^= 


Sheet > 


Rename 
1 Hide 

1 Unhide... 




AutoFormat... 
Conditional Formatting. . . 
Style... 


^ Background... 








Tab Color... 



35 



ricrosoft Excel 



Tricks^n T«p5 



Step 3: Select the colour on the colour palette chart 



Select the colour you want to change the 
worksheet tab to on the Fornnat Tab Color 
dialog box (shown below). In this exannple, 
we want the tab to be gold in colour. 



Format Tab Color 



Tab Color 



No Color 



iBdhh 
innnn 



n 



nn 



OK 



Cancel 



Finish: The Worlcsheet tab is now the coloured 

Coloured worksheet tabs look different depending on their selection state. These 
two "looks" are shown below. 

Selected Worksheet Tab View: 



32 
















33 
















34 
















n i ► H 7 LOOKUP Function / VLOOKUP Table Function 


)y Coloured 


Tab/^ 



Background Worksheet Tab View: 



32 












33 












34 












H ^ ► n\ LOOKUP Function/ VLOOKUP Table Function / Coloured Tab / 



36 



( 



ISBN 978-0-9804922-0-0 



780980"492200' 



>