Fill-Handles and Replication

Location: http://www.mvps.org/dmcritchie/excel/fillhand.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Fill-Handle

The fill-handle is a very powerful tool in Excel. -- Ctrl + D (for Edit, Fill, Down)

The black square in the lower-right corner of the active cell or selection. When the pointer is on the fill handle, the pointer changes from an arrow to a crosshair. Drag the fill handle down or to the right to fill cells with data based on the current selection. Drag it up or to the left to clear the contents of selected cells. You can insert or delete rows and columns by pressing SHIFT while dragging the fill handle.

The above text was obtained directly from your HELP tooltips, using the Context Sensitive Help button [\?] and pointing to the Fill-Handle.

A technique that is not mentioned in the tooltip is to Double-click fill-handle, so Excel fills the formula or sequence down as far as the column to the left is filled with adjacent data/formula.

Also check HELP (F1) --> Index --> Fill in a series ...

To help preselect a range of cells such as before using the fill-handle:

Select a range in the Name box: A1:A5000
Select cell A1, then Edit goto A500
Likewise with A1 selected, type A500 in name box, then hold Shift key and hit Enter.

Topics include from a formula, of numbers dates or other items, linear best-fit trend; growth tread.

The fill-handle is an option:«
Tools --> Options --> Edit --> (select) Allow cell drag and drop check box is selected.

HELP (F1) --> Answer Wizard --> fill-handle

Some things to note when you want to increment constants.
The use of the control key will cause a different result, similar to other usages where the use of SHIFT+ causes the opposite result.
with single cell selections
dragging the fill handle of a single cell with a number will replicate down without incrementation.
Ctrl+ drag will increment a single cell selection with a numeric constant. It will not increment constants in multiple cell selections if there is no increment. i.e. A1:B1 with constants will not increment downward.
Letters are the opposite. A single cell letter constant will increment through the alphabet with the fill handle.
Ctrl+ drag fill handle will not increment a single cell letter constant as it is replicated down.
with multiple cell selections:
you must include an increment cell in the direction you fill to get incrementation (both numbers and single letters)
Holding the Control (Ctrl+) key down will only fill down repeating groups of cells.
Use of RtMouse button to indicate what you want
You can specify how you want the filling to be done if use the RtMouse button instead of the LtMouse button when you use the fill handle to drag your selection down. After dragging you will be asked what you want to do.
|| copy values, fill series, fill formats, fill values||
|| fill days, fill weekdays, fill months||
|| Linear trend, Growth Trend, series ... ||
with other choices where applicable.
You can make your own custom lists
Tools, Options, Custom Lists
Sun,Mon,Tue,Wed,Thu,Fri,Sat

Examples

Excel makes a pretty good guess as what you want when you use the fill-handle, the following table is an example of how FORMULAS are copied down from the first row with the fill-handle.

abc 1 =ROW(1:1) A1 =A1 =E1+D2 =OFFSET(E2,-1,0)+D2 row 1 col 1

abc 2 =ROW(2:2) A2 =A2 =E2+D3 =OFFSET(E3,-1,0)+D3 row 1 col 2

abc 3 =ROW(3:3) A3 =A3 =E3+D4 =OFFSET(E4,-1,0)+D4 row 1 col 3

abc 4 =ROW(4:4) A4 =A4 =E4+D5 =OFFSET(E5,-1,0)+D5 row 1 col 4

abc 5 =ROW(5:5) A5 =A5 =E5+D6 =OFFSET(E6,-1,0)+D6 row 1 col 5

abc 6 =ROW(6:6) A6 =A6 =E6+D7 =OFFSET(E7,-1,0)+D7 row 1 col 6

abc 7 =ROW(7:7) A7 =A7 =E7+D8 =OFFSET(E8,-1,0)+D8 row 1 col 7

abc 8 =ROW(8:8) A8 =A8 =E8+D9 =OFFSET(E9,-1,0)+D9 row 1 col 8

The items in bold were replicated (propagated) down using the fill-handle.

formula formula formula formula date date series

=A1+1 =A1+1 =1+A1 =ROW(1:1)+A1 01/01/2000 01/01/2000 2

=A2+2 =A2+1 =1+A2 =ROW(2:2)+A2 01/02/2000 02/01/2000 4

=A3+1 =A3+1 =1+A3 =ROW(3:3)+A3 01/03/2000 03/01/2000 6

=A4+2 =A4+1 =1+A4 =ROW(4:4)+A4 01/04/2000 04/01/2000 8

=A5+1 =A5+1 =1+A5 =ROW(5:5)+A5 01/05/2000 05/01/2000 10

=A6+2 =A6+1 =1+A6 =ROW(6:6)+A6 01/06/2000 06/01/2000 12

Use of fill-handle with superscripts involved

copied with fill-handle by column

A2⁹⁸ A2⁹⁸1 A⁹⁸B A⁹⁵ A⁹⁸1

A299 A2982 A⁹⁸B A⁹⁶ A⁹⁸²

A300 A2983 A⁹⁸B A⁹⁷ A⁹⁸³

copied first row, with fill-handle

A2⁹⁸ A2⁹⁸1 A⁹⁸B A⁹⁵ A⁹⁸1

A2⁹⁸ A2⁹⁸1 A⁹⁸B A⁹⁵ A⁹⁸1

A2⁹⁸ A2⁹⁸1 A⁹⁸B A⁹⁵ A⁹⁸1

The table at the left involves superscripts. It is interesting to note that there is a difference in using the fill-handle to copy one cell down a single column, and copying a row downward also with the fill-handle. In other word when a row is replicated it is treated same as if CTRL were held down at same time.
Incidental note, if you Copy and Paste cells with different font formatting in a cell be sure to copy from the cell and not from the formula line. Color, bold, italic, subscripts, and superscripts are font attributes and can be applied to individual characters in a cell. to copy from the cell not from the formula line.

Superscript 0, 1, 2, 3 as characters

The above examples use font changes to effect superscript, and the numbers really are numbers. The fonts used in Excel generally also contain a superscript 0, 1, 2, and 3 which can be keyed in as code. Once inserted you can grab it, copy it and insert it elsewhere like any other text, but 4 glyphs is rather limiting and is dependent on your font and codepage in use.

NumLock on: Alt+nnnn where nnnn is 0186 or 0185 or 0178 or 0179

These are separate characters so any use of the fill-handle will not change them. See symbols.

When using autoreplace and it changes something you don't want it to, you can immediately hit CTRL+z which is faster for most people than using the UNDO button or edit menu.

Tools -- Autoreplace change: m%%2 to m² as in meters squared.

Duplicate and Unique entries

	A	B	C	D	E	F	G	H (as used in Column D)
1	apples	apples		apples				=IF(COUNTIF(A:A,B1)=0,"",B1)
2	beef	salt		salt		beef	salt	=IF(COUNTIF(A:A,B2)=0,"",B2)
3	beets	tea		tea		beets	tea	=IF(COUNTIF(A:A,B3)=0,"",B3)
4	butter	salt		salt		butter	salt	=IF(COUNTIF(A:A,B4)=0,"",B4)
5	corn	corn		corn				=IF(COUNTIF(A:A,B5)=0,"",B5)
6	milk	mustard			mustard			=IF(COUNTIF(A:A,B6)=0,"",B6)
7	oats	milk		milk		oats		=IF(COUNTIF(A:A,B7)=0,"",B7)
8	milk	sesame			sesame			=IF(COUNTIF(A:A,B8)=0,"",B8)
9	pepper	fish			fish	pepper		=IF(COUNTIF(A:A,B9)=0,"",B9)
10	popcorn	salt		salt		popcorn	salt	=IF(COUNTIF(A:A,B10)=0,"",B10)
11	salt	rice			rice			=IF(COUNTIF(A:A,B11)=0,"",B11)
12	tea	tea		tea			tea	=IF(COUNTIF(A:A,B12)=0,"",B12)
13	vinegar	vinegar		vinegar				=IF(COUNTIF(A:A,B13)=0,"",B13)

cell =GETFORMULA(cell) Description

D1 =IF(COUNTIF(A:A,B1)=0,"",B1) Exists in both Col A and Col B

E1 =IF(COUNTIF(A:A,B1)=0,B1,"") Exists only in Column B

F1 =IF(COUNTIF(B:B,A1)=0,A1,"") Exists only in Column A

G1 =IF(COUNTIF(B:B,B1)>1,B1,"") Duplicates within Col B

See Chip Pearson's Duplicate And Unique Items In Lists for information on identifying duplicate and unique entries in a single column.

Get a Field from other sheets for a Team Summary Sheet

Based on a posting by George Simms on 2000-01-30 [Example] introducing a slick technique that simplifies replication using ROW(1:1). Having a distinct advantage over ROW(A1), for instance.

   =LARGE($A$1:$A$10,ROW(1:1))

The following will work   note result:  ='Team 1'!$B$2
   =INDIRECT("'Team " & ROW(1:1) & "'!$B$2")
   =INDIRECT("'Team " & ROW(2:2) & "'!$B$2")
   =INDIRECT("'Team " & ROW(3:3) & "'!$B$2")

will generate formulas that act as if coded as follows:
   ='Team 1'!$B$2
   ='Team 2'!$B$2
   ='Team 3'!$B$2

A variation would be where the sheetname is in Column A
   Team-AA      =indirect("'" & A2 & "'!$B$2") i.e. 'Team-AA'!$B$2
   Tiger-Team   =indirect("'" & A3 & "'!$B$2") i.e. 'Tiger-Team'!$B$2
   Wood Knots   =indirect("'" & A4 & "'!$B$2") i.e. 'Wood Knots'!$B$2

Similar structure

All detail sheets have same format and we only want information from Column B of each detail sheet.

   A B C D

1 Ages   1970 1971 each year shown is the name of a detail sheet)

2 5-12 35 38 35 is from cell b2 on sheet 1970

3 13-17 40 37 40 is from cell b3 on sheet 1970

Formulas for this example, and note that a single quote is placed in front of ages to prevent from being interpreted as a date.

A B C

1 Ages   1970 1971

2 '05-12 =INDIRECT("'" & B$1 & "'!B" & ROW()) =INDIRECT("'" & C$1 & "'!B" & ROW())

3 '13-17 =INDIRECT("'" & B$1 & "'!B" & ROW()) =INDIRECT("'" & C$1 & "'!B" & ROW())

This is what the effective formulas would look like

    A B C D

1 Ages 1970 (revised) 1971 1972

2 '05-12 ='1970 (revised)'=!$B2 ='1971'!$B1 ='1972'!$B2

3 '13-17 ='1970 (revised)'=!$B3 ='1971'!$B2 ='1972'!$B3

If the detail sheets 1970 and 1971 do not have descriptions at the top then adjust the row.

A B C

1 Ages 1970 1971

2 '05-12 =INDIRECT("'" & B$1 & "!'B" & ROW()-1) =INDIRECT("'" & C$1 & "!'B" & ROW()-1)

3 '13-17 =INDIRECT("'" & B$1 & "!'B" & ROW()-1) =INDIRECT("'" & C$1 & "!'B" & ROW()-1)

Show values on another sheet, without adjustments for changes

A question that pops up every so often is to be able to show the content of Cell A1 on Sheet1 and insert a column in front of Column A on Sheet1 and have Sheet2 refer to the new cell A1 instead of the displaced cell A1 relocated to cell A2. i.e. 'Sheet 1'!$A$1, 'Sheet 1'!$A$2

=INDIRECT("'Sheet 1'!$A$"&ROW(1:1)) =INDIRECT("'Sheet 1'!$B$" &ROW(1:1)) =INDIRECT("'Sheet 1'!$C$" &ROW(1:1))

=INDIRECT("'Sheet 1'!$A$"&ROW(2:2)) =INDIRECT("'Sheet 1'!$B$" &ROW(2:2)) =INDIRECT("'Sheet 1'!$C$" &ROW(2:2))

=INDIRECT("'Sheet 1'!$A$"&ROW(3:3)) =INDIRECT("'Sheet 1'!$B$" &ROW(3:3)) =INDIRECT("'Sheet 1'!$C$" &ROW(3:3))

Another Example showing the sheet being changed but with the source cell being the same on each sheet was included above in a Team Summary page.

Similar code for showing top LARGE/SMALL were shown on my Formula page.

Check if cells have formulas or numbers

One way to tell if the cells are numeric constants without using functions is Select ALL --> Edit --> GoTo Special --> constants & numbers Using functions if you have a mixture of constants and formulas, run this down another column with fill-handle . For more information see my Formula page.
=IF(ISNUMBER(A1),"N",IF(ISTEXT(A1),"Text","O"))
Other things found on my Formula page include formulas to show the formula used in another cell, the format used in another cell, the hyperlink used in another cell (URL, hyperlink).
Running Total and Checkbook Balance
Using a formula like =F2+E3 for a running total will work, but it is troublesome if you want to insert or delete lines, in which case the formula =OFFSET(F3,-1,0)+E3 is a lot less problematic. Read more about Inserting Rows.

GoTo Special

Some other methods that can replicate values or formulas into an area.
This came to my attention from a posting by Eddie Griffiths.

Select column B -- you can use Del if you've messed with it before
CTRL+G (Edit --> GoTo), Special
select Blanks
Enter formula =URL(A1) into cell B1 and then
CTRL+Enter (Fill the selected cell range with the current entry) and now to remove the errors with Column B still selected, or reselect Column B
CTRL+G --> Special --> Formula, Errors
Del key

Filling in zeros to empty cells in a range

A non-programming method:

place a zero in an empty cell and copy it (Ctrl+c)
Select a range that includes the empty cells that are to receive zeros.
Ctrl+G (edit, goto), Special, blank cells
Edit, paste special, values
If you record a macro you would get something along the lines of:
Sub Macro1()
    Range("H3").Select
    Selection.Copy
    Range("F1:F22").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

Programming solution

The correct way is a lot smaller (Tim Zych, programming, 2001-09-21)
  On Error Resume Next
  ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = 0
You can use Selection. in the above or a Range. instead of Activesheet.Used.Range.

Use of Mouse and keys to move, and insert rows or columns

Techniques for moving rows or columns; and inserting blank rows or columns all using the mouse cursor in combination with the CTRL, SHIFT, or ALT keys.

grabbing the demarcation line between column or row headings, you will see large plus with directional arrows. This is the part most people are already aware of, from a demonstration.
- Widen or narrow a row or column, grab the border between adjacent row or column headers and move to either side to narrow or increase width or height.
- Widen or narrow a row or column to fit data, grab the border demarcation between adjacent rows or column headers and double-click to adjust to fit data. fit.
- Widen/narrow or adjust for a group of rows or columns same as above except will apply to all rows or columns in the group.
- Grouping can be done in combination with the above.
grabbing the fill-handle, you will see a small bold plus sign.
- Filling a rows or column, Select a column, drag the fill handle. Has been described earlier.
- Insert a Column, Select a column, hold SHIFT, grab fill-handle you will see two flat lines for insert, drag it right to insert columns.
- Insert a Row, Select a row, hold SHIFT, grab fill-handle you will see two flat lines for insert, drag it down to insert rows.
- Using fill-handle with Ctrl will copy & paste.
- Using fill-handle with Alt will copy & paste. but is non-destructive regards to current data.
- Grouping can be done in combination with the above.
grabbing the side just off of the fill-handle, you will see a line when you move.
- Move a Column, Select a column, grab the side of the column below the column headers (A,B,C) and drag it left or right to move, you will see a single line following the cursor, drop it ontop of an existing column or use with SHIFT to insert it between columns.
- When grabbling the left side of a column it must be below the row headers of the selected column and the pointer arrow should just barely touch the left border.
- Move a Row, Select a row, grab the side of the row to the right of the row headers (1,2,3) and drag it up or down to move, you will see a single line following the cursor, drop it ontop of an existing row or use with SHIFT to insert it between rows.
- Grouping can be done in combination with the above.
- If you move the line with the right-cursor you will see options, for copy, move, copy values, copy format, create hyperlinks.

Shortcut keys, related to Filling cells

Fill down, fills down constna CTRL+D

Fill to the right CTRL+R

wipes out fill to right Ctrl+Shift+R

Fill the selected cell range with the current entry CTRL+ENTER

More on Shortcut keys, Fill-Handle is not involved.

Place a constant or a formula in cell; =ROW()
Place a range in the name box; A1:A200
Fill down from the active cell, and only from the active cell; Ctrl+R

Review of Fill Handle

Using the fill handle. Select a cell, you will see a little black square at the lower left corner. Grab the fill handle using the left mouse button drag down as far as you want, or if there are things to the left -- double-click on the fill handle and it will be replicated down automatically.
Auto Filter.
Select all cells (Ctrl+a)
Data --> Filter --> AutoFilter
select arrow in cell D1
make a choice from the alphabetic list i.e. aol.com
Listed will be aol.com
To see everything again choose "All" on the filter arrow.
To turn off autofilter: Data --> Filter --> click to uncheck AutoFilter

A B C D E

1 Name Location Userid ISP Formula used in Column D
Using GetFormula

2 A2 B2 A2@aol.com aol.com =MID(C2,FIND("@",C2)+1,99)

3 Wagner B3 A3@Laramie.edu Laramie.edu =MID(C3,FIND("@",C3)+1,99)

4 A4 B4 A4@aol.com aol.com =MID(C4,FIND("@",C4)+1,99)

5 A5 B5 A5@yahoo.com yahoo.com =MID(C5,FIND("@",C5)+1,99)

6 Robert B6 Robert@deja.com deja.com =MID(C6,FIND("@",C6)+1,99)

7 Larry B7 larry@fakeisp.com fakeisp.com =MID(C7,FIND("@",C7)+1,99)

Related Items

How to install/use a macro can be found on my formula page.

Formulas page, has some examples of formulas.
Rearranging Data in Columns, has some examples of creating test data with address of cell when macro was run.
Insert ROW using a Macro, the macro described will insert row(s) below a selected row or cell. � The advantage of using a macro is that the formulas will be copied, but not the data; providing a more reliable method of inserting lines than simply inserting a row and then dragging a row with formulas and data into an inserted empty row.
Fill in the Empty Cells, this macro will fill in empty cells with the content of the cell above it, providing the cell above is also within the selection range.

This page was introduced on February 13, 2000.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com.

abc	1	=ROW(1:1)	A1	=A1	=E1+D2	=OFFSET(E2,-1,0)+D2	row 1 col 1
abc	2	=ROW(2:2)	A2	=A2	=E2+D3	=OFFSET(E3,-1,0)+D3	row 1 col 2
abc	3	=ROW(3:3)	A3	=A3	=E3+D4	=OFFSET(E4,-1,0)+D4	row 1 col 3
abc	4	=ROW(4:4)	A4	=A4	=E4+D5	=OFFSET(E5,-1,0)+D5	row 1 col 4
abc	5	=ROW(5:5)	A5	=A5	=E5+D6	=OFFSET(E6,-1,0)+D6	row 1 col 5
abc	6	=ROW(6:6)	A6	=A6	=E6+D7	=OFFSET(E7,-1,0)+D7	row 1 col 6
abc	7	=ROW(7:7)	A7	=A7	=E7+D8	=OFFSET(E8,-1,0)+D8	row 1 col 7
abc	8	=ROW(8:8)	A8	=A8	=E8+D9	=OFFSET(E9,-1,0)+D9	row 1 col 8

formula	formula	formula	formula	date	date	series
=A1+1	=A1+1	=1+A1	=ROW(1:1)+A1	01/01/2000	01/01/2000	2
=A2+2	=A2+1	=1+A2	=ROW(2:2)+A2	01/02/2000	02/01/2000	4
=A3+1	=A3+1	=1+A3	=ROW(3:3)+A3	01/03/2000	03/01/2000	6
=A4+2	=A4+1	=1+A4	=ROW(4:4)+A4	01/04/2000	04/01/2000	8
=A5+1	=A5+1	=1+A5	=ROW(5:5)+A5	01/05/2000	05/01/2000	10
=A6+2	=A6+1	=1+A6	=ROW(6:6)+A6	01/06/2000	06/01/2000	12

cell	=GETFORMULA(cell)	Description
D1	=IF(COUNTIF(A:A,B1)=0,"",B1)	Exists in both Col A and Col B
E1	=IF(COUNTIF(A:A,B1)=0,B1,"")	Exists only in Column B
F1	=IF(COUNTIF(B:B,A1)=0,A1,"")	Exists only in Column A
G1	=IF(COUNTIF(B:B,B1)>1,B1,"")	Duplicates within Col B

	A	B	C	D
1	Ages	1970	1971	each year shown is the name of a detail sheet)
2	5-12	35	38	35 is from cell b2 on sheet 1970
3	13-17	40	37	40 is from cell b3 on sheet 1970

	A	B	C	D
1	Ages	1970 (revised)	1971	1972
2	'05-12	='1970 (revised)'=!$B2	='1971'!$B1	='1972'!$B2
3	'13-17	='1970 (revised)'=!$B3	='1971'!$B2	='1972'!$B3

=INDIRECT("'Sheet 1'!$A$"&ROW(1:1))	=INDIRECT("'Sheet 1'!$B$" &ROW(1:1))	=INDIRECT("'Sheet 1'!$C$" &ROW(1:1))
=INDIRECT("'Sheet 1'!$A$"&ROW(2:2))	=INDIRECT("'Sheet 1'!$B$" &ROW(2:2))	=INDIRECT("'Sheet 1'!$C$" &ROW(2:2))
=INDIRECT("'Sheet 1'!$A$"&ROW(3:3))	=INDIRECT("'Sheet 1'!$B$" &ROW(3:3))	=INDIRECT("'Sheet 1'!$C$" &ROW(3:3))

Fill down, fills down constna	CTRL+D
Fill to the right	CTRL+R
wipes out fill to right	Ctrl+Shift+R
Fill the selected cell range with the current entry	CTRL+ENTER

	A	B	C	D	E
1	Name	Location	Userid	ISP	Formula used in Column D Using GetFormula
2	A2	B2	A2@aol.com	aol.com	=MID(C2,FIND("@",C2)+1,99)
3	Wagner	B3	A3@Laramie.edu	Laramie.edu	=MID(C3,FIND("@",C3)+1,99)
4	A4	B4	A4@aol.com	aol.com	=MID(C4,FIND("@",C4)+1,99)
5	A5	B5	A5@yahoo.com	yahoo.com	=MID(C5,FIND("@",C5)+1,99)
6	Robert	B6	Robert@deja.com	deja.com	=MID(C6,FIND("@",C6)+1,99)
7	Larry	B7	larry@fakeisp.com	fakeisp.com	=MID(C7,FIND("@",C7)+1,99)