<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>
Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Fill Handle Explained

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Got any Excel Questions? Excel Help

FillHandle | also see Creating a Custom List

The Fill Handle in Excel ispossibly one of Excels most under utilized features. For those of you that don'tknow 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, ifyou type the number 1 in any cell and then the number 2 in a cell that adjoinsit, you can use the Fill Handle to increment up to any number desired. To dothis 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 asmall black cross), left click and drag in the direction you want theincremented 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 canalter the amount we increment by simply typing any two numbers we want eg 5 and10 would result in 5, 10, 15, 20, 25 etc. If you wanted a blank cell betweeneach number simply use the method below:

  1. Type 5 in cell A1
  2. Leave A2 blank
  3. Type 10 in cell A3
  4. Leave A4 blank
  5. Select cells A1:A4
  6. Drag down using the Fill Handle

The exact same principle applies todates, after all dates in Excel are only numbers (Serial Values).

The other thing that you can dowith the Fill Handle is drag it up or to the left to clear the contents of selectedcell(s). You can even insert or delete rows or columns by holding down the SHIFTkey while dragging the fill handle.

Lets assume you have a column ofdata in cells A1:A500 and you place a formula into cell B1. Normally you wouldcopy and paste the formula down to row 500, but instead of this try doubleclicking the Fill Handle. The whole thing is done for you and will stop atthe first blank cell in column A! The same thing happens if you put twodifferent numbers in cells B1 and B2, select both cells then double click theFill Handle.

The other little known feature is Excels pop-up Fill menu you get whenyou right click on the Fill Handle and drag. Try this.

  1. Type any number in any cell
  2. Select the cell
  3. Right click on the Fill Handle
  4. Drag down and then release
  5. Select Fill Series

By using this method you eliminatethe need for the secondary number. Now repeat steps 1 to 4 and for step 5 select Seriesinstead of Fill Series. The option here are:

  1. Series in: Determines whether the series is filled across selected rows or down selected columns. The contents of the first cell or cells in each row or column of the selection are used as the Starting values for the series.
  2. Type (Linear or Growth): Creates a growth series or geometric growth trend.
    If the Trend box is cleared, a series is calculated by multiplying the value in the Step value box by each cell value in turn. If the Trend box is selected, the value in the Step value box is ignored, and a geometric growth trend is calculated based on the selected values. The selected original values are replaced with values that fit the trend.
  3. Type (Date): Fills a series with dates. The type of date series that is incremented depends on the option selected under Date unit. Date unit is only available when working with dates.
  4. Type (AutoFill): Fills blank cells in a selection with a series based on data included in the selection. Selecting this option produces the same results as dragging the fill handle to fill a series. Any value in the Step value box and any selected Date unit option are ignored.
  5. Date unit: Specifies whether a series of dates will increase by days, weekdays, months, or years. Available only when creating a date series.
  6. Trend: Calculates a best-fit line (for linear series) or geometric curve (for growth series). The step values for the trend are calculated from the existing values at the top or left of the selection. Any value in the Step value box is ignored if the Trend check box is selected.
  7. Set value: Enter a positive or negative number to indicate the amount by which you want a series to increase or decrease.
  8. Stop value: Enter a positive or negative number to indicate the value at which you want the series to end. If the selection is filled before the series reaches the stop value, the series stops at that point. If the selection is larger than needed to fill the series, the remaining cells of the selection are left blank. You do not need a value in the Stop value box to fill a series.

As you can see this option allowsmany choices and is very useful for incrementing dates! The best way byfar to familiarize yourself with this feature is to jump straight in and have ago.

You may also have noticed when we right clicked the Fill Handle and dragged wehad 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. Butyou can also Copy cells, Fill Values and Fill formats. Thiscan be particularly useful, but there is another Pop-up menu that is better forthis.

  1. Type any formula into any cell
  2. Right click on the cell border (not the Fill Handle)
  3. Drag down then release.

As you will see you now have 10options to choose from. Some of these are short-cut methods of the Paste Specialfeature. If you hold down the Alt key while dragging you can changesheets by hovering over the sheet name tab! I find the Copy here as values only particularly useful whenconverting formulas to permanent values. Try this example:

  1. Type any number of formulas into any adjoining cells
  2. Select all these cells and right click on the border.
  3. Now drag down just one row
  4. With the right mouse button still held down drag back up one row to where you Start ed
  5. Now release and choose Copy here as values only

This method is far quicker andeasier than using Edit>Paste Special-Values!

To find out what the other optionsdo, just jump right in and try them.

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.


Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates