# Spreadsheets for Dummies

### Text-only Preview

**Excel Introduction**

**Academic Computing Services**

www.ku.edu/acs

**Abstract:**This document introduces users to basic Excel tasks, such as

creating, saving, and opening new Excel workbooks and

worksheets; selecting, copying, and moving data; constructing

formulas; formatting worksheets; and setting up worksheets for

printing. It is used in conjunction with the ACS

*Excel*

Introductionworkshop.

Introduction

**Contents**

Introduction ....................................................................................................................... 2

Objectives ......................................................................................................................... 2

Prerequisites ..................................................................................................................... 2

Related Training Available from ACS................................................................................ 2

Definitions ......................................................................................................................... 3

Creating and Opening Excel Workbooks .......................................................................... 3

Inside an Excel Worksheet................................................................................................ 5

Creating Formulas........................................................................................................... 10

Editing & Deleting Formulas............................................................................................ 12

Copying Formulas and Values ........................................................................................ 12

Changing the Workbook or Worksheet Appearance....................................................... 15

Getting Additional Help ................................................................................................... 22

*© 2002University of Kansas. All rights reserved*

**ACS Computer Training**

Excel Introduction

**Introduction**

Excel is a spreadsheet that allows users to create worksheets in that store information in

workbook files. The built-in functions allow users to also create and edit formulas; copy

and move data; format worksheets; and set up worksheets for printing.

**Objectives**

The goal of this workshop is to introduce participants to the introductory commands and

features of the Excel program. After today's workshop, participants will be able to:

• Create, open, and save Excel workbooks

• Select, copy, and move data

• Create formulas using relative and absolute references

• Format worksheets

• Use Page Setup to set up worksheets for printing

**Prerequisites**

It is assumed that the participants in this workshop have basic computing skills and know

how to use the

*Macintosh*or

*Windows*operating system to maintain files and

directories/subdirectories, open, close, and save files.

**Related Training Available from ACS**

All ACS workshops are free to KU students, staff, faculty, and approved affiliates

**.**The

general public is also welcome to most workshops, but some ACS workshops require a

registration fee for them.

To learn more about or register for workshops, receive automatic announcements of

upcoming workshops, and track workshops you’ve registered for and have attended, visit

the ACS Web site at www.ku.edu/acs/train. You can also check our online schedule at

www.ku.edu/acs/schedule for a list of class offerings and their availability. For further

workshop related questions, please email [email protected]

**EXCEL: CHARTING**

This three-hour, hands-on workshop introduces using Excel to create and edit charts,

modify chart options, format chart objects, as well as use trend lines, forecasts, and error

bars to present data graphically. In addition, students will learn to insert Excel charts in

other programs like PowerPoint or Word.

Academic Computing Services

- 2 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

**Definitions**

**Term**

**Definition**

Active Cell

A cell that is selected.

Cell

Cells form where rows and columns intersect. To refer to a cell, enter

the column letter followed by the row number. For example, C15 refers

to the cell at the intersection of column C and row 15.

Workbook

In Excel, a workbook is the file in which you work and store your data.

Because each workbook can contain many sheets, you can organize

various kinds of related information in a single file. By default, all new

workbooks contain three worksheets.

Worksheet

Worksheets consist of cells that are organized into alphabetical labeled

(Spreadsheet) columns and numerically labeled rows and are always located within

workbooks. They are used to list, organize, and calculate data.

Information can be linked from one worksheet to another in the same

workbook or in different workbooks.

**Creating and Opening Excel Workbooks**

**Opening Excel**When you first open the Excel program, a new workbook (Book1) will be created

automatically with three worksheets. Each worksheet will be labeled Sheet1, Sheet2, and

Sheet3. Worksheet tabs can be found at the bottom of the workbook window. To move

from sheet to sheet, you can click the sheet tabs.

Each worksheet contains 65,536 rows and 256 columns. The columns are labeled

alphabetically and run along the top of the worksheet. The rows are labeled numerically

and run along the left side of the worksheet. Columns are labeled A through Z, AA

through AZ, BA through BZ, etc. Rows are numbered from 1 through 65,536. A cell

forms where these rows and columns intersect. The scroll bars on the right and along the

bottom of the worksheet can be used to scroll to any location of the worksheet.

Academic Computing Services

- 3 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

**With Excel Open**If you already have Excel open and want to create a new plain workbook, you can click

on the

**New**button. If you want to create a new workbook from a template, you can

click on

**File**!

**New**to see Excel’s built in templates or custom templates.

Academic Computing Services

- 4 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

**Opening An Existing Workbook**You can always opening existing workbook files as well. To do this:

1. Click

on

**File**!

**Open**or click on the yellow

**Open**folder on the Standard toolbar.

2. Change the folder (if needed) to find the file you want.

3. Select the file name and double click on it or click on the

**Open**button.

**Inside an Excel Worksheet**

**Pointer Appearances**In an Excel spreadsheet, your pointer appearance will change dependent upon where you

are in the worksheet. It is important to understand the distinction between these mouse

pointer types:

**Name**

**Appearance**

**Description**

**Cross**(

**Puffy Plus**):

Used to select a cell or a range of cells.

**I-Beam**:

When you see an I-Beam, click one time for a blinking

insertion point that will allow you to type in text.

**Mouse Pointer**:

The pointer is this shape when using toolbars, moving

and resizing windows, and when moving or copying

information from cells.

Academic Computing Services

- 5 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

**Selecting Parts of a Worksheet**There are lots of reasons to select, or click, in different areas of your workbook. We

select cells to enter, change, and delete information. We select cells for formulas to

reference. We also select cells to format their contents. Any time we select a cell, we

call it an

**active cell**.

**Using the Mouse**

Here are many ways to make a cell active by clicking or clicking and dragging with your

mouse:

**Item Selected**

**How to Select**

**Cell**

Click on the cell.

**Cell Range**

Use the Cross (Puffy Plus) to click and

drag across the group of cells.

**Row**

Click on the row number.

**Column**

Click on the column letter.

**Multiple Rows**

Click and drag down the row numbers.

**Multiple Columns**

Click and drag across the column letters.

**Entire Worksheet**

Click the gray cell between column A and

row 1.

**Non-Contiguous Cells**Select the first cell or range of cells and

then hold down the

*Ctrl*key while

selecting the remaining cells.

**Using the Keyboard**

The keyboard is also a great way to move around inside of Excel workbooks and

worksheets. Here are just a few common keystrokes or keyboard shortcuts:

**Key**

**Direction**

**Enter**

Moves the active cell down, row to row.

**Tab**

Moves the active cell to the right, column to column.

**Shift/Enter**

Moves the active cell up, row to row.

**Shift/Tab**

Moves the active cell to the left, column to column.

Academic Computing Services

- 6 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

**Ctrl/Home**

Moves the active cell to

**A1**.

**Ctrl/End**

Moves the active cell the last cell that contains data.

**Ctrl/Page Down**

Moves from one worksheet to another.

**& Ctrl/Page Up**

**F2 key (function**Opens the active cell for you to edit the data in it.

**key)**

**Alt/Enter**

Enters a hard return inside of the active cell. This

will automatically wrap your text and increase the

size of the cell.

**Cell Contents**In Excel, you can enter four types of values: text, numbers, dates and times, and

formulas. Each cell can hold up to 32,767 characters.

**Text**

In Excel, text is any combination of numbers, spaces, and nonnumeric characters. All

cells that Excel considers to be text will be left aligned. In the following example, these

entries would be treated as text:

1025A63N; 123XYZ; 10-72; 123 456.

**Numbers**

Numbers include the numeric characters 0-9 and the following special characters:

, + - ( ) / E e $ % .

If a number is wider than the cell, ######## is displayed. To display the cell contents,

resize the column. Excel stores numbers up to 15 digits of accuracy. The largest positive

number is 9.99999999999999E307 and the smallest positive number is 1E-307. By

default, negative numbers are preceded by a minus sign. However, they can be formatted

to be enclosed in parenthesis or displayed in red. Entering a dollar sign ($) before a

number or a percent (%) symbol after changes the display of the number.

**Dates and Times**

Excel treats dates and times as numbers. They can be displayed in several built-in

formats. The way that a time or date is displayed on a worksheet depends on the number

format applied to the cell. When a date or time is entered into a cell, Excel automatically

changes the cells format from general to a built in date or time format. By default, dates

and times are right aligned in a cell. If Excel cannot recognize the date or time format, the

date or time is entered as text, which is left aligned in the cell. Date and time can be

mixed into one cell, however slashes and hyphens cannot be mixed in one entry. To type

a date and time in the same cell, separate the date and time with a space.

Academic Computing Services

- 7 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

To type a time based on the 12-hour clock, type a space followed by AM or PM (or A or

P) after the time. Otherwise, Excel bases the time on the 24-hour clock. For example, if

you type 3:00 instead of 3:00 PM, the time is stored as 3:00 AM.

Times and dates can be added, subtracted, and used in other calculations. To use a date or

time in a formula, enter the date or time as text enclosed in quotation marks. For

example, the following formula would display a difference of 68:

="5/12/94"-"3/5/94"

Some examples of date and time formats:

**Date/Time Entry**

**Format**

**6/9/01**

m/d/y

**9-June-01**

dd-mmmm-yy

**June-01**

mmmm-yy

**9-June**

dd-mmmm

**7:00 AM**

h:mm AM/PM

**7:00:00 AM**

h:mm:ss AM/PM

**18:00**

h:mm

**6/9/01 7:00**

m/dd/yy h:mm

**Formulas**

A formula calculates a new value from existing values. An Excel formula can contain a

combination of constant values, cell references (cell addresses), range names, functions,

and/or operators. Formulas always begin with an equal sign (=). Here are a few

examples:

*Constant*

*Values*

=(456+57)*32

**Cell**

**References**

=D3/F13

**Range**

**Names**

=D3*Tax

Academic Computing Services

- 8 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

*Functions*

Excel contains many predefined, or built-in, formulas, which are known as functions.

Functions can be used to perform simple or complex calculations. Some of the most

frequently used function are the SUM, AVERAGE, PMT, DLOOKUP, and IF functions.

Here is an example of the sum function adding cell addresses.

=SUM(D3:D7)

**Operators**

Operators specify the type of calculation that you want to perform on the

elements of a formula. Microsoft Excel includes four different types of

calculation operators: arithmetic, comparison, text, and reference.

*Arithmetic*

**Operator**

**Meaning**

*****

Multiplication

**/**

Division

**+**

Addition

**-**

Subtraction

**%**

Percent

**^**

Caret

*Comparison*

**Operator**

**Meaning**

**=**

Equals

**>**

Greater than

**<**

Less than

**>=**

Greater than or equal to

**<=**

Less than or equal to

**<>**

Not equal to

Academic Computing Services

- 9 -

© 2002The University of Kansas

**ACS Computer Training**

Excel Introduction

*Text*

**Operator**

**Meaning**

**&**

Ampersand—Concatenates, or combines, two values

to produce one continuous text value.

*Reference*

**Operator**

**Meaning**

**:**

Colon—A range operator, which produces one

reference to all the cells between two references that

includes the two references. An example would be

D3:D7.

**,**

Comma—A union operator, which combines multiple

references into one reference. An example would be

SUM(D3:D7,F15,B4).

**Creating Formulas**

There are a couple of ways in which you can create formulas. You can type the formulas

directly into the cell or formula bar; you can use Excel’s built in

**Formula Palette;**type a

formula directly into a cell; or use

**AutoSum**to add a group or range of numbers.

**Paste Function and the Formula Palette**The formula palette (which is under the

**Paste Function**button

on the

**Standard**

**toolbar**) can help you enter worksheet functions. As you enter a function into the

formula, the Formula Palette displays the name of the function, each of its arguments, a

description of the function and each argument, the current result of the function, and the

current result of the entire formula. To display the Formula Palette:

1. Click inside of the cell where you want your results of the formula to be.

2. Click on the

**Paste Function**button

.

Academic Computing Services

- 10 -

© 2002The University of Kansas

# Document Outline

- Introduction
- Objectives
- Prerequisites
- Related Training Available from ACS
- Definitions
- Creating and Opening Excel Workbooks
- Opening Excel
- With Excel Open
- Opening An Existing Workbook

- Inside an Excel Worksheet
- Pointer Appearances
- Selecting Parts of a Worksheet
- Using the Mouse
- Using the Keyboard

- Cell Contents
- Text
- Numbers
- Dates and Times
- Formulas
- Constant Values
- Cell References
- Range Names

- Functions
- Operators
- Arithmetic
- Comparison
- Text
- Reference

- Operators

- Constant Values

- Creating Formulas
- Paste Function and the Formula Palette
- Typing the Formula Directly in a Cell
- Using AutoSum

- Editing & Deleting Formulas
- Copying Formulas and Values
- Autofill
- Relative References
- Absolute References

- Copying Values

- Autofill
- Changing the Workbook or Worksheet Appearance
- Workbook Maintenance
- Naming Worksheets
- Adding Worksheets
- Rearranging Worksheets
- Moving a sheet within a workbook
- Copying (or moving) a sheet to an existing workbook
- Deleting Worksheets

- Worksheet Maintenance
- Adding or Deleting Cells
- Adding or Deleting Rows and Columns

- Formatting Worksheets
- Formatting Toolbar and Dialog Box
- AutoFormat
- Clearing Formats

- Workbook Maintenance
- Getting Additional Help