'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Make Excel Ctrl+F Search Cell Values Instead of Formulas
Question details
The user wants Excel's Find and Replace feature to search for displayed cell values and columns by default rather than searching within the underlying formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for specific displayed text or numerical data within a spreadsheet using the Ctrl+F keyboard shortcut.
- Observed behavior
- When pressing Ctrl+F, the 'Look in' setting defaults to 'Formulas', which causes the search function to miss values that are dynamically generated by those formulas.
Ensure you have your spreadsheet open and verify whether the data you are trying to find is typed directly into the cells or generated as a result of active formulas.
Adjust 'Look in' Settings in the Find and Replace Dialog
Change the advanced search options within the Find and Replace dialog box so Excel searches by values during your current session.
Excel is programmed to search within formulas by default. To find the actual calculated results displayed on your screen, you need to manually switch the search target to 'Values'.
While Excel does not offer a permanent global setting to change this default behavior upon every fresh startup, it will remember your adjusted settings for the duration of your active session.
Press the 'Ctrl + F' shortcut on your keyboard, or go to the 'Home' tab on the ribbon and click 'Find & Select' > 'Find...'.
In the Find and Replace dialog box, click on the 'Options >>' button to reveal the advanced search settings.
Locate the 'Look in' dropdown menu at the bottom right of the options panel. Click it and change the selection from 'Formulas' to 'Values'.
Type your desired text or number into the 'Find what' box and click 'Find Next' or 'Find All'. Excel will now search for the displayed cell values.
Search Cell Values Easily with WPS Spreadsheet
WPS Office provides a highly intuitive Spreadsheet application that makes managing, searching, and filtering your data effortless. You can easily adjust your Find and Replace settings to search by values just like you do in Microsoft Excel.
- 1. Open your file in WPS Office: Launch WPS Office and open your existing spreadsheet (.xlsx or .xls).
- 2. Launch Find and Replace: Press 'Ctrl + F' on your keyboard to open the Find and Replace dialog.
- 3. Adjust to search Values: Click the 'Options' button, click the 'Look in' dropdown, and select 'Values' instead of 'Formulas'.
- 4. Find your data: Enter your search term and click 'Find Next' to easily locate your calculated cell values.
Frequently Asked Questions
Is there a way to permanently set Excel's Find default to Values?
By design, Microsoft Excel resets the Find and Replace defaults every time you restart the application. While you cannot permanently change this default through the standard interface, Excel will remember your 'Values' preference as long as the application remains open during your current session.
Why does Excel default to searching in Formulas?
Excel searches formulas by default to help users find calculation references, cell linkages, and structural data. However, this can inadvertently hide the actual displayed results if users are unaware of the 'Look in' dropdown setting.
Does changing the search to 'Values' affect Replace operations?
If you change 'Look in' to Values, you can successfully search based on the displayed values. However, if that value is generated by a dynamic formula, you cannot replace the value directly without overwriting the underlying formula itself.
Can I search across the entire workbook instead of just the active sheet?
Yes. While you have the Find and Replace options expanded, change the 'Within' dropdown menu from 'Sheet' to 'Workbook'. This will expand your value search across all tabs in your open Excel file.
