EXCEL

Lesson #1: Introduction to Spreadsheets You will learn the basics about

spreadsheets, cell addresses, rows/columns, and data entry.

What is a Spreadsheet?

Spreadsheet software is designed to process numbers just as word processor software processes words.

Spreadsheet software has a number of built-in features for all of the common operations with numbers and more complex ones. It allows users to perform calculations on

values entered into the program. These values can then be analyzed,

graphed, and printed.

What is a Spreadsheet?

Discussion: In what ways can a spreadsheet be used at

home or in school?

What is a Spreadsheet?

Example: Imagine that you are in charge of a school

store, and you have to prepare monthly reports on its sales.

Your original data might looks as follows:

What is a Spreadsheet?

You might decide to use your spreadsheet to enter formulas so that the software will automatically do the following: calculate the totals for you identify the category with the highest sales and the

category with the lowest sales calculate the average sales for each month

You could then use this information to create a pie chart that shows the percentage of sales for each category.

Spreadsheet Basics

A spreadsheet is used to organize data into cells.

The spreadsheet workbook is the file in which you create and work on your data.

It consists of numerous worksheets, each of which consists of a grid of vertical columns and horizontal rows.

Spreadsheet Basics

A spreadsheet is organized into rows (across) and columns (up and down). The rows are numbered (1 2 3 …) down the

left side. The columns are named by letters (A B C …)

along the top of the working area. The rows and columns divide the screen

into rectangles called cells. A cell is identified by a cell address.

The cell address is created using the cell’s column letter and row number: Example, where column C crosses row 2, the

cell address is C2. If you select several cells, that group of

cells is called a cell range. The active cell is identified by a black

boarder. It is the cell in which you can enter information.

Lesson #1: Introduction to Spreadsheets--Assignment1. Open a new spreadsheet, and enter the

following data at the locations indicated:

2. Now enter your name in E1.

Lesson #2: Formatting Data and Using Simple Formulas In this activity, you will be introduced to

the basics of cell alignment and formatting cells for numbers.

You will also learn how to make a spreadsheet add, subtract, multiply, and divide numbers.

Lesson #2: Formatting Data and Using Simple Formulas Information entered into a spreadsheet

cell is one of three types: labels, values, or formulas.

Lesson #2: Formatting Data and Using Simple Formulas Labels:

refer to the text information (all the words) used to describe the data in the spreadsheet.

Numbers on the screen are relatively useless without some information to help users understand what the numbers mean.

Labels can include the title of your worksheet, column headings, row identifiers, or words to describe the functions you have programmed the spreadsheet to perform (e.g., Total).

Lesson #2: Formatting Data and Using Simple Formulas Values:

Any numerical data that will be used in calculations on a worksheet is called a value.

After the data is entered, you can format the cells as a type of number, date, or time.

Lesson #2: Formatting Data and Using Simple Formulas Formulas:

The real power of a spreadsheet comes from the formulas you enter into cells.

A formula is a written set of instructions telling the program to perform calculations on the values you have entered.

Lesson #2: Formatting Data and Using Simple Formulas All formulas begin with an indicator such as an = sign. The order of the elements or parts of a formula is known as the

syntax. Formulas follow the order of operations learned in your

mathematics classes—brackets, exponents, division and multiplication (in the order they occur), and addition and subtraction (in the order they occur). BEDMAS

In addition, spreadsheet formulas can include numbers, cell addresses, or cell ranges (e.g., B5..E9).

Benefit of using formulas: Saves time when you change values.

When you enter a new value, the formulas will automatically recalculate the answers.

Lesson #2: Formatting Data and Using Simple Formulas--Assignment Tips:

AutoFill Let's say you've typed Monday into cell A3, and you want

to fill the cells below with the other days of the week. To do this, you place the pointer over the fill handle in the

bottom right-hand corner of cell A3. The pointer changes to a crosshair. Then, holding down the mouse button, you drag the

pointer downwards over the next eleven cells below cell B4.

As you drag the mouse down the cells, each cell's content – in this case the days of the week– is displayed as a ScreenTip.

When you release the mouse button, the rest of the days of the week are automatically filled in, and a Smart Tag appears next to the Fill handle.

You can do this for months, numbers, formulas anything that you want to fill in.

Lesson #2: Formatting Data and Using Simple Formulas--Assignment Tips Continued:

Copying formulas and functions (explained in Lesson #4) (Slide: 27)

Merge and Centre Fill (yellow shading) Right click on cell (copy, paste, insert/delete

row/column etc.) Bold Justification (left-letters, right-numbers) Adjust column/row width (double click the line

between the column or row) Format Cell

Can apply different formats to your cells, such as $

Lesson #2: Formatting Data and Using Simple Formulas--Assignment Please do Assignment

Assignment #2A If you have time, please do Assignment #2B

Lesson #3: Simple Spreadsheet Functions In this activity, you will learn to create

simple mathematical formulas to make your spreadsheet powerful and efficient.

These will be used to expand upon your school dance spreadsheet. There will be a focus on these functions: sum, average, maximum, minimum, and count

Lesson #3: Simple Spreadsheet Functions Spreadsheets can be created to

automatically find: the sum of a set of numbers, the average of a set of numbers, the largest of a set of numbers, the smallest of a set of numbers, and the number of numbers.

Lesson #3: Simple Spreadsheet Functions

Here is an example of a spreadsheet that uses these functions:

There are functions in the following cell addresses:

Lesson #3: Simple Spreadsheet Functions

The nice thing about using such functions is that when data changes, the function will automatically update the result. For example, in the

above spreadsheet, if “Gas” (cell B5) is changed from $100.00 to $50, the result in B11 (“Total Expenses”) will change from $790.00 to $740.00 without any input from the user.

Lesson #3: Simple Spreadsheet Functions- Assignment Complete the Lesson #3A: Simple

Spreadsheet Functions—Assignment Do the:

Min Max Count Average

For TICKET column

You can use the AutoSum Profit = TOTAL MONEY – TOTAL EXPENSE

Lesson #4: Fill and Replicating in Spreadsheets In this activity, you will learn how to

copy a function from one column or row to another, allowing for more efficiency in how you use them.

As well, you will learn about how to use “relative” and “absolute” values in your functions.

Lesson #4: Fill and Replicating in Spreadsheets Spreadsheets allow you not only to

create and use functions such as sum and average, but also to copy them to other cell locations in the spreadsheet. An example of this can be found by using

the Fill command.

Lesson #4: Fill and Replicating in Spreadsheets We will look at two examples:

AutoFill right and down. Open the “Lesson #4-My Household Expenses”

excel file. Use the sum equation to find the Total Expenses

for column B. To find the sum for the rest of the row:

Instead of typing the sum function several times, we can click on B11, right click and copy OR use the Autofill button (the little black box on the cells bottom left hand

corner) Highlight column C to E, right click and paste. This will copy our formula along row 11. OR you can use the Fill command

Lesson #4: Fill and Replicating in Spreadsheets

Relative Replicating Excel uses relative references in a formula by default. An example of a relative reference is A1.

Cell range references can also be relative, such as A1:C4. Relative references are based on the relative positions

of the first and last cells in a range. This means when you move or copy a formula containing a

relative reference, the cell references adjust accordingly. Example: To add the numbers in column B, you might

have the function = sum(B5:B9). When this is copied to column C, Excel automatically

changes it to = sum(C5:C9). This is called relative data replicating.

Lesson #4: Fill and Replicating in Spreadsheets Absolute Replicating Absolute references in a formula are denoted by

a dollar sign ($) that precedes the row and column portions of a reference – for example $A$1.

An absolute cell reference refers to a specific location, which doesn't change when you move or copy the formula. Example: This could be used when data does not

change from month to month, or when we want to multiply a fixed amount times data that does change.

Lesson #4: Fill and Replicating in Spreadsheets Absolute Replicating Example: Lets say that all the

expenses have increased by 2%. In column A13 type Expense Increase

and in column B13 type in 2%. We need to multiply all our expenses

by this 2% (make B13 an absolute value so that it does not change).

Lesson #4: Fill and Replicating in Spreadsheets--Assignment Please do Assignment #4 in the Student

Share folder

Lesson #5: Graphing My Data Not only can a spreadsheet program

perform calculations, it can also make a wide variety of graphs from the data.

You will learn how to use the charting wizard in your spreadsheet to create both bar graphs and pie charts.

Lesson #5: Graphing My Data A graph is an excellent way to communicate a

set of data. Many subject areas such as science and

geography rely heavily on a wide variety of graphs. A computer-generated graph looks much more

professional than a hand-drawn graph. It is very quick and easy to use spreadsheet software to create graphs.

Lets watch the following demonstration to see how a chart wizard is used to create a bar graph and a pie chart for expenses.

Lesson #5: Graphing My Data--Assignment Please do Assignment #5 Boarders:

You can add boarders to make your data stand out more. To add a boarder, select the cells that you want, and click on

Now chose how you want your boarders to look. You can also see your boarders in Format Cell

Now if you are stuck with anything else, please go to Help and click on Microsoft Office Excel Help, and type what you need to know.