Excel is a powerful tool that combines most of the useful and necessary functions for users. These include graphs, tables, charts, record keeping, making calculations, calculating various functions and so on. In this article, we will learn how to create a database in Excel, why it is needed, and what tips will help us make it easier to work with.
What is a database?
This is a special structure that contains and stores informational materials of various purposes. In simple terms, it is a set of organized and organized information. How many people study in the classroom, their characteristics, dates of birth and report card - all this together constitutes a database. It is needed everywhere - at industrial and technical enterprises, in educational and medical institutions, in power structures and even in public catering establishments. After all, the list of dishes, their recipe with calories, as well as the description, is also a data warehouse.
Here we figured it out. Now you need to find out what the database is in Excel, and how to create it.
Creating a data warehouse in Excel
The base created by us will be simple and without frills. Real data containers are rather cumbersome and represent a large information system with an internal “core”, which consists of many lines of program code and is written by a specialist.
Our work will be a single table in which there will be all the necessary information. Before you begin to address the issue of how to make a database in Excel, you need to find out the special terms used when interacting with it.
Horizontal lines in the layout of the Excel sheet are usually called records, and vertical columns are called fields. You can get to work. Open the program and create a new book. Then in the very first line you need to write the names of the fields.
Cell Format Features
It’s useful to learn how to properly format cell contents. If your database in Excel will contain any sums of money, it is better to immediately indicate the number format in the corresponding fields, in which two digits will come after the comma. And if there is a date somewhere, then you should highlight this place and also set the appropriate format for it. Thus, all of your data will be formatted correctly and without errors. All operations with empty program fields are performed through the “Cell Format” context menu.
The relevant design of the project is also important . The sheet on which the project is located must be signed in order to avoid confusion. In order for the system to be able to distinguish simple content from headings and signatures, it should be highlighted in italics, underline or bold, while remembering to put the names in separate, unified fields. This is worth the effort to use tools such as AutoForm and AutoFilter.
What is Autoform in Excel and why is it required?
Creating a database in Excel is a difficult and painstaking task. To help the user facilitate the work, the program can offer him automatic filling of the cells with predetermined information. For example, column width, row height, font size and type, field color, etc. - everything can make an autoform for you in one click, if you configure it correctly. Convenient, right?
Fixing the database header
In addition, do not forget about fixing the first line. In Excel 2007, this can be done as follows: go to the “View” tab, then select “Freeze areas” and click on “Freeze top row” in the context menu. This is required to fix the "cap" of the work. Since the Excel database can be quite large in volume, when scrolling up and down the main information will be lost - field names, which is inconvenient for the user.
After the top row is pinned, select the first three rows in the future database and add cell borders.
Continuation of the project
In order to continue working, you need to come up with the basic information that the database in Excel will contain. An example of it is given below.
Suppose we want to create a database of raised funds from parents to the school fund. The amount is not limited and individual for each person. Let 25 children study in the classroom, which means that there will be an appropriate number of parents. In order not to pile up the database with a large number of records, it’s worth making drop-down lists that hide the extra information, and when it is needed again, they will helpfully provide it again.
How to create drop-down lists?
Copy the names of the fields and transfer them to an empty sheet, which for convenience also needs to be called. Let it be, for example, “Parents”. After the data has been copied, we write down all the necessary information in empty cells under them.
In order for the MS Excel database to provide the ability to select data from the drop-down list, you must create a special formula. To do this, assign all the information about the parents a range of values, names. We go to the sheet where all the data is written under the name "Parents" and open a special window to create a name. For example, in Excel 2007 this can be done by clicking on "Formulas" and clicking on "Assign Name". In the name field, write: Full name of parent_selection. But what to write in a value range field? Everything is more complicated here.
Range of values in Excel
There are several types of value ranges. The range we are working with is called dynamic. This means that all named cells in the database can change their boundaries. Their change occurs depending on the number of values in a certain range.
To get a dynamic range, you must use the formula OFFSET. It, regardless of how the arguments were specified, returns a reference to the source data. The resulting drop-down list should not contain empty values. The dynamic range is excellent at this. It is defined by two cell coordinates: the upper left and lower right, as if diagonally. Therefore, you need to pay attention to the place where your table begins, or rather, the coordinates of the upper left cell. Let the plate begin at location A5. This value will be the upper left cell of the range. Now that the first element you are looking for is found, let's move on to the second.
The bottom right cell is defined by arguments such as width and height. The value of the latter let it be equal to 1, and the first one will calculate the formula COUNT3 (Parents! $ B $ 5: $ I $ 5).
So, in the range field, write = OFFSET (Parents! $ A $ 5; 0; 0; COUNT (Parents! $ A: $ A) -1; 1). Click OK. In all subsequent ranges, the letter A is changed to B, C, etc.
Work with the database in Excel is almost complete. We return to the first sheet and create drop-down lists on the corresponding cells. To do this, click on an empty cell (for example, B3), located under the field "Name of parents". Information will be entered there. In the "Validation of Entered Values" window, in the tab called "Parameters", write in "Source" = full name of parent_selection. In the menu "Data Type" indicate "List".
We do the same with the rest of the fields, changing the name of the source to correspond to these cells. The work on the drop-down lists is almost complete. Then select the third cell and “pull” it through the entire table. The database in Excel is almost ready!
Database Appearance
Beautiful design also plays a significant role in the creation of the project. Excel can offer the user a variety of ways to design a database. The number of color schemes is very rich, you just need to choose the color you like. In addition, it is not necessary to run the entire database in a single style, you can color one column in blue, the other in green, etc.
How to transfer database from Excel to Access
Not only Excel can make a database. Microsoft has released another product that is superbly managing this difficult task. Its name is Access. Since this program is more adapted for creating a database than Excel, then work in it will be faster and more convenient.
But how do you get the Access database? Excel takes into account this desire of the user. There are several ways to do this:
• You can select all the information contained in an Excel sheet, copy it and transfer it to another program. To do this, select the data intended for copying and right-click. In the context menu, click "Copy." Then switch to Access, select the “Table” tab, the “Views” group and boldly click on the “View” button. Select “Table Mode” and insert information by right-clicking and selecting “Paste”.
• You can import a sheet in .xls (.xlsx) format. Open Access by closing Excel first. In the menu, select the “Import” command, and click on the desired version of the program from which you will import the file. Then click OK.
• You can link an Excel file to a table in Access. To do this, in Excel, select the range of cells containing the necessary information, and right-click on them to specify the name of the range. Save the data and close Excel. Open Access, on the tab under the name External Data, select Excel spreadsheet and enter its name. Then click on the item that suggests creating a table for linking with the data source, and indicate its name.
That's all. The work is ready!