Contains paid promotion

Advertisement

So you just use the
Conditional formatting in Excel

Many times you have certainly already wished the specific content of your Excel spreadsheet be emphasized a little more clearly, so that they sting one at a glance so to speak.

Bedingte Formatierung in Excel 2016

This is basically (as long as you do it statically) no problem and relatively easy to implement.

After all, you can color any cell as you like, and make it larger or smaller as well.
What we really want is cell automation under certain conditions get a predetermined look.

How to use Conditional Formatting in Excel 2016 can be found in our article.

Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
List Price: € 52.99 You Save: € 23.00 (43%) Prime Price: € 29.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. October 2018 um 7:31 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

So you just use the Conditional formatting in Excel

Many times you have certainly already wished the specific content of your Excel spreadsheet be emphasized a little more clearly, so that they sting one at a glance so to speak.

Bedingte Formatierung in Excel 2016

This is basically (as long as you do it statically) no problem and relatively easy to implement.

After all, you can color any cell as you like, and make it larger or smaller as well.
What we really want is cell automation under certain conditions get a predetermined look.

How to use Conditional Formatting in Excel 2016 can be found in our article.

Anzeige
SanDisk Ultra 128GB microSDXC Speicherkarte + Adapter bis zu 100 MB/Sek, Class 10, U1, A1, FFP
List Price: € 52.99 You Save: € 23.00 (43%) Prime Price: € 29.99 zum Angebot*
Price incl. VAT., Excl. Shipping
*Zuletzt aktualisiert am 17. October 2018 um 7:31 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

1. What is Conditional Formatting in Excel?

1. What is Conditional Formatting in Excel?

First of all, let’s clarify the general question of what “conditional formatting” actually is.

As the name implies, this is a cell formatting that automatically occurs under certain conditions. So we can e.g. The table says that if a cell contains a predetermined value, or if it is within a certain range, that cell will be highlighted in color.

So let’s say you make yourself a household table, and there you have a job that usually moves between $ 80 and $ 100 a month. The longer you run this list, the more exhausting it will be to catch up on deviations at a glance.

However, if you add a “Conditional Formatting” to the cells in question, for example, once the value exceeds $ 100, you could automatically have that cell highlighted by Excel with a red background color.

Advertisement

Of course, that’s a bit of work at first, but it’s a job you do just once, and then the process runs automatically.

First of all, let’s clarify the general question of what “conditional formatting” actually is.

As the name implies, this is a cell formatting that automatically occurs under certain conditions. So we can e.g. The table says that if a cell contains a predetermined value, or if it is within a certain range, that cell will be highlighted in color.

So let’s say you make yourself a household table, and there you have a job that usually moves between $ 80 and $ 100 a month. The longer you run this list, the more exhausting it will be to catch up on deviations at a glance.

However, if you add a “Conditional Formatting” to the cells in question, for example, once the value exceeds $ 100, you could automatically have that cell highlighted by Excel with a red background color.

Advertisement

Of course, that’s a bit of work at first, but it’s a job you do just once, and then the process runs automatically.

2. Predefined Conditional Formatting Rules.

2. Predefined Conditional Formatting Rules.

Now we come to the point.

In our first example, let’s look at the simplest discipline of conditional formatting in the form of predefined rules, and we have built up a sample table of budget expenditures for a year in which some values ​​deviate from the usual ones that we would get in the case of an increased amount red, and want to color at a lower amount with a green background.

To do this, we proceed as follows:

  • The first line of the table in which the numerical values ​​which should be considered for formatting should be highlighted.
  • In the “Start” tab, click on “Conditional Formatting” and then on “Highlight Rules” – “Greater Than”.
  • Then set the upper threshold and the color from which the formatting
    to be held.
  • Then again the same game, only here we click on “less than” a lower threshold and a corresponding color set.

See picture: (click to enlarge)

Conditional Formatting Larger than
Excel Define conditional cell formatting

Thus, after we have set the thresholds for each item, our table looks like this in the end result:

See picture: (click to enlarge)

Excel table Conditional formatting
Schluesselnotdienst Aktion - kautionsfrei.de
Anzeige

For example, if you create a household table at the beginning of the year and set thresholds for each item, Excel will automatically check whether the cell is colored or not when you enter it using Conditional Formatting.

This allows you to detect deviations at a glance without having to filter data or have to look through it manually.

Now we come to the point.

In our first example, let’s look at the simplest discipline of conditional formatting in the form of predefined rules, and we have built up a sample table of budget expenditures for a year in which some values ​​deviate from the usual ones that we would get in the case of an increased amount red, and want to color at a lower amount with a green background.

To do this, we proceed as follows:

  • The first line of the table in which the numerical values ​​which should be considered for formatting should be highlighted.
  • In the “Start” tab, click on “Conditional Formatting” and then on “Highlight Rules” – “Greater Than”.
  • Then set the upper threshold and the color from which the formatting
    to be held.
  • Then again the same game, only here we click on “less than” a lower threshold and a corresponding color set.

See picture:

Conditional Formatting Larger than
Excel Define conditional cell formatting

Thus, after we have set the thresholds for each item, our table looks like this in the end result:

See picture:

Excel table Conditional formatting
Schluesselnotdienst Aktion - kautionsfrei.de
Anzeige

For example, if you create a household table at the beginning of the year and set thresholds for each item, Excel will automatically check whether the cell is colored or not when you enter it using Conditional Formatting.

This allows you to detect deviations at a glance without having to filter data or have to look through it manually.

4x CHIP TESTSIEGER Handyversicherung_600x500
Anzeige

3. Conditional formatting with own rules.

3. Conditional formatting with own rules.

In our next example we want to go off the beaten track and set our own rules for conditional formatting.

For the sake of simplicity, we stick to our small budget table and will display all duplicate values in bold.

To do this, we proceed as follows:

  • We mark the entire cell area of the table which in the formatting
    should be considered.
  • Then click again on “Conditional Formatting” then on “New Rule” and select the option “Format only unique or duplicate values”

See picture: (click to enlarge)

Conditional Formatting Set new rule
Conditional formatting double values

Then on “Format” and in the tab “Font” we choose FAT and confirm with “OK“.

See picture: (click to enlarge)

Conditional formatting in bold
300x250_Students
Anzeige
Print Friendly, PDF & Email

In our next example we want to go off the beaten track and set our own rules for conditional formatting.

For the sake of simplicity, we stick to our small budget table and will display all duplicate values in bold.

To do this, we proceed as follows:

  • We mark the entire cell area of the table which in the formatting
    should be considered.
  • Then click again on “Conditional Formatting” then on “New Rule” and select the option “Format only unique or duplicate values”

See picture:

Conditional Formatting Set new rule
Conditional formatting double values

Then on “Format” and in the tab “Font” we choose FAT and confirm with “OK“.

See picture:

Conditional formatting in bold
300x250_Students
Anzeige
Print Friendly, PDF & Email
Become Guest Writer

We publish your guest contribution free of charge in German and English
incl. backlink to your website and identification of the author

Anzeige
Blogverzeichnis Bloggerei.de

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. October 2018 um 0:41 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.

About the author:

Michael Suhr
Michael SuhrWebdesigner / Economist
After 20 years in logistics management, I have been working as a freelance web designer and office trainer since the beginning of 2015. Incidentally, I give tips and tricks for more digital skills in my blog as time permits.

Search by category:

Anzeige
Die Amazon.de VISA Karte
 Preis nicht verfügbar zum Angebot*
Preis inkl. MwSt., zzgl. Versandkosten
*Zuletzt aktualisiert am 18. October 2018 um 0:41 . Preise können sich geändert haben. Alle Angaben ohne Gewähr.
2018-09-29T10:50:16+00:00By |Categories: Excel 2016, Office 2016|Tags: , |
In order to optimize our website for you and to be able to continuously improve it, we use cookies. By continuing to use the website, you agree to the use of cookies. Ok