Happy Computers Essentials Guide to
Microsoft Excel 97 for Windows 95
Copy This Manual!
This course guide is produced for the Happy Computers “Excel 97” course.
For all your computer training needs, contact:
Happy Computers
St James House
10 Rosebery Avenue
London EC1R 4TD
Bookings: 0171 278 5596
E-mail:
Copies of this guide can be obtained from Happy Computers, fully bound, at a cost of £15 each, or £10 for extra copies for organisations who have booked courses.
Happy Computers allows this guide to be copied, provided the name and phone number of Happy Computers remains on the copies.
© Happy Computers 1996 V3b 2/1/97
Microsoft Excel 97 for Windows 95 - Basic PrinciplesContents
Getting Started In Excel4
About This Manual...... 5
Starting and Exiting Excel?...... 6
The Excel Screen...... 7
The Mouse Icons...... 8
What Are All Those Pictures All Over The Screen?...... 9
Using the Mouse...... 11
The Right Mouse Button...... 12
The Office Assistant...... 13
Basic Principles14
Cursor Movement & Functions Keys...... 15
Selecting Cells...... 17
Selecting Columns & Rows...... 18
Basic Text & Number Entry...... 19
Correcting Mistakes...... 20
Undo & Redo - A Licence To Make Mistakes!...... 21
Using AUTOFILL To Make Copying Easy!...... 22
Erasing Cells...... 24
Getting Excel To Do Your Calculations!...... 25
Adding Up Numbers With The SUM Function...... 27
Using AUTOSUM For Quick SUM's...... 28
Principle: Always Include Blank Line in SUM()...... 29
Percentages...... 30
Other Functions That You Can Use...... 32
The Paste Function...... 33
Inserting & Deleting Rows and Columns...... 35
Saving Your WorkBook...... 37
More Options In Saving...... 38
Opening, Creating and Closing Workbooks...... 40
Managing Files in the Open Dialog Box...... 42
Moving And Copying Things...... 46
Copying Formulas...... 47
Absolute Cell References & F4...... 48
The Golden Rule Of Good Spreadsheet Design...... 50
Never Put A Number In A Formula...... 50
Principle: Calculate in One Direction...... 51
Principle: Check your Spreadsheet...... 52
Using Sheets In A Workbook...... 53
Making It Look Good55
Changing The Look Of Your Text & Numbers...... 56
Aligning Text & Numbers...... 57
Other Options In Aligning Text & Numbers...... 58
Changing The Font (The Typeface)...... 60
Changing The Look Of Numbers...... 62
Making Columns Wider & Rows Taller...... 65
Dates & Times...... 67
Adding Lines And Borders...... 68
Adding Colour And Shading...... 70
Use AUTOFORMAT for instant style!...... 71
Conditional Formatting...... 72
Removing & Copying Formatting...... 73
Seeing More Of The Worksheet On Screen...... 74
Printing75
Basic Printing...... 76
Print Preview: So What Will It Look Like On Paper?...... 77
Page Setup...... 78
Making The Printed Output Look Good...... 80
Headers & Footers...... 81
Creating Charts82
Charts: With the Chart Wizard...... 83
Working With A Chart If It Is Inserted As An Object In A Sheet...... 86
Using The Chart Toolbar...... 87
Changing Your Chart After Creating It...... 88
Formatting Your Chart...... 89
Printing A Chart...... 90
Charts: Two Dimensional or Three Dimensional?...... 91
Charts to Display Trends...... 92
Charts to Display Proportions...... 93
Charts to Display Trends & Proportions...... 94
Making Your Spreadsheet Easier To Use95
Two Useful Design Rules...... 96
Grouping Sheets...... 97
Creating Formulas That Link Worksheets & Workbooks...... 98
Adding Comments To Your Worksheet...... 99
Adding A Post-It Type Comment...... 101
Creating Range Names...... 102
Using Named Ranges...... 103
Protecting Your Work...... 105
Protecting A File...... 107
Using The Toolbars...... 108
Spreadsheet Templates...... 109
Excel Spreadsheet Information In Word...... 110
Appendix112
Error Messages...... 113
Selected List of Functions...... 114
Full List Of Functions (Alphabetical)...... 116
Glossary118
Index122
Happy Computers 0171 278 5596Page 1
Microsoft Excel 97 for Windows 95 - Basic PrinciplesGetting Started In Excel
Objectives:
By the end of this section you will be able to:
- Start and Exit from Excel
- Recognise the different parts of the Excel spreadsheet
- Recognise the different mouse icons
- Use the right mouse button
- Use the Office Assistant
About This Manual
This manual has been written by Happy Computers for the Excel 97 courses. It is meant as a learning tool for use during the course and a way of exploring further elements of Excel after the course. It is not a replacement to the huge manuals that come with Excel, but rather a step by step guide to the most useful features.
Write Your Own Manual!
There are sections in this manual that are left blank and it is intended that you should fill these in during the course. Use lots of colour and make it fun, that way it's easier to remember. The manual is only printed on one side of paper so you have plenty of space to make lots of your own notes.
Step by Step Instructions
There are sections within the manual that are step by step guides. These use the following style
Things you should do are hereA description of what's happening is here
Click On FileThe File Menu will drop down
Click On Print PreviewThe Print Preview Box will appear
Each step by step section is self contained, you should not need to refer to another part of the manual to get it to work.
Let Us Know What You Think
We are always searching for ways to improve our service, and are very keen to hear you thoughts on any aspects of this manual, or indeed, any part of Happy Computers!
Starting and Exiting Excel?
Starting Excel 97
Click on the Start Button The Start menu appears
Click on ProgramsThe Programs menu will appear
Click on Microsoft ExcelExcel will start
Exiting Excel 97
Click on the File Menu
Choose Exit
or
Click on the cross on the top right corner of the screen -
The Excel Screen
What Does What And Where?
Excel looks like this. Throughout the course you can mark in the names of certain areas of the screen so that you can build up your own personal reference about what does what! A more detailed look at the toolbar is on the next page
The Mouse Icons
The mouse icon changes according to where it is on the screen. This is because it performs different functions depending on where it is on-screen. The most common ones in Excel are:
Fill in your own description of these icons as the course progresses.
What Are All Those Pictures All Over The Screen?
The Standard Toolbar
NewFormat PainterSort Ascending
OpenUndoSort Descending
SaveRedoChart Wizard
PrintInsert HyperlinkMap
Print PreviewWeb toolbarDrawing
SpellingAutoSumZoom
CutPaste functionOffice Assistant
Copy
Paste
The Formatting Toolbar
FontRight alignIncrease indent
Font sizeMerge and CenterBorders
BoldPercentageFill colour
ItalicsCommaFont colour
UnderlineIncrease decimal
Left alignDecrease decimal
CentreDecrease indent
Tool Tips
You don’t have to remember what all the buttons do. You can use ToolTips.
Move the mouse cursor over the tool for a second and a ToolTip will pop up and a description will appear on the status bar at the bottom of the screen
Using the Mouse
Excel is designed around the 'mouse'. This is a small piece of plastic, with between one and three buttons, which rolls around the desk. As it rolls the mouse 'icon' moves around the screen.
Normally it is the LEFT MOUSE BUTTON that you press to make things happen.
The mouse is designed to make using a computer more in line with how people think. Once you are used to it, a mouse can enable remarkable speed and ease of use. The main mouse actions are:
Point and ClickRoll the mouse around the desk until the icon is at the required place on screen. (Either the required cell or the required menu option.) Click the left-hand button to select.
Point and double-clickClick on an option quickly twice to call up that option without having to click on OK.
Click and DragTo define a range, or re-size a window, you must click at the required point and drag the mouse, without releasing your finger, across as desired.
Drag and DropYou can move parts of your worksheet about the page by clicking on the edge of the selected area, holding down the mouse button, dragging to a new location then 'dropping' the item on to the new place. (Is this mouse droppings!!?)
Using the Keyboard instead of the Mouse
It is generally easier with a mouse, but it is possible to perform nearly all the functions in Excel using only the keyboard. Use the arrow keys to move the cursor box around the worksheet.
Selecting Menus:Note which letter is underlined in the required option. Hold down the ALT key (towards bottom left of keyboard) and press this letter.
Selecting Menu Options:Press letter which is underlined in option that you want.
Dialogue Boxes:Press TAB to move between different selections in Dialogue Boxes.
The Right Mouse Button
Many of the things described in this manual can also be performed in another way. One of the most useful is the Short-Cut menu. This provides quick access to some menu commands, and is especially useful for:
- Formatting Numbers, and Text
- Cut & Pasting
- Inserting & Deleting Rows & Columns
- Adding Patterns and Borders
- Showing Toolbars
- Inserting comments
- Formatting cells
Using The Short-Cut Menu
Click the right mouse button while the cursor is over a selected area or part of the screen
The options on the short-cut menu will change depending on what you are doing at the time so selecting a row or column will give you the option to change row height or column width, for example.
The Office Assistant
What Does It Do?
The Office Assistant watches what you are doing and then...
- Gives you a particular tip once during a Microsoft Excel session.
- Helps you to search for help in Microsoft Excel.
Using The Office Assistant
Click on the Office Assistant icon
The Office Assistant Box will be displayed
Type your question into the entry box
Press Search.
Click on the appropriate bullet.
Scrolling Through Your Tips
Click on the Office Assistant icon
Click on the Tips button
Click on the Back or Forward buttons to scroll through your tips.
Click on Close to close the tips box.
Closing The Office Assistant
Click on Close to hide the Office Assistant box
Click on the x to shut down the Office Assistant
Basic Principles
Objectives:
By the end of this section you will be able to:
- Move around your spreadsheet using the scroll-bars, cursor keys, and keyboard shortcuts
- Select cells, columns and rows
- Enter text and numbers
- Correct mistakes
- Undo & Redo
- Copy numbers, text and formulas using autofill
- Erase cells
- Use formulas to get Excel to do your calculations
- Use percentages
- Use the sum function, and the autosum function to add up lists of numbers
- Use other functions, such as finding the average of a list of numbers using the Paste Function
- Insert and delete rows and columns
- Create, open, save and close workbooks
- Work with more than one workbook at a time
- Move and copy text and figures using cut, copy and paste and drag and drop.
- Use Absolute cell references
- Apply good spreadsheet design
- Re-name and move worksheets
Cursor Movement & Functions Keys
Movement Keys
Up one cell
Down one cell
Left one cell
Right one cell
CTRL-Last entry (to right) in current series
CTRL-First entry (to left) in current series.
HOMELeft-most cell (A column) of current Row
END & HOMEMove to bottom-right cell of current worksheet.
CTRL-HOMEMove to cell A1
CTRL-ENDMove to bottom-right cell of current worksheet
PgUpMoves cursor up one screen
PgDnMove cursor down one screen
Deletion & Cancellation Keys
DELBlanks a cell (with confirmation) / Deletes current character on entry line
BackspaceBlanks a cell (without confirmation) / Deletes previous character on entry line
ALT-BackspaceUndo
CTRL-ZUndo
ESCCancels last command selected
Selection Keys
SHIFT-Extend selection to the right
SHIFT-Extend selection to the left
SHIFT-Extend selection downwards
SHIFT-Extend selection upwards
Format Keys
CTRL-BBold
CTRL-IItalic
CTRL-UUnderline
CTRL-1Format Cells dialogue box
Function Keys
F1Help
SHIFT F1Context sensitive help
F2Edit Formula or Text in a cell
F3List Names
F4Put the dollar signs in (absolute cell references)
F5Go To
F6Next Pane
F9Calculate Now (if on manual re-calculation)
F10Activate menus (in case you haven't got a mouse)
F11New Chart
F12Save As..
CTRL-F6Next worksheet
Arithmetic Symbols
+Addition
-Subtraction
*Multiplication
/Division
^ Exponent
%Percentage
Windows Keys
ALT-F4Exit the program (i.e. Excel)
CTRL-XCut to the clipboard
CTRL-CCopy to the clipboard
CTRL-VPaste from the clipboard
Precedence of Calculation
Calculations are not simply done form left to right. Below is the order in which all calculations are performed.
Priority / Symbol / Explanation1 / () / Anything in brackets is done before anything outside the brackets is even considered
2 / ^ / Raises a number by an order of magnitude: raises it to the power of something else
e.g. X2
3 / * / / Multiply and divide are on the same level. Whichever is furthest left in a formula is therefore done first.
4 / + - / Plus and minus are on the same level as above.
The acronym for this is BODMAS
Brackets Order Divide Multiply Add Subtract
Selecting Cells
Selecting A Single Cell
Click on the cellThe cell will have a dark outline around it and it's contents will be displayed on the entry line
Selecting A Range Of Cells
Click and drag over the range to select
Selecting A Range Of Cells That Are Not Next To Each Other
Select the first cell(s)The cell will be highlighted
Hold down the CTRL key and click on the second cell
You can use this method to select lots of cells or ranges of cells that are not next to each other.
De-selecting An Area
Click elsewhere on the screen.
Adjusting A Selection
After making a selection you may want to adjust it (perhaps you selected too many or not enough cells)
Hold down the SHIFT key on the keyboard
Click to adjust the selection
Selecting Columns & Rows
Selecting A Row Or Column
Click on the ROW NUMBER or COLUMN LETTER (not the cell)
Selecting More Than One Row
Click on the first ROW NUMBER or COLUMN LETTER and drag to the last
Selecting Rows And Columns Not Next To Each Other
Click on the first ROW NUMBER or COLUMN LETTER
Hold Down the CTRL key on the keyboard
Click on the next ROW NUMBER or COLUMN LETTER
And So on...
Selecting The Whole Worksheet
Click on the square at the top left of the screenShown below
Basic Text & Number Entry
Anything that you type will appear in the 3rd line of the screen, the 'Formula Bar', as well as in the cell itself. (A green tick and a red cross also appear on the Entry Line to indicate that you have changed this entry.)
Press the Return key (the large key to the right of the keyboard, with 'Enter' or a bent arrow on it) when you have finished entering for that cell. (The tick and cross will then disappear.)
Cursor Movement
Use the four arrow keys on the keyboard () to move the cursor around the screen. Note that the cell address is shown in the Name Box on the Formula Bar
Entering Numbers
In Excel any number or formula is termed a 'value'. To enter it into a cell:
Select CellClick mouse on cell where number is to appear
125Type in number. It will appear in the cell and on the Entry Line next to the cell address. (A Tick and cross appear beside it.)
Press ReturnOr click on the tick
Entering Text
In Excel any text (combinations including any characters that are not numbers or calculation symbols) is termed a 'label'. These are normally used as headings or side-headings on tables.
Select CellSelect cell where label is to appear
Type TextType in text of label. It will appear in the cell and on the Entry Line. (The tick and cross appear beside it.)
Press ReturnOr click on the tick
Correcting Mistakes
Replacing The Contents Of A Cell
Select the cell
Type the new contents
Press Return Or click on the tick
Or press an arrow key on the keyboard
Correcting The Contents Of Cells
Double-click on the cellOr Press F2
Or Click in the Entry Line
Make the changes you want
Use arrow keys, DEL and Backspace to alter cell contents.
Press ReturnOr click on the tick
Undo
If you make a mistake and change a cell you didn’t mean to- or accidently delete a whole range of cells- Excel has a really useful way of undoing any damage.
Click on the Undo button Or CTRL + Z
Or Edit Menu : Undo
Repeating Your Last Action
You can repeat the last action you did (such as typing in something or formatting)
Click on the Redo button or press F4