Contents

# Lesson 1: Making a markbook

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

## Download lesson files

**Markbook.xlsx**(7.3kb)

## 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?