Excel files can contain an enormous amount of information. You can filter and sort all you want, but sometimes you just need a simple solution. This tutorial shows how to create a search box that will quickly let you find a specific piece of data inside your Excel document.
Create a Custom Search Box
Although there are several methods to search the contents of your Excel file, this one mixes creativity and ease of use. After adding the search box, it’ll automatically highlight any word you type across the whole document, allowing you to quickly find what you’re looking for.
Open your Excel file using the Microsoft Excel app. For example, we’re using an Excel file that contains Box Office data.
Add an extra space to place the search engine by right-clicking the row and selecting Insert. Personalize the look as you want.
To enable the Developer menu, navigate to File Menu -> Options -> Customize Ribbon -> Developer checkbox.
Click the Developer menu at the top, select Insert, then choose the Text Box component.
Go back to the space and draw the box using your mouse. You will have an empty text box.
Right-click the text box, and select Properties. Insert the cell number for the search box in the LinkToCell field. Note that you can locate it by looking at the row and the column. In our example, it was C2.
Click once on the Design Mode menu at the top to unselect the text box and save the changes.
To enable the search function, create a rule for the respective data. Select the data by pressing the keyboard shortcut Ctrl + A, click the Home menu on the top bar, then select Conditional Formatting -> New Rule.
Select Use a Formula to Determine Which Cells to Format, and add the function below to the empty field below it. Make sure to edit $C$2 as the cell you have the box in. Also, A5 is the first cell of the whole range.
=ISNUMBER(SEARCH($C$2,A5))
Click the Format button to add a design to the highlights. You can customize them as you would like, but we are only using the Fill option to color the highlighted results. After doing this, when we search for a text utilizing the box, the text will be highlighted across the file.
To customize the search box and make it more appealing, return to the Developers menu, and select Design Options to customize the width, height, color, etc.
Save your work by pressing Ctrl + S. Share this file with colleagues or friends, either by sharing it via OneDrive or manually.
Tip: if you’re noticing duplicates, blank rows, or other issues, find out how to clean up your data in Excel.
How to Find Data Inside an Excel File
Apart from the self-made Excel search box, you can quickly find data inside your Excel file with the keyboard shortcut Ctrl + F, then start searching and filtering your search. Additionally, if you’re using Excel on Android or iOS, you can search across the file by simply tapping the Search icon at the top.
Microsoft Excel is one of the most versatile spreadsheet processors available. With thousands of functions and formulas to apply to your Excel files, you can make all your data dreams come true. Moreover, you can make use of the Excel tips and tricks to enhance your efficiency with it. It’s up to you to start perfecting your work in Excel!
All images and screenshots by Mustafa Ashour.
Our latest tutorials delivered straight to your inbox
