mad.learn

Microsoft Excel

Spreadsheets Level 2

The Basics.

New to spreadsheets?   Watch this introduction video to get started.

Create your free Office account. 

We will start by creating a free office account.   This will give you access to Microsofts Online Office Tools.  If you already have an account or Office software installed on your computer, you can use this instead.  

Certification Tasks

This course is certified.  If you wish to obtain a Level 2 Agored Qualification then it’s important to save your work for each Task as you will be required to submit it and run tasks on it for submitting as evidence. 

Task 1

  1. Create a folder on OneDrive called Spreadsheet Techniques
  2. Create a new spread sheet and fill it with the data as bellow

SAVE YOUR WORK:  FILE > SAVE AS > TASK 7

Task 2

  1. Change all fonts to Gill Sans MT,  Size 12
  2. Make the column headings Bold and Blue
  3. Format the current empty cells as Curreny.  
  4. Add (Tonnes) after Recycled Waste and Landfill Waste headings. 
  5. Change Unitary Authoriy names to Italic and change the background colour to light grey. 
  6. Centre align the headings for Recycled Waste (Tonnes), Landfill Waste (Tonnes) and Total Waste (Tonnes)

SAVE YOUR WORK:  FILE > SAVE AS > TASK 2

Task 3

  1. Insert two rows at the top of the spredsheet and merge to one large cell.
  2. Enter the title Unitary Authority Waste Management Information
  3. Copy the format of the Unitary Authority column to the title.
  4. Center the title.
  5. Change the page orientation to landscape
  6. Apply borders around each cell of the data table (not the spreadsheet title)

SAVE YOUR WORK:  FILE > SAVE AS > TASK 3

Task 4+5

  1. Enter a suitable formula (not the SUM function) to calculate the Total Waste for Ceredigion
  2. Replicate the formula for each Unitary Authority
  3. Under the ‘Cardiff’ cell (Column A) enter the text Totals (for the row title)
  4. Use the SUM function to calculate the total Recycled Waste (Tonnes) of the Unitary Authorities
  5. Replicate the SUM function for the totals of Landfill Waste (Tonnes) and Total Waste (Tonnes)
  6. Add borders around this new row of data
  7. Delete the entire row and all the data relating to Torfaen

SAVE YOUR WORK:  FILE > SAVE AS > TASK 4+5

Task 6

  1. Insert a new column after Total Waste (Tonnes) and name it Percentage of Waste Recycled 
  2. Copy the format of the Total Waste (Tonnes) heading to Percentage of Waste Recycled 
  3. Enter a suitable formula to calculate the Percentage of Waste Recycled for Ceredigion (the formula is constructed by dividing RECYCLED WASTE by TOTAL WASTE) 
  4. Replicate the formula for each Unitary Authority 
  5. Format the numerical calculations in Percentage of Waste Recycled to percentage format 
  6. Enable the Show Formulas option

SAVE YOUR WORK:  FILE > SAVE AS > TASK 6

Task 7

    1. The Landfill Charge for each Unitary Authority is £90 per tonne
    2. Write a formula to calculate the landfill charge for Ceredigion
    3. Replicate for the other Unitary Authorities
    4. Print out the spreadsheet; ensure you sign and date the print and attach it to the back of this Booklet as TASK 7

SAVE YOUR WORK:  FILE > SAVE AS > TASK 7

Task 8

  1.  In cell G2 enter the decimal 0.2 
  2. hange the numeric format of G2 to percentage 
  3. Write a formula to calculate the VAT charge at 20% on Landfill Charge for Ceredigion 
  4. Within the Ceredigion VAT charge calculation cell, make the cell reference of the VAT rate (G2) within the formula ABSOLUTE 
  5. Now replicate the formula for the other Unitary Authorities

SAVE YOUR WORK:  FILE > SAVE AS > TASK 8

Task 9

  1. Calculate the Total Landfill Charge for Ceredigion and replicate for each Unitary Authority 
  2. Within the Total Landfill Charge for Ceredigion cell, apply fill colour Yellow 
  3. Copy the format (yellow fill) to the rest of the Unitary Authority Total Landfill Charge cells 
  4. Save the spreadsheet as Waste Management 4 into your folder
  5. Print the file showing all formulas.

SAVE YOUR WORK:  FILE > SAVE AS > TASK 9

Task 10

  1. Leave a blank row beneath the Totals row then in the next row, in column A, enter the text Average
  2. Use the AVERAGE function to find the average of Recycled Waste (Tonnes) – Ceredigion to Cardiff (remember to exclude the Totals row and the blank row) 
  3. Format the average calculation to 2 decimal place 
  4. Replicate for Landfill Waste (Tonnes) and Total Waste (Tonnes) 
  5. Underneath Average in column A enter the text Count 
  6. Use the COUNT function to count the number of Unitary Authorities (Ceredigion to Cardiff) 
  7. Underneath Count in column A enter the text Maxim
  8. In the Maximum row, Column C, use the MAX function to identify the highest Landfill Waste (Tonnes) – Ceredigion to Cardiff
  9. Underneath Maximum in column A enter the text Minimum
  10. In the Minimum row, Column C, use the MIN function to identify the Unitary Authority with the lowest Landfill Waste (Tonnes) – Ceredigion to Cardiff
  11. Underneath Minimum in column A enter the text Max/Min difference
  12. In the cell below the minimum Landfill Waste (Tonnes) calculation, write a formula to subtract the lowest Landfill Waste (Tonnes) from the highest Landfill Waste (Tonnes)

SAVE YOUR WORK:  FILE > SAVE AS > TASK 10

Task 11

  1. Insert a column after Landfill Waste (Tonnes) 
  2. Enter the following column heading: 
  3. Over or under average Total Landfill Waste (Tonnes) 
  4. Copy the format of the Landfill Waste (Tonnes) heading 
  5. For Ceredigion, create an IF function to identify if the Authority is “OVER” or “UNDER” the average Total Landfill Waste (Tonnes) 
  6. Within the IF function for Ceredigion, make the cell address for the Average Landfill Waste ABSOLUTE
  7. Replicate the IF function for the rest of the Unitary Authorities 
  8. The column should now correctly identify which Unitary Authorities are OVER or UNDER the Average Landfill Waste (Tonnes) – check the results are correct 
  9. Add All Borders to this column of data 
  10. Create a LOOKUP function to return the Landfill Waste (Tonnes) in cell K5 when you enter the Unitary Authority in cell K4

SAVE YOUR WORK:  FILE > SAVE AS > TASK 11

Task 12+13

  1. Insert a column after Total Landfill Charge
  2. Enter the following column heading: 
  3. Target
  4. Copy the format of the Total Landfill Charge heading 
  5. For Ceredigion, create an IF/AND function to identify if the Authority is reaching the following targets: 
  6. Recycled 20% of Waste
  7. Earned more than £2,500,000 in Total Landfill Charges
  8. The percentage may need to be expressed as a decimal in the formula
  9.  Delete the column titled Over or Under average Total Landfill Waste (Tonnes)

SAVE YOUR WORK:  FILE > SAVE AS > TASK 12+13

Task 14.

  1. Create a 2-D Pie chart for the Recycled Waste (Tonnes) for each Unitary Authority
  2. Move the Pie chart to a New sheet named Pie chart
  3. Change the colour of the highest Recycled Waste (Tonnes) slice to a pale green and explode the slice by 10%
  4. Add Data Labels, Best Fit to each slice of the Pie chart
  5. Create a 2-D Column chart showing each Unitary Authority’s name, Recycled Waste and Landfill Waste
  6. Move the Column chart to a New sheet named Column chart
  7. Change the colour of the three highest Landfill Waste bars to Dark Red
  8. Create a Line graph showing each Unitary Authority’s name, Recycled Waste and Landfill Waste
  9. Move the Line graph to a New sheet named Line graph
  10. Change the axis scale to begin at 1,000 and end at 71,000
  11. Change the Minor scale Units to 1,000

Thats it!.  You just have to do a little organising of your saved files before submitting