Lesson 1: Making a markbook

In this lesson, you will make use of formatting toolsformulas functions to create a class markbook in Excel, and create charts based upon the data you enter.

Download lesson files

Knowledge check

If you’re up for a challenge, here’s an expert’s guide with minimal guidance. Good luck!

If you’d like a little more help, here’s a booklet of step-by-step instructions.

If you feel like you might need a little more help, keep reading! The task instructions below are supported by video tutorials to help you get to grips with Excel.

Instructions

1. Before we start, let’s put the class in alphabetical order by:

  • Selecting the range A6:G19
  • Go to the Data tab & choose Filter
  • Click the dropdown on the Surname column
  • Choose Sort A to Z

2. Make columns A:C 13.00 width, and columns D:I 10.00 width.

3. Format your spreadsheet so that it exactly matches the screenshot provided below.

  • Merge & center cells A1:C3 (Merge & Center can be found under the Home tab in the alignment box)
  • Make the title 32pt bold, left-aligned
  • Apply gridlines around all sections of the table
  • Make headings bold & fill heading cells light grey

4. Insert four columns to make room for the percentage scores from each test.

  • Merge & center the column headings
  • Add a % symbol in the subheadings for each column & center

5. In the % columns for each test, we want to work out the percentages. To do this, type =D7/D$20.

  • Putting a dollar sign in front of the row number tells Excel not to move down when we replicate this formula. This is an absolute cell reference.
  • Format the contents of this column as percentage.
  • Replicate this formula down the column and copy & paste it into the other % columns you just made.

6. Apply conditional formatting to the percentage columns for each of the four tests. You can find conditional formatting by clicking on Home and looking in the Styles box.

7. In cell L7, calculate the average of the first student’s test scores by typing =AVERAGE(E7,G7,I7,K7) or click on the cells while holding control once you have opened your brackets. Replicate this formula down the column.

Find out how in video tutorial: basic functions.

8. In cell M7, write an IF statement that displays “Pass” if the student’s average is 50% or above, or “Fail” if not.

  • The syntax for this is =IF(L7>=0.5,”Pass”,”Fail”) 

Find out how in video tutorial: IF statements.

9. Select cells D20, F20, H20 & J20. Make the contents bold & apply gridlines around these cells

10. Shannon Moynihan was a late arrival to this class, and missed the first two tests. Change the average function so it only includes her result in Test 3 & the Final Test.

11. Banele Aristides hasn’t yet done the final test. You are going to use Goal seek to find out the score he has to get in this test in order to pass.

  • Click on Banele’s average % (this should be cell L7 if you followed step 3).
  • On the ribbon at the top of the screen, click Data > What If Analysis > Goal seek
  • Set cell: L7
  • To value: 0.5 (for 50%)
  • By changing cell: J7

Find out how in video tutorial: Goal Seek.

12. We’re now going to add some class info at the bottom of the spreadsheet. Starting in cell B22, add the following to your spreadsheet:

13. In cells B23 & B24, we want the total number of girls & boys in the class. Use the COUNTIF function to calculate this, referring to your knowledge organizer to see how.

Find out how in video tutorial: conditional functions.

14. In cell B26, we want a class average percentage. Just use a simple AVERAGE function to calculate this.

15. In cells C23 & C24, we want to calculate the averages of girls & boys. To do this, use the AVERAGEIF function, again referring to your knowledge organiser or the tutorial above to help.

16. We’re going to make some charts. For both of the charts below, remember the key components of a chart from your knowledge organiser.

Find out how in video tutorial: charts and graphs.

17. Create a pie chart at the bottom of the page showing the class’s gender breakdown.

18. Create a line chart showing Banele’s progress across the four weeks.

19. Make the entire spreadsheet fit perfectly on to one landscape A4 page.

Your spreadsheet should now look like this:

Finished?

Swap with a partner and peer review each other’s work. Check:

  • Do your results match?
  • Do your markbooks look identical?
  • Do you see any differences?
  • Do your graphs have every component from the knowledge organiser?