Database in Excel: features of the creation, examples and recommendations

Excel is a powerful tool that combines the most useful and useful functions for users. These include graphs, tables, diagrams, accounting, computation, calculations of various functions, and so on. From this article we will learn how to create a database in Excel, what it is needed for, and what tips will help us make it easier to work with it.

What is a database?

This is a special structure that contains and stores information materials of various purposes. In simple terms, this is a set of information organized and laid out on the shelves. How many people study in the class, their characteristics, birth dates and report card - all this together represents a database. It is needed everywhere - in industrial and technical enterprises, in educational and medical institutions, in law enforcement and even in public catering establishments. After all, the list of dishes, their recipes with caloric content, and also the description is also a container of data.

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 any frills. The real data containers are quite cumbersome and represent a large information system with an internal "core", which consists of many lines of code and written by a specialist.

Our work will be a single table, in which there will be all the necessary information. Before deciding how to make a database in Excel, you need to learn the special terms used when interacting with it.

The horizontal lines in the markup of the Excel sheet are usually called records, and vertical columns are called fields. You can start work. Open the program and create a new book. Then, in the very first line, you need to write down the names of the fields.

Cell Formatting Features

It is useful to learn how to properly format the contents of a cell. If your database in Excel contains any monetary amounts, then it is better to specify a numerical format in the appropriate fields, where two digits after the comma will appear. And if somewhere there is a date, then you should select this place and also set the appropriate format for it. Thus, all your data will be processed correctly and without errors. All operations with empty fields of the program are made through the context menu "Format of cells".

Also important is the corresponding design of the project. The sheet on which the project is located must be signed in order to avoid confusion. In order for the system to distinguish between simple content and headings and signatures, it should be italicized, underlined or bolded, while not forgetting to place the names in separate, unified fields. It is worth doing this for the use of tools such as auto-format and auto-filter.

What is AutoForm in Excel and why is it required?

Creating a database in Excel - the occupation is difficult and painstaking. To help the user make their work easier, the program can offer him the automatic filling of cells with pre-defined information. For example, the width of the column, the height of the line, the size and type of the font, the color of the field, etc. - all can in one click make for you an autoform if properly configured. Convenient, right?

Fixing the "headers" of the database

In addition, do not forget about fixing the first line. In Excel 2007, you can do this as follows: go to the "View" tab, then select "Lock areas" and in the context menu click on "Pin the top line". 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 - the names of the fields, which is inconvenient for the user.

After the top line is fixed, select the first three lines in the future database and add the borders of the cells.

Continuation of work on the project

In order to continue working, you need to come up with the basic information that the database will contain in Excel. An example of it is given below.

Suppose we want to create a database of collected funds from parents in the school fund. The amount of the amount is unlimited and individual for each person. Let the classroom has 25 children, which means that there will be a corresponding number of parents. In order not to accumulate a database with a large number of records, it is worth making drop-down lists that will hide superfluous information, and when it is needed again, they helpfully provide it again.

How do I create drop-down lists?

We 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, under them we write down all the necessary information in the empty cells.

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, you need to assign a range of values, names, to all the information about the parents. We pass to the page where all the data under the name "Parents" is written and we open a special window for creating the name. For example, in Excel 2007, you can do this by clicking on "Formulas" and clicking on "Assign name". In the field of the name we write: FIO_delater_selection. But what to write in the value range field? Here everything is more complicated.

Value Range in Excel

There are several types of value ranges. The range with which we work 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 OFFSET formula. It, regardless of how the arguments were given, returns a reference to the original data. In the drop-down list, which will end up as a result, there should be no null values. With this just perfectly copes the dynamic range. It is given by two co-ordinates of cells: the top left and right bottom, as if on a diagonal. 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 A5. This value is the upper left cell of the range. Now, when the first element to be found is found, let's move on to the second one.

The lower right cell is defined by arguments such as width and height. The value of the last one will be 1, and the first will calculate the formula СЧЕТ3 (Parents! $ B $ 5: $ I $ 5).

So, in the range field write = SHIFT (Parents! $ A $ 5; 0; 0; COUNTERS (Parents! $ A: $ A) -1; 1). Press the OK key. In all subsequent ranges, the letter A is changed to B, C, and so on.

Working with the database in Excel is almost complete. 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 "Parent name" field. There will be input information. In the "Check input values" window in the tab named "Parameters", write it in the "Source" = FIR_Prodiator_selection. In the "Data type" menu, select "List".

Similarly we proceed with the rest of the fields, changing the name of the source to the corresponding cells. Work on drop-down lists is almost complete. Then select the third cell and "stretch" it across 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. The Excel program can offer a variety of ways to design the database to choose a user. The number of color schemes is very rich, you only need to choose the right color for your liking. In addition, it is not necessary to perform the entire database in a single style, you can paint one column in blue, the other in green, and so on.

How to move a database from Excel to Access

Not only can Excel do a database. Microsoft has released another product that perfectly manages this difficult task. The name is Access. Since this program is more adapted to create a database than Excel, then the work in it will be faster and more convenient.

But how do I get an Access database? Excel takes into account this desire of the user. This can be done in several ways:

• You can select all the information contained on the Excel sheet, copy it and transfer it to another program. To do this, select the data you want to copy and right-click it. In the context menu, click Copy. Then switch to Access, select the "Table" tab, the "Views" group and click the "View" button boldly. Select the "Table mode" item and insert the information by right-clicking and selecting "Paste".

• You can import a .xls (.xlsx) format sheet . Open Access by closing Excel. On 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 associate an Excel file with a table in the Access program. To do this, in "Excel" you need to select a range of cells containing the necessary information, and by clicking on them with the right mouse button, set the name of the range. Save the data and close Excel. Open Access, on the tab called External Data, select Excel Spreadsheet and enter its name. Then click on the item that prompts you to create a table for communication with the data source, and specify its name.

That's all. The work is ready!