How to Create a Searchable Database in Excel & Google Sheets

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

 

CreateDatabase Example

 

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.

 

CreateDatabase Field Names

 

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.

 

CreateDatabase Keyfield

 

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.

 

CreateDatabase FreezeTopRow

 

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.

 

CreateDatabase formatAsTable

 

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.

 

CreateDatabase HeaderRows

 

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

 

CreateDatabase TableDesign

 

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.

 

CreateDatabase SelectFilter

 

Click OK to apply the filter to the table.

 

CreateDatabase ApplyFilter

 

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

 

CreateDatabase ClearFilter

 

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.

 

CreateDatabase VLOOKUP

 

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.

 

CreateDatabase VLOOKUP ChangeNumber

 

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.

 

CreateDatabase New Field

 

  1. 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.
  2. Then type a formula into the first row of data.

 

CreateDatabase Calculated Field

 

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

 

CreateDatabase CopyDown

 

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.

 

CreateDatabase GS FreezeRow

 

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.

 

CreateDatabase GS FormatRows

 

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.

 

CreateDatabase GS CreateAFilter

 

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

 

CreateDatabase GS ApplyFilter

 

  1. Click OK to filter the data.

 

CreateDatabase GS Filtered

 

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

 

CreateDatabase GS RemoveFilter

 

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.

 

CreateDatabase GS NewField

 

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

 

CreateDatabase GS CalcField

 

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

 

CreateDatabase GS AutoFill

 

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

 

CreateDatabase GS AutoFilled