How to Create a Searchable Database in Excel

Updated April 17, 2017

Learning how to create a searchable database in Microsoft Excel lets you use Excel as you would a database application like Microsoft Access: as a means to create and organise data in a way that other data applications can easily access. The tool built into Excel you can use to verify you've created a searchable database is Microsoft Query. This application lets you run queries in a commonly used data query language called SQL on data sources in several different formats.

Type the following data into a new spreadsheet, pressing "Tab" in place of the commas. This data is for a hypothetical art supply store. Click your mouse on the data's top left cell, then drag down to the bottom right cell to select the data. Type "ArtProducts" in the left text box above the worksheet grid. This action names the table, which makes it easier to identify in database queries.

product, price

paintbrush, 1.98

Gesso, 3.45

Click the "File" menu's "Close" command, then click "OK" to indicate you want to save the workbook. Type "ArtProduct" for the file name, then click "Save" to save the workbook. Click the "File" menu's "New" command to create a new workbook, then click the "Data" tab's "Other sources" icon. Click "Microsoft query."

Click "Excel files" in the "Choose data" dialogue box, then click "OK." Microsoft query will display a dialogue box with which to choose an Excel workbook file. Navigate to and double click the workbook you saved in the previous step, then click "OK."

Click the arrow button in the "Columns" dialogue box to tell Excel you want to base your query on the "ArtProduct" database listed in the left pane. Click "Next," then click the "View data" option button. Microsoft Query's main window will open.

Click the "View" menu's "SQL" command, then type the SQL statement following this step into the new dialogue box. This statement runs a query on your "ArtProducts" database to select only those products whose price is greater than £1.30. Click "OK" to perform the query. Excel will display only the "Gesso" product, confirming that you've created an Excel database whose records you can selectively search with SQL commands from querying programs like Microsoft Query.


FROM ArtProducts

where ArtProducts.price>2.00;

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Darrin Koltow wrote about computer software until graphics programs reawakened his lifelong passion of becoming a master designer and draftsman. He has now committed to acquiring the training for a position designing characters, creatures and environments for video games, movies and other entertainment media.