Spy Journal 3.0 Filtering the web for you

This article has been written in response to a readers question.

Traci writes:

When I take a spreadsheet that someone else has created & remove some unwanted columns & rows, add a notes column of my own outside of the last column of their original spreadsheet, it won’t pick up that last “notes” column when it sorts.  If I highlight the entire document it will, but most people don’t highlight the whole thing when they sort.  We share this document with other people, they sort it and don’t realize that they didn’t sort the notes field.  Do you know of a fix to include the extra added “notes” column in the “my data has headers” “sort by” option that will travel with the document?

This problem is common for a number of things, sorting, filtering and summing. It largely stems from Excels desire to be “helpful” and guess things for the user.

There are a couple of tricks that can be used to assist excel in guessing correctly.

First of all the sorting problem Traci describes. Excel remembers the last sort that was performed on a selection and assumes that any new sorts will be using the same settings. In most cases this works well, but in the case Traci describes it doesn’t, because you have added a column that is not included in the selection previously sorted. A simple solution to this problem is to add the new column inside the previous selection. In other words, not added at the end but inserted into the existing columns. Excel will then expand the sort selection area to include the new column because it is looking at the last column as the bounds of the sort area. The new column can then be moved to the end if needed and the selection area will stay the same.

Using this method will also mean Excel will duplicate the formatting from the column to the left of the one being inserted.

The same concept can be used for filtering, and summing. By inserting rows or columns within a selection between the first and last columns or rows, Excel expands the ranges automatically.

Notes: With filtered ranges, the filters must be turned off in Excel 2003 before inserting a column. In Excel 2007 the new table formats have some specific quirks to their use and selection. However inserting a column inside the table is still the best way to do it.

Another more complex way to deal with this is to create a dynamic range for the sort area and a macro with a button to be used to sort using the parameters you have set.