What is Conditional Formatting?
Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets specific criteria. It serves to highlight important information and allows users to configure specific formatting to cells automatically.
Conditional formatting applies sets of rules to cells using basic, intermediate or advanced formulas that format the overall look of the spreadsheet. This can extend to cell colour, font colour or border style on a basic level, and can extend to other cells, rows or columns based on another cell’s value or contents. You can customise rules (also known as conditions) where you define when and how cells should be formatted.
All conditional formatting rules are applicable without the need to hard-code, as Excel has built-in parameters so you can simply select and manipulate the rules you want your data to meet.
When applied correctly, conditional formatting can save time and increase productivity in the workplace; with less attention spent on manually combining data and simple tweaks that make identifying trends easier will allow you to call visual attention to important information.
Simple Conditional Formatting
Conditional formatting, on the surface, calls attention to certain data points and makes current spreadsheet data more digestible. It is easy and efficient for visually highlighting cells that contain values meeting your conditions.
Some examples include:
- Highlighting cells that contain specific words
- Highlighting cells that exceed or do not meet specific amounts
- Highlighting cells that fall within a certain time frame
- Highlighting the top or bottom 10 values in a selection
Conditional formatting rules can include:
- Highlight rules – essentially highlighting cells a certain colour based on value and contents. Can include background, font and border. Colours and schemes can be added and manipulated to fit data that is greater than, less than, equal to or in between certain amounts, text that contains specific things, dates and duplicate values.
- Top/bottom rules – much the same as above, highlighting cells a certain value but with preset rules that include the top and bottom 10 items, top and bottom percentages, and above/below averages. As specified, these are all customisable.
- Data bars – creating gradient or solid fill bars to represent the value of the cells. In essence, cells containing higher amounts will be more filled up than those on the bottom end of the scale.
- Colour scales – similar to data bars, this rule allows the user to set a colour gradient to a range of cells, rather than a single colour.
- Icon sets – to represent values in specific cells, this formatting rule allows users to attach images and icons to the cells. For example, these icons can include directional images such as arrows, sets of shapes or other indicators.
The fact remains you are capable of selecting any other rule type that is appropriate for your data, and this extends to creating rules from scratch.
If none of the predefined rules in conditional formatting meet your needs, you can create a “New Rule” after selecting the cells you want the rules applied to, and have the option to format cells based on their values, that are above or below certain averages, duplicate values, and much more. The rules are easily deleted as well and can be re-applied or amended in a matter of seconds.
Using Formulas in Conditional Formatting
Conditional formatting rules can be added by way of creating formulas as well, rather than selecting conditions from drop-down lists. You are able to select “New Rule”, and instead of selecting specific options from the window, you can simply select “Use a formula to determine which cells to format”, and from there you can enter your formula and the format for said formula.
Formulas can be added for the following examples of conditional formatting:
- Values and comparisons
- AND/OR conditions
- Values based on conditions
- Highlight duplicates
- Text values
- Values above or below a certain average
There is a good chance you are aware of certain Excel formulas already, and it’s handy if you have this knowledge because if so, applying conditions just became more wholesome.
It’s important to remember to define the absolute and relative cell addresses correctly, as mixing these two cell references can often result in different outcomes. It’s always worth remembering to write any formula beginning from the top-left cell, as that is where cell references are relative to. If you’re using a complex Excel formula that includes various functions, it is ideal to split the formula into some simple and easy-to-verify elements.
As it happens, VBA coding can be used to do conditional formatting too, which is ideal if you are using VBA code to write specific codes for your spreadsheet. Conditional formatting may be limited somewhat in terms of what can be done with it via VBA, but the knowledge is handy if your data is not exhaustive or does not require vast amounts of rules.
Where Can I Learn More?
XL Intelligence offers bespoke Advanced Excel training courses and VBA training courses that can be customised to your needs, or standardised versions that include a workshop where your solutions can be worked out in a more practical way. Both the Advanced Excel and the VBA and Excel Automation training courses can incorporate elements of conditional formatting to suit your needs.
They can play a fundamental part in understanding all there is to know about technical Microsoft Office functions from professionals with in-depth knowledge, hands-on experience and how to apply these solutions in real-world situations.
Download Advanced Excel course outline here.
Download Excel Automation course outline here.