CIS111 Basic PC Literacy

Getting Started with Excel 2007

Understanding Spreadsheet Software

• Microsoft Excel is an electronic spreadsheet program.

• An spreadsheet program allows you to perform numeric calculations.

• The spreadsheet (Excel) file is called a worksheet .

• There are individual worksheets stored within a workbook .

• What can Excel do? It can: - Perform basic and advanced calculations.

- Recalculate data quickly and easily.

Perform “

what-if

” scenarios.

- Organize data in ascending or descending order.

- Analyze data and create data summaries.

- Create charts and other graphical images.

Open the file

EX A-1.xlsx

Save the file to your disk as Tour Guide Payroll Calculator.xlsx

Excel 2007 Window

• The intersection of a

row

and a

column

is called a cell.

• Each cell has its own unique location called the cell address.

• The active cell address is displayed in the name box .

• A cell address is identified by its coordinates, such as A1, B25, etc.

• When you click in a cell, the cell address will appear.

The formula bar allows you to enter or edit data in the worksheet. This displays what is located in the cell you have selected. It may be text, a number, a formula, or a function.

Sheet tabs let you switch from one sheet to another sheet in a workbook. You can give each sheet a name. You can add sheets, delete sheets, copy and move sheets, as well as copy sheets to other workbooks.

Entering Data in Excel

• • • Labels – text and numerical information not used in calculations.

• Labels help you identify data in worksheet rows and columns.

• You should enter all the labels first before entering other content Values – actual numbers used in calculations.

Formulas – equations in a worksheet that use both values and cell addresses.

=C5*(D5-E5) (This formula uses only cell addresses.) • =B25*.2 (This formula uses both cell addresses and values.) Functions – predefined formulas, such as: =sum (A4:A10) (This function would the

total

of the cells.) =average (A10:R10) (This function would the

average

of the cells.)

Understanding Formulas

• When you work with formulas that contain more than one operator, the order of precedence is very important because it affects the final value.

• Excel performs the calculations in a particular sequence based on the following rules: – Operations within the parentheses – Exponents are calculated next.

– Then multiplication – Finally, addition and and division subtraction are calculated first.

– from left to right.

– from left to right.

Excel arithmetic operators

Understanding Formulas

Formula appears in formula bar Result of formula

Click in cell E5

.

The formula calculates the regular pay by multiplying the value in B5 (hours) times the value in D5 (hourly rate).

=B5*D5

Understanding Formulas

Formula appears in formula bar Result of formula

Click in cell F5

.

The formula calculates the overtime pay by multiplying the value in C5 (overtime hours) times the value of (2 times the value in D5, which is hourly rate).

=C5*(2*D5)

This section of the formula calculates the overtime pay (F5), which is twice the regular pay. That’s why regular pay in D5 is multiplied by 2.

Entering Labels

Click in cell

A15

. Key the text,

Totals

.

Using the AUTOSUM button

Function Cells included in function 1. Click in cell

B15

.

2. Click the

AutoSum

button on the Home tab > Editing group.

3. A moving dotted border surrounds the section that may possibly be the cells to add together.

4. The function =SUM(B5:B14) appears in cell 15 and in the formula bar at the top.

5. This is correct, so press [enter].

6. Your answer should be

378

.

Editing Cell Entries

• You can change, or edit , the contents of an

active

cell.

• To edit the contents of the

active

cell: • Double-click the cell, or • Click in the formula bar, or • Just start typing in the cell itself, or • Use the F2 function key.

Edit Mode Indicator • Excel switches to

Edit

mode when you are making cell entries 1. Click in cell C13 . Change the value to

6.

2. Click in cell C18 . Type the text

Average Gross Pay

3. Click in cell A5 behind the “

P

”; press DELETE to remove the “

i

” so that it now reads “

Peter

” 4. Click in cell B6 . Press [

F2

]. Press

BACKSPACE

; type

8;

press ENTER.

5. Click in cell A9 . Press [

F2

]. Type

Maez, Javier

.

6. Double-click cell C12 . press

DELETE

, type

4,

click the on the formula bar.

Common Pointers in Excel

Using the FILL button

• • • Click in cell B15 .

Drag the mouse pointer to cell G15 Click the FILL button in the Editing group, then click RIGHT in the Fill menu.

ANSWERS:

Using the FILL Handle

• • • Click UNDO to remove what you copied on the previous slide.

Click in cell B15.

Note that there is a black square in the bottom right hand corner. This is the FILL handle.

• Click and drag the Fill handle over through cell G15.

This is called relative copying . In other words, the direction in which you copy is

relative

as to what will be changed.

- If you click and drag to the right , the

column letter

will change.

- If you click and drag down , the

row number

will change.

Entering and Editing a Simple Formula

Formulas start with the equal sign

=

.

1.Click in cell G5 2.Type = 3.Click cell E 5 4.Type + 5.Click cell F5 .

To copy the formula down through G14, click the fill handle and drag through G15.

ANSWERS