What is the rule for dragging numbers to next cell?

[kevs]

I am so fed up with this. You have a number , and you click and you drag to
the cell underneath. Sometimes the number stays the same. Sometimes it
increases by a value of one. Sometimes in you click shift or option in
stays the same or increases by value of one. I've seen so much
inconsistency with this I can't keep track.


OS 10.3.8
Office 2004

 

[CyberTaz]

Hi Kevs-

These 'features' can be a little frustrating 'til you get the hang of it,
but I assume the AutoFill handle (the bottom right corner of the active
cell) is what is causing you problems. Basically it does one of two things;

1- If the cell you're starting with has content _not_ recognized as a
series, the fill handle will copy the content to the cells you drag to,

2- If the starting cell's content _is_ recognized a a series, use of the
handle will extend the series. Examples:
Starting cell contains Monday, it produces Tuesday, Wednesday, etc.
Starting cell contains 1/1/2005, it produces 1/2/2005, 1/3/2005 etc.
Same idea for Mon, Tue... As well as month names & abbreviations.

Where it gets more complex is if you have more than one cell selected & use
the handle: first cell = 1, second cell = 2, select both & handle gives you
3, 4, 5, 6, etc.

I truly suggest that you play with it a little to get a better 'feel' for
how it works, but if it really gives you a fit, go to EXCEL>Preferences>Edit
and remove the check for Allow Cell Drag and Drop. (Unfortunately you loose
both D&D as well as AutoFill).

HTH |:>)

I am so fed up with this. You have a number , and you click and you drag to
the cell underneath. Sometimes the number stays the same. Sometimes it
increases by a value of one. Sometimes in you click shift or option in
stays the same or increases by value of one. I've seen so much
inconsistency with this I can't keep track.


OS 10.3.8
Office 2004

-- (e-mail address removed)

 

[Jim Gordon MVP]

Hi Kevs,

In addition to Cybertaz's suggestions, you may be experiencing the
behavior known as "Relative Cell References." From Excel's help:

"relative cell reference
A cell reference, such as =A1, that tells Microsoft Excel how to find
another cell by starting from the cell that contains the formula. Using
a relative reference is like giving someone directions that explain
where to go from where the person is starting out — for example, "go up
two blocks and over one block.""

The other type of cell reference may be of use to you. From Excel's help:
"absolute cell reference
In a formula, the exact address of a cell, regardless of the position of
the cell that contains the formula. An absolute reference takes the form
$A$1, $B$1, and so on."

Think of the dollar sign as meaning "always." When you use the dollar
sign that tells Excel to not use relative references.

-Jim

 

[kevs]

Thanks everyone:
Jim: did not understand what you are trying to say at all values......

But anyway:
I opened blank document.
I type 1 in a cell.
And the use auto fill, and drag down, and cells below all says 1. Ok

But if I put 1-4455 in a cell and drag down, then ones below increase.

So the rule is that if there is a dash, then in does the increasing? If no
dash, then numbers below are same?

What other rules are there then.............


Thanks.

Hi Kevs,

In addition to Cybertaz's suggestions, you may be experiencing the
behavior known as "Relative Cell References." From Excel's help:

"relative cell reference
A cell reference, such as =A1, that tells Microsoft Excel how to find
another cell by starting from the cell that contains the formula. Using
a relative reference is like giving someone directions that explain
where to go from where the person is starting out ‹ for example, "go up
two blocks and over one block.""

The other type of cell reference may be of use to you. From Excel's help:
"absolute cell reference
In a formula, the exact address of a cell, regardless of the position of
the cell that contains the formula. An absolute reference takes the form
$A$1, $B$1, and so on."

Think of the dollar sign as meaning "always." When you use the dollar
sign that tells Excel to not use relative references.

-Jim

OS 10.3.8
Office 2004

 

[J Laroche]

Yes, the hyphen is one rule. But in general Excel will increase the value if
it can determine what's the increment from one cell to the next.

If you start from a single selected cell, most likely all others will
contains the same value. But if you select two cells with different values,
then the next ones will increase with the same increment as between the
first two cells.

Examples:
With 1 and 2, the rest will be 3, 4, 5 etc.
With 2 and 5, the rest will be 8, 11, 14, etc.
With 1, 2.5 and 3.5, the rest will be 4.833, 6.083, 7.333, 8.583, etc. The
first automatic increase is 1.33, then it settles to 1.25. There's probably
a weighted ratio at play here.
With Monday and Wednesday, the rest will be Friday, Sunday, Tuesday, etc.
With 2005/03/01 and 2005/04/01, the rest will be 2005/05/01, 2005/06/01,
2005/07/01, etc. (Notice that even though there are different number of days
in each month, Excel is clever enough to see that the step is one month, not
31 days).
It doesn't increase alphabetically, i.e. a and b don't increment to c, d ,
e.

HTH
JL
Mac OS X 10.3.9, Office v.X 10.1.6



kevs wrote on 2005/04/23 22:54:

 

[Jim Gordon MVP]

Ah yes!

Now that I understand what it is that Kevs is looking for I would like
to add that in addition to Excel trying to figure out patterns, you can
specify your own lists for Excel to use as patterns, too.

To do that, choose Excel > Preferences > Custom Lists.

-Jim

 

[kevs]

Thanks again.
All very interesting.
You see how confusing this can be.
One moment, I put a number in a cell and drag down, and they stay the same
number. Fine.

Then on another day, the numbers increase. Cause was a dash...or something
else??

Of course I can get what I want with Option, but I just can't know anymore
when to use Option or not. It's frustrating.

Ah yes!

Now that I understand what it is that Kevs is looking for I would like
to add that in addition to Excel trying to figure out patterns, you can
specify your own lists for Excel to use as patterns, too.

To do that, choose Excel > Preferences > Custom Lists.

-Jim

OS 10.3.8
Office 2004

 

[Jim Gordon MVP]

Hi Kevs,

The Excel help topic "About filling in data based on adjacent cells" has
more examples of how this feature works. I think Microsoft used to call
this behavior "intellisense" or something like that.

-Jim