How To Create A Database From Excel Spreadsheet

Databases are all the rage...and for a good reason. Most websites, apps, and businesses rely on a database to organize store and edit information. Databases are also useful for aggregating information. So whether you're creating a lightweight inventory system for your retail business or just planning a family reunion, a database is an excellent place to start.

In 1985, Microsoft introduced the first version of Excel for the Macintosh computer. It was one of the first graphical spreadsheet applications to use point and click mouse capabilities. Even though Excel is older than most millennials, it's still one of the most popular business software applications in the world.

In this tutorial, we'll create a database in Excel to store employee information. For the record, actual employee information should be stored securely in the cloud, and not in an Excel spreadsheet on your computer.

‍

What is an Excel database?

Every database is made up of information. A critical component of that information is how you organize it. A database's organization reflects the relationships between different pieces of information

If you're already familiar with spreadsheets, then you're used to using rows, the horizontal cells in spreadsheets, and columns, the vertical cells in spreadsheets.

In a database, each row is called a record. And each column is a field.

‍

What is a Record (rows)?

Records are the primary components of information that make up a database. Each row of our database will represent a new record. By putting all of that information in the same row, we're creating a relationship between each cell in the row.

In our employee database, each record holds information about a single employee. And each unique piece of information, like their name, title, department, and salary is a new field within our database.

What is a Field (columns)?

Fields are the pieces of information that each record shares. The very first field (column) in a database is called the key field. Unlike the other fields, the key field must be unique for every single record. So even though it's tempting to start our employee database example with a first name column, many employees share the same first name. Instead, we should use a unique identifier like an employee number.

How do you create a database?

First, let's set up our database fields or columns. We'll set the employee number as the key field to make sure that each record has a unique identifier. Next, we'll add additional fields for any values we want to store about our employees.

For this example, we'll add first name, last name, title, department, and salary as additional fields in the first row of our spreadsheet.

Now that the fields are in place, we're ready to start adding records, for each employee in our company or database. Starting with the employee ID, we'll fill in each row of information based on the employee record we are creating.

If you'd like to follow along, download this CSV of sample employee information.

Once all the employee records have been added to the spreadsheet, we are ready to turn our spreadsheet into an Excel table.

Highlight all the information on your spreadsheet (A1 to F11) and select Table from the Insert dropdown in the primary header.

Excel will automatically add your highlighted cells to the create table popup. Check My table has headers and select OK.

The new format of the table includes alternating colors and column dropdowns that allow you to search, sort, and filter the values stored in your database.

Sort, Search, Filter

Now that your spreadsheet has been upgraded to an Excel table, you'll see gray column dropdowns to the right of each field. These dropdowns will display the sort, search, and filter capabilities that will help you find the information you need in your database.

Sort

Sort changes the order that the records are in based upon the criteria you set for a specific field. For example, you can sort the records alphabetically, from highest to lowest, or by color.

Let's sort our table by salary, to quickly find out which employee in the database is receiving the largest compensation package.

With just 10 records in our database, it's pretty easy to at-a-glance spot the largest salary, but if your company has hundreds of employees, this task isn't so simple.

Search

Search, as its name implies, allows you to find records holding specific information within a column. Remember sort, search, and filter apply to columns instead of rows. So if you're searching for information from a single record, make sure that you have the right column before beginning your search.

Let's search the title field (column D) for any job title including vice president. Once we've entered the search term, Excel automatically removes any records that don't include vice president in the title column. We're left with just two records in our database.

Notice how the dropdown carrot icon changes once a search has been applied. This tells us that we're not seeing all the information in our database.

Filter

Filter is very similar to search—it allows you to remove or include specific results based upon the criteria you select.

Let's select the first name field dropdown. Underneath the filter heading you'll see an additional dropdown that allows you to filter by contains, does not contain, begins with, ends with, and more. Select begins with, and enter mark into the text field to the right. Hit enter.

The result should remove all database records except two from our view.

Combining Search, Sort, and Filter

If this functionality seems pretty basic, consider how you might combine them to find a quick answer to a question. Let's use the salary example from earlier.

Although it was easy to quickly see which employee had the highest total compensation, what if you needed to find the average software engineer salary at your company to create an offer for a new hire?

To solve this, we would want to filter job title by contains software engineer, and then use the average formula to find the mean of the two software engineer records in our database.

Dynamic database / Data Entry Form

Excel is a powerful tool for creating a database and storing information but, as you likely experienced, it has limitations. Using Excel as a database doesn't allow you to connect information or perform actions with your data.

Hivewire is a new kind of visual workflow builder that allows you to create a database and build processes from your data. By combining your data, actions, and automation in a single tool, you can get work done faster without worrying about which tab you're on. Try Hivewire today, import your Excel database to get started.