Anzeige

How does Conditional Formatting work in Excel

Many times, you have certainly wished that certain contents of your Excel spreadsheet be highlighted a little bit more, so that they catch your eye at a glance.

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. However, what we really want is that cells automatically get a pre-determined look under certain conditions.

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

How does Conditional Formatting work in Microsoft Excel

Topic Overview

Anzeige

How does Conditional Formatting work in Excel

Many times, you have certainly wished that certain contents of your Excel spreadsheet be highlighted a little bit more, so that they catch your eye at a glance.

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. However, what we really want is that cells automatically get a pre-determined look under certain conditions.

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

How does Conditional Formatting work in Microsoft Excel

Topic Overview

Anzeige

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 already implies, this is a cell formatting that under certain conditions takes place automatically. 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 suppose you make yourself a household table, and there you have a job that usually always moves between 80 € and 100 € per month. However, the longer you run this list, the harder it will be to catch up on deviations at a glance.

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

Advertisement

Of course, this is first of all a bit of work, 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 already implies, this is a cell formatting that under certain conditions takes place automatically. 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 suppose you make yourself a household table, and there you have a job that usually always moves between 80 € and 100 € per month. However, the longer you run this list, the harder it will be to catch up on deviations at a glance.

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

Advertisement

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

2. Predefined rules for conditional formatting

2. Predefined rules for conditional formatting

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 get in the case of an increased amount red, and 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 ​​that should be considered for formatting should be highlighted.
  • In the “Start” tab, click on “Conditional Formatting” and click 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 that we click here on “less than” a lower threshold and a corresponding color set.

See picture: (click to enlarge)

Bedingte Formatierung Größer als
Excel Bedingte Zellformatierung festlegen

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

See picture: (click to enlarge)

Bedingte Formatierung in Excel
cshow
Ads

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 get in the case of an increased amount red, and 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 ​​that should be considered for formatting should be highlighted.
  • In the “Start” tab, click on “Conditional Formatting” and click 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 that we click here on “less than” a lower threshold and a corresponding color set.

See picture:

Bedingte Formatierung Größer als
Excel Bedingte Zellformatierung festlegen

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

See picture:

Bedingte Formatierung in Excel
cshow
Ads

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.

3. Conditional formatting with own rules

3. Conditional formatting with own rules

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

For the sake of simplicity, we will stick with our small household 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 we click again on “Conditional Formatting” then on “New Rule” and select the option “Format only unique or duplicate values”

See picture: (click to enlarge)

Bedingte Formatierung Neue Regel festlegen
Bedingte Formatierung doppelte Werte

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

See picture: (click to enlarge)

Bedingte Formatierung in Fettschrift
cshow
Ads
Blogverzeichnis Bloggerei.de

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

For the sake of simplicity, we will stick with our small household 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 we click again on “Conditional Formatting” then on “New Rule” and select the option “Format only unique or duplicate values”

See picture: (click to enlarge)

Bedingte Formatierung Neue Regel festlegen
Bedingte Formatierung doppelte Werte

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

See picture:

Bedingte Formatierung in Fettschrift
cshow
Ads
Blogverzeichnis Bloggerei.de

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Spring Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top