Excel tips - Tracking deadlines
July 23, 2013 11:24 AM   Subscribe

I have an excel spreadsheet that contains a bunch of random information about some work projects. I'm looking for a way to quickly highlight and summarize upcoming deadlines. I'm already using conditional formating to highlight upcoming dates, but I need more than that.

The important information that I have to track is the Project Name, Project Deadline, and Deadline Date Action (each project has a different last day requirement). Each of these items has their own column. There's a bunch of other columns as well, but for my purposes I don't care about them right now.

I've used conditional formating to highlight the deadline column so the deadline date is a different color depending on if the deadline is 61-90 days out, 31-60 days out, 1-30 days out, or today. However, it's sort of a hassle to scroll through the entire sheet to see what deadlines are approaching based on the color. Is there a way I can easily create a summary chart that shows only the upcoming deadlines, 30, 60, and 90 days out along with the corresponding Deadline Date Action column and Project Name?
posted by Arbac to Technology (3 answers total) 1 user marked this as a favorite
Take the formula you are using in the conditional formatting rules and put it in new column instead. Then apply an autofilter to the whole table.
posted by paper chromatographologist at 12:12 PM on July 23, 2013

You should be able to easily create a pivot table to do this - pivot tables in office 2010+ are a lot easier to use these days.

Using a pivot table you can slice and dice the info in pretty much anyway you wish, and I'm sure as you described.

There's lots of help out there on the net but pushing the pivot table button and tinkering about worked well for me.

See this link too:

posted by razzman at 12:13 PM on July 23, 2013 [1 favorite]

You can filter the data in that column by date. There is an option for all values less than X so you can put in the date 30 days out or even 7 days out.
You can also sort the data by deadline. If you want to keep your original order, add a column called Order and fill it with the order (1, 2, autofill). Then sort the deadline column. When you want your original order back, sort the Order column. If you have any blank rows or columns in the middle of your data, highlight all of your data before sorting.
posted by soelo at 1:57 PM on July 23, 2013

« Older What are your recommendations for contemporary...   |   How to express interest in a job after the first... Newer »
This thread is closed to new comments.