Excel is one of the most popular Office applications, if the email I receive from readers is any indication. This month, two Excel users asked for guidance using two of Excel's most popular features — conditional formatting and filtering. Susan needed a conditional format that compared dates and Mir was hoping for a macro that would help simplify a charting problem that relied on filtering. For your convenience, you can download the example .xlsm or .xls file.
Conditional format that compares dates
Susan uses Excel to track tasks. Each task has a start and end date; a matrix to the right lists the first day of each week. She asked for a conditional format that can highlight the corresponding matrix cell when the start date falls within a given week. The solution is to compare both dates. It doesn't matter if they're the same date. We only need to know when both dates fall within the same week. This is easily managed with a simple comparison expression using WEEKNUM():
WEEKNUM() returns a value that represents the week number of a specific date within the year. For example, January 1 falls in the first week, so the function would return 1. Similarly, December 31, 2015 would return 53, and May 6, 2015 would return 19. When the results of both WEEKNUM() functions return the same value, because they occur within the same week, the expression returns True.
The WEEKNUM() function uses the following syntax:
where serialvalue is a valid date and returntype is an optional value that determines the first day of the week. The default is 1, which specifies a Sunday. (The European week-numbering system is more specific, which you'll want to consider if working in that system.) We'll be using the default, but you'll want to keep this optional argument in mind when applying it to your own work.
We're going to take an extra step so you can see how the formula works — seeing results often clarifies things best. To illustrate, Figure A shows the result of entering the following formula into C3 and filling the matrix:
(You don't have to do this, I'm just illustrating how the expression works.) When the two dates match, the formula returns TRUE.
The WEEKNUM() expression returns TRUE when the start date falls within the same week as the matrix date(s) in row 2.
You probably don't want a bunch of TRUE and FALSE values though, because these are just for show. If you entered them, delete them before continuing.
Now, we need to express the above expression as a conditional formatting rule as follows:
- Select the matrix. In this case, that's C3:X10.
- Click Conditional Formatting in the Styles group (on the Home tab), and then choose New Rule.
- In the top pane of the resulting dialog, click Use a formula to determine which cells to format.
- Enter the following formula: =WEEKNUM($B3)=WEEKNUM(C$2)
- Click Format.
- Click the Fill tab, choose a color, and click OK to return to the first pane (Figure B).
- Click OK. Figure C shows the resulting rule at work.
Note: This rule doesn't work in Excel 2003. The WEEKNUM() function is available via the Data Analysis Pack, but the conditional formatting feature won't support it.
You might have noticed that there's no highlight in row 3. That's because the start date for the first project falls in the first week of the year, and the first date in the matrix represents week 2. There's no matrix column for the first three days of the year. There's a simple, but inconsistent fix for the problem. You could insert a column to the left of column C and insert the matrix date of 1/1/2015 and then adjust the conditional rule and the range it applies to accordingly. You'll want to plan for potential holes before setting up your rule, when possible.
To learn more interesting ways to use Excel's conditional formatting feature, read "10 cool ways to use Excel's conditional formatting feature."
Mir has an interesting requirement for a line chart that tracks monthly values. Sometimes, there's no value for a specific month, but Excel still charts those blank values. It's easy to remove the value from the plot area, but it isn't so easy to remove the labels from the X axis. Figure D shows a simple example. As you can see, one month is 0 and two are blank. (Realistically, you probably wouldn't have both, but I want to cover both possibilities.)
This graph displays labels on the X axis when there's no corresponding series value.
In this example, the chart plots the 0 value but not the blanks, which is what you might expect. However, the labels for the blank values appear on the X axis. You might want to hide the entire month when there's no value. The truth is, I don't know of any setting or feature that will let you hide the labels on the X axis when there's no corresponding series value to chart, but I'm not saying it can't be done.
Filtering the blanks is an easy solution. As you can see in Figure E, the chart updates automatically when you add a filter that eliminates the blanks.
Filtering is an easy solution for hiding X labels when there's no series value.
To setup this filter, do the following:
- Click any cell in the data set. Click Filter in the Sort & Filter group on the Data tab. In Excel 2003, select the data set, choose Filter from the Data menu, and then choose AutoFilter.
- Click the Amount filter's drop-down arrow and unselect blanks (Figure F). Choose (Nonblanks) in Excel 2003.
- Click OK to see the results shown above in Figure E.
The filter works for Mir, but he regularly adds to the data set. That means he has to clear the filter, enter records, and apply the filter each time he wants to update the chart. That's not a huge deal, but we can make that task easier by adding a simple macro. To add this macro, do the following:
- Press [Alt]+[F11] to launch the Visual Basic Editor (VBE).
- In the VBE, choose Module from the Insert menu.
- In the new module, enter the appropriate-versions sub procedure in Listing A.
- Return to the Excel sheet. If you're using the .xlsx format, save the file as a macro-enabled file. Click the File tab and choose Save As. From the Save As Type drop-down, choose Excel Macro-Enabled Workbook (*.xlsm), and click Save.
.xlsm version Public Sub FilterOutBlanks() 'Filter out blanks and 0s. ActiveSheet.Range("$A$1:$B$13").AutoFilter Field:=2, Criteria1:="<>", _ Operator:=xlFilterValues 'ActiveSheet.Range("$A$1:$B$13").AutoFilter Field:=2, Criteria1:="<>0", _ 'Operator:=xlAnd, Criteria2:="<>" End Sub .xlx version Public Sub FilterOutBlanks() 'Filter out blanks and 0s. Range("A1:B13").Select Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="<>" 'Selection.AutoFilter Field:=2, Criteria1:="<>0", _ 'Operator:=xlAnd, Criteria2:="<>" End Sub
This sub procedure (macro) filters the dataset. When applying this to your own work, update the range and the AutoFilter arguments accordingly. The Field argument identifies the column within the data set that you're filtering. In this case, that's the Amount column, the second column in the range. The criteria is the "not equal to" operator, so this simple expression omits blank cells. The quotation marks are necessary. The commented statement at the bottom is an alternative to the one in use and filters 0 and blank values.
Before running the macro, clear the filter so you can see that it's actually working. To do so, click the Data tab, and then click Clear in the Sort & Filter group. Notice that the chart updates accordingly, and you can see the blank labels on the X axis again. To run the macro, click the Developer tab, select FilterOutBlanks, and click Run. In Excel 2003, you'll find macros on the Tools menu.
Using this macro, you can add to the dataset and quickly reset the filter with a quick click once you add it to the Quick Access Toolbar (QAT) as follows:
- From the QAT drop-down, choose More Commands.
- From the Choose Commands From drop-down, choose Macros.
- In the list on the left, find FilterOutBlanks.
- Click Add to add the macro to the list of commands on the right.
- Click OK.
(Note: There's no QAT in Excel 2003.) To learn how to suppress 0 values in a chart, read "How to suppress 0 values in an Excel chart."
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at firstname.lastname@example.org.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.