Excel Create Database (Table of Contents)
Create Database in Excel
If you want to create a database, MS Access is the tool you ideally should look for. However, it is a bit complicated to learn and master the techniques therein as MS Access. You need ample time to master those. In such cases, you can use excel as a good resource to create a database. It is easier to enter, store, and find specific information in the Excel Database. A well-structured, well-formatted excel table can be considered as a database itself. So, all you have to do is create a table which has a proper format. If the table is well-structured, you can sort the data in many different ways. Moreover, you can apply the filters to a well-structured data to slice and dice it as per your requirements.
How to Create Database in Excel?
We’ll be creating an employee database for the organization. Let’s see how to create a database in Excel by following the below process:
Data Entering to Create Excel Database
Data entering is the main aspect while you are trying to create a database in Excel.
Suppose this is the data which you are going to use as an employee database.
I have added the first few Employee ID’s. Say D01, D02, D03 and then dragged the remaining till row 12 using Fill Handle. Column second onwards contains the general employee information like First Name, Last Name, Designation, and Salary. Fill these information in cells manually as per your details. Make sure the format for Salary column is applied to all the cells in the column (Otherwise this database may cause an error while using).
Entering Correct Data
It is always good to enter the correct data. Make sure there is no space in your data. When I say no other blanks, it covers the column cells which are not blank as well. Try to the utmost that no data cells are blank. If you don’t have any information available with you, prefer to put NA over a blank cell. It’s also important to make the right input to the right column.
See the screenshot below:
Suppose as shown in the image above, you wrongly interchanged the column inputs. i.e. you have mentioned Designation under Last Name and Last Name under Designation, which is a serious drop-back when you are thinking of this as a master employee data for your organization. It may mislead some of your conclusions.
Suppose you have added a correct entry, but at 2nd row after last row (i.e. one row is left blank). It is also not recommended to do so. It is a breakdown for your data. See screenshot given below:
As you can see, there is one row left blank after row no. 12 ( second last row of the dataset) and added one new row which is Not recommended. On similar lines, you should not leave any blank column in the database.
Let’s see the screenshot below:
As you can see, column F is left blank. Which causes Excel to think, there is a split of data. Excel considers that a blank column is a separator for two databases. It is misleading, as the column after blank column is a part of your original database. It’s not the start column of a new database.
All the Rows are called Record in Excel Database
It is a kind of basic knowledge we should have about the database we are creating. Every single row we create/add is called as a Record in database. See the below screenshot for your reference:
Every Column is a Field in Excel Database
Every column is called Field in the Excel database. The column headings are called Field Names.
Format Table
Once you are done with inputting the data, it should be converted into a table for better visualization.
- Select cells A2 to F12 from the spreadsheet.
- Go to the Home tab.
- Select Format as Table drop-down menu. You can choose table layout of your own.
Once you click a particular table format, a table window will pop-up with the range of data selected and that range will be surrounded by a dotted line. You can change the range of the data therein the table dialog box as well.
Once you are happy with the range, you can choose OK. You can see your data in a tabular form now. See the screenshot given below:
Use Excel Database Tools to Sort or Filter the Data
You can use the drop-down arrows situated beside each Field Name to Sort or Filter the data as per your requirement. These options are really helpful when you are dealing with a large amount of data.
Expanding the Database
If you want to add some more records in your table, you can do it as well. Select all the cells from your table.
Place your mouse at the bottom of the last cell of your table. The mouse pointer will turn into a two-headed arrow. You can drag down the pointer from there until you want to add that much blank rows in your database. Subsequently, you can add data under those blank cells as well.
Database Formatting
Highlight cell A1 to F1 from the spreadsheet.
- Select Home tab
- Under the Home tab, go to Wrap Text as well as Merge and Center.
- You can also change the fill color. Select Fill Color. Choose the color of your interest. Here I have selected Green as a color.
This is how we have created our Database in Excel.
Things to Remember About Create Database in Excel
- Information about one item should be populated in one single row entirely. You can’t use multiple lines to add different data of the same item in the excel database.
- The field should not be kept empty. (Including Column Headings/Field Name).
- Data type entered in one column should be homogeneous. For eg. If you are entering Salary details in Salary column, there should not be any text string in that column. Similarly, any column containing text strings, should not contain any numerical information.
- Database created here is really a very small example. It becomes huge in terms of employees joining every now and then and becomes hectic to maintain the data again and again with the standard formatting. That’s why it is recommended to use databases.
Recommended Articles
This has been a guide to Create Database in Excel. Here we discuss how to Create Database in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
