Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Numbers > Design > Ex. Excel 5-2
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Numbers

    Design: Exercise Excel 5-2

Exercise Excel 5-2You will use the IF function and conditional formatting to improve the budget sheet for City Soccer League.

 

 

 

 

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.

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: Icon: Class disk, ex3-2-soccerbudget-Lastname-Firstname.xlsx (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. Icon: Class disk  Open ex3-2-soccerbudget-Lastname-Firstname.xlsx from the excel project3 folder on your Class disk. Select the sheet Soccer Budget.
  2. Icon: Class disk Save as  ex5-2-soccerbudget-Lastname-Firstname.xlsx  to the excel project5 folder on your Class disk.

  3. Man kicking soccer ballInsert and format image: Insert into cell A1 the clip art image you used in Working with Words Project 2, the man kicking a soccer ball.

    [Search with the term 'soccer'. 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. The picture should have a transparent background.

  4. Format: Change the fill color for the category labels to Dark Blue.
  5. 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.
  6. Conditional formatting: In the first calculated cell in column F, create a conditional formatting  rule for 'Format only cells that contain..." if the cell value is equal to "Equal". [Type the word in the text box without the quotes.] Format with Font Style = Italics, Font Color = Dark Blue.

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

  7. Conditional formatting- more conditions: For the Income total cell in column F, add two more conditional formatting rules.
    Condition 2 - if the cell value is greater than zero, create a custom format with gray fill (3rd row, 1st column) and bold dark blue font.
    Condition 3 - if the cell value is less than zero, create a custom format with gray fill and bold red font.

    Copy this new conditional formatting to the other totals, for Expenses and Grand Total in column F.

  8. Repair formatting: If the totals cells lost formatting, 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.
  9. Results of exercise: City Soccer League BudgetIF function: Forthe cell in column F below the Grand Total, create an IF statement that checks to see if the grand total is greater than zero. If it is, your budget is in good shape so display the phrase "Great!" in the cell. If the grand total is not greater than zero, display the phrase "Need more $$!"
  10. Format: Format cell F25 as Bold Italic and centered.
  11. 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 margins until it does fit. Print Preview.
  12. Icon: Class disk Save.
    [ex5-2-soccerbudget-Lastname-Firstname.xlsx]
  13. Print  Print the sheet on one page.