Design:
Exercise Excel 5-2

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

You need to use what you just learned, and maybe learn a little more. Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

This exercise uses files from Project 3. Save the changed documents to your Class disk in the excel project5 folder. This keeps the original files intact in case you need to start over.
Full floppy disk How to handle a full disk

 


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics   

Project 4: Groups & FormulasTo subtopics

Project 5: Design
    Analysis To subtopics
    What If...To subtopics
    Logical TestsTo subtopics
    Sharing DataTo subtopics
    Summary
    Quiz
    Exercises To subtopics
    Exercise Ex. 1 Theater Tickets
    Exercise Ex. 2 Soccer - IF
    Exercise Ex. 3 Soccer - What If
    Exercise Ex. 4 Computers Today
    Exercise Ex. 5 Ticket subtotals
    Exercise Ex. 6 On Your Own

Search 
Glossary  
Appendix

Exercise Excel 5-2:

Soccer Budget - IF function
What you will do: insert an image
apply conditional formatting
copy conditional formatting
create IF functions

Start with: Class disk, soccer budget3.xls (created in previous exercise)

Using the IF function, you will show the word equal when the Budget and Actual amounts are the same and you will format it differently from the values. You will also add an image to perk things up.

  1. Class disk  Open soccer budget3.xls from the excel project3 folder on your Class disk. Select the sheet Soccer Budget.
     
  2. Class disk Save as  soccer budget5.xls  to the excel project5 folder on your Class disk.
     
  3. Insert & format image: Insert into cell A1 the clipart image you used in Working with Words Project 2, the man kicking a soccer ball. [It's in the Clipart Gallery under Sports & Leisure. If this image is not available to you, pick some other image related to soccer.]. Size the image to fit on the left of the sheet titles, above the category row. Format the picture with no lines and no fill so it will have a transparent background. [Format Picture]
     
  4. IF function: In cell F6 enter an IF formula that checks to see if Actual-Budget is zero. If it is, show the word equal. Otherwise show the value of Actual-Budget. Copy the formula to the other calculated cells in this column. [AutoFill down the column and then delete the contents of cells that should be blank.] You will lose some formatting for cells that are totals. Do not fix this yet.
     
  5. Conditional formatting: In cell F6 create conditional formatting if the cell contents is equal to "equal". [Type the word in the text box without the quotes.] Format with Font Style = Italics and Font Color = Dark Blue

    Copy this formatting with Format Painter to all the cells that use the IF function in the step above.
     

  6. Conditional formatting- more conditions: For cell F9 modify the conditional format to include 2 more conditions. Condition 2 - if the cell value is greater than zero, format with light gray fill and bold green font. Condition 3 - if the cell value is less than zero, format with light gray fill and bold red font.

    Copy this new conditional formatting to the totals in F23 and F24.
     

  7. Repair formatting: For the totals cells that lost formatting previously, reapply the Totals style with the Style box. (Warning: If you use Format Painter to copy the formatting from another column, you will lose the conditional formatting you just applied.) Reapply the border for Row 24.
     
  8. IF function: In cell G24, create a formula that looks to see if the grand total in cell F24 is greater than zero. [Use E24-D24>0 as the test.] If it is, your budget is in good shape so display the phrase "Money left over!" in the cell. If the grand total is not greater than zero, display the phrase "Not enough $$!"
     
  9. Format: Format cell G24 as Bold Italic.
     
  10. Prepare to print: Edit the header to include Exercise Excel 5-2 on the right. Set the sheet to print in Portrait orientation at 100% scaling. Check Page Break Preview. If the sheet does not fit on one page, make changes to the sheet until it does fit. [Reduce column widths or decrease margin size.] Print Preview.
     
  11. Class disk Save.
     
  12. Print  Print the sheet on one page.

Results of exercise: City Soccer League Budget