Spreadsheets with Excel

129 Pages·2011·3.528 MB·English

by  Stephen Morris

Spreadsheets
with Excel
Learning Made Simple
Stephen Morris
AMSTERDAM • BOSTON • HEIDELBERG • LONDON • NEWYORK • OXFORD
PARIS • SANDIEGO • SANFRANCISCO • SINGAPORE • SYDNEY • TOKYO
Butterworth-Heinemann is an imprint of Elsevier
http://avaxhome.ws/blogs/ChrisRedfield
Butterworth-Heinemann is an imprint of Elsevier
Linacre House, Jordan Hill, Oxford OX2 8DP, UK
30 Corporate Drive, Suite 400, Burlington, MA 01803, USA
First edition 2006
Copyright © 2006 Elsevier Ltd. All rights reserved
The right of Stephen Morris 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
Notice
No responsibility is assumed by the publisher for any injury and/or damage
to persons or property as a matter of products liability, negligence or
otherwise, or from any use or operation of any methods, products,
instructions or ideas contained in the material herein.
TRADEMARKS/REGISTERED TRADEMARKS
Computer hardware and software brand names mentioned in this book are
protected by their respective trademarks and are acknowledged
British Library Cataloging in Publication Data
A catalogue record for this book is available from the British Library
Library of Congress Cataloging in Publication Data
A catalogue record for this book is available from the Library of Congress
ISBN-13: 978-0-7506-8185-8
ISBN-10: 0-7506-8185-3
For information on all Made Simple publications
visit our website at http://books.elsevier.com
Typeset by Butford Technical Publishing Ltd, Birlingham, Worcs.
Printed and bound in Italy
.Contents
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .vii
1 First steps 1
About Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2
Screen components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
The worksheet grid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
Moving around . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Saving the worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . .6
Loading a worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
The worksheet window . . . . . . . . . . . . . . . . . . . . . . . . . .10
Getting Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Closing a file . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
Closing Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14
2 Working with cells 15
Entering data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16
Display formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18
Simple editing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20
Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22
Freezing titles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24
Toolbars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25
Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26
User options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28
iii
3 Using formulae 29
Entering a formula . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30
Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32
Using brackets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33
Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34
Excel functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36
Inserting a function . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38
The IF function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40
Copying formulae . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42
Relative and absolute . . . . . . . . . . . . . . . . . . . . . . . . . . .44
Moving cells and ranges . . . . . . . . . . . . . . . . . . . . . . . . .45
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46
4 Sheet formatting 47
Columns and rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48
Creating gaps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50
Deleting rows/columns . . . . . . . . . . . . . . . . . . . . . . . . . .51
Hiding rows/columns . . . . . . . . . . . . . . . . . . . . . . . . . . .52
Checking text . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
Sorting data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56
5 Formatting cells 57
Number formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .58
Decimal places . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60
Displayed values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61
The Currency format . . . . . . . . . . . . . . . . . . . . . . . . . . . .62
Dates and times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Alignment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66
Changing fonts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68
Borders and patterns . . . . . . . . . . . . . . . . . . . . . . . . . . .70
iv
Copying formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
Protecting data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73
Format styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .74
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .76
6 Workbooks 77
Multiple worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . .78
Starting a new sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . .80
Changing worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . .82
Formulae in workbooks . . . . . . . . . . . . . . . . . . . . . . . . . .84
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .86
7 Charts and graphs 87
Chart Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .88
Editing the chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94
Changing chart data . . . . . . . . . . . . . . . . . . . . . . . . . . . .96
Chart sheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .98
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .100
8 Printing 101
Page set-up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102
Margins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .103
Headers and footers . . . . . . . . . . . . . . . . . . . . . . . . . . .104
Sheet settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105
Preview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .106
Print options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
v
9 Macros 109
Recording macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
Editing macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
Adding buttons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .114
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
vi
.Preface
The books in the Learning Made Simple series aim to do
exactly what it says on the cover – make learning simple.
A Learning Made Simple book:
(cid:2) Is fully illustrated: with clearly labelled screenshots.
(cid:2) Is easy to read: with brief explanations, and clear
instructions.
(cid:2) Is task-based: each short section concentrates on one
job at a time.
(cid:2) Builds knowledge: ideas and techniques are presented
in the right order so that your understanding builds
progressively as you work through the book.
(cid:2) Is flexible: as each section is self-contained, if you
know it, you can skip it.
The books in the Learning Made Simple books series are
designed with learning in mind, and so do not directly follow
the structure of any specific syllabus – but they do cover the
content. This book covers Module 4 of the ECDL syllabus and
the Spreadsheets and Graphs aspects of New CLAIT. For
details of how the sections map against your syllabus, please
go to the website:
http://www.madesimple.co.uk
vii
Acknowledgments
I would like to thank Natalia Zak for her hard work during
the production of this book.
viii
1 First steps.
About Excel . . . . . . . . . . . . . . . . . .2
Screen components . . . . . . . . . . .3
The worksheet grid . . . . . . . . . . .4
Moving around . . . . . . . . . . . . . . .5
Saving the worksheet . . . . . . . . .6
Loading a worksheet . . . . . . . . . .9
The worksheet window . . . . . . .10
Getting Help . . . . . . . . . . . . . . . .11
Closing a file . . . . . . . . . . . . . . .12
Closing Excel . . . . . . . . . . . . . . .13
Exercises . . . . . . . . . . . . . . . . . .14