The Fill Handle in Excel is possibly one of Excels most under utilized features. For those of you that don't know of it, it's the small black square in the bottom right of the active cell.
In it's simplest form it will increment any series of numbers. For example, if you type the number 1 in any cell and then the number 2 in a cell that adjoins it, you can use the Fill Handle to increment up to any number desired. To do this you simply select you two cells (Starting from the one with the number 1) and then hover your mouse pointer over the Fill Handle (until it changes to a small black cross), left click and drag in the direction you want the incremented numbers to show. You can also do the same by entering any Starting number in any cell, selecting the cell, holding down the Ctrl key and then dragging down with the Fill Handle. If you do not hold down the Ctrl key Excel will simply copy the same number.
The best bit about this feature is that we can alter the amount we increment by simply typing any two numbers we want eg 5 and 10 would result in 5, 10, 15, 20, 25 etc. If you wanted a blank cell between each number simply use the method below:
The exact same principle applies to dates, after all dates in Excel are only numbers (Serial Values).
The other thing that you can do with the Fill Handle is drag it up or to the left to clear the contents of selected cell(s). You can even insert or delete rows or columns by holding down the SHIFT key while dragging the fill handle.
Lets assume you have a column of data in cells A1:A500 and you place a formula into cell B1. Normally you would copy and paste the formula down to row 500, but instead of this try double clicking the Fill Handle. The whole thing is done for you and will stop at the first blank cell in column A! The same thing happens if you put two different numbers in cells B1 and B2, select both cells then double click the Fill Handle.
The other little known feature is Excels pop-up Fill menu you get when you right click on the Fill Handle and drag. Try this.
By using this method you eliminate the need for the secondary number. Now repeat steps 1 to 4 and for step 5 select Series instead of Fill Series. The option here are:
As you can see this option allows many choices and is very useful for incrementing dates! The best way by far to familiarize yourself with this feature is to jump straight in and have a go.
You may also have noticed when we right clicked the Fill Handle and dragged we had many other options available on the Pop-up menu. The Fill day, Fill months etc will only be available if the cell(s) contain a date. But you can also Copy cells, Fill Values and Fill formats. This can be particularly useful, but there is another Pop-up menu that is better for this.
As you will see you now have 10 options to choose from. Some of these are short-cut methods of the Paste Special feature. If you hold down the Alt key while dragging you can change sheets by hovering over the sheet name tab! I find the Copy here as values only particularly useful when converting formulas to permanent values. Try this example:
This method is far quicker and easier than using Edit>Paste Special-Values!
To find out what the other options do, just jump right in and try them.
|Dependent Validation Lists|
|Different Width Chart Bars. Stacked Fat/Thin Bar Chart|
|Excel Dynamic Formulas. Flexible & Changeable Formula Arguments|
|Excel Dynamic Lookup Formulas|
|Dynamic Named Ranges|
|Excel Errors & Alerts|
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.