Daily Dose of Excel

El Says:
“I have a spreadsheet with dates and on each day, there are several activities with start time and end time. I want to add a row between each day, and also to add up the total time for all the activities in each day.”

This is where the Subtotals feature is quite useful.
It can add a variety of footers to appear at breaks in the group. You get outlining too.
Select your range, then from the Data menu, choose Subtotals…
At each change in StartDate, Use function Sum, Add subtotal to Duration.

Hi all,

Today I published my first article dedicated to Excel 2007 on my site.

The article is about Excel 2007’s new Table feature and is aimed at the beginner Excel 2007 user (that would be just about everyone except John Walkenbach ).

From the intro:

With the release of Excel 2007, Microsoft has introduced a new concept of working with tables of data. This new functionality is (not surprisingly) called “Tables”.
This article introduces you into the concepts of working with Tables and shows you how they may help you in your everyday Excel use.

Tables in Excel 2007 feature:

Integrated autofilter and sort functionality
Easy selecting
Header row remains visible whilst scrolling
Automatic expansion of table
Automatic reformatting
Automatic adjustment of charts and other objects source range

Read on here…

### Edited to correct the name of the feature ###

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

One of Excel’s features which I use frequently is the Advanced Filter - Unique Values.
It’s pretty handy for analysing data and building summary / master tables.

From Excel’s menu: Data > Filter > Advanced Filter…

Copy to another location = selected.
List range: Select the list of values you want to see unique. Include the heading.
Copy to: Select a single cell. You can only copy filtered data to the active sheet.
Unique records only = ticked.

Update:
“Copy to” must be the active sheet but “List range” may be another sheet.
Read the comments for more info. Thanks r.venkataraman!

Hi all

I have upload Easyfilter 2.1 Beta to my site with a Color tab.
It is not working on CF colors (this is the next step).
You can filter on Fill, Font colors and on bold cells.

Comments good or bad are welcome about this Beta version.
You can find it here (on the bottom of the page)
http://www.rondebruin.nl/easyfilter.htm

Ron de Bruin

Hi all

Together with Norman Harker and the late Frank Kabel I have create EasyFilter 2.0.
This version have a special Date tab with a lot of Date filter options.
I hope it is useful for a lot of Excel users.

This Version of EasyFilter is dedicated to the memory of Excel MVP Frank Kabel.
We will miss him very much.

You can download it here
http://www.rondebruin.nl/easyfilter.htm

Please read the Superb User Guide made by Norman Harker

Ron de Bruin
http://www.rondebruin.nl/

The Advance Filter feature (Data > Filter > Advanced Filter) can be used to filter a list based on the criteria range you specify. For instance, to find the week that Team3 hosts Team4 on a schedule, your worksheet might look like this:

The result being one time in Week 1.

The criteria you specify contains a header row with headers that match the column headings of the range you want to filter. I’m not filtering on the Week column, so I didn’t include that in my criteria range. Because Team3 and Team4 are on the same line in the criteria range, they are treated as an AND comparison. It says “Return all rows where Team3 is in the Home column AND Team4 is in the Away column”.

When the criteria are on different lines, it’s an OR comparison. If you want to print out Team3’s entire schedule, you would structure it like this:

It’s says “Return all rows where Team3 is the Home team OR Team3 is the Away team”. It returns every game that Team3 plays.

To be accurate, it really says “Return all rows where (Team3 is Home AND AnyTeam is Away) OR (Team3 is Away AND AnyTeam is Home)”. Leaving a blank under the criteria indicates that all records should be returned for that column as it relates to that particular criteria row.

In the first example, Team3 was Home and Team4 was Away. In this example, we’ll filter to see any time that Team3 plays Team4, regardless of whose Home. It also requires an OR criterion.

It’s a good practice to have the criteria range above the range to be filtered. If it’s adjacent, the criteria range might be made invisible when certain rows are hidden. You could also put it below the range, but you’ll have to move it if your list grows and it changes position on the screen when you filter.