by 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.
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”.
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”;
- 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”!
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
#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”.
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.
The solution is very simple, just change the “Look in” to “Values”~
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;
- Click “Options”, change “Scope” to “Workbook” — “Find Next” or “Find All”.
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!
