Excel

This tip is provided by the Centre for Enhancing Learning through Technology (CELT). The images in this post will enlarge when clicked – use the Back button to return to the post.

It seems as though the Excel tips of recent weeks are being well received so in-keeping with the theme, today’s tip is about the Lookup functions in Excel and Absolute references.

Using the lookup functions we can get Excel to search for values within a range and return a result based on a fixed set of criteria. This can be useful when have a large sheet of data and we want to, for example, append the data with extra information.

There are two Lookup functions in Excel; Vertical (up and down columns) and Horizontal (along the rows). The example that follows uses the VLookup (vertical) function to interrogate a list of fictional exam results and return the grade (as defined in a seperate table) in the empty column.

These are the data we’ll be working with. I need the Grade column (column E) to be populated but I don’t want to manually type all of those grades in! So I have created a separate table (in columns G and H) with the potential grades and some matching marks. The name of the game here is to use the VLookup formula in column E and tell it to use the smaller table (the Array) to fill column E in with something depending on what it finds in column D (the result)
When using VLookup I generally use the wizard – to open this click on Formulas>Insert Function>VLOOKUP before clicking OK
With my cursor in cell E:2 I have opened the wizard and this is my view. This can be a bit daunting to begin with – here’s my understanding of the different elements: Lookup_value In our example this is the result in column D. Essentially, what we want Excel to look for in our little Array Table_array This is the little table I created with the potential grades and marks in columns G and H Col_index_num This is the confusing one! Excel usually likes to use letters for column labels but here it wants to use numbers. This field relates to the column in the Array where the, in this example, the grades are stored. Our Array is a two column table and the grades are in the second column so we need to put a “2” in this field Range_lookup enables us to add some flexibility. If we leave this blank Excel will return a grade based on the Score that is closest to the Result. This is fine as our student results won’t fit neatly into our seven options within the Array. However, be careful with this as you may need Excel to find exact values when looking up data in a column – if this is the case type the word “FALSE” in this field
Here’s how the wizard looks when completed as described
Having clicked Ok we now have a grade in Christine Alderman’s Grade column. To recap, cell E:2 is looking at D:2 and then seeing if there’s anything close within the Array (column G) and if so returning whatever sits in column H accordingly. Therefore Christine has a result of 87, this is closest to 85 so Excel returns an A* – well done Christine
The formula looks like this
Now we have a formula in place, to make life easier we can drag this down column E and get Excel to do the heavy work for our entire list – however, something doesn’t look right
This is the formula for row 10 (patricia Rowe) – notice that although Excel is looking at patricia’s Result (column D) the cells in which it thinks our Array lives have changed. Our Array is in range G1 to H8 but patricia’s row thinks our Array is from G9 to H16 – no wonder it can’t find anything
There’s an easy way to fix this; before dragging the formula, we need to make the cell references for our Array ‘Absolute’ or fixed. To do this we use the dollar sign “$” to fix both the columns and rows for our Array. We can amend the first formal that was created by the wizard by typing in the Formula Bar – I have added four dollar signs to the formula
This time, when I drag the formula down the Table Array will stay fixed and a full set of grades will be populated; bad luck Heather, patricia and Ashley! Absolute references can be used in all parts of Excel when we’re dragging formulae around – it makes life so much easier when we need to fix a part of a formula

This tip is provided by the Centre for Enhancing Learning through Technology (CELT). The images in this post will enlarge when clicked – use the Back button to return to the post.

The way we interpret and respond to data can be linked to how the data are presented. For example, a long list of numbers ordered largest to smallest may not be the best way for everybody to see a pattern but using Conditional Formatting we can apply other visual techniques to help make our data easier to interpret.
Using colours in this way, while thinking about preserving accessibility, can help those with a more visual preference to engage with numbers.

Here’s a very practical example that can be applied to different data in Excel.

This is a list of items for purchase from a supplier and their cost – the list is sorted by Total Cost. But what if I want Excel to show me, for example, everything that costs £50.00 or more?
Using Conditional Formatting I can tell Excel to make all the items that cost £50.00 or more a different colour. To do this I will highlight all of the items in the Total Cost column and click on Home>Conditional Formatting. In here there are a number of pre-sets that can be applied to apply different colours but I just want to make everything that costs over £50.00 pink so I will click Manage Rules.
In Manage Rules we can ‘build’ our conditions and what Excel does with cells that meet the conditions. This may seem a little daunting at first but using the wizards in here offers a lot more flexibility over the standard pre-set rules. Having clicked Format only cells that contain; I have used the three drop-down menus to tell Excel that cells whose value is equal to or greater than 50 should be coloured pink.
Clicking Apply and Ok; my list now looks like this. As you can see all of the items priced £50.00 or more are coloured pink. Now as it stands this may not be particularly useful as my list is already sorted by Total Cost but I can now quickly and easily see where my £50.00 threshold is. However, with the Conditional Formatting applied to the Total Cost column I can now sort my list using other columns but items costing £50.00 or above will remain pink.
For example, my supplier needs my list sorted by part number but I still need to see which the more expensive items are. Sorting the list by Part number satisfies my supplier’s requirement while still enabling me to quickly see which of the items are costing £50.00 or more.
Finally, we can apply multiple rules and change the order in which they are applied so have a practice with some of your own data and see if you see how to use conditional formatting to highlight items that fit multiple conditions across multiple criteria.