Excel online training course

excel lesson 8, Conditional formatting and Autoformat

Conditional format

With the conditional format we can depending if the cell fulfills certains conditions, control the format of the cell.
A example:
Lets say we want to change the background of our cells red when we didnt reach a turnover of $5.000.
First we select the area where we want to apply our conditional format to.
Then we click on "Format" and "Conditional Format" in the menubar.
The "Conditional Format" window will open.

In the first field we can choose between celvalue or formula.
Since it is value we want to select on ($5.000) we choose for cellvalue.

If you choose for formula in the first field you need to enter a formula with a logical value (True (1) or Untrue (0)).
The formula may contain values, cell references and workbook functions.
The formula must allways start with the = sign.

In the second field we have alot of choices, between, equal to, greater then, less then, etc..
We choose for "Less than".
In the third field we will enter a value which Excel will compare with. (in our case 5000)

We then click on the "Format" button.
A new window will open with three tabs: borders, fonttype and patterns.
We only want our cell to color red if the values of the turnover was smaller then 5.000, so we choose for red color in the patterns tab, we click on "Ok" and once more on "Ok" to see the results.

As you can see in the image above the cell will color red if the turnover of $5.000 is not reached.

We can set a maximum of three criteria for a cell or cellrange.
Lets say we want the other cells which are larger, or equal to $5.000 have the color green, then we click in the "Conditional Format" window on "Add".
In the first field we choose for "Cell Value" and in the second field we choose "Greather then or Equal to".
And the third field will stay the same, 5000.
Then we click on the "Format" button, and in the patterns tab we choose the color green.

We click on "Ok" and once more on "Ok".
This will be the result:

Becarefull not to use to much colors or your worksheet will become cluttered.

If you choose a number in your formatted range (for example cell B4 to $5.500) then the color will automaticly adapt.

To delete the conditional format, we click on "Format" and "Conditional Format" in the menubar.
In the opened window we click on "Delete".

In the popup window that appears we choose the formatting we want to remove and click on "Ok".

Autoformatting

Autoformatting allows us to quickly apply formatting to entire worksheets.
Autoformatting is usefull for worksheets with default colomn and row headers, some information and a totals row.
Before we start Autoformat, we first select the cells and then choose "Format", "Autoformat" in the menubar.
You can also click on the "Options" button which will open the option panel where we can make some small changes.
Choose your style in the table format list and click on "Ok".