How to remove Autofill Formula in Excel

[shacol03]

Hi,

May you please help me to remove this autofill formula in excel. I cannot delete these formula from cell J9 to J5 due to autofill.

I already Uncheck this in following below but still not working.

Option > Advance Settings > Editing Option > Automatically Flash Fill
Option > Proofing > AutoCorrect Option > AutoFormat As you Type > I unchecked all listed

I really appreciate all the response. Please help me.

 

[shacol03]

correction: I cannot delete these formula from cell J9 to J25 due to autofill.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I cannot delete these formula from cell J9 to J25 due to autofill.

It is not AutoFill, is is a dynamic array formula result that automatically 'spills' into the relevant cells.

Please tell us exactly what you are trying to do in columns I & J

 

[shacol03]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


It is not AutoFill, is is a dynamic array formula result that automatically 'spills' into the relevant cells.

Please tell us exactly what you are trying to do in columns I & J

Hi Sir,

I appreciate your response. I just wanted to use count formula for each Name in Column I.

Like. to get how many Name A listed in Column B and the result will be show in Column J.

please see this screenshot below for my expecting result. For example In this screenshot, there are 5 Name A in Column B..... 9 Name C in Column B .. etc....

I'm just annoying with the auto formula and I want it to disable.

 

[Peter_SSs]

Thanks for updating your version info.

I just wanted to use count formula for each Name in Column I.

Then try this formula in cell J3 - it will spill just far enough & no further.

24 01 04.xlsm
	B							I	J
1
2	Name A
3	Name A							Name A	5
4	Name C							Name A	5
5	Name A							Name C	9
6	Name A							Name A	5
7	Name E							Name A	5
8	Name B							Name E	1
9	Name B
10	Name C
11	Name C
12	Name C
13	Name D
14	Name B
15	Name B
16	Name B
17	Name B
18	Name C
19	Name A
20	Name C
21	Name C
22	Name B
23	Name C
24	Name C
Count

Cell Formulas
Range	Formula
J3:J8	J3	=COUNTIF(B2:B24,I3:I8)
Dynamic array formulas.

 

[shacol03]

Thanks for updating your version info.


Then try this formula in cell J3 - it will spill just far enough & no further.

24 01 04.xlsm
	B							I	J
1
2	Name A
3	Name A							Name A	5
4	Name C							Name A	5
5	Name A							Name C	9
6	Name A							Name A	5
7	Name E							Name A	5
8	Name B							Name E	1
9	Name B
10	Name C
11	Name C
12	Name C
13	Name D
14	Name B
15	Name B
16	Name B
17	Name B
18	Name C
19	Name A
20	Name C
21	Name C
22	Name B
23	Name C
24	Name C
Count

Cell Formulas
Range	Formula
J3:J8	J3	=COUNTIF(B2:B24,I3:I8)
Dynamic array formulas.

ohh.. it works.. really appreciate it .. thanks alot sir !!!!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.