The fill handle is a convenient Excel tool that is used to fill data with a specific pattern. You can extend a series of numbers, dates and text combinations to desired number of cells.
To use fill handle bring the mouse cursor at right bottom corner of the cell it will change into a + sign. Now left click on it and drag it downward to the desired range of cells.
If you select one cell the value in the cell will be copied to other cells and if you choose multiple cells with specific pattern like difference between cells then the same pattern will be copied to other cells. See the image shown below.
Excel Fill Handle with Text
Fill handle works in a similar way with texts. It helps repeat the text to desired range of cells. See the images shown below:
The text is selected and then copied to other cells using fill handle.
Excel Fill Handle with Text and Numbers
Fill handle also works with cells containing both text and number. In this case, the text does not change but the number increases by one for every cell you select. See the images:
Excel Fill Handle with Dates
Fill handle can be used to fill dates in multiple cells. If you need to enter a complete month in worksheet, you don't need to type each date separately. Just choose a cell, type the date and using fill handle carry it over other cells, Excel will enter the subsequent dates in the selected cells. See the images:
Excel Creating Formulas
In cell C5 enter the number 20, now in cell E5 type the formula: =C5+5 then press the Enter key. You will get the result. The formula says 5 is added to the value entered in the cell C5 as the value is 20 the result is 25. See the image:
In a similar way other formulas can be created:
=C2*5 for multiplication, the value in the cell C2 is multiplied with 5.
=C2-5 for subtraction, 5 is subtracted from the value in the cell C2.
More formulas can be created by typing = in the cell then selecting the appropriate values and typing correct mathematical symbol in between.
See the formula created to calculate the distance in the image given below:
See the formula created to calculate the phone bill in the image given below:
Excel Fill Handle in Formulas
In the image given below, we want to multiply the numbers in column B with 5, so we can type the formula such as =B3*5 for cell B3 and other cells of the column B, but this is not possible with long lists.
In such cases, we use Fill handle to perform the calculations. So, type the formula =B3*5 in cell C3 then left click on the right bottom corner of cell C3, fill handle will appear. Pull the fill handle downward till cell C10 then leave the mouse. The whole list of numbers in column B will be multiplied by 5.
The pull handle also works horizontally. See the image given below. The value in cell B10 is multiplied with 5 in cell B12 and as you move the pull handle horizontally towards cell C12, the value in cell C10 also gets multiplied by 5.
Excel Relative Referencing
Relative referencing tells that you don't need to type the formula separately for each calculation in a column or a row. See the image:
Type the formula in the first cell D3 and press Enter key to get the result, then use the fill handle. The formula will change automatically in the respective cells and all values of list 1 will get multiplied with respective values of list 2.
This way you can easily multiply values of list 1 with respective values of list 2 but if you want to multiply all cells of list 1 with a single cell of list 2 such as C3 then it won't work.
Excel Absolute Referencing
Absolute referencing helps multiply multiple values with a particular value.
See the image shown below. The list 1 values are multiplied by 4, value of cell C3. In this case, we will use symbol $ in the formula.
So we have to modify the formula for that after entering the formula press F4 key. $ Sign will appear in the formula. See cell D3 in the image. Then press Enter key to get the result. Now drag the fill handle from cell D3 till cell D10. All values of list 1 will get multiplied with 4.
In a similar way, in the image shown below, the bonus marks are added to the marks of the students by modifying the formula from =C5+E2 to =C5+$E$2.
Instruction while typing data in excel
Type the values and text in separate cells else the formula will not work. See the example given below:
In case you want to add currency symbol to values then use mini toolbar as shown in the image given below.
Select the range of cells then right click. Mini toolbar appears above the shortcut menu. Click on the currency symbol. It will be added to the given values. To find more currency symbols click on the arrow next to the currency symbol. See the images given below.
Quick Excel Functions
There are some quick functions in Excel that can provide you the statistics of your worksheet without using formulas such as Status Bar quick functions.
See the image given below, as soon as you select the desired range you will get the statistics in the status bar, the average of numbers, the number of cells that contain values and the sum of numbers.
By a right click on the status bar you can customize it. You can add more functions in the status bar. Select the function from the menu which you want to add in the status bar. See the image shown below, status bar is customized. It is also showing maximum and minimum of the numbers.
