Forum Discussion

JDW176's avatar
JDW176
Copper Contributor
Oct 01, 2024

Set Default Search to By Columns in Excel

I have several Excel spreadsheets with over 100,000 lines.  I would like to set the default Search parameter for each to search By Column instead of By Row.  Preferably for all Excel documents, but I...
Excel
office 365
JDW176's avatar
JDW176
Copper Contributor

peiyezhu The default Find and Replace settings are

 

Within: Sheet

Search: By Rows

Look in: Formulas

 

I want it to be 

Within: Sheet

Search: By Columns

Look in: Formulas

 

 

peiyezhu's avatar
peiyezhu
Bronze Contributor
Oct 12, 2024
There appears to be a way to do this in older versions of Excel, but the directions provided do not work with my current version of Excel

I guess the UI has been removed from new 365.

Why do you need search by columns?
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Oct 12, 2024
    re:have several Excel spreadsheets with over 100,000 lines.
    Do you feel lag or slow when you open a workbook with 100,000 lines?
    • JDW176's avatar
      JDW176
      Copper Contributor
      Nov 12, 2024

      I have several columns containing formulas.  When opening the document with lots of lookups, then yes it can be slow.  Primarily it causes my Bluetooth keyboard to be unresponsive for several seconds.  I go through the spreadsheet weekly and copy the lookup results and then paste values only.  That reduces the overall lookups and then it is not too slow.

  • JDW176's avatar
    JDW176
    Copper Contributor
    Nov 12, 2024

    I prefer to sort by column because each line contains a unique client ID number.  That ID number is concatenated into a few other columns as well.  when searching with that client ID number I don't need it to find it four times in one row.

Resources