# Excel Gurus - Filters **URL:** \ **Category:** Factual Questions\ **Created:** [November 6, 2009, 2:42am UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520 "2009-11-06T02:42:48Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Jman](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jman/32/4416_2.png) [@Jman](https://boards.straightdope.com/u/Jman)\ **Post date:** [November 6, 2009, 2:42am UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/1 "2009-11-06T02:42:48Z")
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? ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [November 6, 2009, 2:49am UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/2 "2009-11-06T02:49:36Z")
I believe you can format the entire dataset as a “table” (at least in 2007) and everything becomes magically sortable. No filtering needed. ---
**Author:** ![Alex\_Dubinsky](https://avatars.discourse-cdn.com/v4/letter/a/dbc845/32.png) [@Alex\_Dubinsky](https://boards.straightdope.com/u/Alex_Dubinsky)\ **Post date:** [November 6, 2009, 3:04am UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/3 "2009-11-06T03:04:55Z")
Oh. I just put periods into empty cells. ---
**Author:** ![GorillaGirl](https://avatars.discourse-cdn.com/v4/letter/g/8491ac/32.png) [@GorillaGirl](https://boards.straightdope.com/u/GorillaGirl)\ **Post date:** [November 6, 2009, 5:34pm UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/4 "2009-11-06T17:34:31Z")
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. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [November 6, 2009, 5:48pm UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/5 "2009-11-06T17:48:12Z")
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. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [November 6, 2009, 6:09pm UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/6 "2009-11-06T18:09:10Z")
> [@GorillaGirl](#): > > 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. 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.) ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [November 6, 2009, 6:15pm UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/7 "2009-11-06T18:15:24Z")
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. ---
**Author:** ![GorillaGirl](https://avatars.discourse-cdn.com/v4/letter/g/8491ac/32.png) [@GorillaGirl](https://boards.straightdope.com/u/GorillaGirl)\ **Post date:** [November 6, 2009, 7:06pm UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/8 "2009-11-06T19:06:57Z")
**Munch** , I didn’t know that would work. Much better than my method. Ignorance fought! ---
**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\ **Post date:** [November 6, 2009, 9:42pm UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/9 "2009-11-06T21:42:08Z")
> [@CookingWithGas](#): > > 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. This is how I would sort. i’m not getting why filters are useful in sorting. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [November 8, 2009, 2:50am UTC](https://boards.straightdope.com/t/excel-gurus-filters/516520/10 "2009-11-08T02:50:01Z")
> [@gigi](#): > > 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.