March 27, 2023
88 31

One of the most powerful shortcut keys in Excel -[Ctrl+F]! But do you really use it? 7 common tips to learn quickly! – Urban Tech Story

I believe that students and office workers who own a laptop now know that there are many shortcut keys commonly used in Excel, such as Ctrl+T, Ctrl+E, Ctrl+G, Ctrl+H and so on.There is also a search function, which is[Ctrl+F]The search window called up. However, most people only know its basic functions, but they don’t know that it also hides many useful functions that you don’t know.

GettyImages 157619338

One of the shortcut keys in Excel is[Ctrl+F]which can open the search window, enter the characters you want to find, and then you can find what you are looking for. But did you know that “Find” can do more than that, you can also search by displayed value, search in multiple tables, search in a specified area, etc.! When you are looking for specific content in the table, you will use the search function.

The editor will now talk about the common usage of “Ctrl+F”. There are a total of 7 tips. After learning it, your work efficiency will be improved several times. Come take a look!

#1 Exact match lookup

In Excel, press[Ctrl+F]to use the search function, which will generally find all cells containing keywords. For example, in the following table, if you want to search for “EXCEL”, then all cells containing keywords will be searched, including “Excel” and “excel”.

format,f auto

If you want to find only “EXCEL”, you can try to check “Cell Match” for precise search.

  • Press[Ctrl+F]to open the “Find and Replace” window, and then click “Options”;
format,f auto
  • Check “Cell Match”, click “Find All”, and see if only “EXCEL” is found.

Carefully compare the cells found before and after checking the “Match Entire cell contents” (Match Entire cell contents), it is obvious that, after checking, the cells containing both search keywords and Chinese characters are not found.

The 3 cells found are all EXCEL, but 2 of them contain lowercase letters. According to this feature, we can try again, and check “Cell Matching” and “Match case” at the same time.

It should be noted that when the search area only has Chinese characters, you can check “Cell Match” to search accurately.

#2 Fuzzy match lookup

While most of the time we need to do exact match lookups, we also encounter fuzzy match lookups. For example, to search for all names starting with “Autumn” from the table below, how to do it?

It’s very simple, just enter “Autumn*” in “Find Content”!

format,f auto

Because “*” replacesany number of characters. If you enter “Qiu?E” in “Find Content”, you will find “Qiu Xiao E”, but “Qiu Xiao Xiao E” will not be found, because “?” only replaces one character. It should be noted that “?” needs to be entered in English.

  • *: Indicates any number of characters
  • ? sign: represents any single character
format,f auto
0 2

#3 Find by displayed value

Have you ever encountered this situation: it is obvious that there is data in the table, press “Ctrl+F”, enter data in the search content, but it displays:


Thinking that there is a problem with the input data, and then try to copy the data in the table. Paste in “Find what”, click “Find Next” (Find Next), but still can’t find what you are looking for.

In fact, one of the most common reasons for this situation is that the “Look In: ​​Formulas” is the “Look In: ​​Formulas”.

format,f auto
excel find options

Because the “formula” looks for the actual value, that is, the value seen from the formula bar; and the value here is the “display value”, that is, the “cell format” is set, that is, the value with a layer of skin.

format,f auto

The solution is very simple, just change the “Look in” to “Values”~

format,f auto

Excel generally defaults to “Formulas”. If you set “Cell Format”, remember to change “Find What” to “Values” when searching.

#4 Find in a specified area

Sometimes there are multiple duplicates in a worksheet, but only one area of ​​data needs to be found. At this time, you can first select the area to be searched, and then use the search function, so that other areas will not be found.

#5 Multi-table lookup

In most cases, we are “finding” in the same worksheet, when we need to find data in multiple worksheets, it is often a headache, because it means repetitive work – opening a worksheet , find, open sheet 2, find…

Is there a way to query multiple worksheets at once? Of course there is!

  • Press “Ctrl+F” to open the “Find and Replace” window;
format,f auto
  • Click “Options”, change “Scope” to “Workbook” — “Find Next” or “Find All”.
format,f auto
main qimg 92696217cc3685bbc1b1e32fccff55be

There are two options for “range” here, namely “Sheet” and “Workbook”.

#6 Fill the found content with color

The content to be found is not very obvious just by selecting the cell, and if there are multiple values ​​and they are distributed in different rows, it is not easy to find. However, if you can mark the found batches with colors, it will become much easier; with the function of filtering colors, finding them is a simple task.

If you want to fill the search content with a color, you can enter the keyword you want to search for, and then click Find All. You also need to press Ctrl+A to select all the found cells and set the fill color.


#7 Remove blank lines

First select the data area, then press Ctrl+F to call up Find, directly click “Find All”, this will find all blank cells, then press Ctrl+A to select all, then right-click to find “Delete” Select “Entire row”.


The above are the 7 tips for finding and replacing the Excel shortcut key[Ctrl+F]shared by the editor. Hurry up and learn it, and you will no longer have a headache in work and study!

For more technical information, please continue to hold the Urban Tech Story version!

Ewen Eagle

I am the founder of Urbantechstory, a Technology based blog. where you find all kinds of trending technology, gaming news, and much more.

View all posts by Ewen Eagle →

Leave a Reply

Your email address will not be published.