99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Create Date Sensitive Colour Changing Cells In Excel
Fri 23rd September 2011
However, the DVD rentals are a sideline in his grocery store, so trade is not booming, but it ticks over. This slow but steady stream of custom means that there is no need to buy a specialist application to monitor rentals and returns; Billy can keep track of things in an Excel worksheet. The only data Billy needs to enter is today's date, which he does by pressing Ctrl + :
One area where Billy had problems was with overdue returns. There was nothing on the worksheet to tell him that a rental had been returned late and he had to check each return against its rental date. When the shop was busy, some late returns were slipping through the net. Then a friend showed him how to solve this problem, and he now has an excellent system in place.
When a DVD is returned on time, Billy enters the date in the Returned On column and that cell automatically turns green to indicate no penalty. If the rental is returned between one and three days late, the cell turns yellow. This indicates that the customer is late and a small penalty should be imposed. Any DVDs returned over three days late will trigger a red cell, which means a more stringent penalty. It is all very clear and it works perfectly, but what did Billy's friend do?
She set up the worksheet to perform these actions using our old friend, conditional formatting. Here's how to do it.
Select the column to which you want to apply the formatting. Go to Format and select Conditional Formatting from the list to open the relevant dialog box. For Condition 1, select Cell Value Is, and equal to. In the right hand box enter the following:
Click on the Format button and select the Patterns tab. Choose a colour (in Billy's case green) and click OK.
Click Add to create Condition 2, and repeat the process, but this time you are entering a slightly different formula. Select Cell Value Is, and in the following box click on the down arrow and select Between. In the first box enter the following:
In the second box enter:
This gives us a range of one to three days after today. Click Format and select a colour as before, then click OK. Click Add to set up Condition 3.
This is slightly different again, as there is no upper limit to this condition. Again Cell Value Is is left in place, but this time from the drop down list, select Greater Than. You only have one formula box again, and into this type the following:
Click OK and the formatting is complete.
In doing this we used three different cell values in the Conditions box: equal to, between and greater than, and the basic formula had to be adapted for each condition. This is a good way to get 'under the bonnet' of Excel to see how different actions affect data. I have learned a great deal simply by playing around with various dialog boxes and experimenting. Dedicated training in the use of this popular application, however, would offer a more professional, streamlined approach to becoming an Excel wizard than my 'suck-it-and-see' method.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Pine (UK) Holdings Ltd
Director Of Sales
Really enjoyed the training and everything was so well explained and Tony really patient and motivating
Barbican Insurance Group
Jen was incredibly knowledgeable, clear and enthusiastic. Would recommend to everyone. We learnt so much but pace/ content was manageable.
Really great course with an extremely knowledgeable and enthusiastic trainer.