Flash fill and the fill handle
The fill handle can be used in a variety of ways that may save you time. From repeating sequences or repeating data to repeating a formula over a large area.
The fill handle is the name given to the green dot found on the bottom right corner of the selected cell/s.
Use of the fill handle
The fill handle has a variety of simple uses, but we can also specify what type of pattern we'd like as well. As demonstrated here:
Examples in text:
- If we make a sequential sequence 1,2,3 in three separate cells, we can select these cells and drag the fill handle to carry on the sequence: 1,2,3,4,5,6,7,8,9,10 etc - Left click and drag the fill handle
- The fill handle as well as handling sequences of numbers also works for days of the week and months of the year Mon, tue, wed, will extend to the rest of the week: thurs, Fri, Sat etc. - Left click and drag the fill handle
- Growth sequences such as 1,2,4,8,16 will come out wrong if you left click and drag, right click instead and you'll created a growth trend 1,2,4,8,16,32,64,128 etc: Right click and drag, then select "Growth Trend"
- Repeat a sequence of anything - Right click and drag, then select "copy cells"
The fill handle and cell referencing
The fill handle also works with cell referencing as well. This means that you can quite often use the fill handle to duplicate/repeat the same formula/function across an area. For instance: If I wanted to Sum total the value of several columns of data. I could start by creating the function for column A: "=Sum(A2:A18)".
After this has been completed I could drag the total-value cell's fill handle to the right, to effectively copy the function to the cells In the direction I drag the fill handle. This will automatically move the cell references by whichever way I moved the fill handle. In this example (where I've moved the fill handle to the right) it will replicate the function with the appropriate column reference: =Sum(A2:A18) becomes =Sum(B2:B18) and subsequently =Sum(C2:C18).
You are also able to fix references in place so that moving the fill handle will not affect the references movement. This is covered on the Excel - Relative cell references and absolute cell references help page.