• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Find Tool

You are here: Home / Excel / Excel Find Tool
December 2, 2014 by Mynda Treacy

You’ll be Amazed at what you can CTRL+F in Excel

Excel’s Find tool is one of the first features you’re likely to learn and for many of us we only use its most basic feature, which is to find a particular word or value in a cell.

It is such a diverse tool that I reckon you could almost write a whole book on it. Don’t worry I’m not going to bore you to death with every feature of the Find tool, but I am going to share a couple of gems.

Find Gem # 1 – Find Backwards

Did you know that if you hold down the SHIFT key and click on the ‘Find Next’ button Excel will find backwards?

Excel find backwards

It’s handy if you want to find the last instance of a value in a column/worksheet.

This is the kind of tip that makes me ask 'who knows that kind of stuff?'. There’s not even a hint that this might work in the tool tips.

Well, I learnt that tip from Excel MVP Bob Umlas. If you’d like to learn more tips like that then you’re in luck. Bob has written a book containing over 100 Excel tips called ‘This isn’t Excel, it’s Magic!’. Bob gave me an autographed copy when I met him at the Microsoft MVP Summit and I’ve been making my way through it since and have learned quite a few tips that are new to me already.

Click here to get your own copy of Bob’s book, or here to get his other Excel Book called ‘Excel Outside the Box’.

Find Gem # 2 – Find Blanks left by “” in Formulas

This tip was originally given to me by Drazen and then again recently by Excel MVP Ron Coderre after he read my blog post on deleting rows containing blanks returned by double quotes in formulas.

Just to recap, the blanks I’m talking about are the ones as a result of a formula like this which returns a blank if A1 is empty:

=IF(A1="","",A1)

To cleanse your worksheet of these pesky blanks:

  1. Select the range of cells you want to cleanse
  2. CTRL+F to open the Find dialog box.
  3. Find what: <-leave this blank (or clear it if it has contents)
  4. Click: Options > Look in: Values <-Without this it won’t find the text blanks
  5. Click: Find all
  6. CTRL+A to select ALL of the matching cells. Your Find dialog box should look something like this:
  7. Excel find and replace

  8. Press: ESC to close the Find window
  9. Press: DELETE

Gone are the formulas that evaluate to "".

Note: if you have pasted your formulas as values then you can skip step 4 above.

And with only keystrokes:

CTRL+F > ALT+I > CTRL+A > ESC > Delete

Thanks to Drazen and Ron Coderre for this clever tip.

Gem # 3 – Find and Replace Formats

And lastly a tip from me, did you know you can use the Find tool for formatting?

Let’s say we have a list of fruit and we want to colour the font for the instances of Apples in red. One way is to use Conditional Formatting, but we can also use the Find tool. Here’s how:

  1. Select the cells you want to search, or simply have just one cell anywhere in the worksheet selected.
  2. CTRL+H to open the Find and Replace dialog box > click the Options button to expand the dialog box
  3. In the Find what field I’ll enter ‘apples’
  4. In the Replace with row click on the ‘Format’ button and set your formatting. Your dialog box should look like this:
  5. Excel find formatting

  6. Click the Replace All button. Job done:
  7. Excel find format done

A variation on this is to find all values formatted in a certain way, let’s say you wanted to change all of your headings from one formatting combination (colour, font, size) to another.

  1. CTRL+H to open the Find and Replace dialog box > click the Options button to expand the dialog box if it isn’t already
  2. Click on the down arrow on the Format button and select ‘Choose format from cell’
  3. Excel find and replace formatting

  4. The mouse pointer will now be a pen and you can click on any cell containing the format you want to change.
  5. Next set your new format by clicking the Format button on the Replace with row, or you can click the down arrow on the Format button and select a format from another cell.

So, there you have 3 uses for the Find tool that far exceed what it appears to offer at first glance. I hope you find them useful.

Thanks

I'd like to say thanks to Bob Umlas for allowing me to steal gem #1 from his book and share it with you. And a double thanks to Drazen and Ron Coderre for gem # 2 which, after all these years, has relieved me of my frustration with the double-quote blanks.

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.




Category: Excel
Previous Post:Excel Advent CalendarExcel Advent Calendar
Next Post:Excel Custom Chart LabelsExcel custom chart labels

Reader Interactions

Comments

  1. Hasmik

    December 6, 2014 at 1:02 am

    It is really fantastic. I love this.

    Reply
    • Mynda Treacy

      December 6, 2014 at 7:24 am

      🙂 Thanks, Hasmik.

      Reply
  2. Lisa

    December 4, 2014 at 2:36 am

    Thank you, thank you, thank you for Gem #2! The blanks left by “” in formulas have been an irritant and now I know how to eliminate them. I can’t wait to apply this new trick!

    Reply
    • Mynda Treacy

      December 4, 2014 at 6:00 am

      Thanks, Lisa. Glad you will find it useful too.

      Reply
  3. MF

    December 4, 2014 at 12:33 am

    Hi Mynda,
    #2 is awesome! Just a little twist could make thing so different. Thanks for sharing!

    For #3, I think that is another “under-valued” function of Excel which, when used properly, could save us huge amount of time.

    This is my sharing. Hope you like it.
    http://wmfexcel.wordpress.com/2013/12/05/quickly-deletehide-records-rows-with-strikethrough-format-by-using-find-and-a-couple-of-simple-techniques/

    Cheers,

    Reply
    • Mynda Treacy

      December 4, 2014 at 6:01 am

      Another good one, MF. Thanks for sharing your tip.

      Reply
  4. Col Delane

    December 3, 2014 at 11:49 am

    Hi Mynda
    More hidden gems pulled from the Pandora’s Box of Excel!

    Tip #3 is interesting. What’s weird is that if you Find “apples” and Replace with a format of say bold red font (but leave the Replace with text field blank), Excel will merely change the font, leaving “apples” intact in each targeted cell. If you had the same setup but without the Format change, Excel would replace “apples” with blank! So it seems that as long as the user specifies something to change in EITHER the text OR the format selection fields (or both), then Excel will effect a Replace (i.e. the text & format fields are not seen as independent of each other), whereas my initial expectation was that “apples” would be replaced with blank, making the format change somewhat superfluous.

    Reply
    • Mynda Treacy

      December 3, 2014 at 11:53 am

      Hi Col,

      That’s interesting. I hadn’t noticed that twist.

      Thanks for sharing.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.