Conditional Formatting Stop if True

Friday, October 10th, 2014

In conditional formatting, we can set more than one rule to format the cells. If the rules do not conflict with each other, then all is well. But what happens when more than one rule turn TRUE? According to Microsoft website, the earlier rule that turns takes precedence over the latter rules. So if the earlier rule format the cells Red and there comes a second rule that formats the cells Blue, the cell will remain as Red.

Then in this case, what is the purpose of the option Stop IF True?

That option works if you have a different format for the 2 rules. If the earlier rule format the cell Red and the latter rule format the Font colour to Blue, both rules will be applied. This is different from the earlier example when the rules are using the same formatting, change the cells colour. In this example, the 2 rules apply different kind of format (one is cell colour and the other is font colour) and therefore, both are applied. If you do not wish to apply the latter rule, change in font colour when the earlier rule turn TRUE, then check the option STOP IF TRUE on the earlier rule. This will make the conditional formatting stop checking the latter rule and therefore, format font colour will not be applied when the earlier rule (format cell colour) turns TRUE.

For a more detailed explanation, please refer to this write-up on Microsoft website


Adding Tabs into Excel 2010 on commonly used functions

Monday, April 28th, 2014

During the training course “Breakthrough Performance for Human Resources for Excel” today, participants asked how come they do not have a tab called “Applied Excel” which is showing up on my Excel 2010 program. So I showed them how I created it by customizing the Ribbon. The customized ribbon helps to put together your commonly used functions in one place so that you do not have to scroll through tabs to find them. In the video, I showed them how to create and name a new tab and add a new group called File in the new tab. I added in some commonly used functions such as open file command, new workbook, open recent files, print preview common into the group.

In the demo, I have also shared with them how to find and quickly added groups into the new tab instead of doing it one by one. In the last part of the video, I showed them that they could export the customized tab and import it into their computer once they are back in the office. Watch this video to get the step by step guide.


Saturday, April 12th, 2014

I had an opportunity to test out one of the participant’s data comprising 500,000 (half a million) rows using PowerPivot with my computer which is running a 2 year old i5 chip with 6GB ram. The data failed to load into PowerPivot because of insufficient memory. The data source is an Excel file.