EXCEL PRACTICAL EXERCISE – 4

Entering Formulas, IF functions and Conditional Formatting

Instructions:

• Save the workbook as yourname_P4
• Enter the data below into an Excel Spreadsheet.Be sure to keep the same columns and rows.
• Enter the column title Penalty at cell E1
• Insert 1 blank rows above row 1
• Enter the title at A1:Player Penalties
• Center the Title across the table
• Enter formulas to calculate the Penalty
  • Using the Actions below the table, use the IF Function and use Absolute Cell Reference to get the penalty amounts
    • If the player has 30 or more yellow cards, the penalty is 13% of the salary
    • If the player has 10 or more yellow cards, the penalty is 3.2% of the salary
    • If the player has less than 10 yellow cards, there is no penalty
• Format the table appropriately
• Format the table to your liking.Don’t just enter data
• Change the page orientation to Landscape
• Change the TAB name toPlayers and color it BLUE
• Use conditional formatting to show which of the players has more than 5 RED cards
  • Format the cells with BOLD White letters and a RED background.
• Create a 3-D Column Chart showing the Player and Penalty received
• Move the Graph to a new sheet
  • Call the sheet:Penalties and color it RED
  • Add a title to the Chart
  • Add Axis Titles to both Axis’
• Complete the Document Properties with your name, subject and TAGS (Keywords – use a minimum of 3 – separate with commas)

GRAPH