What is the rule for dragging numbers to next cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kevs - 23 Apr 2005 02:46 GMT

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

Reply to this Message

CyberTaz - 23 Apr 2005 03:28 GMT

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 |:>)

On 4/22/05 9:46 PM, in article BE8EF710.AF32%studiok4485@hotmail.com, "kevs"
wrote:

> 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
[quoted text clipped - 4 lines]
> OS 10.3.8
> Office 2004

-- generaltaz1@comSpaMcast.net

Reply to this Message

Jim Gordon MVP - 23 Apr 2005 18:33 GMT

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

Signature

Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

> Hi Kevs-
>
[quoted text clipped - 35 lines]
>
> -- generaltaz1@comSpaMcast.net

Reply to this Message

kevs - 24 Apr 2005 03:54 GMT

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.

On 4/23/05 10:33 AM, in article um3pCqCSFHA.164@TK2MSFTNGP12.phx.gbl, "Jim
Gordon MVP" <goldkey74@WarmerThanWarmMail.com> wrote:

> Hi Kevs,
>
[quoted text clipped - 18 lines]
>
> -Jim

OS 10.3.8
Office 2004

Reply to this Message

J Laroche - 24 Apr 2005 17:19 GMT

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:

> Thanks everyone:
> Jim: did not understand what you are trying to say at all values......
[quoted text clipped - 41 lines]
> OS 10.3.8
> Office 2004

Reply to this Message

Jim Gordon MVP - 25 Apr 2005 01:42 GMT

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

Signature

Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

> 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.
[quoted text clipped - 69 lines]
>>OS 10.3.8
>>Office 2004

Reply to this Message

kevs - 25 Apr 2005 06:08 GMT

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.

On 4/24/05 5:42 PM, in article uw66y#SSFHA.1528@TK2MSFTNGP09.phx.gbl, "Jim
Gordon MVP" <goldkey74@WarmerThanWarmMail.com> wrote:

> Ah yes!
>
[quoted text clipped - 5 lines]
>
> -Jim

OS 10.3.8
Office 2004

Reply to this Message

Jim Gordon MVP - 26 Apr 2005 01:39 GMT

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

Signature

Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

> Thanks again.
> All very interesting.
[quoted text clipped - 23 lines]
> OS 10.3.8
> Office 2004

Reply to this Message

Mac Forum / Applications / Excel / April 2005

What is the rule for dragging numbers to next cell?