Jannah Theme License is not validated, Go to the theme options page to validate the license, You need a single license for each domain name.
منوعات تقنية

How to Use Conditional Formatting in Microsoft Excel


You don’t have to manually format your data with colors and styles to make it stand out. With conditional formatting in Microsoft Excel, you can automatically format your dataset based on conditions. Plus, Excel gives you five preset conditional formatting options to make this task an easy one.

Note: keep in mind with all the conditional formatting rules below that if you change the cell data, and it no longer meets the criteria, the formatting will be removed.

Highlight Cell Rules

Using rules to highlight cells based on a value, text, or date is probably the most popular type of conditional formatting in Excel. With this option, you can do things like highlight cells with values that are greater than or less than another, text that contains something in particular, a date occurring in a certain time frame, or duplicate values.

Highlight Values

If you’re using values like numbers, currencies, or percentages in your sheet, you can highlight values as they relate to others. You could format sales greater than or inventory less than a given amount.

  1. Select the cells you want to test, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, select “Highlight Cell Rules,” then choose the condition you would like to use.
  1. Enter the comparison value in the field on the left of the pop-up box. Alternatively, enter a cell reference containing that value. For instance, we’re highlighting cells in our selected range that are greater than “3,000.”
Highlight Cell Rules value field
  1. Choose the highlighting to apply in the drop-down list to the right. We’re choosing “Light Red Fill.”
Highlight Cell Rules value format box
  1. Click “OK” to apply and save the formatting.
Highlight Cell Rules preview and OK button

Highlight Text

Perhaps it’s particular text in your cells that you want to highlight. You can use a word or even a single letter for your condition. You could format names of employees, cities or states, or letter grades.

  1. Select the cells you want to check, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, select “Highlight Cell Rules,” and choose “Text That Contains.”
Highlight Cell Rules Text Contains option
  1. Enter the text in the field on the left of the pop-up box. For example, we’re highlighting cells that contain the letter grade “A.”
Highlight Cell Rules text field
  1. Choose the highlighting in the list on the right. We’re choosing “Green Fill with Dark Green Text.”
Highlight Cell Rules text format box
  1. Click “OK” to apply your formatting.
Highlight Cell Rules text formatting applied

Highlight Dates

Another way to highlight values is using dates. You could choose a specific color for due dates for upcoming bills or tasks with dates from the past.

  1. Select the cells you want to check, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, select “Highlight Cell Rules,” and choose “A Date Occurring.”
Highlight Cell Rules Date Occurring option
  1. Choose a time frame in the pop-up box. As an example, we’re choosing “This Month.”
Highlight Cell Rules Date box
  1. Choose the highlighting from the list on the right. We’re selecting “Yellow Fill with Dark Yellow Text.”
Highlight Cell Rules Date format box
  1. Click “OK” to save the formatting.
Highlight Cell Rules Date formatting applied

Tip: if you’re interested in highlighting duplicate values in your sheet using that conditional formatting option, check out our complete how-to for finding and removing duplicates in Excel.

Apply Top or Bottom Rules

If you want to highlight things like top sales, bottom test scores, or above average quarterly profits, you can use the Top or Bottom Rules conditional formatting option.

Highlight a Top Percentage

Perhaps you’re looking for the top numbers from a group of cells. You can highlight a top percentage and adjust that percentage if you would like.

  1. Select the cells you want to test, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, move to “Top/Bottom Rules,” and choose “Top 10%.”
Top and Bottom Rule Top 10 Percent option
  1. Leave the number “10” in the field on the left of the pop-up box, or adjust that percentage by entering a number or using the arrows. For example, we’re changing it to view the top “20” percent.
Top and Bottom Rule percent field
  1. Choose the formatting you want to apply in the list on the right. We are choosing “Red Border.”
Top and Bottom rule percent format box
  1. Click “OK” to save and apply the formatting after seeing a preview.
Top and Bottom rule percent formatting applied

Highlight Below Average

You can also highlight the amounts below average.

  1. Select the cells, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, move to “Top/Bottom Rules,” and choose “Below Average.”
Top and Bottom rule Below Average option
  1. Choose the formatting you want to apply, then click “OK.” We’re choosing “Custom Format,” then selecting “Bold Italic” in the next window.
Formatting box
  1. Select “OK” to save the formatting you see in the preview.
Top and Bottom rule Below Average preview and custom format

Tip: did you know that you can also use conditional formatting in Microsoft Outlook to make certain emails pop? Take a look at our how-to for setting this up.

Use Data Bars

Another useful conditional formatting in Excel is Data Bars. You can apply colored bars to your cells, where the length of each bar represents the cell’s value. This offers a quick view of high and low numbers, like a city’s population, product inventory, or revenue by location.

  1. Select the cells you want to format, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, and select “Data Bars” to see gradient and solid fill options.
Data Bars options
  1. As you hover your cursor over each Data Bar option, you’ll see a preview in your selected cells.
Data Bars preview
  1. Click the style and color you want to use.
Data Bars applied

Note: instead of data bars, you could use Excel’s sparklines to spot a trend in your data. Sparklines are single cell mini charts that visualize a set range of data.

Add Color Scales

Maybe you like the splash of color for quickly identifying values, but prefer different colors to represent the values. With Color Scales, the colors change according to how high or low the values are.

This type of formatting is used with heat maps showing temperatures, populations, or income by region, but you can use it just as easily for sales, revenue, or expenses.

  1. Select the cells you want to format, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, and select “Color Scales” to see the color options.
Color Scales options
  1. Hover your cursor over each Color Scale to see a preview, then make your selection. You can use a two- or three-color scale, depending on the number of items you’re comparing.
Color Scales preview

The first color in the scale represents the highest value in the group. Large groups of items will show different shades of the colors as the differences between the values. As an example, we’re selecting the “Red Yellow Green” option.

Color Scales Red Yellow Green option

The highest values are in red, the middle values in yellow, and the lowest values in green, with the values in between in different shades of those colors.

Color Scales values

Tip: if you’re struggling to get the right results because your spreadsheet is a mess, try cleaning up your data first.

Insert Icon Sets

Another conditional formatting option you’ll see in Excel is the Icon Sets. This type of formatting places icons next to the cells in your group, based on their values.

The collection of icons you use is up to you, but Excel offers some assistance by categorizing them as Directional, Shapes, Indicators, and Ratings. Additionally, you’ll notice that some sets contain different numbers of icons, which is also useful in selecting a set.

Select the cells you want to use, go to the “Home” tab, open the “Conditional Formatting” drop-down menu, select “Icon Sets,” then select the icon set you would like.

Icon Sets options

As an example, we have a range of ratings for our cities, so we’re selecting one of the “Ratings” sets. Since our ratings are on a scale of one to three, the “3 Stars” set is ideal.

Icon Sets Ratings using stars

In another example, we have the number of members in each of our cities and want to quickly spot the highest and lowest. We’re choosing the “Directional” set and the “3 Triangles” option to see the highest numbers with a green up-pointing arrow, the middle numbers with a yellow rectangle, and the lowest numbers with a red down-pointing arrow.

Icon Sets Directional using triangles

How to Edit a Conditional Formatting Rule

Once you apply conditional formatting to your Excel sheet, you may want to make a change. Whether you’re making a change to the cell range or the formatting itself, you can edit an existing rule easily.

  1. Go to the “Home” tab, open the “Conditional Formatting” menu, and select “Manage Rules.”
Conditional Formatting menu Manage Rules option
  1. Use the drop-down box at the top to choose the rules you want to see. You can view them for selected cells, the current worksheet, or a specific sheet in the workbook.
Rules Manager Show Rules For box
  1. If you only want to adjust the cell range, do so in the corresponding field below “Applies to.” Otherwise, click “Edit Rule” at the top.
Rules Manager Edit Rule button
  1. Make your changes in the “Edit Formatting Rule” box that opens, then click “OK,” In our example, we’re changing the Icon Style.
Edit Formatting Rule box and OK button
  1. Click “OK,” then “Apply” to save your changes.
Rules Manager edited rule and OK and Apply buttons
  1. Your sheet will update with your edits.
Sheet with an edited Conditional Formatting rule

How to Delete a Conditional Formatting Rule

Should you decide you no longer want a conditional formatting rule you’ve set up, you can delete it one of two ways.

Use the Conditional Formatting Rules Manager

You can remove a rule in the Conditional Formatting Rules Manager with the click of a button.

  1. Go to the “Home” tab, open the “Conditional Formatting” menu, and select “Manage Rules.”
  1. Select the rule, select “Delete Rule” at the top, then click “OK” and “Apply.”
Rules Manager Delete Rule button

Use the Clear Rules Option

Another way to remove a conditional formatting rule is using the Clear Rules option.

  1. Go to the “Home” tab, open the “Conditional Formatting” drop-down menu, move to “Clear Rules,” and choose to clear the rules from selected cells, the entire sheet, a table, or a pivot table. Note that if you don’t have a rule set up for a particular action, it’ll be grayed out.
Conditional Formatting Clear Rules options
  1. Your rule will be removed.
Sheet with a cleared Conditional Formatting rule

Frequently Asked Questions

Can I have two conditional formatting rules for the same cells?

You can apply various conditional formatting rules to the same set of cells if you would like. For instance, you may want to highlight dates for last month in red text and dates for next month in green text. Follow the same steps you see here to add more rules to your selected cells, but be careful to not make your rules conflict with each other.

If it’s possible that all rules can apply, but it could cause a formatting problem, select the first rule for the cell range in the Conditional Formatting Rules Manager, then check the box for “Stop If True.” This stops the remaining rules from running, should they also apply.

Can I create my own conditional formatting rule?

Along with the preset conditional formatting rules shown here, you can set up your own. Select the cells you want to use, go to the “Home” tab, and click “Conditional Formatting -> New Rule.” Choose a rule type at the top, set up the rest of the rule in the section at the bottom, then click “OK” to save it.

Can I use conditional formatting to highlight an entire row or column?

You can set up a conditional formatting rule beyond a particular range of cells. So, yes, you can highlight an entire row or column based on conditions. Select the row or column, then set up your rule as described above. Keep in mind that if you have empty cells, you may not see formatting based on value, text, or date.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Sandy Writtenhouse
Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

اترك تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *

زر الذهاب إلى الأعلى