Use Excel as a database - how it works

If you only need a small database without complex queries, you can also use Excel as a database. Thus you save the costs for MS Office Access and can still manage your data relatively well.

Create a database in Excel

In our small example, we create a supplier database.

• After you have considered which information should be managed in the database, distribute the data sensibly in the columns.
• If possible, you should always put the data for which you want to create queries in a separate column.
• We provide the respective columns with self-explanatory headings such as supplier name, street, zip code, city, contact person, telephone number, supplier since, supplier for, delivery time, discounts and so on.
• In the first column we put an internal supplier number. So that you can assign the ID numbers later, it consists of one or more letters, depending on how many tables you want to create, and then a sequential number. The letters are abbreviations for the respective tables. In the supplier table it would consequently be an L.

The database functions in Excel

In Excel you cannot create SQL queries as complex as in Access, but the spreadsheet also has some database functions.

• To access the database functions, click on the "Formulas" tab.
• Select the "Insert function" button in the ribbon.
• Under "Insert function" you will find the option "Database" in the pull-down menu of "Select category".
• The database functions available in Excel are then displayed under "Select function".
• If you click on the individual functions, you will receive an explanation of what you can use the respective database function for.