Conditional Formatting : Change Background Color Using a Formula

Hi guys, we probably need a condition where the error occurred when loading the data into a spreadsheet can be minimized. This would be very useful for beginners like us, who sometimes or even often make a mistake when entering data especially intended for the calculated data. By using Conditional Formatting, it seems that our mistakes can be reduced. As this example.

Conditional Formatting : Change Background Color Using a Formula.

In this tutorial, we will select each cell that contains an error by changing its background color using Conditional Formatting. Logically, the application of the Conditional Formatting is almost similar to the use of IF functions, but here we do not use the IF command directly. Here is a step by step of making Change Background Color Using a formula with Conditional Formatting.

1. Make a range with multiple rows and columns to form a spreadsheet as shown at step 2 below. Columns A and B we assume as the source column, and column D we assume as a result column.
2. Click on cell D1 that we assume to be the sum of cells A1 to cell A2. Then click Conditional Formatting> New Rule ...
   
3. After Conditional Formatting window opens, then we select the "Use a formula to Determine the which cells to format". In the "Format values ​​where this formula is true:" we fill with the formula "= A1 + B1 <> D1" (without the quotes). Then click the Format button on the lower right. After the Cell Format window opens, click on the Fill tab and select the color you want and click OK. Click OK again to close the New Formatting Rule window.
   
4. To apply on the other cells, copy and paste the D1 cell to the next cells, by copying cell D1 and highlights the destination cells and use the Paste Special option from the context-menu. In the Paste Special window select the Formats button.
5. Image of the spreadsheet below is the final result. There appears in cells that do not match to the sum of column A and column B was given a red background. That way, we can easily correct mistakes like typing errors.
   

A little bit explanation.
We use the formula "= A1 + B1 <> D1" in the third step. In this step, we give input as parameters that cell D1 will be given a background color if the condition indicates the TRUE value. So, if A1 plus B1 is not equal to the value contained in cell D1, then the background color applied to D1.

Hope this tutorial about How to Change Background Color Using a Formula with Conditional Formatting is helpful, Excelsor !