How to use fill handle in Excel

4
9500
How to use fill handle in Excel
Excel Spreadsheets

O Excel, as it is known, brings several resources that can facilitate work, however the lack of knowledge of such resources can make the day to day of users become repetitive and even tedious.

One of these features is the fill handle, which, despite its simplicity of use, can be used on several occasions. In this way, we separate some tips and ways to use this feature to facilitate and avoid repetitive work. See below for more details:

Fill handle

The fill handle is basically a feature that aims to convey a logical sequence of data in a spreadsheet, facilitating the insertion of such data. For a better understanding, let's use a practical example:

Excel Spreadsheets

A user, for some reason must create a sequential number list, so for a small list the work would be irrelevant, but if it is an extensive list, the work becomes difficult. Thus, by using the fill handle feature, simply insert an initial sequence, select it and drag it to complete it.

See the images below for a better understanding:

How to use fill handle in Excel

How to use fill handle in Excel

Use on days of the week

We can use the fill handle to enter days of the week. In the example below, we enter only the days "Monday" and "Tuesday". After selecting the days inserted, drag with the fill handle represented by the sign "+" in the lower right corner of the selected cells.

How to use fill handle in Excel

How to use fill handle in Excel

Use in months of the year

In the example below, we enter only the month of January in the first cell, and then we drag the selection to the other cells until the other months are completed.

How to use fill handle in Excel

How to use fill handle in Excel

Basic excel and intermediate online courses

Use in formulas

There is also the possibility of repeating formulas by means of the fill handle. See the example below:

How to use fill handle in Excel

In the above example, we need to calculate the average of a class. In this way, we only calculate the average of the first student by means of the AVERAGE formula based on his grades obtained. Then we drag the result of the function to the other students and this way the function will be repeated for each student.

How to use fill handle in Excel

Use in random sequences

When you want to repeat a sequence of data that does not have a logic, simply create the terms to be repeated, select the information and then follow the standard procedure with the fill tab as shown below:

How to use fill handle in Excel

How to use fill handle in Excel

Use in logical sequences

In addition to using traditional sequences, it is also possible to vary the various types of combinations. In the example below we perform a logical sequence with even numbers, and another sequence with days of the week counted every two days.

How to use fill handle in Excel

How to use fill handle in Excel

How to use fill handle in Excel

How to use fill handle in Excel

Catching cells

In some cases, it is necessary to use the cell lock feature so that the use of the fill handle does not return errors in the other copies. See below for a practical example for better understanding:

The image below shows the ratio of overtime of a company, calculating as base the value of R $ 9,50 for each overtime. In this way, each employee will receive a value relative to the hours worked.

How to use fill handle in Excel

Note that if we do the traditional calculation by multiplying the number of hours worked by the overtime value and then using the fill handle to complete the other employees in the list, we will have the following error:

How to use fill handle in Excel

This is because Excel interprets autocompletion, referring to the cells below the cell that we want to keep default. That is, for the employee 2, the value of hours worked with cell A3 and so on for the other fills will be multiplied.

To correct this error, simply insert the cell lock option with the $ symbol. Thus, after calculating the overtime of the first employee, enter a dollar sign to lock the time reference and the cell to be copied according to the example below:

How to use fill handle in Excel

Therefore, when using the fill handle, the overtime values ​​of each employee will be calculated correctly.

How to use fill handle in Excel

As simple as the fill handle feature in Excel, there are numerous possibilities for day-to-day use. In this way, knowledge of this feature brings the facility to fill sequence data on many occasions.

This tip was helpful to you? Keep following our blog to learn even more about Excel!

Basic excel and intermediate online courses

Excel Spreadsheets

4 COMMENTS

  1. Hi Myllena, for the fill handle to work, it is necessary that the first month or full day of the week is spelled correctly, otherwise it does not work. The automatic formats are January, Jan, Monday, Mon

  2. Hello,
    The fill handle is repeating the values ​​when I select and drag. I can not set the days of the week / month in sequence, just numbers. What do I do?

  3. Hi David

    I believe that CTRL + C and CTRL + V are good for this, but it is not a handle functionality.

  4. Is there any command on the keyboard to raise multiple cells with formulas ??

LEAVE AN ANSWER

Please, write your comment
Please enter your name