Excel Gurus - Filters

So, I’m trying to get a good size spreadsheet (about 15 columns, hundred or so rows and growing) to be sortable by the columns. I’ve added filters to the column headers, which allows sorting forwards or backwards in any column.

The problem is, if a blank line is inserted anywhere in the middle of the data, which the users do from time to time, it only sorts up to the break. I need it to sort the whole spreadsheet, regardless of any breaks. I know I could do a sort macro with a command button for each column, but I really don’t want to take that much time to do 15 macros, plus if the spreadsheet grows beyond the sort boundaries, I’d have to update all 15 macros. Not what I want to do.

How do I do this simply and effectively?

I believe you can format the entire dataset as a “table” (at least in 2007) and everything becomes magically sortable. No filtering needed.

Oh. I just put periods into empty cells.

Select all the cells in your spreadsheet, including blank rows, then apply the filter. So for 15 columns and 100 rows, you’d select from cell A1 to cell O100. If you add rows or columns, you’ll have to remove the filter and re-apply it so the new records will show up.

If you use the Data, Sort command (in Excel 2003) then it will also include the blank rows. It is only maybe two extra mouse-clicks compared to using the Sort command in the the Autofilter.

Don’t do this.

Select columns A through O, but select ALL the rows. (Hover your mouse over the “A” and drag through “O”, don’t start at A1 and drag through O100.)

Having wall-to-wall blanks in your raw dataset can cause problems. This filtering thing is one of them. Think of the reasons those blank rows are in there, and consider strongly removing them from the raw dataset.

Now, for a finished report, sure … go nuts with the blank rows. But for something you want to sort and filter … at least have one column that will always have data.

Munch, I didn’t know that would work. Much better than my method. Ignorance fought!

This is how I would sort. i’m not getting why filters are useful in sorting.

Fewer mouse clicks for a sort with one column for the sort key.