# Design: Exercise Excel 5-2

Did you want Working with Numbers: 2007,2010,2013,2016  or español

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.
How to handle a full disk

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 4: Groups & Formulas

Search
Glossary

Appendix

## Soccer Budget - IF function

 What you will do: insert an image apply conditional formatting copy conditional formatting create IF functions

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.   Open soccer budget3.xls from the excel project3 folder on your Class disk. Select the sheet Soccer Budget.

2.  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.  Save.

12.   Print the sheet on one page.