Excel exercises

EXCEL PRACTICAL EXERCISE

Entering Formulas, IF functions and Conditional Formatting

Instructions:

1. Save the workbook as yourname_P5_

2. Enter the data below into an Excel Spreadsheet. Be sure to keep the same columns and rows.

	A	B	C	D
1	Player	Salary	Yellow Cards	Red Cards
2	Wayne Rooney	85000	9	1
3	Robin van Persie	87500	30	0
4	Lionel Messi	92300	11	4
5	Cristiano Ronaldo	98600	16	5
6	Fernando Torres	74500	13	3
7	Gareth Bale	38000	48	0
8	David Silva	46400	19	8
9	Frank Lampard	64500	28	2
10	Carlos Tevez	78300	33	19
11	Didier Drogba	66350	17	2
12
13	Action
14	Yellow Cards >= 30	13%
15	Yellow Cards >= 10	3.2%
16	Yellow Cards < 10	0%

3. Enter the column title Penalty at cell E1

4. Insert 1 blank rows above row 1

5. Enter the title at A1: Player Penalties

6. Center the Title across the table

7. Enter formulas to calculate the Penalty

a. Using the Actions below the table, use the IF Function and use Absolute Cell Reference to get the penalty amounts

i. If the player has 30 or more yellow cards, the penalty is 13% of the salary

ii. If the player has 10 or more yellow cards, the penalty is 3.2% of the salary

iii. If the player has less than 10 yellow cards, there is no penalty

8. Format the table appropriately

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 Players and color it BLUE

12. Use conditional formatting to show which of the players has more than 5 RED cards

a. Format the cells with BOLD White letters and a RED background.

GRAPH

13. Create a 3-D Column Chart showing the Player and Penalty received

14. Move the Graph to a new sheet

a. Call the sheet: Penalties and color it RED

b. Add a title to the Chart

c. Add Axis Titles to both Axis’

15. Complete the Document Properties with your name, subject and TAGS (Keywords – use a minimum of 3 – separate with commas)