• ## Email Subscription

Join 1,624 other followers

## Creating a RAG system in Excel tutorial

It may sound a bit sad, but I love Excel – once you have mastered a few simple techniques, you can put these techniques together in different orders to create some very powerful effects, and for me one of the most powerful things that I can do in Excel, is analyse some data in a way that will visually highlight an issue to me, so that I can act upon that issue quickly. One such technique that I (and many others) use is using a RAG rating system. RAG stands for Red, Amber, Green – (based on traffic lights), where things that are on schedule and up to date are Green, things that are a possible concern are Amber, and things that are a significant concern are Red.

This following set of videos, are designed as a tutorial to teach you the skills required to create an effective RAG system within your own Excel files.

If you like this tutorial, then please subscribe to my YouTube Channel at:

## Introduction

The first video is an introduction, showing the end product of what will be created.

## Using the Now() function

The Now() function is a very simple way to bring today’s date and time into a cell within the spreadsheet, which can then be used to compare against other dates within the spreadsheet, e.g. to see which are in the past or future.

## Using a basic IF statement

The IF statement in Excel is one of the simplest and most powerful ways to carry out analysis of data in Excel.

## Using a Vlookup function

The Vlookup function, seems a little confusing at first, but once used a few times is relatively straight forwards – and allows you to lookup a value in the left hand column in a table, and then return a value from a specified column in the same row of that table.

## Using the Max and Min functions

The Max and Min function are very simple to use, and will tell you what the largest or smallest value is in a list.

## Using Conditional formatting to create horizontal bars

Conditional formatting is where the appearance of a cell changes based on values (either the value of that cell, or different cells). This video looks at creating horizontal bars that move further right as the value increases.

## Using Conditional formatting to create icons

Another option when applying conditional formatting is to add small icons to cells, for example up and down arrows, traffic lights, warning flags etc.

## Putting this altogether to create the RAG system

The final video shows how the skills covered above can be put together to create the desired RAG effect.

I hope that this tutorial has proved to be useful.

If organisations want training providing in things like using Excel more effectively, then please get in touch via http://www.a6training.co.uk/contact.php

## Using Excel to create a matching pairs activity

I am a big fan of Excel, and have discovered over the years how to use it as a very effective teaching and learning tool. In the coming weeks (and months depending on how busy I get) I plan to create a few tutorials to show how different types of activity can be created.

My first attempt is going to look at creating a simple matching pairs activity, and to do this we will learn about

• IF statements
• Data validation
• Inserting a hyperlink to move from 1 sheet to another
• Sorting a list
• Combining contents of cells to create a sentence
• Unlocking cells
• Protecting sheets
• Hiding gridlines, sheet tabs, formula bar and row + column headers

This tutorial will consist of 4 screencasts.

The first shows the end product, and the reasons behind some of the choices

In the second screen cast we will look at how we create the sheet that the student will enter the answers into

The way that this resource works, is the student enters some answers, then clicks on a ‘check answers’ button which shows how many they have right. This looks really clever, and some people think I have used code to create it, but all I have done is create a second sheet in the workbook, that is laid out identically (so looks the same) which checks the answers. When the student clicks on the button to ‘check answers’ or the equivalent one to return to test, all they are doing is flicking between 2 separate sheets.

The third screencast looks at how to duplicate the entry sheet (to create the check sheet) and change it to check how many the student has got correct.

In the 4th tutorial, we will look at the final stages of tidying up the resource and making it work neatly. We will during this process hide the rows of the table containing the answers. The key to a resource like this, is once you have created something like this once, you can then just change the data in this table and you have a new resource.

There are a few more improvements that you could make – for example, on the check sheet, at the top of the page we could add a formula to the title, to copy it through from the ‘test’ sheet – this way if you change the title on the first sheet, it will automatically change the the heading on the second page. Also if the students don’t answer all the questions, when they check their answers they will get a ‘0’ displayed. This could easily be removed by adding another IF statement.

I hope that this has been useful, and look out for future tutorials on this topic.

## Simple formatting tips in Excel, to improve quality of learning materials

People often talk about what is there favourite e-learning tool or piece of software, and if I am asked, I answer by saying “If I were to be abandoned on a desert island with 30 students, with computers but I was only allowed one piece of software, I would take Microsoft Excel”

Why – quite simply it is a very powerful tool, which when you know how to use it, becomes a very easy tool within which to create learning materials, and even though I have access to more sophisticated tools, over 50% of the learning objects that I create use Excel. A lot of people think that Excel is about tables of data, with an ugly grid behind and complicated tool bars and navigation system – but when creating learning objects, all these can be stripped out, so when the learner views the resource they won’t even realise that it is Excel.

This short screencast shows a few such formatting tips that can be applied to make a learning resource more user friendly.

I will be creating a series of blog post on the use of Excel over the coming weeks, so watch this space for more information.