Getting to grips with Excel – from the basics up to useful functions for creating tracking sheets.
Data trackers and Microsoft Excel can provide a lot of people with anxiety, and some people give up at the first hurdle. I am by no means a mathematical person (I leave that to my brother!) but at my current workplace, I’ve had to really engage with Excel as part of my role. It is something I’m very thankful for and I feel a lot more confident in its workings. In this blog, I will take you from the very basics to some useful functions should you wish to start setting up a tracking sheet.
Cells, rows and columns
These are something that you will hear a lot about in Excel.
A cell is a single rectangle shape in Excel. You can type into these and insert a formula to carry out a calculation for you.
A row in Excel is all of the cells in a line going along in Excel. Each row is a number in Excel. For instance, all of the cells in row 2 are all in a straight line along from the number 2 at the side.
Conversely, a column is a line of cells running up and down. These are lettered A-Z. Once the alphabet completes itself, the cells then start with AA, AB, AC and so on.
From rows and columns, you can create a cell reference by looking at the row and column on which a cell is located. The column (letter) is stated first, followed by the row (number). For instance, the cell highlighted would be cell B4. These ‘grid’ references are important later on when dealing with creating your own formulae.
The importance of = when writing formulae
Before we get going, for any formula (or you might want to think of it as a calculation) to work in Excel, you must start your formula with the = sign. This tells Excel that you want to want the formula in that cell to carry out a task. If you simply put in 1+2 Excel will treat this as text and it won’t carry out its mathematical magic.
The four operations
The first thing that is useful to do is to be able to the basic operations of addition, subtraction, division and multiplication.
The first thing you need to know is that some of the symbols for the four operations are different:
To start your basic formula (calculation), always start with an =. This tells Excel you want this cell to carry out a calculation. After this, it is simply a case of typing in your calculation, remembering to use * for multiplication, and / for divide and not to leave any spaces – write the whole formula out together.
Example: to get Excel to work out 3×2:
The four operations using cell references
If you are creating a marksheet, typing out the actual calculation can be long and defeats the ease that Excel can create when used well. If you input some data in excel, you can very easily create a formula for the four operations. All you need to do is decide where you want the answer to appear. In that cell, start with the = sign. Then click the cell of the first number you want in your calculation. This will bring a cell reference up in your formula bar (at the top), such as =B4. To this, add the sign of the operation that you want to carry out, then simply click the cell of the second number you need to complete your calculation. Then press enter.
Example: multiply cell C4 by D4:
When working with cell references this way, you will notice if you click on the cell afterwards, the references will show up in different colours. These colours will also appear on the sheet, to show you where the cells are that you have put into your formula:
The good thing about this is, should the data change (say a child scores higher or lower in an exam) – the result formula will automatically carry out the calculation for you, meaning you have to do nothing to the formula.
Adding multiple cells in a row or a column
This is a great trick. Rather than clicking on each cell as before and having to type in + between each cell reference (=C3+D3+E3); there is an easier way to get excel to add several cells in a row for you. Start your formula with =, followed SUM in capital letters and the opening of a set of brackets. This tells Excel you want it to tally several cells in one go. After the open bracket, insert the cell reference of the first cell you want to add, followed by : finishing with the final cell reference of the sequence you want to add. Round off by closing the brackets and pressing enter.
Example: add cells C3, D3, E3 together:
If you want to make this even quicker, after typing =SUM( – you can click and drag from the first cell to the last cell you want to add. This saves you having to type the cell reference out for the end cell. Again, like before, because we are using cell references, any changes in data will be calculated when they are inputted – meaning you don’t have to change anything.
It is very similar to the sum formula.
Click on the first cell you want to include in the average and drag to the last. Press enter and it will calculate your average.
Example: work out the average for C2 to C12:
Replication is a way of copying down a formula quickly. If we added cells C3, D3 and E3 together and I wanted to do the same operation for the row below, I could click on the cell where the formula is that counts up C3, D3 and E3, and a the cell would come up with a thick boarder around it and a little square in the bottom right corner of the boarder. If you click on the little square and drag down, it will repeat the formula for the row of data below. This time, it would add up C4, D4 and E4!
Filters allow you to search for specific criteria and only display the results that match – for instance, you might want to only see the data for your class. To do this, click on the number of the row where the tiles are for the data sets, click on sort and filter, followed by filter. This will bring up some dropdown arrows. Simply click on the squares of the data you want to see and it will bring this up for you.
Merging cells allows you to merge two cells into one – which is very useful for when you need to create titles. To do this: highlight the two or three cells you want to merge, right click and click on merge cells. If you use Excel on Mac, an option on the home tab will say ‘merge and centre’ – click this and it will merge the cell for you.
Formatting cells for decimal points, currency and getting rid of decimal points
Sometimes, especially if you apply a scaling factor, your results will come up as decimal points, when you need to report a whole number; conversely you might want to do the opposite. To do this, simply highlight the results you want to change format (from decimal values to whole numbers; or from whole numbers to display the decimal point), right click and select format cell.
This will bring you a range of options. For decimal values (or to get rid of them), click number, and use the arrows to get to the result you want. If you want whole numbers, go to 0. This will automatically round up or down any decimal values for you.
For currency or time, simply select the relevant option and the format (the option) in which you want your results to appear.
Creating a new sheet
In your ‘workbook’ (the file), you might want to create another ‘sheet’ (or page) for another class. To do this, simply go to the very bottom where it says ‘sheet 1’. There will be a + icon – click on this and another sheet will appear.
To give the sheet a name, for example a class name, right click where it says ‘sheet 1’ and then click on ‘rename’. Then, you can give the sheet any name you wish.
USEFUL FUNCTIONS FOR CREATING A TRACKING SHEET
Building on from filters, custom sorting allows you to arrange your data in a specific way. To do, highlight the data and the titles that you want to sort out and then click on sort and filter, followed by custom sort.
It will say ‘sort by’ and underneath column, you will see dropdown options for each of the titles of your columns. You can then sort by different layers.
If creating a tracker, I would sort by class first, so that your classes appear together, then sort by name, so that the names and data are arranged firstly in class, and then in alphabetical order within the class.
It is really important you highlight all data and the titles, because if you miss some columns off, the data will not be rearranged with the student, mixing up scores and grades.
Replication of formula, keeping one element the same
Sometimes you might want to replicate some elements of a formula, for instance a paper mark; but you might need to keep one element the same. For instance, you might want to multiply the total sum in cells F3, F4, F5 by cell H3, which is a scale factor of a paper. To do this, when doing looking at the formula, you will initially have something like this:
If you replicate this, the formula will automatically change to:
=F4*H4 – this will not include the right scale factor and will look at the cell below that of the cell that contains the scale factor and will keep moving down. To stop this, you need to add $ signs before the letter and the number of the cell reference you want to keep the same, giving you:
Conditional formatting is incredibly useful as it allows you to see when values meet a certain condition. A good example might be in a test, if you set a pass mark of 8 out of 10 marks, you can set conditional formatting to highlight any students that do not achieve at least 8 marks.
To do so, first, highlight the row or column in which the data you want to be scrutinised is placed. On the ‘home’ tab, click on ‘conditional formatting’. Next, click on ‘highlight cell rules’. This will give you a range of options, such as ‘greater than’, ‘equal to’, ‘less than’ and so on.
To highlight students who have achieved less than 8 marks, select ‘less than’. When you go along the options, in the value column, simply enter the number ‘8’. You can edit the colour the cell turns, but for less than I usually stick with red. Click on ‘ok’ and this rule will then be applied so any number 7 or less will turn red in the set of cells which you highlighted.
For greater than, repeat the same process, albeit selecting greater than as your option.
If you want to format the cells to include 8 in the pass marks, rather than selecting ‘greater than’ – click the drop down arrow and click ‘equal to or greater than’. This will then tell Excel to colour any cells in the range we selected containing 8 as well as those above 8 in the pass colour.
To do more than one row or column at a time, hold down CTRL whilst you select and the same rule will be applied to all the selected cells.
To change any of the highlighting rules – select the cells that contain the formatting that you want to edit, click on ‘conditional formatting’ – followed by ‘manage rules’. This will show you the rules you’ve applied for that section and will allow you to delete or edit.
To see all of the rules for the entire sheet, at the top of the dialogue box after opening ‘manage rules’ – select the drop down arrow and change from ‘current selection’ to ‘this worksheet’.
V lookups can appear to be frightening, and when they don’t work, they can cause a lot of hassle; but when they do work – they are brilliant.
Essentially, what they do is look up a value in a vertical table, and bring you back the relevant answer. For instance, if you create a tracker, and after totalling the amound of marks from a paper, it will look at the total, then delve into a vertical table of results, and pick you the grade and put that in the cell where the formula is located. It can give exact matches or approximate matches.
The first thing you need to do, especially if you’re working with grades, is make a new sheet that lays out all of the possible amount of marks and plots what grade each individual mark is worth. This should start with 0 nearest the top, and the highest mark at the bottom.
After you do this, you should define this table (in other words, give this group of cells a name). To do this, highlight your tables (grades and raw marks); right click and select ‘define name’. Enter a name, and do not forget to copy and paste this! This is important. I always paste the name directly below the table so I know what this is called. This will be important later on. I named mine BOUNDARIES2021.
Now to go onto the Vlookup formula itself. First, select the cell where you would like the result to appear, in this case, the grade.
Start your formula as following:
The first thing you want to put into the formula is the value you want to look up, in this case, the raw mark. Follow it by a comma:
The next thing we need to do is tell the formula where it can find the result and the grade with which it matches. This is where defining our table is important (I named mine BOUNDARIES2021). After the comma following the value you want to look up, paste the name of the table you defined earlier:
Following this, we have to instruct the formula from which column of the table we want it to pick the result. I have arranged my table so that the raw marks are in the first column (column A) and the grades which we want it to retrieve, in the second column (column B). What is important to remember, and what confuses some people, is that the table is stand alone, so column 1 refers to the first column of the table – regardless of whether you put the table starting in column A or Z of your sheet!.
My Raw marks are in the first column of my table, and my grades, which I want it to bring back are in column 2. So, to my formula, I follow the comma with 2 – so it brings be the value from the 2nd column rather than the first column:
The last part of the formula is range lookup. For this, if you want to look up an exact value, you must type false; if you want an approximate, it should be true. For looking up a grade, either will work and I usually go for true.
Round off by closing with a close bracket and press enter.
After this, it’s a case of replicating down!
This is by no means an extensive guide for what Excel can do, but I find these are the things that are really useful to me when dealing with creating, editing and finding issues in trackers that have been made.