Excel Find and Replace

November 18, 2011 10:36 AM

Excel - Find and Replace -- I use the excel function find and replace often. One spreadsheet finds it instantly. Another searches each cell and takes longer. I stopped updating the instant one before I noticed the effect. Windows xp -- Office 2007 I'd like to have more of the instant search.
posted by notned to technology (8 answers total)

What's the difference between the two spreadsheets? There's not a lot of info here. Maybe Excel is recalculating the entire spreadsheet when you search.
posted by dfriedman at 10:54 AM on November 18, 2011

I use these to track orders. The instant search last had information added in 2009. The other is current. As far as I know that's the only difference.
posted by notned at 11:00 AM on November 18, 2011

How much bigger is one than the other? If they're reasonably close in size, try highlighting, say, the first 5000 rows of data and doing the S/R just within the selection; then do the same thing in the other one.

What might be happening is that Excel is for the fast search, it's only searching the range of cells that actually contain data, whereas for the slower one it's looking at the entire sheet for whatever reason.
posted by Doofus Magoo at 11:40 AM on November 18, 2011

Does not much difference cover file size?
posted by Lesser Shrew at 11:43 AM on November 18, 2011

And you might want to delete unused rows and columns, just for kicks.
posted by Lesser Shrew at 11:44 AM on November 18, 2011

If it is a Last Cell issue......

Excel Last Cell can be a funny thing. There are a couple of ways to clear out unused rows and columns, but that does not always guarantee it will set it right.

It is a little overkill, but when I run into an issue with Last Cell and I absolutely want to be sure, I create a new sheet and only copy over the active cells of the questionable sheet to the new one. I delete the old sheet and rename the new sheet the same as the deleted one so any links will continue to work.
posted by lampshade at 12:16 PM on November 18, 2011 [1 favorite]

Useful tip, lampshade!
posted by Lesser Shrew at 2:57 PM on November 18, 2011

I copied my cells into a new sheet. There were a lot. Now I have instant search and I am happy.
posted by notned at 4:57 PM on November 28, 2011