Tech’ top tips – #28. Here’s looking up you kid

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) A set of sample data in Excel
When using VLookup I generally use the wizard – to open this click on Formulas>Insert Function>VLOOKUP before clicking OK The Excel Insert Function wizard
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

The VLookup wizard
Here’s how the wizard looks when completed as described The completed VLookup wizard
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 dataset with one grade in place
The formula looks like this The VLookup formula
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 Errors in the dataset
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 A faulty Excel formula
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 Amending formulae in Excel
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

The final dataset

Tech’ top tips – #27. I’ll format that on one condition

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? A list of data points in Excel
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. Selecting 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.
A Contidional Formatting rule in Excel
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.
A conditionally formatted list in Excel
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. A conditionally formatted sorted list in Excel
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.


Explore some of the 9000 video courses available through the LinkedIn Learning Pilot trial

linkedIn learning for decorationBoost your skills or feed your curiosity by exploring some of the
video courses available through LinkedIn Learning. Develop and improve ‘the most’ in demand business, technology and creative professional skills by accessing online over 7000 online courses and around 300k videos with LinkedIn Learning. Courses ranging from ‘soft skills’, design to programming and much more in this continuous evolving library of courses.

Make the most of the opportunity to access to LinkedIn Learning for free as part of our pilot trial subscription which ends in June 2019. Our guide will explain how you can access and get started in LinkedIn Learning, discover the varied learning routes available to you and provide further useful tips.

Gain access:

  • To expert led courses in Technology, Creativity and Business
  • Learn from industry leaders and watch high level experts in the field of your interest – all in one place
  • Get personalised recommendations. Explore the most in demand skills based on your experiences
  • Access courses from any desktop or mobile device anytime
  • Reinforce knowledge with quizzes, exercises and practice materials
  • Discover learning paths for your courses by industry experts providing valuable career knowledge and skills
  • Certificates of completion

Find out more in our guide on LinkedIn Learing and here in this link on  how to get started.

Tech’ top tips – #24. Excel when it comes to data

This tip is provided by the Centre for Enhancing Learning through Technology (CELT).

Currently, my two favourite tools in Microsoft Excel are ‘Text to Columns’ and the ‘Concatenate’ function. Both of these tools really help when we have long lists of data and we need to either sort them or maybe merge data from different cells into one string.

A comma seperated string in ExcelText to Columns enables us to split long strings into individual cells for sorting. For example, a database export containing addresses produces one string per address.

By using Data>Text to Columns we can tell Excel to split the string down using the Commas that appear in the text string.

The Text to Columns wizard

Text to Columns resultWhen the Finish button is clicked, our text string is now displayed across multiple columns – ready for sorting by whichever column we desire.


But what if we want a text string and all we have is data in columns? This where Concatenate comes in.

A basic Concatenate formulaBy typing a formula we can bring data from separate cells into a text string – the key is the ‘&’ symbol.


Basic Concatenate resultsHowever, the formula above will mash all of the data together without any spaces, not very good if we’re going to use this for a mail merge data source or if we need to read it.


Concatenate with spaces addedBy using the speech marks, we can augment the formula to add a space in between the contents of each cell.

Now, our data appears as one string but with the appropriate spacing for reading and using.