Pinterest Stumbleupon Whatsapp
Ads by Google

Microsoft Excel is a vibrant tool that is used for both business and personal tasks How To Use Microsoft Excel To Manage Your Life How To Use Microsoft Excel To Manage Your Life It's no secret that I'm a total Excel fanboy. Much of that comes from the fact that I enjoy writing VBA code, and Excel combined with VBA scripts open up a whole world of possibilities.... Read More . One robust feature is called conditional formatting. This feature can come in handy for everyday situations as well as work-related ones. But, it can also be overwhelming if you have never used it.

Here are some general uses for conditional formatting, how to set them up, and of course, what exactly this feature is capable of doing.

Conditional Formatting Explained

This wonderful Excel feature applies formatting to cells based on the data populated. Whether you enter a number, percentage, letter, formula 16 Excel Formulas that Will Help You Solve Real Life Problems 16 Excel Formulas that Will Help You Solve Real Life Problems The right tool is half the work. Excel can solve calculations and process data faster than you can find your calculator. We show you key Excel formulas and demonstrate how to use them. Read More , or word, the formatting will be applied automatically per the rules you set up.

For instance, if you want the cell to be highlighted in red every time you enter the letter A, this is a simple setup.

Excel Conditional Formatting New Rule

The tool has a variety of settings, options, and rules that can save time in spotting important items. And, for those who are familiar with the feature, it can be used quite extensively. However, it can also be advantageous for everyday tasks for school, home, work, and personal activities and for those who have never used it before.

Ads by Google

For School: See Things Simpler

Whether in high school or college, you may keep track of your assignments, due dates, and grades in Excel. With a couple of convenient conditional formatting options, you can quickly see important items at a glance.

For due dates, you can apply formatting in a variety of ways to highlight what is past due, due tomorrow, or due in the next month. It just just takes a couple of steps:

  1. Select the cells where you will be adding the due dates.
  2. Click Conditional Formatting on the Home tab.
  3. In the Highlight Cells Rules section, pick A Date Occurring.
  4. 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 in a fast and easy way. Assignments that are past due or due tomorrow will pop right out.

Excel Conditional Formatting -- School Due Dates

For grade tracking and spotting your lower grades easily, 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. Click Conditional Formatting on the Home tab.
  3. In the Highlight Cells Rules section, select Between.
  4. 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.

Excel Conditional Formatting -- School Grades

For Home: Highlight What’s Important

Excel’s conditional formatting can be just as helpful if you use the application at home. For finances, home projects, and task lists 10 Amazingly Useful Spreadsheet Templates to Organize Your Life 10 Amazingly Useful Spreadsheet Templates to Organize Your Life Is your life a hotch-potch of missed deadlines, forgotten shopping and reneged commitments? Sounds like you need to get organized. Read More , make those important items pop with a few simple steps.

If you use it for your monthly budget, for example, 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.
  3. In the Highlight Cells Rules section, select Less Than.
  4. Then, add 0 with the highlight you would like. In this example, we selected Custom Format with Yellow Fill to make those cells really stand out.

This provides a fast method for seeing negative numbers within your budget. And remember, you can apply whatever type of formatting makes it more noticeable to you from highlight to text to border color.

Excel Conditional Formatting -- Home Budget

Excel is a popular tool for to-do lists as well and there is no better way to see items you have and have not completed 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 Conditional Formatting on the Home tab.
  3. In the Highlight Cells Rules section, scroll down to More Rules.
  4. In the pop-up window choose Format only cells that contain.
  5. At the bottom under Edit the Rule Description, select Blanks from the drop-down box.
  6. Then, click Format to pick the highlight you would like to apply, for instance, fill the cell with orange.

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

Excel Conditional Formatting -- Home Tasks

For Personal: Make Goals Pop

Many people keep careful track of their personal goals. From workout sessions to weight maintenance to calorie counting 10 Excel Templates To Track Your Health and Fitness 10 Excel Templates To Track Your Health and Fitness Read More , you can see how well you are meeting those goals with cool formatting options.

For counting and tracking calorie intake, apply formatting to see when you go over and under your goal or limit 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 Conditional Formatting on the Home tab.
  3. In the Highlight Cells Rules section, select Greater Than.
  4. 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.

Excel Conditional Formatting -- Personal Calorie

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

  1. Select the same cells as in the first rule.
  2. Click Conditional Formatting on the Home
  3. In the Highlight Cells Rules section, select Less Than.
  4. Then, add the calorie goal number with the highlight you would like. In this example, we selected 200 with Green Fill.

Excel Conditional Formatting -- Personal Calorie 2

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 into 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.

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

  1. Select the cells where you will be entering the number of hours.
  2. Click Conditional Formatting on the Home tab.
  3. In the Data Bars section, select either Gradient Fill or Solid Fill, depending on your preference.

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 totals you have calculated at the bottom. This is a nice way to see your heaviest and lightest work days, weeks, or months.

Excel Conditional Formatting -- Work Hours

Another great use of conditional formatting for business is for managing projects 15 Useful Excel Templates for Project Management & Tracking 15 Useful Excel Templates for Project Management & Tracking Read More . Using indicator shapes and highlighting for items such as priority and effort can be beneficial, especially 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 will be entering the numbers.
  2. Click Conditional Formatting on the Home tab.
  3. In the Icon Set section, select the shapes you would like to use.

Excel Conditional Formatting -- Work Priority

Now as you enter the numbers 1, 2, and 3, you will see the shapes adjust their color 3 Crazy Excel Formulas That Do Amazing Things 3 Crazy Excel Formulas That Do Amazing Things I have always believed that Excel is one of the most powerful software tools out there. It's not just the fact that it's spreadsheet software. No, Microsoft Excel 2013 simply has an awesome collection of... Read More . Keep in mind that 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. You can also reverse the order of the shapes and assign specific numbers by selecting Conditional Formatting > Icon Sets > More Rules.

For setting the effort of a project or task, you can apply formatting using a specific word such as small, medium, or large.

  1. Select the cells where you will be entering the effort word.
  2. Click Conditional Formatting on the Home tab.
  3. In the Highlight Cells Rules section, select Text That Contains.
  4. Type in the word you want highlighted, such as Small and then choose the format, such as Yellow Fill. You can do the same for medium and large to give them each a different color.

With this type of rule set up, all you have to do is type in a word and the cell will be automatically formatted for you. This makes viewing your project or task efforts with a glance extremely easy.

Excel Conditional Formatting -- Work Effort

Are You Ready to Apply Conditional Formatting?

Have you tried conditional formatting for everyday activities? Or are you ready to start having some fun with this feature?

Share your thoughts with us in the comments below!

Leave a Reply

Your email address will not be published. Required fields are marked *