How to Autofill Cells in Excel

Excel allows not only to process large amounts of data, but also, thanks to various tools and functions, to do it as quickly and efficiently as possible. In this article, we will analyze how autofill cells with data works.

How autocomplete works

In order to perform autocomplete in Excel, you need to use the so-called fill marker. This is done quite simply:

1. Move the mouse pointer to the lower right corner of the cell from which we want to duplicate the information.
2. A black plus sign should appear, which is fill handle. Hold down the left mouse button and drag it down / up / right / left – depending on which direction we need (in our case – down).
3. Having stopped at the cell to which we want to autofill, release the button.

Note: filling cells with marker depends on what data is contained in the source. In our case, this is a text value that was simply copied.

Automatic filling with numbers

One of the most common uses for autocomplete is to enter consecutive numbers. Let’s say we need to number from 1 to 25. In order not to enter values ​​manually, we do this:

1. We write a unit in the topmost cell of the future list. Then, as described above, we use fill handle, dragging it down 25 lines.
2. As a result, in all cells we get a number 1. After that, click on the icon that appears to the right of the filled range and select the command from the proposed list “Fill”.
3. In just a few clicks, we were able to automatically enter numbers from 1 to 25, which is what was required.

Alternative method

While pulling fill marker down with the left mouse button pressed, also hold the key Ctrl. Next to the usual icon in the form of a black plus sign, another smaller plus sign should appear on the left and above.

After that, the numbers will immediately be filled in order, and there is no need to use the menu with autocomplete options.

Filling in Mathematical Progression Values

Using autocomplete, you can quickly create a series of progressions, however, only arithmetic ones.

1. We print the first numerical value in the initial cell of the future row, and the second number in the adjacent cell, taking into account the progression step.
2. With both cells selected, use fill handle, which will appear at the bottom of the last one (in our case, the bottom one).
3. Everything is ready, we have continued the series of arithmetic progression with a given step equal to two.

Note: If the first two cells contain numbers 1 и 2, then we get another way to fill in numerical values ​​in order (progression with a difference of one).

Using the “Fill” function

Autocomplete in Excel can be done using a special tool “Fill”. The algorithm of actions is as follows:

1. We enter the information in the original cell. After that, in any convenient way (for example, using the left mouse button pressed), select the area of ​​cells to which we want to apply autocomplete.
2. In the tool group “Editing” of the main tab, click on the icon in the form of an arrow down (this is the function “Fill”). A list of possible options will open, among which we select the one that is needed (“way down” in our example).
3. We get an automatically filled range of cells.

Filling in a series of progressions

Using the function “Fill” it is possible, among other things, to continue the progression series:

1. As in the algorithm above, we indicate the first value in the initial cell. Then select the required range, click on the tool “Fill” and stop at the option “Progression”.
2. Set the progression parameters in the window that appears:
   • because we initially made a selection, the appropriate location is already immediately selected, however, it can be changed if desired.
   • select the type of progression: arithmetic, geometric, dates, autocomplete. This time we will focus on geometry.
   • indicate the desired step.
   • fill in the limit. Not necessarily – if a range has been selected in advance. Otherwise, a specific number must be specified.
   • in some situations it is required to select a unit of measure.
   • when all parameters are set, click OK.
3. Everything is ready, we have received a series of geometric progression numbers, taking into account the settings made.

Autocomplete cells with formulas/functions

Another common use case for autocomplete is to quickly copy formulas/functions. This takes into account contained in the source cell.

Let’s say we have a list of goods, their cost and sales in units. It is necessary to calculate the total revenue for each position:

1. In the first cell of the resulting column, we write the formula as the product of the price by the number of pieces sold. In our table – we put a sign “=” (any formula must start with a sign “equals”) and multiply the corresponding cells: =B2*C2.For more information on how to perform this action, see our publication -.
2. By pressing the key Enter on the keyboard we get the result. Now it remains only to use fill handleto stretch/copy the formula to the remaining lines.
3. The calculation of revenue for each product is ready.By selecting an arbitrary cell from the automatically filled range, we can make sure that the formula is copied taking into account the type of links (in this case, they are relative).

Other types of autocomplete

Excel features allow you to auto-complete in order not only numbers, but also other values ​​uXNUMXbuXNUMXbsuitable for this. However, the action plan remains the same.

Dates

1. Enter the date in the original cell. Then we pull fill marker in the chosen direction (say, to the right).
2. We get consecutive dates in the selected range.
3. Also instead of marker you can use the function “Fill”, having previously selected a range of elements in the desired direction and selecting it in the available options.

Note: this can be customized to our liking.

Days of the week/months

Similarly, cells are automatically filled by days of the week or names of months (in the screenshot).

Arbitrary values

Sometimes in a certain range of cells it is required to enter the same type of data with a change in the contained numeric value in ascending order. For example, it could be a list of addresses.

When dragging the value from the original cell to others, we see that only the house number changes: 2, 3, 4, 5, etc.

Create your own lists

If the required autofill option is not available in Excel, you can create it manually. In this case, we are guided by the following action plan:

1. Open the menu “file”.
2. Select an item from the list on the left “Parameters”.
3. In the options that open, go to the subsection “Additional”. Scroll through the contents on the right side of the window, find the group “General”, in which we click on the button “Change lists…”.
4. A window with standard lists will appear on the screen. The item should be selected by default. “NEW LIST” (if not, mark it manually). To create your own, click inside the field “List Items”, after which we add the necessary values ​​in order, each of which must begin on a new line. When everything is ready, click “Add”.
5. The created list will appear in the list on the left. Now the window can be closed. We also exit the program parameters.
6. We write in a free cell any word from the created list, after which you can use fill handle.We get a range of cells filled with the location of the values ​​in our list. Moreover, this is done cyclically, i.e. after the last word, the first one begins again – and so on until the last element of the selected area.

Conclusion

Thus, thanks to autocomplete, the process of entering the same type of data in Excel can be accelerated as much as possible, which makes this function one of the most useful and indispensable for almost every user. And this means that the time spent on studying this tool will pay off many times in the future.