Home
Tips & Tricks
Excel Functions
Excel Formulas
Applied Formulas
Applied Functions
Excel Macros
Business Uses
Downloads
Excel Tests
Latest Updates
Recent Posts
About us
Q&A
Excel Calendar enhanced with the improved Conditional Formatting 2007/2010
Enhancing my original excel calendar has provided me with the opportunity to test the improved conditional formatting made available in Excel 2007 and 2010 to the limit. The original calendar was able to highlight the dates with different colours for public holidays, school holidays and special activities, which in my case, are my excel courses. It has served me very well for the last few years. But as our room rental activities picked up, I realized that it is not able to serve our needs anymore. The original
calendar can only provide a single shade to identify whether the room is used for that day. It cannot differentiate whether it is used in the day, evening or both. So I decided to enhance it with the improved conditional formatting function in Excel. What I thought was a simple task turned out to be very challenging as I grapple with how the conditional formats behaves under the different conditions. For example, the cell will be shaded diagonally from the left to the right when the room is used in the day. When the room is also used in the evening, my original idea was to have the cell shaded diagonally from the right to the left. In this way, the cell will be crossed with lines if the room is used both in the day and evening. But it did not behave that way. The shade will over-write the earlier one. So in the end, I have to figure out a way to differentiate the usage for the day and the evening. In the process, I get to understand how the improved conditional formatting works:
1. Unlike Excel 2003, the improved Conditional formatting can cotniue to evaluate the conditions if we turn off the setting located next to the conditions.
2. If the conditions continue to be evaluated, it will overwrite the format that meet the previous conditions, if they are in the same format, e.g. both conditions are set to fill up the cell with colours. Based on the above example shown in the diagram above, the cell will overwrite the red cell and turn yellow if it is found that the fifth condition is also true after the first condition turns true.
3. But if the format for the condition is not in the same category as the earlier condition, it will add on to the format. Based on the same example shown in the diagram above, the first condition will turn the cell into red if the condition is met. When the second condition turns true. The cell will continue to be coloured red. At the same time, it will draw a border for the cell.
Other than conditional formatting, I have also changed the way dates are input into the calendar. In the original excel calendar, the cell will change to the designated colour when the dates are entered in a certain range.
But in the upgrade calendar, there is no need to do so. I have designated a central location where the dates can be entered. To change the cell colour, just put the same code in colour column AF and the extreme right cell within the header. The dates will automatically match the course name and show up in the specific range and at the same time, turns the date into the colour displayed by the header. This is done with a complex array formula which acts like an auto-filter function, listing only entries that meet the criteria.
Learn more about the original excel calendar.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
