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

See other formats

Datasheet

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.

«/}

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

E F G

Example - Yearly Cashfl

ow Table

Yearly Cashflow ' '

1 1

4
5

Income $ 45,000

$ 45,000

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

Expenses $ 38,299

$ 39,234

$ 40,134

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

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

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.

C D E F G

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

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.

B i C J D E F G

2
3

Example -Yearly Cashflow Table

Y^iiily Cashflow

2000 2001 1

Income

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

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

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.

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 $

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

2
3

Example -Yearly Cashfl

ow Table

Yearly Cashflow 2000 2001 2002 ' 2003 2004 2005

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

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).

Example - How to View Formulas in All Cells

Name

Net
Worth

Years
Worhed

Made Per
Year

Person 1

$191.2M

$ 10.62 M/yr

Person 2

$179.4M

$7.17 M/yr

Person 3

$171.3M

$5.19 M/^r

Person 4

$138.7M

$5.14 M/iT

Person 5

$143.3M

$3.77M/yr

Person 6

$181.7M

$12.11 M/yr

Person 7

$113.5M

$2.52M/^r

Person 8

$157.3M

$4.14 M/iT

Person 9

$173.3M

$ 12.38 M/yr

Person 10

$176.6M

$6.31 M/yr

Person 11

$119.1M

$2.77M/^r

15
16
17
IS

Person 12

$199.4M

$4.86^^/1^

Person 13

$192.8M

$5.35M/yr

Person 14

$155.1 M

$4.31 M/yr

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.

D 1

Example -How to Vie

Name

Net Worth

Years Worhed

Made Per Year

Person 1

=(RAND0*1OO+1OO)

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

Person 2

=(RAND0*1OO+1OO)

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

Person 3

=(RAND0*1OO+1OO)

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

Person 4

=(RAND0*1OO+1OO)

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

Person 5

=(RAND0*1OO+1OO)

=INT(RANDO*40+10) =B80

Person 6

=(RAND0*1OO-h1OO)

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

Person 7

=(RAND0*1OO+1OO)

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

Person 8

=(RAMD0*1OO+1OO)

=INT(RANDO*40+10)

=B11/C11

Person 9

=(RAND0*1OO+1OO)

=INT(RANDO*40+10)

=B12/C12

Person 10

=(RAND0*1OO+1OO)

=INT(RANDO*40+10)

=B13/C13

Person 11

=(RAND0*1OO-h1OO)

=INT(RANDO*40-h10)

=B14/C14

Person 12

=(RAND0*1OO+1OO)

=INT(RANDO*40+10)

=B15/C15

Person 13

=(RAND0*1OO+1OO)

=INT(RANDO*40+10)

=B16/C16

Person 14

=(RAND0*1OO+1OO)

=INT(RANDO*40+10)

=B17/C17

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.

Example - Conditional Formatting

2
3

Banh AccDunis

Savings Account 1

$2,100

Savings Account2

$1,500

Credit Card 1

Credit Card 2

-$200

Credit Card 3

-$1,800

Net Value =

$1,600

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

B ' C J

E J

Example - Conditional Formatting

Banh AccDunis

Savings Account 1

$2,100

Savings Account2

$1,500

Credit Card 1

Credit Card 2

-$200

Credit Card 3

-$1,800

Net Valuer

$1,600

1 1

ricro&off Excel 2003 Tricks'n T«ps

Format Tools Data Window

']j^ Cells...
Row
Column
Sheet

Ctrl+1

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... [

' Condition i

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

? X

Font II Border Patterns

Cell shading
Color:

No Color

■DDHHHHI

)

^ -n-i-i-

■■DDHHHE

Pattern;

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.

Example - Conditional Formatting

Banh AccDunis

Savings Account 1

$2,100

5
6

Savings Account2

$1,500

Credit Card 1

-$2,000

Credit Card 2

-$200

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.

C D

2
3

ExampI

e - Automatic Invoice Discount

System

Order Des

cription

Unii Price Ctjauntily

Cost

Item1

Ball Point Pens

$0.35 100

$35.00

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

Subtotal =

$270.40

•u.

Discount^

Totals

$270.40

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

Click on the ElO
cell.

C D

E ■

ExampI

e -Automatic Invoice Discount !

System

2
3

Order Description

Unit Price Q^untitj^

Cost

Item1

Ball Point Pens

$0.35 100

$35.00

Item 2

Whiteboard markers

$2.95 2

$5.90

Item 3

Executive Pens

$9.95 5

$49.75

Item 4

Executive Gift Sets

$35.95 5

$179.75

Subtotal =

$270.40

Discount=f

Total =

$270.40

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.

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:

AND
FALSE

NOT

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

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

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

Subtotal $270.40

Discount^

%*Ea0)

Totals $270.40

Function Arguments

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

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).

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.

C D

E .

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

Subtotal =

$270.40

Discount =

-$27.04

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.

C D

E 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

Subtotal =

$90.65

Discount =

$0.00

Total =

$90.65

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.

BCD

Example - H<

ow many students passed?

Student Name

Exam Result

Maty

FAIL

Patricia

PASS

Linda

PASS

Barbara

FAIL

Elizabeth

FAIL

Jennifer

FAIL

Maria

PASS

Susan

PASS

Margaret

PASS

Dorothy

FAIL

Number of PASS

Number of FAIL

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.

Example - How many students passed?

2
3

Student Name Exam Result

Mary

FAIL

Patricia

PASS

Linda

PASS

Barbara

FAIL

Elizabeth

FAIL

Jennifer

FAIL

Maria

PASS

Susan

PASS

Margaret

PASS

Dorotiiy

FAIL

Number of PASS

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

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

CORREL
COUNT
COUNTA
COU NTBLANK

COVAR
CRITBINOM

COUINTIF(rangepCriteria)

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

IHelp on this function

Cancel

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

Example - How many students passed?

Student Name

Exam Result

4
5

Mary

FAIL

Patricia

PASS

Linda

PASS

Barbara

FAIL

Elizabeth

FAIL

Jenniter

FAIL

IVlaria

PASS

11
12
13

Susan

PASS

IVlargaret

PASS

Dorotliy

FAIL

14
15

Number of PASS

13;PASS")

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"

= 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.

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

Number of FAIL

313;FAIL")

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

Cancel

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

The spreadsheet will now look similar to the figure below.

Example - How many students passed?

Student Name

Exam Result

Mary

FAIL

Patricia

PASS

Linda

PASS

Barbara

FAIL

Elizabeth

FAIL

Jennifer

FAIL

Maria

PASS

Susan

PASS

12
13
14

Margaret

PASS

Dorotliy

FAIL

Number of PASS

Number of FAIL

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.

D E

: F

Example - Sports Betting

Results

Player to Win 1 Amount 1 Betting 1 Potential 1

Bet Odds Winnings

1 Match 1
Result

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

Won

Player 2

$ 20

4:1

$ 80

Won

Player 3

$ 10

6:1

$ 60

Won

Player 4

$ 25

4:1

$ 100

Won

Player 5

$ 10

10:1

$ 100

Lost

Player 6

$ 5

8:1

$ 40

Lost

10
11
12

Player 7

$ 20

2:1

$ 40

Lost

Player 3

$ 15

8:1

$ 120

Lost

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.

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

Search Por a function:

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

Or select a category:

Math S; Trig ^

;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

Cancel

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.

D [

E F

Example - Sports Betting Results

Player to Win

Amount
Bet

Betting
Odds

Potential 1
Winnings

:T 4o'

1 IVIatcli
Result

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~

Range F4:F11

^=-("Wc

)n";" Won";" Won

Criteria "Won"

=k.]="WQr

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 ]

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

2
3

Example - Sports Betting Results

Player to Win

Amount 1 Betting 1 Potential 1
Bet Odds Winnings

Player 1 $ 10 4:1 $ 40 ,

Player 2

$ 20

4:1

$ 80

Player 3

$ 10

6:1

$ 60

Player 4

$ 25

4:1

$ 100

Player 5

$ 10

10:1

$ 100

Player 6

$ 5

8:1

$ 40

Player 7

$ 20

2:1

$ 40

Player B

$ 15

8:1

$ 120

12
13

Total Amount Bet =

Total Amount Won =

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.

D E

Example - Sports Betting

Results

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

Player 6

$ 5

8:1

$ 40

Player 7 ! $ 20

2:1

$ 40

Player 3

$ 15

8:1

$ 120

i J

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

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

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.

D E

E F

Example - Sports Betting Results

Player to Win

Amount
Bet

Betting Potential Match
Odds Winnings Result

Player 1

$ 10

4:1

$ 40

Won

Player 2

$ 20

4:1

$ 80

Won

Player 3

$ 10

6:1

$ 60

Won

Player 4

$ 25

4:1

$ 100

Won

8
9

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 =

$ 115

Total Amount Won =

$ 280

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.

Example - Hiding a price list from view

2
3

MyHampers Pty Limited Price List

Reference Description

Unit Price

CB1

Kwackei^s Chocolate Box (Small)

$ 10.00

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

CHI

Death By Chess

$ 52.00

CH2

Bonus Chocolate Gift

$ 16.00

CHS

Christmas Tower Gift Box

$ 40.00

CH4

Easter Tower Gift Box

$ 50.00

HNI^IwKHideV

Vorksheet EKample / Show Formulas / Conditio

nal Formatting

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

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...

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:

ISIflSMISIllEtl^J^jIdlEinni^H

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.

Example - Hiding a price list from view ,

MyHampers Pty Limited Price List

Reference Description

Unit Price

CB1

Kwackei^s Chocolate Box (Small)

$ 10.00

CB2

Kwacket's Chocolate Box (Medium)

% 22.00

CB3

Kwacket's Chocolate Box (Large)

$ 65.00

BD1

Chocolate Dream Basket

$ 120.00

BD2

Chocolate Addictions

$ 67.00

BD3

Supreme Chocoholic's Gift Box

$ 115.00

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

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

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.

Example - Lookup Exam Standards

3
4
5
6
7
8

Student

Result Standard

Paul

40%

Greg

40%

Peter

37%

Sam

69%

Penelope

20%

Rima

83%

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.

2
3

Example - Lookup Exam Standards

Student

Result Standard

5
6

Paul

40%

Greg

40%

Peter

37%

Sam

69%

8
9

Penelope

20%

Rima

83%

Annabelle

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

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

Cancel

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.

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"}.

2
3

Example - Lookup Exam Standards

Student

Result ' Standard

4
5

Paul

40%

itinction"})

Greg

45%

Peter

37%

7
8
9
10

Sam

69%

Penelope

20%

Rima

83%

Annabelle

26%

Function Arguments

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( |

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.

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.

2
3

Example - Lookup Exam Standards

Student

Result Stfindfird

Paul

Fail

Greg

90%

Peter

31%

Sam

19%

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.

C '

2
3
4
5

Example -Lookup

Exam Standards

Student

Result

Standard

Paul

87%

Distinction

Greg

58%

Peter

91%

Sam

60%

Penelope

10%

Rima

32%

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 ^

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.

Example - Lookup Exam Standards

Student

Result Standard

Paul

40%

Fail

Greg

45%

Fail

Peter

37%

Fail

Sam

69%

Pass

Penelope

20%

Fail
Distinction

Rima

83%

Annabelle

26%

Fail

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

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

Example - How to always view the headings

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

Kelly

Nancy

Richard

Kerry

Jim

Zahi

Bill

.Inhn

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.

Example - How to always view the headings

Week Ending

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

5
6

Mike

Kelly

Nancy

Richard

Kerry

Jim

12
13

Zahi

Bill

John

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

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.

Example - How to alw:

O Resource 17-Mar-06

24-Mar-06

31-Mar-06

7-Apr-06

14-Apr-06

21-Apr-06

28-Apr-06

Jim

Zahi

12
13

Bill

John

14
15

Jim

16
17

Steven

Jacques

Andrew

19
20
21

Jimmy

Geoffrey

Muqtada

Ellen

Hugo

George

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.

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.

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...

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

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:

n i ► H 7 LOOKUP Function / VLOOKUP Table Function

)y Coloured

Tab/^

Background Worksheet Tab View:

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

(

ISBN 978-0-9804922-0-0

780980"492200'

Internet Archive Audio

Featured

Top

Images

Featured

Top

Software

Featured

Top

Books

Featured

Top

Video

Featured

Top

Mobile Apps

Browser Extensions

Archive-It Subscription

Save Page Now

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

See other formats