Cts130 spreadsheet
This presentation is the property of its rightful owner.
Sponsored Links
1 / 30

CTS130 Spreadsheet PowerPoint PPT Presentation


  • 63 Views
  • Uploaded on
  • Presentation posted in: General

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.

Download Presentation

CTS130 Spreadsheet

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Cts130 spreadsheet

CTS130 Spreadsheet

Lesson 5

Working with Simple Formulas


Using a template to create a workbook

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.

  • A template is useful as the basis for a workbook that is the same each week or month – but with different data.

  • Templates:

    • are automatically saved with an .xlt filename extension. (a spreadsheet is .xls)

    • are automatically saved in a “Templates” folder on your computer.

    • must be in the Templates folder if you want the templates to be listed in the Templates dialog box.


How to locate template files

How to Locate template files…

Step 1

Step 2

Step 3

Note the yellow section behind the icon image.


Template file this is how a template appears in a list of files on a disk

Template File - This is how a template appears in a list of files on a disk.

Again, note the yellow section behind the icon image.


Templates cont d

Templates (cont’d)

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.

TASKPANE


Templates in office online

Templates in OFFICE ONLINE


Templates cont d1

Templates (cont’d)

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.


Example expense statement template

Example:Expense Statement template


Example loan amortization template

Example:Loan Amortization Template


Building addition and subtraction formulas

Building Addition and Subtraction Formulas

  • Addition formulas total or sum using the plus sign ( + )

    =B10+B25+B30

  • Subtraction formulas compute the difference between cells using the minus sign ( - )

    =D44-A6-B10


Building multiplication and division formulas

Building Multiplication and Division Formulas

  • Multiplication formulas use the asterisk ( * )

    =B10*B25

  • Division formulas use the slash ( / )

    =B12/B6


Using the order of precedence in a formula

Using the Order of Precedence in a Formula

NOTES:

Parentheses override everything

You must have sets of parentheses.


Example

Example:

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


To enter the edit mode

To enter the EDIT Mode

  • Double click the cell

  • Click the function key [F2]

  • Click in the Formula Bar

Note that the X and the checkmark appear when you click in the Formula Bar.


Working with percentages

Working with Percentages

The following values are the same value for the value .07

0.7*100

70%


Using relative references

A1

Using Relative References

  • When you copy a formula, Excel adjusts the formula relative to the row or column where the copy is located.

  • This is known as a relative reference and is most often exactly what you want Excel to do.

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.


Using relative references examples

When copying vertically (down) - the row numbers changed.

When copying horizontally (across) - the column letter changed.

A changes to B; B to C, etc.

Using Relative References - Examples:


Example of relative copying

Example of RELATIVE copying:

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.


Example of relative copying1

Example of RELATIVE copying:

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.


Using absolute references

Using Absolute References

  • There are situations when you want EXCEL to copy the row number or column letter in the formula exactly the way it appears.

  • A cell with an absolute reference “absolutely” does not change when a formula is copied vertically or horizontally.

  • Absolute references use the dollar sign in front of each cell that you do not want to change.

    Example: =$B$3*C25

If you copied this formula vertically or horizontally, the B and the 3 would not change.


Absolute referencing cont d

Absolute Referencing (cont’d)

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)


Absolute referencing cont d1

Absolute Referencing (cont’d)

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)


Example 1 see handout

Example #1 (see handout)


Cts130 spreadsheet

Example #2

Note that the “4” is anchored in each formula so that it will not change when copied.


Changing page orientation

Changing Page Orientation

  • Page orientation is a print setting that determines if the worksheet prints in landscape or portrait. The default is portrait orientation (taller than it is wider.)

  • Many worksheets are too wide to fit in a portrait orientation. A landscape orientation (wider than tall) is necessary.

  • File Menu > Page Setup > Landscape

    “OR”

    In the Print Preview window, click the Setup button to view the Page Setup dialog box.


Change scaling

Change scaling

  • The scaling command enables you to set a size percentage for the printed page.

  • File Menu > Page Setup > Page tab


Change margins

Change Margins

File Menu > Page Setup > Margins tab

Remember: You can also do this in the Print Preview Window.


Formulas view

Formulas View

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


Worksheet with formulas showing

Worksheet with Formulas Showing

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.


Cts130 spreadsheet

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:

  • RIGHT-click on the tab of the original worksheet.

  • Select the “Move or Copy” option.

  • Click in the “Create a copy” box.

  • Click the “Move to end” option.

  • RIGHT-click the copy you just created and add the text “formula” – or something similar.

Now you have two copies of the worksheet – one with answers and one with formulas/functions.


  • Login