The Definition of A Spreadsheet

A spreadsheet is the computer equivalent of a paper ledger sheet. It consists of a grid made from columns and rows. It is an environment that can make number manipulation easy and somewhat painless.

The math that goes on behind the scenes on the paper ledger can be overwhelming. If you change the loan amount, you will have to start the math all over again (from scratch). But lets take a closer look at the computer version.

So Why do it on a Computer?

Looking at our previous example it seems pretty evenly matched. Right? WRONG! The nice thing about using a computer and spreadsheet is that you can experiment with numbers without having to RE-DO all the calculations. Lets change the interest rate and then the number of months. Let the COMPUTER do the calculations! Once we have the formulas set up, we can change the variables that are called from the formula and watch the changes.

Change the Interest Rate		Change the Number of Months

Do that on paper and you better get your calculator back out and get an Eraser and hope you punched all the right keys and in the right order. Spreadsheets are instantly updated if one of the entries is changed.
NO erasers! NO new formulas! NO calculators!

Spreadsheets can be very valuable tools in business. They are often used to play out a series of what-if scenarios! (much like our car purchase here.)

Basic of Spreadsheet

So let's get started digging into what makes a spreadsheet work. Spreadsheets are made up of

• columns
• rows
• and their intersections are called cells

In each cell there may be the following types of data

• text (labels)
• number data (constants)
• formulas (mathematical equations that do all the work)

Take a look at the explanations of each of these.
Remember there will be a short quiz later on so if there is something you need to take notes on, TAKE NOTES ON IT!

What is column?

In a spreadsheet the COLUMN is defined as the vertical space that is going up and down the window. Letters are used to designate each COLUMN'S location.

In the above diagram the COLUMN labeled C is highlighted.

What Is Row?

In a spreadsheet the ROW is defined as the horizontal space that is going across the window. Numbers are used to designate each ROW'S location.

In the above diagram the ROW labeled 4 is highlighted.

What is Cell?

In a spreadsheet the CELL is defined as the space where a specified row and column intersect. Each CELL is assigned a name according to its COLUMN letter and ROW number.

In the above diagram the CELL labeled B6 is highlighted. When referencing a cell, you should put the column first and the row second.

Types of Data

In a spreadsheet there are three basic types of data that can be entered.

• labels - (text with no numerical value)
• constants - (just a number -- constant value)
• formulas* - (a mathematical equation used to calculate)

data types	examples	descriptions
LABEL	Name or Wage or Days	anything that is just text
CONSTANT	5 or 3.75 or -7.4	any number
FORMULA	=5+3 or = 8*5+3	math equation

Labels in Excel

Labels are text entries. They do not have a value associated with them. We typically use labels to identify what we are talking about.

In our first example: the labels were

• computer ledger
• car loan
• interest
• # of payments
• Monthly Pmt.

---

Again, we use labels to help identify what we are talking about. The labels are NOT for the computer but rather for US so we can clarify what we are doing.

---

*ALL formulas MUST begin with an equal sign (=).

Constants in Excel

Constants are entries that have a specific fixed value. If someone asks you how old you are, you would answer with a specific answer. Sure, other people will have different answers, but it is a fixed value for each person.

In our first example: the constants were

• $12,000
• 9.6%
• 60

As you can see from these examples there may be different types of numbers. Sometimes constants are referring to dollars, sometimes referring to percentages, and other times referring to a number of items (in this case 60 months).These are typed into the computer with just the numbers and are changed to display their type of number by formatting (we will talk about this later).
Again, we use constants to enter FIXED number data.

Formulas in Excel

Formulas are entries that have an equation that calculates the value to display. We DO NOT type in the numbers we are looking for; we type in the equation. This equation will be updated upon the change or entry of any data that is referenced in the equation.

In our first example, the solution was $252.61
This was NOT typed into the keyboard. The formula that was typed into the spreadsheet was:
=PMT(C4/12,C5,-C3)

C4 (annual interest rate) was divided by 12 because there are 12 months in a year. Dividing by 12 will give us the interest rate for the payment period - in this case a payment period of one month.

It is also important to type in the reference to the constants instead of the constants. Had I entered =PMT(.096/12,60,-12000) my formula would only work for that particular set of data. I could change the months above and the payment would not change. Remember to enter the cell where the data is stored and NOT the data itself.

Formulas are mathematical equations. There is a list of the functions available within Excel under the menu INSERT down to Function.

Formulas OR Functions MUST BEGIN with an equal sign (=).

Again, we use formulas to CALCULATE a value to be displayed.

Formulas in Excel

Formulas are entries that have an equation that calculates the value to display. We DO NOT type in the numbers we are looking for; we type in the equation. This equation will be updated upon the change or entry of any data that is referenced in the equation.

In our first example, the solution was $252.61
This was NOT typed into the keyboard. The formula that was typed into the spreadsheet was:
=PMT(C4/12,C5,-C3) C4 (annual interest rate) was divided by 12 because there are 12 months in a year. Dividing by 12 will give us the interest rate for the payment period - in this case a payment period of one month.

It is also important to type in the reference to the constants instead of the constants. Had I entered =PMT(.096/12,60,-12000) my formula would only work for that particular set of data. I could change the months above and the payment would not change. Remember to enter the cell where the data is stored and NOT the data itself.

Formulas are mathematical equations. There is a list of the functions available within Excel under the menu INSERT down to Function.

Formulas OR Functions MUST BEGIN with an equal sign (=).

Again, we use formulas to CALCULATE a value to be displayed.

Basic Formulas in Excel

When we are entering formulas into a spreadsheet we want to make as many references as possible to existing data. If we can reference that information we don't have to type it in again. AND more importantly if that OTHER information changes, we DO-NOT have to change the equations.

If you work for 23 hours and make $5.36 an hour, how much do you make? We can set up this situation using

• three labels
• two constants
• one equation

Let's look at this equation in B4:

• = B1 * B2
• = 23 * 5.36

Both of these equations will produce the same answers, but one is much more useful than the other.
DO YOU KNOW which is BEST and WHY?

It is BEST if we can Reference as much data as possible as opposed to typing data into equations.

Change in Formulas

In our last example, things were pretty straightforward. We had number of hours worked multiplied by wage per hour and we got our total pay. Once you have a working spreadsheet you can save your work and use it at a later time. If we referenced the actual cells (instead of typing the data into the equation) we could update the entire spreadsheet by just typing in the NEW Hours worked. And -- you're done!

---

Let's look at the new spreadsheet:

• hours have been changed to 34
• wage is the same
• total pay would now be = 34 * 5.36
• but would still be = B1 * B2

---

If we had typed in ( = 23 * 5.36 ) the first time and just changed the hours worked, our equation in B4 would still be ( = 23 * 5.36 )

INSTEAD we typed in references to the data that we wanted to use in the equation.
We typed in ( = B1 * B2 ). These are the locations of the data that we want to use in our equation.

It is BEST if we can Reference as much data as possible as opposed to typing data into equations.

Basic Math Functions

Spreadsheets have many Math functions built into them. Of the most basic operations are the standard multiply, divide, add and subtract. These operations follow the order of operations (just like algebra). Let's look at some examples.

---

For these following examples let's consider the following data:

A1 (column A, row 1) = 5 A2 (column A, row 2) = 7 A3 (column A, row 3) = 8 B1 (column B, row 1) = 3 B2 (column B, row 2) = 4 B3 (column B, row 3) = 6

A B 1 5 3 2 7 4 3 8 6

---

Operation	Symbol	Constant Data	Referenced Data	Answer
Multiplication	*	= 5 * 6	= A1 * B3	30
Division	/	= 8 / 4	= A3 / B2	2
Addition	+	= 4 + 7	= B2 + A2	11
Subtraction	-	= 8 - 3	= A3 - B1	5

Methods of Selecting Cells

Selecting cells in an equation is a very important concept of a spreadsheet. We need to know how to reference the data in other parts of the spreadsheet. When entering your selection you may use the keyboard or the mouse.

We can select several cells together if we can specify a starting cell and a stopping cell. This will select ALL the cells within this specified BLOCK of cells.

If the cells that we want to work with are not together (non-contiguous cells) we can use the comma to separate the cells or by holding down the control-key (command key on a MAC) and selecting cells or blocks of cells the comma will be inserted automatically to separate these chunks of data.

---

For the following examples let's consider the table below:

A1 (column A, row 1) = 5 A2 (column A, row 2) = 7 A3 (column A, row 3) = 8 B1 (column B, row 1) = 3 B2 (column B, row 2) = 4 B3 (column B, row 3) = 6

A B 1 5 3 2 7 4 3 8 6

This is just a discussion of selection methods. If we wanted to add the cells in the (To Select) you would type in
=sum(Type In)
or
=sum(Click On)

---

To Select	Type In	Click On
A1	A1	click on A1
A1, A2, A3	A1:A3	click on A1 with button down drag to A3
A1, B1	A1:B1	click on A1 with button down drag to B1
A1, B3	A1, B3	click on A1 type in comma (or hold down the control key on a PC) (or hold down the command key on a MAC) click on B3
A1, A2, B1, B2	A1:B2	click on A1 with button down drag to B2