Creating a search box in Excel enhances the functionality of your spreadsheets by making it easier to filter and access specific data quickly. This guide covers several methods to implement a search box, catering to different versions of Excel. Whether you're a beginner or an advanced user, these steps will help you set up a dynamic search box using features like the FILTER function, Conditional Formatting, and various formulas.

• Easily create a search box with the Funcția FILTER
  (available in Excel 2019 and later, Excel for Microsoft 365)
• Create a search box using Formatarea condițională
  (available in all Excel versions)
• Create a search box with formula combinations
  (available in all Excel versions)

Easily create a search box with the FILTER function

• This function automatically updates the output as your data changes.
• The FILTER function can return any number of results, from a single row to thousands, depending on how many entries in your dataset match the criteria you've set.

Here I will show you how to use the FILTER function to create a search box in Excel.

Step 1: Insert a text box and configure properties

1. Du-te la Dezvoltator fila, faceți clic pe Insera > Text Box (ActiveX Control).
   varful: În cazul în care Dezvoltator tab is not shown on the ribbon, you can enable it by following the instructions on this tutorial: Cum se afișează / se afișează fila dezvoltator în panglica Excel?
   
2. The cursor will turn into a cross, and then you need to drag the cursor to draw the text box at the location in the worksheet where you want to place the text box. After drawing the text box, release the mouse.
   
3. Right click the text box and select Proprietăţi din meniul contextual.
   
4. În Proprietăţi pane, link the text box to a cell by entering the cell reference in the Celulă conectată field. For example, typing "J2" ensures that any data entered in the text box automatically updates in cell J2, and vice versa.
   
5. Apasă pe Mod de proiectare în temeiul Dezvoltator tab to exit the Design Mode.
   

The text box now allows you to enter text.

Step 2: Apply the FILTER function

1. Before using the FILTER function, copy the original header row to a new area. Here I place the header row under the search box.
   varful: This approach allows users to clearly see the results under the same column headings as the original data.
   
2. Select the cell under the first header (e.g. I5 in this example), enter the following formula into it and press the Intrați cheie pentru a obține rezultatul.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   As shown in the above screenshot, since the text box now has no input, the formula displays the result "Nu s-au gasit date„în I5.

Result: Test the search box

Let's now test the search box. In this example, when I enter a customer's name in the search box, the corresponding results will be filtered and displayed immediately.

Create a search box using Conditional Formatting

Conditional Formatting can be used to highlight data that matches a search term, indirectly creating a search box effect. This method does not filter out data but visually guides you to the relevant cells. This section will show you how to create a search box using Conditional Formatting in Excel.

Step 1: Insert a text box and configure properties

1. Du-te la Dezvoltator fila, faceți clic pe Insera > Text Box (ActiveX Control).
   varful: În cazul în care Dezvoltator tab is not shown on the ribbon, you can enable it by following the instructions on this tutorial: Cum se afișează / se afișează fila dezvoltator în panglica Excel?
   
2. The cursor will turn into a cross, and then you need to drag the cursor to draw the text box at the location in the worksheet where you want to place the text box. After drawing the text box, release the mouse.
   
3. Right click the text box and select Proprietăţi din meniul contextual.
   
4. În Proprietăţi pane, link the text box to a cell by entering the cell reference in the Celulă conectată field. For example, typing "J3" ensures that any data entered in the text box automatically updates in cell J3, and vice versa.
   
5. Apasă pe Mod de proiectare în temeiul Dezvoltator tab to exit the Design Mode.
   

The text box now allows you to enter text.

Step 2: Apply the Conditional Formatting for searching data

1. Select the entire data range to be searched. Here I select the range A3:G279.
2. Sub Acasă fila, faceți clic pe Formatarea condițională > Noua regulă.
   
3. În Noua regulă de formatare căsuță de dialog:
   1. Selectați Utilizați o formulă pentru a determina ce celule să formatați în Selectați un tip de regulă opțiuni.
   2. Enter the following formula into the Formatează valorile în care această formulă este adevărată cutie.

      =$B3=$J$3

      Aici, $ B3 represents the first cell in the column you want to match with the search criteria in the selected range, and $J$3 is the cell linked to the search box.
   3. Apasă pe Format button to specify a fill color for the search results.
   4. Apasă pe OK buton. Vedeți captura de ecran:
      

Rezultat

Let’s now test the search box. In this example, when I enter a customer’s name into the search box, the corresponding rows that contain this customer in column B will be immediately highlighted with the specified fill color.

Create a search box with formula combinations

If you are not using the latest version of Excel and prefer not to only highlight rows, the method described in this section may be helpful. You can use a combination of Excel formulas to create a functional search box in any version of Excel. Please follow the steps below.

Step 1: Create a list of unique values from the search column

1. In this case, I select and copy the range B4: B281 to a new worksheet.
2. After pasting the range in a new worksheet, keep the pasted data selected, go to the Date Și selectați Eliminați duplicatele.
   
3. În deschidere Eliminați duplicatele , faceți clic pe OK butonul.
   
4. A Microsoft Excel prompt box then pops up to show how many duplicates are removed. Click OK.
   
5. After removing duplicates, select all the unique values in the list, excluding the header, and assign a name to this range by entering it in the Nume si Prenume box. Here I named the range as Client.
   

Step 2: Insert a combo box and configure properties

1. Go back to the worksheet containing the data set you want to search. Go to the Dezvoltator fila, faceți clic pe Insera > Combo Box (control ActiveX).
   varful: În cazul în care Dezvoltator tab is not shown on the ribbon, you can enable it by following the instructions on this tutorial: Cum se afișează / se afișează fila dezvoltator în panglica Excel?
   
2. The cursor will turn into a cross, and then you need to drag the cursor to draw the combo box at the location in the worksheet where you want to place the search box. After drawing the combo box, release the mouse.
   
3. Right click the combo box and select Proprietăţi din meniul contextual.
   
4. În Proprietăţi panou:
   1. Link the combo box to a cell by entering the cell reference in the Celulă conectată field. Her I type "M2".
      Tip: Specify this field ensures that any data entered in the combo box will automatically update in cell M2, and vice versa.
   2. În ListFillRange câmp, introduceți range name you specified for the unique list in Step 1.
   3. Schimba MatchEntry domeniu la 2 – fmMatchEntryNone.
   4. Inchide Proprietăţi panoul.
      
5. Apasă pe Mod de proiectare în temeiul Dezvoltator tab to exit the Design Mode.
   

You can now select any item from the combo box or type in the text to search for.

Step 3: Apply formulas

1. Create three helper columns adjacent to the original data range. See screenshot:
   
2. In the cell (H5) under heading of the first helper column, enter the following formula and press Intrați.

   =ROWS($B$5:B5)

   Aici B5 is the cell containing the first custmer's name of the column to be searched.
   
3. Double click the lower right corner of the formula cell, the following cell will automatically fill in the same formula.
   
4. In the cell (I5) under the second helper column header, enter the following formula and press Intrați. And then double click the lower right corner of the formula cell to automatically fill the cells below with the same formula.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   Aici M2 este celula legată de caseta combinată.
   
5. In the cell (J5) under the third helper column header, enter the following formula and press Intrați. And then double click the lower right corner of the formula cell to automatically fill the cells below with the same formula.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. Copy the original header row to a new area. Here I place the header row under the search box.
   
7. Select the cell under the first header (e.g. L5 in this example), enter the following formula into it and press the Enter key.

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   Aici A5: G281 is the entire data range that you want to displayed in the result cell.
   
8. Select this formula cell, drag the Mâner de umplere to the right and then down to apply the formula to the corresponding columns and rows.
   
   notițe:

   • Since there is no input in the search box, the results of the formula will show the raw data.
   • This method is case-insensitive, meaning it will match text regardless of whether you type in uppercase or lowercase letters.

Rezultat

Let's now test the search box. In this example, when I enter or select a customer's name from the combo box, the corresponding rows that contain that customer name in column B will be filtered and immediately displayed in the result range.

Creating a search box in Excel can significantly improve how you interact with your data, making your spreadsheets more dynamic and user-friendly. Whether you choose the simplicity of the FILTER function, the visual assistance of Conditional Formatting, or the versatility of formula combinations, each method provides valuable tools to enhance your data manipulation capabilities. Experiment with these techniques to find which works best for your specific needs and data scenarios. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Descoperiți mai multe sfaturi și trucuri Excel aici.

Articole pe aceeaşi temă