Contents

Contents 

Happy Computers' Telephone Help-Line

More about the help-line

The Happy Computers' Web Site

About this manual

Who is it for and how to use it

What do the icons mean?

Getting Help

The Office Assistant

Getting Started in Excel

What can you use Excel for?

Starting and closing Excel

The Excel 2000 Screen

The Mouse keeps changing shape

Moving around the spreadsheet

Entering text and numbers

Correcting your mistakes

Undo and Redo - A licence to make mistakes 

Saving your Workbook

Closing and Opening your Workbook

AutoFill

Selecting parts of a spreadsheet

Inserting and deleting columns and rows

Cut, copy and paste

Drag and Drop

Changing the zoom control

Freeze Panes

The Basics

Using AutoSum

Formulas

Creating a formula

Using paste function

Using percentages

Copying formulas

What are absolute cell references?

Creating and using absolute cell references 

Printing your spreadsheet

Making a Spreadsheet Look Good

Using AutoFormat

Basic Formatting

Changing the alignment

Changing the number formats

Changing the date format

Changing the column widths

Adding Borders

Adding shading

Format painter

Getting a Print Preview

Changing the page set-up

Headers and footers

Creating Charts

Creating charts

Moving, re-sizing and deleting charts

Selecting parts of a chart

Using the chart toolbar

Formatting the chart

Printing the chart

Which chart shall I use?

Design Principles

The Golden Rules of Good Spreadsheet Design

Range Names

Creating range names

Selecting a named range

Printing a named area

Using range names in formulas

Protection

Protecting your sheet

Putting a password on an Excel Workbook

Appendices

What do all the icons do?

What do the error messages mean?

Glossary

Index

Happy Computers' Telephone Help-Line

Our service does not end when you leave our training centre. For two years from the day of your course you will be able to use, free of charge, our telephone help-line. The aim is to give you the backup to ensure you can confidently do what you covered on your course.

The helpline staff are happy to help out - but please note that the support we can offer is based on the course you attended.
If your question goes beyond the level of the course you attended it is up to the discretion of the Helpline person whether they answer it.  We will always try to point you to another source of help if this is the case.  Please note that the Service Level Agreement cannot be guaranteed in this instance and other calls to the Helpline may take priority over your own."

We want to hear from you. The aim of our courses is to leave delegates confident in using the software. If you have difficulty with any aspect of what you covered on the course, we want to know about it and we want to help you through it.

Your help-line questions also help us. We find out how you use the software, the problems you hit, and sometimes, bugs we don't know about. All this helps to improve our courses and our service. So please keep those calls coming.

You can ring the help-line if you sat on a Happy computers course and for anything covered on that course for up to two years, even if you have changed jobs since doing the course.

It is a guarantee of the quality of our training, so we don't extend it to anyone else in your organisation who has not been trained by us.

(Though ring us if you would like to arrange cover for holidays or sickness)

Access, Excel and web design:  please note that we can't undertake re-design work.  If your database, spreadsheet or web site isn't working because it has been built incorrectly (design faults), we can advise where the problem may lie but we can't do the work for you, I'm afraid.

Outlook: Our training courses use Outlook on an exchange server.  Exchange server can be configured in many different ways,  and you may not be using exchange server at all.  Due to these differences, the menus and other options in Outlook can be very different.  We cannot be responsible for issues that arise due to these differences.

VBA and Javascript:  Sorry, but we won't be able to write code or de-bug yours if it isn't working (unless you are basing your code on an example from our manual). However we may be able to offer you advice on how to change your existing code or point you to VBA resources. Hope that sounds fair.

The help-line hours are 9.30am - 5.00pm. The target speed at which we can resolve your problem depends on which course you have done with us:

Microsoft Excel 2000 Essentials is a category A course

If you have difficulty getting through please contact Henry Stewart, Managing Director of Happy Computers on 020 7375 7300.

Other sources of help:

Here are some sites that we have found useful for information and problem solving. Please note we are not responsible for anything that appears on these sites and cannot guarantee any of the solutions that you may find on them.

More about the help-line

On 8^th August 2002 Happy Computers Helpline gained TSC Accreditation from the Institute of IT Training, for commitment to high quality support through its service delivery and internal processes. The three main areas assessed were:

Career development, training & assessment for support analysts: clearly defined roles and responsibilities, appropriate technical skills and product knowledge, call handling skills, questioning skills and problem diagnosis, coaching skills

The life cycle of the call - call progression, call escalation, call logging and analysis.

The client relationship: service level agreements - promises and undertakings, measurement of satisfaction, call effectiveness and behavioural change.

These are the IITT Assessor's concluding comments:

"Happy Computers Helpline provides a most impressive service to its training client-base. Staffed entirely by 'Helpline Staffers' who have training familiarity with the products they support, they have the ability to provide links to the training courses and continuity to the training process. In the jargon of a traditional helpdesk, the Helpline can demonstrate almost zero tolerance in its compliance with SLA and an almost 100% first time fix rate. The service can frequently boast 'no unresolved calls'.

In conclusion, the Accreditation Consultant reports a highly professional and well managed operation which. conforms with and follows entirely, the ethos of the Institute TSC Accreditation."

Desktop Streaming

We now have the technology to share your screen using the Internet, thanks to software provided by Desktop Streaming. It is very simple to use and means we can look at your computer screen and help you out immediately over the Internet. It does not require you to have any special software apart from a web browser eg Internet Explorer. The only software that will be downloaded onto your machine is a small chat applet, allowing you to enter into a chat on screen with us if you want to.

It is amazing technology and means that you don't need to worry about emailing a document to us, or trying to explain what you see on your screen! It saves you time and allows us to resolve your query more speedily, improving our customer service to you!

The Happy Computers' Web Site

https://www.happy.co.uk

The Happy Computers web site is dedicated to providing you with information about both the software you use, and the courses we run. You'll find copies of manuals to download and tips on the programs you use, designed to make your work quicker and easier. You'll find up-to-date news about Happy Computers and the team, and you can of course find information on all our courses and even book your place on one. You can also find out about the many awards Happy Computers has won, information about our training style and client comments.

Visit the site regularly as it changes every few days - weekly at the longest!

If you have any comments, ideas or just fulsome praise, you can e-mail Colin, our web editor, at: [email protected].

Alternatively, write your comments when you do your evaluations on-line at the end of a course at Happy Computers.

If the above means nothing to you, and you are interested in learning more about the World Wide Web and the Internet, Happy Computers run a wide range of courses in Internet software.

About this manual

Who is it for and how to use it

This manual is designed for use with the Microsoft Excel 2000 Essentials course with Happy Computers.

It is also meant as a back up for when you get back to work in combination with the two year telephone help-line you get free with every Happy Computers course.

It is not meant as a replacement to the full reference manuals that come with Excel 2000

This manual is a step by step guide to the functions taught in the Microsoft Excel 2000 Essentials course.

You should be able to find the part you're after by looking in the index and contents and noting that the general course will follow the pattern of the manual.

The step by step parts are in italics. Simply do the things on the left, and read the things on the right for further information

This is what you do This is a description of what is happening

What do the icons mean?

Getting Help

Excel 2000 keeps the screen fairly simple. But don't expect to have to remember the functions. There are several levels of help:

This guide contains all the basic functions of Excel 2000. Use the Table of Contents and the Index to find the functions that you need explained.

The on-screen help function explains commands in detail. It is simple to use

Press F1

Press buttons and scroll bars as required to get more help

For help on a particular part of the screen

Press Shift F1

Click on the area of the screen you wish to know about

To close help

Alt + F4

Or

File menu: Exit (make sure you get the file menu for the help and not for the software)

For more information on using the Office Assistant in Excel 2000 see the next page

Software manuals have improved. Use them as a reference on specific functions, rather than for a general read on how to use the software.

Go to the reference section and look up the thing you want explained.

If you received this manual at a Happy Computers course, we will provide phone support on any functions covered on the course for two years from the date of the course. This is a guarantee of the quality of our training:

Ring: 020 7375 7373 and we will help you with your difficulty. You can do this as many times as you like.

The Office Assistant

Excel 2000 comes with an animated office assistant to help you if you get stuck.

Click on help menu

Click on show office assistant The office assistant will appear (see below)

Type your question into the space provided

Click on search

Click on the blue circle next to the topic you are interested in
Your answer will appear in a new help window

Click on the print icon in the help window

Click on the 'X' at the top right of the help window

Click on the help menu

Click on hide office assistant

Click and drag to a new position

Show the office assistant (see previous page)

Right-click on the office assistant A menu will appear

Click on options  A dialog box will appear

Click on the gallery tab at the top of the dialog box

Click on next to move through the assistants

Click O.K. when you have found the assistant you require

Once you become familiar with the software you may not need office assistant's help so much. If you want to permanently disable it then you can change the options.

Show the office assistant

Right-click on the office assistant A menu will appear

Click on options  A dialog box will appear

Click on the options tab at the top of the dialog box

Un-tick the options you do not require

Click OK

Getting Started in Excel

What can you use Excel for?

Excel is a spreadsheet program. Spreadsheets are basically big tables that hold text and numbers. Calculations can then be performed on those numbers.

Formulas or calculations

Storing information (database)

Creating tables

Here is a very simple domestic budget using Excel:-

For more information on formulas see page

Excel has a simple database facility that allows you to

Sort information into any order (e.g. by surname)

Extract the information you wish to see (e.g. only the females)

Excel can also be used very much like Word processing tables, i.e. for laying information out neatly in rows and columns and making it look impressive.

Starting and closing Excel

Click on start 

Click on programs  A new menu will appear to the right

Click on Microsoft Excel

Or if you have a shortcut

Click or double-click on the shortcut icon on the desktop/office toolbar

Click on file

Click on exit

Or

Click on the 'X' at the top right hand corner of Excel

The Excel 2000 Screen

The Mouse keeps changing shape

As you progress through the course you will see that the mouse changes shape all the time, depending on what action you are performing. It's really important that before you start to do anything, you check that your mouse looks correct. Use this page as a reference to remind you what the different mouse shapes mean.

Moving around the spreadsheet

Entering text and numbers

The appearance of a cell changes. Originally you will see a thick border around the cell

But when you enter information the border will become thinner and a cursor will appear

The information you are typing will appear on the formula bar, along with a red cross and green tick

When you have finished typing you must let Excel know by clicking on the green tick or pressing enter, otherwise you will not be allowed out of the cell! Once you have confirmed, the green tick and red cross will disappear, and the thick border will return.

Click on the cell you require

Type in the text you require  The border will appear thinner

Press enter or click on the green tick Text will go to the left of the cell

Click on the cell you require

Type in the number you require  The border will appear thinner

Press enter or click on the green tick Numbers will go to the right of the cell

Click on the cell you require

Type the date with forward slashes around it
e.g. 10/3/99

Press enter or click on the green tick

Click on the cell you require

Type the number followed by the percentage sign

Press enter or click on the green tick

Correcting your mistakes

Click on the cell required

Press delete

Click on the cell required

Type the new contents  The original contents will disappear

Double-click on the cell required A cursor will appear inside the cell

Or

Click on the cell required

Press F2 on the keyboard  A cursor will appear inside the cell

Or

Undo and Redo - A licence to make mistakes

Undo allows you to cancel up to 16 of your previous actions if you have made a mistake. If you then decide that you didn't mean to cancel those actions, you can redo up to 16 things that you have undone!

Click on the undo button

Or

Press CTRL & Z

Click on the redo button

Or

Press CTRL & Y

Click on the down arrow next to undo

Find the action(s) you want to undo, scrolling down if necessary

Click the on the action you wish to undo from

Click on the down arrow next to redo

Find the action(s) you want to redo, scrolling down if necessary

Click on the last action you wish to redo

Saving your Workbook

Click on the save icon

Type in a name for your workbook (up to 255 characters)

Change the folder to save in if required

Click on save

Click on the save icon The workbook will be saved in the same place

Using save as will allow you to make a copy of your workbook in a different location or with a different name

Click on file

Click on save as

Type in a new name for the workbook if required

Change the folder if required

Click save

Click on the tools menu

Click on options 

Click on the general tab

Click in the box next to default file location (see below)

Type in the drive and folder you wish to save to, e.g. C:\work

Click O.K.

Closing and Opening your Workbook

Click on the open icon

Change the folder Excel is looking in if required

Click on the name of the workbook you wish to open

Click on open
or
Double-click the name of the workbook you wish to open

Click on the bottom X at the top right of the screen

or

Click on the file menu

Click on close

Click on the new icon

Or

Click on file

Click on new

Double-click on workbook

AutoFill

AutoFill is a great timesaving feature that allows you to copy text, numbers or formulas in a spreadsheet.

Certain text such as months, days or dates work well with AutoFill. Have a look at the examples below, which were all created using AutoFill

When using AutoFill for dates, for example, you might want the sequence to go from week to week rather than day to day. To achieve this, you must start the sequence off for AutoFill

Type the first date into one cell

Type the second date into an adjoining cell
e.g.

Select both cells (see page

AutoFill as normal

Click on the tools menu

Click on options

Click on the custom lists tab

Select new list on the left hand side

Click in the box underneath list entries

Type your new list into the box, pressing enter after each entry

Click on add when you have finished

Selecting parts of a spreadsheet

Start from the cell at the top left hand corner of the area you wish to select

Make sure that your mouse looks like the big plus sign

Click and drag over the cells you require They will go purple

Click on the column letter you require

Or

Click and drag over the column letters to select several columns

Click on the row number you require

Or

Click and drag over the row number to select several rows

Click on the grey square at the top left corner of the spreadsheet

Select the first area you require

Hold down the control key on the keyboard

Select the second area you require

Inserting and deleting columns and rows

Select a row  The new row will be inserted above this

Click on insert

Click on rows  A new row will be inserted above the selection

Or

Select the row below where you require a new one

Press CTRL +

If you select row 6  A new row is inserted above

Select the column to the right of where you require a new one

Click on insert

Click on columns  A new column will be inserted to the left of the selection

Or

Select the column to the right of where you require a new one

Press CTRL -

If you select column B A new column is inserted to the left

e.g. Inserting six rows

Select six rows below where you require the new rows

Click on insert

Click on rows  Six new rows will be inserted

Or

Select six new rows below where you require the new rows

Press CTRL +

Adjust the number from six to the number of rows you require

Select the rows or columns you wish to delete

Click on edit

Click on delete

Or

Select the columns or rows you wish to delete

Press CTRL -

Cut , copy and paste

Drag and Drop

Changing the zoom control

This allows you to stand back from your spreadsheet, so that you can see more of it, or zoom in closer. It does not change the size of the spreadsheet when it is printed

Click on the down arrow next to the zoom control icon

Click on the zoom level you require

Click on the view menu

Click on zoom

Click in the circle next to the zoom you require, or type in your own zoom next to custom

Click OK

Freeze Panes

If your spreadsheet has headings at the top and down the side, it becomes a bit frustrating when you scroll across or down and can no longer see what you are talking about. Freeze panes solves this problem by sticking columns and rows down on the screen where you can always see them.

If you scroll down or across the frozen rows and columns will stay put!

e.g. If you have cell B2 selected:-

Column A is to the left, and row 1 is above the active cell. These will become frozen when you scroll across or down

The Basics

Using AutoSum

AutoSum is a quick and easy way of adding up lists of figures.

Select the figures you wish to add up, the blank cell, and the cell where you require the answer

Click on the AutoSum icon

If you prefer, you do not have to use the AutoSum icon. You can type the formula into the cell instead

Click on the cell where you require the answer (make sure there is a blank row or column between the figures you are adding and the answer)

Type =sum(

Click on the first cell you wish to add up The cell reference will appear

Type a colon

Click on the blank cell at the end of the list The cell reference will appear

Press return or click on the green tick

Formulas

Formula is the term used for calculations in your spreadsheet. The diagrams below show an example formula being entered.

To work out the surplus we need to do a calculation by taking away the expenditure from the salary. You can see this being entered on the left-hand diagram. On the right-hand side you can see what happens once the formula has been completed.

Entering the formula Completed formula

Formulas always start with the equals sign - that's how Excel knows it's a formula

Cell references are used instead of numbers

A mathematical symbol is used to denote the type of calculation

e.g. Here is the formula from the example above, which found us the surplus (or money left over)

Creating a formula

Once the formula is confirmed the answer will appear in the cell, and the formula will appear on the formula bar.

Calculations are not simply done from left to right. Below is the order in which all calculations are performed.

The acronym for this is BODMAS

Brackets Order Divide Multiply Add Subtract

Using paste function

There are certain useful calculations that become long-winded if you try and create them with straightforward formulas - such as finding the average of a group of numbers, or rounding numbers up. The paste function in Excel is there to make such calculations quick and easy.

Click on the cell where you require the answer

Type the = sign

Type AVERAGE, MAX or MIN

Type an open bracket

Type in the first cell reference you require (or click on the cell)

Type a colon

Type in the blank cell reference at the end of the list (or you can click on the cell)

Click on the green tick or press enter

e.g.

=Average(A1:A6)

=Max(A1:A6)

=Min(A1:A6)

Using percentages

See page 23

e.g. What is the VAT on a £100

Click on the cell where you require the answer

Type the = sign

Click on the cell containing the percentage, e.g. 17.5% for VAT

Type the asterisk to signify multiplication

Click on the cell containing the number you wish to find a percentage of, e.g. £100

Press enter or click on the green tick

e.g. Finding out what percentage of your salary your rent takes up

Entering the formula Completed formula

Click on the cell you wish to change

Click on the percent icon

Copying formulas

You can copy formula using AutoFill and they will automatically adjust to make sense!

Create your first formula

AutoFill this formula across or down to copy to other columns or rows (See Page )






The formula will not stay the same, but will adjust to make sense!

e.g. This spreadsheet needs totals in row 10, and in column I. To do each formula separately would be time-consuming, so instead, the formula to find the total for January has been copied across to the other months. Likewise, the formula to find the total for rent has been copied down to find the totals for food, social, bills and other.

What are absolute cell references

Using AutoFill to copy formulas is a great way to save time, but you do not always need it to adjust the cell references in the original formula. There are some situations where a cell reference needs to remain constant. Have a look at the example below

In this spreadsheet everyone's salary is due to increase by 10%. The first formula, to find Shing Chen's new salary has been created. We multiply his current salary (cell B4) by the 10% (cell B1)

However, everyone's salary is being increased by 10%. If we AutoFill the formula as it is, Excel will adjust the row numbers, and move down from the 10%, and we'll end up with some funny answers.....

If you look at the spreadsheet on the left hand side, you'll see that AutoFill has caused the row numbers to be adjusted. But the formula we need requires cell B1 to remain constant.

So we need to tell Excel to stay at this cell when we AutoFill, and not move down. Excel must absolutely always and forever look at this cell. In other words, we need to make this an absolute cell reference.

Formulas with absolute cell references

Creating and using absolute cell references

Printing your spreadsheet

Click on the print icon

If required, select the area you wish to print

Click on file

Click on print A dialog box will appear (shown below)

If required, click next to selection to print the selected area

If required, type in the pages you wish to print

Type in the number of copies you require

Click O.K.

If you often print the same section of your worksheet, you can set it as the print area. This means that when you click on print Excel will only print out this area.

Select the area you wish to print

Click on file

Click on print area

Click on set print area  Dots will appear around the selected area

Click on file

Click on print area

Click on clear print area

Click on the print preview icon

For more information on getting a print preview see (see page 70)

Making a Spreadsheet Look Good

Using AutoFormat

Select the cells you wish to autoformat

Click on Format

Click on Autoformat

Choose the Autoformat style you require from the left hand side

Change the options if required (see section Autoformat Options below)

Click O.K.

Select the cells you wish to clear the Autoformat from

Click on Format

Click on Autoformat

Click on none at the bottom of the list on the left hand side

Click on the options button in the AutoFormat box

Click next to the option to remove the tick

Basic Formatting

Select the cells you wish to change

Click on the icon you require (shown above)

Select the cells you wish to change

Click on the down arrow next to the font box

Click on the font you require

Select the cells you wish to change

Click on the down arrow next to the font size box

Click on the size you require

Excel 2000 has a feature that displays the font list in their actual fonts. You can turn this on or off according to preference

Click on tools

Click on customise

Click on options

Click in the box next to list font names in their font

Click close

Select the cells you wish to change

Click on Format menu

Click on cells 

Click on Font tab 

Make the changes you require 

Click O.K.

Changing the alignment

Select the cells you wish to change

Click on the alignment icon required (shown above)

e.g.

Centre align will only centre text within a single column, but you often require headings to be centred in the middle of several columns.

Select the cell containing the text, and the cells you wish to put it in the middle of
e.g.

Click on the merge and centre icon

Select the merged and centred cell

Click on Edit menu

Click on Clear

Click on Formats

If you have tall rows, you may want the text to be aligned vertically in the row
e.g.

Select the cells to change

Click on format

Click on cells

Click on alignment

Click on the down arrow next to the vertical alignment box

Click on the alignment option you require

Click O.K.

Select the cells you wish to change

Click on format

Click on cells

Click on alignment

Type the number of degrees you wish to rotate the text by
or
Click and Drag the text marker (see below)

Click O.K.

If you have a lot of text to fit within a cell, you may want it to wrap onto more than one line
e.g.

Select the cells to change

Click on format

Click on cells

Click on alignment

Click in the box next to wrap text, so that it is ticked

Click O.K.

Changing the number formats

Number formats allow you to change the appearance of your figures. For example, if your figures represent money, you can format them to appear with the pound sign and two decimal places.

Select the cells you wish to change

Click on the number format icon you require (see above)

Select the cells you wish clear number formats from

Click on the edit menu

Click on clear  A new menu will appear to the right

Click on formats

Select the cells to change

Click on format

Click on cells

Click on number tab 

Choose the category of number you are using from the list on the left hand side
e.g. Number, currency

Click on one of the red numbers underneath negative numbers

Click O.K.

Select the cells to change

Click on format

Click on cells

Click on number tab 

Click on custom from the list on the left hand side

Type in a code underneath the Type box (see below)

Click O.K.

Changing the date format

You must always type in the date with forward slashes around it (see page 23), but it does not have to look like this forever!

Changing the column widths

Place your mouse to the right of the column letter you wish to re-size
or
Place your mouse below the row number you wish to re-size

Double-click

Place your mouse to the right of the column you wish to re-size
or
Place your mouse below the row number you wish to re-size

Click and drag to the size you require

Select the columns or rows you wish to re-size

Place your mouse at the right-hand edge of the selected columns
or
Place your mouse underneath the selected rows

Click and drag  The columns or rows will all become the same size

De-select the rows

Select the whole of the spreadsheet (see page

Re-size column A to the desired size
And/or
Re-size row 1 to the desired size

Click in the middle of the spreadsheet to deselect

Adding Borders

Select the cells you wish to apply a border to

Click on the down arrow next to the borders icon

Click on the border style you require (shown below)

Select the cells you wish to apply a border to

Click on format

Click on cells

Click on the border tab

Click on the line style you require from the right hand side

If you would like to change the colour, click the down arrow next to the colour box

Click on the preview to set the borders you require (see above)

Click O.K.

Select the cells you wish to clear borders from

Click the down arrow next to the borders icon (see above)

Choose the no borders style

Adding shading

Select the cells you wish to shade in

Click on the down arrow next to the shading icon Colours will appear

Click on the colour you require

Select the cells you wish to shade in

Click on format

Click on cells

Click on the patterns tab 

Click on the colour you require

If you require a pattern, click on the down arrow next to pattern

Click O.K.

Select the cells you wish to clear shading from

Click on the down arrow next to the shading icon (see above)

Click on no colour

Select the cells you require

Click on the down arrow next to the font colour icon

Click on the colour you require

Format painter

Getting a Print Preview

Click Print Preview icon

Hover the mouse over the print preview It will look like a magnifying glass

Click the mouse  You will be zoomed in or out

Or

Click on the zoom icon

You can see the page you are currently on at the bottom left of the screen

Click on next and previous icons to move through the pages

Or

Use the scroll-bar on the right hand side of the screen

Click on the margins icon The margins will appear as dotted lines

Position your mouse over a dotted line so that it changes to a cross arrow

Click and drag to increase or decrease the margin

Click on the close icon

Click on set-up icon to access the page set-up dialog box

For more information on page set-up see pages 72 -

Page Break Preview takes you out of print preview and back into the normal view of your spreadsheet. However, once back in normal view, you will be able to see where the page breaks are

Click on print preview icon

Click on the page break preview icon

Click and drag over the blue lines to adjust the position of the page breaks if needed

Click on the print preview icon

Click on normal view icon

Changing the page set-up

There are two ways of getting into page set-up - through print preview, or from the normal view. Going to page set-up from print preview means that you can see how your changes have affected the spreadsheet. If you go from normal view you will not be able to see the changes.

From print preview

Click on set-up icon

From normal view

Click on file

Click on page set-up

Bring up the page set-up dialog box (see above)

Click on the margins tab

Type in the margins you require
or
Use the up and down arrows next to the margin sizes

Bring up the page set-up dialog box (see previous page)

Click on the margins tab

Click next to centre horizontally so that it is ticked

Click next to centre vertically so that it is ticked

Click O.K.

Bring up the page set-up dialog box (see previous page)

Click on the page tab

Click next to portrait or landscape

Click O.K.

If your spreadsheet is too big to fit on the page, or if is too small to read, you can scale it up or down in size.

Bring up the page set-up dialog box (see previous page)

Click on the page tab

Increase or decrease the percentage next to adjust to....

Click O.K.

Bring up the page set-up dialog box (see previous page)

Click on the sheet tab

Click next to gridlines so that it is ticked

Bring up the page set-up dialog box ( see previous page)

Click on the sheet tab

Choose the option you require underneath page order

Headers and footers

Excel contains standard headers and footers that are easy to apply

Bring up the page set-up dialog box (see page

Click on Header/Footer tab

Click on the down arrow next to header and choose the header text you require

Click on the down arrow next to footer and choose the footer text you require

Click O.K.

For a bit more flexibility, you will need to customise your headers and footers

Bring up the page set-up dialog box (see page

Click on header/footer tab

Click on custom header or custom footer

Type in the text you require
Or
Click on one of the icons to enter specific information (see below)

Click O.K.  Your custom header or footer will show in the preview

Click O.K.

Creating Charts

Creating charts

Select the cells you wish to chart
Include labels and figures (see below), but do not select whole rows or columns

Press F11 A bar chart will appear on a new sheet

Select the cells to be charted
Include labels and figures, but do not select whole columns and rows

Click on the chart wizard icon

In Step One of the Chart Wizard, choose the chart type you require (see below)

Click on next

In step 2 of the wizard, check that the cells you selected have produced the expected chart

Click on next

In step 3 of the wizard, change the chart options if required

Click on next

In step 4 of the wizard, decide whether you wish your chart to appear on a new sheet or as an object next to your figures.

Click on finish

Moving, re-sizing and deleting charts

This only applies to charts which have been created using the wizard, and are on a sheet as an object

Click on the chart to select it  Black boxes will appear around the edge

Click and drag from the middle of the chart to a new location.
Your mouse should look like a cross arrow

Click on the chart to select it  Black boxes will appear around the edge

Press delete

Selecting parts of a chart

Click on the part of the chart you require  Black boxes will appear around the part

Or

Select the chart

Click on the down arrow next to the chart objects icon on the chart toolbar
(see next page for more information on the chart toolbar)

Click on the part you require  (see above) Black boxes will appear around the part

Using the chart toolbar

The chart toolbar should appear on your screen automatically when you have a chart selected. It allows you to make changes to the way your chart looks.

Sometimes the chart toolbar refuses to come out! Just follow the steps below if this happens

Click on view

Click on toolbars 

Click on chart

Formatting the chart

Select the chart

Click on the down arrow next to the chart type icon on the chart toolbar

Click on the chart type you require

For more information about chart types see page 87

Select the series you wish to change (see page

Click on down arrow next to the fill-icon

Click on the colour you require

Select the chart area (see page

Click on the down arrow next to the fill icon

Click on the colour you require

Select the a title, data label, axis or the legend (see page

Click on Format icon on the chart toolbar

Click on font

Make any changes you require

Click O.K.

Select a title, data label, axis or legend (see page

Click on an orientation icon on the chart toolbar

Select the chart area

Click on chart menu

Click on add data 

Click and drag over the data you wish to add

Click O.K.  The information will be added to the chart

Select the chart area

Click on chart menu

Click on chart options

Click on titles tab

Type in the titles you require

Click O.K.

Select the chart area

Click on the chart menu

Click on Add trendline

Choose the type of trendline you require

Click on the series you wish to see a trendline for

Click O.K.

Select the chart area

Click on the chart menu

Click on chart options

Click on the gridlines tab

Click next to the gridlines you would like to see

Click O.K.

Select the chart area

Click on the chart menu

Click on chart options

Click on data labels

Choose the type of data labels you would like to see
value, label or percentage

Click O.K.

If you would like the axes to start or finish at different numbers so that you can see your data more clearly, follow the instructions below

Select the axis you wish to change

Click on the format icon on the chart toolbar

Click on the scale tab

Change the options next to minimum and maximum

Click O.K.

Printing the chart

Click on the sheet to select it

Click on the print icon

Select the chart area

Click on the print icon

Your chart must be inserted as an object on the sheet

Click and drag over the figures and the chart

Click on print icon

Which chart shall I use?

Excel offers you some impressive looking three-dimensional chart options. However, they are not always as clear to read as two-dimensional charts.

Design Principles

The Golden Rules of Good Spreadsheet Design

Always use cell references in formulas and never numbers. If you use numbers:-

Other people using your spreadsheet may not know what the number refers to

If you come back to the spreadsheet a long time after you created it, you may not know what the number refers to

If the number should change, your formula will not update to give the correct answer

It will be difficult to find all the formulas that relate to this number

You will have to change every formula that uses the number, rather than just changing the contents of one cell

Always leave a blank cell between the list of figures and the sum formula. If you do not leave the blank cell, any new information you add may not get included in the formula.

Clean and well-designed spreadsheets calculate downwards and to the right. This makes them easy to follow and avoids circular references.

Circular references occur when a formula loops back on itself. At its most simple, a circular reference can occur when a cell containing a formula is using itself somewhere in the calculation. However, it can also occur when a formula contains a cell reference which happens to contain another formula, and the answer cannot be achieved as both formulas are dependent on the other. Excel then goes round in circles trying to get the answer. You can avoid circular references by ensuring formulae only refer to cells above or to the left.

e.g.

In this spreadsheet, a formula in B8 works out the total.
To get the total Excel must add cells B3 to B7.

In cell B6, however, we need a bonus figure. The bonus is the total in B8 multiplied by 10% in B1.
So we cannot get the total in B8 without the bonus, and we cannot get the bonus without the total. Hence we have a circular reference

Mistakes can easily arise through:-

Figures being entered incorrectly

Formulas being typed in incorrectly

New information being typed in that does not get included in existing formulas

Range Names

Creating range names

So far we have always referred to cells by their cell reference, e.g. A1, B7. However it can sometimes make more sense to give a cell or cells a name. There are several advantages to doing this

Selecting a named range of cells is a lot quicker and easier

Printing a named range of cells is a lot quicker and easier

Formulas can be clearer and easier to create

Moving around different areas of the spreadsheet can be done more efficiently

Have a look at the example below, which uses range names in a formula

To get the surplus we need to take expenditure away from income. We would normally do this by saying B3-B4. However, if we name these cells as income and expenditure respectfully, then the formula looks clearer and it is easier to see what is happening.

If you have typed the names you would like to use on your spreadsheet already, then you can use them to create range names very quickly!

Click on the insert menu

Click on name

Click on define 

Select the name you would like to delete

Click on delete

Click O.K.

If you create range names after your formulas, you can change the formulas to use the range name instead of the cell references.

Click on the insert menu

Click on names  A new menu will appear

Click on Apply  A list of your names will appear

Click on the name you wish to apply
or
Hold down the control key and click on several names to apply

Click O.K.

You can name whole columns and rows and then use both the column and the row number as a cell reference. For example in the spreadsheet below you could name column B as Jan, and rows 2,3,4 as bills, food and beer respectively

If you then used the reference Jan Bills (there must be a space between the names), Excel would realise you were referring to cell B2.

Selecting a named range

Click on the drop down arrow next to the name definition box

Click on the range you would like to select or move to

Printing a named area

Click on the drop down arrow next to the name definition box

Click on the range you would like to print

Click on file

Click on print

Choose selection underneath print what

Click O.K.

Using range names in formulas

You can type in a range name instead of using the cell reference in a formula.

e.g.

Protection

Protecting your sheet

Once you have set up a spreadsheet, it is all too easy for formulas to get deleted by accident. It's a good idea to protect your spreadsheet to save it from calamity in the future!

Click on the tools menu

Click on protection

Click on protect sheet

Type in a password if required

Click O.K.

If you have entered a password, you will be asked to type it in again

You will not be able to change any of the information on your sheet if is all protected

Click on tools

Click on protection

Click on unprotect sheet

You will be asked to enter your password if you set one

When you first create a spreadsheet, all of the cells are formatted as "locked". Any cells that are locked will be protected when you use the protection command. However, if there are cells that you want to be able to change, then you must format them to be "unlocked".

If cells are unlocked then protection will have no effect on them!

Select all of the cells that you want to be able to change
(this is usually all of the cells, except those containing formulas or text labels)

Click on format

Click on cells 

Click on the protection tab

Click on the tick next to locked, so that the box is blank

Click O.K.

Click on the tools menu

Click on protection

Click on protect sheet 

Enter a password if required

Click O.K.

If you entered a password, you will be asked to type it again

Now you will be able to change the cells that you unlocked, but everything else will be protected!

Putting a password on an Excel Workbook

If you choose to put a password onto the Workbook, other people will not be able to open it without giving the password

Click on file

Click on save as

Click on options A dialog box will appear

Type in the password you require next to Password to open

Click O.K.

Appendices

What do all the icons do?

What do the error messages mean?

When something goes wrong with a formula Excel produces messages that attempt to describe what the problem is:-

Glossary

If you want to copy a formula to another cell, but you want to prevent Excel from adjusting the cell references, make the cell references "absolute". Absolute cell references are indicated by a dollar sign ($).

The cell border of the active cell is darker than the other cells, and indicates the cell you are currently working on

This feature is useful when you need to enter a series of numbers, dates or formulas

This will automatically format your table, for example adding heavy lines to form a box around the date

This feature allows you to select the cell range containing the values that you wish to add together. It then adds those figures up.

This is the basic building block of the Excel worksheet. A cell if formed by the intersection of the row and column gridlines. Each cell is identified by the letter of the column and the number of the row. E.g. B7

A group of cells that are next to each other. To select a range of cells see page 31.

These are the co-ordinates of the cell in the worksheet, e.g. B7

This is a sheet in a workbook containing a chart. The chart sheet is updated when the worksheet data is changed.

This is a solid black square in the right hand corner of the active cell border. Using your mouse, grab the black square and drag it across to AutoFill your cells.

Formulas are the basis of Excel. Formulas will do all the calculations that will normally be done by a calculator. You can use formulas to do simple calculations such as additions, subtractions, multiplication, division as well as statistical, financial and scientific calculations.

This is the bar at the top of the Excel screen. It is used to enter and edit formulas and values. It also displays the formulas in a particular cell.

These are the lines that you see in your spreadsheet. You can choose to print them using the tools menu, then the options dialog box.

This is the text which usually appears next to the figures in a spreadsheet, and identifies what the figures mean.

A legend (also known as a key) is used in charts and graphs. Excel actually creates legends from the labels on the worksheet. The legends can be customised with borders, patterns and different fonts.

In a formula there can be a combination of relative and absolute cell references. A mixed reference can look like this $A1 or A$1. $A1 refers to column A regardless of the position of the cell containing the formula.

This allows you to enter commonly used calculations quickly, such as finding the average, maximum or minimum of a group of numbers.

Relative cell references are used in formulas where you do not wish the reference to remain constant as the formula is AutoFilled. Relative cell references do not contain the $ found in absolute cell references.

It is possible to insert, delete, rename, move and copy sheets in Excel. The sheet can be a worksheet, chart sheet, module sheet or dialogue sheet. A worksheet is the most often used.

This is also called a spreadsheet. The worksheet is the primary document used in Excel. A worksheet is made up of cells in columns and rows and is always part of a workbook.

Index

Absolute cell references 50

creating 51

Adding

with AutoSum 41

Alignment

horizontal 58

Vertical 59

AutoFill 29

AutoFormat 55

AutoSum 41

Average 45

Bold 56

Borders 65

Calculations

creating 43

Centring

text 58

Whole of the spreadsheet 73

Changing

Contents of a cell 24

Chart wizard 78

Charts

Changing the colour 84

Changing the type of 84

Creating 78

Definition of types 87

Deleting 80

Moving 80

Printing 86

Re-sizing 80

Selection 81

Titles 85

Toolbar 83

Trendlines 85

Closing

Excel 18

print preview 71

Workbook 28

Colour

of Charts 84

Text 68

Columns

changing the width 64

deleting 34

inserting 33

selecting 31

Commas 61

Constants 50

Copy 35

Copying

Formats 69

formulas 49

with drag and drop 37

Currency 61

Custom Lists 30

Cut 35

Database 16

Dates

Entering 23

Formatting 63

in Header or Footer 76

Decimal points 61

Default folder 27

Deleting

Charts 80

columns 34

Contents of a cell 24

Range Names 96

rows 34

Design 92

DIV/O! 108

Drag and Drop 37

Editing

Contents of a cell 24

Entering

Dates 23

Numbers 22

Percentages 23

Text 22

Error Messages 108

Exiting

Excel 18

F4 50

Filename 76

Font 56

Fonts

displaying as they look 57

Format painter 69

Formatting

basics 56

charts 84

with AutoFormat 55

Formatting Toolbar 107

Formulas 16

copying 49

creating 43

definition 42

Freeze Panes 39

Golden Rules 92

Gridlines

Printing 73

Headers and footers 75

Headings

Centring 58

Help 12

Highlighting 31

Icons 106

Inserting

columns 33

rows 33

Italic 56

Keyboard

for creating charts 78

Moving around 21

to select cells 32

Landscape 73

Lines

Printing all 73

Margins

In print preview 70

Page Setup 72

Maximum 45

Merge and centre 58

Minimum 45

Mistakes 25

Mouse Shapes 20

Moving

with cut and paste 35

with drag and drop 37

Moving around 21

N/A! 108

NAME? 108

Naming

Ranges 95

Negative numbers

in brackets 62

in red 62

New

columns 33

row 33

Workbook 28

NULL! 108

NUM! 108

Number formats 61

Numbering 74, 76

Office Assistant 13

Opening

Workbook 28

Orientation

Page 73

Text 59

Page Break Preview 71

Page breaks 53

Page Numbering 74, 76

Page order 74

Page setup 72

Paper Clip 13

Passwords 102, 104

Paste 35

Paste function 45

Percentages 47

Entering 23

Portrait 73

Print preview 70

Printing

Charts 86

help topics 13

spreadsheets 52

Protection 102, 104

Range Names 95

Deleting 96

Using 100

Redo 25

REF! 108

Replacing

Contents of a cell 24

Rows

deleting 34

inserting 33

selecting 31

Rules 92

Saving 26

Default folder 27

Scaling 73

Screen 19

Security 102, 104

Selecting 31

areas not next to each other 31

with the keyboard 32

Selection

Charts 81

Shading 67

Size

Of Charts 80

Scaling up or down 73

text 56

Spreadsheet

selecting 31

Spreadsheet Design 92

Standard Toolbar 106

Starting

Excel 18

Workbook 28

Text

Colour 68

copying 37

entering 22

moving 37

Orientation 59

Text-wrapping 60

Toolbars

Formatting 107

Standard 106

Typing in 22

Underline 56

Undo 25

Uses of Excel 16

VALUE! 108

Width

of columns 64

Zoom Control 38

print preview 70