Filtering an Excel Spreadsheet
May 17, 2010 11:07 AM Subscribe
Problems filtering a large Excel worksheet that resides on a shared drive.
This spreadsheet has 1200+ rows and columns A through AN. I need to filter the entire spreadsheet based on the data in one column, which I am easily able to do when it is saved on my desktop. But when it is saved on the shared network drive, I am only given filter options for values in the first 200ish rows.
I am selecting the header row 1 and clicking the Filter button in the toolbar, which gives me the little filter arrows on each of the column headers. I am using Office 2007 on WinXP, but this was also happening when I used Office 2003.
This spreadsheet has 1200+ rows and columns A through AN. I need to filter the entire spreadsheet based on the data in one column, which I am easily able to do when it is saved on my desktop. But when it is saved on the shared network drive, I am only given filter options for values in the first 200ish rows.
I am selecting the header row 1 and clicking the Filter button in the toolbar, which gives me the little filter arrows on each of the column headers. I am using Office 2007 on WinXP, but this was also happening when I used Office 2003.
Yeah, what mreleganza said. Selecting a value from the drop down list is just a shortcut selecting "(Custom...)" and entering the value to filter for.
posted by Perplexity at 11:23 AM on May 17, 2010
posted by Perplexity at 11:23 AM on May 17, 2010
Response by poster: I just tried a custom filter which "contains" the string I need, but it only applied the filter to the first 216 rows - these rows numbers are now colored blue. The same document saved on my local machine applies the custom filter to the whole document.
I just noticed that the first 216 rows are the first Group of data.
posted by rhapsodie at 11:28 AM on May 17, 2010
I just noticed that the first 216 rows are the first Group of data.
posted by rhapsodie at 11:28 AM on May 17, 2010
After you set the filter, click on the filter header row, and hit "Advanced" in the "Sort & Filter" part of the toolbar. Make sure the filter applies to all the rows you want it to. By default Excel only auto-selects up to the first blank line.
posted by inigo2 at 11:31 AM on May 17, 2010
posted by inigo2 at 11:31 AM on May 17, 2010
Response by poster: I set the custom filter and set the List Range in the Advanced option to include the entire spreadsheet, not just the first 216 rows. Hitting okay removes the filter. Setting the custom filter again only applies to the first 216 rows, and the List Range in the Advanced option has reset itself to the first 216 rows. That cycle just continues.
posted by rhapsodie at 11:39 AM on May 17, 2010
posted by rhapsodie at 11:39 AM on May 17, 2010
Best answer: inigo2 has got it. Your filter area does not include the entire data set.
To remedy: Choose Data > Filter > Auto Filters to REMOVE all auto filters. All little arrow things should disappear.
Now select not only the header row, but the ENTIRE data set. In other words, every cell that you want to deal with should be highlighted (from A1:AN1200, or whatever). Now go back and choose Data > Filter > Auto Filters again (or just click your filters button).
Now the auto filters should know that they should include everything.
posted by crapples at 11:41 AM on May 17, 2010
To remedy: Choose Data > Filter > Auto Filters to REMOVE all auto filters. All little arrow things should disappear.
Now select not only the header row, but the ENTIRE data set. In other words, every cell that you want to deal with should be highlighted (from A1:AN1200, or whatever). Now go back and choose Data > Filter > Auto Filters again (or just click your filters button).
Now the auto filters should know that they should include everything.
posted by crapples at 11:41 AM on May 17, 2010
Response by poster: That did it! Thank you to everyone for their help!
posted by rhapsodie at 11:45 AM on May 17, 2010
posted by rhapsodie at 11:45 AM on May 17, 2010
This thread is closed to new comments.
posted by mreleganza at 11:17 AM on May 17, 2010