Excel assignment

EXCEL PRACTICAL EXERCISE – 3 Entering Formulas and Conditional Formatting

You are the manager for a group of Sales Reps and you’d like to check how

much money was raised by each representative based on their target number. NOTE: Percent amounts may be larger than 100%, but not too much larger Instructions:

1. Save the workbook as yourname_P3 2. Enter the data below into an Excel Spreadsheet. Be sure to keep the same columns

and rows. DO NOT ENTER THE ROW NUMBERS OR COLUMN LETTERS.

A B C

1 Name Target Achieved

2 Captain Hook 99314 98510

3 Jiminy Cricket 134573 119719

4 Snow White 148253 117652

5 Donald Duck 68647 31547

6 Queen Elsa 52000 69478

7 Aladdin 128336 133451

8 Mickey Mouse 125000 108117

9 Wendy Darllng 92954 98978

10 M. T. Lot 88000 83014

3. Enter the column title Percentage (%) at cell D1 4. Enter formulas (NOT functions) to calculate the percentage based on the target and

achieved amounts. a. Some percentages may be over 100%

5. Insert 2 blank rows above row 1 6. Enter the title at A1: Sales Reps – Annual Summary 7. Center the Title across the table 8. Format the Percentage column to show 2 decimal places 9. Format the table to your liking. Don’t just enter data 10. Change the page orientation to Landscape 11. Change the TAB name to Sales Representatives 12. Color the tab blue

CONDITIONAL FORMATTING 13. Apply conditional formatting

a. If the percentage is more than 100% color the cells green with a white colored bold font

CHARTING

14. Create a 3-D Column Chart showing the percentages by each sales representative 15. Move the Graph to a new sheet

a. Call the new sheet Annual Percent b. Add a title to the Chart

i. You create one c. Add Axis Titles to both Axis’ d. Color the TAB RED

Continued on next page

FINISHING TOUCHES

16.Add your name and student ID number to the header

17.Change the document properties - Author, Subject and TAGS (keywords – use a minimum of 3 words

separated by commas). o Tags should NOT be your initials, Excel, exercise, …