Skip to content

Conditional Formatting

September 9, 2010

Welcome back !!! Its hard to believe that I’m writing my second blog. However I am going to try and  do a one blog a month. Its not too much to commit to and it gives me a whole month to think about the next one !!!

In this blog I am going to talk about conditional formatting.  As many of you will know formatting a cell in Excel is just changing its colour e.g making certain cells green etc.

What is Conditional Formatting ?

Conditional formatting means that the cell changes colour when certain criteria are met.  The cells change colour automatically. This may seem strange but there are valid uses for such behaviour :-

  • May be you have a spreadsheet where you store all your invoices and need to know when they are overdue. You can have a field for the due date – and apply a rule so that if the invoice is over due the cell colour changes red automatically.
  • Perhaps you have a list of customers – and their addresses. You can have rules to automatically show which customers are from say England, Scotland, Wales or Northern Ireland
  • Or you have a list of sales staff who participate in a bonus scheme. Condtional formatting will allow you to highlight those staff that are eligible for a bonus based on the criteria that you use.

Probably the best example of where I have used Conditional Formatting on projects is at the Department of Health. They required to validate a large amount of data on a monthly basis. They needed to see that the data contained the correct prices and codes for a number of operations. I built a tool that highlighted the errors in red – so that they could be seen at a glance.

You can find the full case study on my website at :-

http://www.simplyspreadsheets.co.uk/case-study.php/The_Department_of_Health

The model contained over 40 rules.

Incidentally in Excel 2007 and later Microsoft considerably expanded the capability of conditional formatting. You can find more information in the video links below.

Video Tips

The following is a link to show you some examples of how to use conditional formatting in Excel.

If you are using Excel 2007 or later then the following video will be useful


http://www.youtube.com/watch?v=PpQ9d4cHVdk

For those of you using Excel 2003 or earlier then the following link is appropriate:-

http://www.youtube.com/watch?v=3p0fkljYwPY&feature=related

Advertisements

Automated Invoicing Solutions

August 1, 2010

After weeks of agonising and finally getting my new website up and running, I’ve decided to produce an occasional blog about Simply Spreadsheets Ltd. Its aims are to :-

  • Make people more aware of the kinds of projects that I enjoy working on
  • Help explain what works well in Excel and what doesn’t work so well
  • Give some general tips on Excel – these may be short cuts or how to things better

Remember there is no perfect IT tool. Excel seems to be able to do many of the things that I like doing.

Project Focus – Invoicing

In this blog I’m going to talk about an area that I often get asked to automate –invoicing. There are many reasons why it makes sense to automate invoicing process including :-

  • It ensures that the invoices are always correct
  • They are consistent in their look and feel
  • It is a repetitive process – and hence one that ought to be automated
  • As invoices are being created faster, it means that cashflow is being improved

One of the most recent companies that I worked with was Back Office Support Solutions Ltd (BOSS) who provide admin and financial support to small businesses. They asked me to automate their monthly invoicing process. The solution had to :-

  • Allow for clients to be charged either on an hourly rate (depending on the services used) or a monthly retainer
  • Cater for a varying number of clients
  • Show the breakdown of charges by service type and also allow for expenses

You can read the full case study on the site http://www.simplyspreadsheets.co.uk/case-study.php/BOSS_-_Back_Office_Support_Solutions

However, Simply Spreadsheets delivered a solution that meant that staff continued to work in the same way and then at the end of the month the invoices would be created at a press of a button.

Excel Tips

I’m going to conclude each blog with some general Excel links:-
How to use VLOOKUP:-

http://www.videojug.com/webvideo/how-to-use-vlookup-in-excel

Want help with Pivot tables then look at :-
http://www.youtube.com/watch?v=CuhQ9lSMmjg&feature=related

Thats all for now.

regards

Kaps