Show only rows in Excel with specific dates and colors

May 18, 2015 Phillip Burton Leave a comment

Introduction

This is a series of articles about filtering, which is the temporary removal of rows you don’t want to see, so that you can see those rows which contain data you specify.

Previous articles talked about setting up a filter, and then filtering a text column.

This article will investigate filtering a date column, and finally filtering on colors.

Using the check boxes – Dates

Clicking on the drop-down filter box shows the date data. At first, all you can see are the years, but you can click the + box to show the months, then the dates, hours, minutes and all the way down to the seconds.

As before, you can select multiple dates by clicking the appropriate check boxes.

In this way, you can de-selecting “(Select All)” to remove all the check boxes, and then click on “July” within “2014” to select 28, 29 and 30 July 2014 (and any other dates in July 2014 that may be there – did you see that “Not all items showing” sign?)

Date Filters

Alternatively, you can use the Date Filters. These are a series of pre-defined searches, together with one where you can define your own searches.

The searches are:

Equals. This means that the displayed value in the cell should be exactly the value that you enter.

It sounds like “Equals” should be fairly simple to define, but is actually pretty complex. That’s because it’s not based on the value that a cell actually IS, but is based on what it displays.

If your cells are formatted down to the minute (e.g. 03 Mar 2014 11:24), then your searches will be down to the minute. Any searches for “04 Apr 2014 11:24” will return cells which internally have “04 Apr 2014 11:24:00”, “04 Apr 2014 11:24:30” and “04 Apr 2014 11:24:59”, because they all show the same result to the minute.
If your cells are formatted down to the day (e.g. “02 Feb 2014”), then your searches will return all cells for that day. Therefore, a search for “01 Jan 2013” will return cells which internally have “01 Jan 2013 00:00:00” all the way to “01 Jan 2013 23:59:59”.

Therefore, how your cells are formatting is very important to this “Equals”, but also to the next 4 searches.

Before… – This shows all rows where the cells which are formatted prior to the date (or date/time) you enter. Note that it will not include the search results itself. So looking for “Before… 01 Jan 2014” will show everything from the year 2013 prior, but not New Year’s Day 2014 itself.
After… – This shows all rows where the appropriate cell is later than the date (or date/time), but not that date itself.
Between… – This, on the other hand, is inclusive of your date entries.

Custom Filter… – this is shown at the very bottom of the list. It is less useful with date data columns than for text data, but one of the major uses is that it allows you to do “Is before or equal to” and “Is after or equal to”. Another use is to use two ranges, such as Before 1 Jan 2013 or After 1 Jan 2015.

The next set of searches are ranges based on today’s date:

Tomorrow, Today and Yesterday. These are almost self-explanatory. However, just to note that it does not matter what the format of the cells are this time; it is based on the internal values.
Next Week, This Week and Last Week. The definition of “Week” is defined in Control Panel – Region and Language – Formats – First day of week. On my computer, the first day of the week is Monday.
Next Month, This Month, Last Month.
Next Quarter, This Quarter and Last Quarter – quarter groups together January-March, April-June, July-September and October-December.
Next Year, This Year and Last Year. This is based on calendar years, not financial.
Year to Date. From the start of the calendar year.

Clicking on “All Dates in the Period” gives you the following options: Quarter 1-4, and an individual month.

It should be noted that this does not restrict the search based on a particular year. So “February” would give you all rows which had a date in February 2012 or 2013 or 2014 etc. For a particular year, you should use the “Between…”.

Filtering for colors

New to Excel 2007, you can filter on the colors used.

You can either filter by “Cell Color”, which is the background color, or by “Font Color”, which is the foreground color.

You can only filter on one color at a time, so if you want to filter on both a forecolor and a backcolor, then you should copy that column into another column, so you can filter on the original column using the forecolor and the new column using the backcolor filter at the same time.

What’s Next?

That’s all for this series of articles. If you found this interesting, you may also be interested in how to create PivotTables, which can also use filters to narrow down the information.