Look Who it Was Random video

Excel - Using Formula-Based Conditional Formatting in Excel - Episode 1007

747 views · Published 5 May 2009 · 3:25 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Using Formula-Based Conditional Formatting in Excel.

Welcome back to the MrExcel netcast! In today's episode, we're going to tackle a common problem faced by many Excel users - analyzing massive amounts of data. But don't worry, we've got a solution for you - pivot tables! So let's dive in and see how we can use them to solve a problem sent in by one of our viewers, William.

William wants to use conditional formatting to highlight rows based on a start date in column A and the duration in column B. This is a great way to identify projects that are past due or need attention. But before we jump into conditional formatting, let's first solve this problem using a simple formula in the spreadsheet. We'll use the TODAY function to get today's date and then add the duration to the start date to get the due date. Then, we'll use a formula to check if the due date is less than today's date. This will give us a range of TRUEs and FALSEs, indicating which projects are past due.

Now, let's move on to conditional formatting. We'll need to add some dollar signs to our formula to make sure it works correctly. Then, we'll select all the rows and go to "Conditional Formatting" and choose "New Rule". We'll use a formula to determine which cells to format and make sure the formula refers to the current cell. Then, we'll paste our formula and choose a formatting style - maybe a bold red with white font. And just like that, any projects that are past due will be highlighted in red.

For those of you using Excel 2003, the process is a bit different. You'll need to go to "Format" and then "Conditional Formatting" and choose "Formula Is" from the drop-down menu. Then, you can paste the same formula we used in Excel 2007. It's a bit more hidden, but still possible to achieve the same result.

I want to thank William for sending in this question and for all of you for tuning in. I hope this tip helps you better analyze your data and identify any projects that need attention. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time on the MrExcel netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


William asks how to base his conditional formats on a start date in column A and a duration in column B. This calls for the Formula version of conditional formatting. Episode 1007 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Introduction to the MrExcel netcast with Bill Jelen
(00:10) Solving a problem with conditional formatting
(00:24) Using conditional formatting to highlight rows based on start date and duration
(01:00) Creating a formula to determine if a project is past due
(01:34) Applying conditional formatting to the spreadsheet
(02:24) Changing the start date to see instant updates
(02:40) Using conditional formatting in Excel 2003
(03:01) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Applying formulas to determine due dates
Checking if a date is past due in Excel
Creating a range of True and False values in Excel
Finding past due projects in Excel
Formatting cells based on a dynamic formula in Excel
Formatting cells based on conditions in Excel
Highlighting rows based on start date and duration
Understanding conditional formatting in Excel
Using conditional formatting in Excel
Using dollar signs in conditional formatting formulas
Using the TODAY function in Excel
YouTube video tutorial on pivot tables


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152394/

More from this channel