• Dave Foord
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,639 other followers

  • Dave Foords Twitter

  • Advertisements

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.

Image showing a simple RAG system created in Excel

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:

https://www.youtube.com/channel/UCWuDqvf7nO6-00JMMxm1lIw?view_as=subscriber


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

Advertisements

Adding a date picker to Excel

As a huge user of Excel, I am often entering dates into cells. Different versions of Microsoft have had date pickers as an optional add on, but they haven’t always worked for me with all versions or consistently.

I have however just discovered this free tool:
http://samradapps.com/datepicker/

Which is created by Sam Radakovitz , which does exactly what I want.

Once set up, if a cell in Excel is set as a date, clicking on that cell, will bring up a small calendar icon to the right of the cell:

Image showing the calendar icon to right of the selected cell

Which when clicked on, will bring up the calendar tool, making it easier to enter dates, and not have to worry about the correct format, or whether the spreadsheet has been set up in UK or USA format.

 

F

Full credit to Sam for this one, I am just blogging about this mainly for my own benefit so that I can relocate this in the future when I change computers.