With conditional formatting, you can change colors and styles of a cell based on the results of a formula. Formulas return values, and Excel's conditional formatting features will take these results and change a cell's style based on a true or false value. It's not uncommon to use conditional formatting features when you have large, dynamic spreadsheets that alert you to specific values using changed background colors, borders and fonts.
Standard Styles
Before you work with conditional styles, you should also know that Excel has standard styles that you can quickly add to your cells. Styles, colors, borders and font changes are found in the "Home" Excel tab in the main menu. Conditional styles as well as premade ones can be found in the "Styles" section of the "Home" tab.
To see a list of premade styles, click the "Cell Styles" button.
(Standard cell styles)
The default style is the upper-left corner style named "Normal." You can click any of these styles, and Excel will automatically format the selected cells. This feature is useful if you don't want to take the time to create designs and styles for your spreadsheets. These styles make it easy to make your spreadsheet attractive without the need to design colors, fonts and borders on your own.
Conditional Formatting Features
Before you decide on your formatting rule, you must highlight the cells that you'll use. You can use conditional formatting for text, but most conditional formatting is used on money or numeric values. Formulas are used to set the conditional formatting, so you also need to know what type of data rules that you want to set to change cell styles.
With he cells highlighted, click the "Conditional Formatting" button in the "Styles" menu section. Click "Highlight Cells Rules" to see a list of options.
(Conditional formatting option list)
Notice that the options for highlighting cells follows the basic calculations available with many of the pre-existing mathematical functions. You can set formatting using:
- Great Than
- Less Than
- Between
- Equal To
- Text that Contains
- A Date Occurring
- Duplicate Values
Each one of these conditions must be configured when you choose the one that you want to use. Excel can work with number values, text, and dates. The duplicate values feature will detect cells that have duplicate text or numbers so that you can eliminate them from your records or make them stand out.
Greater Than Formatting
With the greater than formatting option, you can set a style on a cell based on if a value is greater than another value. Click this option from the dropdown and a configuration window opens.
(Greater Than formatting configuration)
When you're working with conditional formatting features, you can see which cells will change as you work with the configurations. With a preview, you can see what cells will be highlighted before you save the configurations. The number shown in the value text box in the configuration window is an average of the highlighted rows. This allows you to quickly set conditional formatting based on numbers that are higher than the average. For instance, $87.50 is the average payment for utilities, so Excel sets this as the default and highlights all values in red that are higher than the average.
You can place any value in the text box to instantly see data change in the highlighted cells if the meet the condition. The right side of the configuration is a dropdown where you can choose the color of the highlight. When you click this dropdown, a number of colors for both fonts and the background display. You can choose your own style by clicking the "Custom Format" option. When you chose this option, a window opens where you can format fonts, borders, background colors and text size.
After you finalize your settings, click "OK" and the changes take effect. This is the way most conditional formatting rules work.
Interested in learning more? Why not take an online Advanced Excel 2019 course?Less Than Formatting
The less than formatting feature works the same way as the greater than feature, except cell styles are changed when the value stored is less than the conditional value. Click the option from the "Conditional Formatting" dropdown and the configuration window opens.
(Less Than formatting configuration)
If you use the same highlighted cells to perform the conditional formatting, you'll notice that the average value is automatically filled in the text box and now the cells that were not stylized using the greater than feature are now highlighted in red. Just like the greater than option, you can change the colors using the dropdown on the right side of the configuration window. You can also customize the formatting by choosing the "Custom Format" option in the dropdown and selecting the styles that you want to use in the window that displays.
Between Formatting
With some spreadsheets, you might not want to know if a value is just higher or lower than another. You often want to know when a value is between a start and stop value. You can set styles based on a value that is between two other values using the between conditional formatting feature.
The between conditional formatting works similarly to the previous two greater than and less than features. You click this option from the "Conditional Formatting" dropdown and a configuration window opens where you set your values and formatting styles.
(Between formatting configurations)
Because the between conditional formatting evaluates if a number is between two other values, you must set the two values in this configuration window. Excel makes an assumption for the numbers that you might want to use in the configuration window and adds them to the two text boxes. You can see if any values match the default configuration as they will be shown in red. If you want to use different values, enter them in the first two text boxes.
Just like the other conditional formatting options, you can use the dropdown to change the color and font styles. The dropdown contains the same options that you saw in the previous two greater than and less than conditional formatting features. Again, you can choose your own custom colors, fonts, borders and background by click the "Custom Format" from the dropdown selections.
You can see the changes in the preview of your spreadsheet but click "OK" to finalize them and apply changes to the highlighted cells.
Equal To Formatting
Equal to formatting lets you set a style for highlighted cells only if they equal a value set in your configurations. The value must be exactly the same as the one in your conditional formatting configuration or the cell will not be stylized.
(Equal To formatting configurations)
Excel 2019 uses the average again to fill in the text box with a default value, but you can enter any value into this text box. Use the dropdown to select a color for fonts or the background. When you finish configuring your styles, click the "OK" button to finalize them and apply changes to your spreadsheet.
Text That Contains Formatting
When you're not working with numbers, you might need to perform conditional formatting based on text values stored in a cell. You can dynamically change text formatting using the "text that contains" formatting option. This option does not affect cells that contain numbers, so you would only use this option if you have a column or row of data in text format. Click this option from the dropdown to open the configuration window.
(Text That Contains formatting configuration)
Remember that string values are case sensitive. This means that the term "Payment" and "PAYMENT" and "payment" are all different values to Excel 2019. You can use functions such as UPPER and LOWER to evaluate text that is first changed to upper or lowercase and then evaluated. This will ensure that your condition is first compared to normalized text. If you know the format of the text, make sure you enter the right string with the right upper and lowercase letters included in the value.
After you enter the string value that you want, use the dropdown to select a style. This style can be any one of the premade styles in the dropdown, or you can customize your own by clicking the "Custom Format" option.
Date Occurred Formatting
For spreadsheets with dates and deadlines, you can use conditional formatting to highlight cells that contain dates within a specific range. Conditional formatting based on dates must be within a preset range in Excel. Excel offers several options in the configuration dropdown, but you can't specify a specific date. Choose the date occurred option from the "Conditional Formatting" dropdown and a configuration window displays.
(Date Occurring formatting configuration)
In the configuration dropdown, notice that you only have a specific timeframe. This is the one limitation to the date occurring conditional formatting. If the highlighted cells don't have dates stored in them, then the conditional format options will not take effect. In the example, the highlighted cells are numeric, so adding a conditional format feature will be ignored on these cells.
Duplicate Formatting
When you have numerous rows and columns of data, you might need to know when some of this data is duplicated. For instance, you wouldn't want to accidentally count a duplicate customer order when tallying revenue for the quarter but exporting data to Excel can occasionally have mistakes when you store it differently in a database. Instead of searching through thousands of rows or sorting data and then reviewing it, you can have Excel find duplicate content for you by using the duplicate conditional formatting feature.
Choose the duplicate formatting option from the dropdown and a configuration window displays.
(Duplicate Values formatting configuration)
You aren't limited to just finding duplicate values. You can also find unique values within a list of cells. The dropdown in the configuration window lets you choose either to find duplicates or unique values. Choose which one that you want to use and then select the formatting option in the dropdown on the right of the configuration window.
Additional Formatting
These main conditional formatting features aren't the only ones available. Excel offers some other less-common features to conditionally format data.
(Conditional Formatting dropdown options)
These additional conditional features stylize cells based on conditions not found in the main rules. You can format cells based on a sort order and create rules that have data bars and color scales unique to basic solid colors. If you can't find a rule that you're happy with, you can also set a new rule. A new rule will let you conditionally format based on your own logical input and output. The "New Rule" option opens a configuration window where you can create a custom rule without any Excel 2019 presets. Use this option if you can't find a rule that fits your requirements in the Excel preset options.
