How to make autocouplers in Excel: detailed instructions

Few who will like long and monotonously enter the same or the same type in the table. This is quite boring work, picking up a large amount of time. The Excel program has the ability to automate the entry of such data. This provides for the function of the autocillry of cells. Let's see how it works.

Operation of autofill in Excel

Autocomplete in Microsoft Excel is carried out using a special filling marker. In order to invoke this tool, you need to bring the cursor to the lower right edge of any cell. There will be a small black cross. This is a filling marker. You just need to hold the left mouse button and pull the sheet to the top one where you want to fill the cells.

The way cells will be fired already depends on the type of data that is in the original cell. For example, if there is a normal text in the form of words, then when drawing using a filling marker, it is copied to other cells of the sheet.

Auto confillment of cells numbers

Most often autofill is used to enter a large array of numbers that follow in order. For example, in a certain cell there is a number 1, and we need to numbered cells from 1 to 100.

1. Activate the filling marker and spend it down on the required number of cells.



2. But, as we see, only a unit was copied to all cells. Click on the icon, which is from the bottom to the left of the completed area and is called "Auto-Filling Parameters".



3. In the list that opens, set the switch to the "Fill" item.

As we see, after that, the entire required range was filled with numbers in order.

But it can be done even easier. You will not need to call autocomplete parameters. To do this, when the replenishment marker is down, then besides the left mouse button, you need to hold the Ctrl button on the keyboard. After that, the filling of the cells in order occurs immediately.

There is also a way to make autocopters a number of progression.

1. We introduce into neighboring cells the first two numbers of progression.



2. We highlight them. Using the filling marker we introduce data to other cells.



3. As we see, a consistent number of numbers are created with a given step.

Tool "Fill"

The Excel program also has a separate tool called "Fill". It is located on the ribbon in the "Home" tab in the Editing toolbar.

1. We introduce the data to any cell, and then select it and the range of cells that are going to fill.



2. Click on the "Fill" button. In the list that appears, select the direction in which cells should be filled.



3. As we see, after these actions, the data from one cell was copied into all the others.

With this tool, you can also fill in progressional cells.

1. We enter the number in the cell and allocate the range of cells that will be filled with data. We click on the "Fill" button, and in the list that appears, select the "Progression" item.



2. The progression setup window opens. Here you need to make a number of manipulations:
   • Select the location of the progression (on columns or by lines);
   • type (geometric, arithmetic, dates, autofill);
   • set a step (by default it is 1);
   • Set the limit value (optional parameter).

   In addition, in some cases, units are set.

   When all settings are made, click on the "OK" button.



3. As you can see, after that, the entire dedicated range of cells is filled in accordance with the progression rules you installed.

Autofill Formulas

One of the main tools Excel is formulas. If there is a large number in the table of the same formulas, you can also use the auto-complete function. The essence does not change. It is necessary to copy the formula to other cells in the same way. At the same time, if the formula contains references to other cells, then by default when copying in this way the coordinates change according to the principle of relativity. Therefore, such links are called relative.

If you want to automatically become fixed with the address, then you need to put the dollar sign in front of the coordinates of the rows and columns. Such links are called absolute. Then, the usual autofill procedure is performed using a filling marker. In all the cells filled in this way, the formula will be absolutely unchanged.

Lesson: Absolute and relative links to Excel

Autocomplete by other values

In addition, the Excel program provides autofill with other values ​​in order. For example, if you enter some date, and then using the filling marker, select other cells, then the entire selected range will be filled with dates in a strict sequence.

In the same way, it is possible to make auto-fill on the days of the week (Monday, Tuesday, Wednesday ...) or by months (January, February, March ...).

Moreover, if there is any digit in the text, then Excel recognizes it. When using the filling marker, there will be copying text with a change in the incident. For example, if you record the expression "4 case" in the cell, then in other cells filled with a filling marker, this name will be transformed into "5 housing", "6 hull", "7 case", etc.

Adding your own lists

The capabilities of the autofill function in Excel are not limited to certain algorithms or pre-installed lists, such as, for example, days of the week. If desired, the user can add a personal list to the program. Then, when writing to the cell of any word from the elements that are in the list, after applying the filling marker, the entire selected range of cells will be filled with this list. In order to add your list, you need to perform such a sequence of actions.

1. We make the transition to the "File" tab.



2. Go to the "Parameters" section.



3. Next, we move to the subsection "Advanced".



4. In the "General" settings block in the central part of the window we click on the "Change Lists ..." button.



5. The list of lists opens. In the left part there are already available lists. In order to add a new list, write to the desired words in the "List Elements" field. Each element should begin with a new line. After all the words are recorded, click on the "Add" button.



6. After that, the lists window closes, and when it opens it, the user will be able to see the elements that it added already in the active lists window.



7. Now, after you make a word in any cell of the sheet, which was one of the elements of the added list, and apply the fill marker, the selected cells will be filled with characters from the corresponding list.

As you can see, autofill in Excele is a very useful and convenient tool that allows you to significantly save time on adding the same data repetitive lists, etc. The advantage of this tool is that it is customizable. You can make new lists or change the old ones. In addition, with the help of autofill, it is possible to quickly fill the cells with various types of mathematical progressions.