How simple, advanced search works in Excel Online

Excel is designed for faster calculations. Often, documents here consist of a large number of sheets, which contain long tables with numbers, formulas or text. To conveniently find the required cells, there is a special automated search. By becoming familiar with the features of its use, you can reduce the time spent working on documents. How to search for words, phrases or cells in Excel is described in detail below.

How to search for values ​​in Excel.

Search in Excel The following describes several options for searching and filtering data in an Excel table.

1. Classic search "MS Office".

2. Conditional formatting (highlighting the desired cells with color)

3. Setting up filters based on one or more values.

4. A fragment of a macro for iterating through cells in a range and finding the desired value.

Search multiple words

If you don’t know how to find a word in a table in Excel, you should also use the function of the “Editing” section – “Find and highlight”. Next you need to start from the search phrase:

• if the phrase is accurate, enter it and press the “Find all” button;
• if the phrase is broken up by other keys, when writing it in the search line, you need to additionally put “*” between all the words.

In the first case, the search will return all results with the exact search phrase, ignoring other declensions or diluted variants. In the second case, all values ​​with the entered labels will be found, even if there are other characters between them.

1) Classic search (ordinary).

You can call the search panel (menu) using the hotkey combination ctrl+F. (Easy to remember: F- Found).

The search window consists of a field into which the desired piece of text or the desired number is entered, a tab with additional settings (“Options”) and a “Find” button.

Classic Excel Search

In the search parameters, you can specify where to search for text, whether to search for a whole word in a cell or the occurrence of a word in sentences, and whether to take case into account or not.

Conditional formatting for the cells you are looking for.

We search for the specified range of cells

If the table is large and it is not convenient to search the entire sheet, you can limit the search space to a specific range of cells.

To search a specified range of cells, follow these steps:

1. Select the required cells to search;
2. Enter the combination Ctrl+F;
3. In the “Find and Replace” window that opens, perform all the steps from the first method. The search will be performed in the specified range of cells.

3) The third way to search for words in an Excel table is to use filters.

The filter is installed in the “Data” tab or by pressing the key combination ctrl+shift+L .

Setting up a filter for searching words

By clicking on the filter triangle, you can select “Text filters” in the context menu, then “contains...” and specify the search word.

After clicking the “Ok” button, only the column cells containing the searched word will remain on the Screen.

Advanced search capabilities for Excel Online tables

With a simple search, all cells will be included in the result, this is inconvenient. Unacceptable results must be eliminated. An advanced search will help with this.

1. Once you have opened the Find and Replace window, click the Options button;
2. Many additional tools have appeared, and, if necessary, they can be adjusted;
3. After setting all the necessary settings, click Find all or Find next, after which a search will appear.

So, Excel documents allow you to simply, but quite functionally, search the document. To perform a simple search, you need to highlight the search window and type the required query. There are also options for setting up an individual search using different parameters. Use all methods in your work.

4) Search method number four is a VBA macro for searching (iterating values).

Depending on the purpose and conditions of use, the macro may have different configurations, but the main part of the VBA macro iteration cycle is given below.

Sub Search()

' ruexcel. ru macro for checking values ​​(search)

Dim keyword As String

keyword = "Search word" 'assign the variable to the search word

On Error Resume Next 'skip if there is an error

For Each cell In Selection 'for all cells in the selection (selected range)

If cell. Value = "" Then GoTo Line1 'if the cell is empty go to " Line1"

If InStr( StrConv( cell. Value, vbLowerCase), keyword) > 0 Then cell. Interior. Color = vbRed 'if a cell contains the word color it red (search)

Line1:

Next cell

End Sub

Additional search options for words and phrases

When the table is large enough and you need to search for certain parameters, they can be specified in special search settings. Click the Options .

Here you can specify additional search parameters.

Search:

• on sheet - only on the current sheet;
• in workbook —search the entire Excel document if it consists of several sheets.

Browse:

• by lines - the searched phrase will be searched from left to right from one line to another;
• by columns - the search phrase will be searched from top to bottom from one column to another.

Choosing how to view is relevant if there is a lot of data in the table and there is some need to view by rows or columns. The user will see exactly how the table is viewed when they click the Find Next to move on to the next match found.

Search scope—defines exactly where to look for matches:

• in formulas;
• in cell values ​​(values ​​already calculated using formulas);
• in the notes left by users on cells.

And also additional parameters:

• Case sensitive means that uppercase and lowercase letters will be treated as different.

For example, if you do not take case into account, then the query “excel” will find all variations of this word, for example, Excel, EXCEL, ExCeL, etc.

If you check the box to take into account case, then the query “excel” will find only this spelling of the word and the word “Excel” will not be found.

• Entire cell —check this box if you need to find those cells in which the searched phrase is found entirely and there are no other characters. For example, there is a table with many cells containing different numbers. Search query: "200". If you do not check the entire cell, all numbers containing 200 will be found, for example: 2000, 1200, 11200, etc. To find cells with only “200”, you need to check the entire cell box. Then only those with an exact match to “200” will be shown.
• Format... - if you specify a format, then only those cells will be found that contain the desired set of characters and the cells have the specified format (cell borders, alignment in the cell, etc.). For example, you can find all yellow cells containing the characters you are looking for.

You can set the search format yourself, or you can select from a sample cell - Select format from cell...

To reset the search format settings, click Clear search format .

This menu is called up by clicking on the arrow on the right side of the Format .

Formatting cells

Ready to format some cells? These shortcut keys make your job easier!

• Ctrl+B: Add or remove bold text to cell content, selected data, or selected cell range
• Ctrl+I: Add or remove italics to cell content, selected data, or selected range of cells
• Ctrl+U: Add or remove underline to cell contents, selected data, or selected range of cells
• Alt + H + H: Select a fill color
• Alt + H + B: add border
• Ctrl + Shift + &: Apply outline border
• Ctrl + Shift + _ (underline): remove path border
• Ctrl + 9: hide selected rows
• Ctrl + 0: hide selected columns
• Ctrl + 1: Open Format Cells dialog box
• Ctrl + 5: Apply or remove strikethrough text
• Ctrl + Shift + $: Apply currency format
• Ctrl + Shift +%: Apply percentage format

The more you use keyboard shortcuts, the easier it is to remember them. And no one expects you to remember them. Hopefully you've found a few new ones that you can use to make your Excel life a little better.

Finding non-strict character matching

Sometimes the user does not know the exact combination of the searched characters, which makes the search much more difficult. The data may also contain various typos, extra spaces, abbreviations, etc., which further adds to the confusion and makes searching almost impossible. Or the opposite situation can happen: too many cells correspond to a given combination and the search goal is again not achieved (who needs 100500+ found cells?).

Wildcards (wildcard characters) are very useful for solving these problems Various symbols can be hidden under wild cards, and Excel only sees their relative location in the search phrase. There are two such jokers: an asterisk “*” (any number of unknown symbols) and a question mark “?” (one “?” – one unknown character).

So, if you need to find a person named Ivanov in a large customer database, then the search can return several dozen values. This is clearly not what you need. You can add a name to the search, but it can be entered in a variety of ways: I. Ivanov, I. Ivanov, Ivan Ivanov, I.I. Ivanov, etc. Using jokers, you can specify a known sequence of symbols regardless of what is in between. In our example, it is enough to enter i*ivanov and Excel will find all the above listed options for recording the name of this person, ignoring all P. Ivanovs, A. Ivanovs, etc. The secret is that the “*” symbol tells Excel that any number of symbols can be hidden under it, but you need to look for something that matches the symbols “and” + something else + “Ivanov”. This technique significantly increases search efficiency, because allows you to operate with imprecise criteria.

If it is very difficult to understand the information you are looking for, then you can use several stars at once. So, in a list of 1000 positions for the search phrase mol*s*m*care, I quickly find the position “Mol-ko d/sn poppy. GARNIER Basic care d/dry/sens. 200ml" (this is an abbreviation for "Garanier Basic Care Makeup Remover Milk...."). At the same time, it is obvious that a search for the phrase “milk” or “makeup remover” would not yield anything. Often it is enough to enter the first letters of the search words (which are probably present), separated by asterisks, for Excel to show the wonders of the search. The main thing is that the sequence of characters is correct.

There is another joker – the “?” sign. Only one unknown symbol can be hidden under it. For example, by specifying the search criterion 1?6, Excel will find all cells containing the sequence 106, 116, 126, 136, etc. And if you specify 1??6, then cells containing 1006, 1016, 1106, 1236, 1486, etc. will be found. So the joker "?" imposes more stringent restrictions on the search, which takes into account the number of missing characters (equal to the number of “?” questions entered).

If unsuccessful, you can try to change the search phrase by swapping known characters, shortening them, adding new wildcards, etc. However, this is not all the nuances of the search. There are situations when you closely observe the cell you are looking for, but for some reason the search does not find it.

Editing Cells

Excel also provides some keyboard shortcuts for editing cells.

• F2: Edit Cell
• Shift + F2: Add or edit cell comment
• Ctrl+X: Cut cell contents, selected data, or selected cell range
• Ctrl + C or Ctrl + Insert: Copy the contents of a cell, selected data, or selected range of cells
• Ctrl + V or Shift + Insert: Paste the contents of a cell, selected data, or selected range of cells
• Ctrl + Alt + V: Open Special Attachment dialog box
• Delete: Delete the contents of a cell, selected data, or selected range of cells
• Alt + Enter: Insert a hard return inside a cell (while editing a cell)
• F3: Insert cell name (if cells are on the worksheet)
• Alt + H + D + C: Delete column
• Esc: Cancel an entry in a cell or formula bar
• Type: Complete an entry in a cell or formula bar

Search for MS EXCEL solution. Acquaintance

Solution Finder is a Microsoft Excel add-in that helps you find the optimal solution to a problem given user-specified constraints.

We will consider finding a solution in MS EXCEL 2010 (this add-in has undergone some changes compared to the previous version in MS EXCEL 2007). In this article we will look at:

• creating an optimization model on a MS EXCEL sheet
• setting up the Search for a solution;
• simple example (linear model).

Installation Search for a solution

The Search for Solution command is located in the Analysis group on the Data tab.

If the Search for Solution command in the Analysis group is not available, then you need to enable the add-on of the same name. For this:

• On the File tab, select the Options command, and then the Add-ons category;
• In the Manage field, select Excel Add-ins and click Go;
• In the Available add-ins box, select the check box next to Search for a solution, and then click OK.

Excel Hotkeys Using Function Keys (F1-F12)