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:
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