CTS130 Spreadsheet. Lesson 5 Working with Simple Formulas. Using a Template to Create a Workbook. A template is a model or sample workbook that can include font types and styles, alignment settings, borders, labels, values, and formulas.
Working with Simple Formulas
Note the yellow section behind the icon image.
Again, note the yellow section behind the icon image.
You can use a template as the model for a workbook by choosing ON MY COMPUTER in the TEMPLATES section of New Workbook task pane.
You can use a template as the model for a workbook by choosing the template name in the RECENTLY USED TEMPLATES section of the NEW WORKBOOK task pane.
Again: Templates must be in the TEMPLATES folder if you want the templates to be listed in the Templates dialog box.
Otherwise, you can use the OPEN option and locate the template file on a disk, a CD, etc.
Parentheses override everything
You must have sets of parentheses.
To add three scores together and then find the average, the following formula would NOT work because S3 would be divided by 3 - then the result of that operation would be added to scores S1 and S2.
=S1 + S2 + S3 / 3
The correct formula would be:
=(S1 + S2 + S3) / 3
Note that the X and the checkmark appear when you click in the Formula Bar.
The following values are the same value for the value .07
If you copy this cell horizontally, the column letter will change to B to C to D, etc.
What changes – either the column letter or row number – is relative to the direction you copy.
If you copy this cell vertically, the row number will change to 2 to 3 to 4, etc.
When copying horizontally (across) - the column letter changed.
A changes to B; B to C, etc.Using Relative References - Examples:
In the formulas, the 2 changes to 3, which changes to 4, which changes to 5, which changes to 6.
Since we are copying DOWN, the ROWnumber changes.
In the formulas, the B changes to C, which changes to D, which changes to E, which changes to F.
Since we are copying ACROSS, the COLUMN letter changes.
If you copied this formula vertically or horizontally, the B and the 3 would not change.
To add the anchor ($) you can simply click in the
function or formula in front of the cell address and key the $(s) yourself; or….
You can also use the F4 function key:
The first time you click F4 it will anchor both the row and column =sum($B$4:B25)
The second time you click F4, it will anchor the row =sum(B$4)
The third time you click F4, it will anchor the column =sum($B4)
The fourth time you click F4, it will return back to its original state =sum(B4)
You do not have to “anchor” both items in a cell.
You can anchor only the row number
=A$4*B25 (only the “4” is anchored)
=C30/N$21 (only the “21” is anchored)
You can anchor only the column letter
=$F34+G88 (only the “F” is anchored)
=$E3-A3 (only the “E” is anchored)
Note that the “4” is anchored in each formula so that it will not change when copied.
In the Print Preview window, click the Setup button to view the Page Setup dialog box.
File Menu > Page Setup > Margins tab
Remember: You can also do this in the Print Preview Window.
You can view the formulas and functions within your worksheet all at once instead of checking the formula bar cell by cell.
TOOLS MENU > OPTIONS > FORMULAS
The actual worksheet will change in view. Note the change in column A where A1 and A2 are truncated. That’s OK when you are simply printing the worksheet to turn in to instructor. As long as the formulas are showing, that’s fine.
The formulas display instead of the answers to the formulas.
In order to keep the original version of your worksheet with the answers showing, you can make a copy of it and show the formulas on the copy. This will leave the original copy showing the answers to the formulas.
To do this:
Now you have two copies of the worksheet – one with answers and one with formulas/functions.