Hi fellow Excel Addict,
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you 'Two Easy Methods to Extract Unique Items From a List'. You can read all about it here.
Quote of the Day
Every week I receive emails from readers who say they love the quotes in my newsletter. Often a particular quote will 'strike a chord' with them. I find that reading quotes breaks me out of my routine for a few moments and prompts me to reconsider some of my own limiting beliefs. I typically share quotes on topics such as persistence, success, gratitude, goals, knowledge and sharing.
Today I am including one of my favourite quotes by Mark Twin. So if you have been putting off some of your life goals, maybe this quote will speak to you as well.
Have a great week and keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Highlight Upcoming Dates in Real Time
I am often asked if it is possible to have upcoming dates highlighted based on the current date in 'real time'.
The answer is Yes and the solution can be found in Excel's Conditional Formatting.
Let's look at this example for Excel Cable TV. Lately, some of their customers have been canceling their cable TV service and switching to NetFlix and other online streaming services. The service techs are having trouble keeping track of projects, so their boss wants them to instantly be able to see which projects are due in the next week or two.
She's asked to have due dates that are within 7 days from the current date to be highlighted in red and dates that are between 8 and 14 days from the current date, she wants highlighted in yellow.
We can use Excel's Conditional Formatting to do this. However, since there are multiple criteria, we will be doing this in multiple steps. Let's start...
Highlight dates that are within 7 days of today
1) Select all of the date cells where you want to apply this formatting;
2) From the Home tab, click Conditional Formatting, then click New Rule... The New Formatting Rule dialog appears;
3) In the 'Select a Rule Type' section, select 'Format only cells that contain';
4) In the 'Format only cells with' section, select Cell Value from the first dropdown, 'less than or equal to' from the second dropdown and type the formula =TODAY()+7 in the third field. TODAY() is an Excel function that returns the current date (in this example, the current date is April 7). So, the formula TODAY()+7 means '7 days from now'. If the Conditional Formatting rule 'Cell value is less than or equal to TODAY()+7' evaluates to True, formatting will be applied to that cell;
5) The next step is to choose the format to apply to cells that meet this condition. To do this, click on the Format... button;
6) In the Format Cells dialog, click the Fill tab and choose a background color. If you use a dark color (e.g. red) for the background, you may prefer to select a lighter font color (e.g. white) from Font tab;
7) Click OK to close the Format Cells dialog and you will see a preview of the cell formatting you selected;
8) Click OK and you will be returned to the worksheet and any dates within 7 days of the current date should be highlighted.
Next, Highlight dates that are within 14 days of today
1) With the date cells still selected, click Conditional Formatting on the Home tab, then click New Rule... The New Formatting Rule dialog appears;
2) In the 'Select a Rule Type' section select 'Format only cells that contain';
3) In the 'Format only cells with' section, select Cell Value from the first dropdown, 'less than or equal to' from the second dropdown and type the formula =TODAY()+14 in the third field';
4) Click on the Format... button and choose the formatting you want to apply to cells that are within 14 days of now;
5) Click OK to close the Format Cells dialog, then click OK again and you will be returned to the worksheet and all dates within 14 days of the current date should be highlighted with a yellow background.
I know! I know! The dates within 7 days aren't red anymore! So let's see why?
Changing the Order of Conditional Formatting Rules
1) With the cells still selected in the worksheet, on the Home tab, click Conditional Formatting, then select Manage Rules... The Conditional Formatting Rules Manager dialog appears. You can now see the two rules we previously added ;
2) When we apply two or more rules to the same range of cells, the newer rules are always added at the top of the list in the Rules Manager. Rules are then evaluated in the order they are listed, with precedence given to the higher rules.
Since our '14 day' rule was added last, it is evaluated first. For cells where this rule =TODAY()+14 evaluates to True, the yellow background formatting is applied. If a second or subsequent rule also evaluates to True, the formatting for that rule will be applied as well, unless there is a conflict between the two formats. In this example, the first rule sets the cell's background color to yellow and the second rule sets the cell's background to red. Because of these conflicting formats the first rule takes precedence and only the yellow background format is applied.
Obviously, there is something wrong with this. The reason is the way we have applied these rules.
There are a couple of ways to correct this. The preferred way is to add the rules in the correct order when they are being initially set up. In this example, if we had added the '14 day' rule first and then the '7 day' rule last, everything would have been fine.
Another way to correct this is to change the order of precedence by (1) selecting the '7 day' rule in the Rules Manager dialog and moving it to the top by (2) clicking the Move Up arrow, giving it precedence.
Now the cells are formatted correctly since the '7-day' rule is being evaluated first.
Projects due within 7 days are highlighted in red and projects due within 14 days are highlighted in yellow. Each passing day, these rule will be evaluated based on the current date because of the TODAY() function used in the conditional formatting rules.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630