Materials Credited to DMACC
Agriculture Technology Class
Spreadsheet Unit – Lesson #1
Chapter 1 – Introduction to Excel
Today, I'm going to teach you all the introductory information you'll need to work successfully with Excel.
You're going to learn a little terminology, some navigation and editing techniques, and the difference between constant values and formulas.
Starting Excel
If you use Windows 95 or 98, click the 'Start' button, choose 'Programs' and then locate and click once on the 'Microsoft Excel' icon.
If you use a Mac or Windows 3.x, locate and double-click on the icon that says 'Excel.' Tip: You may find this icon inside the 'Microsoft Office' group or folder.
About Your Keyboard
Much of the navigation in Excel will take place with your keyboard. Most of the time, you will want to move one column over or one row up or down. As you will learn, the arrow keys on your keyboard will take you anywhere you want to go. However, there are two sets of arrow keys on most keyboards.
You should have four keys (just to the right of your ENTER and right SHIFT key) with arrows printed on them. When I refer to the 'down arrow' in these lessons, I am talking about one of these keys--the key with a down arrow printed on its back. Likewise, when I refer to the 'up arrow' or 'left arrow' or 'right arrow,' I am talking about other keys in this group.
Unless you're using a laptop computer, there may be a set of number keys on the far right side of your keyboard. Collectively, these keys are known as the 'numeric keypad.' The numeric keypad has keys representing all the digits between zero and nine. It also has a decimal point and some mathematical operators. The numeric keypad will come in handy when we need to start working with numbers.
Look closely at your numeric keypad. You will also see that the 8 key doubles as an up arrow. The 2 key could be used as a down arrow. The 4 and 6 would work as left and right arrows. When I ask you to press the down arrow, I imagine some of you might accidentally hit the '2' key on the numeric keypad instead of striking the plain vanilla down arrow that is positioned elsewhere on your keyboard.
There is nothing wrong with using the arrow keys on your numeric keypad. However, you need to be very careful. Above the number 7, there should be a button with the phrase 'Num Lock' printed on it. Tap this key repeatedly, and a light somewhere on your keyboard should alternately glow and dim. When the light is glowing, the numeric keypad is locked into typing numbers. When the light is out, the numeric keypad can be used for navigation.
Remember: if you just use the plain arrow keys on your keyboard for navigation and avoid the numeric keypad, you won't have any difficulties. But if you'd like to use the arrow keys on the numeric keypad, strike the 'Num Lock' key until the light on your keyboard goes out.
Chapter 2
The Workbook
Once Excel has started, you will be presented with a workbook, consisting of one or more worksheets divided into columns and rows.
Columns
At the top of each column, you should see a letter of the alphabet. The first column is identified with the first letter of the alphabet (A). The second column is identified with the second letter of the alphabet (B), and so on.
In most versions of Excel, there are 256 columns. The first 26 columns are identified with a single letter of the alphabet.
Tap the right arrow in the lower right corner of your keyboard repeatedly until you can see column Z (the 26th column). Then, tap the right arrow once more. Notice how the next column is identified as 'AA.' The column after that is AB, and then we have AC, and so on.
As you travel across your worksheet, you will notice that each column has its own unique one- or two-letter identifier. No two columns have the same identifier.
Rows
At the beginning of each row, you should see a number. The first row is identified with the number 1, the second row with the number 2, and so on, all the way down your screen. Tap the down arrow in the lower right corner of your keyboard a few times and you'll see these row numbers get progressively larger.
Don't bother trying to find the last row--it will take far too long. In the oldest versions of Excel, there were over 16,000 rows. In the latest version, there are more than 65,000 rows to work with!
Cells
The intersection of any column with any row is referred to as a cell. Because there are so many columns and rows in a typical worksheet, you should have at least 4 million cells to work with.
Each of the cells in your worksheet has its own specific address. A cell's address consists of that cell's column letter followed immediately by its row number.
For example, a cell at the intersection of column B and row 7 would be identified as cell B7.
A cell at the intersection of column I and row 15 would be identified as I15.
And a cell at the intersection of column AB and row 423 would be identified as cell AB423.
Each cell in your worksheet is a potential repository for information. If you would like to type information into a specific cell, you must first maneuver a small box known as a cell pointer into that cell.
Chapter 3
Workbook Navigation
Each time you tap the arrow keys on your keyboard, you will move the cell pointer. You can maneuver the pointer into any cell in your worksheet by tapping one or more of your arrow keys.
Although the arrow keys will take you anywhere you'd like to go, there are many other keys on your keyboard that can assist in navigation. I have two personal favorites that I use all the time. I'd like you to try them out, so please press your down arrow to move to any row other than row 1. Then, press your right arrow to move to any column but column A.
Home
Now, tap the 'Home' key on your keyboard. You'll find it just above the plain arrow keys. (There may also be an extra Home key on your numeric keypad, but Num Lock must be turned off for that one to work). Notice how the pointer moves immediately to the beginning of the row.
This can be extremely convenient if you're working with multiple rows of data. Any time you need to get to the beginning of a row, just tap the Home key.
CTRL+Home
This one is wonderful. Hold down the CTRL key on your keyboard (you'll find one below your SHIFT key) and gently tap and release the Home key. Notice how you move immediately and gracefully to cell A1--the very beginning of your worksheet!
Navigation Keys
Below, you'll find a complete list of Excel navigation keys. Save this list near your computer--you're bound to refer to it again and again!
Note: when you see two keys separated by a + sign, that simply means to hold down the key in front of the + sign while gently tapping and releasing the key after the + sign.
Right arrow Move one cell to the right
Left arrow Move one cell to the left
Down arrow Move down one cell
Up arrow Move up one cell
Ctrl+Rt arrowMove to last cell in row
Ctrl+Left arrow Move to first cell in row
Ctrl+Up arrow Move to first cell in column
Ctrl+Dn arrow Move to last cell in column
Tab Move one cell to the right
Shift+Tab Move one cell to the left
Enter Move down one cell
Shift+Enter Move up one cell
Home Move to beginning of row
Ctrl+Home Move to beginning of worksheet
Ctrl+End Move to end of worksheet
Page Up Move up one screen
Page Down Move down one screen
Alt+Page Up Move left one screen
Alt+Page Down Move right one screen
Ctrl+Page Up Move to previous worksheet
Ctrl+Pg Down Move to next worksheet
Hit F5, then enter cell address (such as 'A5' or 'Q323') Go immediately to cell address you typed
Mouse Navigation
If you'd like, you can also move the pointer with your mouse. Simply point your mouse at the desired cell, click the left mouse button gently and release. A scroll bar on the right side of your screen and another on the bottom right corner of the screen should help you to bring hidden cells into view. To bring cells into view with the scroll bars, just click your mouse on the small arrow buttons on either end of each scroll bars.
Chapter 4
Entering Data
Any cell in Excel will only accept one of two types of data.
You can either enter something called a constant value into a cell, or you can enter a formula.
- Constant values are typed directly into a cell and never change unless a human manually edits the information.
- Formulas are mathematical equations which depend on existing constant values and/or other formulas in order to calculate a value. The value of a formula can automatically change, without any human intervention, when other constant values or formulas in your worksheet change.
Constant values
There are two types of constant values: numbers and text.
Numbers, which are always right-aligned, can include any of the digits between 0 and 9 or any of the following characters:
$ % ( ) , . - +
If a constant value contains any other character (such as a space or a question mark or the letter 'z'), it will be classified as text. Excel will never perform any mathematical operations on text.
You can usually tell how Excel has classified a constant value immediately after you leave the cell containing the constant value.
If Excel has classified a constant value as a number, the number will scoot over to the right side of the cell when you leave.
If Excel has classified a constant value as text, the text will scoot over to the left side of the cell when you leave.
Exercise– Assignment to follow along with
To see Excel's constant value classification scheme in action, try this little exercise:
First, press CTRL+Home to move quickly to cell A1.
In cell A1, type the number 45
Press the down arrow or the ENTER key on your keyboard. Notice how the number 45 scoots over to the right. Excel has classified it as a number.
On row 2, type the word 'car' (without the quotes).
Press the down arrow or the ENTER key again. This time, Excel pushes the value to the left. It has clearly been classified as text.
On row 3, try this: type 1,234 and press the down arrow or ENTER key. Notice how the value you just typed is immediately classified as a number. Excel permits the appropriate use of commas in numbers.
On row 4, type 23,59 and press the down arrow or ENTER key. Notice how this value is immediately classified as text. This is because you have not used the comma in a fashion that is appropriate for numbers.
If you asked Excel to add up all the values in column A, it would completely ignore the items on row 2 and 4. These items have been classified as text, and therefore have no place in a mathematical calculation. The only values Excel would consider summing would be the number 45 on row 1 and the number 1,234 on row 3.
Chapter 5
Formulas
A formula always consists of an equal sign ( = ) followed immediately by a mathematical equation. The formula can contain a mixture of numbers and/or cell addresses which are acted upon by the following mathematical operators:
+ add
- subtract
/ divide
* multiply
% percent (divide by 100)
** multiply by a power of 10
^ exponentiate
Formulas MUST start with an = sign. If you forget to include the = sign at the beginning of your formula, Excel will not calculate an answer for you.
For example, suppose you'd like to know how many minutes there are in a day. There are 60 minutes in an hour and 24 hours in a day, so you'll need to multiply 60 and 24 together to get your answer.
Use your arrow keys to get to cell A5 and type the following:
=60*24
Notice how we use an asterisk (*) to represent multiplication. You may have learned to use the letter 'x' for multiplication in grade school. Excel does not permit the use of the letter 'x' for multiplication, because it has already used the letter 'x' to represent a column.
If you ever needed to multiply something in column 'x' with another value, and you were using the letter 'x' to represent multiplication as well as to represent the column, things could get very confusing very fast. By using one character (the asterisk) to represent multiplication and another character entirely to identify the column, we avoid that sort of confusion.
Anyway, press the down arrow or the ENTER key, and you'll get your answer. If you don't get an answer, I bet you forgot to type an = sign out front, didn't you? Press your up arrow to return to cell A5 and retype the equation from scratch.
Now, suppose you'd like to find out how many minutes are in a (non-leap) year. You're going to need to multiply the number in cell A5 (the number of minutes in a day) by 365 (the number of days in a year) to get that answer.
But suppose further that you're a bit of a slow and ineffective typist and you'd rather not have to retype the answer that is displayed in cell A5. One of the wonderful things about Excel is that you don't have to retype anything! Instead, just incorporate the address containing the value you want to use right into your next equation.
Use your arrow keys (if necessary) to get to cell A6 and type the following equation (don't forget the = sign this time):
=a5*365
Press the down arrow or the ENTER key, and there's your answer! You have just succeeded in typing your very first formula.
When you enter a formula, the resulting value of the formula appears on the worksheet.
If you would like to see the formula you typed again, use your arrow keys to return to the cell containing your formula.
The formula will be displayed in an area known as the 'formula bar' at the top of the screen (just above your column letters and below the 'File,' 'Edit' and other menus and toolbars).
If you'd like to edit a formula you've typed, just go to the cell containing the formula (cells A5 or A6 will work here). Then, click once inside the formula bar, and you will find yourself able to edit the formula.
You can then use your arrow keys and Home/End to move through the formula bar. You can add text at any time, and the Backspace/Delete keys can be used to remove text. Press ENTER when you are finished editing your formula.
You can also move into edit mode by returning to a cell containing a formula you wish to change and pressing the F2 key at the top of your keyboard. Again, press ENTER when you are done modifying the cell contents.
If you want to completely erase the contents of a cell, use your arrow keys or mouse to return to the cell (let's use cell A2 for this exercise) and then press the DELETE key on your keyboard.
If you just want to overwrite the contents of a cell, just return to the cell and type something new. The new information will replace the old instantly. Let's fix the value in cell A4. Use your arrow keys to move the pointer to cell A4. Then, simply type the following:
2,359
and press your ENTER key. Notice how the old text value is replaced by this new numeric value.
Undo
If you ever accidentally delete or overwrite an important value or formula, don't try to fix it yourself! Instead, simply click Excel's 'Edit' menu and then choose 'Undo.' The value or formula will be reconstituted instantly!
Tip: Need to See your Formulas?
If you would like to make all your formulas visible, hold down the CTRL key and strike the backward accent
(on the upper left side of your keyboard to the left of the number 1 and above the TAB key).
Hold down the CTRL key and press the backward accent again to toggle back to the normal display.
Ag Technology – TaylorSpreadsheet Unit –Lesson #1Page #1