Microsoft Office Conditional Formatting Tricks

Microsoft Office Conditional Formatting Tricks
In this article youll get to learn Excel formatting tips that will make your life a whole lot easier. I will primarily deal with conditional formatting and will cover its two major areas Microsoft MCTS Training.


Highlighting Table in a Repeating Table


If you want to use conditional formatting in order to highlight data in a repeating table, you should understand that repeating tables work in a similar method as repeating sections. However, if you set a condition for a repeating table, with every row of the table an independent condition is applied.


Say for example, you want a repeating table which contains a list of customers and the third column of the table is the number of years that the customer has been with a specific company. In this case we want the to highlight customers that have been with the company over 5 years by turning the row blue, and highlight customers who have been there with the company over 10 years by turning the row green.


First of all we need to insert a repeating table:


When you are in design mode, you can start by inserting a repeating table onto the form, youll be asked for the number of columns required you can leave it with default value 3 and hit OK.


Now we have to work on specifying the conditions for the conditional formatting:


For this you first need to select the repeating table, and then click Conditional Formatting from the Format menu. Now, in the Conditional Formatting dialog box, we need to click Add in order to add a condition. When you are in the Conditional Format dialog box, under If this condition is true, within the first drop down list you must now select the field that corresponds to the text box in the third column of the repeating table. When you reach the second drop-down list, select is greater than. Correspondingly, in the third drop down list you should first select Type a number and then key in 5. Now under, Then apply this formatting, in the Background color list you must now select the color blue and hit OK so that the first condition is saved. All you need to do now is to repeat these steps again in order to add second condition and replace 5 by 10 also select green as the background color. You can order the conditions using the Move Up and Move Down buttons in the Conditional Formatting dialog Box Microsoft MCITP Training, to make

sure that the greater than 10 condition is on top.
TIP The sequence of conditions applied is from top to bottom. Hence, in case a greater than 10 condition is on top, this condition will never be applied as anything that is greater than 10 is also greater than 5.


We can now preview and test the conditional formatting:


For this we just need to click Preview Form on the Standard toolbar. We can now add some rows to the table and start entering numbers in the third column to make sure that the conditions we specified are being applied in a correct manner.


NOTE When you type 10 it wont satisfy the greater than 10 condition, so it is suggested that we change the condition to greater than or equal to 10.