Excel Tutorial No 1
What is 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 let's take a closer look at the computer version.
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 setup, we can change the variables that are called from the formula and watch the changes.
Change the Interest Rate / Change the Number of MonthsDo 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.)
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.
Columns
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 labelled C is highlighted.
Exercise
1. Click on the letter “C” of the C column. The column C is highlighted.
2. Highlight subsequently columns A, B and E.
3. Click on column B. Then press Ctrl on the keyboard and click on column F at the same time.
4. Repeat the same as on 3 but with Shift pressed. What are the differences?
Rows
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 labelled 4 is highlighted.
Exercise
1. Click on row number “4”. The 4th row is highlighted.
2. Highlight subsequently rows 1, 2 and 5.
3. Click on row 3. Then press Ctrl on the keyboard and click on row 7 at the same time.
4. Repeat the same as on 3. but with Shift pressed. What are the differences?
Cells
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 labelled 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 / descriptionsLABEL / 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
*ALL formulas MUST begin with an equal sign (=).
Entering Text and Constants
Click on the Excel window, select a cell by clicking on it, and enter: Excel is fun.
Observe the following:
Observe that your text is displayed in two areas. Text is displayed in the active cell within the workbook and it is also displayed in the formula bar. The formula bar is activated as soon as you begin typing in a cell. At the far left is the reference section, which will show the reference of the active cell.
Next to the formula bar are the Cancel and Enter buttons (). The Cancel and Enter buttons are only visible while Excel is in edit mode. Excel is in edit mode anytime you begin typing an entry. To put Excel in edit mode, click in the formula bar.
Within the Excel window, click in the formula bar to display the Cancel and Enter buttons.
The Enter button enters the text you typed into the cell. You could also press the Return key on the keyboard.
If you want to edit the text you entered into a cell, you click the formula bar, type your changes and click on the Enter button.
The Cancel button cancels your changes.
Exercise
1. Within the Excel window, click in the formula bar and change the text:
fun, to outrageous.
2. Click on the Enter button to enter the edit.
3. Click on the Cancel button to cancel the edit.
Entering constant values is the same as entering text, except that constant values are right-justified by default. You will learn how to change this default when you learn other formatting changes.
Entering Formulas
All formulas in Excel must begin with an equal sign (=). When a formula is entered into a cell, the formula itself is displayed in the formula bar when that cell is highlighted, and the result of the formula is displayed in the actual cell. When you are typing in formulas, do not type spaces; Excel will delete them.
Exercise
1. Within the Excel window, select cell A2 and enter the constant value 12. (Remeber to click the Enter button or the Return key when you are done typing).
2. Within the Excel window, select cell A3 and enter the constant value 15.
3. Within the Excel window, select cell A4 and click on the formula bar.
4. Within the formula bar, enter an equal sign followed by A3+A4.
Observe:
Click on the Enter button or press the Return key to enter the formula.
Your worksheet should look as follows:
Excel displays the result of the formula in cell A4.
Within the Excel window, select cell A3 and change the number 15 to the number 40, and enter the edit.
The formula value should have changed in cell A4 to the number 52.
Now that you know how to enter information into an Excel workbook it is time to create a simple workbook
Creating a Simple Workbook
To create a simple workbook, you need to start with a blank workbook.
Within the Excel window, close and do not save the Workbook1 document.
Choose New from the File menu.
A new workbook should appear labeled Workbook2.
Starting in cell A1, build the following table:
To move through your worksheet, you can use the arrow keys.
· The down arrow moves the active cell down to the next row.
· The up arrow moves the active cell up to the previous row.
· The right arrow moves the active cell right to the adjacent column.
· The left arrow moves the active cell left to the adjacent column.
· The return key moves the active cell to the next row.
Remember to click on the Enter button or press the Return key to enter each check number.
Exercise
1. Select cell B1 and type in the text:Data.
2. Starting in cell C1, build the following table:
3. Select cell D1, and type the following text: Payment Amount.
Observe that the text is too large for the cell. You can change the width of the cell and as a result the whole column to fit the text.