How to Create a Searchable Database in Excel

Written by darrin koltow
  • Share
  • Tweet
  • Share
  • Email

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.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    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

  2. 2

    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."

  3. 3

    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."

  4. 4

    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.

  5. 5

    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.

    SELECT *

    FROM ArtProducts

    where ArtProducts.price>2.00;

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.