How to Create a Searchable Database in Excel & Google Sheets

This tutorial demonstrates how to create a searchable database in Excel and Google Sheets.

 

 

Excel has some amazing database features and is a perfect place to create a fully searchable flat-file database. A flat-file database contains all its information in a single table. Excel has the ability to store information in a table format with columns as the database fields, and rows as the database entries. You can then use features like sorting, filtering, and pivot tables.

Fields and Key fields

The labels you put at the top of each column describe the data that is stored in the rows beneath them. In database language, these labels are known as fields and go into the first row of the table you are creating. In the example below, these field names have gone into Row 1 of the spreadsheet.

 

 

Once you have created your field names, you can start entering your data. The field ItemID is a key field and can be entered manually for the first row (e.g., 1), and then from the second row down you can add a formula to increase the number size by 1 as you go down each row. A key field is used to identify a unique row of data. For example, in the graphic below, there are two instances of a book called “Bridge to Terabithia” – however, they are two separate items as identified by the ItemID field.

 

 

If you have many rows of data, you can lock the first row of field names in place by freezing the top row of data.

In the Ribbon, select View > Window > Freeze Panes > Freeze Top Row.

 

 

Note: If field names are not in the first row of the spreadsheet, you can still freeze the row that contains the field names. Just select the first entry in the data and click on Freeze Panes instead of Freeze Top Row.

Creating a Table

Once you have typed in or imported your data, you can convert the database to an Excel table.

Click in the data, and then in the Ribbon, select Home > Styles > Format as Table.

 

 

Select the type of format you wish to apply, and then, in the Format As Table dialog box, make sure that My table has headers is checked.

 

 

When the format is applied, a new tab called Table Design appears in the Ribbon.

 

 

Filter Data

Once you have formatted your data as a table, the ability to filter by each field is automatically applied. Click on the drop-down list and select the value you wish to filter on to show only values that match.

 

 

Click OK to apply the filter to the table.

 

 

To clear the filter, either click Select All in the drop-down list or in the Ribbon, select Home > Editing > Sort & Filter > Clear.

 

 

VLOOKUP Search

You can use the VLOOKUP Function to search for specific items in your database by making use of the unique ID field: in this case, ItemID.

 

 

The image below shows a vlookup table (in Columns F:G). Here, entering 45 in the search cell (G1) grabs the relevant data (in G2:G4) from ItemID #45 of the database.

 

 

Import Data

Often, you can obtain data from outside of Excel from a different data source such as a TXT, CSV, XML, or HTML file – or a different database, such as SQL or MYSQL. Import into Excel, and then clean up the data accordingly. Data comes into Excel formatted as a table.

Calculated Fields

You can add calculated fields to your database. For instance, you can combine two separate fields together or multiply fields (like quantity × price).

Here’s an example of a calculated field that combines two fields together in a desired format.

1. Add a new field – Description – to the database shown in the example above.

 

 

2. As soon as you type in the field name and press ENTER, the new field is added to the current table and the table is extended.
3. Then type a formula into the first row of data.

 

 

4. When you press ENTER, the rest of the table is automatically populated.

 

 

Create a Searchable Database in Google Sheets

The database functionality in Google Sheets is very similar.
Once you have set up field names and typed in or imported data into your Google sheet and cleaned it up, you can freeze the top row as you do in Excel.

Lock Field Names

Click in the top row of your data and then, in the Menu, select View > Freeze > 1 row.

 

 

Add Formatting

While Google Sheets does not have an option to format the data as a table, you can format the data with alternate colors to make it easier to read.

 

 

Apply Filter

You can also apply filters to the data.

1. Select the column you wish to filter on, and then in the Menu, select Data > Create a Filter.

 

 

2. Click Clear to clear the check marks from the list and then check the item you wish to filter on (here, Biology).

 

 

3. Click OK to filter the data.

 

 

To remove the filter, either click clear in the drop down list of the filtered data, or in the Menu, select Data > Remove filter.

 

 

Insert New Fields

1. To add a new field to the database, type the field name in the next available column to the right of the data.
2. As soon as you press ENTER, the field is added to the table and formatted accordingly.

 

 

You can now create a calculated field as you did in Excel.

 

 

3. When you press ENTER, you get the option to autofill the database.

 

 

4. Click the check mark to copy the formula down and autofill the data.