Excel 2000 Basic User Manual

Kaitech Solutions Limited

February 2005

Kaitch Solutions Limited [Link] IT Support Nigeria Microsoft Office Training Manuals

KAITECH SOLUTIONS LIMITED

2005

TABLE OF CONTENTS

INTRODUCTION....................................................................................................................... 1
HOUSEKEEPING................................................................................................................................................... 1 INSTRUCTIONS..................................................................................................................................................... 1

STARTING EXCEL 2000.......................................................................................................... 2 PARTS OF THE EXCEL 2000 SCREEN..................................................................................3

The Title Bar................................................................................................................................................... 3 Minimise, Maximise/Restore, Close...............................................................................................................3 The Menu Bar................................................................................................................................................. 3 The Toolbars.................................................................................................................................................. 3 The Formula Bar............................................................................................................................................ 4 Column Headings........................................................................................................................................... 4 Row Headings................................................................................................................................................ 4 Spreadsheet Cells.......................................................................................................................................... 4 Scroll Bars...................................................................................................................................................... 4 The Status Bar............................................................................................................................................... 4

SELECTING CELLS................................................................................................................. 5
TO SELECT ONE CELL......................................................................................................................................... 5 SELECTING A RANGE OF CELLS....................................................................................................................... 5 SELECTING NON ADJACENT RANGES.............................................................................................................5 SELECTING COLUMNS........................................................................................................................................ 5 To Select One Column................................................................................................................................... 5 To Select Multiple Columns........................................................................................................................... 5 SELECTING ROWS............................................................................................................................................... 5 To Select One Row........................................................................................................................................ 5 To Select Multiple Rows................................................................................................................................. 5 SELECTING THE WHOLE SPREADSHEET.........................................................................................................6 TO DESELECT A SELECTED RANGE................................................................................................................. 6

ENTERING DATA INTO CELLS...............................................................................................7

To Enter Data Into Any Cell .......................................................................................................................... 7

AUTOFILL................................................................................................................................. 8
To Use AutoFill............................................................................................................................................... 8

EDITING AND DELETING CELL CONTENTS..........................................................................9

EDITING A CELL IN THE FORMULA BAR..........................................................................................................9 EDITING A CELL IN THE CELL ........................................................................................................................... 9 TYPING OVER A CELL......................................................................................................................................... 9 TO DELETE CELL CONTENTS............................................................................................................................. 9

UNDOING MISTAKES.............................................................................................................10
To Undo A Mistake....................................................................................................................................... 10 To Redo What You Just Undid.................................................................................................................... 10

INSERTING NEW COLUMNS AND ROWS.............................................................................11

INSERTING COLUMNS....................................................................................................................................... 11 To Insert One New Column......................................................................................................................... 11 To Insert Multiple Columns..........................................................................................................................11 INSERTING ROWS.............................................................................................................................................. 11 To Insert One New Row............................................................................................................................... 11

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

To Insert Multiple Rows................................................................................................................................ 11

DELETING COLUMNS AND ROWS.......................................................................................12

DELETING COLUMNS........................................................................................................................................ 12 DELETING ROWS................................................................................................................................................ 12

MOVING AROUND THE SPREADSHEET .............................................................................13 USING FORMULA...................................................................................................................14

To Create A Basic Formula..........................................................................................................................14

CREATING TOTALS...............................................................................................................15
Example........................................................................................................................................................ 15 To Total A Large Range Of Cells.................................................................................................................15 AUTOSUM............................................................................................................................................................ 16

EDITING AND DELETING FORMULA....................................................................................17

To Edit A Formula........................................................................................................................................ 17 To Delete A Formula.................................................................................................................................... 17

COPYING FORMULA.............................................................................................................. 18
To Copy A Formula...................................................................................................................................... 18

OTHER FORMULA FUNCTIONS............................................................................................19

Average........................................................................................................................................................ 19 The Maximum.............................................................................................................................................. 19 The Minimum............................................................................................................................................... 19 Count............................................................................................................................................................ 19

CUT, COPY AND PASTE........................................................................................................20

COPY AND PASTE.............................................................................................................................................. 20 CUT AND PASTE................................................................................................................................................. 20 PASTE FROM THE CLIPBOARD........................................................................................................................ 21 COPYING BETWEEN APPLICATIONS..............................................................................................................21 DRAG AND COPY............................................................................................................................................... 22

WORKING WITH SHEETS......................................................................................................23

Moving Between Sheets...............................................................................................................................23 RENAMING A SHEET.......................................................................................................................................... 23 INSERTING A NEW SHEET................................................................................................................................ 23 MOVING SHEETS................................................................................................................................................ 23

SAVING YOUR WORKBOOK.................................................................................................24

SAVING A WORKBOOK THAT HAS NOT BEEN SAVED BEFORE.................................................................24 Saving Your Workbook As An Excel 5 File..................................................................................................25 RE-SAVING A WORKBOOK............................................................................................................................... 25 DUPLICATING A WORKBOOK.......................................................................................................................... 25

OPENING WORKBOOKS.......................................................................................................27
OPENING A NEW BLANK WORKBOOK...........................................................................................................27 OPENING AN EXISTING WORKBOOK..............................................................................................................27 RECENTLY USED FILES..................................................................................................................................... 28 OPENING MULTIPLE WORKBOOKS................................................................................................................ 28 To Move Between Open Workbooks...........................................................................................................28

CLOSING A WORKBOOK......................................................................................................29
To Close A Workbook.................................................................................................................................. 29

ii

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

The Close/Save Safety Net..........................................................................................................................29

BASIC FORMATTING.............................................................................................................30
To Change The Font.................................................................................................................................... 30 To Change The Text Size............................................................................................................................. 30 To Make Text Bold, Italic or Underlined.......................................................................................................31

ALIGNMENT FORMATTING...................................................................................................32
CHANGING THE ALIGNMENT...........................................................................................................................32 CENTERING TEXT ACROSS CELLS................................................................................................................. 32 To Remove Center Across Selection Formatting......................................................................................33

RESIZING COLUMNS AND ROWS........................................................................................34

CHANGING THE WIDTH OF COLUMNS............................................................................................................34 To Resize A Single Column......................................................................................................................... 34 To Resize Multiple Columns........................................................................................................................ 34 AutoFitting Columns..................................................................................................................................... 34 CHANGING THE HEIGHT OF ROWS................................................................................................................. 35 To Resize A Single Row...............................................................................................................................35 To Resize Multiple Rows.............................................................................................................................. 35 AutoFitting Rows.......................................................................................................................................... 35

NUMBER STYLES..................................................................................................................36
To Apply Number Styles.............................................................................................................................. 36

APPLYING BORDERS TO CELLS.........................................................................................37

To Apply Borders.......................................................................................................................................... 37 To Remove Borders..................................................................................................................................... 37

APPLYING COLOURS............................................................................................................ 38
To Change The Colour Of Cells...................................................................................................................38 To Change the Colour Of Text..................................................................................................................... 38

REMOVING FORMATTING.....................................................................................................40
To Clear Formatting..................................................................................................................................... 40

USING ZOOM.......................................................................................................................... 41
To Change The Magnification...................................................................................................................... 41

FREEZING TITLES.................................................................................................................42
To Freeze A Row Of Titles........................................................................................................................... 42 To Freeze A Column Of Headings...............................................................................................................42 To Freeze Both a Row of Titles and a Column Of Headings......................................................................42 To Unfreeze Your Titles............................................................................................................................... 42

WORKING WITH DATES........................................................................................................43

To Insert A Fixed Date................................................................................................................................. 43 To Insert A Rolling Date...............................................................................................................................43 APPLYING DATE FORMATS.............................................................................................................................. 43

PRINTING SPREADSHEETS.................................................................................................44
Printing With The Print Button.....................................................................................................................44 Printing Part Of A Spreadsheet.................................................................................................................... 44

PRINT PREVIEW.................................................................................................................... 45
To Print Preview Your Spreadsheet.............................................................................................................45

PAGE SETUP......................................................................................................................... 46
To Open The Page Setup Dialog Box..........................................................................................................46

EXCEL 2000 BASIC USER MANUAL

iii

KAITECH SOLUTIONS LIMITED

2005

The Page Tab Options............................................................................................................................... 46 The Margins Tab Options.......................................................................................................................... 47 The Sheet Tab........................................................................................................................................... 47 The Header/Footer Tab.............................................................................................................................48 To Change the Header or Footer................................................................................................................. 48

CREATING GRAPHS AND CHARTS......................................................................................49

To Create A Chart........................................................................................................................................ 49 To Print Your Chart...................................................................................................................................... 51 To Delete The Chart..................................................................................................................................... 51

PERCENTAGES..................................................................................................................... 52
To Turn The Answer Into A Percentage.......................................................................................................52

KEYBOARD SHORTCUTS.....................................................................................................53
Navigation Shortcuts.................................................................................................................................... 53 Selection Shortcuts...................................................................................................................................... 53 Editing Shortcuts.......................................................................................................................................... 53 General Shortcuts........................................................................................................................................ 53

iv

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

INTRODUCTION
The purpose of this manual is to explain the basic principles involved in the creation of spreadsheets and graphs using Excel 2000. Excel 2000 is a spreadsheet program, used to store and manipulate data (mainly numerical, but also text). Having entered the data into the spreadsheet, you can then perform a wide range of calculations upon it, and then format the data before you print it out. You can also create a wide variety of graphs from your data to display the information in a more interesting manner. Excel 2000, made by Microsoft, is a powerful tool that is comprehensive and easy to use at all levels. It runs in Windows 95 (or Windows NT) and is compatible with other Windows 95 programs. This means that you can copy and paste data between Excel 2000 and other programs, such as Word 2000. You can also attach Excel 2000 files to mail messages and send them to other people. HOUSEKEEPING There is an empty folder on your C drive called My Documents. This is available for you to store your work in. If you will be creating a lot of different files over the years, you should create some subfolders within this folder, to organise your filing. In case of theft or computer failure, you should copy backups of all your important files onto floppy disks. In case of fire, you should store the backup disks in a separate location from your computer. INSTRUCTIONS All the instructions in this manual will appear in bold. Instructions to click on Menus, Toolbar buttons and dialog buttons will appear in inverted commas. For example: in the Edit menu, select Copy. Or: click on the Undo button. All instructions to press keyboard keys will appear in square brackets. For example: press [Return]. Or: undo your mistake using the shortcut [ Ctrl]+[Z]

KAITECH SOLUTIONS LIMITED

2005

STARTING EXCEL 2000

Excel 2000 runs in Windows NT. In Windows NT: Click on the Start button Select Programs Select Microsoft Excel

Excel 2000 will open up containing a new blank workbook called Book 1.
Maximise/Restore Title Bar Menu Bar Minimise Close

Standard Toolbar Formula Bar Formatting Toolbar

Column Heading Vertical Scrollbar

Row Heading

Spreadsheet

Sheet tabs

Status Bar

Horizontal Scrolling

Excel 2000 is not a single spreadsheet (as it was in earlier versions of Excel), but a book (Workbook) containing different sheets (Spreadsheets). Each new workbook contains 3 separate sheets. But you can add extra sheets or delete sheets at a later date. You can have up to 250 sheets in a workbook. The sheets allow you to break your data down into separate categories. Each category can be stored on a separate sheet. This makes it easier to locate, view and print the data. You can also create formula that perform calculations across sheets, so you can create, for example, a summary sheet to total up the data in all the sheets in your workbook.

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

PARTS OF THE EXCEL 2000 SCREEN

The Title Bar The blue Title Bar at the top of the window contains the name of the window/program and the name of the currently open Workbook. You can click and drag on a title bar to move any window across the screen. Minimise, Maximise/Restore, Close In the top right hand corner of the window, the Minimise, Maximise and Close buttons allow you to control the size of the Excel window. Minimise reduces the window down to a button on the Task Bar at the bottom of the screen. Click on the appropriate button to open the window up again. The Maximise button makes the Excel window fill the screen. Once maximised, the button changes to the Restore button. Click on the Restore button to restore the window to its previous size. The Close button is used to close down the window. You should remember to save your work before you close down Excel 2000. The Menu Bar The Menu Bar contains the main menu headings. Click on a menu heading to bring up a drop down menu containing different menu commands. Each command will perform a different action. Not all of the commands available under each menu heading are visible. At the bottom of the menus there are arrows indicating that there are more commands available. Click on this to reveal the other commands on the menu.

Excel 2000 personalises the menu so that the commands you use most frequently are visible. When you use a command that is not displayed on the menu it moves to the personalised menu where it will be visible the next time you click on that menu heading. The Toolbars Toolbars contain buttons that provide shortcuts to the commands found in the drop down menus. Each button performs a different action.
Standard Toolbar Formatting Toolbar

When Excel 2000 starts up, it will contain two Toolbars, side by side, at the top of the screen. The left hand toolbar, the Standard toolbar, contains buttons for performing common actions such as saving or printing your work. The right hand toolbar, called the Formatting toolbar, contains buttons for formatting (changing the appearance of) the data in your workbook.

KAITECH SOLUTIONS LIMITED

2005

Not all of the buttons available on the toolbar are visible. At the right hand side of the toolbar there is a More Buttons button. Click on this to reveal the other buttons on the toolbar.

Excel 2000 personalises the toolbar so that the buttons you use most frequently are visible. When you use a button that is not displayed on the toolbar it moves to the toolbar and one you havent used recently moves to the More Buttons list. To find out what each button does, position the mouse cursor arrow over a button. A description of the buttons function will appear underneath the button. This is called a ToolTip. The Formula Bar The Formula Bar appears at the top of the screen just below the Formatting Toolbar. The left part of the formula bar displays the name (reference) of the cell that is currently selected (the active cell) in the current sheet. The right part of the Formula Bar is used to edit the contents on the active cell. In particular, it is used to edit formula. Column Headings There are 250 columns in each spreadsheet. Each column has its own name (reference). They start at A and go to Z, then continue with AA and finish at IV. Row Headings The row headings appear on the left side of the spreadsheet. Each row is given a number. There are 65,536 rows in each sheet. Spreadsheet Cells The intersection of each column with each row creates a cell. Thus each sheet contains millions of cells. Every cell has its own reference, a combination of the column reference and row reference it is located in. For example the very first cell in the sheet is cell A1. Scroll Bars The Scroll Bars are used to move around the spreadsheet. The Vertical Scroll Bar is used to move up and down the sheet. The Horizontal Scroll Bar is used to scroll left and right across the sheet. The Status Bar The Status Bar is located at the bottom of the Excel 2000 screen. It tells you Excels current status. Usually it just says that Excel is Ready for you to perform the next action.

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

SELECTING CELLS
In Excel 2000 you can select individual cells and blocks of cells (ranges). You can also select whole columns, whole rows or even every cell in a sheet. TO SELECT ONE CELL When the mouse cursor is positioned over the spreadsheet cells, it will change from an arrow to a large white cross. This is used for selecting cells. Click on the cell

SELECTING A RANGE OF CELLS Click and drag across all the cells you wish to select (from one corner to the other opposite corner of the range)

SELECTING NON ADJACENT RANGES It is also possible to select two ranges of cells that are not touching each other. Click and drag through the cells to select the first range Press the [Ctrl] key and hold it down Click and drag through the second range of cells

This is useful if you wish to apply the same formatting to different groups of cells, or create a chart from data that is not located in one block. SELECTING COLUMNS To insert, delete or resize a column, you will need to select the column first. To Select One Column Click on the grey column heading at the top of the column

To Select Multiple Columns Click and drag across the column headings

SELECTING ROWS You need to be careful, when selecting rows, that you use the white cross cursor shape when clicking or dragging on the row headings. The alternative cursor shape, that appears when you position over the border between two row headings (a double headed black arrow), is used to resize the rows. To Select One Row Click on the grey row heading at the left of the row

To Select Multiple Rows Click and drag (with the white cross cursor) down through the row headings

KAITECH SOLUTIONS LIMITED

2005

SELECTING THE WHOLE SPREADSHEET This is useful if you want to make a formatting change to the whole spreadsheet, i.e. make the text a bit bigger, or change the Font. Click on the small grey box at the top of the spreadsheet, it is located to the left of the column heading A and above row heading 1

Every cell in the spreadsheet will be selected, turning purple. Any formatting you apply now will affect the whole sheet.

TO DESELECT A SELECTED RANGE When you click to select a new cell, the previously selected cell/range is automatically deselected. Click on any cell in the spreadsheet

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

ENTERING DATA INTO CELLS

There are two types of data you can enter into a cell: text or numbers. Any data containing a text character is treated as text, i.e. January, Week 1 or 55B. Any data containing purely numbers is treated as a number, i.e. 5, 3.14 or 1998. You should use a full stop as a decimal point. If you put two full stops, or a comma, into a number it will be treated as text. Excel 2000 recognises and treats text and numbers slightly differently. The most obvious difference is that text will be automatically left aligned in a cell. Numbers will be right aligned. (You can override this alignment at any time). To Enter Data Into Any Cell Select the cell Type the data

Having typed the data, you need to enter it into the cell. The main method is to press [Return]. This will enter the data and then move you down to the cell underneath, ready to enter the next piece of data. Alternatively, you can also press the [Tab] key to enter the data and move across to the cell on the right. Press [Return] If the text you enter is too long to fit in a cell, it will spill across into the neighbouring cell on the right. If you enter data into the cell on the right, it will cut off the text. In this situation you should either keep the cell on the right empty, or increase the width of the column containing the first cell until it is wide enough to contain the text. As Excel 2000 is designed more for number storage than word processing, there is a limit to the amount of text you can enter into any cell. The limit is 112 characters in a cell.

KAITECH SOLUTIONS LIMITED

2005

AUTOFILL
The AutoFill feature is used to copy cell contents across or down the spreadsheet. This is useful if you have to type the same word or number into a group of neighbouring cells. To Use AutoFill Select the cell containing the data you wish to copy When a cell is selected it has a thick black border around it. In the bottom right hand corner of that border is a little black square called the fill handle. Position over the fill handle until the cursor changes to a black cross

Click on the fill handle and drag down or across the sheet, until the data series has reached the size you want (indicated by a flag next to the cursor), then release the mouse button

If AutoFill is used with dates or words relating to dates, such as months or days, it will produce a fill series. For example if you use the AutoFill feature on a cell containing the word January it will create the series: February, March, April, etc in the neighbouring cells. January February March April

This will work with months in full (January) or in short (Jan) and days of the week in full (Monday) or in short (Mon).

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

EDITING AND DELETING CELL CONTENTS

You can edit or delete the contents of any cell. Cell contents can be edited in the formula bar or directly in the cell itself. EDITING A CELL IN THE FORMULA BAR Click on the cell you wish to edit Click in the formula bar, to insert the flashing text insertion point

The contents of the cell will appear in the Formula Bar. The flashing line marks the point at which you can delete or add new text. The same editing rules apply here as in Word. You can use the left arrow [ ] and right arrow [ ] keys on the keyboard to move through the text one character at a time. You can use the [Backspace ] key to delete to the left, and the [Delete] key to delete to the right. Edit the text When you start editing the text, two new buttons will appear in the Formula Bar: a red cross and a green tick. The green tick is used to okay the changes you have just made, or you can press [Return]. The red cross is used to undo the changes you just made. Click on the green tick button, or press [Return]

EDITING A CELL IN THE CELL Alternatively, you can edit the contents directly in the cell. Double click on the cell to enter the edit mode Again you can use the left [ ] and right [ ] arrow keys to move through the text, and the [Backspace ] or [Delete] keys to delete the text. Edit the text To okay the changes, press [Return]

TYPING OVER A CELL You can replace the contents of any cell by typing over it, without having to delete the existing contents first. Select the cell Type the new data Press [Return]

TO DELETE CELL CONTENTS You can use this method to delete the contents of a range of cells, as well as one individual cell. Select the cell(s) Press [Delete]

KAITECH SOLUTIONS LIMITED

2005

UNDOING MISTAKES
Excel 2000 now contains a versatile Undo feature like Word. You can use it to undo the last 16 actions you performed in Excel. This includes actions such as: entering data, editing data and applying formatting. You can only undo actions in the reverse order that you performed them. Having Undone some recent actions, you can then change your mind and use the Redo button to perform the actions again. The Undo and Redo buttons are located on the Standard Toolbar. If they are not displayed they can be found by clicking on More Buttons.
Undo Redo

To Undo A Mistake If you make a mistake it is best to use the Undo button immediately. Click on the Undo button to undo the last action Click on the Undo button again To undo the action before that. You can keep clicking to undo the last 16 actions. To Redo What You Just Undid Click on the Redo to redo the last action you undid. Click on the Redo button again, for each undo you wish to redo

10

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

INSERTING NEW COLUMNS AND ROWS

You can insert new columns and rows into the spreadsheet at any point. INSERTING COLUMNS You can insert single or multiple columns into the spreadsheet. To Insert One New Column New columns are inserted to the left of the selected column. Click on a column heading to select the column to the right of where you wish to insert the new column In the "Insert" menu, select "Columns"

To Insert Multiple Columns Select the number of columns you wish to insert. The new columns will be inserted to the left of the selected columns. In the "Insert" menu, select "Columns" INSERTING ROWS You can insert single or multiple rows into the spreadsheet. To Insert One New Row New rows are inserted above the selected row. Click on a row heading to select the row underneath where you wish to insert the new row In the "Insert" menu, select "Rows"

To Insert Multiple Rows Click and drag through the row headings to select the number of rows you wish to insert In the "Insert" menu, select "Rows"

The new rows will be inserted above the selected rows.

11

KAITECH SOLUTIONS LIMITED

2005

DELETING COLUMNS AND ROWS

You can delete single or multiple columns. DELETING COLUMNS Use the column headings to select the columns you wish to delete In the "Edit" menu, select "Delete"

You can delete single or multiple rows. DELETING ROWS Use the row headings to select the rows you wish to delete In the "Edit" menu, select "Delete"

The row will be deleted and all the rows underneath will move up a row.

12

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

MOVING AROUND THE SPREADSHEET

There is a variety of ways of moving around the spreadsheet. The basic method is to use the scroll bars to scroll around the spreadsheet, and then click on the cell you wish to select. But as you add more data to your spreadsheet you will find it quicker to start using shortcuts to move around the spreadsheet. The up, down, left and right arrow keys are useful for moving small distances around the spreadsheet. SHORTCUT [ ] [ ] [ ] [ ] [Ctrl]+[Home] [Ctrl]+[End] [Page Down] [Page Up] [Alt]+[Page Down] [Alt]+[Page Up] [Ctrl]+[Page Down] [Ctrl]+[Page Up] Moves you one cell up Moves you one cell down Moves you one cell to the left Moves you one cell to the right Will take you to the first cell in the spreadsheet (A1) Will take you to the last used cell in the spreadsheet Will move you one screen view down the spreadsheet Will move you one screen view up the spreadsheet Will move you one screen view to the right Will move you one screen view to the left Will move you to the next sheet Will move you to the previous sheet RESULT

13

KAITECH SOLUTIONS LIMITED

2005

USING FORMULA
Excel 2000 has the facility to perform both simple and complex equations using formula. Here are some basic rules for working with formula. 1. 2. ALWAYS start a formula with an = sign NEVER put numbers in formula, ONLY cell references This lets Excel know that you are creating a formula, not just entering text or numbers. Always use the references of the cells you wish to act upon in your formula. Then, if the numbers in the cells change, the result of the formula will automatically update to show the correct answer. 3. Leave an empty row between the data and the totals row, but include the empty cell in the total

This is not a necessity, but it does make it easier to add extra rows into your data table, that will then be automatically included in the totals. It is also important when you start sorting your data (see Intermediate Excel 2000 Manual). To Create A Basic Formula Select the cell to contain the formula Type an = sign Enter the cell references that you wish to act upon and the appropriate mathematical operators between each cell reference

There are four main mathematical operators used in formula to perform calculations on your data. These are: Mathematical Operator Addition Subtraction Multiplication Division Keyboard Character + * / Formula Example =A1+A2 =A1-A2 =A1*A2 =A1/A2

When the formula is complete, press the [ Return] key Do not type spaces into a formula. Do not end the formula with a mathematical operator.

If you wish to create a formula that contains a mixture of operations, you will need to use brackets to let Excel know which operation should be performed first. For example: Whereas: =(A1+B1)*C1 =A1+(B1*C1) will add A1 to B1 and then multiply the result by C1 will multiply B1 by C1 and then add the result to A1

14

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

CREATING TOTALS
If you need to add up a large number of cells, you will not want to create a long formula, i.e. =A1+A2+A3+A4+A5+A6+A7+A8+A9 etc. To perform calculations on large ranges of cells, you must use a shortcut to express the range of cells, and then a Function to tell Excel what you would like to do to that range of cells. Example The range of cells A1, A2, A3, A4, A5 can be expressed as (A1:A5) The Function for adding up a range of cells is SUM All formula must start with an = sign The formula to total this range of cells is =SUM(A1:A5) To Total A Large Range Of Cells Select the cell you wish to contain the total Type = to start the formula Type the Function for adding cells, SUM Type the open brackets character ( Click on the first cell in the range Type a colon : Click on the last cell in the range Type the close brackets character ) Press [Return] to finish the formula

As calculating totals is the most common formula used in Excel, there is a toolbar button that can be used to automatically create the formula for you. It is called the AutoSum button.

15

KAITECH SOLUTIONS LIMITED

2005

AUTOSUM Select the cell you wish the total to appear in Click on the "AutoSum" icon on the Standard Toolbar. You may have to click on More Buttons on the toolbar to find the Autosum button.

Excel 2000 will guess which cells you wish to add up, by looking for the nearest group of cells.

A flashing dotted line will appear round the range of cells it has guessed you wish to add up. You must check this range to see if it is correct. If Excel has guessed correctly then click on the "green tick button" in the formula bar or press [Return] to finish the formula

If Excel has guessed INCORRECTLY then you will need to redefine the range that Excel is trying to add up. AT THIS POINT (before you do anything else) In the spreadsheet, click and drag through the cells you wish to add up Click on the "green tick button" in the formula bar or press [ Return] to finish the formula When the correct cells are selected, within the flashing dotted line.

16

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

EDITING AND DELETING FORMULA

When you create a formula in a cell, Excel will display the result of the formula in the cell. If you wish to view or edit the actual formula, you must use the Formula Bar. To Edit A Formula Select the cell containing the formula Click in the Formula Bar, and make the appropriate changes To okay your changes, click on the "green tick" button on the formula bar, or press [Return] The formula will be visible in the Formula Bar.

To Delete A Formula Select the cell containing the formula Press the [Delete] key

17

KAITECH SOLUTIONS LIMITED

2005

COPYING FORMULA
If you need to total up several columns of data, you can create the total for the first column, and then copy the formula across into the neighbouring columns. A B C 1 Jan Feb Mar 2 44 75 85 3 35 62 74 4 24 60 80 5 6 103 7 8 D

Because Excel formulae think in relative terms (to the cells that they are acting upon), when you copy a formula it will usually update to total the correct data. For example, in the picture above, the cell A6 contains the formula =SUM(A2:A5). Thus cell the formula, in relative terms, is thinking that it must add up the four cells above it. When you copy the formula into cell B6, the formula is still thinking that it must add up the four cells above it, so it changes the references in the formula to =SUM(B2:B5) to reflect the move. The easiest way to copy formulae, is to use the AutoFill feature. To Copy A Formula Select the cell containing the formula you wish to copy Click on the Fill Handle and drag across (or down) the cells you wish to copy the formula into Release the mouse button The Fill Handle will be visible in the bottom right hand corner of the selected cell.

18

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

OTHER FORMULA FUNCTIONS

Average Excel 2000 can provide you with the average of any numbers in a range of cells. For example =AVERAGE(A1:A5). Select the cell you wish to calculate the average in Type an equals = sign, to start the formula Type the Function code AVERAGE Enter the range you wish to act upon, in brackets, ie (A1:A24) Press [Return] to finish the formula

The Maximum Excel 2000 can also display the maximum value in a range of cells. For example =MAX(A1:A5). The Minimum Excel 2000 can also display the minimum value in a range of cells. For example =MIN(A1:A5). Count Excel 2000 can count the number of cells containing numbers (i.e. not empty) in a range of cells. For example =COUNT(A1:A5). Select the cell you wish to calculate the count in Type an equals = sign, to start the formula Type the Function code COUNT Enter the range you wish to act upon, in brackets, i.e. (A1:A24) Press [Return] to finish the formula Select the cell you wish to calculate the minimum in Type an equals = sign, to start the formula Type the Function code MIN Enter the range you wish to act upon, in brackets, i.e. (A1:A24) Press [Return] to finish the formula Select the cell you wish to calculate the maximum in Type an equals = sign, to start the formula Type the Function code MAX Enter the range you wish to act upon, in brackets, i.e. (A1:A24) Press [Return] to finish the formula

19

KAITECH SOLUTIONS LIMITED

2005

CUT, COPY AND PASTE

The Cut, Copy and Paste commands allow you to move or copy cells into different locations in the same sheet, in a different sheet, or even in a different workbook. The easiest way of cutting, copying and pasting cells is to use the Cut, Copy and Paste buttons on the Standard Toolbar (you may have to click on More Buttons). But you can also find these commands in the "Edit" menu.
Copy

Cut Paste

COPY AND PASTE The Copy and Paste commands are used to copy a range of cells to another location. Select the cell(s) you wish to copy Click on the "Copy" button

A flashing dotted border will appear around the copied cells. You must now proceed with the next steps before you do anything else, otherwise Excel will forget which cells it is copying. Select one cell, where you would like to place the copied cells Click on the Paste button

CUT AND PASTE The Cut and Paste commands are used to move a range of cells to another location. . Select the cell(s) you wish to cut. Click on the Cut button

A flashing dotted border will appear around the cut cells. You must now proceed with the next steps before you do anything else, otherwise Excel will forget which cells it is cutting. Select one cell, where you would like to place the cut cells Click on the Paste button If you start a copy or cut and change your mind, press the [ Esc] key to cancel. This removes the flashing dotted line from around the cut or copied cells.

TIP

20

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

THE CLIPBOARD TOOLBAR

This is a useful new feature in Excel 2000 which allows you to collect up to 12 items on the Clipboard and view the contents of the Clipboard. The Clipboard Toolbar is automatically activated when you cut or copy more than one item. Alternatively you can manually display the Clipboard Toolbar. Click on View in the menu bar Click on Toolbars Click on Clipboard

The Clipboard Toolbar appears as a floating toolbar which you can move around the screen. PASTE FROM THE CLIPBOARD As you cut or copy an item, it appears on the Clipboard Toolbar. You can then select an item from the Clipboard to Paste into your spreadsheet. You can view the contents of a Clipboard Item by resting the cursor over the item, without clicking. Select the cell where you want to Paste the item Click on the item in the Clipboard

Multiple items can be copied and pasted simultaneously. Display the Clipboard Select the cell(s) to be copied Select the cell where you want to Paste the items Click on the Paste All in the Clipboard

The items are pasted in the order in which they appear on the Clipboard. COPYING BETWEEN APPLICATIONS The Clipboard can be used to copy between Applications such as Excel, Word, PowerPoint, Access and Outlook. Display the Clipboard in the Application you want to copy from Select the item(s) to be copied Position the cursor where you want to Paste the items Click on the item in the Clipboard

21

KAITECH SOLUTIONS LIMITED

2005

DRAG AND DROP

The Drag and Drop feature is a quick way of moving cells around the spreadsheet. For moving small distances, it is slightly faster than using Cut and Paste. Select the cells you wish to move Position the cursor over the thick border, it will turn into a white arrow Click on the border, and drag across the spreadsheet Release the mouse button to drop the cells A thick black border appears around the selected cells.

A dashed border will appear on the spreadsheet to mark your position.

NB, With formula cell(s), the formula references and values displayed will not change, they will move intact, i.e., they will keep their original references and values.

DRAG AND COPY You can also use the drag and drop feature to copy cells. Select the cells you wish to move Position the cursor over the thick border, it will turn into a white arrow Press the [Ctrl] key and hold it down Click on the border, and drag across the spreadsheet A thick black border appears around the selected cells.

A dashed border will appear on the spreadsheet to mark your position, and a small + will appear by the cursor arrow to tell you you are copying. Release the mouse button to drop the copied cells Release the [Ctrl] key

22

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

WORKING WITH SHEETS

Each new workbook contains 3 different sheets. You can add extra sheets, or delete sheets, if you need to. If you need to enter different tables of data into your workbook you can put each table on a separate sheet. This makes it easier to locate and print the data. You can also rename the sheet, to give a name that describes the data it contains. You can create formulae that act across sheets (see Intermediate Excel 2000 Manual). This means that you could set up, for example, an annual expenditure workbook, where each month of data is put in a separate sheet. You could then name each sheet Jan, Feb, Mar, Apr, etc, and on a 13th sheet you could create totals for the whole year. Moving Between Sheets At the bottom of the screen are a series of tabs displaying the current sheet names. The default name is Sheet1, Sheet2, etc. The active sheet tab is shown in white.

To move to another sheet, click on the sheet name tab

RENAMING A SHEET The sheets can be given names that are up to 31 characters long. Double click on the sheet name tab Type in a new name To okay the new name you can either press [Return], or click on a cell to return to your spreadsheet The current name will be selected

INSERTING A NEW SHEET You can insert up to 250 sheets into your workbook. New sheets are inserted before (to the left of) the sheet that is currently selected. Select the sheet that you would like to insert the new sheet in front of In the Insert menu, select Worksheet

The new sheet will appear in the workbook, and will be given the next available default name, i.e. Sheet4, Sheet5, etc. MOVING SHEETS You can move a sheet, using click and drag, to change the order of the sheets in your workbook. Click and drag the tab, of the sheet you wish move, across all the sheet tabs Release the mouse when the triangle is in the correct position A black triangle will appear, marking the position that the sheet will be moved to.

23

KAITECH SOLUTIONS LIMITED

2005

SAVING YOUR WORKBOOK

You can save a workbook at any time.

TIP

It is advisable to save your work on a regular basis, i.e. every 15 minutes, in case of power failure or accidental pushing of the wrong buttons.

If you close a workbook you will be prompted to save it on exit. If you close Excel 2000 you will be prompted to save the changes in any workbooks you have open at that point. The easiest way to save your work is using the "Save" button on the Standard Toolbar. SAVING A WORKBOOK THAT HAS NOT BEEN SAVED BEFORE The first time you try to save a workbook you will need to give the file a name and choose where you want to save it. Click on the "Save" button The Save As dialog box will appear.
Save In Box Up One Level Button

Contents of Save In Folder

File Name Box

File Type Box

Save Button Cancel Button

The File Name box contains the suggested file name [Link], but you should type over this to give your workbook a name that describes the data it contains. All Excel files have the three character extension .xls, but you do not have to type this in as Excel will do it for you automatically when you save the file. Files in Windows NT can have long names (up to 250 characters), and can also include spaces. However, it is best not to use file names that are longer than 30 characters.

24

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

If you will ever need to mail this file to someone who is still using Excel 5, you must not save the file with a name longer than 8 characters. You must also save the file as an Excel 5 file type (rather than Excel 2000). Type a name for the file into the File Name box

You now need to choose a folder to save your work in. The Save In box displays the currently selected folder. The contents of the selected folder are displayed in the Contents area. You can double click on a folder in the Contents area to open that folder. Or you can click on the "Up One Level" button to move one level up in the folder structure. Navigate through the folder structure to open the folder you wish to save your file in

Saving Your Workbook As An Excel 5 File If you will ever need to mail this file to a person who is still using Excel 5, you will need to save the file as an Excel 5 file type. This is because you cant open Excel 2000 files in Excel 5 (though you can open Excel 5 files in Excel 2000). In the Save As Type box, select Microsoft Excel 5.0/95 Workbook

When you have given the file a name and chosen where to save it. Click on the "Save" button The workbook is now saved with the name you gave it, in the selected folder. The new workbook name will be displayed in the blue Title Bar at the top of the screen.

RE-SAVING A WORKBOOK If you have made changes to a workbook since you last saved it, you will need to save it again before you close it. You should also resave your work on a regular basis (approx every 15 minutes). Click on the "Save" button

The workbook will be resaved, i.e. this version of the workbook will be saved over the last saved version of the workbook.

DUPLICATING A WORKBOOK If you wish to save a file without saving over the previously saved version, you can use the "Save As" command to create a copy of the file. In the workbook you wish to create a copy of. In the "File" menu, select "Save As" Change the file name, in the File Name box The Save As dialog box will appear. You can then save the file in the same folder, or save it in a different folder

25

KAITECH SOLUTIONS LIMITED

2005

Move to a different folder (if you want to) Click on the "Save" button

26

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

OPENING WORKBOOKS
You can open/create a brand new blank workbook, or you can open an existing workbook (i.e. one that has been saved before). OPENING A NEW BLANK WORKBOOK Click on the "New" button in the Standard Toolbar.

A new blank workbook will open up, with the default name Book1, Book2, Book3, etc. OPENING AN EXISTING WORKBOOK Click on the "Open button on the Standard Toolbar

The Open dialog box will appear.

Look In Box Up One Level Button

Contents Of Look In Folder

Open Button Cancel Button

The "Look In" box contains the name of the folder you are currently looking in. The contents of the current folder are displayed in the Contents area. You can double click on a folder in the Contents area to open it. Or you can click on the "Up One Level" button to move up one level in the folder structure. You need to navigate through the folder structure on your computer to locate the folder containing the file you wish to open. Navigate to the folder containing the file The file will then be visible in the contents area. All Excel files end in the three character extension .xls. Click on the file in the Contents area to select the file

27

KAITECH SOLUTIONS LIMITED

2005

Click on the "Open" button to open the selected file

RECENTLY USED FILES Excel 2000 remembers the last four files you worked on (in Excel 2000). You can select them in the "File" menu to locate and open them quickly. Click on the "File" menu Click to select the filename you want to open At the bottom of the menu you will see a list of last 4 files used. Excel 2000 will locate and open the file (unless it has been moved to a different folder in Windows Explorer since you last worked on it).

OPENING MULTIPLE WORKBOOKS In Excel 2000 you can have several workbooks open simultaneously. Each open workbook sits in its own window. The last workbook opened sits on the top layer, with all the others hidden behind it.

TIP

The more workbooks you have open, the slower your computer will run. If you have finished using a workbook, save it and close it down.

To Move Between Open Workbooks Click on "Window" in the menu bar Select the file you wish to view The Window menu will drop down, at the bottom is a list of all the open workbooks. The selected file is then brought to the front window layer. The previous file is still open, but is now hidden behind this file.

28

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

CLOSING A WORKBOOK
It is good practice to save your workbook (by clicking on the "Save" button) before you try to close it. However, Excel 2000 will always ask you if you wish to save unsaved changes. To Close A Workbook In the "File" menu, select "Close"

The Close/Save Safety Net Fortunately Excel 2000 can tell if your workbook contains changes that have not been saved. If you try to save your workbook, it will ask you if you wish to save the unsaved changes. The following dialog box will appear.

Click on the "Yes" button to save the changes and then close the workbook or Click on the "No" button to close the workbook without saving the changes . If you choose this option, you will not be able to retrieve the unsaved changes. or Click on the "Cancel" button to return to your workbook

TIP

Save your workbook every time you are happy with it. Then if you suddenly mess it up, you can close it down without saving the changes to revert it back to the version you were last happy with.

29

KAITECH SOLUTIONS LIMITED

2005

BASIC FORMATTING
Having entered your data into the spreadsheet, you can apply formatting to change the way it looks. The quickest way to format your data is using the buttons on the Formatting Toolbar.
Font Size Bold

Font Italics Underline

To Change The Font The Font controls the 'look' of the text. Select the cells you wish to format Click on the "Font" drop down arrow to bring up a list of all the available Fonts

The Fonts are listed in alphabetical order and appear as they will look in the spreadsheet. Click to select a Font

To Change The Text Size Select the cells you wish to format Click on the "Font Size" drop down arrow to bring up a list of sizes Click to select a size

30

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

To Make Text Bold, Italic or Underlined Select the cells you wish to format Click on the "Bold", "Italic" and/or "Underline" buttons (you may have to click on More Buttons to find these).

31

KAITECH SOLUTIONS LIMITED

2005

ALIGNMENT FORMATTING
Alignment formatting controls the position of the data within each cell. The default alignment for data, is that text is automatically left aligned in each cell, and numbers are right aligned. You can override this at any time by applying different alignment formatting.
Center Align

Left Align

Merge and Center

Right Align

CHANGING THE ALIGNMENT Select the cells you wish to format Click on the appropriate alignment button

You can also centre the contents of one cell across several cells using the "Merge and Center" button. CENTERING TEXT ACROSS CELLS Text can be centered across a range of cells. This is useful for centering a heading across a number of cells to create a title. See the Monthly Budget heading in the diagram below.
A 1 2 3 4 5 6 7 8 B C D Monthly Budget Jan Feb Mar 300 300 300 105 95 120 60 65 60 10 23 15 475 483 495

Rent Food Travel Drink Total

Unfortunately the button used in Excel 5 to centre text across cells, has been changed slightly in Excel 97 / 2000. This is now the Merge and Center button, which MERGES the selected cells together. However this will cause problems when you try to select columns containing a merged cell. I suggest you do not use it. To centre text across cells, you will now have to go through the Format menu.

32

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

Select the cells you wish to centre the text across. The text to centre must be in the leftmost cell in the selected range

Click on the

button

To Remove Center Across Selection Formatting You can remove this formatting by going through the Format Cells dialog box. Select the all the cells containing this formatting In the Format menu, select Cells

The Format Cells dialog box will appear.

In the Horizontal drop down menu, select Left Indent Deselect the Merge Cells option under Text Control Click OK Click on the Left Align button on the Formatting Toolbar

33

KAITECH SOLUTIONS LIMITED

2005

RESIZING COLUMNS AND ROWS

Every column and row in your spreadsheet can be resized to change the width or height. There is an AutoFit feature that can be used to make your columns just wide enough to fit the longest piece of text in each column. This is useful for fitting as much data on one page as possible without cutting off any text. CHANGING THE WIDTH OF COLUMNS To Resize A Single Column Click on the column heading to select the column you wish to change In the column headings, position the cursor on the right border of the column heading. It will change to a double headed black arrow Click and drag the border to resize the column

To Resize Multiple Columns If you select the whole spreadsheet, you can use this method to resize every column on the sheet. Click and drag across the column headings to select the columns you wish to change In the column headings, position the cursor on the right border of one of the selected column headings. It will change to a double headed black arrow Click and drag the border to resize all the selected columns

AutoFitting Columns Click and drag across the column headings to select the columns you wish to change In the column headings, position the cursor on the right border of one of the selected column headings. It will change to a double headed black arrow Double click on the column heading border

34

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

CHANGING THE HEIGHT OF ROWS The default height for rows is based on the size of the text in that row. As the text size increases, the row height will automatically increase. When you resize a row manually, the row is fixed at that height until you resize it again. You can return a row to the default setting using the AutoFit feature. To Resize A Single Row Click on the row heading to select the row you wish to change In the row headings, position the cursor on the bottom border of the row heading. It will change to a double headed black arrow Click and drag the border up or down to resize the column

To Resize Multiple Rows If you select the whole spreadsheet, you can use this method to resize every row on the sheet. Click and drag across the row headings to select the rows you wish to change In the row headings, position the cursor on the bottom border of one of the selected row headings. It will change to a double headed black arrow Click and drag the border to resize all the selected rows

AutoFitting Rows Click and drag across the row headings to select the row you wish to change In the row headings, position the cursor on the right border of one of the selected row headings. It will change to a double headed black arrow Double click on the row heading border

35

KAITECH SOLUTIONS LIMITED

2005

NUMBER STYLES
When you enter numbers into your spreadsheet you do not need to type in pound signs, commas for thousands or percentage signs. You can apply these characters to your data very quickly using the Number Style buttons on the Formatting Toolbar.
Increase Decimal Decrease Decimal

Currency Style

Percent Style

Comma Style

This is what each Number Style button does: Currency Style: Percent Style: Comma Style: Increase Decimal: Decrease Decimal: Applies a sign in front of each number, commas for thousands and displays your numbers to two decimal points. Multiplies the numbers in the cells by 100 and puts a % sign after each number. Applies commas for thousands and displays your numbers to two decimal points. Increases the number of digits showing after the decimal point. Decreases the number of digits showing after the decimal point.

To Apply Number Styles The number style you apply does not affect the actual cell value that Excel uses to perform calculations. The real value in each cell can be viewed in the formula bar. Select the cells containing the numbers you wish to format Click on the appropriate number style button

A wider selection of number styles can be found in the Format Cells dialog box. To access these options - in the "Format" menu, select "Cells", then select the "Number" tab.

36

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

APPLYING BORDERS TO CELLS

By default, every cell in the spreadsheet has a thin grey border around it, called a 'gridline'. These gridlines will show when you print out your spreadsheet. But you can apply thicker borders to selected cells. You can also turn off the gridlines when you print, so that the only borders visible are the ones you have applied. (See section on Page Setup options for turning off gridlines). The easiest way to apply borders to cells is using the Borders button on the Formatting Toolbar.

Borders

To Apply Borders Select the cells you wish to format Click on drop down arrow on (the right half of) the "Borders" button A drop down menu will appear containing different border styles

Click to select a border style

To Remove Borders Select the cells you wish to format Click on drop down arrow on (the right half of) the "Borders" button to bring up the border choices Select the "No Border" option (the first choice in the top left hand corner of the menu)

37

KAITECH SOLUTIONS LIMITED

2005

APPLYING COLOURS
There are two colours options. You can change the colour of the text in each cell, or you can change the colour of the actual cell itself. The easiest way to apply colours is using the colour buttons on the Formatting
Fill Colour

Font Colour

Toolbar.

If you are printing out on a black and white printer, the colours will appear as shades of grey. Some printers print out grey better than others. You should also remember that if you have to photocopy your work, dark greys will start to go black, and light greys will fade to white. To Change The Colour Of Cells Select the cells you wish to format Click on the drop down arrow to the right of the "Fill Color" button Click to select the colour you prefer

A selection of colours will appear. Use the "No Fill" option to remove fill colour (rather than applying white).

To Change the Colour Of Text Select the cells you wish to format Click on the drop down arrow to the right of the "Font Color" button

38

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

A selection of colours will appear. Click to select the colour you prefer Use the "Automatic" option to return the text to the default colour..

39

KAITECH SOLUTIONS LIMITED

2005

REMOVING FORMATTING
The data in a cell is treated separately from the formatting applied to that cell. This makes it possible to delete just the formatting in a cell but not the data (the contents). Similarly you can delete the contents without affecting the formatting. One result of this is that an empty cell can still have formatting applied to it, such as Bold or a Number Style, which will only become apparent when you enter some data into that cell. To Clear Formatting All: Formats: Contents: Comments: Select the cell(s) you wish to remove the formatting from In the "Edit" menu, select "Clear" Removes the formatting and the data in the cells Removes just the formatting Removes the data in the cells (but not the formatting) Removes the comments attached to a cell

A further sub-menu will appear. These are the options:

Select Formats

The formatting will be removed without affecting the data contained in the cells. The quick way to remove just the contents, is to select the cells and press the [Delete] key on the keyboard.

40

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

USING ZOOM
The Zoom control allows you to change the magnification at which you view your spreadsheet. You can use it to zoom in on your spreadsheet to see the data in more detail, or to zoom out to see a larger area of the spreadsheet. The easiest way to change the magnification is to use the Zoom control on the Standard Toolbar.
Zoom

To Change The Magnification Click on the down arrow to the right of the Zoom Control box The magnification menu will appear. A bigger percentage zooms you in more on your spreadsheet. Click to select a magnification

To return to the default magnification, select 100%.

41

KAITECH SOLUTIONS LIMITED

2005

FREEZING TITLES
When a spreadsheet starts to contain a lot of data, you will find that the titles in a row at the top of the sheet, or in a column to the left of the sheet, will disappear as you scroll around the sheet. This makes it harder to input new data, as you become unsure which is the right column or row to use. You can use the Freeze Panes command to freeze a row or column of headings, so that they remain visible as you scroll across the spreadsheet.

To Freeze A Row Of Titles In the Row Headings, click to select the row underneath your titles In the Window menu, select Freeze Panes

Every row above the selected row will be frozen in place as you scroll up and down the sheet.

To Freeze A Column Of Headings In the Column Headings, click to select the column after your titles In the Window menu, select Freeze Panes

Every column before the selected column will be frozen in place as you scroll left and right across the sheet.

To Freeze Both a Row of Titles and a Column Of Headings Select the cell to the right of your column of titles and in the row below your row of headings In the Window menu, select Freeze Panes

Every column before the selected column and above the selected row will be frozen in place as you scroll around the sheet.

To Unfreeze Your Titles In the Window menu, select Unfreeze Panes

42

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

WORKING WITH DATES

Excel recognises dates in a variety of formats. For example: 4 June 1998, 4-Jun-98, 04/06/98 or 4/6/98. You can type dates straight into any cell.

TIP

The easiest way to enter dates, is in the format 4/6/98, because you can use the numeric keypad (on the right of the keyboard) to enter all these characters (including the slash /).

Dates are actually treated as numbers. This enables you to use them in formula. For example, you can subtract one date from another to calculate the length of time between the two dates (in days). Warning - Do not use the format 04.06.98. Excel 2000 treats this as text and not a date.

There are several shortcuts for inserting dates into cells. To Insert A Fixed Date A fixed date stays the same whatever day you open the workbook. Press the keyboard shortcut [Ctrl]+[;] Alternatively you could just type it in. To Insert A Rolling Date A rolling date updates each day to display the current date. Enter the formula =TODAY() This is very useful if you are using a date in a calculation. For example, if you wish to calculate a persons age by subtracting their date of birth from the current date. As the rolling date changes each day, the result of the calculation will also change. APPLYING DATE FORMATS To access the available date formats. Select the cells containing the dates In the Format menu, select Cells

The Format Cells dialog box will appear. The date formats are located in the Number tab. Click on the Number tab In the list of Categories, select "Date", then choose a date format in the "Type" list Click on "OK"

NB The date types 3/4/98 and 03/04/98 are American, where the day and the month is switched around.

43

KAITECH SOLUTIONS LIMITED

2005

PRINTING SPREADSHEETS
Because each spreadsheet is vast, Excel 2000 will only print out the part of it which contains data. If there is a lot of data on a sheet, it will print out on multiple pages. You can also print out a selected range of cells. The Print Preview feature allows you to see how the spreadsheet will print out before you print it (see section on Print Preview). The Page Setup options are used to change your page orientation to portrait or landscape, to change the margins, to stop the gridlines from printing, or to scale your data to make it print out on a specified number of pages. (See section on Page Setup).
Print Preview

Print

Printing With The Print Button The Print button is used to print out all the data on the current sheet. Click on the "Print" button If you want to print out only part of the data, you will need to bring up the Print dialog box. Printing Part Of A Spreadsheet If you wish to print out a certain range of cells, select the cells In the "File" menu, select "Print"

The Print dialog box will appear. The "Print Range" options allow you to select a page range to print from and to (i.e. from page 5, to 9). Select a Page Range

The "Print What" options allow you to print out the cells currently Selected in the sheet. Select the "Selection" option Click on "OK"

44

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

PRINT PREVIEW
The Print Preview facility allows you to see how your spreadsheet will actually look when you print it out.
Print Preview

Print

To Print Preview Your Spreadsheet Click on the "Print Preview" button on the Standard Toolbar The Print Preview screen will appear, showing you how your data will look when it is printed. At the bottom of the screen, in the status bar, it will tell you which page you are viewing and the total number of pages that your data spreads across.

At the top of the screen is a bar containing a variety of buttons: Next: Previous: Print: Setup: Close: Help: Moves you to the next page of data on your sheet. Moves you to the previous page of data on your sheet. Brings up the Print dialog box. Brings up the Page Setup dialog box (see next section). Closes down Print Preview, returns you to your spreadsheet. Brings up the Help menu To see your data in more detail, click on the page to zoom in. Click again to zoom back out. If you are happy with the way it looks, click on the " Print" button to bring up the Print dialog box Click on the "Close" button to return to the spreadsheet

45

KAITECH SOLUTIONS LIMITED

2005

PAGE SETUP
The Page Setup options are used to change your page orientation to portrait or landscape, to change the margins, to stop the gridlines from printing, or to scale your data to make it print out on a specified number of pages. To Open The Page Setup Dialog Box In the "File" menu, select "Page Setup"

The Page Setup options are divided into four categories: Page, Margins, Header/Footer and Sheet. The tabs at the top allow you to access each category. The Page Tab Options Orientation: Scaling: Choose Landscape or Portrait. You can use this to scale your data up or down. The "Adjust To" option lets you choose a percentage amount. The "Fit To" option forces the data to print out on a specified number of pages. Lets you change the paper size. The default is A4.

Paper Size:

46

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

The Margins Tab Options

Page Margins: Center On Page :

Choose a Top, Bottom, Right and Left margin for each page. Centres the data on the page if only part of the page has been used. You can centre on the Horizontal and Vertical axes.

The Sheet Tab

Print Area: Rows To Print At Top:

You can enter a range reference here to define the print area. Enter a row reference to print out the data in that row as headings at the top of every page. Use the format $5:$5 to set row 5 as the print row. Enter a column reference to print out the data in that columns as headings on the left of every page (in the format $A:$A to set column A) Turn off to stop gridlines from printing out. To set the sequence the pages will print out in

Columns Print At Left:

Print Gridlines: Page Order:

47

KAITECH SOLUTIONS LIMITED

2005

The Header/Footer Tab The Header is text that prints out at the top of every page. The Footer is test that prints out at the bottom of every page. You can see previews of the current Header and Footer. To Change the Header or Footer Click on the "Custom Header" or "Customer Footer" button

The Header or Footer dialog box will appear. Format Page Page Number Numbers Date Time File Reference Sheet Name

The Header and Footer areas are split into three sections: Left, Center and Right, i.e. the Header sections are - top left, top middle, or top right of page. Click in a section and type whatever text you wish to add or click on a button to insert a special code that will automatically print out: page numbering, the total number of pages, the current time, the current date, the file name, the sheet name To format the text in the Header or Footer, drag through the text to select it and click on the "Format" button. A dialog box will appear in which you can make formatting selections, then click "OK" To okay all your changes, click "OK"

48

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

CREATING GRAPHS AND CHARTS

You can create Charts (or graphs) from your data to display it in a more interesting format. To Create A Chart Select the cells containing the data you wish to build your chart from
1 2 3 4 5 6 7 8 A B C D Monthly Budget Jan Feb Mar Rent 350 350 350 Food 100 110 95 Travel 60 75 65 Drink 10 0 25 Total 510 535 510

Generally this is all the numbers (except the totals), one row of headings at the top of the data, and one column of headings on the left of the data. In the "Insert" menu, select "Chart"

Step 1 of the Chart Wizard dialog box will appear. The Chart Type box contains all the chart types available. Avoid using Doughnut, Radar and Surface charts, as they are difficult to read. Select a Chart Type

Having selected a main chart type, you can then select a sub-type of that chart. This includes 2D or 3D versions of the chart. Select a Chart Sub-type

Click on the "Next" button to move on to the next step

You can click on the "Back" button at any stage to return to the previous step.

49

KAITECH SOLUTIONS LIMITED

2005

Step 2 of the Chart Wizard will appear, containing a preview of your chart. There are always two ways of setting up a data table in a spreadsheet. The data can be arranged by row or by column. You can change the way the chart displays the data, using the "Series In" "Rows" or "Columns" options

If you are happy with the chart preview. Click on the "Next" button

Step 3 of the Chart Wizard will appear. In this step you can give your chart a Title, an X axis title, and a Y axis title (Z axis for 3D charts). Enter a Chart Title Enter an Category X axis title Enter a Category Y or Z axis title Click on the "Next" button

Step 4 of the Chart Wizard will appear

50

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

In step 4, you can choose to create the chart as a new sheet, or as an object that will float over the data in your spreadsheet.

Select "As New Sheet" Type a name for the chart sheet into the box on the right

You are now ready to create the chart. If you have made any mistakes, you should use the "Back" button at this point to return to the previous steps. Click on the "Finish" button to create the chart The chart will be inserted as a whole page, on its own sheet. The chart sheet will be inserted into the workbook in front of the sheet containing the data. The Chart maintains a link with the data it is based on. If the data changes, the chart will automatically update.

To Print Your Chart Click on the "Print" button

To Delete The Chart The chart is a whole sheet, so you will need to delete the sheet to remove it from the workbook. Click on the Chart sheet tab to select the chart In the "Edit" menu, select "Delete Sheet" Click "Yes"

You will be asked if you are sure you wish to delete it.

51

KAITECH SOLUTIONS LIMITED

2005

PERCENTAGES
Here is a guide to calculating percentages in Excel 2000. If you have set up a table showing your expenditure figures for January, as shown on the right. You can calculate the percentage of your monthly total spent on any individual item, by dividing the individual item amount by the total amount. For example, the percentage of total expenditure spent on rent =340/500 Obviously you should use cell references in the formula, rather than the data. So the formula for the percentage of total expenditure spent on rent =B4/B9 This will give you an answer that will be between 0 and 1. In this case the answer will be 0.68 (equal to 340/500). To Turn The Answer Into A Percentage Select the cell and click on the "Percent" button on the Formatting Toolbar to apply percent number style formatting
1 2 3 4 5 6 7 8 9 A B Monthly Expenditure Item Rent Food Travel Drink Total Jan 340 100 60 10 500

This button multiplies each number by 100 and then puts a % sign after it. In this case the percentage of the monthly total spent on rent will be 68%.

52

EXCEL 2000 BASIC USER MANUAL

KAITECH SOLUTIONS LIMITED

2005

KEYBOARD SHORTCUTS
Although using the mouse is usually the quickest way of working in Excel 2000 there are times when keyboard shortcuts may be faster. Here are some useful keyboard shortcuts: Navigation Shortcuts [Ctrl]+[Home] [Home] [Page Down] [Page Up] [Alt]+[Page Down] [Alt]+[Page Up] [Ctrl]+[Page Down] [Ctrl]+[Page Up] Selection Shortcuts [Shift]+[Spacebar] [Ctrl]+[Spacebar] [Ctrl]+[A] Editing Shortcuts [Ctrl]+[Z] [Ctrl]+[X] [Ctrl]+[C] [Ctrl]+[V] General Shortcuts [Ctrl]+[S] [Ctrl]+[P] Saves the workbook Brings up the Print dialog box Undoes the last action Cuts the selected cells to the clipboard Copies the selected cells to the clipboard Pastes cells into the spreadsheet Selects current row Selects current column Selects the whole spreadsheet Will take you to the first cell in the spreadsheet (A1) Moves you to the start of the current row Will move you one screen view down the spreadsheet Will move you one screen view up the spreadsheet Will move you one screen view to the right Will move you one screen view to the left Will move you to the next sheet Will move you to the previous sheet

53