Fill-Handle, Replication and use of the Mouse

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

[superscript] [duplicate] [team] [indirect] [special] [balance] [fill_empty] [mouse]
[autofit] [shiftmenu] [shiftmouse] [shiftscroll] [keys] [review] [grouping] [grouped] [related]

Fill-Handle and Replication   (#fillhandle)

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

click to see custom list for  Sun, Mon, Tue, Wed, Thu, Fri, Sat 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.

Press left button on fill-handle:  You get a toolbar tip to Drag outside selection to extend series, or fill; drag inside to clear. (#push)

fill handle is missing:  If the fill handle is missing go into tools, options, Edit, and check Allow cell drag and drop.
This option also affects ability to move page breaks with the mouse.

The fill-handle is actually optional, if it is missing use Tools|Options|Edit Tab|Check allow drag and drop   to restore it's use.  [In Excel 2007 click on Office button where File used to be, then click on Options at the bottom, then on Advanced at the left, then Enable fill handle and cell drag-and-drop]

Right-Click fill-handle and drag:  Topics include copy from a formula, of numbers dates or other items, linear best-fit trend; growth trend.

The macro equivalent ( SUB ) to double-click.
Dim rng as Range
set rng = Range(cells(2,1),cells(2,1).End(xldown))
rng.offset(0,1).Formula = Cells(2,2).Formula
Double-click fill-handle:  A technique that is not mentioned in the tooltip is to Double-click the fill-handle, so that Excel fills the formula or sequence down as far as the column to the left is filled with adjacent data/formula.  If there is nothing to the immediate left because you are in Column A or everything to the left is hidden the column to the right will be examined.  Specifically if there is nothing in the cell immediately to the left, and hidden columns don't count, and there is something in the cell immediately to the right, then the right column will determine the cells that get filled down.

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

You can make your own custom series using Tools, Options..., Custom Lists (tab)   i.e. Sun, Mon, Tue, Wed, Thu, Fri, Sat [– see picture]

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

An alternative to the fill handle is place the formula in a cell then select the area with the name box then hit F2 (or hit the formula bar) to get into Edit mode and then use Ctrl+Enter to copy to rest of selection. (shortcut keys)

The fill-handle is an option:« -- i.e. "The Fill Handle is missing"
    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

  1. dragging the fill handle of a single cell with a number will replicate down without incrementation.
  2. 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.
  3. Letters are the opposite. A single cell letter constant will increment through the alphabet with the fill handle.
  4. Ctrl+ drag fill handle will not increment a single cell letter constant as it is replicated down.
with multiple cell selections:
  1. you must include an increment cell in the direction you fill to get incrementation (both numbers and single letters)
  2. Holding the Control (Ctrl+) key down will only fill down repeating groups of cells.

Use of RtMouse button to indicate what you want

  1. 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

Equivalent Examples With the data in cell A3, to be copied down to cell A30:

  1. Select cell A3
  2. Point to the fill handle (the black square in the lower right corner of the selection)
  3. When the pointer changes to a black cross, drag down to A30
 
  1. Click in the Name Box, to the left of the Formula Bar
  2. Type -- A3:A30
  3. Press the Enter key
  4. Press Ctrl+D to fill down.
 
Use of the name box is handy when involving several screens, particularly if you have difficulty controlling the speed of the mouse when filling down.

You will not be able to make full use of the fill handle if you are not also familiar with the use of Absolute and Relative cell references, you will find articles in HELP as topics “About cell and range references”, which points to better topic “The difference between relative and absolute references”, and while the help pages are very good they can't be directly linked to regardless of your version, which makes Chip Pearson's article the more accessible reference “Relative And Absolute Addressing”  (Direct links to Excel Help on your machine is clueless as direct links would depend on the Excel version, so you will have to use the inadequate Help search facility).

The absolute reference refers to absolute with regards to fill handle, copy and paste, drag and drop, and other fill operations, it is not absolute with regard to inserting and deleting rows / columns.  For something to never change you would have to use something that places the address within double quotes.

Filldown as a Macro solution   (#filld)

The following macro will simulate fill down (Ctrl+D), as long as there is data to the left.  The shortcut would do use right if it can't use the left.
Sub filld()
  'Simulate Ctrl+D (fill down), D.McRitchie  2005-06-14 programming
  ' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld
  If IsEmpty(ActiveCell) Then Exit Sub
  Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown
End Sub
The following will fill down as far down as the column to the left has content, as opposed to contiguous content.  It will continue to fill down even if there are gaps in data to left but stops when there in no more data in the column to left.
Sub filld_to_last_at_left()
  'Fill down to lastrow based on cell to left, D.McRitchie  2005-06-14 programming
  ' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld 
  If IsEmpty(ActiveCell) Then Exit Sub
  Range(ActiveCell, Cells(Rows.Count, _
       ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FillDown
End Sub

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.  In the US our dates are ordered
as mm/dd/yyyy, which you will see as the days being incremented when a single cell was selected.

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
A298 A2981 A98B A95 A981
A299 A2982 A98B A96 A982
A300 A2983  A98B A97 A983
copied first row, with fill-handle
A298 A2981 A98B A95 A981
A298 A2981 A98B A95 A981
A298  A298 A98 A95  A98
  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 (#super)

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   m2  as in meters squared.

Duplicate and Unique entries (#duplicate)

 ABCDEFGH   (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.

Sorting on a Value That Appears in Alternate Rows (#sortbyalt)

 ABCDEFGHIJK
 1  Smith 5  4  5   3  17    Smith1  37   =A1&"1"  =G2
 2          6  5  5  4   20  37   Smith2  37   =A1&"2"  =G2
 3  Jones 4  3  3   3  13    Jones1  33   =A3&"1"  =G4
 4          5  5  5  5   20  33   Jones2  33   =A3&"2"  =G4
 5  white 5  4  4   4  17    white1  30   =A5&"1"  =G6
 6          4  3  3  3   13  30   white2  30   =A5&"2"  =G6
 7  Pete 5  5  5   5  20    Pete1  38   =A7&"1"  =G8
 8          6  5  4  3   18  38   Pete2  38   =A7&"2"  =G8
  Data is contained in two rows per name (in A), only the second row has the grand total (in G), data is to be sorted by Grand Totals but keep the two rows together.

Worksheet solution posted by "arno" 2004-07-06 [altG] in programming.

Sort on Column I descending, then on column H.

Excel sorts retain the order, so you actually only need the extra column that carries the Grand Total (G) for sorting. Carrying both extra columns provides the ability to sort on names instead (don't actually want the 1&2 suffix).

Get a Field from other sheets for a Team Summary Sheet (#team)

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)
25-12 3538  35 is from cell b2 on sheet 1970
313-17 4037  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.

 

 ABC
1Ages  19701971
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 
 Ages  1970 (revised)   1971  1972 
 '05-12  ='1970 (revised)'=!$B2   ='1971'!$B1  ='1972'!$B2 
 '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.

 

 ABC
1Ages19701971
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 (#indirect)

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 (#special)

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 (#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.

Replication using: Edit, GoTo (ctrl+G), Special, Blanks (#fill_empty)

The following do not involve the Fill Handle, but do fill downward

Replicate values into blank cells   (#fill_empty)

Some other methods that can replicate values into blank cells in a selection

This came to my attention from a posting by Eddie Griffiths though I'd seen it earlier in John Walkenbach's article in How to Duplicate Values in an Excel List , and you can find a more graphical presentation on Debra Dalgleish's page, Excel -- Data Entry -- Fill Blank Cells

Programming solution: Equivalent to the above, but bolding the original text descriptions
 
Sub Fill_Empty()
 '--David McRitchie,  2003-07-24,
 '--Macro version of -- Excel -- Data Entry -- Fill Blank Cells
 'http://www.contextures.com/xlDataEntry02.html
 'http://www.pcworld.com/shared/printable_articles/0,1440,9346,00.html
    Dim oRng As Range
    Set oRng = Selection
    Selection.Font.Bold = True
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Font.Bold = False
    Selection.FormulaR1C1 = "=R[-1]C"
    oRng.Copy
    oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub
   
 A
1Alex
2 
3 
4Barb
5 
6Cleo
7 
8 
   
 A
1Alex
2Alex
3Alex
4Barb
5Barb
6Cleo
7Cleo
8Cleo
Also see Fill in the Empty Cells (fillempt.htm)

    Filling in zeros to empty cells in a range

A non-programming method:
  1. place a zero in an empty cell and copy it (Ctrl+c)
  2. Select a range that includes the empty cells that are to receive zeros.
  3. Ctrl+G (edit, goto), Special, blank cells
  4. 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 Mouse (#mouse)

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.  (drag and drop is an option affects this and the fill-handle) -- Mouse Operations

You will NOT find the following information in HELP.  If you don't believe me try looking for any of the following using any of these key words:  mouse, mouse operations, drag, dragging, grab, grabbing, cell border -- you will get hits but none of them point to the following information.  If you don't know you have something then you don't really have it.

Mouse Settings (#settings)


in Excel Tools->Options->General->"zoom on roll with intellimouse
    keep this off or you won't be able to use mouse for
    a vertical scrolling,  you can still use Ctrl+wheel to zoom in/out.

My settings basically: Device: IntelliMouse Optical,
  Left Button: Click (default), Right Button: Right-click (default),
  Wheel Button: Enter, Left Side Button: Back (default),
  Right Side Button: Forward (default).
  Wheel tab: Scroll one "screen" at a time,
     [Advanced]: Only turn off IntelliPoint support for: AutoCAD, Fractal ...,
         Macromedia ..., MS PhotoDraw.

  on the wheel tab, advanced
    remove any disable exceptions for  IE  and for Visual Basic

    I guess they thought they could do it better themselves, but I'm
    pretty sure that was where the problem was with not being able to
    to use the wheel to scroll within the VBE.

if you still can't get scroll to work in VB then check out Shahin Gasanov's VBScroll at
    http://www.gasanov.net/VBScroll.htm  [ref]
    
   but make sure it is not in advance options on wheel button,
   or the buttons(tab), Settings (button)

All my mouse settings have been put in mouse.txt
   http://www.mvps.org/dmcritchie/excel/mouse.txt    and includes  
   Additional Details for [IrfanView] [Mozilla] [Excel] [Power Point] [Quicken 4th: PageUp, 5th: PageDn]

Mouse Pointers (#pointers)

Click to see default system mouse Pointers, and to see descriptions for pointer shapes

See the VBA help for
  Application.Cursor = xlWait 'hourglass -- see VBA help "MousePointer Property"
  Application.Cursor = xlDefault 'restore default square cross
  '--other options: xlDefault, xlBeam, xlNorthwestArrow, xlWait

Also see VBA mouse code (below)

Shift Key used with menu (#shiftmenu)

and other non-intuitive menu usage.
(Will probably be moved to toolbars.htm with references in shortx2k.htm and fillhand.htm)
copying a picture (also see xl2gif.htm)
Shift+Edit / Copy Picture
Shift+Edit / Paste Picture
Shift+Edit / Paste Picture Link
closing all files
Shift+File / Close All
always create a backup
File / SaveAs / Options / Always create a backup
which became, in 2000
File / SaveAs / Tools / General Options / Always create a backup
  (and similarly read-only recommended)
switch between read-only and read-write
get Toggle Read-Only toolbar button from File category or write own macro to do it
select whole of a table
Edit / Goto / Special / Current Region
or Ctrl+*
or customise toolbar with Select Current Region button from Edit category
(and similarly other Edit / Goto / Special options)

Shift Key used with mouse (#shiftmouse)

    (reserved, also see next topic)

Shift Key used with ScrollBars (#shiftscroll)

Hold the shift key down when scrolling to speed up scrolling.  This will make the scroller smaller.  The size of the scroller is normally despendent on the the number of used rows.  If you don't have much data and have a small scroller, it is an indication of of your lastcell (Ctrl+End) incorrectly set.  To fix last cell problems see particularly Reset_lastcell and MakeLastCell macros.  More information on Scrollbars.

shortcut keys, related to Filling cells (#keys)

Fill down, fills down constnaCTRL+D
Fill to the rightCTRL+R
wipes out fill to rightCtrl+Shift+R
Fill the selected cell range with the current entryCTRL+ENTER
More on Shortcut keys, Fill-Handle is not involved.
 

Space Bar or Enter (#spacebar)

The Spacebar is used for quick entry from within locations where you can't type.  In Internet Explorer and in Outlook Express, you can use it to Page Down.  In Excel you can use it to Select a dialog button instead of clicking on it (use TAB key for next choice).  You can Maximize a Window including IE, OE, and Excel, and Excel Help, with Alt+SpaceBar+X. or Restore a Window with Alt+SpaceBar+R.  If you lose the Titlebar under the frame you can Move the Window (pull the TitleBar Down) with Alt+Spacebar the Move.

Some Excel shortcuts using the spacebar (all can be done with the left hand):
 

  Perform the action assigned to the active button (the button with the dotted outline), or select or clear the active check box   SPACEBAR
  Note   To enlarge the Help window to fill the screen, press ALT+SPACEBAR and then press X.  To restore the window to its previous size and location, press ALT+SPACEBAR and then press R.   ALT+SPACEBAR+X
  ALT+SPACEBAR+R
  With an object selected, select all objects on a sheet   CTRL+SHIFT+SPACEBAR
  Select the entire column  CTRL+SPACEBAR
  Select the entire row  SHIFT+SPACEBAR
  Select or clear a check box in the list  SPACEBAR
  Show the program icon menu (on the program title bar)   ALT+SPACEBAR
A more complete list of Excel shortcuts is available.

Review of Fill Handle (#review)

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.

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

 ABCDE
1NameLocation UseridISP Formula used in Column D
Using GetFormula
2A2B2A2@aol.com aol.com=MID(C2,FIND("@",C2)+1,99)
3WagnerB3 A3@Laramie.eduLaramie.edu=MID(C3,FIND("@",C3)+1,99)
4A4B4A4@aol.com aol.com=MID(C4,FIND("@",C4)+1,99)
5A5B5A5@yahoo.com yahoo.com=MID(C5,FIND("@",C5)+1,99)
6RobertB6 Robert@deja.comdeja.com=MID(C6,FIND("@",C6)+1,99)
7LarryB7 larry@fakeisp.comfakeisp.com =MID(C7,FIND("@",C7)+1,99)

Grouping Worksheets to Make Manual Changes to Several Sheets at Once (#grouping)

By grouping your worksheets, you can make the same change manually to several sheets at once.  Select a sheet and with the Ctrl key, select additional sheets.  Another way of grouping sheets is Right-click then “Select All Sheets”.

When Grouping of sheets is in effect you will see [Group] on the title bar after the filename.  (don't confuse with multiple views :1, :2, :3).

Any change you make to the active worksheet will be made to all of the grouped worksheets whether they be constants or formulas.

WARNING: Don't forget to ungroup your sheets when finished or you may cause severe damage to your workbook.

To ungroup sheets: Select a sheet outside the group, or right-click sheet tab and choose “ungroup sheets”.

With Grouped sheets you can change at least some of page setup options.  Macros will not process grouped sheets unless specifically coded to do so.  Example of how a macro can be modified is the INSRTROW macro on my Insert rows maintaining formulas (insrtrow.htm) page.

As an alternative to grouping sheets you might want to linke the values ='Sheet4'!A4

Some specific reasons to Group sheets (#grouped -- topic actually starts above)

Repeating warning:  Ungroup your worksheets immediately when finished, failure to ungroup will destroy your workbook if grouping remains in place. 

Problems (#problems)

fill handle is missing:  If the fill handle is missing go into tools, options, Edit, and check Allow cell drag and drop.

formulas don't change (same values) when using fill handle and a quick check with F2 then Enter will update formula – Turn calculation back on Tools, calculate (tab), calculation: automatic

Cursor Problems (#cursorprob)

Keywords: problems grabbing text with cursor selection release

(2004-04-11) Trying to grab text such as several cells in Excel, text in text, or HTML results in unpredictable selections. Selection highlighting may disappear when releasing mouse or change the selection to more, fewer, or different characters.  Had a problem a few months ago with LockerGnome newsletters (not the web site) unitil they changed them back.

Current workarounds that may work for some usages.  Select text by multiple clicks to get perhaps the entire word, sentence, or paragraph.  If the purpose is to delete hit the Del key before releasing mouse button to lock the selection, then Delete.  Triple clicking in HTML gets a paragraph as does Ctrl+click (in HTML).  Place cursor down and use arrow key and delete keys to delete text since it's hard to select text.

This has been bugging me last night and most of today, defragged and rebooted last night not no change, but as soon as finished writing this up the problem disappeared.

Don't think I changed any Accessibility Options.  Had “Word Web 2.2” running, stopped the program and seems to be a slight improvement but not much, but as soon as I finished writing this up the problem disappeared.  Brought up Word Web again and had the problem again momentarily but okay again.  Found that there is a 3.1 free version http://wordweb.info/free/ so will try that later. Am still experiencing some problems even though Word Web was just uninstalled.  Brought up 3.1 more problems, took it down, still has problems.

Mouse Problems   (#mousedroppings)

Here's a thread to check on the subject:
  http://groups.google.com/groups?threadm=%235ZCGvAMBHA.1400%40tkmsftngp03

I had closed the mouse on the system tray and the wheel worked in Excel but not in the VBE. Can be fixed by going to control settings mouse, ok. Then mouse will be back in systray. I no longer show the mouse in the systray, but it has to be active and I can see mine in the running tasks.

To test now. terminated point32.exe from runnings tasks with task manager (Ctrl+Alt+Del). Mouse wheel works in Excel but not the code window of the VBE. Settings, Control Panel, Mouse, didn't do anything but press the [OK] button. Mouse wheel works again in the VBE, and point32.exe shows up in the Task Manager. Mouse does not show up on the System Tray and I don't want to see it there even though it has to be running.

I see point32.exe in system Information, Software Environment, Startup Programs but that is not where ones adds or removes such things.

One more point, my Wheel click is set Enter instead of AutoScroll. Dislike AutoScroll and think it a possible cause of ghosting problems, another possibility of ghosting problems is crippled use of CELL Worksheet Function described on pathname.htm page when used without the cell reference, and the other often mentioned not having latest mouse drivers..

TAB Key twists and turns (#tabkey)

Another item you will not find in Excel Help.

When you start Tabbing across a row and then hit ENTER (default down), then the cursor will return to the cell below the cell you started tabbing from. [Ragdyer in misc 2004-06-07]. ]

VBA coding (#vba)    *       [don't panic, It's just a picture of an hour glass]

See Mouse Pointers (above)
 
Application.Cursor = xlWait   'turn on hourglass pointer
<Your code here >
Application.Cursor = xlDefault 'return normal pointer 
For more information look up the following in VBA Help:  “Mousepointer property”, and “cursor property”.
How to install/use a macro can be found on my formula page.

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).  Stick to newsgroups and avoid submitting through forums.
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 send email comments


Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved