excel 2000 - basic training.ppsx

7/29/2019 Excel 2000 - Basic Training.ppsx

1/131

2/25/2013 1
http://www.jjsea.com/jjsea/pack.nsf/lookupByUniqueKey/JPLI-4YTAYZ?OpenDocument


2/131

2/25/2013 2

Excel Basics

1: The Excel Window

2: Entering and Editing Data

3: Working with Multiple Worksheets

4: Creating Formulas

5: Complex Formulas

6: Formatting Numbers

7: Using Functions


3/131

2/25/2013 3

Changing Worksheet Layout

8: Inserting and Deleting Rows and Columns

9: Adjusting Column Width and Row Height

10: Inserting and Deleting Cells

11: Moving and Copying Cell Contents

12: Aligning Text13: Fonts, Color, and Borders


4/131

2/25/2013 4

Charting and Printing

14: Creating a Chart

15: Moving, Resizing, and Deleting Charts

16: Editing Charts

17: Using the Page Setup Dialog Box

18: Print Preview19: Print


5/131

1: The Excel Windowntroduction

Microsoft Excel 2000 is a spreadsheet application in the Microsoft

ffice Suite. A spreadsheet is an accounting program for the computer.

preadsheets are primarily used to work with numbers much likeMicrosoft Word is used to work with text. Spreadsheets calculate and

nalyze information by using mathematical formulas.

When you open Excel, many items you see are standard in most

Windows 95 or 98 programs. A few things are specific to Excel 2000.

2/25/2013 5


6/131

2/25/2013 6


7/131

oolbarsoolbars can be customized and offer a series of commonly used Excel

atures

olumn Headings

ach Excel spreadsheet has 256 columns

ow Headingsach spreadsheet has 65,536 rows

ame Box

hows the address and name of the current selection

ormula Bar

isplays the current entry (as you are typing or editing) or the contents

f the current cell

ell

n intersection of a column and row. Each cell has a cell address. In the

bove picture, the cell address of the selected cell is A1. The heavy

order around the selected cell is called the cell pointer.

avigation Buttonsllow you to move to another worksheet in an Excel workbook easily

heet Tabs

llow you to move around the workbook easily

72/25/2013


8/131

2/25/2013 8

The Excel Workbook

When you open Microsoft Excel, a workbook automatically

shows in the workspace.

Important Terms

A workbook is made up of three worksheets. The worksheets are labeled Sheet1, Sheet2, and Sheet3.

Each Excel worksheet is made up of columns and rows.

In order to access a worksheet, click on the tab that says

Sheet#.

To Change the Number of Worksheets:

Choose Tools Options. In the Options dialog box, click the General tab.

Click on the spin box controls (up and down arrows) to change

the number of worksheets in a new workbook.


9/131

The Cell

The Excel worksheet is made up of columns and rows. When these

columns and rows intersect, they form little boxes called cells.

Important Terms

Each cell has a unique cell address composed of a cell's

column and row. The active cell is the cell that receives the data or command

you give it.

A darkened border, called the cellpointer, identifies it.

2/25/2013 9


10/131

2/25/2013 10

Moving the Cell Pointer

To activate any cell, click the cell.

To move the pointer one cell to the left, right, up, or down, use

the keyboard arrow keys.

To see other areas of the worksheet, use the scroll bars. To

scroll large distances, hold the Shift key while dragging thescroll bar.

Some frequently used keyboard commands:

PgDn: Down one screen

PgUp: Up one screen

Home: To column A in the current rowCtrl + Home: To cell A1


11/131

2: Entering and Editing DataIntroduction

By the end of this lesson, learners should be able to:

Enter and Delete data

Edit data Select multiple cells

ou can enter three types of data in a worksheet: text, numbers, and

rmulas. Text is any entry that is not a number or formula. Numbers are

alues used when making calculations. Formulas are calculations.

o Enter Data into a Cell: Click the cell.

Begin typing the data.

An insertion point appears, and the text you are entering

appears in the active cell and in the formula bar.

112/25/2013


12/131

2/25/2013 12

The formula bar buttons (Cancel button and Enter button)

appear.

Clicking the Enter button ends the entry and turns off the

formula bar buttons.


13/131

o Delete Data: Click the cell.

AND

Click the Cancel button to delete an entry and turn off th

rmula buttons.

OR

Cancel an entry by pressing the Escape key.OR

Right-click and choose Clear Contents from the shortcut

menu.

OR

Select data and press Delete or Enter key.

Excels AutoComplete feature keeps track of text entered in a

olumn and can complete other entries in the same column. For

xample, if you typed GCFLearnFree.org in cell A1 and enter "G" in A2

xcel automatically fills the cell with GCFLearnFree.org. If GCF Globa

earning is the text you want to enter, press Enter or Tab to move to the

ext cell. If its not what you want, just keep typing until you overwrite

e AutoComplete feature.

2/25/2013 13


14/131

2/25/2013 14

Editing Data

ou may find you need to edit or change data you enter into a cell. You

an do this two ways.

Quick and Easy Method:

Click the cell and type the new entry; the old entry isreplaced.

If the original entry is long and only requires a minor adjustment

(in spelling, for example), edit the entry.

To Edit:

Click on the cell and edit the entry in the formula bar.OR

Double-click on the cell. This opens the cell for direct

editing.

Press Enter or click the Enter button on the Formula bar

to complete the entry.


15/131

Selecting Multiple CellsIn Excel, the cell that is currently selected is the active cell. A

group of cells is called a range.

To Select a Range of Cells:

Move to the first cell in the range.

The mouse pointer becomes a large cross.

Hold down the left mouse button and drag to the last cell yo

want to select.

Release the mouse button. The cells you selected are shaded.

To Select All Cells in a Column or Row:

Click the Column or Row heading.

To Select Multiple Columns or Rows:

Click one heading and drag to select the others.152/25/2013


16/131

2/25/2013 16


17/131

2/25/2013 17

the cells and columns you want to select are not directly next to one

nother, select one of the ranges you want to select, and hold down the

ontrol key while selecting other ranges. For example, you may want to

elect column A and column C, but not column B.

To Select the Entire Worksheet: Click the gray rectangle in the upper left corner to select

entire worksheet .


18/131

Introduction

By the end of this lesson, learners should be able to: Name Worksheets

Insert and Delete Worksheets

Group and Ungroup Worksheets

Copy and Move Worksheets

Naming WorksheetsWhen working with an Excel workbook, it's not always easy to

remember where your data is located. Give each worksheet a

descriptive name so you can find it within the workbook.

To Name a Worksheet:

Double-click the sheet tab to select it. Type a new name for the worksheet.

Press Enter.

Now, the worksheet has a descriptive name.

3: Working with Multiple

Worksheets

182/25/2013


19/131

2/25/2013 19

nserting and Deleting Worksheets

o Insert a New Worksheet:Choose Insert Worksheet.


20/131

2/25/2013 20

o Delete one or more Worksheets:Click on the sheet and choose Delete Sheet from the Edit menu.

or-Right Click on the Sheet Tab and select Delete from the shortcut

menu.


21/131

2/25/2013 21

Grouping Worksheetsworkbook is a multi-page Excel document. It is made up of multiple

orksheets.

When you select more than one worksheet, you are grouping your

orksheets. Duplicate information is entered into all sheets in a group.To Select One Worksheet, simply click a sheet tab.

To Select More than One Worksheet, hold the Control key down and

click on the sheet tab of each worksheet.

To Select All Worksheets in a Workbook, right-click on any sheet tab

nd choose Select All Sheets from the shortcut menu.


22/131

2/25/2013 22

Grouping and Ungrouping WorksheetsWhen worksheets are grouped, the information you enter into one

orksheet is entered into all sheets in the group. Grouping worksheets

aves you time; it eliminates all that cutting and pasting!

emember to ungroup worksheets after you are finished entering,

moving, or copying common data.o Ungroup Worksheets:

Right-click on any of the selected sheet tabs.

Choose Ungroup Sheets from the shortcut menu.


23/131

2/25/2013 23

Copying and Moving Worksheetsemember, when you copy a sheet, you are doing just that--making

copy. When you move a sheet, you are moving it to a new location.

o move a worksheet within the same workbook, drag the sheet tab

the new location(a small black triangle appears) and drop it. To

opy it, hold the Control key down while dragging the worksheet to its

ew location.


24/131

2/25/2013 24

To Move or Copy between Workbooks:Select the worksheet you want to move/copy.

Choose Edit Move or Copy.

n the Move or Copy dialog box, use the drop down boxes to select the

name of the workbook you are copying the sheet to, and where you

want the sheet positioned in the workbook.

Check Create a copy to copy it.

Click OK.


25/131

2/25/2013 25

ntroductiony the end of this lesson, learners should be able to:

Create a simple formula

Creating Formulasn school, you may have had to memorize certain formulas in order to

ork math problems. Microsoft Excel uses formulas to perform

alculations within a spreadsheet. In this lesson, you will learn how to

se Excels formula features.

4: Creating Formulas

Addition +Subtraction -Multiplication *Division /Exponentiation (to the power of)

Precedence (do this operation first)

(put in parentheses)

Equal to =Not equal to < >Greater than >Less than


26/131

2/25/2013 26

Creating a Simple Formulaet's start with a simple formula that contains one mathematical

peration. All formulas begin with the equal sign (=), whether the

ormula consists of adding, subtracting, multiplying, or dividing. Here's

n example of a simple formula: =B2-B3.


27/131

2/25/2013 27

o Create a Simple Formula using the Point and Click Method:

Enter the numbers you want to calculate.

Click the cell where you want the result to appear.

Type =.

Click on the first cell to be included in the formula.

Type a mathematical operator (Ex: the addition symbol +). The operato

displays in the cell and Formula bar.Click on the next cell in the formula.

Repeat steps 4 and 5 until the entire formula is entered.

Very Important: Press Enter or click the Enter button on the Formula

bar. This step ends the formula.

you simply move to another cell, Excel will include that cell in the

ormula.

lick the = sign button on the formula bar rather than the = sign on the

eyboard to open the Formula Palette. The Formula Palette lets you see

e progress of a formula as you enter it.


28/131

2/25/2013 28

5: Complex Formulasntroduction

y the end of this lesson, learners should be able to:

Create complex formulas

Fill a formula to another cellCopy and paste a formula to another cell

Revise a formula

Create an absolute reference

Complex Formulas

imple formulas have one mathematical operation. Complex formulasvolve more than one mathematical operation.

he order of mathematical operations is very important. If you enter a

ormula that contains several operations, like adding, subtracting and

viding, Excel knows to work those operations in a specific order. The

rder of operations is:

Operations enclosed in parenthesis

Exponential calculations (to the power of)

Multiplication and division, whichever comes first

Addition and subtraction, whichever comes first


29/131

2/25/2013 29

elative to the order, you will also calculate from left to right. Let's lookan example.

*(6-4) =?

the answer 8 or 4? If you calculated in the order in which the number

ppear, ignoring the parentheses, 2*6-4, you'd get the wrong answer, 8.ou must follow the order of operations to get the correct answer.

o Calculate the Correct Answer,

Calculate the operation in parenthesis 6-4=2.

Multiply 2*2=4.

The answer is 4.

When using formulas, the results change each time the numbers are

dited.

Remember: In Excel, never do math "in your head" and type the

nswer in a cell where you would expect to have a formula calculatee answer.


30/131

2/25/2013 30

Creating Complex FormulasWhen you create a complex formula, Excel automatically follows the

rder of operations. So, if you want a certain portion of the formula to b

alculated first, put it in parentheses. For example, =(B8+B9)*A10.

Enter the numbers you want to calculate.

Click the cell where you want the formula result to appear.

Type =.

Click on the first cell to be included in the formula. The reference

displays where you

want your result.

Type a mathematical operator (Ex: the addition symbol +). The operato

displays in the

cell and Formula bar.Click on the second cell in the formula. The reference displays where

you want your result.

Type the next operator in the formula. The operator displays in the cell

and Formula bar.

Click on the third cell to be included in the formula. The reference

displays where you want your result.Repeat steps 7 and 8 until the formula is complete, adding parentheses

where necessary.

Very Important: Press Enter or click the Enter button on the

Formula bar.This step ends the formula.


31/131

2/25/2013 31

Filling Formulas

Once you've created a working formula, don't waste time typing it

over and over again. Simply fill it to the other cells. In order to do

this, you will need to recognize the mouse pointer's various shapes.

When you move the mouse pointer around the Excel window, it

changes shape and function.

If you're going to copy a formula to a surrounding cell, you can use

the Fill method. The square box in the lower right corner of the cell

pointer is the fill handle.


32/131

2/25/2013 32

o Use the Fill Handle:

Click on the cell that contains the formula.

Position the mouse pointer on the lower right corner of the beginning o

he cell pointer (fill handle). The mouse pointer becomes a black

rosshair.

Click and hold the left mouse button and drag to select the next cells to

e filled in. The cells are now selected.Release the mouse button. The formula is copied.

In Excel, the references change as the formula is copied. (Remember

ou use references to identify cells containing the numbers you want to

alculate in a formula.)

You can also use copy and paste to copy a formula to other cells.

lick next to learn more about the copy and paste method.


33/131

2/25/2013 33

Copying and Pasting FormulasWhile you can use both the fill method and the copy and paste method t

opy a formula to surrounding cells, if you want to copy a formula to

ells beyond the cells adjacent to the formula, use copy and paste.

o Use Copy and Paste:

Select the cell with the formula you want to copy.Click the Copy button. Marching "ants" appear around the copied cells

Click the area where you want to place the duplicated formula.

Press the Enter key. The formula is copied to the new location.

Cut, Copy, and Paste are discussed in detail in the Office 2000 lesson


34/131

2/25/2013 34

Revising Formulasyou entered an incorrect formula or added new data and need to

hange the formula to reflect the new entries, you can revise your

ormula using the Range Finder or your keyboard.

What is the Range Finder?

Double-click the cell that contains the formula you want to edit.Excel colors each cell address or range address in the formula a differe

color. (Below, see B4.)

Corresponding colored borders appear around the cell or range. Each

olored border is called a Range Finder. (Below, see B2 and B3.) This

makes it easy to see whether a formula refers to the correct cells.


35/131

2/25/2013 35

o Revise a Formula using the Range Finder:elect the cell or range you want to revise, and drag its Range Finder

he mouse pointer should be an arrow) to the cell or range with which

ou want to replace it. (Below, see Figure 1.)

ress the Enter key or click the Enter button to complete the formula.

The new result now displays in the cell that contains the formula.

Below, see Figure 2.)


36/131

2/25/2013 36

o Revise using the Keyboard:

Double-click the cell that contains the formula you want to revise. The

Range Finder displays.

Select the cell or range you want to revise.

Select another cell or range with which you want to replace it.

Press the Enter key or click the Enter button. The result now displays i

he cell that contains the formula.

ou can also select the cell that contains the formula, select the part of

e formula you want to revise, and type a new cell address. This is a

omewhat inaccurate method, as it's easy to mistype.

Absolute Referencesormally, when you copy a formula that uses references, Excel

utomatically adjusts the reference in the pasted formula to refer to

fferent cells relative to the position of the formula.


37/131

2/25/2013 37

ometimes, when you copy a formula, you don't want one of the cell

ferences to change. Creating an Absolute reference, a reference to a

ell that does not change when copying a formula, solves this problem.

n absolute reference always refers to the same cell or range. It is

esignated in the formula by the dollar sign ($).For this exercise, delete

e original formulas and start again.

o Delete a Formula:

Select the cells in which the original formulas exist. The cells are now

selected.

Press the Delete key. The information in these cells is deleted.

o Create an Absolute Reference:

Enter the numbers you want to calculate. Then, create a simple

ormula (=A1+A2).

Click on the cell in which you want to place the formula answer.

To create an absolute reference, add a dollar sign ($) in front of the

olumn reference and the row reference (=$A$1+A2).

Press the Enter key or click the Enter button. The answer displays in

the cell.

Copy and Paste the formula to another adjacent cell. The formula now

ncludes an absolute reference (=$A$1+C2).


38/131

2/25/2013 38


39/131

2/25/2013 39

6: Formatting NumbersIntroduction

By the end of this lesson, learners should be able to:

Format numbers

Formatting Numbers

By default, values are formatted as general numbers. Meaning, Excel

doesn't display zeroes that don't affect the actual numeric value of the

number. For example, Excel will display 250 but not $ 250.00 unless

you format the number.

Values can be formatted as currency,percentages, fractions, dates, and many other formats. Remember,

when you format a number, you're not changing the numeric value.

You can format selected cells using the Formatting toolbar, the

Format dialog box, or the shortcut menu.

To Format Cells Using the Formatting toolbar:Select the cell or range you want to format.

On the Formatting toolbar, click one of the buttons explained in the

table below. Your number is now formatted.

Click anywhere outside the cell to turn the formatting off.


40/131

2/25/2013 40

Currency Displays and aligns dollar signs, comma

separators, and decimal points. Ex: 89.5 as $89.50

Comma Same as currency, but without dollar signs. Ex:

120.5 as 120,50

Percentage Displays numbers as percentages. Ex: 0.82 as82%

Increase Decimal Ex: .12 as .120Decrease Decimal Ex: 0.5670 as 0.567

Remember, formatting only affects the look of a cell, not the cell's

ontents. To view the contents of a cell, click on the cell and look at the

ormula bar.

Excel has several number formats to choose from in the Format Cell

alog box. Click next to learn more.


41/131

2/25/2013 41

To Format using the Format Cells Dialog Box:Select the cells you want to format.

Choose Format Cells from the menu bar. The Format Cells dialog

box opens.(Alternately, right-click on the cells to open the shortcut

menu and choose Format Cells.)

The Format Cells dialog box has several categories to choose from,

each one on a separate tab: Number, Alignment, Font, Border, Patternsand Protection.

Click the Number tab if it is not already displayed.

Click on the different categories to preview results.

Set other options if you like (font, borders, etc).

Click OK.


42/131

2/25/2013 42

If you hover your mouse pointer over the dollar sign button on theormatting toolbar, the Tooltip reads "currency style." When you apply

is button's format, Excel applies the accountant format (dashes for

eroes and dollar signs lined up, right next to the numbers).


43/131

2/25/2013 43

7: Using Functionsntroductiony the end of this lesson, learners should be able to:

Use functions

Using Functionsmportant Term:A function is a pre-defined formula that helps perform common

mathematical functions.

ach function has a specific order, called syntax. Syntax must be

ollowed for the function to work correctly.

yntax Order:

All functions begin with the = sign.

After the = sign is the function name.

One or more arguments enclosed in parentheses. If there is more than

one argument, they are separated by commas.

n example of a function with one argument:


44/131

2/25/2013 44

n example of a function with more than one argument:

xcel has hundreds of different functions (pre-defined formulas) to

ssist you with your calculations. As you know, building individual

ormulas can be difficult and time consuming. Using Excel's functions

an save you a lot of time and headaches.


45/131

2/25/2013 45

Excel's Different Functionshere are many different functions in Excel. For our purposes in

xcel 2000, we will discuss some common functions.

tatistical, including:

UM - summation adds a range of cells together.

VERAGE - average calculates the average of a range of cells.OUNT - counts the number of chosen data in a range of cells.

MAX - identifies the largest number in a range of cells.

MIN - identifies the smallest number in a range of cells.

mounts

inancial, including:

nterest Ratesoan Payments

epreciation Amounts

ate and Time:

ay of Week

earime

ate

ou don't have to memorize the functions, but have an idea of what

ey will do for you.


46/131

2/25/2013 46

Accessing Functionscommon function, the Sum function is located on the Standard toolba

o use the Sum Function:

f necessary, enter the numbers you want to calculate.

Select the numbers you want to total.

On the Standard toolbar, click the Sum button.

Your total appears in the cell below the range you selected.

o Access Other Common Functions:

Click the cell where you want the result of the function to appear.

Type =

On the Formula Bar, click the Edit Formula button.

Choose a function from the Function Box drop-down list.The result of the function appears


47/131

2/25/2013 47

If you didn't see the function you were looking for, there are other

nctions available through the Paste Function dialog box, explained

elow.

o Access Other Available Functions:

Click on the cell in which you want to place the end result.

Type =.

On the Formula Bar, in the Function Box drop down list, choose

More Functions.

The Paste Function dialog box opens. (Choosing Insert Functions o

clicking the Paste Function button opens the Paste Function dialog

box as well.)

Notice the Function Categories available, including statistical, financiaand date and time.

When a function category is selected, the corresponding function name

display. Additional information about the function name displays in the

ower part of the dialog box.

Click OK.


48/131

2/25/2013 48


49/131

2/25/2013 49

8: Inserting and Deleting Rows

and Columnsntroductiony the end of this lesson, learners should be able to:

nsert rows and columns

Delete rows and columns

nserting Columnsn Excel, you can insert a column anywhere you need it. Excel moves

e existing columns to make room for the new one.

o Insert a Column:

Click anywhere in the column where you want to insert a new column.

Choose Insert Columns from the menu bar.

A new column is inserted to the left of the existing column.

R

Click anywhere in the column where you want to insert a new column.

Right-click and choose Insert from the shortcut menu. The Insert dialobox opens.

Click the Entire Column radio button in the Insert dialog box.

A new column is inserted to the left of the existing column.


50/131

2/25/2013 50

o Insert Multiple Columns:Select more than one column.

Choose a method (menu or shortcut menu) to insert a new column.

The number of columns you originally selected are inserted into the

preadsheet.

bserve how the existing column C is moved to column D to make room

or a new column (and new content).


51/131

2/25/2013 51


52/131

2/25/2013 52

nserting Rowsou can insert a row in a spreadsheet anywhere you need it. Excel

moves the existing rows down to make room for the new one.

o Insert a Row:

Click anywhere in the row below where you want to insert the new row

Choose Insert Rows from the menu bar.

A new row is inserted above the cell(s) you originally selected.R

Click in the row below where you want to insert the new row. A cell in

the row will be selected.

Right-click and choose Insert from the shortcut menu. The Insert dialo

ox opens.

Choose the Entire Row radio button. Click OK.A new row is inserted above the cell(s) you originally selected.

To quickly insert a row or column, select the row or column

eading. Right-click to open the shortcut menu and add a new row.


53/131

2/25/2013 53

elow, observe how the existing row 16 is moved to row 18 after a newow is inserted.


54/131

2/25/2013 54

Deleting Columns and RowsDeleting columns and rows is much like inserting columns and rows.

To Delete Columns and Rows:

Select one or more columns or rows.

To clear the content but leave the emptied row in place, press the

Delete key on your keyboard.

ORTo delete the contents and remove the row or column, choose

Edit Delete from the menu bar.


55/131

2/25/2013 55

9: Adjusting Column Width and

Row Heightntroductiony the end of this lesson, learners should be able to:

Adjust column width and row height

Adjusting Column Widthsy default, Excel's columns are 8.43 characters wide, but each

dividual column can be enlarged to 240 characters wide.

the data you are entering in your worksheet is wider or narrower

an the default column width, you can adjust the width so it is widenough to contain the data.

You can adjust column width manually or use AutoFit.

djust Column Width Two Ways

Manually:

Place your mouse pointer to the right side of the column header.The mouse pointer changes to the adjustment tool

double-headed arrow).

Drag the edge of the column header to the desired width and release

the button.


56/131

2/25/2013 56

utoFit:

Place your mouse pointer to the right side of the column header.

The mouse pointer changes to the adjustment tool

double-headed arrow).

Double-click the column header border.

Excel "AutoFits" the column, making the entire column slightly larger

than the largest entry.

To access AutoFit from the menu bar, choose Format Column

utoFit Selection.


57/131

2/25/2013 57

Adjusting Row Heightdjusting row height is much like adjusting column width.

o Adjust Row Height:

Place your mouse pointer to the lower edge of the row heading you

want to adjust.

The mouse pointer changes to the adjustment tool

(double-headed arrow).Double-click to adjust the row height to "AutoFit" the font size.

Drag to manually increase or decrease size.


58/131

2/25/2013 58

10: Inserting and Deleting Cellsntroductiony the end of this lesson, learners should be able to:

nsert cells

Delete cells

nserting CellsWhen working in an Excel worksheet, you may need to insert or delete

ells without inserting or deleting entire rows or columns.

o Insert Cells:

Select the range where new cells should be inserted.

Right-click and choose Insert.The Insert dialog box opens and presents the following four choices:

Shift cells right: cells in the same row are shifted right.

Shift cells down: selected cells and all cells below them in the

same column are shifted.

Entire row: inserts an entire row of cells.

Entire column: inserts an entire column.Choose an option and click OK.

Your result displays in the spreadsheet.

Remember, you can also use the Insert/Delete dialog box to

sert/delete columns and rows.


59/131

2/25/2013 59

Deleting Cellso Delete Cells:

Select one or more cells.

To delete cell contents but leave the cell in place, press the Delete key.

R

Right-click and choose Delete.

The Delete dialog box opens and presents the following choices:

Shift cells left: cells in the same row are shifted right.

Shift cells up: selected cells and all cells below them in the samecolumn are shifted.

Entire row: deletes an entire row of cells.

Entire column: deletes an entire column.

Choose an option and click OK.

Your result displays in your spreadsheet.


60/131

2/25/2013 60

11: Moving and Copying CellContentsIntroductionBy the end of this lesson, learners should be able to:

Move and copy cell contents using:Cut, copy, paste

Drag and drop

Moving and Copying Cell Contents

f you're not happy with the location of your data, you don't have to

delete it and start entering data all over again. Instead, you can move it

around in the worksheet.

Basic moving and copying techniques can be reviewed in our Office

2000 lessons.

Copying and Pasting CellsThe Office 2000 Clipboard is very helpful if you want to transfernformation within a worksheet. In fact, the Clipboard allows you to

tore up to 12 items and paste them one at a time or all of them at once

To turn on the Clipboard toolbar, choose View Toolbars

Clipboard from the menu bar.


61/131

2/25/2013 61

o Cut, Copy and Paste:Select a cell or cells to be duplicated.

Click on the Copy button. "Marching Ants" appear around the selected

cells.

f you want to move the information from one cell to another, click the

Cut button instead.

Click on the cell where you want to place the duplicated information.The cell will be highlighted. If you are copying contents into more tha

one cell, click the first cell where you want to place the duplicated

information.

Press the Enter key. Your information is copied to the new location.


62/131

2/25/2013 62

Select a cell or cells to be moved or duplicated and right-click to ope

e shortcut menu.


63/131

2/25/2013 63

ifferences in Excel's Cut, Copy, and Paste Functions

While basic cut, copy, and paste functionality works in Excel, you

hould know that Excel handles it a bit differently:

You must paste data you've cut or copied immediately. If you don't, the

cut or copy operation is cancelled.

When you cut a selection, you can paste it once. You can't paste the

selection multiple times. Instead, you must copy and paste the same

information over and over again.

When you cut a cell, it's copied to the Clipboard. That information

remains on the Clipboard until you paste it by pressing Enter or the

Paste button.

You can't paste cells on top of existing data. If you do, the existing data

s overwritten.

Using Drag and Droprag and drop is useful in Excel when you can actually see where you

ant to move your data to a new location.

o Use Drag and Drop:

Select some cells you'd like to move to a new location.Position the mouse pointer near one of the outside edges of the

selected cell (however, avoid the fill handle in the lower right during

this process).

The mouse pointer becomes a small white arrow.


64/131

2/25/2013 64

Keeping the mouse pointer on the outer edge of the selected cell, press

nd hold the mouse button and drag the cells to a new location.

Basic drag and drop techniques can be reviewed in Office 2000essons.


65/131

2/25/2013 65

12: Aligning Textntroductiony the end of this lesson, learners should be able to:

Align text and numbers in cells

Use the Format Cells dialog box

ligning TextYouve probably noticed by now that Excel, like Word, left-aligns text

(labels) by default.

However, Excel, by default, right-aligns numbers (values). This makes

data easier to read. Below, see examples of text (labels) aligned right,

eft, and centered.


66/131

2/25/2013 66

Use the buttons of the Formatting toolbar to align text and numbers:

Basic text alignment can be reviewed in Office 2000 and Word 2000

ssons.

nother Alignment Option: Merge and Center

n Excel, you have another alignment option available to you: merge

nd center. When you merge a cell, you are selecting more than one cellnd creating one large cell. You can center the contents of a cell across

he new merged cell.

Merge and center is helpful when creating worksheet titles.


67/131

2/25/2013 67

o Create a Title using Merge and Center:Enter the title of the worksheet in the top left cell.

Select the first row of the worksheet (including the top left cell

containing the title of the worksheet). How many cells you select

depends on how wide your label is.

Click the Merge and Center button.

Excel only centers the text in the top left cell of the selection, so if th

tle is in more than one row, merge and center each title row separately.

The Format Cells Dialog Boxou have more alignment options available to you in the Format Cells

alog box.

o Change Alignment using the Format Cells Dialog Box:Select the range of cells you want to format.

Choose Format Cells from the menu bar or right-click and choose

Format Cells from the shortcut menu. The Format Cells dialog box

opens.

Click the Alignment tab.

Choose Horizontal, Vertical, Orientation, Merge, and wrap options.Click OK.


68/131

2/25/2013 68

Click next for a detailed explanation of the Format Cells dialog box

lignment tab


69/131

2/25/2013 69

The Format Cells Dialog Box - Understanding theAlignment Tab

n the Alignment tabs Text Alignment section, there are Horizontal

nd Vertical alignment options, as shown below:


70/131

2/25/2013 70

The Horizontal section features a drop down box with left, center,

nd right alignment options.

also features:

eneral

he default alignment.

illFills" the cell with the current contents by repeating the contents for th

width of the cell.

ustify

Wraps the text in the cell and adjusts the spacing within each line so tha

ll lines are as wide as the cell.

enter Across Selection

ontents of the cell furthest to the left are centered across the selection

f cells. Similar to merge and center, except the cells are not merged.

The Vertical sections default is bottom. Top and Center are used to

osition the data close to the top or center of the cell.


71/131

2/25/2013 71

he Format Cells Dialog Box - Understanding the Alignment Tabn the Orientation section of the Format Cells dialog box, you can orien

xt vertically or rotate text.

To rotate text to another orientation, use the Degrees spin box or click

nd drag the Text indicator.

To orient text vertically, click the box containing the vertical word Text


72/131

2/25/2013 72

The Format Cells Dialog Box - Understanding the

Alignment Tabou can use Wrap Text, Shrink to Fit, and Merge cells to display

xt (labels) in cells.


73/131

2/25/2013 73

o Display a Vertical Title Across Several Cells:Select the title and several cells below it.

Choose Format Cells from the menu bar.

Click the Merge Cells check box to merge the cells.

Shrink to Fit shrinks the size of the font so it fits in the selected cells.

Wrap Text wraps the contents of a cell in case its too large for the cell

boundaries.


74/131

2/25/2013 74

13: Fonts, Color, and Bordersntroductiony the end of this lesson, learners should be able to:

Apply fonts

Apply a font color

Apply borders

onts, Color, and Borders

onts, colors, and borders, as discussed in Word 2000, can enhance you

ocument and increase understanding by highlighting important

formation in an Excel worksheet.

mportant Terms:Font is typeface.

Font color is applied to text.

border is a line around a cell or group of cells.

ormatting With Fontshe default font in a spreadsheet is Arial 10 points, but the typefacend size can be changed easily.


75/131

2/25/2013 75

electing a Font Typeface:Select some cells to change the typeface.

Click on the down arrow to the right of the Font Name list box. A

drop-down list of available fonts appears.

Click on the font of your choice. The selection list closes and the new

font is applied to the selected cells.

Remember, font choices vary depending on the software installed onour computer.


76/131

2/25/2013 76

electing a Font Size:Select some cells to change the font size.

Click on the down arrow to the right of the font size list box. A drop

own list of available font sizes appears.

Click on the font size of your choice. The selection list closes and the

new font size is applied to the selected cells.

electing a Font Styleold, italics, and underline are all examples of font style attributes.


77/131

2/25/2013 77

o Select a Font Style:

Select some cells.

Click on any of the following options on the Formatting toolbar.

Bold button (Ctrl + B).

Italics button (Ctrl + I).Underline button (Ctrl + U).

The attribute (bold, italics, or underline) is applied to the font.

The Bold,Italics, and Underline buttons on the Formatting toolbar ar

ke toggle switches. Click once to turn it on, click again to turn it off.

dding Borders and Colormphasize important data by adding borders, lines, or color to

our worksheet.

o Add Borders:

Select the cells you want to put a border around.

Click on the down arrow next to the Borders button. The Border

drop-down appears.

Choose a borderline style from the Border drop-down menu. The

selected cells display the border.


78/131

2/25/2013 78

o Add Color:

Select the cells you want to color.

Click the down arrow next to the Font Color button. A Font Color

rop-down menu displays.

Choose a font color from the Font Color drop-down menu. The selecteells display the color.


79/131

2/25/2013 79

14: Creating a Chartntroductiony the end of this lesson, learners should be able to:

dentify the parts of a chart

dentify different types of charts

Create an Embedded ChartCreate a Chart Sheet

hart Basics

ave you ever read something you didn't fully understand but when you

aw a chart or graph, the concept became clear and understandable?

Microsoft Excel 2000 has a chart feature you can use to help explain

ata. Many People find that a picture helps when they are trying tonderstand the significant of the list of numbers.

ource Data

he range of cells that make up a chart.

itle

he title of the chart.

egend

hink of this as the key to the chart. This shows what each of the colors

n the chart represents.


80/131

2/25/2013 80


81/131

2/25/2013 81

Types of Chartsxcel offers a variety of ways to chart your data.

Think of a chart as a tool that helps people visualize data. Charts

eliver information more quickly and easily than a column of numbers

a worksheet, especially to people sitting in the audience for a

resentation. Choosing the right kind of chart for your data can really

elp get your point across.

ome of the most common charts:

ie Chart

hows the relationship of parts to a whole. If the pie is the sum of the

ource data, each slice of the pie represents an individual number.


82/131

2/25/2013 82

olumn or Bar Chartseful to follow trends or to compare numbers. Each column represents

particular value. A bar chart is a horizontal version of a column chart.

Line Chart

Most useful for tracking trends over time. They make it very easy tostudy the rise or decline of a particular item. A variation of the line

chart is the area chart where the area under the line is shaded a

particular color.


83/131

2/25/2013 83

The Chart Toolbarxcel 2000 provides a number of ways to work with charts. The quicke

ay to create and edit your charts is to use the Chart Toolbar.

o show the Chart Toolbar:Choose View Toolbars Chart.


84/131

2/25/2013 84

arts of the Chart Toolbar:


85/131

2/25/2013 85

hart Objects List Boxllows you to select the individual parts of your chart.

ormat Button

llows you to format the current selection.

hart Type

se this to select the type of chart.egend

how or hide the chart legend.

ata Table

how or hide the actual data used to create the chart.

y Rowisplays the Y-axis using the row labels.

y Column

isplays the X-Axis using the column labels.

ngle Text

se these to change the angle of the x and y axis labels.


86/131

2/25/2013 86

Creating an Embedded Chartmbedded charts are charts that reside on the same worksheet as the

ource data.

o embed a chart in a worksheet:

Choose View Toolbars Chart.

Select the range of cells that you want to chart. Your source data

should include at least three categories or numbers.On the chart toolbar, click the chart type pull down and select the

chart that you would like to use.


87/131

2/25/2013 87

To add a title to your chart, open the chart options dialog box:

hart Options.

Select the titles tab and enter your title in the Chart Title text box.


88/131

2/25/2013 88

Keep in mind that different charts will work best with different data.

or example, a pie chart can only display one data series at a time.

Creating a Chart Sheetlthough Excel creates an embedded chart by default, in some situation

ou may want to place a chart on a separate sheet. A separate sheet withchart on it is called a Chart Sheet . Chart sheets can make your charts

and out, particularly when you are working with very complicated

preadsheets.

o move an embedded chart to a Chart Sheet:

Select the chart that you would like to move.

Choose Chart Location.


89/131

2/25/2013 89

n the Chart Location dialog box, select the As a new sheet radio buttonClick OK.

our chart should now display on a separate Chart Sheet in your

Workbook:


90/131

2/25/2013 90

You can also use the Chart Location dialog box to rename the Chart

heet


91/131

2/25/2013 91

The Chart Wizardxcel 2000 also includes a Chart Wizard which will take you through

e steps for creating a chart.

o use the Chart Wizard:

Select the range of data that you would like to chart.

Choose Insert Chart.

Or, press the chart button on the standard toolbar.


92/131

2/25/2013 92

Follow the directions in the Chart Wizard.


93/131

2/25/2013 93

15: Moving, Resizing, andDeleting Chartsntroductiony the end of this lesson, learners should be able to:

Delete a chart

Move a chart

Resize a chart

Deleting a Chartyou make a mistake inserting a chart, or if you would like to remove

from your document, use the delete key on your keyboard.eleting a chart:

Click on the white space anywhere on your chart to show the selection

box.

Press the delete key on your keyboard.


94/131

2/25/2013 94

If you are having difficulty deleting a chart, click the worksheet

utside of the chart. Now you should be able to select and delete the

hart.

Moving a Charthe easiest way to move a chart is to drag it around the worksheet.

o move a chart:

Click on the white space anywhere on your chart.

Use the cursor to drag your chart anywhere on the worksheet.


95/131

2/25/2013 95

Resizing a Chartometimes a chart just will not fit on a worksheet. Excel makes it easy

make a chart smaller (or larger).

esizing a Chart:

Click on the white space anywhere on your chart.

Use the grab handles to resize the chart.


96/131

2/25/2013 96


97/131

2/25/2013 97

16: Editing Chartsntroductiony the end of this lesson, learners should be able to:

Make changes to the data presented in a chart

Format the following parts of a chart:

TitleLegend

Category Axis and Value Axis

Data Series Color

Changing the Data in a ChartWhen you add a chart to your worksheet, Excel creates a link between

e chart and your source data. That way, any changes that you make toe original source data are instantly reflected in your chart.

ou can also add rows or columns of data to an existing chart by

electing Add Data on the Chart Menu.

o add data to an existing chart:

Select the chart that you wish to edit.

Choose Chart Add Data.


98/131

2/25/2013 98

Enter the range that you would like to add in the Add Data dialog box,r select the range of cells that you want to add to your chart.

Click OK .


99/131

2/25/2013 99

Your chart should update to display the new data:


100/131

2/25/2013 100

Editing the Chart Titlee sure to select a meaningful title for your chart.

o change the title of a chart:

Click on the Chart Title.

Use the cursor to select the text.

Make any changes that you want to the text.Click anywhere outside of the title to apply your changes.


101/131

2/25/2013 101

ormatting the Chart Titlen addition to selecting the right title for your for your chart, you can us

e Format Chart Title dialog box to apply several kinds of formatting,

cluding:

itle color and pattern.

ont, size and style.

itle alignment.

o format the chart title:

Select the Chart Title.

Click the Format Button on the Chart Toolbar

or double click the Chart Title).

Use the different tabs of the Format Chart Title dialog box to apply

ormatting to your title.

Click OK.


102/131

2/25/2013 102


103/131

2/25/2013 103

ormatting the Chart Legendhe chart legend displays very useful information. Be sure to format the

gend so that it is as clear as possible.


104/131

2/25/2013 104

o Format the Chart Legend:

f the legend is not showing press the show/hide legend button on

e Chart Toolbar.

Click on the Chart Legend.

Click the Format Button on the Chart Toolbar (or double click the

hart legend).

Use the different tabs of the Format Chart Legend dialog box to applyormatting to the Legend.

Click OK.


105/131

2/25/2013 105

ormatting the Value and Category Axisou have several options when formatting the Value and Category Axisyour chart. In addition to changing font, color, and style, you can also

djust the numbers on the Scale of the chart.

o format a Chart Axis:

Select the Axis that you wish to edit.

Click the Format Button on the Chart Toolbar

(or double click the chart axis).

Use the different tabs of the Format Axis dialog box to apply formattin

Click OK.


106/131

2/25/2013 106

ou can also use the angle axis buttons on the chart toolbar to

ange the angle of the value and category axis.


107/131

2/25/2013 107

Changing the Color of a Data Seriesou can apply formatting to each data series of a chart. Although you

an change different aspects of each data series, you will probably find

at you change the color of bars, columns, pie slices and areas most

ften.

o change the color of a data series:

Select the data series that you wish to edit.

Click the Format Button on the Chart Toolbar (or double click the

ata series).

Use the Format Data Series dialog box to pick a new color.


108/131

2/25/2013 108

Click OK

our chart should display the new colors:


109/131

2/25/2013 109

17: Using Page Setup Dialog Boxntroductiony the end of this lesson, learners should be able to:

Use the Page Setup dialog box to set:

page orientation

paper size

print quality

margins

sheet settings

headers and footers

The Page Setup Dialog Box

efore you print your spreadsheet, you will want to define pagerientation, paper size, print quality, margins, sheet settings, and

eaders and footers.

he Page Setup dialog box includes four tabs to help you do this:

Page

Margins

Header/FooterSheet


110/131

2/25/2013 110


111/131

2/25/2013 111

Page Orientation and Paper Sizeyour spreadsheet is wider than it is tall, change the page orientation o

aper size.

he default paper size is 8.5 X 11 inches, or portrait. Changing to

ndscape orientation prints the longer side of the paper at the top

1 X 8.5 inches), allowing room for extra columns.

o Change Page Orientation:

Choose File Page Setup from the menu bar.

Click on the Page tab.

Choose an Orientation (Portrait or Landscape).

HEN

Click on the down arrow to the right of the Paper Size list box. A list ofvailable paper size options appears.

Click on the paper size.

Click OK.

You can shrink spreadsheet data so it fits on a specified number of

ages when you print. Click on the Page tab, click the Fit to: option

utton and enter the desired number of pages wide and pages tall.

Print Quality is measured in dpi, or dots per inch. High dpi gives yo

higher resolution and a better print quality. However, it takes longer to

rint all those dots!


112/131

2/25/2013 112

etting Up Marginsy default, the top and bottom margins are set at 1 inch. The left and

ght margins are set at .75 inch. You can change these margins using th

age Setup dialog box's Margins tab.

Margins display as dotted lines. Use the spin box controls to determine

he size of each margin.


113/131

2/25/2013 113

o Change the Margins in the Page Setup Dialog Box:


Select the Margins tab.

Use the spin box controls for each margin that you want to change.

A sample displays.

Click OK.

Headers and Footersheaderis a title that appears at the top of each page of a document.

ooters are printed at the bottom of the page. Headers and footers displ

n the 1/2-inch between the header and footer margin and the regularmargin. The default setting is no header and no footer.

you'd like to add a header and/or footer to your spreadsheet, you can

o so using the Page Setup dialog box's Header and Footer tab.


114/131

2/25/2013 114

o Select and Create Headers and Footers:


n the Page Setup dialog box, select the Header/Footer tab.

EXT

Click the Header drop down list and select:A pre-designed header OR

Click the Custom Header button to create your own header. If you

choose to create your own header, the Header dialog box appears

Make your entry and click OK to return to the Page Setup dialog

box.


115/131

2/25/2013 115

EXTClick the Footer drop down list and select:

A pre-designed footer OR

Click the Custom Footer button to create your own footer. If you

choose to create your own footer, the Footer dialog box appears.

Make your entry and click OK to return to the Page Setup dialog

box.


116/131

2/25/2013 116

Placeholders in the Header/Footer Dialog Boxn the Header and Footer dialog boxes, there are a series of Placeholder

uttons you can ignore or use to format text, insert page numbers, date,

me, filename, or tab name.

o insert a page number, date, time, filename, or tab name, position the

sertion point in the appropriate text box, then choose the appropriate

utton.

When the file is printed, Excel will replace placeholders with those youelected. Click OK to return to the Page Setup dialog box.


117/131

2/25/2013 117

Select text and click the Font button to format header and footer

text. Click OK to return to the Page Setup dialog box.

Button Button Name Function of Placeholder

Page Number Current page number

Total Pages Total pages printed

Date Date worksheet printed

Time Time worksheet printed

File Name Name of workbook

Tab Name of worksheet


118/131

2/25/2013 118

heet Settingshe Page Setup dialog box's Sheet tab allows you to consider other

ptions for your worksheet, such as whether to print gridlines or row an

olumn headings.


119/131

2/25/2013 119

rint Area

y default, Excel prints from the A1 to the last occupied cell in a

orksheet. You can specify a different range of cells to print.

rint Titles

rints column and row labels on each page of the printout. Specify

ese rows or columns in the Rows to Repeat at Top and Columns to

epeat at Left textboxes.rint or Hide Gridlines

etermines whether gridlines are printed. However, turning off gridline

oes not affect their appearance in Normal View.

raft Quality

hoose draft quality to print the worksheet without gridlines or graphic

lack and White

you used colors in your worksheet but don't want to waste the ink in

our color printer, use black and white.

ow and Column Headings

lick this option to include row numbers and columns letters in your

rinted document.age Order

etermines the order in which worksheets are printed.

To turn off gridlines, you can also choose Tools Options from the

menu bar. Click the View tab and uncheck the gridlines option.


120/131

2/25/2013 120

18: Print Previewntroductiony the end of this lesson, learners should be able to:

Use print preview

Create page breaks

Print Previewrint Preview allows you to see how your file looks before you print,

reventing a waste of paper and ink.

o Print Preview:

Click the Print Preview button on the Standard toolbar.

n Print Preview mode, the document is sized so the entire page is

visible on the screen.

n the Preview window, the text in your document appears very small.

on't strain your eyes trying to read the text in the Preview window.

xcel assumes you already proofread your spreadsheet, so data is not

ditable from this screen. Here, you're simply checking the overall

ormatting and layout.

or a Better View of Data while in Print Preview:

Click the Zoom button.

The text becomes larger and easier to read onscreen.

Click the Zoom button again. The text becomes smaller.

Click Close to return the document to Normal View.


121/131

2/25/2013 121


122/131

2/25/2013 122

he Print Preview Toolbarn the previous page, you learned about the Zoom function in Print

review mode. Let's "zoom" in and take a look at the Print Preview

olbar:

ext/Previous

vailable when using a workbook with multiple sheets

oom

iew details in your spreadsheet

rint

rint your document

etup

pens the Page Setup dialog box with Sheet tab displayed

Margins

Margins appear as dotted lines. Point to any of the margin lines, and the

ointer changes to an double-arrowed adjustment tool. Drag the marginou wish to change to its new location.

age Break Preview

hows you what and in what order pages will be printed

lose

xit or Close Print Preview


123/131

2/25/2013 123


124/131

2/25/2013 124

Define Page Breaks Using Print Previewhe gray space between two pages is called a page break. Most of theme you will not have to worry about page breaks since Excel

utomatically inserts soft page breaks when necessary. However, hard

age breaks allow you to fine tune the appearance of your document.

hey are particularly useful when you want to begin a new section of

our document.

When you turn on Page Break Preview (available on the

rint Preview toolbar), the Office Assistant appears, telling you that

ou can adjust page breaks by clicking and dragging the edge of the

age break with your mouse. Page Break Preview shows you what and

what order pages are printed.


125/131

2/25/2013 125

Areas that are printed are white; areas that won't be printed are gray

Page Break Preview

o Turn On Page Break Preview:

Choose View Page Break Preview from the menu bar.

n Print Preview mode, the document is sized so the entire page isvisible on the screen.

R

Click the Page Break Preview button while in Print Preview mode.

o Adjust a Page Break:

n Page Break Preview, adjust the breaks by dragging the page break if

worksheet prints on multiple pages.

o Add a Hard Page Break:

n Page Break Preview, select the first row or column you want to

appear in the page after the hard break.

Right-click and choose Insert Page Break from the shortcut menu.

o Remove a Hard Page Break:

Right-click in the row below the horizontal page break.

Choose Remove Page Break from the shortcut menu.


126/131

2/25/2013 126

o return to Normal View, choose View Normal from the menu bar.

emember, Page Break Preview is only useful if you are dealing with

multiple page worksheets.


127/131

2/25/2013 127

19: Printntroductiony the end of this lesson, learners should be able to:

Print in Excel 2000

rinting in Excel

nce you've made all the necessary adjustments to your Excelpreadsheet, you're ready to print. Printing in Excel is much like printin

other Office 2000 applications, so we're reviewing printing basics and

mphasizing any differences.

o Print your Document:

Click on the Print button on the Standard toolbar.The document prints with default options.

R

hoose File Print from the menu bar. The Print dialog box opens.

Choose print options in the Print dialog box.

Click OK. The document prints.

Don't print your Excel document without checking spelling first!

xcel includes two tools to help correct spelling errors: AutoCorrect and

pelling. For a review of these features, see Word 2000's AutoCorrect

nd Spelling and Grammar lessons.


128/131

2/25/2013 128

The Print Dialog Box in ExcelMany options are available in the Print dialog box including:


129/131

2/25/2013 129

rinter namehe name of the default printer appears in this box. If you are connected

more than one printer, you can choose the name of the printer to use

or this print job. Click on the drop down arrow and make a printer

election.

roperties button

iew or change the printer's settings.

rint Range

sing the Print Range option boxes, choose which pages of your

ocument to print.

umber of Copies

lick on the up or down arrows in the Number of Copies list box tohoose the number of copies you want to print.

Click on any of the desired options. The options are activated.

Click OK to print your document and close the Print dialog box.


130/131

2/25/2013 130


131/131

Q & A
http://www.gcflearnfree.org/excel2000/quizhttp://www.jjsea.com/jjsea/pack.nsf/lookupByUniqueKey/JPLI-4YTAYZ?OpenDocument

excel 2000 - basic training.ppsx

Documents