How to Create a Searchable Database in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on June 27, 2023

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

CreateDatabase Example


Excel has some amazing database features and is the perfect place to create a fully searchable flat-file database. A flat-file database contains all its information in a single table, with columns as the database fields and rows as the database entries. You can then use features like sorting, filters, 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

  1. Once you have created your field names, you can start entering your data. The field ItemID is a key field, used to identify a unique row of data.
    One option is to use AutoFill to create a series of numbers, but you can use any unique identifier. 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

  1. 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, go to 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 Freeze Panes instead of Freeze Top Row.

Create a Table

Once you have typed in or imported your data, you can convert the database to an Excel table and gain all of the associated benefits.

  1. Click anywhere within the data, and then in the Ribbon, go to Home > Styles > Format as Table.

CreateDatabase formatAsTable

  1. Choose a format 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.

  1. Click on the filter button and check the value(s) you wish to filter for.

CreateDatabase SelectFilter

  1. Click OK to apply the filter to the table, hiding all unchecked items.

CreateDatabase ApplyFilter

  1. To clear the filter, click Select All in the filter drop down or, in the Ribbon, go to Home > Editing > Sort & Filter > Clear.

CreateDatabase ClearFilter


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

CreateDatabase VLOOKUP

The picture below shows a vlookup table (in Columns F:G). Here, typing 45 into 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.

  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

Database functionality in Google Sheets is very similar. Once you have set up field names and typed in or imported data into your worksheet 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, go to 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 by, and then in the Menu, go to Data > Create a Filter.

CreateDatabase GS CreateAFilter

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

CreateDatabase GS ApplyFilter

  1. Click OK to filter the data, hiding all unchecked items.

CreateDatabase GS Filtered

  1. To clear the filter, click Clear in the filter drop down. Or in the Menu, go to 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.

CreateDatabase GS NewField

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

CreateDatabase GS CalcField

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

CreateDatabase GS AutoFill

  1. Click the checkmark to copy the formula down.

CreateDatabase GS AutoFilled

AI Formula Generator

Try for Free

See all How-To Articles