# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Can I stop excel incrementing values when using drag to fill cells?

## Grimace

Good afternoon,
I frequently use the drag function to fill data and formulas across columns on lengthy workbooks. I have noticed that dependant on what excel "thinks" is in the cell, it will sometimes decide to increment the values as it goes (eg in some rows where there is a random number, it will copy that same number, however if it is say a 1 in the first column it will then increment each column by 1 all the way across. 
This causes me to have to go back through the whole workbook and manually copy / paste the ones I dont want to increment.

Is there a setting in the options that can switch this function off?  I have looked for it but cant find it.

Prime examples are:
- items set as %'s - will increment by 100% each column, so in essence adding 1.
- if the first column is a 1, it will then autofill as 2,3 etc etc.

Hope you can assist, thanks in advance  :Smilie:

----------


## JBeaucaire

Manually fill in the first two cells with the non-incrementing values.  Then highlight both cells, then drag down.  Excel will then use the "pattern" it detects in  the two cells for incrementing, which in this case is none.

----------


## Grimace

Thanks for that ... I have used that occasionally when I think of it prior to doing the drag and drop.

I am assuming then that there is no setting available in the options to stop excel doing the auto-increment as a default ?

----------


## JBeaucaire

Correct, there is no setting I've ever seen, that's the generic behavior.

----------


## Grimace

Thanks very much for the advice ... enjoy your day / night wherever ye may be   :Smilie:

----------


## SmartyPIOW

G'day Grimace - when using small + sign to drag down to other cells, hold the CTRL key down and the same value will be copied to the other cells without incrementing. Have a good day  :Smilie:

----------


## TedPennell

Holding down the Alt keyallows you to drag the same number down the screen. Holding down Ctrl causes the + to have another little + appear by the side of it but it does not stop the incre"mental" increase.

----------


## ApacheCleric

> Holding down the Alt keyallows you to drag the same number down the screen. Holding down Ctrl causes the + to have another little + appear by the side of it but it does not stop the incre"mental" increase.



It is definitely holding the CTRL key while click dragging. It will give you a second even smaller + sign next to the normal one and keeps it from incrementing. Holding the ALT key while dragging does not stop it from incrementing. OFFICE 2013

----------


## Softey

> It is definitely holding the CTRL key while click dragging. It will give you a second even smaller + sign next to the normal one and keeps it from incrementing. Holding the ALT key while dragging does not stop it from incrementing. OFFICE 2013



It depends on the content of the dragged cell. If it is a *number*, use ALT key to stop incrementing; if it is a *date*, then use CTRL key. OFFICE 2010

----------


## Softey

I forgot to mention that cells containing *strings ending with a number*, e.g. "abc01", behave like dates when dragging their lower right corner and holding ALT or CTRL key: ALT increments the number when dragging down or right, CTRL inhibits the incrementing. Pressing no ALT nor CTRL, then Excel decides; sometimes it increments sometimes not. This different behaviour of Strings/Dates vs Numbers is surprising ! :Roll Eyes (Sarcastic):

----------


## Softey

Completing the answer to thread question (no new question), helping to stop incrementing:
When you drag down *more cells together* on a line, the following rules apply:
- if *only numbers*, there is never incrementing, whether pressing ALT/CTRL or not
- if there is a *mix of numbers, dates and/or strings*: CTRL key inhibits incrementing, ALT or no key will increment dates and strings ending with a number, whereas pure numbers are incremented only in some cases, not clear when.

----------


## xlnitwit

In my experience the Alt key has absolutely no effect when filling cells, unlike Ctrl which simply reverses whichever behaviour Excel is implementing with no keys held down. One may also hold the right mouse button while dragging to be offered the option of how to fill.

----------

