Monday, July 27, 2009
Sunday, July 26, 2009
Copying the Percentage Formula
Notice that we didn't copy the percentage formula when we did the last copying process. If we had copied a sum formula, it would have added teh four percentages. we don't want the sum of the percentages . we want a percentage of only applies to the overall monthly totals. so , we need to copy the percentage formula separately . Click on cell f21 copy the percentage formula in cell r21 to cell g21 . This is the average percentage that Income is greater than Expenses .
Tuesday, July 21, 2009
conttinue function
This is a really great, timesaving feature. We’ll now show you how to use the Help features of Excel 2007 to work with, and understand, these functions.
Click All in the drop down menu.
The Select a function menu will look like the image below.
Look at all the functions (formulas)! We’ll just go through how to use the addition formula (SUM) in this tutorial. If you need these formulas in the future, you’ll know they’re here.
Use the elevator bar on the right side of the Select a function menu screen to move down the list until you see SUM. Click SUM.
Then click OK.
Remember, you clicked-on Cell C9 – which was “empty” because you deleted the formula in that cell.
When you clicked OK, the Function Arguments menu screen (above) appeared. If you look at the top of the screen in the SUM area, you’ll see that Excel 2007 has “guessed” that you desire to add the numbers above cell C9 – where you clicked in your spreadsheet. Smart Excel! Notice that it indicates that cells C6:C8 will be added (sum cells C6 through C8 – the colon (:) means “through.” It also indicates the numbers in cells C6, C7 and C8 and gives you the sum {300;50;150} = 500 (right arrow above).
But it’s a little unclear how Excel did this. The Help on this Excel Function is excellent. So, to see how this SUM equation works, we’ll go to Help. To do this, click Help on this function in the lower left corner of the screen (see lower left arrow above).
You will see a Microsoft Office Excel Help window appear (similar to the one above) that will show you how to use this SUM function (or any function).
One of the really neat things about these Help windows is that there are examples for each function. We moved down the SUM help screen using the elevator bar on the right of the help screen. The bottom of the screen looks like the image below. Spend a few minutes looking at the SUM Help window and notice all of the features.
The bottom of the SUM help screen looks like the image on the left. Notice that it gives you examples from a small spreadsheet that has data in cells A1 through A6. It uses these numbers in the examples at the bottom of the help screen.
When you have reviewed all of the help you care to see, carefully click the X at the upper right corner of the Microsoft Office Excel Help blue bar to close the Microsoft Excel Help window. If you accidentally close the spreadsheet, simply reply yes to Save, and then re-open the spreadsheet as you did on Page 13.
The Function Arguments menu screen will still be on the screen.
As you can see, in the area to the right of Number 1, the “Wizard” has “guessed” that you want to add the numbers in the range C6 to C8 - (C6:C8). Now that you are becoming skilled with Excel, we’ll try something special. Carefully, point to some “plain part,” in the gray area above. Click and hold down the left mouse button, and drag the above SUM box “away” so that you can see your numbers in C column cells. When you have done this, release the mouse button. Now click-on the “small box” on the right edge of the Number 1 area (see arrow above). It has a little red arrow in it.
The below Function Arguments window will appear.
Highlight cells C6 to C8 in the spreadsheet (click-on C6, hold down the left mouse button, and drag until the three cells are highlighted). A “marquee” will begin to flash around the cells, indicating they are highlighted (left arrow above). The Function Arguments area will appear as above. Now click the small button on the right of the cell (see right arrow above). The numbers will show in the area to the right of Number 1. Click OK at the bottom of the Function Arguments menu screen. You’ll see that the SUM formula [=SUM(C6:C8)] shows in the formula area at the top of the screen. This is a really handy method to highlight a “group” of numbers you want to add.
AutoSum METHOD - ∑
Since we add numbers more than any other operation in spreadsheets, Excel spreadsheet has an additional feature - Auto Sum. Move to cell C9 again and tap the Delete key to erase your last formula.
You should still be on the Formulas Tab/Ribbon. Notice ∑ Auto Sum button. Click the AutoSum button.
An image similar to the one on the left will appear.
Click ∑ Sum.
WOW !! Automatic addition!! Notice that the cells, you’d logically desire to add, have a marquee around them and that the SUM function is displayed in cell C9. You’ll need to confirm that this is the correct formula. So, tap the Enter key, and the SUM function will now be set in cell C9. Any time you want to add using this method just click-on the cell where you desire the total to be and click ∑ Sum.
This would be a good time to save your work.
PERIODICALLY SAVE AND REPLACE YOUR WORK IN CASE YOU LOOSE POWER TO YOUR COMPUTER
Now move to cell C17 and add the total Expenses in cells C13 to C16 - using each of the four methods.
While you are in cell C17, go ahead and place a line at the top of cell C17 using the format cells – border method that you learned on Page 5.
Subtraction
In cell A19 type-in Net Income. Next, adjust the width of column A (Page 6).
Click-on cell C19.
In cell C19 we want to subtract ( - ) the amount in for Expenses in cell C17 from the amount for Income in cell C9. This can be accomplished by using either the Type-In Method or Point Method. Go ahead and do this. Don’t forget to tap the Enter key to confirm your formula.
The formula should look like =C9‑C17
More Cell Formatting
We want our numbers to look better. To do this we'll include dollar signs and decimal points in our numbers. This is done by using the mouse. Point to cell C6, hold down the left mouse button and drag (move) down slowly to highlight cells C6 through C19. Your screen should look like the image below.
Now point anywhere in the highlighted area and click the RIGHT mouse button. A pop-up menu will appear. Click-on Format Cells (like you have done before).
Your Format Cells menu screen will appear – similar to the image at the top of the next page.
Click-on the Number “Tab” at the top of the Format Cells menu screen. Point to Currency and click-on Currency.
Notice several things. The right side shows the number of decimal places. The 2 is the default for cents. We'll use 2. Notice above the Decimal Places that there is a sample of what our number will look like. At the lower right it shows how negative numbers can appear, depending on your choice. When a negative number is calculated, it will appear with your choice. Now click-on OK. All the numbers now have $. If you have large numbers that are "too wide" for the current column width you will see some ######## in the cells where these numbers are located. If this occurs in your spreadsheet, go ahead and widen the columns as you did previously (Page 6).
Your spreadsheet numbers should now look like the one on the left.
Division
Now move to cell A21 and type in the word Percent. We're going to calculate a fun percentage to show you how division works and give you some more practice with numbers.
Now move to cell C21. Using either the Type-In Method or the Point Method, divide ( / ) the amount for Income in cell C9 by the amount for Expenses in cell C17.
[The formula should look like =C9/C17]
This will give you a horrid number so why not put a percent symbol with it. Now we’ll repeat what we did above to format our $$$$ (Currency).
Percentages
Point to cell C21 and click the right mouse button. Point to Format Cells, then click the Number tab, then click-on Percentage. Select zero ( O ) Decimal Places. Click OK.
Ta Da !!! A %.
Your spreadsheet should look similar to the image below.
Copying
We could repeat what we did to this point and fill in the Income and Expenses for each of the remaining columns (months). There is a simpler way to do this. Assuming our income and expense amounts are about the same, throughout the months, we want to copy the amounts in Column C to Columns D, E and F. This will require TWO “steps.”
First: Move your cursor to cell C6. We'll highlight what we want to copy; second, we'll tell the spreadsheet where we want to place what we've copied. So, point to C6, hold down the left mouse button and drag (move) down the column until cells C6 through C21 are high-lighted. Your highlighted area should look like the one on the left.
Click the Home Tab then click the Copy button.
You will notice that once again, when you highlight an area, a marquee of running lights moves around the copy area. So, you’ll know you highlighted the correct area (image on right).
Now we'll tell Excel where to copy the data. Point to cell D6, click and hold down the left mouse button and drag down and to the right to cell F21 (This will highlight three columns ‑‑ OCT, NOV, DEC ‑‑ to copy to.). When you have finished your highlighting, your screen should look like the image below.
Make sure you are still on the Home Tab and click the Paste button. Wow !' All those numbers and dollar signs and formulas - EVERYTHING - was copied in a flash!! That sure saved us a lot of time.
Click All in the drop down menu.
The Select a function menu will look like the image below.
Look at all the functions (formulas)! We’ll just go through how to use the addition formula (SUM) in this tutorial. If you need these formulas in the future, you’ll know they’re here.
Use the elevator bar on the right side of the Select a function menu screen to move down the list until you see SUM. Click SUM.
Then click OK.
Remember, you clicked-on Cell C9 – which was “empty” because you deleted the formula in that cell.
When you clicked OK, the Function Arguments menu screen (above) appeared. If you look at the top of the screen in the SUM area, you’ll see that Excel 2007 has “guessed” that you desire to add the numbers above cell C9 – where you clicked in your spreadsheet. Smart Excel! Notice that it indicates that cells C6:C8 will be added (sum cells C6 through C8 – the colon (:) means “through.” It also indicates the numbers in cells C6, C7 and C8 and gives you the sum {300;50;150} = 500 (right arrow above).
But it’s a little unclear how Excel did this. The Help on this Excel Function is excellent. So, to see how this SUM equation works, we’ll go to Help. To do this, click Help on this function in the lower left corner of the screen (see lower left arrow above).
You will see a Microsoft Office Excel Help window appear (similar to the one above) that will show you how to use this SUM function (or any function).
One of the really neat things about these Help windows is that there are examples for each function. We moved down the SUM help screen using the elevator bar on the right of the help screen. The bottom of the screen looks like the image below. Spend a few minutes looking at the SUM Help window and notice all of the features.
The bottom of the SUM help screen looks like the image on the left. Notice that it gives you examples from a small spreadsheet that has data in cells A1 through A6. It uses these numbers in the examples at the bottom of the help screen.
When you have reviewed all of the help you care to see, carefully click the X at the upper right corner of the Microsoft Office Excel Help blue bar to close the Microsoft Excel Help window. If you accidentally close the spreadsheet, simply reply yes to Save, and then re-open the spreadsheet as you did on Page 13.
The Function Arguments menu screen will still be on the screen.
As you can see, in the area to the right of Number 1, the “Wizard” has “guessed” that you want to add the numbers in the range C6 to C8 - (C6:C8). Now that you are becoming skilled with Excel, we’ll try something special. Carefully, point to some “plain part,” in the gray area above. Click and hold down the left mouse button, and drag the above SUM box “away” so that you can see your numbers in C column cells. When you have done this, release the mouse button. Now click-on the “small box” on the right edge of the Number 1 area (see arrow above). It has a little red arrow in it.
The below Function Arguments window will appear.
Highlight cells C6 to C8 in the spreadsheet (click-on C6, hold down the left mouse button, and drag until the three cells are highlighted). A “marquee” will begin to flash around the cells, indicating they are highlighted (left arrow above). The Function Arguments area will appear as above. Now click the small button on the right of the cell (see right arrow above). The numbers will show in the area to the right of Number 1. Click OK at the bottom of the Function Arguments menu screen. You’ll see that the SUM formula [=SUM(C6:C8)] shows in the formula area at the top of the screen. This is a really handy method to highlight a “group” of numbers you want to add.
AutoSum METHOD - ∑
Since we add numbers more than any other operation in spreadsheets, Excel spreadsheet has an additional feature - Auto Sum. Move to cell C9 again and tap the Delete key to erase your last formula.
You should still be on the Formulas Tab/Ribbon. Notice ∑ Auto Sum button. Click the AutoSum button.
An image similar to the one on the left will appear.
Click ∑ Sum.
WOW !! Automatic addition!! Notice that the cells, you’d logically desire to add, have a marquee around them and that the SUM function is displayed in cell C9. You’ll need to confirm that this is the correct formula. So, tap the Enter key, and the SUM function will now be set in cell C9. Any time you want to add using this method just click-on the cell where you desire the total to be and click ∑ Sum.
This would be a good time to save your work.
PERIODICALLY SAVE AND REPLACE YOUR WORK IN CASE YOU LOOSE POWER TO YOUR COMPUTER
Now move to cell C17 and add the total Expenses in cells C13 to C16 - using each of the four methods.
While you are in cell C17, go ahead and place a line at the top of cell C17 using the format cells – border method that you learned on Page 5.
Subtraction
In cell A19 type-in Net Income. Next, adjust the width of column A (Page 6).
Click-on cell C19.
In cell C19 we want to subtract ( - ) the amount in for Expenses in cell C17 from the amount for Income in cell C9. This can be accomplished by using either the Type-In Method or Point Method. Go ahead and do this. Don’t forget to tap the Enter key to confirm your formula.
The formula should look like =C9‑C17
More Cell Formatting
We want our numbers to look better. To do this we'll include dollar signs and decimal points in our numbers. This is done by using the mouse. Point to cell C6, hold down the left mouse button and drag (move) down slowly to highlight cells C6 through C19. Your screen should look like the image below.
Now point anywhere in the highlighted area and click the RIGHT mouse button. A pop-up menu will appear. Click-on Format Cells (like you have done before).
Your Format Cells menu screen will appear – similar to the image at the top of the next page.
Click-on the Number “Tab” at the top of the Format Cells menu screen. Point to Currency and click-on Currency.
Notice several things. The right side shows the number of decimal places. The 2 is the default for cents. We'll use 2. Notice above the Decimal Places that there is a sample of what our number will look like. At the lower right it shows how negative numbers can appear, depending on your choice. When a negative number is calculated, it will appear with your choice. Now click-on OK. All the numbers now have $. If you have large numbers that are "too wide" for the current column width you will see some ######## in the cells where these numbers are located. If this occurs in your spreadsheet, go ahead and widen the columns as you did previously (Page 6).
Your spreadsheet numbers should now look like the one on the left.
Division
Now move to cell A21 and type in the word Percent. We're going to calculate a fun percentage to show you how division works and give you some more practice with numbers.
Now move to cell C21. Using either the Type-In Method or the Point Method, divide ( / ) the amount for Income in cell C9 by the amount for Expenses in cell C17.
[The formula should look like =C9/C17]
This will give you a horrid number so why not put a percent symbol with it. Now we’ll repeat what we did above to format our $$$$ (Currency).
Percentages
Point to cell C21 and click the right mouse button. Point to Format Cells, then click the Number tab, then click-on Percentage. Select zero ( O ) Decimal Places. Click OK.
Ta Da !!! A %.
Your spreadsheet should look similar to the image below.
Copying
We could repeat what we did to this point and fill in the Income and Expenses for each of the remaining columns (months). There is a simpler way to do this. Assuming our income and expense amounts are about the same, throughout the months, we want to copy the amounts in Column C to Columns D, E and F. This will require TWO “steps.”
First: Move your cursor to cell C6. We'll highlight what we want to copy; second, we'll tell the spreadsheet where we want to place what we've copied. So, point to C6, hold down the left mouse button and drag (move) down the column until cells C6 through C21 are high-lighted. Your highlighted area should look like the one on the left.
Click the Home Tab then click the Copy button.
You will notice that once again, when you highlight an area, a marquee of running lights moves around the copy area. So, you’ll know you highlighted the correct area (image on right).
Now we'll tell Excel where to copy the data. Point to cell D6, click and hold down the left mouse button and drag down and to the right to cell F21 (This will highlight three columns ‑‑ OCT, NOV, DEC ‑‑ to copy to.). When you have finished your highlighting, your screen should look like the image below.
Make sure you are still on the Home Tab and click the Paste button. Wow !' All those numbers and dollar signs and formulas - EVERYTHING - was copied in a flash!! That sure saved us a lot of time.
Function Method
Move again to cell C9 and Delete the formula by tapping the Delete key.
Now type in the following: =SUM(
[This tells Excel that we are going to sum some numbers in a RANGE which will follow the =SUM(]
Notice – a new feature in Excel 2007: As you are typing SUM in cell C9 that a pop-up menu appears under the cell. What you see are mathematical functions. One of these is SUM. As you become more comfortable with numbers in Excel, you can select the functions you need without typing in the entire function. We’ll get into this a bit more later.
There are two ways to put in this range:
Arrow Key and Anchor Method: With the keyboard arrow keys, move the cursor to cell C6. As you move you will notice that the cell where the cursor is located appears after the =SUM(. When you get to C6 tap the . (Period) Key. This is called an ANCHOR and holds one end of the RANGE in place. You will notice that a C6:C6 appears in the formula area under the button bar. This is a one cell range. Now move, with the arrow keys, to cell C8. See how cells C6, C7 and C8 are highlighted. This indicates the Range is C6:C8. Excel assumes, logically, that these are the numbers you want to add. Now tap Enter. The numbers still add, but now the formula reads =SUM(C6:C8) instead of =C6+C7+C8 like it did before.
Mouse Method: Move again to cell C9. Delete the formula in cell C9 by tapping the Delete key. Type in =SUM( as you did before. Point to Cell C6 – with your mouse cursor. Click and hold down the left mouse button and move/drag the cursor down to Cell C8 (Cells C6, C7 and C8 should be highlighted) – take your finger off the left mouse button. Tap Enter.
This =SUM Function is a great way to add a lot of numbers, or a block/range of numbers. By simply anchoring, and using page downs, or using the mouse, you can highlight lots and lots of numbers to add quickly. However, since it only sums you can't do subtraction, etc.
Point to cell C9 again. Tap the Delete key to remove the formula currently in cell C9. This is a really important DELETE, since what we’ll explain below won’t work correctly if you do not delete the formula in cell C9.
Functions
There are a number of formulas built into Excel, like Sum. These formulas are called Functions.
Another new feature of Excel 2007 – Tabs/Ribbons. Look at the top of your Excel screen and click on the Formulas Tab. The Formulas Ribbon will display.
On the left of the Formulas Tab/Ribbon is an Insert Function button. Click the Insert Function button.
The Insert Function menu screen will appear (image at right).
Let’s work with the Insert Function menu screen. Click the small down arrow to the right of Or select a category: (see arrow at left).
In the drop down menu that appears you can see that there are all kinds of formulas (functions) that come with Excel spreadsheet (e.g. statistical, mathematical, financial, etc.). Instead of having to go to math, financial, or statistical tables in a book, you can enter data from your spreadsheet into the formulas and
Now type in the following: =SUM(
[This tells Excel that we are going to sum some numbers in a RANGE which will follow the =SUM(]
Notice – a new feature in Excel 2007: As you are typing SUM in cell C9 that a pop-up menu appears under the cell. What you see are mathematical functions. One of these is SUM. As you become more comfortable with numbers in Excel, you can select the functions you need without typing in the entire function. We’ll get into this a bit more later.
There are two ways to put in this range:
Arrow Key and Anchor Method: With the keyboard arrow keys, move the cursor to cell C6. As you move you will notice that the cell where the cursor is located appears after the =SUM(. When you get to C6 tap the . (Period) Key. This is called an ANCHOR and holds one end of the RANGE in place. You will notice that a C6:C6 appears in the formula area under the button bar. This is a one cell range. Now move, with the arrow keys, to cell C8. See how cells C6, C7 and C8 are highlighted. This indicates the Range is C6:C8. Excel assumes, logically, that these are the numbers you want to add. Now tap Enter. The numbers still add, but now the formula reads =SUM(C6:C8) instead of =C6+C7+C8 like it did before.
Mouse Method: Move again to cell C9. Delete the formula in cell C9 by tapping the Delete key. Type in =SUM( as you did before. Point to Cell C6 – with your mouse cursor. Click and hold down the left mouse button and move/drag the cursor down to Cell C8 (Cells C6, C7 and C8 should be highlighted) – take your finger off the left mouse button. Tap Enter.
This =SUM Function is a great way to add a lot of numbers, or a block/range of numbers. By simply anchoring, and using page downs, or using the mouse, you can highlight lots and lots of numbers to add quickly. However, since it only sums you can't do subtraction, etc.
Point to cell C9 again. Tap the Delete key to remove the formula currently in cell C9. This is a really important DELETE, since what we’ll explain below won’t work correctly if you do not delete the formula in cell C9.
Functions
There are a number of formulas built into Excel, like Sum. These formulas are called Functions.
Another new feature of Excel 2007 – Tabs/Ribbons. Look at the top of your Excel screen and click on the Formulas Tab. The Formulas Ribbon will display.
On the left of the Formulas Tab/Ribbon is an Insert Function button. Click the Insert Function button.
The Insert Function menu screen will appear (image at right).
Let’s work with the Insert Function menu screen. Click the small down arrow to the right of Or select a category: (see arrow at left).
In the drop down menu that appears you can see that there are all kinds of formulas (functions) that come with Excel spreadsheet (e.g. statistical, mathematical, financial, etc.). Instead of having to go to math, financial, or statistical tables in a book, you can enter data from your spreadsheet into the formulas and
TYPE-IN METHOD
We want to add the three numbers in cells C6, C7 and C8. To use this method type-in (using the keys on the keyboard) the following formula in cell C9:
= C6 + C7 + C8
Your spreadsheet should look like the image to the right as you are typing in this equation. Note: you don’t have to use capital (upper case) letters – we only did this because they are easier to “see” in the tutorial.
Now – tap the Enter key. Then, click on cell C9 again. The total of these cells will now appear in C9.
When you have completed typing your equation, you will see this formula in the area below the menu bar.
Change the number in cell C6 to 500 (and tap Enter). See how the total AUTOMATICALLY recalculates!!!
THIS IS THE TRUE POWER OF THE SPEADSHEET !!!
Whenever a number is entered in a cell the entire spreadsheet will automatically recalculate.
Something happened here. Notice: you typed an (equal sign) = before the cell location. If you had typed in C6 + C7 + C8, Excel would have thought this entry was a word (text) and this entry would have shown as you typed it.
Try this if you want. Any time you “create” an error in Excel, you can simply re-type or edit the formula to correct the error.
The Type in Method is really easy if you have a few numbers and can see their cell locations on the screen. If you have a lot of cells in the formula, which are on several screens, this is not such a great method. The next method will work a lot better for numbers “all over the place.”
Subtraction, Multiplication, and Division
You can type a (minus) ‑ for subtraction, (asterisk) * for multiplication, and (slash) / for division. As you become more skilled we'll, build some effective formulas – using these features.
Point Method
Move to cell C9 again and click-on it. We'll now add the numbers a second way. Tap the Delete key on the keyboard to delete the current formula.
First, tap the = and then POINT (move) the cursor over cell C6 and tap the LEFT mouse button on cell C6 (you will see a marquee box go around the cell). Now tap a + and move cursor to C7, tap the left mouse button, and tap another + and move the cursor to C8 and tap the left mouse button (notice how as you " + and point " the addition formula is being built in cell C9), now tap Enter. The same formula can be built using the arrow movement keys on the keyboard (except that you don’t have to click each cell as the cell is marked - when you move with the arrow keys). Notice, as you are entering the cell addresses, that as you place another + in the formula, that the cursor “returns” to cell C9. Also notice, as you point to each cell that it is highlighted by a “marquee box.” This “tells” you what cell you’ve pointed to. Pretty neat!
This method is good when you need to move to numbers that are spread out all over the place. Some people like it best and use it all the time ‑‑ it's your choice.
= C6 + C7 + C8
Your spreadsheet should look like the image to the right as you are typing in this equation. Note: you don’t have to use capital (upper case) letters – we only did this because they are easier to “see” in the tutorial.
Now – tap the Enter key. Then, click on cell C9 again. The total of these cells will now appear in C9.
When you have completed typing your equation, you will see this formula in the area below the menu bar.
Change the number in cell C6 to 500 (and tap Enter). See how the total AUTOMATICALLY recalculates!!!
THIS IS THE TRUE POWER OF THE SPEADSHEET !!!
Whenever a number is entered in a cell the entire spreadsheet will automatically recalculate.
Something happened here. Notice: you typed an (equal sign) = before the cell location. If you had typed in C6 + C7 + C8, Excel would have thought this entry was a word (text) and this entry would have shown as you typed it.
Try this if you want. Any time you “create” an error in Excel, you can simply re-type or edit the formula to correct the error.
The Type in Method is really easy if you have a few numbers and can see their cell locations on the screen. If you have a lot of cells in the formula, which are on several screens, this is not such a great method. The next method will work a lot better for numbers “all over the place.”
Subtraction, Multiplication, and Division
You can type a (minus) ‑ for subtraction, (asterisk) * for multiplication, and (slash) / for division. As you become more skilled we'll, build some effective formulas – using these features.
Point Method
Move to cell C9 again and click-on it. We'll now add the numbers a second way. Tap the Delete key on the keyboard to delete the current formula.
First, tap the = and then POINT (move) the cursor over cell C6 and tap the LEFT mouse button on cell C6 (you will see a marquee box go around the cell). Now tap a + and move cursor to C7, tap the left mouse button, and tap another + and move the cursor to C8 and tap the left mouse button (notice how as you " + and point " the addition formula is being built in cell C9), now tap Enter. The same formula can be built using the arrow movement keys on the keyboard (except that you don’t have to click each cell as the cell is marked - when you move with the arrow keys). Notice, as you are entering the cell addresses, that as you place another + in the formula, that the cursor “returns” to cell C9. Also notice, as you point to each cell that it is highlighted by a “marquee box.” This “tells” you what cell you’ve pointed to. Pretty neat!
This method is good when you need to move to numbers that are spread out all over the place. Some people like it best and use it all the time ‑‑ it's your choice.
Retrieving Spreadsheets
When you need to return to a spreadsheet, open Excel, as you did on Page 1. When Excel opens, click the Microsoft Office Button in the upper left corner of the Excel screen.
When you click the Microsoft Office Button you will see, on the right of the Microsoft Office Button menu screen your spreadsheets (Recent Documents). Your MYBUDGET should be on the list. Click on MYBUDGET and your spreadsheet will open.
If you do not see your spreadsheet, click the Open button and follow the steps you used to save your spreadsheet (on Pages 9-11) – except choose Open.
Adding Numbers
Next we want to learn how to add numbers. There are several ways to do this. Each method has its advantages and disadvantages.
Begin by moving your cursor to cell C9, and clicking-on cell C9.
Always move to the cell where you want the answer
to be located.
When you click the Microsoft Office Button you will see, on the right of the Microsoft Office Button menu screen your spreadsheets (Recent Documents). Your MYBUDGET should be on the list. Click on MYBUDGET and your spreadsheet will open.
If you do not see your spreadsheet, click the Open button and follow the steps you used to save your spreadsheet (on Pages 9-11) – except choose Open.
Adding Numbers
Next we want to learn how to add numbers. There are several ways to do this. Each method has its advantages and disadvantages.
Begin by moving your cursor to cell C9, and clicking-on cell C9.
Always move to the cell where you want the answer
to be located.
Saving Spreadsheets
We have done quite a bit of work so now is a good time to save your spreadsheet.
If you have used previous versions of Microsoft Office, 2007 Office will be quite different - in many ways. You’ve already noticed the Tabs and Ribbons, and that there is no File choice in a Menu Bar. Many “selections” have changed significantly in 2007 Office. This is one of them.
Microsoft Office Button
The Microsoft Office Button has replaced File in the Menu Bar. In the upper left corner of your Excel 2007 screen you will see a button similar to the image on the right. This is the Microsoft Office Button.
Click the Microsoft Office Button.
You will now see the Excel 2007 Microsoft Office Button selections.
First, notice that many of the “old” File-Menu Bar choices are included in this menu (they are all here – we’ll show you.)
When we move our cursor over Save As an expanded menu of Save choices appear on the right.
Notice that You can save your spreadsheet in many different formats.
If you save as Excel Workbook, it will save your spreadsheet in an .xlsx format. This will save your spreadsheet in an Extensible Markup Language (XLS) format. This format requires less storage space and makes the spreadsheet more “shareable” with others. However, folks using previous version may have a problem opening your spreadsheet (and may have to download a special program to assist them).
Many folks really like to save their files in Portable Document Format (PDF). One of the neat new features of 2007 Office is the ability to save applications as PDF.
For this introductory Excel tutorial, we’d suggest that you save in the Excel 97-2003 Workbook format.
It’s your choice, so you select the format you desire.
Notice in the upper left corner that there is a “box” to the right of Save In: with a down pointing arrow to the right. Click-on the arrow. This will show you all of the “drives” and “folders” where you may save your work.
When you see the drop-down list in the Save in: area, choose the drive where you want to save your file. If you are going to use a diskette, put a formatted 3 ½ diskette in the A Drive, then click-on the 3 ½ Floppy (A):. We are going to save our file on the Local Disk (C:) – our hard drive, so we chose that drive in the image above (see top arrow).
To the right of File name:, delete the information (which is in the box) and type-in MYBUDGET (see lower left arrow above). This is the name under which you are saving your file. (In the future you will choose logical names for your spreadsheets as you save them.) Now point to Save and click the left mouse button (see lower right arrow above).
Exiting Spreadsheets
Anytime you need to leave your spreadsheet, click the Microsoft Office Button in the upper left corner of your Excel screen, then click Exit Excel. If you have not saved your spreadsheet, a reminder box will appear asking you to do so.
Notice the Excel Options button to the left of Exit Excel. Earlier, we indicated that all of the choices under File in the Menu Bar are still available using the Microsoft Office Button. Click the Excel Options button. The Excel Options menu screen (below) will appear. As you can see, all of the choices available under File in the menu bar are here – as well as many more.
If you click the Resources selection in the Excel Options menu, you will see some great on-line resources available to assist you with Excel.
If you have used previous versions of Microsoft Office, 2007 Office will be quite different - in many ways. You’ve already noticed the Tabs and Ribbons, and that there is no File choice in a Menu Bar. Many “selections” have changed significantly in 2007 Office. This is one of them.
Microsoft Office Button
The Microsoft Office Button has replaced File in the Menu Bar. In the upper left corner of your Excel 2007 screen you will see a button similar to the image on the right. This is the Microsoft Office Button.
Click the Microsoft Office Button.
You will now see the Excel 2007 Microsoft Office Button selections.
First, notice that many of the “old” File-Menu Bar choices are included in this menu (they are all here – we’ll show you.)
When we move our cursor over Save As an expanded menu of Save choices appear on the right.
Notice that You can save your spreadsheet in many different formats.
If you save as Excel Workbook, it will save your spreadsheet in an .xlsx format. This will save your spreadsheet in an Extensible Markup Language (XLS) format. This format requires less storage space and makes the spreadsheet more “shareable” with others. However, folks using previous version may have a problem opening your spreadsheet (and may have to download a special program to assist them).
Many folks really like to save their files in Portable Document Format (PDF). One of the neat new features of 2007 Office is the ability to save applications as PDF.
For this introductory Excel tutorial, we’d suggest that you save in the Excel 97-2003 Workbook format.
It’s your choice, so you select the format you desire.
Notice in the upper left corner that there is a “box” to the right of Save In: with a down pointing arrow to the right. Click-on the arrow. This will show you all of the “drives” and “folders” where you may save your work.
When you see the drop-down list in the Save in: area, choose the drive where you want to save your file. If you are going to use a diskette, put a formatted 3 ½ diskette in the A Drive, then click-on the 3 ½ Floppy (A):. We are going to save our file on the Local Disk (C:) – our hard drive, so we chose that drive in the image above (see top arrow).
To the right of File name:, delete the information (which is in the box) and type-in MYBUDGET (see lower left arrow above). This is the name under which you are saving your file. (In the future you will choose logical names for your spreadsheets as you save them.) Now point to Save and click the left mouse button (see lower right arrow above).
Exiting Spreadsheets
Anytime you need to leave your spreadsheet, click the Microsoft Office Button in the upper left corner of your Excel screen, then click Exit Excel. If you have not saved your spreadsheet, a reminder box will appear asking you to do so.
Notice the Excel Options button to the left of Exit Excel. Earlier, we indicated that all of the choices under File in the Menu Bar are still available using the Microsoft Office Button. Click the Excel Options button. The Excel Options menu screen (below) will appear. As you can see, all of the choices available under File in the menu bar are here – as well as many more.
If you click the Resources selection in the Excel Options menu, you will see some great on-line resources available to assist you with Excel.
Inserting Rows
Oops... a mistake (on purpose). We haven't left enough room at the top of the spreadsheet to insert some budget months. So... move the cursor to the gray 2 along the left edge (this is the second row) so we can insert two new rows. Click the left mouse button. You will notice that the whole row goes light blue and the 2 turn’s dark blue. Make sure the cursor arrow is either on the 2 or somewhere in the blue row.
Click the right mouse button. A drop down menu will appear. Point to Insert. Click the left button on Insert. Notice how one row was inserted and how everything below moved down. Do this again to insert another row. Excel, and all spreadsheets, will remember where they moved your work and automatically adjust for these changes. Income should now be in cell A5.
Aligning Cells
Now we'll type some more text. Go to cell
C3 SEPT (Type-in SEPT and tap the Enter key)
Notice how SEPT is automatically left aligned. Logically, since you are using Excel, the English version, the text is left aligned so that all of the text entries will line up nicely in the column cells. We would like to center SEPT in cell C3. Click on cell C3 to “mark” the cell. One way to center SEPT is to simply click-on the Center button in the button bar at the top of the screen. Make sure that you are ON cell C3, then click-on the center button (see image above right). You’ll notice that SEPT is now centered in cell C3.
Here is another way to center SEPT. Click right on cell C3. Then click on Format Cells.
When the Format Cells Menu appears, click-on the – Alignment Tab and then click-on -- Horizontal - Center --Vertical - Center -- then click OK. Try it.
This is how you can align words for neatness. You can also point to several cells you want aligned and do this. We'll try that next.
Now type the below text in the cells indicated.
D3 OCT
E3 NOV
F3 DEC
G3 MONTHLY TOTALS (tap the Enter key and then widen the width of
Column G)
Next we’ll highlight cells C3 through G3. To do this, point to C3 and click the Left
mouse button. Then, holding down the left mouse button, drag (move) the mouse to the right through G3 – when the cells are highlighted – take your finger off of the left mouse button.
Then point to the group of cells and click the RIGHT mouse button to bring up the Format Cells menu. Click the Alignment Tab and choose Center (vertical & horizontal). Then point to OK and click the left mouse button. All of the cells will be as centered. You could also click the Center button as you did before.
Don’t forget to widen Column G and MONTHLY TOTALS. You know what to do. Move the cursor over the line between cells G and H and drag the line to the right to widen the G column, just like you did a few minutes ago.
Click the right mouse button. A drop down menu will appear. Point to Insert. Click the left button on Insert. Notice how one row was inserted and how everything below moved down. Do this again to insert another row. Excel, and all spreadsheets, will remember where they moved your work and automatically adjust for these changes. Income should now be in cell A5.
Aligning Cells
Now we'll type some more text. Go to cell
C3 SEPT (Type-in SEPT and tap the Enter key)
Notice how SEPT is automatically left aligned. Logically, since you are using Excel, the English version, the text is left aligned so that all of the text entries will line up nicely in the column cells. We would like to center SEPT in cell C3. Click on cell C3 to “mark” the cell. One way to center SEPT is to simply click-on the Center button in the button bar at the top of the screen. Make sure that you are ON cell C3, then click-on the center button (see image above right). You’ll notice that SEPT is now centered in cell C3.
Here is another way to center SEPT. Click right on cell C3. Then click on Format Cells.
When the Format Cells Menu appears, click-on the – Alignment Tab and then click-on -- Horizontal - Center --Vertical - Center -- then click OK. Try it.
This is how you can align words for neatness. You can also point to several cells you want aligned and do this. We'll try that next.
Now type the below text in the cells indicated.
D3 OCT
E3 NOV
F3 DEC
G3 MONTHLY TOTALS (tap the Enter key and then widen the width of
Column G)
Next we’ll highlight cells C3 through G3. To do this, point to C3 and click the Left
mouse button. Then, holding down the left mouse button, drag (move) the mouse to the right through G3 – when the cells are highlighted – take your finger off of the left mouse button.
Then point to the group of cells and click the RIGHT mouse button to bring up the Format Cells menu. Click the Alignment Tab and choose Center (vertical & horizontal). Then point to OK and click the left mouse button. All of the cells will be as centered. You could also click the Center button as you did before.
Don’t forget to widen Column G and MONTHLY TOTALS. You know what to do. Move the cursor over the line between cells G and H and drag the line to the right to widen the G column, just like you did a few minutes ago.
Widening Columns
You probably noticed, as you typed in the numbers, some of the words were just too wide for the default cell width (Investments and Miscellaneous). Let's widen column B to take care of this.
Slowly move the mouse arrow to the right edge of the B cell (between the B and the C). The cursor will turn into an arrow pointing right and left with a small vertical line in the middle (see arrow below). Hold down the left mouse button and move (drag) the line to the right.
As soon as you start to move (drag) the mouse, a dotted vertical line will go down the spreadsheet and it will move as you hold down the left button and drag the mouse to the right. Keep moving your mouse to the right until you are past the widest word - and a bit more (for some space). Release the button. The column is widened. Notice, above the two headed arrow cursor, that as you hold down and drag, it indicates the current width of the column.
Here is another way to widen a column. Point to the B at the top of column B (in the Gray area) and click the left mouse button (The cell should turn dark blue and the column light blue.).
Now, keeping the cursor somewhere in the “blue” area, click the RIGHT Mouse Button. Notice that a menu with Column Width… appears. Click-on Column Width… A new Column Width menu appears. Type in 15 and click-on OK. This is another way to widen a column.
Slowly move the mouse arrow to the right edge of the B cell (between the B and the C). The cursor will turn into an arrow pointing right and left with a small vertical line in the middle (see arrow below). Hold down the left mouse button and move (drag) the line to the right.
As soon as you start to move (drag) the mouse, a dotted vertical line will go down the spreadsheet and it will move as you hold down the left button and drag the mouse to the right. Keep moving your mouse to the right until you are past the widest word - and a bit more (for some space). Release the button. The column is widened. Notice, above the two headed arrow cursor, that as you hold down and drag, it indicates the current width of the column.
Here is another way to widen a column. Point to the B at the top of column B (in the Gray area) and click the left mouse button (The cell should turn dark blue and the column light blue.).
Now, keeping the cursor somewhere in the “blue” area, click the RIGHT Mouse Button. Notice that a menu with Column Width… appears. Click-on Column Width… A new Column Width menu appears. Type in 15 and click-on OK. This is another way to widen a column.
Monday, July 20, 2009
Format Cells
A sub-menu with a caption Format Cells appears.
The RIGHT click will “always bring up” a menu that is “tailored” to the “place” where you click. This will work in any Microsoft Windows product. You can always tell “where” you click the right mouse button for the cursor arrow will always be in a corner of the menu that appears – exactly where you clicked the right mouse button..
Select Format Cells.
When the Format Cells menu screen (below) appears, select the Border Tab.
Look at the Line Style box on the right side of the menu screen. There are several types of lines that you can choose. Point to the thick single line in the Style Area (see arrow) and click the left mouse button. A box will go around the line. Look at the area which says Border. Point to the upper part of the Text box (see arrow) and click the left mouse button. A thick black line will appear at the top of the Text box.
If the thick line does not show-up at the top of the Text box, click-again at the “top line area in the Text box” and the line will “disappear”. Then click-on the thick, single line in the Line Style box again and repeat the previous instructions. If, somehow, you make a mistake, simply click “on and off in the Text line boxes.” You will notice that the lines appear and disappear. This is called a “toggle” in computer “talk.” So, work at this until you get the line on the top of the cell. We have indicated that we want a single thick underline at the top of the cell C7. Point to OK and click the left mouse button.
When you return to the spreadsheet, click somewhere other than cell C7. This is called “clicking away.” You should now see a line at the top of cell C7. Sometimes the box highlighting a cell hides the lines. If you “messed-up”, try again.
Now type in the numbers in the cells indicated.
C11 30
C12 50
C13 150
C14 70 (After you type 70, tap the Enter key)
Now, underline the top of cell C15 like you did cell C7.
Your spreadsheet should now look like the image on the right.
The RIGHT click will “always bring up” a menu that is “tailored” to the “place” where you click. This will work in any Microsoft Windows product. You can always tell “where” you click the right mouse button for the cursor arrow will always be in a corner of the menu that appears – exactly where you clicked the right mouse button..
Select Format Cells.
When the Format Cells menu screen (below) appears, select the Border Tab.
Look at the Line Style box on the right side of the menu screen. There are several types of lines that you can choose. Point to the thick single line in the Style Area (see arrow) and click the left mouse button. A box will go around the line. Look at the area which says Border. Point to the upper part of the Text box (see arrow) and click the left mouse button. A thick black line will appear at the top of the Text box.
If the thick line does not show-up at the top of the Text box, click-again at the “top line area in the Text box” and the line will “disappear”. Then click-on the thick, single line in the Line Style box again and repeat the previous instructions. If, somehow, you make a mistake, simply click “on and off in the Text line boxes.” You will notice that the lines appear and disappear. This is called a “toggle” in computer “talk.” So, work at this until you get the line on the top of the cell. We have indicated that we want a single thick underline at the top of the cell C7. Point to OK and click the left mouse button.
When you return to the spreadsheet, click somewhere other than cell C7. This is called “clicking away.” You should now see a line at the top of cell C7. Sometimes the box highlighting a cell hides the lines. If you “messed-up”, try again.
Now type in the numbers in the cells indicated.
C11 30
C12 50
C13 150
C14 70 (After you type 70, tap the Enter key)
Now, underline the top of cell C15 like you did cell C7.
Your spreadsheet should now look like the image on the right.
If you happen to make a mistake
If you happen to make a mistake simply retype the entries. Later on we'll see how to
edit mistakes. Any time you want to replace something in a cell you can simply retype and the new entry and it will replace the old one.
Cell Type-in
A3 Income
B4 Parents
B5 Job
B6 Investments
B7 Total
A10 Expenses
B11 Food
B12 Beverages
B13 Parties
B14 Miscellaneous
B15 Total
Your spreadsheet should now look similar to the image on the right.
At this point you probably noticed, the words "Investments" and "Miscellaneous" run over the spaces given in the cells. Do not be concerned at this point. We’ll soon fix this.
Now, type the numbers in the cells indicated:
C4 300
C5 50
C6 150
When you type-in the 150, tap Enter.
Your spreadsheet should look like the image on the right.
Notice, when you enter text that the words line up on the left side of the cells. When you enter numbers, they line up on the right side. This is because we are using the United States (English) version of Excel. Other international versions will line up logically for their text and monetary forms.
We would like to place an underline at the bottom of the three figures so that we can indicate a total below – in cell C7. Point to cell C7 (with the mouse). That's where we want the line ‑‑ always move the cursor to the place where you want to insert a line. With the Arrow on cell C7 tap the right mouse button.
edit mistakes. Any time you want to replace something in a cell you can simply retype and the new entry and it will replace the old one.
Cell Type-in
A3 Income
B4 Parents
B5 Job
B6 Investments
B7 Total
A10 Expenses
B11 Food
B12 Beverages
B13 Parties
B14 Miscellaneous
B15 Total
Your spreadsheet should now look similar to the image on the right.
At this point you probably noticed, the words "Investments" and "Miscellaneous" run over the spaces given in the cells. Do not be concerned at this point. We’ll soon fix this.
Now, type the numbers in the cells indicated:
C4 300
C5 50
C6 150
When you type-in the 150, tap Enter.
Your spreadsheet should look like the image on the right.
Notice, when you enter text that the words line up on the left side of the cells. When you enter numbers, they line up on the right side. This is because we are using the United States (English) version of Excel. Other international versions will line up logically for their text and monetary forms.
We would like to place an underline at the bottom of the three figures so that we can indicate a total below – in cell C7. Point to cell C7 (with the mouse). That's where we want the line ‑‑ always move the cursor to the place where you want to insert a line. With the Arrow on cell C7 tap the right mouse button.
Sunday, July 19, 2009
Creating an Excel 2007 Spreadsheet
Starting Excel 2007
In the following exercises you will learn some of the necessary steps to create a spreadsheet using Microsoft Excel 2007. You will learn not only how to type various items into the spreadsheet, but also how to copy columns, widen columns, fill columns, add, subtract, multiply, divide, do graphics and a variety of other “things.”
To begin, load the spreadsheet by quickly clicking twice on the Excel 2007 Windows Icon in the Windows Desktop. If you do not see an Excel Icon, click the Start Button in the lower left corner of the screen, move the cursor up to Programs, then move to Microsoft Office. Move down to Microsoft Excel 2007 and click.
A spreadsheet is a “number manipulator.” To make the handling of numbers easier, all spreadsheets are organized into rows and columns. Your initial spreadsheet will look something like the one below:
In the following exercises you will learn some of the necessary steps to create a spreadsheet using Microsoft Excel 2007. You will learn not only how to type various items into the spreadsheet, but also how to copy columns, widen columns, fill columns, add, subtract, multiply, divide, do graphics and a variety of other “things.”
To begin, load the spreadsheet by quickly clicking twice on the Excel 2007 Windows Icon in the Windows Desktop. If you do not see an Excel Icon, click the Start Button in the lower left corner of the screen, move the cursor up to Programs, then move to Microsoft Office. Move down to Microsoft Excel 2007 and click.
A spreadsheet is a “number manipulator.” To make the handling of numbers easier, all spreadsheets are organized into rows and columns. Your initial spreadsheet will look something like the one below:
Notice that the “main” part of the spreadsheet is composed of Rows (Labeled 1, 2, 3, 4, etc.) and Columns (Labeled A, B, C, D, etc.). There are a lot of rows and columns in a spreadsheet. The “intersection” of each row and column is called a cell. In the image above the cursor is on the “home” cell – A1. Notice Row 1 and Column A are “bold,” and colored “orange.” This indicates what is called the “address of the cell. Notice right above cell A1, that A1 is displayed in a small box called the Name Box. Whenever you “click” on a cell the address of that cell will be shown in the Name Box.
If you have used previous versions of Microsoft Excel you will quickly notice that the above image is very different from what you are used to seeing. In Excel 2007 you will now use Tabs, Ribbons and Groups, as well as special Tabs/Ribbons. These replace the Menu Bar and Buttons in older versions. For an overview of 2007 Office, please see the Introduction to Microsoft Office 2007 Tutorial. This short tutorial introduces you to the many enhancements in the 2007 Office Suite.
Subscribe to:
Posts (Atom)