News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Using Find in Excel

    Posted on beethoven Comment on the AskWoody Lounge

    This topic contains 1 reply, has 2 voices, and was last updated by  zeddy 3 weeks, 6 days ago.

    • Author
      Posts
    • #1804924 Reply

      beethoven
      AskWoody Plus

      Despite having used excel for years, I still have to ask this basic question. I noticed when using find in excel in very large spreadsheets that the system goes into “overdrive”. Instead of jumping to the next cell with the relevant value, it keeps searching for a long time. at times the find and replace box vanishes, then comes back with the counter in the top left still running through its searches.

      I normally use find  within sheet, search by rows and Look in Values as these seem to be the obvious criteria for looking for a name or address or a specific amount.  Changing this to look in Formulas gets my results almost immediately but why should I use Formulas instead values in these cases?

    • #1896195 Reply

      zeddy
      AskWoody_MVP

      Hi Beethoven

      The reason the system goes into “overdrive” instead of jumping to the next cell with the relevant value is because you have clicked the [Find All] button instead of the [Find Next] button.

      With Excel2010 the Find and Replace dialog box can indeed “vanish” whilst the find-all process is underway. I tested this with around 1 million data records split across a few worksheets. The Find and Replace dialog box didn’t disappear when I ran the same checks with Excel2016.

      Using Look in: Formulas instead of Look in: Values wouldn’t make any difference if your data records are just that i.e. cells contain just names, addresses, amounts etc. It might appear faster because you have just repeated the same search.

      zeddy
      Excel Eye Tracking Decoy Analyst

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Using Find in Excel

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.