Microsoft Excel is a vibrant tool that is used for both business and personal tasks. One robust feature is called conditional formatting. This can come in handy for everyday situations as well as work-related ones.

We'll be taking a look at some general uses for conditional formatting, how to set them up, and of course, exploring a variety of tasks this feature is capable of doing.

Conditional Formatting Explained

Conditional formatting is a tool that can be used to apply a certain format to cells based on the data entered. For instance, you can color-code or highlight cells based on a set of rules.

Format Cells Using a Single Rule

You can apply this feature when you want to format cells based on one specific condition. If you want the cell to be highlighted in red every time you enter the letter A, this is a simple setup.

Conditional formatting using 3-color scale

Format Cells Using Multiple Rules

You might want to apply multiple rules to cells based on a set of conditions. Let's say you wish to cut down project expenses, so you can try to apply formatting in the Estimated Costs column to automatically highlight values between $10000 and $50000.

Using nested formulas for applying multiple rules in Conditional formatting

Here, you can use a nested formula—IF and AND functions to format cells. It's easy to do this:

  1. Select the cells to apply conditional formatting rules.
  2. Click on the Home tab > Conditional Formatting > New Rule.
  3. From the dropdown options, select Use a formula to determine which cells to format, and enter the following formula: 
    =IF(AND(C3>10000, C3<50000), TRUE, FALSE)
  4. Head over to Format > Fill and choose a fill color to use, and click OK.
Applying multiple rules and nested formulas in Conditional formatting

The tool has a variety of settings, options, and rules that can save time in spotting important items. It can also be advantageous for everyday tasks for school, home, work, and personal activities.

For School: Keep Things Simpler

You can keep track of your assignments, due dates, and grades in Excel. It's easy to apply formatting to highlight what is overdue, due tomorrow, or due in the next month, and it just takes a couple of steps:

  1. Select the cells where you will be adding the due dates.
  2. Go to the Home tab > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
  3. Then, choose your criteria, such as In the last 7 days with Red Text and Tomorrow with Yellow Fill.

This will help you see those important dates quickly. Assignments that are overdue or due shortly will pop right out.

Assignment tracking using Conditional formatting

For grade tracking, you can apply formatting for both letter and number grades that include a range.

  1. Select the cells where you will be adding the grades.
  2. Navigate to Home > Conditional Formatting > Highlight Cells Rules > Between.
  3. Then, add your values such as between C and F or 0 and 2 with Red Fill.

This gives you a nice and simple way to see how well your classes are going and where your grades are lower.

Tracking grades using Conditional Formatting

For Home: Highlight What's Important

Excel's conditional formatting feature can be just as helpful for managing finances, home projects, and task lists. If you feel like you're running behind on everything all the time, why not organize your life with these spreadsheet templates?

Manage Your Finances

If you use it for your monthly budget, you can spot trouble quickly. Whether you have created your own spreadsheet or are using a handy template, applying formatting to negative numbers automatically is useful.

  1. Select the cells where you will be entering the numbers or formulas.
  2. Click Conditional Formatting on the Home tab > Conditional Formatting > Highlight Cells Rules > Less Than.
  3. Then, add 0 with the highlight you would like. In this example, we selected Custom Format with Yellow Fill to make those cells noticeable.

You can apply any type of formatting to make it more noticeable to you, from highlight to text to border color.

Applying conditional formatting rules to monthly budgets

Create Personal To-Do Lists

There is no better way to see completed and pending items on your to-do lists than with automatic formatting. With this approach, any task that is not marked completed with an X will be highlighted.

  1. Select the cells where you will be entering the completed indicator (X).
  2. Click on the Home tab > Conditional Formatting > Highlight Cells Rules > More Rules.
  3. In the pop-up window, choose Format only cells that contain.
  4. At the bottom, under Edit the Rule Description, select Blanks from the drop-down box.
  5. Then, select Format to pick the highlight color (orange, for instance) and click OK.

Applying this formatting takes only a minute and can be very helpful for keeping track of tasks that you have yet to complete.

Using Conditional Formatting to check items in to-do lists

For Personal Use: Make Goals Pop

Many people keep careful track of their personal goals with spreadsheet templates. From workout sessions to calorie counting, you can see how well you are meeting those goals with some cool formatting options.

Track Your Calories

For tracking calorie intake, apply formatting to see when you go over or under your goal for the day. In this instance, we will apply two separate rules.

  1. Select the cells where you will be entering the number of calories.
  2. Click the Home tab > Conditional formatting > Highlight Cells Rules > Greater Than.
  3. Then, add the calorie goal number with the highlight you would like. In this example, we selected 200 with Red Fill. This will show when we go above the calorie limit.
Calorie tracking by applying automatic formatting in Excel

Next, set up the rule for going below the calorie limit.

  1. Select the same cells as in the first rule.
  2. Navigate to the Home tab > Conditional Formatting > Highlight Cells Rules > Less Than.
  3. Then, add the calorie goal number with the highlight you would like. In this example, we selected 200 with Green Fill.
Using Conditional formatting to track calorie goals

Keep in mind that in this example, if you enter the exact number 200 for the calorie count, no formatting will be applied. This is because the number falls under neither rule.

For Business: Bring Out the Boldness

For those who work remotely or as independent contractors, Excel may be used for such tasks as time tracking or project estimates. Here's how you can do this:

Apply Time Tracking With Data Bars

With a convenient way to keep track of hours worked as well as see a monthly or weekly overview, formatting with data bars can be quite useful.

  1. Select the cells where you will be entering the number of hours.
  2. Click Home tab > Conditional Formatting > Data Bars > Gradient Fill or Solid Fill, depending on your preference.
  3. Once you enter numbers into the designated cells, you will see the data bar adjust. The lower the number, the smaller the bar and vice versa. You can also apply the formatting to the totals you have calculated at the bottom.

This is a super-easy way to see your heaviest and lightest work days, weeks, or months.

Data bars for applying automatic formatting

Manage Project Priorities Using Icons and Shapes

Another great use of conditional formatting for business is for managing projects using Excel. Using indicator shapes and highlighting for items such as priority and effort can be beneficial when sharing the data with others.

For setting priorities using numbers with 1 as the highest priority in red and 3 as the lowest in green, you can apply shape formatting.

  1. Select the cells where you'll be entering the numbers.
  2. Navigate to the Home tab > Conditional Formatting > Icon Set.
  3. Select the shapes you would like to use.
Icon sets in Conditional Formatting

As you enter the numbers 1, 2, and 3, you will see the shapes adjust their color. If you use more than three numbers, which equals the three shapes, they will change accordingly. For instance, 1 will remain red, while 2 and 3 will turn yellow, and 4 will be green.

Using More Rules in Icon Shapes Conditional Formatting

You can also change shapes, reverse the order, or assign specific numbers by selecting Conditional Formatting > Icon Sets > More Rules.

Set Custom Text Rules

For setting the effort of a project or task, you can apply formatting using a specific word such as small, medium, or large. Here's how to do this:

  1. Select the cells where you will be entering the effort word.
  2. Go to the Home tab > Conditional Formatting > Highlight Cells Rules > Text That Contains.
  3. Type in the word you want to be highlighted, such as Small, and then choose the format, such as Yellow Fill. You can do the same for medium and large with different colors.

All you have to do is type in a word, and the cell will be automatically formatted for you. This makes it extremely easy to view your project or task efforts at a glance.

Setting Project Efforts in MS-Excel using Conditional Fomatting

Are You Ready to Apply Conditional Formatting?

From being able to keep track of when different assignments are due to monitoring your project's progress, conditional formatting can be quite useful for organizing a myriad of tasks.

Have you tried it managing for everyday activities? The options are endless when it comes to this feature, making it a central part of Microsoft Excel.