How to create a database in Excel
Excel is a very powerful tool when it comes to handling big numbers. Whether it is the sales data of your company or the employee check-in, check-out records for the whole month, if the data is converted into an Excel Database then it makes it a lot easier to understand, analyse and present the results to the related audience.
Therefore, in this tutorial we’ll learn how to create a database in Excel and also cover some of the advantages of converting ordinary data into Excel Database. Let’s do this by following these steps.
Step 1 – Create a Blank Excel Spreadsheet
– First of all you will need to open up a blank spreadsheet to create a database or if you already have a spreadsheet with data in it then we can convert that data into Excel Database format.
– For this, press the windows logo on the keyboard and type Excel. When you see the below shown image, press enter.
Step 2 – Understanding Data Sources in Excel
– We can use an existing Excel file or Table / Range from the same file and then convert it into Excel Database or we can import data into Excel from the following sources. Finally we can enter data manually as well.
Step 3 – Importing Data in Excel from CSV file
– For our tutorial we will import data from another file which is saved as a CSV File. This is a very common file format and usually data downloaded from web sources is in this format. However, we can import any type of data into Excel.
– Data imported through this method will automatically be converted into an Excel Table which is practically a proper database. In this database, the columns of the data become fields and rows become records of the database.
Step 4 – Customize the Data by adding a name
– We can customize the database by changing the name of the database as per our requirement and for the ease of use as well.
– We can do this by clicking on any cell inside the table and then going to the table properties as shown below.
Step 5 – Perform calculations on the Database Data
– The database has been created and Excel provides us with a lot of facilities to perform basic or complex calculations automatically with the data in the database.
– Let’s calculate the average age and average weight of the players from the database by using auto functions of the database.
– To turn on or off the auto function options in a database, select any cell inside the database and press CTRL+SHIFT+T. This will add a totals row below the database data.