Excel Conditional Formatting Guide

[nerdslife_team]

Conditional formatting in Excel is a great way to automatically change how cells look based on certain rules. Think colors, icons, data bars—all designed to highlight the important stuff in your data.

By setting up these rules, Excel will format cells according to their values. If a cell meets your condition, it gets formatted; if it doesn’t, it stays as it is. This can make data analysis a lot easier and quicker.

Let’s explore how you can use conditional formatting to highlight cells based on different conditions, making your data much more understandable.

Highlighting cells based on rules

Let’s say you have a spreadsheet with sales data, and you want to quickly see which sales are below $500. Conditional formatting can help!

1. Select the range of cells you want to format. In our example, select column D, which has the ‘Amount’ values.
   

   

2. Go to the Home tab on the ribbon, then click Conditional Formatting. Hover over Highlight Cells Rules and pick Less Than… You’ll see several options.
   

   

3. In the ‘Less Than’ window, type 500 in the value field. Choose how you want the formatting to look using the dropdown, or create your own custom format.
   

   

4. Click OK. Now, all the sales amounts below $500 will be highlighted based on the style you chose.
   

You can also use conditional formatting to highlight text. For instance, if you want to find all employees in New South Wales (NSW):

1. Select the column or range with the location info.
   

   

2. Go to the Home tab, click Conditional Formatting, hover over Highlight Cells Rules, and then choose Text that Contains…
   

   

3. In the dialog box, type NSW and select a formatting style, then click OK.
   

   

Now, every cell containing ‘NSW’ will be highlighted, so it’s easy to see who’s located there.

Using top/bottom rules for conditional formatting

Excel’s top/bottom rules are great for finding the highest or lowest values in your data. These can highlight the top or bottom n items, percentages, or values above or below average.

For example, if you want to highlight the top 10 students based on their total score:

1. Select the range containing the total marks, which would be the ‘Total’ column in this scenario.
   

   

2. Go to Home, click Conditional Formatting, hover over Top/Bottom Rules, and choose Top 10 Items…
   

   

3. In the dialog box, you can change the number of top items to highlight, if needed. Select a style from the dropdown, and click OK.
   

   

Now the top 10 values are highlighted, which helps you easily spot the best students.

You can also highlight values above or below the average. For instance, let’s highlight students who scored below average in ‘Exam 1’:

1. Select the ‘Exam 1’ column.

2. Go to Home > Conditional Formatting > Top/Bottom Rules > Below Average…
   

   

3. In the dialog box, choose a preset format or create a Custom Format… then click OK.
   

   

Now, all cells below the average will be formatted according to your style choice.

Applying data bars

Data bars are great for visually showing the size of a number compared to other numbers. The length of each bar represents how big the number is.

1. Select the range of cells that you want to visualize with data bars.

2. Go to Home, click Conditional Formatting, hover over Data Bars, and choose either a gradient or solid fill.
   

   

Data bars will appear in the cells, instantly showing a visual comparison of the values. Data bars are applied to all the selected cells, not just cells meeting a condition.

Applying color scales

Color scales use a range of colors to visualize your data. The colors change based on the size of the number, with one color for the highest numbers and another for the lowest.

1. Select a range of cells to analyze.

2. Go to Home, click Conditional Formatting, hover over Color Scales, and pick a color scale.
   

   

For example, using the red-yellow-green scale will make high values green, low values red, and medium values yellow.

Applying icon sets

Icon sets add symbols to cells based on their values. These symbols can be arrows, shapes, or ratings, giving you quick visual cues about your data.

1. Select the range of cells that you want to format using icon sets.

2. Go to the Home tab, click Conditional Formatting, hover over Icon Sets, and pick a style. For our example, we’ll select the first option under ‘Directional’.
   

   

Icons will appear in the cells representing the relative value. Upward arrows might indicate higher values, horizontal arrows middle values and downward arrows lower values.

Removing conditional formatting

To get rid of conditional formatting, just clear the rules. Here’s how:

1. Go to the Home tab, click Conditional Formatting, and select Clear Rules from the dropdown.
   

   

2. Choose if you want to clear the rules from just the selected cells or the entire sheet.
   

   

That’s it! You can now make your data in Excel more dynamic and easier to analyze using conditional formatting.