Skip to main content

How to Highlight Search Word in Excel with Color

By John Bennet
Got a spreadsheet, full of values/text content and you want to search for a specific word in a row or column then excel with its Find utility(CTRL+F) will let you strike through the search word from the selected position of excel sheet in no matter of time one by one until the last matching search term is found.
But if you want to highlight the search word to filter out the field containing the search term then unlike other tools it doesn't highlight all the search term containing fields by default. For instance: A column with n rows of data like Home Addresses and you want to filter out address fields containing the street name as New Jersey.

Read Also: How to highlight Matching values of other columns in Excel

How to Highlight MS Excel Search Results In Color?
Just follow the below steps to emphasize the search term containing fields in colors of your choice from the color palette.
  1. Open the excel sheet containing the data.
  2. Press Ctrl+A to select for search through the whole excel worksheet (or) select the range of row/column from the excel sheet. Eg: Column A from Row 1 to 100.
  3. Press Ctrl + H to open find & replace the window and enter the search term For example "Jones Street".
    Selected column E to search for the search word "Jones Street" using Find & Replace
  4. Now click Options >> on the find and replace the window to open additional options underneath the same.
  5. Then in Replace with row Enter the same search term as entered in Find what field and then click on Format option under replace with a field to open a new window with formatting options.
  6. In Replace Format window ->Tap to Fill tab and then select any color from the color palette and click on Ok to confirm.
    Fill Tab - Select any color from the color palette and click on OK
  7. Now in Find and Replace window click on Replace All to highlight the search term containing fields with the opted color.
    All excel Cells containing Search term "Jones Street" is Highlighted in Color
  8. Now all the cells containing the search term will be highlighted with the selected color on the excel sheet.
  9. After this, if you want to filter out the search cells separately, press CTRL + Shift + L keys (or) Under the main menu Click on Data ->Filter then Filter by Color.
Labels:

Comments

  1. AnonymousJune 16, 2018 at 10:26 PM

    My brother suggested I might like this web site.
    He was totally right. This post truly made my day.
    You cann't imagine just how much time I had spent for this information! Thanks!

    ReplyDelete
  2. AnonymousJune 16, 2018 at 10:58 PM

    Wow, marvelous blog format! How lengthy have you ever been running a blog for?
    you make blogging look easy. The overall look of your web site is excellent, as
    smartly as the content material!

    ReplyDelete
  3. AnonymousAugust 10, 2018 at 5:21 AM

    Very nice blog post. I absolutely appreciate this site.
    Keep writing!

    ReplyDelete
  4. AnonymousSeptember 23, 2018 at 2:17 AM

    You really make it seem so easy with your presentation but I find
    this matter to be really something which I think
    I would never understand. It seems too complex and very broad for me.

    I am looking forward for your next post,
    I'll try to get the hang of it!

    ReplyDelete
  5. AnonymousNovember 2, 2018 at 8:00 PM

    Appreciate this post. Will try it out.

    ReplyDelete
  6. AnonymousApril 6, 2019 at 1:30 AM

    Nice answers in return of this question with solid arguments and explaining all on the topic of that.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Format Write Protected USB Disk Drive and SD Card

By John Bennet
The main intention to append the write protection switches in USB flash drives and SD cards is to prevent accidental file deletion and to avoid suspicious files such as malware/trojan etc. from unauthorized sources delivering/storing into the USB disk. If the USB disk is in read-only mode, any attempts to create (or) edit file into the USB disk will be denied, But when you really want to make changes in the USB disk to add some more information (or) to edit the information that is already present in it, the restriction that you added on your computer initially will now prevent you from making changes such as adding new files or deleting existing files from any USB disk drive plugged into it. However, you can still read the files available in it, but you'll not be allowed to make changes and format the disk. The disk is write-protected -  You cannot format Having a flash drive/ SD card which is not completely accessible even by the owner of that device, is like having a l
1 comment
Read more

How to Hide Windows Drive Partitions Without Third Party Tools

By John Bennet
We all have some secrets that we don't prefer to share even with our besties, And if you're a guy then you might be having some folders kept hidden and blended between some other folders to keep it invisible from anyone else using your computer. Even though protecting important secret files in folders with passwords is necessary, but sometimes you may be forced to reveal your passwords to unlock secret folders to protect your valuable relationship from breaking. So here we come with a new technique that will keep your secrets as secret without let anyone know that you too have secrets, sounds good isn't it. Don't Just Protect Disk Drives with Passwords Better Hide From Others S topping anyone from accessing your secret files with passwords is good. However, they know that you have secrets and they might attempt to crack your password in your absence with the traces left around. So don't give anyone the chance to peep into your secrets. Also Read: How to F
Post a Comment
Read more

How to Delete WhatsApp Chats Partially Like Only Photos, Videos or Other Data

By John Bennet
We cannot keep everything in WhatsApp for so long, sometimes due to less importance of the chat and most of the time to save memory space. If you wonder what is the big deal in deleting chat data of contact from WhatsApp, thinking just by holding the chat item and tap on delete itself would do so (or) by going to Contacts chat - > click on Top Right Corner -> Tap More option ->Clear Chat would also delete the entire chat conversation. But in either way, the chat item will be deleted one by one or as a whole. Read Also: How to Download WhatsApp Status Videos, Photos, and GIF Files How to Delete Chat-Based WhatsApp Data Partially from Conversation? WhatsApp chats conversation data can be deleted in multiple ways but no other way has provided a feature to delete or retain chat conversation-based on the chat data type such as photos, videos, voice notes, audio files, etc. Here in this article, we've detailed one simple way to delete conversation data of the cont
Post a Comment
Read more