Shulou(Shulou.com)11/24 Report--

[Ctrl+F] who doesn't know how to use it, isn't it just "find"?

Look for characters, numbers, letters, Chinese characters. It's super simple!

But you know what?

The function of "find" is more than that, it can also look up according to the displayed value, multi-table search, and search in a specified area.

When you look up specific content in a table, you need to use the search function.

Today, I will systematically talk about [Ctrl+F] this shortcut key, a total of 5 tips, after learning, to ensure your work efficiency several times!

01. Precise matching search in Excel, press [Ctrl+F] to use the search function, and you will generally find all the cells that contain keywords.

For example, if you want to search for "EXCEL" in the following table, all cells containing keywords will be searched, including "Excel" and "excel".

If you want to find only "EXCEL", we can try to check [Cell match] to find it accurately.

❶ press [Ctrl+F] to open the find and replace window, and then click [options]

❷ check [Cell match] and click [find all] to see if only "EXCEL" has been found.

Oh ho, it looks like the car overturned!

What's going on?

If you carefully compare the cells found before and after checking [Cell matching], you can clearly see that after checking, the cells containing both search keywords and Chinese characters have not been found.

The three cells found are all EXCEL, except that two of them contain lowercase letters.

According to this feature, we can try again and check Cell matching and case sensitivity at the same time.

Let's take a look at the moving picture demonstration:

Success!

Accurate matching search, do you all know it?

PS: when there are only Chinese characters in the search area, check [Cell matching] to find it accurately!

02. Fuzzy matching lookup although most of the time we need exact matching lookup, we will also encounter the situation of fuzzy matching lookup.

For example, what should we do if we search for all the names that begin with "autumn" in the following table?

It's very simple, just enter "Qiu *" in [find what]!

Because "*" replaces any number of characters.

If you enter "Qiu E" in "find what", you will find "Qiu Xiao E", but "Qiu Xiao E" will not be found:

Because "?" only replaces one character ~

PS: "?" needs to be typed in English.

03. Press the display value to find out if you have encountered this situation. Make it clear that there is data in the table, press [Ctrl+F], enter the data in "find what", but display:

Think there is something wrong with the data you entered, and then try to copy the data in the table.

Paste into find what, and click find next.

But still "can't find what you're looking for. ".

What's going on? One of the most common reasons is that [look in] is a "formula".

Because the formula looks for the "actual value", that is, the value seen in the edit bar

The value here is "display value", that is, the value of "cell format" and "skin" is set.

Solution:

Just change [look in] to "value".

Excel generally defaults to "formula". If you set "cell format", remember to change "what to find" to "value" when looking for it.

Look up in a specified area sometimes, multiple duplicates appear in a worksheet, but you only need to find the data for a certain area.

At this point, you can first select the area you want to find, and then use the find function, so that you will not find other areas.

Animated illustration:

05. multi-table lookup in most cases, we "look" in the same worksheet, and we often have a "headache" when we need to find data in multiple worksheets.

Because it means repetitive work: open a worksheet-find, open the second worksheet-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 2 items to choose from here, "worksheet" and "workbook" ~

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.