[img][img][img]أصدقائي الأعزاء بحكم عملي الهندسي وجدت من المفيد المشاركة و لو بشكل متواضع جداً لتعم الفائدة في تعلم هذا البرنامج لمن يرغب بذلك.
يرجى تحميل الملف الـ PDF و من ثم فتحه.
أتمنى منكم الدعاء[/
------------------------------------------------------------------------------------------------------
[english]A Guide to Microsoft Excel 2002
for Scientists and Engineers
Third Edition
Bernard V. Liengme
St. Francis Xavier University
Nova Scotia, Canada
ELSEVIER
BUTIERWORTH
HEINEYANN
AMSTERDAM BOSTON HEIDELBERG LONDON NEW YORK OXFORD
PARIS SAN DIEGO SAN FRANCISCO SINGAPORE SYDNEY TOKYO
Elsevier Butterworth-Heinemann
Linacre House, Jordan Hill, Oxford OX2 8DP
200 Wheeler Road, Burlington, MA 01803
First published 2000
Third edition 2002
Reprinted 2003
Copyright 0 2000, 2002 Bernard V. Liengme. All rights reserved
The right of Bernard V. Liengme to be identified as the author of this work
has been asserted in accordance with the Copyright, Designs
and Patents Act 1988
No part of this publication may be reproduced in any material form (including
photocopying or storing in any medium by electronic means and whether
or not transiently or incidentally to some other use of this publication) without
the written permission of the copyright holder except in accordance with the
provisions of the Copyright, Designs and Patents Act 1988 or under the terms of
a licence issued by the Copyright Licensing Agency Ltd, 90 Tottenham Court Road,
London, England W1T 4LP. Applications for the copyright holder’s written
permission to reproduce any part of this publication should be addressed
to the publisher.
Permissions may be sought directly from Elsevier’s Science and
Technology Rights Department in Oxford, UK: phone: (44) (0) 1865 843830;
fax: (+a(0)) 1 865 853333;e -mail: permissions@elsevier.co.uk .You may also
complete your request on-line via the Elsevier homepage
(http://www.elsevier.corn), by selecting ‘Customer Support‘ and then ‘Obtaining
Permissions’.
British Library Cataloguing in Publication Data
A catalogue record for this book is available from the British Library
Library of Congress Cataloguing in Publication Data
A catalogue record for this book is available from the Library of Congress
ISBN 0 7506 5613 1
For information on all Butterworth-Heinemann publications
visit our website at http://www.bh.com" onclick="window.open(this.href);return false;
Printed and bound in Great Britain by Martins the Printers, Berwick upon Tweed
Contents
Preface xi
1 The Microsoft@E' xcel Window
Concepts
Exercise 1 : Anatomy of the Window
Exercise 2: The Workspace
Exercise 3: The Menu Bar
Exercise 4: The Toolbars
Exercise 5: Customizing Menus and Toolbars
Exercise 6: Getting Help
Problems
2 Basic Operations
Concepts
Exercise 1 : Filling in a Series of Numbers
Exercise 2: Entering and Copying a Formula
Notes on Copying Formulas
Exercise 3: Formatting the Results
Notes on Precision and Formatting
Exercise 4: Displayed and Stored Values
Exercise 5: Formats Get Copied
Exercise 6: Too Many Digits
Exercise 7: Calculation Example
Exercise 8: Entering Formulas by Pointing
Exercise 9: References: Relative, Absolute and Mixed
Exercise 10: Editing and Formatting
Exercise 1 1 : What's in a Name?
Exercise 12: Custom Formats
Exercise 13 : Symbols and Such
Exercise 14: Fractions
Natural Language Formulas
Problems
3 Printing a Worksheet
Concepts
Exercise 1 : A Quick Way to Print
Exercise 2: Another Way to Print
Exercise 3: Page Setup
Exercise 4: Changing Margins
Exercise 5: Header and Footer
Documenting Worksheets
vi A Guide to Microso$ Excel 2002 for Scientists and Engineers
Exercise 6: Gridlines and Row/Column Headings
Exercise 7: Setting the Print Area
Exercise 8: Printing Titles
Exercise 9: Forcing Page Breaks
Exercise 10: Viewing and Printing Formulas
Summary of Print Commands
4 Using Functions
Concepts
Exercise 1 : AutoSum and AutoCalculate
Exercise 2: Insert Function
Exercise 3: Entering a Function Directly
Exercise 4: Mixed Numeric and Text Values
Exercise 5: Trigonometric Functions
Exercise 6: Exponential Functions
Exercise 7: Rounding Function
Exercise 8: Array Functions
Some Other Mathematical Functions
Working with Time
Problems
5 Decision Functions
Concepts
The IF and the Logical Functions
Exercise 1 : A What-if Analysis
Exercise 2: Avoiding Division by Zero
Exercise 3: Quadratic Equation Solver
Exercise 4: Protecting the Worksheet
Exercise 5: Imaginary Roots
Exercise 6: Logical Functions
Table Lookup Functions
Exercise 7: Horizontal Lookup
Exercise 8: Vertical Lookup
Exercise 9: Conditional Summing and Counting
Exercise 10: Array Formulas
Problems
6 Charts
Concepts
Types of Charts
Line and XY (Scatter) Charts
Embedded Charts and Chartsheets
Anatomy of a Chart
Smoothing Option
Exercise 1: Creating an XY Chart
Contents vii
Exercise 2: Modifying a Chart
Exercise 3: Line Chart with Two Data Series
Exercise 4: XY Chart with Two Y-Axes
Exercise 5: Combination Chart
Exercise 6: Chart with Error Bars
Exercise 7: Changing Axis Crossings
Exercise 8: Blank Cells in a Data Series
Exercise 9: Selecting Non-adjacent Data
Exercise 10: A Chart with Two X-Ranges
Exercise 1 1 : A Bar Chart with a Difference
Exercise 12: Displaying Units
Exercise 13: Setting the Default Chart Type
Selecting a Chart Component
Too Much Data
Dynamic Charts
Printing a Chart
Problems
7 Curve Fitting
Concepts
Exercise 1 : Finding the Slope and Intercept
Exercise 2: Adding the Trendline to a Chart
Exercise 3: Adding the Trendline Equation
Exercise 4: The LINEST Function
Exercise 5: LINEST with Polynomial Data
Exercise 6: Non-linear Plots
Exercise 7: Residuals
Exercise 8: Calibration Curve
Exercise 9: Interpolation
Exercise 10: Difference Formulas and Tangents
Problems
8 User-defined Functions
Concepts
Security Alert
Exercise 1 : The Visual Basic Editor
Syntax for a Function
Exercise 2: A Simple Function
Naming Functions and Variables
Worksheet and VBA Functions
Exercise 3: When Things Go Wrong
Programming Structures
Exercise 4: The IF Structure
Exercise 5: Boolean Operators
Exercise 6: The SELECT Structure
viii A Guide to Microsoft Excel 2002 for Scientists and Engineers
Exercise 7: The FOR.. .NEXT Structure
Exercise 8: The DO.. .LOOP Structures
Variables and Data Types
Exercise 9: A User-defined Array Function
Exercise 10: Inputting an Array
Exercise 1 1 : Improving Insert Function
Exercise 12: Some Debugging Tricks
Using Functions from Other Workbooks
Problems
9 Modelling I
Concepts
Exercise 1 : Model of a Bouncing Ball
Exercise 2: Population Model
Exercise 3: Titration Model
Exercise 4: Making Waves
Exercise 5: Taking Control
Problems
10 Solving Equations
Concepts A: Finding Roots
Exercise 1 : The Bisection Method
Finding Roots with Goal Seek
Exercise 2: A Simple Quadratic Equation
Exercise 3: Solving a Cubic Equation
Exercise 4: Transcendental Equations
Using Excel’s Solver
Exercise 5: Roots of a Cubic Equation with Solver
Exercise 6: Using a Constraint
Solver Options
Concepts B: Solving Simultaneous Equations
Exercise 7: A Simple Simultaneous Equations Problem
Exercise 8: An Improved Simultaneous Equations Solver
Exercise 9: Non-linear Simultaneous Equations Solver
Concepts C: Matrix Algebra
Exercise 10: Some Matrix Operations
Exercise 1 1 : Solving Systems of Linear Equations
Concepts D: Curve Fitting
Exercise 12: A Linear Curve Fit
Exercise 13: A Gaussian Fit with Solver
Matrix Diagonal
Problems
Contents ix
11 Numerical Integration
Concepts
Exercise 1 : The Trapezoid Rule
Exercise 2: Simpson’s ’/3 Rule
Exercise 3: Adding Flexibility
Exercise 4: Going Modular
Exercise 5: Tabular Data
Improper Integrals
Exercise 6: Gaussian Integration
Exercise 7: Monte Carlo Techniques
Problems
12 Differential Equations
Concepts
Exercise 1 : Euler’s Method
Exercise 2: The Runge-Kutta Method
Exercise 3: Solving with a User-defined Function
Simultaneous and Second-order Differential Equations
Exercise 4: Solving a Second-order Equation
Exercise 5: The Simple Pendulum
Problems
13 Modelling I1
Concepts
Exercise 1 : The Four-bar Crank: Using Solver
Exercise 2: Temperature Profile: Circular References
Exercise 3: Temperature Profile: Matrix Method
Exercise 4: Emptying the Tank
Exercise 5: An Improved Tank Emptying Model
Problems
14 Statistics for Experimenters
Concepts
Exercise 1 : Descriptive Statistics
Exercise 2: Frequency Distribution
Exercise 3: The Confidence Limits
Exercise 4: Experimental and Expected Mean
Exercise 5: Pooled Standard Deviation
Exercise 6: Comparing Paired Arrays
Exercise 7: Comparing Repeated Measurements
Exercise 8: The Calibration Curve Revisited
Exercise 9: More on the Calibration Curve
Problems
15 Report Writing
Concepts
Exercise 1 : Copy and Paste
Exercise 2: Object Embedding
Embedding vs Linking
Exercise 3: Embedding and Linking
Exercise 4: Creating an Equation
Exercise 5: Interactive Web Page
Answers to Starred Problems
Index 315
Preface
Microsoft@ Excel is a ‘number crunching’ application with the
accountant as its primary target. However, it also provides the
scientist or engineer with a very powerful computational tool.
True, there are more sophisticated mathematical applications, such
as Mathematica, MathCAD, Maple, etc., but none are as widely
available as Microsoft Excel. Furthermore, the learning curve for
Excel is very gentle; a little learning goes a long way! Once a few
basic skills have been mastered, many spreadsheets may be
developed in much the same way one would proceed with pencil,
paper and calculator, but with more speed, higher precision and
greater flexibility.
This Guide is designed to give readers a wide range of examples
from which they may learn how to apply Excel to problems in their
specialized fields. For the student reader, no advanced knowledge
of science or engineering is expected and no one who has taken, or
is currently taking, an introductory calculus course should find the
mathematics difficult. In many cases numerical methods are used
to find approximate answers to problems which can be solved by
analytical methods. It is a great confidence booster to know you
have obtained the correct result and encourages one to try problems
for which the exact methods are either very complex or nonexistent.
This is very much a practical book designed to show how to get
results. The problem sets at the ends of the chapters are part of the
learning process and should be attempted. Many of the questions
are answered in the last chapter. The Guide is suitable for use as
either a textbook in a course on scientific computer applications,
as a supplementary text in a numerical methods course, or as a selfstudy
book. Professionals may find Excel useful to solve one-off
problems rather than writing and debugging a program, or for
prototyping and debugging complex programs. A few topics are
not covered by the Guide: the major ones being the database
functions and subroutine modules. These are fully covered in Excel
books targeted at the business community and the techniques are
applicable to any field.
When I was working on the first edition of this book Microsoft
introduced Excel 97, Since then we have had Excel 2000 and then
xii A Guide to Microsoft Excel 2002 for Scientists and Engineers
Information box: Boxes like this
contain additional information,
shortcuts, etc.
The Save tool
New to Excel 2002
Website: http://www.bh.com" onclick="window.open(this.href);return false;
/companions/0750656 13 1
Excel 2002. While these have only a marginal effect on
functionality, there have been some significant changes in the
appearance of dialog boxes and in some terminology. This book
uses Excel 2002 for its screen captures but readers with earlier
versions should have no difficulty following the instructions.
I am indebted to many people: my students and colleagues for
helpful suggestions, to readers of earlier versions who e-mailed
comments, Nikki Dennis and Matthew Flynn for agreeing to
publish the first edition, Rachel Hudson ofButtenvorth-Heinemann
for her invaluable assistance with the present project, and my wife,
Pauline, without whom this book would still be a pipe-dream.
I welcome e-mailed comments and corrections (hopefully with a
third edition, these will be few and minor!), and will try to respond
to them all. Please check my web site and the Guide’s web site at
http://www.bh.com/companions/0750656" onclick="window.open(this.href);return false; 13 1 for updates.
I hope you enjoy learning to ‘excel’.
Bernard V. Liengme
bliengme@stfx.ca
http:/www,stfx.ca/people/bli engme
Conventions used in this book
Information boxes in the left margin are used to convey additional
information, tips, shortcuts, etc.
Data which the user is expected to type is displayed in a
monospaced font. This avoids the problems of using quotes. For
example; in cell A1 enter the text Resistor Codes.
Non-printing keys are shown as graphics. For example, rather than
asking the reader to press the Control and Home keys, we use text
such as: press [ctrll+[Homel. When two keys are shown separated by
f, the user must hold down the first key while tapping the second.
Generally when a new reference is made to a button on a toolbar,
a graphic of the icon is shown in the left column. New features in
Microsoft Excel 2002 are flagged in the left margin.
In the Problems section of each chapter, an asterisk against a
problem number indicates that a solution is given at the end of the
book. Excel files for answered problems and additional files may
be found at: http://www.bh.com/companions/0750656113" onclick="window.open(this.href);return false;.
The Microsoft@E xcel Window
Concepts
Exercise 1: Anatomy
of the Window
You are probably anxious to start using Microsoft@ Excel.
However, you must first become familiar with its window and we,
the reader and the writer, must agree on some basic terminology.
It is useful to conform to the terminology that Microsoft uses so
that you will know what topic to search in Help when more
assistance is needed. Unless you are new to Windows@, many of
the topics covered in this chapter will be familiar. You should learn
the names of the various parts of the window and how to access
commands using menus and toolbars.
Begin this exercise by starting Excel. Your screen will look similar
to that in Figure 1 .l. There could be some minor differences
because Excel allows the user to customize the menus and toolbars.
Furthermore, starting with Excel 2000, the items displayed in
toolbars and in the initial drop down menus change with usage. A
tool that has been recently used will be displayed on the initial
menu. We will look at customization in Exercise 5.
It is convenient to divide the window into seven main parts: title
bar, menu bar, Standard toolbar, Formatting toolbar, workspace,
task pane and status bar. You will be familiar with the first four
areas from using other applications so they will be described only
briefly.
Title bar
In starting Microsoft@E xcel, we have opened a new workbook.
Because we have not yet saved our work, Excel has given this the
default name of Bookl.
Menu bar
The menu bar provides the user with one way to access the
Microsoft Excel commands. Commands are actions you perform on
your worksheet. Examples are: saving the data to a file, printing a
worksheet, changing the appearance of some text, etc.
Toolbars
Toolbars are another, more intuitive and quicker, method of
accessing commands. Each tool on a toolbar is depicted by an icon.
2 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Specifications: For other limits
(such as the maximum number of
characters allowed in a cell) use the
Answer Wizard in Help to search
for specifications.
We may speak of, for example, ‘clicking on the Copy tool’ or the
‘Copy button.’
Formula bar
We will examine this more closely in a later chapter. For now,
click the mouse in several places within the workspace and watch
the information change in the name box which is the left-hand part
of the formula bar. As you select different cells you will also note
how the corresponding column and row headings are highlighted.
Works pace
This is the central part of your work. It is here that you will type
data and perform calculations. Note how the main part of the space
is divided by gridlines into rows and columns. The smallest unit of
space, where a row and a column intersect, is called a cell. At the
top of the worksheet are the 256 column headings starting with A
and ending with IV. To the left are the row headings numbered 1
to 65,536. How many cells are there on a single worksheet?
I Title I bar MeI nu bFaorr mula bar tSotoantbdaarr d tFooorlmbaart ting Questi1o n bToaxs kDane
What we are looking at in Figure 1.1 is only part of one of the
worksheets which makes up your workbook. To the far right, and
at the bottom of the workspace, you will see the vertical and
The Microsoft Excel Window 3
New to Excel 2002
horizontal scroll bars which allow you to view other parts of the
worksheet. Also at the bottom are the sheet tabs which give you
access to the other worksheets. Excel normally opens a new
workbook with three worksheets. It is possible that your copy of
Excel has been configured to give a new workbook some other
number of worksheets. We may delete or add extra worksheets to
a maximum of 255 depending on the amount of memory in your
computer. Later, we will be introduced to chart sheets. We will not
investigate module sheets which were made redundant with
Microsoft Excel 95.
Task pane
In Excel 2002, to the right of the workspace is the task pane. This
is designed as a productivity tool. It adds no new functionality but
groups frequently used tools in one place. However, it greatly
reduces the amount of the workspace that is visible so you may
wish to close it. There is more on this topic in Exercises 4 and 5.
Question box
In Excel 2002, to the right of the menu bar is the question box.
This provides a handy way of starting Help and recalling earlier
topics you have looked up.
Status bar
The status bar provides information. To the left is the message
area. If your mouse pointer is within the workbook area, this
should be showing the word ‘Ready’. To the right are some
sculptured boxes called the keyboard indicators. Press the [Capslock]
key a few times and watch the text ‘CAPS’ appear and disappear.
Cells and ranges
Clearly, we need a way to refer to a specific cell on the worksheet.
We have seen that a cell occurs at the intersection of a column and
a row. We speak of a cell reference which is a combination of the
column heading and the row number. The cell at the top left, which
is at the intersection of column A and row 1, has the cell reference
Al. The cell below is A2 while the cell to the right is B1. This
method ofreferring to cells using the column letter is called the A1
method.
There is another method in which the column letter is converted to
a number; this is called the R1 C1 method since the top left cell has
the address R1 C 1 using this method. We shall not pursue this.
4 A Guide to Microsoft Excel 2002 for Scientists and Engineers
3 length
4 width
Exercise 2: The
Workspace

Carefilly note that in this paragraph we have not spoken of a cell
name. In Chapter 2 we find that this has a very specific meaning.

A range is a rectangular block of cells. The cells Al, A2, A3, B1,
B2 and B3 form a range which we can refer to using Al:B3. In
general a range is denoted by the cell references of the top left cell
and the bottom right cell separated by a colon. Since a range may
be a one by one block, the word range may also refer to a single
cell. In a later chapter, we will learn how to reference a range from
another worksheet in the current or another workbook.
area 42
perimetre 26
In this exercise you will learn how to enter data into a cell, edit this
data and move around (navigate) the worksheet. We also introduce
the formula concept. By the end of step (8) of this exercise your
worksheet should resemble that in Figure 1.2, complete with
misspellings. If you do not have time to complete the exercise in
one session go to step (a) of Exercise 3 to see how to save your
work.
I I A I B I C I
I 1 ]Area and Perimeter calculations I
(a) We wish to enter some data into cell A1 so this needs to be the
active cell. There are two ways of knowing which is the active
cell. Look in the name box of the formula bar (it is just above
the A column heading). Does it say ‘A1 ’? Alternatively, in the
worksheet is there a border around cell AI? This is called the
cell selector. If AI is not the active cell, the quickest way to
make it so is to press @+[Home].
(b) With AI as the active cell, type the heading Area and
Perimeter calculations and then press the [Enterl key. Note how
the active cell becomes A2. Pressing [Enterl always moves the
cell selector down one row. If you make a typing error
continue on; we will see how to make corrections later. Press
[Enterl once more to move to A3.
The Microsoft Excel Window 5

may be completed in one of severa
ways. They differ in how the:
relocate the cell pointer.
-We ma-y use -the ke-ys:
(c) In A3 type the word length but do not press [Enter]. This time use
the mouse to click the Enter button (the green t4 button) in the
formula bar - see Figure 1.3. This time the cell selector has not
moved. We have now seen two methods of completing a data
entry: pressing the [Enter] key and clicking the Enter button on
the formula bar.
Comnleting an entrv: A cell entry
\Enter&], I c - S h i f t J + ( E n t e r ~ ] , [TabSl,
[z5xshlf+[TabS), P J, m, FJ , @ (d) To move to cell A4 , press the @ or the [wke]y. Do not
left click the cell A4. It will get you to A4 but this is a bad
We may also use the Enter button habit. If you are working with a formula, clicking another cell
(4)on the formula bar. This enters its reference into the formula. Continue typing the
displays only when a cell is being words width, area and perimetre in A4, A5 and A6,
modified. respectively. We will correct the incorrect spelling later.
are urged not to develop the
habit of clicking another cell to
(e) In cells B3 and B4 enter the values 6 and 7, respectively.
complete an entry; this will give
erroneous results when you start
working with formulas.
If you start an entry in the wrong
cell. use the [Escl key or the Cancel
button (X) on the formula bar to
terminate the action.
(f) In B5 we will use a formula to calculate the area. In this cell
type =B3*B4. There are two important items to note here: (1)
a formula always begins with the equal sign, and (2) the
multiplication operator is not x but *. Ifyou pressed [Enter] rather
than the Enter (4b)utt on in the formula bar to complete the
formula, return to cell B5 by clicking it once. Notice how the
cell now displays the result of the formula while in the
formula bar we can see the actual contents of this cell.
Cancel Enter
button button
Figure 1.3
(g) In B6 we shall purposely make a mistake. To compute the
perimeter, type the formula =2*B3+B4 and click the Enter
button (4)I.f you pressed [Enter], go back to B6 since we are
going to correct it. The formula we need is 2*(63+B4) to give
6 A Guide to Microsoft Excel 2002 for Scientists and Engineers
the correct result; the reason for this is given in Chapter 3.
Here we will see one way of making a correction. Move the
mouse until the pointer is in the formula bar; note how the
pointer changes shape from a hollow + shape to a 1 (I-beam)
shape. Move the I-beam to just after the * symbol and press the a key to insert the opening parenthesis. Now press the (End]
key and add the closing parenthesis, a.Cl ick the Enter button
(d)to complete the editing. The cell should now show the
correct value.
Your worksheet should now be similar to that in Figure 1.2.
Type another number in B3. Note how the values shown in
cells B5 and B6 change as soon as the entry is completed.
We have entered three types of data: text, numbers and formulas.
Note how text is left justified while numbers are displayed right
justified. Later we will see how to change this justification or
alignment.
We have learnt how to move armnd the worksheet using the
mouse and the arrow keys. Before completing this exercise, take
some time out to explore how the scroll bars work and find what
happens when you Ip-r[ess an .]pUd=[ Compare the effects
with pressing these keys when the key is held down.
Remember that m+m will always return you to A1 . If your
mouse has a wheel between the two buttons you may wish to
experiment with it.
With cell AI as the active cell, see what @ + a does. Try the
same command starting first from A3 and then from A6. Return to
A1 and find the effect of + [End]. You may also try Go To -
either from the Edit menu or by using either [F51 or [ctrl+G as a
shortcut.
Now we will alter the text in cells AI to A6. For the purpose of the
exercise we will assume that we want each word to start with a
capital letter. There are a number of ways of doing this.
(h) Make A3 the active cell and retype the entire word Length to
make the correction.
(i) The next correction will be made by editing. Using [ctrll+lHome],
make AI active. To edit the cell press m. The status bar will
display Edit rather than Ready and the mouse pointer has
become an I-beam. You can also tell you are in Edit mode by
The Microsoft Excel Window 7
looking at the lower right corner of the cell selector border. In
Ready mode it has a block shape called the cell handle; in Edit
mode the corner resembles an inverted L shape. Position the
pointer before the first letter of ‘calculations’, press [Deletel and
then an upper ‘cy.
While we are in A1 , we can try another experiment. Because
A1 contains text and the cells to its right are empty, the text in
A 1 can overflow into B 1 and C 1. Click on C 1, type some text
and press [Enter]. Now we have ‘lost’ some characters of the last
word in A1 . To restore it click the Undo key or use the Undo
item on the Edit menu.
The Undo tool
Shortcut: The keyboard shortcut
for Undo is m+Z. Hold down m
while tapping the Z key.
(i) Double click on A4 and you will find that this too places you
in edit mode. Press the [End) key. What happens? Now press
[Home]. Type a ‘W’. Now we have two ‘w’s because the default
in Excel is insert mode - anything we type is inserted into
existing text. You can toggle between insert and overtype (also
called typeover) mode with the [Insert] key. In overtype mode the
status bar displays OVR and in the cell one or more characters
are highlighted. Experiment with the [CBksp] and [Deletel keys
while editing the cell entry to Width.
(k) In steps (i) and (i) we did the editing within the cell. Make A5
the active cell but this time click the mouse in the formula bar
and do the editing there. Press [ww]hen you have made
the change. Do the same with A6 changing only the first letter;
do not correct its spelling. After clicking the Entry button (a),
click the Spelling button and let Excel find this and any other
H T h e Spelling tool
Shortcut: The keyboard shortcut
for Spelling is a.
spelling errors.
Exercise 3: The Menu In this exercise we look at the main menu bar and find out how to
use some of its various commands.
(a) We are going to save the file containing the work you did in
Exercise 2. Click File on the menu bar to pull down its menu.
Choose the Save item to bring up the Save dialog which is
common to all Windows applications. Select the folder you
have chosen to keep your Excel file in. I suggest you name this
workbook CHAP1 .XLS so that we can refer to it again.
Bar
Note how the title bar has changed to reflect the new name.
Next time you start Excel, the name of this file will appear at
the bottom of the File menu. This gives you a quick way of
8 A Guide to Microsoft Excel 2002 for Scientists and Engineers
reopening the file. This feature may not be available if you are
on a network.
(b) Next we will explore the Format menu. You may be wondering
Keyboard alternative: An why some letters in the names of menu items are underlined.
alternative way to open a menu is: You can open a menu using this letter in association with the
Press @, use the @ and keys key in place of clicking the mouse. To open the Format
to the main menu and menu, hold down and press the @ key. Note the triangle
press [Entert']. symbol (b) to the right of the Columns item. When you click
on an item with this symbol a submenu appears. Click on
-Co lumns and note the ellipses (three dots .. . ) after the Width
item. When you click on such an item, in a menu or a
submenu, a dialog box is opened. Click on Width to open its
dialog box. Click the Cancel button since we do not wish to
change the column width at this time.
(c) Open the Format menu again. Excel 2000 and 2002 display a
drop down menu with a downward pointing arrow at the
bottom. To make the menu less cluttered, items that you
seldom use are not immediately displayed. The menu will
expand if you wait or if you click on the arrow - see Figure
1.4. Most expanded menus are much larger than this example!
Use the (Escl key to close the menu. In Exercise 4 we learn how
to customize Excel so that all menu items show immediately.
W C l i c k to expand menu
Figure 1.4
The Microsoft Excel Window 9
Exercise 4: The
Tool bars
The Print tool
B T h e What’s This tool
WThe Spell tool
(d) You may sometimes wish to use a keyboard shortcut rather
than clicking the mouse. Open the Edit menu. If you have not
copied something since logging on to your computer, the
clipboard will be empty and the Paste feature will be displayed
in light text - we say it is greyed out. If an item in a menu is
greyed out it is currently unavailable to you. To the right of
Cut we see the text [Ctrll+X, while to the right of Go To is
ICtrl]+G or [. These are notes telling you that Cut may be
accessed by holding down the [Ctrll key while you press the X
key without ever opening a menu. Experiment with the @+C
and m+V to copy the entry in A6 to A10.
To close a menu opened by mistake either click the mouse
anywhere in the workbook window or repeatedly press the [Escl key
until nothing in the menu bar is highlighted.
Microsoft Excel provides a number of toolbars. The Standard and
the Formatting toolbars are generally displayed all the time. Others
appear when the context is right.
An icon on a toolbar allows the user to quickly access one of the
Excel commands without the need to open a menu. In addition,
since the icons are simple pictures, there is little to learn. For
example, if you wish to print a worksheet using the menu bar, you
either know the Print command is in the File menu or you must go
searching for it. On the other hand, the icon depicting a printer is
self-explanatory and easy to find.
(a) The pictures for some buttons are less obvious. However,
when you move the mouse pointer over an icon without
clicking and wait a second or two, Excel displays a tool tip
naming the tool associated with the icon. For some icons you
may find that this method does not give enough information.
Let’s use the context-sensitive method to get more
information. Open the Help menu and click on the item What s
This? The cursor changes to a question mark. Move the cursor
to the Spell tool (it has ABC on its icon) and left click it. A
larger tool tip with additional information is displayed. Note
that if you need additional information about another icon you
must visit the Help menu item again.
(b) By default, the Standard and Formatting toolbars are shown
side by side in newer versions of Excel to give more room for
the worksheet area. Of course, this means that fewer tools can
IO A Guide to Microsoft Excel 2002 for Scientists and Engineers
be displayed. Figure 1.5 shows the Formatting toolbar. Note
the double arrow on the right of the toolbar; this gives us
access to the otherwise hidden tools.
Figure 1.5
The handle on the left of the toolbar may be used to move the
toolbar. When the cursor is moved over the handle we get a
cursor with four arrows +. Carefully drag the Formatting
toolbar’s handle to the left to reveal more formatting tools but
fewer Standard tools. If your toolbar moves below the
Standard toolbar, use the menu commandToolslCustomize and
click on the Options tab. Remove the check mark from Show
Standard and Formatting toolbars on two rows - see Figure
1.6. Note also the options concerning the Menu commands.
If you drag the toolbar down too far it will become a floating
menu; it will be a window with its own title bar. To dock the
toolbar drag it with the title bar into the area where Excel
generally displays toolbars.
Figure 1.6
The Microsoft Excel Window I I
(d) While by default Excel displays only the Standard and the
Formatting toolbars, it has a wide variety of specialized
toolbars. Generally these become visible when needed. So, for
example, when we are working on a chart, Excel displays the
Chart toolbar provided it is checked in the YiewlToolbars
menu.
The Drawing toolbar is useful for annotating a worksheet with
arrows and other symbols. Use the menu command
-Vi ewlIoolbars to make the Drawing toolbar visible. Note that
its normal docking place is above the status bar but it can be
made floating by dragging its handle. Experiment with the
drawing tools and then use YiewlIoolbars to hide the toolbar.
The command YiewJTask Pane may be used to open and close
the task pane.
Exercise 5:
Customizing Menus
The menus and toolbars are set up for the average user. Sometimes
we would like something different. Microsoft Excel makes it easy
to modify the menus and the toolbars.
(a) A menu or toolbar can be customized only when the
Customization window is open. We do this with
-To olslcustomize. The window is depicted in Figure 1.7. It is
similar to Figure 1.6 but this time the Toolbars tab is open.
and Toolbars
(b) When Figure 1.7 was captured, the Drawing toolbar was
displayed. We saw in step (c) of Exercise 4 how to open and
close toolbars with YiewlIoolbars. The Customize window
provides another method. Once again all that is needed is to
place or remove a check mark in the appropriate box. If you
scroll down the list of toolbars you will find another way of
displaying the task pane.
WThe Hyperlink tool
(c) Let us assume the user does not need the Insert hyperlink tool.
With the Customize window open, place the mouse pointer
over the Insert hyperlink tool in the Standard toolbar and,
holding down the left mouse button, drag the icon off the
toolbar. To restore the tool, select Standard in the Toolbars tab
and click on the Reset button. Click on Yes when Excel asks
you to confirm the action. The same dragging method may be
used to reposition an icon on a toolbar.
12 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Figure 1.7
(d) To find even more tools, click on the Command tab of the
Customize window. Select a topic such as Web and move the
vertical slider to review the available tools. To add a tool, drag
it onto the toolbar at the required position. Try dragging the
Back tool on the standard toolbar next to the Insert hyperlink
tool. Use the Beset button on the Toolbars tab to restore Excel
to the default setting.
(e) Items on the menu bar and on popup menus may be dragged to
new positions in the same manner as tools. Items may be added
to the menu bar. A tool may be placed on the menu bar if you
so wish. To restore the menu bar, locate Worksheet Menu Bar
in the list of toolbars and click the Reset button. To have all
commands display immediately you open a menu bar item,
remove the check mark from the Menus show recently used
commandsflrst box of the Options tab.
Exercise 6: Getting In this exercise we briefly look at how to get help in Microsoft
Excel. Suppose we wish to know how to change the width of a
column. There is no need to type a complete question such as How
do I change a column width? You need enter only the key words;
so in the case we could use column width.
Help
@ The Help tool
The Microsoft Excel Window I3
(a) Click on either the Help item of the menu bar or on the Help
tool, or press [F11.
(b) Unless your copy of Excel has been set to do otherwise (see
step (c) below), one of the Assistants will appear together with
a dialog box into which you type your question. Then click on
Search and click the item that seems closest to your question
- Figure 1.8. This will open the full Help window.
Figure 1.8
rthevlbii'of a 3 Dch3tt
ice data for a PIV
ze chart items wth the
5 from moving and 5121
n imparted text tile Iw
/ /
Change column width and row height
Change the width:
b Of a single cokmn
P Ofrnuft~pleccob.nnns
b To fR the contmts
b TO a WeClfK 4 t h
P To match another cbmn
change the ddbult width
1 I Right-click a sheet tab, and then clickselect All Sheets
on the shortcut menu
2 On the Format mem, pomt to Column, and then ckk
Standard Width
3 Type a new meawrement
The number that w a r s n the Standard column ddth
box IS the average number of &nts 0-9 of the standard
font that Flt in a cell
b Tip
Change the Mqht
P Ofasmglerow
b OF multiple rows
P To fit the contents
b Toa spec& hetoht
Figure 1.9
I4 A Guide to Microsoft Excel 2002 for Scientists and Engineers
New to Excel 2002
If no Assistant appears you will see the full Help window
where you can type your question in the text box associated
with the Answer Wizard - Figure 1.9.
With Excel 2002 one can type the question in the question box
- less intrusive than the Assistant. Furthermore, the question
box retains your question during a session so you can quickly
return to a Help topic.
(c) The information you need is to the right of the Help window.
Remember that text in blue will expand to give more
information or is a link to another part of Help.
(d) In this step we remove the Assistant. There are two reasons for
doing so: (1) some users find the animation annoying and (2)
you may wish to see more of the Help system. With the
Assistant’s dialog box showing (click on the Help tool or on
the Assistant graphic) and select Option in the box.
Alternatively, right click on the Assistant and select the Option
item in the popup menu. In the resulting dialog box, remove
the check mark from Use the Ofice Assistant box. At this point
you may wish to look at the other options.
Restoring the Assistant is simple: click on Help and on the
drop down menu select the item Show the Office Assistant.
(e) Experiment with the tools on the Help toolbar - you can do no
harm! One of the tools hidedunhides one half of the Help
window. Did you find it? Open the Contents tab and explore
that part of Help. The Index tab is sometimes useful if the
Answer Wizard fails to locate your answer.
The Microsoft Excel Window 15
Problems It would take a large book to explain every item on the menu and
toolbars. There are two ways to find what various items do: using
Help and experimenting. These problems are designed to
encourage you to try both ways. You should also learn how to use
the Help command Index.
1. Using Exercise 4(b) as a guide, find the purpose of these
buttons.
Use Help on the menu bar to learn more about these buttons.
Using either the Help button or the Help command, find how
to hide a column.
2. Construct a worksheet with the misspellings as shown in the
figure below.
(a) Use the Spelling tool to correct the misspellings.
(b) Experiment with items on the Format menu to make the
text: (i) in A1 italic, (ii) in A2 centred in the cell, and (iii)
in A3 centred in the cells A3 and B3.
(c) Click the Undo button. What ha pens?
(d) Locate the Zoom tool ( d 100% v ) and experiment to find
its purpose.
(e) Find a menu command with the same purpose as the tool
just used.
(f) Locate the Borders tool and put a black border around
each cell in A1 :A3.
(g) Select Cl:C3 and experiment with the FormatlCglls
command to find how to draw a red border around the
outside of C 1 :C3.
(h) Select Al:C3 and experiment with the command
-Ed itlCleg-[Formatst o see what it does.
3. On a new worksheet enter 1234567.8 in Al. Observe how the
column width is automatically adjusted to accommodate the
number. Using the Decrease Decimals tool, format the cell to
show no decimal places.
I*oxI The Decrease Decimals tool
16 A Guide to Microsoft Excel 2002~foSr cientists and Engineers
In C1:Dl enter the words: Acceleration, Velocity. The first
word is truncated.
Use the mouse to drag the line between the C and D column
headers and widen column C to have the whole word
displayed.
4. Find out how to use the Office Clipboard. This has the
advantage over the Windows Clipboard in that it can hold
more than one object. You can even place a series of objects
on the Office Clipboard and paste the entire collection with
one command.
Basic Operations
Concepts
Note: Cell reference is the
correct term to use when
speaking about, for example, AI
or (320. You may find books that
use the term cell address.
However, this is a term that is
never used in Microsoft
documentation. It was used by
Lotus 1-2-3, a spreadsheet
application that preceded
Microsoft Excel. Surprisingly,
Excel does have a worksheet
function called ADDRESS that
returns a cell reference! Do not
use the term name in place of
reference; later in this chapter
we will learn that this term has a
very specific use in Excel.
This chapter introduces a number of features needed to prepare a
worksheet. We see how to fill a range with a series of numbers, use
a simple formula, copy a formula to a range, format numbers and
change column widths. The concepts of relative and absolute
references are examined. First we review some terminology.
Cell reference
This term has been used before; it is how we refer to a specific
cell. The reference for a cell consists of the letter for the column in
which the cell is located followed by the row holding the cell. The
top left cell in a worksheet has the reference A1 . To refer to the
cell A 10 on a worksheet named Data when that is not the current
sheet, we would use Data!Al 0. To refer to a cell in another, open
workbook a reference in the form [Book2.xls]Sheetl !A1 is used. A
reference to a cell in an unopen file requires the full path and the
file name. We may use 'C:\MyData\[Book2.~ls]SheetI'!Al for
example; note that the path and file name are enclosed in single
quotes.
Range
A range is a rectangular group of one or more cells. To reference
a range we use the top left cell reference separated from the bottom
right reference by a colon. Thus AI : B2 refers to the cells A1 , A2,
B1 and B2. Similarly, AI:A4 refers to the cells A1 to A4.
References may be made to ranges in other worksheets and files.
We may refer to the range 'C:\MyData\[Book2.~ls]Shee't!l AI :B4 for
example.
Active cell
The active cell is the one with a heavy black border. A cell must be
active if we wish to type data into it. A cell is made active by
moving the border with various keys. The key [wm)ove s it
down one row ]w-[+Ih-[ ile moves it up one row;
moves it right by one column and [Shift]+[TabS] moves it left one
column. The keys ~ l + ldo~ the~ salme . Of course, you can
move the mouse pointer and click on a cell to make it active. The
active cell is surrounded by the selection box which has afill
handle at the bottom right corner. The reference of the active cell
I8 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Note: The format in a cell may be
Exercise 1: Filling in
a Series of Numbers
is displayed in the name box of the formula bar. In addition, the
column and row headings for the active cell are enhanced.
Selection
If a cell is clicked and the mouse is moved while the button is held
down (dragging), a range may be selected. Figure 2.1 shows a
selected range. A range may also be selected with 1~~~~ while
holding down [Shift]W. hen a range is selected, all the cells other
than the active cell have a blue-grey background.
f-- Selection box
i t i
--- Fill handle
Figure 2.1
Formatting
The manner in which a cell entry is displayed may be changed by
formatting the cell. Frequently, we use formatting to change the
way a cell displays its numeric value. We may change the number
of decimals, show the value in scientific notation or with commas,
etc. Other formatting options are associated with the typeface and
the colour of the displayed entry. The horizontal and vertical
positioning of the entry within the cell is also a formatting feature.
In addition, cells and ranges may be given borders. In this book we
concentrate on formatting for practical rather than presentation
purposes.
For Exercises 1 to 3, imagine we have a laboratory heating
apparatus with a thermometer calibrated in degrees Fahrenheit. We
need a table to give approximate Celsius values. On completion of
the exercise, the worksheet will resemble that in Figure 2.2.
(a) Open a new workbook. Type the text in cells AI to B3 as in
Figure 2.2. Note that the text is automatically left justified.
Remember to pre 1s-1s to complete the entry in each cell.
You may also complete an entry by any of the methods
mentioned in Exercise 2 of Chapter 1 including clicking the
green check mark in the formula bar. The check mark is visible
only when you are entering or editing an entry.
Basic Operations 19
- The Right Align tool
The Save tool
(b) Select A3:B3 and click the Right Align button on the
Formatting toolbar. This will vertical align the text with the
numbers we are about to enter.
A 1 B I C
1 Temperature Conversion Table
I I
Figure 2.2
(c) Type the numbers 50 and 100 in A4 and A5, respectively.
(d) Rather than type the rest of the values we will use the AutoFill
feature. Select A4:A5 and move the mouse pointer over the fill
handle. The pointer changes from an open to a solid cross - see
Figure 2.3. Drag the fill handle down to A 13. Note the screen
tip showing the value in the last cell as you drag the mouse
down. The range A4:A13 will now have the values shown in
Figure 2.2.
The same result can be obtained using the command
-Ed itlFjlllSeries. More information on this convenient way of
getting data into a worksheet can be found in Help under Fill
in a series of numbers or Fill Series command.
(e) Save the workbook as CHAP2.XLS using either the menu
command FilelSave or the Save tool.
20 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Exercise 2: Entering
and Copying a
Formula
Correcting an error
going to make mist
learning Excel. Here a
quickly correcting then
1. If you start
existing entry, press
before the error is made.
2. You compl
rea.liz.e you ha
-Ed itlLJndo or the shortcut jCtrl4-Z to
go back one step.
3. If all else fails, selec
press [Delete] and start again.
A formula is an expression telling Excel to perform an operation.
Initially, we will limit ourselves to arithmetic operations. All
formulas begin with the equal symbol (=). To perform an
arithmetic operation we need to follow the = symbol with an
arithmetic expression. The expression may contain numeric values,
cell references and arithmetic operators. An example of a formula
is =AI +A2+A3 which returns the value resulting from the addition
of the values in the three cells.
If you were asked to compute the simple expression 3 + 4 x 2
would you reply 1 1 or 14? Most of us would give the former as
would Microsoft Excel. In Excel, and in other programs written in
all the major languages, an expression is evaluated following this
order of precedence for the arithmetic operators:
Negation (-)
Exponentiation (A)
Multiplication and division (*, /)
Addition and subtraction (+, - )
This precedence order may be overridden by using parentheses. So
2*5 + 6 (or 6 + 2*5) evaluates to 16 while 2*(5+6) evaluates to 22.
Note that -3"2 evaluates to 9 not -9 because negation has a higher
ranking than exponentiation so it is evaluated as 'the square of
negative 3' not as 'the negative of the square of 3'. To get the
negative value use -(3"2).
(a) On Sheet1 of the CHAP2.XLS workbook, in B4 type the
formula to convert Fahrenheit to Celsius, i.e. =(5/9)* (A4 - 32).
Do not overlook the equal sign. The spaces around the
operators are optional but they do make the formula more
readable. If you type lowercase letters for a cell reference,
Microsoft Excel automatically converts them to uppercase.
The cell displays the value 10. Ifyou make B4 the active cell,
you will see =(5/9) * (A4 - 32) in the formula bar.
(b) We need to copy this formula to B5:B12. If you are familiar
with Windows you will know how to copy and paste using the
-Ed it menu, shortcuts such as [Ctrll+C and ICtrl]+V, or the Copy
and Paste toolbar buttons. We will use another method. Select
B4, 'grab' the fill handle and drag it down to B13 to copy the
contents of B4 to these cells below it.
(c) Now we will explore yet another way to copy cells. Begin by
selecting B5:B 12 and pressing [oeletel to clear the entries in the
range. Place the pointer over the fill handle of B4 and double
Basic Operations 21
click. Excel detects that columns A and B form a table and
completes the Autofill. This trick works only with vertical
tables.
(d) Save the workbook by clicking the appropriate tool.
The values in B4:B 13 will be similar to those in Figure 2.2 but will
not be integer values. In the next exercise we format the results to
show integer values.
Notes on Copying Select B5 and look in the formula bar. This cell contains the
formula =(5/9)*(A5 - 32) while the formula we copied from B4 has
=(5/9)*(A4 - 32). When the formula was copied from B4 to B5,
Microsoft Excel automatically adjusted it to reflect its new
position, changing the A4 to A5. We need to understand the
concept of a relative reference. How does Excel interpret A4 in the
original formula in cell B4? Rather than ‘thinking’ that it needs the
value in A4, Excel ‘reasons’ along these lines: the formula is in B4,
so A4 refers to the value in a cell one to the left but in the same
row. When it copies the formula to B5, the A4 becomes A5 - one
cell to the left in the same row as the formula’s new location.
Formulas
Figure 2.4 shows the results of copying a formula to various other
cells. Cells Al:B4 contain values. The formula in C2 is =2*A1.
Relative to C2, the cell A1 is two columns to the left and one row
up. When C2 is copied to C4, the resulting formula is =2*A3 since
relative to C4 the cell A3 is two columns to the left and one row
up. Similarly, when C2 is copied to D3, we get =2*B2 since,
starting at D3, and moving two columns to the left and one row up
we arrive at B2.
=2*82
Figure 2.4
You should note that if a formula is moved, rather than copied, no
adjustment takes place. Later in this chapter we will explore
absolute referencing which allows us to write a formula in such a
way that copying will modify cell references such that only the
row, the column or neither is changed.
22 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Exercise 3:
Formatting
Resu I ts
Our project is almost complete. All that remains is to change the
way the results are displayed. For this project it is inappropriate to
display so many digits after the decimal; we need integer values.
(a) Select range B4:B13 on the worksheet of the previous
exercise. From the Format command on the menu select Cells.
In the resulting dialog box select the Number tab - see Figure
2.5. The General format will be highlighted in the Category
box. This is the default number format. In most cases, an entry
in a cell having the General format is displayed the same way
as it was typed. When the cell is not wide enough to show the
entire number, the General format rounds numbers with
decimals and uses scientific notation for very large and small
numbers .
the
(b) In the Category box, click the Number tab. Change the value
in the Decimal places box to 0. You may use the spinner or
type the value in the box. Click the OK button to close the
dialog box. Your worksheet now displays integer values.
Figure 2.5
(c) There is another way to do this. Click the Undo button on the
The Undo tool Standard toolbar to display the original values.
(d) Select B4:B 15 and click once on the Increase Decimals button:
Basic Operations 23
pJ The Increase Decimals tool
b"gl The Decrease Decimals tool
if you try to use the Decrease Decimals tool Excel will make
a ping sound to warn you this is impossible - B4 has an integer
value so it cannot be made to display any fewer decimal
places. All the numbers now display one decimal place. Click
the Decrease Decimals button once to display them all as
integers.
It is important to know that formatting changes only the way
a value is displayed. It does not change the actual value stored
in a cell. We look at this in Exercise 4.
(e) Save the workbook CHAP2.XLS.
Notes on Precision Numbers are stored with 15-digit precision by Microsoft Excel.
The number of digits displayed depends on the format and width
of the cell. If the user has not applied a format, Excel uses the
General format. A cell may be formatted to display a required
number of decimal places or to use scientific notation. We do this
with the command Fo-mat(Cgll(Numbero r by using the Increase
and Decrease Decimals button.
and Formatting
In the next exercise we demonstrate that formatting does not alter
the stored value. Later we will examine functions that round values
to a specified number of decimal places. You should also be aware
that when a cell is copied or moved, the target cell gets the same
format as the source cell.
Excel can store positive numbers as large as 9.99 999 999 999
999 x and as small as 1 x 10-'07. The range for negative
values is - 9.99 999 999 999 999 x 1 0+30to7 -1 x 1 O-307. The range
of values in Microsoft Excel is thus while a typical hand
calculator has a range of 1 O*99.
You should be aware that conversion from decimal to binary can
result in round-off errors. Suppose you perform two complex
calculations and expect A99 and B99 to have the same values.
Because of round-off errors, the two values may differ by a small
amount and the formula =A99 - 699 may not give exactly zero but
a value such as 0.000 000 000 000 008 or 8E-15. Just as in decimal
notation (base 10) the result for 10/3 cannot be written with infinite
precision as a real number, so in binary (base 2) there are some real
numbers that cannot be represented exactly.
24 A Guide to Microsoft Excel 2002 for Scientists and Engineers
A I B I C
I Displayed and stored values
Enter the values 27.05 and 26.1 in A1 and A2 of an empty
worksheet. In A3 enter =AI - A2 and the value 0.95 is displayed as
expected. Now we look at the actual value stored for A3.
Progressively increase the number of displayed decimal digits by
clicking the appropriate button on the Formatting toolbar. After a
while the value 0.94999 ... is displayed. Clearly there has been
round-off error since the result should be 0.95 exactly.
Programmers seldom test if two numbers are exactly equal but
rather they test if the absolute difference in the two numbers is less
than some arbitrarily small quantity.
D E
Exercise 4: Displayed The purpose of this exercise is to demonstrate that formatting
changes only the way in which a value is displayed. The stored
value is unaltered. When completed the worksheet will resemble
that in Figure 2.6.
and Stored Values
2 1 1
Figure 2.6
(a) Open the workbook CHAP2.XLS and click on the Sheet2 tab
to begin a new worksheet. Begin by typing the text in A 1 :C3.
Entering the text in B3:C3 presents a small problem. The equal
sign alerts Excel to use a formula but this is not what we want.
Before typing the equal sign, type a single quote (an
apostrophe) to tell Excel that you want text.
(b) In row 4 enter the following:
A4: the value 1.234.
B4: the formula =A4
C4: the formula =A4+2
D4: the formula =A4*2
(c) Using the Decimal tools, format A4 to display one decimal
place. The cells A4:D4 should show the same values as in
Figure 2.6.
Basic Operations 25
(d) Make A4 the active cell. The value displayed in A4 is 1.2 but
from the formula bar we see that the stored value is 1.234.
Unfortunately, one cannot see the applied format by looking
here. You may check how a cell is formatted by selecting the
cell and using the FgmatlCglllNumber command.
If we wish to have a value stored with a set number of decimal
digits, we use the ROUND function which is discussed in a later
chapter. It is possible to use the IoolslQption command to have
Excel use the same precision as the displayed value. This may be
useful in financial worksheets but is not recommended.
In the next part of the exercise we see an oddity of Microsoft
Excel. When a formula is typed into a cell which has not been
previously formatted (Le. it has the General format) and the
formula contains only (i) references to one or more cells with
identical formats and (ii) either no operator, or only the addition or
subtraction operator, then the cell with the formula gets the format
of the referenced cells.
(e) Type the text in A6.
(f) Enter the value 1.234 in A7 and format it to one decimal place.
(g) In B7 enter the formula =A7. The value 1.2 is displayed. Cell
B7 has taken on the format of A7 because the formula is a
simple reference to a formatted cell. We may format the cell to
restore the value of 1.234 if that is required.
(h) In C7 enter =A7+2. Again the value is displayed with one
decimal place. However, when =A7*2 is entered in D4 we get
2.468 -this cell does not take on the format of A7. If you use
a formula such as =B7+C7 in D7, the result will be displayed
with one decimal place since that is how B7 and C7 are now
formatted.
(i) Save the workbook.
Exercise 5: Formats In this exercise we see that with the Copy and Paste commands
both the values and the formats are copied. However, Excel 2002
provides a way to avoid copying formats. The worksheet will
resemble Figure 2.7 when the exercise is complete.
Get Copied
26 A Guide to Microsoft Excel 2002 for Scientists and Engineers
(a) On Sheet3 of CHAP2.XLS, type the text shown in Al:A5.
Type 12.555 in B3 and copy this to C3:E3 by dragging the fill
handle to the right.
U v L L L I I I U I I Y a v1 $ * X U LV"l.7 "11 I
Standard toolbar. But there are t7
more ways; you may find one
them more convenient
Paste action we could use the menu
Pnmm-mAo nv the tnnla nn the
XO
of
Keyboard shortc
[Ctrll+C and for Paste use [Ctrl/
(b) Format cells in B3:E3 to display the values as in Figure 2.7.
(c) Select B3:E3 and click the Copy tool. Make B5 the active cell
and click the Paste tool. Note that the values in the destination
cells are displayed with the same formatting as the source
cells.
The Copy operation places material on the Clipboard. To warn
you that this has occurred, an animated border (the 'ant track')
is placed around the copied material. While the ant track is
present you can paste the material on the same worksheet; on
another worksheet in the same or another workbook, or in any
open Windows document. When you perform any other action
in Excel, the material is removed for the Clipboard and the ant
track goes way. This is a safety precaution not used in other
applications; numeric material erroneously pasted into a
worksheet might not be spotted and could lead to incorrect
business decisions.
(d) Excel 2002 has a new feature associated with the Paste
operation: a Paste option smart tag appears. It resembles the
Paste icon on the toolbar but when the mouse hovers near it a
down arrow is added to the icon. Click the icon to open the
smart tag. It offers options to use the formatting from the
New Excel 2002 feature
Basic Operations 27
source or to match the formatting of the destination. Use Help
to learn about the other options when you know more about
Excel. Save the workbook.
Exercise 6: Too Many In this exercise we discover what to do when the value in a cell has
too many digits to display. There are a variety of ways to change
the column width to accommodate the value. Digits
(a) When we opened the worksheet that was to become
CHAP2.XLS it very likely had three worksheets since this is
the default setting. We need a new worksheet for this exercise.
Use the command InsertlWorksheet to make a new one. Now
look at the sheet tabs and locate the tab for Sheet4. It was
placed to the left of whichever was the current sheet when you
use the insert command. Clearly it is in the wrong place. Click
on the Sheet4 tab and drag it to the right of the Sheet3 tab to
locate it correctly.
(b) Enter the value 123.456 in Al.
(c) Using the Increase Decimal icon, increase the number of
decimal digits. After a few clicks the value has filled the cell
and Microsoft Excel automatically widens the cell to keep pace
with the number of characters displayed. There is, however, a
limit; a cell cannot hold more that 256 characters. Furthermore,
do not be misled by all those zeros! Microsoft Excel stores
numbers to a precision of 15 digits. Adjust the value to about
8 decimal places.
(d) Place the cursor in the column headings and position it on the
divider between the A and B headings. The cursor will change
to a new shape - see Figure 2.8. Drag the column divider to the
left making column A narrower. The cell now displays
########## indicating that the cell is not wide enough to
display the value with its current format.
Figure 2.9
(e) In the last step we changed the width of a column but we have
28 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Shortcut: You can also open the
Format dialog box fi-om the popup
menu that appears when you right
click in a cell.
Shortcut: To change a column
width to accommodate the widest
entry in that column, double click
on the divider to the right of the
column header.
Exercise 7:
Ca I cu lat i o n Example
no idea what the actual width is. This time we will change the
width to a specified size. With A1 as the active cell, use the
command Fo-rmatlColumnlWidth. In the Column Width dialog
box (see Figure 2.9) enter the value 8.43. This is the default
column width with Aria1 font of size 10 or 11 points and is
large enough to display eight digits.
(f) The cell may still display #######. Use Format(Cgl1t o display
the value with 3 decimal places. It should now fit the column.
(g) In A2 enter the value 12345678912. This time Excel does not
expand the column to accommodate all the digits because the
column has been given a fixed width. Rather, Excel displays
the value in scientific format as 1.23E+10 which is to be
interpreted as 1.23 x 10".
Microsoft Excel behaves differently with text entries. If you type
a text entry with more characters than the cell can hold, the entry
will overflow into the cells to the right provided they are empty.
(h) Type Sample heading, in B 1. Both words are readable but
much of the second overflows into column C. Now type
Another heading in C1. Most of the characters of the second
word in B 1 are now lost. B 1 is not wide enough to display its
contents and text overflow is not permitted now that C1 is
occupied.
(i) We now experiment with another way of widening a column.
With B1 and C1 selected, use the command FormatlColumnl
-Au toFit Selection. The two columns are made exactly wide
enough for B 1 and C 1 to hold their contents.
The AutoFit command may be used with any type of data, numeric
or textual. If we select the column headings rather than a range, the
AutoFit command makes the columns the correct width for the cell
in each column with the greatest need for space.
(i) Save the workbook.
Once a worksheet has been set up to solve a problem, it may be
used repeatedly for the same type of problem but with different
input values. For example, if you had one quadratic equation to
solve, it might not be worth the effort to design a worksheet to do
it. If you had a dozen or so equations to solve, then a worksheet
Basic Operations 29
solution would be more efficient than using a pocket calculator.
Other advantages of the worksheet are (i) the ability to see what
values you have used and (ii) the facility to modify the calculation
without re-entering all the data.
In this exercise we will design a worksheet to compute the
effective resistance of four resistors in parallel. The four resistors
(RI, R2, R3 and R4) in Figure 2.10 have the equivalent resistance
value of the single resistor (Re) whose value is determined by the
relationship shown in the figure.
3 Resistors 1IR

(a) Using the method in the last exercise, insert Sheet5 in the
correct position on the workbook CHAP2.XLS.
(b) Enter only the text and values shown in A1 :A1 0 and B 1 :B3 of
Figure 2.10.
(c) In B4 enter the formula =1/A4. Copy this formula to B5:B7 by
either dragging the fill handle of €34 or double clicking on B4's
handle of I34 causes the formula t fill handle.
be copied down the table.
(d) The formula in B9 is =B4+B5+B6+B7, giving the value 1/Re.
Remember you may add spaces around the addition operators
if you wish. Later we shall use a function to evaluate a
summation like this.
(e) The formula in B10 is =1/B9 to give the value of Re.
(f) Test your worksheet with the values 2,2,4,4. Since 1/Re will
be % + 'h + % + % or 1%, your worksheet should give Re as
0.666667. Whenever possible, check a new worksheet with a
30 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Exercise 8: Entering
Formulas by Pointing
few manual (or mental) calculations. Save the workbook.
While our worksheet is able to compute the equivalent resistance
of any four resistors in parallel, it cannot be used for fewer. If we
enter 0 in A7 (for example), Excel will return the error value
#DIV/O! in B7. The same error value will be displayed for all
formulas that use B7. Incorporating an IF function (see Chapter 5 )
in the formulas used in B4:B7 would make the worksheet much
more versatile.
What we did in A4:B 10 is similar to how we would manually solve
this problem with paper, pencil and calculator, writing down every
intermediate result rather than using the calculator’s memory.
Wherever there is repetition (e.g. calculating the reciprocals in this
example), the worksheet method simply requires us to copy
formulas. These two points may help you design your own
worksheets.
In this exercise we look at an alternative method to typing cell
references when building a formula. When you type a cell
reference you must take care to use the correct address. With
larger, more complex worksheets, it is easy to make a mistake. The
alternative method is to point to the cell with the mouse. It is akin
to saying ‘use that one’.
For the problem in Exercise 7, clearly we could combine steps (d)
and (e) and compute the value of the effective resistence in one
formula: = 1/( B4+B5+B6+B7). The parentheses are essential.
(a) In A12 enter the text Re.
(b) In B 12, begin the formula by typing =1 I(. Now left click on cell
B4 and observe the result in the formula bar - the formula is
now =Bl4/. (Ty pe the plus sign and click on B5. Continue until
the formula reads =1/(84+85+B6+87 and click on the green
check mark in the formula bar.
(c) If you have entered everything correctly, Microsoft Excel
politely points out that there is a small error and offers to
correct it by adding the closing parenthesis. Click the Yes
button of the dialog box.
(d) Double click on B12 and note the status bar now reads Edit
rather than Ready. But the more obvious change is the Range
Basic Operations 31
Finder feature which causes the cells and ranges to which the
formula refers to be displayed in colours, and matching colour
borders to be applied to the cells and ranges referenced in the
formula. This provides a convenient graphical way for us to
check formulas. Save the workbook.
Exercise 9: In Exercise 2 we saw that Excel normally treats cell references as
relative references when a formula is copied. There are times when
References: Relative’ this is not what we need. If the formula refers to the cell A1 we
Absolute and Mixed may modify the reference by adding one or more $ symbols.
Reference Result when formula is copied
=A1 the row and the column may change
=A$1 the row remains constant, the column may change
=$A1 the column remains constant, the row may change
=$A$l both the row and the column remain constant
A cell reference in the form A1 is called a relative reference while
$A$1 is called an absolute reference. The forms $A1 and A$1 are
mixed references. Remember that when a formula is copied to the
same row, the row reference is unchanged without the need for the
$ symbol. Similarly, when a formula is copied to the same column,
the column reference is unchanged without the $ symbol.
To demonstrate the use of mixed references, we will develop a
simple worksheet that displays a multiplication table as shown in
Figure 2.1 1.
Shortcut: To enter a series of
I A I B l C l D I E l F I G 1 H I I I J
11 I 21 31 41 51 61 71 SI 91 10
Figure 2.11
(a) Insert Sheet6 in the workbook CHAP2.XLS. Start by entering
the values 2 and 3 in B 1 and C 1. Use the Series Fill method to
complete the row. Enter the data in column A in a similar
manner.
32 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Exercise I O : Editing
and Formatting
Note: We could, of course, use the
equivalent formula:
=( B4 * B8)/(A9 - E4)- D4/(A9/'2).
(b) In B2 we need a formula to compute A2 x B 1. If we use
=A2*B1 we will not be able to copy it. What we need is
=$A2*B$l. The $ before the A in the first term ensures that,
when the formula is copied across the worksheet, the reference
will always be to that column. Similarly, the $ in the second
term keeps the reference to row 1 constant when the formula
is copied down the worksheet.
The $ signs may be typed as you enter the formula but we shall
use another technique. Type =A2 to start the formula. Now
press [F4) repeatedly until the formula reads =$A2. Next add
*B1 to the formula and again use IF4] to make the formula read
=$A2*B$l.
(c) Copy B2 to B2:JlO.
(d) Examine the values and the formulas in a few cells to make
sure you understand the process.
(e) Save the workbook.
In this exercise we construct a table to display the pressure of a gas
at various temperatures and volumes using the van der Waals
equation:
p=---R T a
V-b V2
We would like to be able to change the values of a and b so that
our table may be used with different gases, so we will place these
values in their own cells rather than in the formulas. We will also
place the value of the gas constant R in a cell to provide
documentation - and to allow us to change it quickly if we use the
wrong value. The final worksheet will resemble that in Figure 2.12.
Do not worry about the negative pressure value - the gas has
condensed under these conditions and the equation is not really
applicable.
(a) Open the workbook CHAP2.XLS and move to Sheet7. Type
the text shown in rows 1 to 7 of Figure 2.12. In C4 type C02.
Enter the values in B4:E4. We will format the cells (centring,
subscript, etc.) later.
Basic Operations 33
(b) Enter the values in B8:H8 and in A9:A
method.
8 using the Series Fill
(c) In B9 enter the formula =(B4*B8)/(A9 E4)-D4/(A9*A9). The
pointing method from Exercise 8 would be appropriate here.
Click the check mark in the formula bar when the formula is
complete. The cell should show the value 1374.21. Correct the
formula if needed. Examine the formula making sure you
understand how it computes the pressure for V= 0.05 litres and
T= 250 K.
Figure 2.12
We need to modify the formula in B9 before copying it to the range
B9:H18. There are three considerations: (i) In the formula, B4
refers to the value of the gas constant, D4 to the a constant and E4
to the b constant. These references must not change when the
formula is copied; (ii) On the other hand B8 refers to the
temperature and we have a range of these in row 8. When the B9
formula is copied, the reference to B8 must still refer to row 8 but
the column must change; we need to replace B8 by B$8; and (iii)
The references to A9 must continue to point to the volume values
in column A but as the formula is copied the row must change. So
we need to use $A9.
In summary, we need to edit the formula to read =($B$4*B$8)/(A$9
- $E$4) - $D$4/($A9*$A9). We shall not retype the formula, rather
we shall edit the existing one. We shall do this in a series of steps
to illustrate the various options that are available.
34 A Guide to Microsoft Excel 2002 for Scientists and Engineers
HMerge and Center tool
- Center Align tool
(d) Move to cell B9 and enter editing mode by either double
clicking or by pressing [.
(e) We have a variety of options for editing the formula. In cell
B9, move the mouse pointer in front of the reference to B4 and
click to position the insertion point. Type the $ symbol. Move
the insertion point in front of the $ in B4 and type another $
symbol. Now move the insertion point between the B and the
8 in B8 and insert a $ symbol.
(f) Next move the insertion point anywhere within the first
reference to A9 -you may have the insertion point in front of
the A, between the A and the 9, or just after the 9. Now press
[F41 repeatedly and watch the reference cycle through the
values $A$4, A$4, $A4 and A4. Stop when you have the
required value of $A9. Use the same technique to change E4
to $E$4. Click the green arrow in the formula bar to complete
the entry and return to ready mode.
(g) We have not completed the editing so we will re-enter the edit
mode and explore another way of editing. This time we will
activate the edit mode by pressing m. It has the same effect
as double clicking but some users find it more convenient. For
variety this time, rather than doing the editing in the cell we
will do it in the formula bar. We need to replace D4 by $D$4
and A9*A9 by A$9*A$9. Use whichever method you prefer:
typing the $ or using m. Click on the green arrow in the
formula bar when you have completed the task.
(h) B9 should now read: =($B$4*B$8)/($A9- $E$4)- $D$4
/($A9*$A9). Copy it to row 18 and column H. Check that your
values agree with those in the table. If they do not, you may
need to edit B9 and recopy it.
(i) Now some formatting to improve the appearance of some cells.
(i) Format the range B9:H18 to display two decimal places.
(ii) Select AI:Hl and centre the van der Waals ... text over
these cells using the Merge and Center button. Use the
second item in the Formatting toolbar to increase the size
of the font to 14. Click the Bold button on the same
tool bar.
(iii) Select B3:E4 and centre the entries with the Center button
on the Formatting toolbar.
(iv) Using the technique in (i), centre the text in A6 over
columns A:H and the text in B7 over columns B:H.
Basic Operations 35
(v) In C4 we have C02 but wish to have CO, with the ‘2’ as
a subscript. Select C4 and start the edit mode. Highlight
the 2 and, from the menu bar, select Foymat(Cel1.s. Using
the Effect portion in the resulting dialog box (lower left),
click on the Subscript box to place a J in it. Click the OK
button.
(j) We will do more with this worksheet later. Giving the
worksheet a name other than Sheet7 will help us locate it.
Right click the sheet’s tab, select Rename from the menu and
type the name VanderWaals. Save the workbook CHAP2.XLS.
Exercise 1 1 : What’s In the exercises so far, we have constructed formulas that use other
cells’ values by using cell references. It is possible to give a cell,
or a range of cells, a name. The advantages of this are twofold:(i)
it is easier to remember where a value is stored if the cell has a
name and (ii) names are always treated as absolute references.
in a Name?
In this exercise we use names with one letter. This is not a
requirement, we may use a name such as Gasconstant. Note that
names are not case sensitive so GASCONSTANT and Gasconstant
are treated as the same name. When single letters are used, R and
C are invalid since Excel uses these letters for other purposes.
Similarly, we may not use a name which could be a cell reference.
However, we may add an underscore to create names such as R-,
c-, X I , etc.
(a) Open CHAP2.XLS and move to the VanderWaals worksheet.
Delete the range B9:H 18.
(b) With the range B3:E4 selected use the command
InsertlBamelCreate. A dialog box similar to Figure 2.13
appears. Excel has detected text in the top row and values in
the lower one, so it correctly assumes that you wish to apply
the names in the top row to the corresponding cells in the
lower row. Click OK.
(c) Move to I1 and use InsertlNamelPaste. In the resulting dialog
box (Figure 2.1 1) click Paste List. This lets us check what
names have been assigned to what cells. Note that B4 got the
name R- not R. Delete the list or press the Undo button.
(d) In cell B9 type the formula =(R-*B$8)/($A9 - b) - a/( $A9 *
$A9). Check its value and copy it to B9:HlS.
36 A Guide to Microsoft Excel 2002 for Scientists and Engineers
(e) Save the worksheet.
A ' R c.- ' D E I F G '
van der Waals Equation of State
R Gas a b
008206 C02 359 00427.
I i I
Figure 2.13
We used a semi-automatic method to create names for some cells.
We may also select a single cell and use ZnsertlEamelDefine to
name a specific cell.
There are a number of ways of referencing a named cell (or range)
in a formula. To enter, for example, R- in a formula we may (i)
type the name R-, (ii) point to the corresponding cell with the
mouse, and (iii) use the command lnsertlN_amelPastea, nd select the
required name and click the OK button or simply double click on
the required name.
We began this exercise by deleting the range B9:HlS. We did this
to show how to build a formula with names. Alternatively, after we
had created the cell names, we could have used the command
InsertlN_amelApply to replace cell references by cell names.
A named cell or range may be referenced in any sheet of the same
workbook. It is possible to use the same name for two cells (or
ranges) in different worksheets of the same workbook. Suppose a
cell in Sheet1 is named Mass, and a cell in Sheet2 has the same
name. A reference to Mass in Sheet 1 will automatically refer to the
Basic Operations 37
cell in that worksheet. If, in Sheet2, you need to reference the Mass
cell of the other sheet, you would use SheetI!Muss. If we move to
Sheet3, where no cells are named, what would a reference to Mass
mean? Generally, it would refer to the cell in Sheet 1 since this was
first named. However, it would be safer to qual@ the name using
either Sheetl!Mass or Sheet2!Mass as required.
Exercise 12: Custom In Exercise 3 we discovered how to format numbers. We may, for
example, arrange to have three decimal places displayed, or to use
scientific notation. Excel also permits the user to develop custom
formats. To demonstrate this we will format numbers so that the
exponent is always a multiple of three - the engineering notation.
Formats
Figure 2.14
(a) Open CHAP2.XLS and insert a new worksheet. You may need
to drag the sheet’s tab to the right and correctly locate it.
(b) Enter the text shown in A1 :B3 of Figure 2.14. Enter the values
shown in A3:A9. When you enter the large number in A9,
Excel will display it in scientific notation; change this to the
number format with two decimal places.
(c) In B3 enter =A3 and copy this to C3 by dragging the fill handle
to the right.
(d) Select B3 and format it to scientific notation with two decimal
places. Copy B3 down to B9 by double clicking its fill handle.
(e) Select C3 and open the Format dialog either by using the
command FormatlC& or by right clicking the cell and
selecting the Format Cells item in the popup menu.
38 A Guide to Microsoft Excel 2002 for Scientists and Engineers
J
Figure 2.15
(f) Open the Custom tab on the dialog - Figure 2.15. Place the
cursor in the Type box and backspace out whatever is there
already. Do NOT use the Delete button on the dialog box to do
this.
Type in the custom format in this form: ##O.OOE+O;
-##O.OOE+O; 0. A custom format has three parts separated by
semicolons. The first part specifies the format for positive
values, the second for negative values, and the third for zero.
By limiting the number of digits before the decimal to three we
have forced Excel to use the engineering notation where the
exponent is a multiple of three. Unfortunately, the result is not
very pleasing for values less than 1000. We may display as
many digits after the decimal as we wish. If you wish not to
have zeros displayed use I' for the third part of the format.
(8) Double click on the fill handle of C3 to copy the formula down
to C9. Save the workbook.
Basic Operations 39
Exercise 13
and Such
,: Symbols We may need to use symbols and Greek letters in table headings so
we need to know how to get such text as Temp OC and AV=x?Ah
into cells. The reader is encouraged to investigate these ways of
obtaining such results.
(a) Certain symbols are readily generated by holding down the
key and entering a four-digit code on the number pad (it
must be the number pad, not the digits on the top row of the
'typewriter' keys). The first two rows of Figure 2.16 show
some useful symbols. Thus (Anl+O I 77 gives f, the plus-minus
symbol.
This table is most easily made by entering the numbers in the
second row, typing the formula =CHAR(A2) in A3 and copying
it across the row. So we may have a cell display "C either by
typing mAt0186 followed by C, or with the formula
=CHAR(186)&"C". The former is, of course, more convenient.
Note that we can use this method to get superscripts in text (as,
for example, R2 and m3) but we can also get these by
formatting a normal digit as superscript - see step (i) of
Exercise 10.
~~~
Figure 2.16
(b) Greek letters are obtained with the Symbol font. Suppose you
want a cell to display AV. Begin by entering the two letters
DV. Now either select the D in the formula bar, or double click
the cell to enter edit mode and select the D in the cell. Next
change the font of the selected letter. This may be done with
the command Fgmat(Cg1ls and opening the Font tab. But it is
more convenient to use the Font box on the Formatting toolbar.
Remember that there is no need to scroll down the list, typing
40 A Guide to Microsoft Excel 2002 for Scientists and Engineers
S will jump to the first font beginning with this letter.
The formula bar does not display the formatted value. When
you select a cell having some or all the characters formatted in
Symbol font, you will see Roman (regular) characters in the
formula bar generally in Aria1 font.
New Excel 2002 feature
(c) If you are familiar with Word you will know that one can use
InsertlSymbol to get symbols and Greek characters into a
document. Until the latest version, Excel did not support this
feature. However, one could use the Windows procedure
(StartlProgramsIAccessories)CharacterM ap) to copy symbols
to the clipboard for pasting into a cell. With Excel 2002 one
has access to the InsertlSymbol dialog. Indeed, it is a much
improved feature - see Figure 2.17.
Exercise 14:
Fractions
Figure 2.17
From Figures 2.16 and 2.17 we can see that it is possible to enter
fraction symbols into cells. It is important to realize that fractions
entered in either of these ways are just symbols. They are not
numerical values and cannot be used in mathematical operations.
Functional fractions are possible. A cell can display, for example,
2 1/4 but its stored value is, of course, 2.25 -the display results
from formatting.
Basic Operations 41
3
4
If you mistakenly type 1/8 in A4,
3.5 2 7
Total 28
Excel Will assume you want
(I August or 8 January dep
on your regional settings).
the entry and use EditlClearlEo
to remove the date formatting.
Natural Language
Formulas
The so-called natural formula
feature is mentioned
the reader finds a re
elsewhere. Your authorrespectfbl
advises you not to use this feature
The option Accept Iahels in
that was active
and to all new
created when the option is in e
(a) In Figure 2.18 the entry in A3 was made by typing 2 114 - note
the space between the integer and the fraction. The entry in A4
was made by typing 0 118 - Excel does not display the leading
zero. Select each cell in turn and observe the values displayed
in the formula bar; they will be decimal values since that is
how Excel stores the numbers.
2 1/2
1/16
Figure 2.18
(b) The formulas in B3 and B4 are =A3*2 and =A3/2, respectively.
These are copied down to row 4. It is most likely that C4 will
initially display 0. Open the Format dialog and select the
Fractions category to adjust this.
One of the most hailed new features of Excel 97 was called natural
language formulas. This allows the use of column or row labels in
formulas without creating names. For example, with the worksheet
shown in Figure 2.19, the natural language feature allows you,
without going through the process of creating names, to use
formulas such as =Density*Mass in C2:C3 and =SUM(Mass) in C4. s Density Volume
Figure 2.19
This feature proved to be a mixed blessing and, while it was
retained in later Excel versions, it is switched off when you first
install the product. It is controlled by the box labelled Accept
labels in formulas in the Calculations tab (ToolslQptions). The
reader is encouraged to leave it switched off and to avoid using this
feature since it can easily lead to errors.
42 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Problems 1 .* On a new worksheet enter these numbers: 1, 2, 3 and 4 in
A1:DI. These represent the values of w, x, y and z,
respectively. Do not name the cells for this problem. In row 2
enter formulas to compute the following. Check the results.
(b) x2 + w
(a) 2w-y
w+x
( 4 -
Y-=
2.* Construct a formula to find: (a) the square root of the value in
D1, without using the SQRT function which we look at in the
next chapter, (b) the cube root of D2, and (c) the reciprocal of
D3.
3. Use the pointing method on the worksheet used for Exercise 7
to build these formulas:
=II/A/4(+ 1/A5+1/A6+1/A7) and
Do you get the same, correct result?
=(l/A4+1/A5+1/A6+1/A7)"-1
4. Some of the formatting commands are available on the popup
menu that appears when you right click on a cell or a column
(or row) heading. Experiment!
5. If P dollars/pounds are invested in a savings account with an
interest rate of R per year, compounded A4 times a year, then
at the end of N conversion periods the accumulated amount is
given by A, = P(I + WwNC. onstruct a table showing the
accumulated amount for annual interest rates of 5, 6, 7, 8, 9
and 10% with interest compounding monthly, quarterly and
semi-annually. Namedcells and formulas with mixed absoluterelative
references will work well here.
6. The data validation feature (accessed using PataIValidation
from the menu) permits you, for example, to require only
integers in the range 1 to 10 in a specified cell. Insert a new
worksheet in the CHAP2.XLS workbook and experiment with
this feature.
Printing a Worksheet
Concepts Sooner or later you will wish to print your Microsoft Excel
worksheet so we will examine this topic now although we have
hardly scratched the surface of Excel. In this chapter we will see
how to print all the used area or a selected part of a worksheet. We
will also explore various options such as changing the header and
footers on a printed page, removing the gridlines, having the
column and row headings in the printout and making the selected
print area fit one page of paper.
Exercise 1 : A Quick (a) To begin this exercise we will open the file which was saved
in the previous chapter. Open the File menu and look at the
bottom. Excel saves the names of the user's last four
workbooks. Click on CHAP2.XLS to open your workbook. If
you are working on a network, or if you have worked on other
files since doing the exercises in Chapter 1, the name of this
file may not be present. Use the menu item FilelQpen to locate
the appropriate folder and select CHAP2.XLS. You may wish
to type your name in an empty cell such as D4 if you are using
a network environment.
Way to Print
H T h e Print tool
(b) Click on the first sheet tab to select the worksheet where you
made the temperature conversion table.
(c) Click on'the Print button on the Standard toolbar and retrieve
your printout. Note that the printed area is AI :D14 since this
is the area containing data.
(d) Inspect your worksheet screen. There will be a vertical and a
horizontal dotted line. The exact positions depend on the
margin setting (we look at these later). Generally, the vertical
line runs between columns I and J and the horizontal one
between rows 5 1 and 52. These lines show you what data will
fit on a single page in the printout.
44 A Guide to Microsoft Excel 2002 for Scientists and Engineers
Exercise 2: Another This exercise uses the menu to print a worksheet. To demonstrate
how this is more versatile than the Print button, we will print just
a part of the worksheet. Way[/english]
------------------------------------------------------------------------------------------------
لو سمحتم أخوتي الأعزاء اشرحوا طريقة وضع رابط تحميل ضمن الموضوع Upload لأن تحميل ملف الكتاب أفضل أكيد من هذه الطريقة .
-----------------------------------------------------------------------------------------------

daherstar,
شكراً الك وعلى الأهتمام بس وين الرابط وكمان كأنوا ها ناسخ الرابط من عندك من مكان مانك حطط الرنامج شكراً على كل حال

شكرا اخي على الموضوع
اذا بدك تحط الكتاب كرابط افتح شي موقع upload
و حمل الملف عليه ... و عطينا الرابط في كتير مواقع للرفع منن
http://www.tntup.com" onclick="window.open(this.href);return false;
و غيرهم كتير
وشكرا الك على الموضوع و يا ريت تحطلنا الكتاب كامل
وشكرا معلم

شكرا كتير عالموضوع و هي رابط تاني لموقع لرفع الملفات : http://www.fileflyer.com/" onclick="window.open(this.href);return false;
هاد الموقع أنا مجربتو كتير من قبل لأنو كتير سهل