Hello and welcome to A1 Excel! In this post I'll be going over Conditional Formatting. The main purpose of Conditional Formatting (from my own personal use) is to draw attention to certain cells based on a descriptive/specific criteria. This can be useful in either a summary table to draw the audience in to a specific data point or to determine data for additional formatting. Below I will show you a couple examples of how to utilize Conditional Formatting (as well as how to remove it if you do not need it on a specific sheet anymore).
To start with, proceed to the Home section of Excel where you will see the Conditional Formatting tool under the Style section:
In this example I will be utilizing the highlight duplicate function to show how you can quickly and accurately check your own data. This can be especially useful in a list of data to ensure overall accuracy with duplicates before they are removed or altered.
In this table you will see a list of pet vet clinic data. I want to see if there were any duplicates made in this list. So I will select the list of data that I want to be under the conditional formatting rule and then select the Duplicate Values rule as shown above. From here I can click OK and then see where the duplicates are (if any).
Now I can see that a duplicate entry was made to my data list so I can simply delete it and have accurate data! What makes conditional formatting especially helpful is that you can also stack conditional formatting rules on top of each other. In this example, let's keep the duplicate rule on the data list but also add in a rule for any pet visits that are over $100. To do this you will need to have the data set highlighted like in the last example but add on a rule from the "greater than" drop-down.
Since conditional formatting will typically auto default to filling in cells with red we want to adjust this to green as a means to differentiate the conditional formatting rules. This can be customized for any of your formatting needs.
This rule can easily be used to highlight anything in a "less than" conditional formatting.
Our last example here is regarding highlighting something in a data range. Here I will be highlighting items from 2019-2023. This can be done by highlighting the years in Column D of our example data list.
If you make a conditional formatting rule or two but need to remove one all you need to do is have the data set highlighted where the rules are applied and select manage rules as seen below:
From here you can select which rule you wish to delete. You can also modify any rules here as well.
To summarize, conditional formatting has so many purposes that it is a very customizable tool. In my own experience I've used it for investigating duplications in data and for calling attention to specific data points in summary tables. I hope this information is useful for you!
Comentários