MICROSOFT EXCEL
TRAINING MANUAL

For OFFICE 97 / 98

Level: Intermediate to Advanced
January 2001

Table of Contents

• A Quick Review
• Main Screen
• Basic Calculation Operators in Formulas
• Creating A Template
• Copying & Deleting Sheets
• Entering Data Automatically
• Adding Functions To Cells
• Writing Your Own Function
• Using The Paste Function Icon
• Add Values in Different Cells
• Adding Alternate Cells
• Design A Template With Functions For Museum Shops
• Inserting Rows And Columns
• Deleting Rows And Columns
• Set Print Area For Printing Spreadsheets
• Using The Print Preview Window
• Custom Footers And Headers
• Importing Spreadsheets Into Ms Word
• Importing Clipart And Images Into Excel
• Introduction To Object Linking And Embedding (OLE)
• File Management (Windows 95 / 98 / NT)
• Windows Shortcut Keys (Windows 95 / 98 / NT)

A Quick Review

• Help Menu,
• The Web- this web site
• User Manual- Getting Results with Microsoft Office 97

EXCEL WORKBOOK [Excel file]
SHEETS [sheet1, sheet2, … OR JAN, FEB, MAR,… OR 1997, 1998, 1999,…]

• File
• Edit
• View
• Insert
• Format
• Tools
• Data
• Window
• Help

• Standard toolbar
• Formatting toolbar

• AutoSum
• Paste Function
• Format Painter

• Merge and Center
• Currency Style
• Increase and Decrease Decimal
• Borders

• Name Box
• >> Selection reference (column and row)
• Formula Bar

ROW HEADINGS [1, 2, 3, …] & COLUMN HEADINGS [A, B, C, …]

HOW TO TURN THEM OFF AND ON:     ( click here for Options screen )

Tools > Options > cView tab > WINDOWS Options section >

CHECK or UNCHECK Row Or Column Headers

 

BASIC CALCULATION OPERATORS IN FORMULAS

Arithmetic Meaning	Arithmetic Operator	Example
Addition	+ (plus sign)	1+1
Subtraction	– (minus sign)	8-7
Multiplication	* (asterisk)	2*2
Division	/ (forward slash)	6/3
Percent	% (percent sign)	10%



 

The Good Stuff!

CREATING A TEMPLATE

PLANNING IS ESSENTIAL!

DEFINE OBJECTIVES

FIELDS: …hold similar types of data.

1. Columns: Date, Debit, Credit, Check #, Balance, Description (DESC)

Rows: each transaction

2 By MONTH [columns]

By SITE or DESC [rows]

1 TEMPLATE TO MANAGE YOUR BANK ACCOUNT





COPY THE TEMPLATE SHEET:

CTRL + drag to the right and when the PLUS sign appears, let go

OR

Edit menu > Move or Copy Sheet > check the option Create a copy and

select the location > OK

Rename the copy- call it JAN

Repeat the same for FEB and MAR

COPY A SHEET FROM ONE WORKBOOK TO ANOTHER:

1. Open both files in Excel
2. Click on Window Menu > Arrange > select Tiled format > OK
3. Hold down the control (Ctrl) key and drag and drop the source sheet into destination workbook

DELETING SHEETS:

SELECT THE SHEET > Edit menu > Delete Sheet > OK

ENTERING DATA AUTOMATICALLY:



Enter the beginning of the series > select the entries > drag the FILL HANDLE in the direction of the cells that need to be filled > let go!

 

NOTE: Excel looks for the trend in the series to fill in the rest.



FUNCTIONS, FORMULAE AND OTHER FUN STUFF

ENTER FUNCTIONS INTO SHADED CELLS

1. Creating functions for Excel

Start with the template:



2. WRITE YOUR OWN FUNCTION

Go to cell E5 > start with an EQUAL SIGN [=], get previous BALANCE [cell E4] minus the DEBIT [cell B5] add the CREDIT amount [cell C5] > press Enter



Once you complete the function in cell E5, you may copy and paste the function to cells below in the same column.

USING THE PASTE FUNCTION ICON

Select the cell to insert the function >

click on the Paste Function icon >



Select the function from

the Function category >

Try the Most Recently Used category first … >

[SUM function for example] > OK

 

 

 

 

 

 

 

 

Now you may move this window (below) to a side and select cells to add



1. Add cells inside the current sheet



OR

2. Add cells located in a different sheet (or a different workbook).



NOTE: The name of the sheet [JAN-WICK] is displayed in the Number 1 box and the

result of the Formula is displayed below (Formula result =100).

You may follow the same procedure [steps 1 and 2] to add more cells into Number 2 box

or

Say OK to complete the operation

NOTE: This allows the user to add any combination of cells in any workbook.

 



ADDING ALTERNATE CELLS

Locate the cell that needs to hold the final value (G1 in this example, shown below) ... type in the following:
=SUM(                    [do not press enter]
Now point at the first cell you need to add (A1)
Type a comma and point at the second cell (C1) you need to add
Continue the process until all cells are in the formula
Press ) followed by the ENTER key

The example shown below adds alternating cells A1, C1 and E1. The SUM is displayed in cell G1.



 

2 DESIGN A TEMPLATE WITH FUNCTIONS FOR MUSEUM SHOPS

Open file excel_97 and select sheet named 1997.

Use the procedures discussed above to SUM appropriate columns and rows.

INSERTING ROWS AND COLUMNS:

Select row or column heading and right-click > select Insert > and specify how you want excel to Insert rows or columns

 

OR

Insert menu > Rows or Columns

 

DELETING ROWS AND COLUMNS:

Select row or column heading and right-click > select Delete > and specify how you want excel to Delete rows or columns

 

OR

Insert menu > Rows or Columns



SET PRINT AREA FOR PRINTING SPREADSHEETS

Select the range of cells you need to print

File Menu > Print Area > select Set Print Area

 

 

 

Also turn on page break indicators by

Tools Menu > Options > View tab > CHECK Page breaks > OK





USING THE PRINT PREVIEW WINDOW

Use the Print Preview icon

or

File Menu > Print Preview >

Excel will display a preview of the selected

cells that can be printed (one sheet of paper at a time!)

Select Setup from the menu on top.



 

 

Examine the following tabs:

• Page
• Margins
• Header / Footer
• Sheet




 



CUSTOM FOOTERS AND HEADERS

Click on Custom Footers

• Set the Fonts
• Insert Page Numbers and Total Pages
• Insert the Current Date and Time
• Insert the File Name
• Insert Sheet Name

You may insert these into any of the sections of the footer shown by the three windows- Left, Center and Right sections



Right-click on a button and select What’s This? for more info

Use the procedures discussed above for the header.

 



IMPORTING SPREADSHEETS INTO MS WORD

1. Open both the EXCEL and WORD documents.
2. Got the EXCEL document and select by highlighting the range of cells you want in your WORD document.
3. Copy them by

Edit menu > select Copy

OR

CTRL + C

4. Got the WORD document and click on the location to insert and paste the EXCEL cells by

Edit menu > select Paste

OR

CTRL + V

The cells will be inserted as a WORD table. You may make any changes to contents inside WORD.

IMPORTING CLIPART AND IMAGES INTO EXCEL

Inserting Clipart

Insert menu > Picture > Clip Art > Microsoft Clip Gallery >

1. Look through the Categories
2. Select a Clip Art
3. Click on Insert



 

Inserting Images              How to insert the Museum Loge into a spreadsheet

Insert menu > Picture > From File > Insert Picture >



 

1. Set Files of type to All Pictures (*.emf; *.wmf… [Default setting] )
2. Use Look In: box to locate the image on your disk drive
3. Inspect the image if a preview is displayed
4. Select an image
5. Click on Insert

You may resize images by selecting them and dragging the arrows on the sides.

 

 

 



Introduction to Object Linking And Embedding     (OLE)

• Insert a WORD document into an Excel spreadsheet.
• The inserted portion is considered an OBJECT inside EXCEL.
• Changes made in the WORD document are automatically reflected in EXCEL.

Inserting Objects

Insert menu > Object >

Create New – to create a new object

1. Select the Object Type
2. Click OK

OR

Create from File – to use an existing object

(Example: import a word file currently saved on you disk)

1. Use Browse button to locate the file on your disk drive
2. Check Link to File
3. Click on OK



 

 



FILE MANAGEMENT



MY DOCUMENTS MUSEUM PERSONAL OTHER		ORGANIZED BY SITE [MUSEUM] EXPO PAM PAGE
ORGANIZED BY YEAR 1997 1998 1999		ORGANIZED BY DEPT SHOPS IT FINANCE

WINDOWS SHORTCUT KEYS



SAVE ..... CTRL + S

COPY ............. CTRL + C
PASTE ........... CTRL + V
CUT ............... CTRL + X
UNDERLINE ... CTRL + U
UNDO ............ CTRL + Z
PRINT ............ CTRL + P
BOLD ............. CTRL + B
ITALIC ............ CTRL + I

IF AN APPLICATION "SEEMS" TO BE FROZEN AND YOU CANNOT MOVE THE MOUSE OR CAN’T CLOSE APPLICATION:
Try this:
PRESS ALT AND F4 (F4 FUNCTION KEY) TO CLOSE APPLICATION



 

Site Design by Wick Gankanda

TRADEMARKS: Microsoft, Windows and Excel, and/or other Microsoft products referenced herein are either trademarks or registered trademarks of Microsoft Corporation