“Conditional Formatting”

“Conditional Formatting” can be done to “Format” the “Cell”, depending on whether the “Cell” data meets certain conditions or not.

For example, suppose we want the background color in our cells to be red when we have the sales under 10,000 Euro.

First, we select the area where we want to apply “Conditional Formatting”.

Then we click on “Format” (“Format”) in the menu bar and select ” Conditional Formatting” (“Conditional Format”)

The “Conditional Formatting” dialog box (“Conditional Formatting”) opens.

In the first box we can choose “Cell Value Is” or “Formula”.

Since it is a value which we want (ie 10,000), we choose :”Cell Value Is”.

If you choose the option as “Formula” you should always enter a “Formula” with a logical value (TRUE (1) or FALSE (0)).
The “Formula” may be values, “Cell references” or “Worksheet” “Functions”.
The “Formula” must always begin with a ‘=’ sign.

In the second box, we have a large number of choices, “Greater than”, “Less than”, “Equal to”, “Not equal to”, etc. …

We choose “Less than” (“Less than”).

In the third section we give a value for Excel to make the comparison. (In our case 10000)

We click on the “Format” (“Format”) button.

A new dialog box opens with three tabs: Borders, Font and Patterns.

We just want our “Cell” to turn red if the value of sales is “Less than” 10,000 Euros, so we choose the “Patterns” tab for red, Click OK and OK again.

And as you see in the picture above, the “Cell” turns red when our forecast sales of 10,000 Euro is not met.

We can set upto three criteria for a “Cell” or “Range” of “Cells”.

Suppose we want the other cells that have sales “Greater than or equal to” 10,000 Euros to be in green color, then we click the “Conditional Formatting” dialog box, click “Add>>” (“Add>>”).

In the first box we again choose for “Cell Value” and in the second box choose “Greater than or equal to”.

And in the third box remains the same, 10000.

Then we click the “Format” button (“Format”) and the “Patterns” tab, then we choose the color “green”.

We click OK and OK again

Result:

Please note that you do not make too much of a color splash..

Change of a value in your formatted “Range” (eg cell B5 to 10.500) will automatically adjust the color.

If we want to remove the “Conditional Formatting”, we click on “Format” (“Format”) and select “Conditional Formatting” (“Conditional Formatting”) in the menu bar.
In the dialog we click “Delete “.

In the pop-up window that appears, we choose the “Conditions” that we want to “Delete” and click OK.

“AutoFormat” (“Auto Format”)

“Auto Format” allows us to provide a “Format” for all “Worksheets” in a faster way.

“Auto Format” is good for headers with a standard columns and rows, type of data and a “Total” row.

It is better that you have the layout of your worksheet in place for your convenience..

Before we start the “Auto Format”, we first select the cells and choose “Format” then select ” Auto Format” (“Auto Format”) in the menu toolbar.

Hopefully you have the “Options” button, which in turn opens the “Options” panel, where we can make some small changes.

Choose a style in the “Table Format” list, and click OK.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats