Excel assignment
EXCEL PRACTICAL EXERCISE 3
Entering Formulas and Conditional Formatting
You are the manager for a group of Sales Reps and youd 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. Dont 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,
