0

At first sight, it seems like an easy issue for me. But after extensive googling, I just can't seem to find the solution to this particular issue. The issue being: I have a table of data. But for some of the columns, Excel keeps on replicating the value I enter in a single cell to all the other cells in the column. How can I stop Excel from doing that?

I found it a bit difficult to describe in detail without the chance of misinterpretation so I uploaded a video on youtube that shows it clearly: https://youtu.be/fabdeyKgH5c

As you can see, at the start, there's a formula in the last column. But for the sake of illustration, I just give the first row in the column the value of 5. Rows 6 and 7 also change into 5. I change it into 3, everything changes into 3. Same with the value of 1.

Works with the formulae as well. For example, the column K (dagen in periode / days in period) is supposed to count the number of days for the period between the values in column C and D. Right now, it works, because 243 is the right answer for the first row of the table (row 5). But it replicates the answer for rows 6 en 7, which is false.

The formula in the cells of column K is: =DAYS([@Eind];[@Begin])+1 When I click the cell in column K of row 7, and affirm the formula using Enter, all three values in the column will show the result that is supposed to be specific for row 7.

If you guys have any idea, please let me know. Pretty much stuck with my project right now. Would be greatly appreciated!

cngzz1
  • 143
  • 2
  • 9
gurben
  • 1
  • 3
  • @Sean C, thanks for the reply, but the option you mention is already deselected. So unfortunately not the solution to the problem here. – gurben Dec 21 '20 at 09:49

2 Answers2

0

I think this is due to the AutoCorrect option in Excel. Try changing the options in Excel: Options > Proofing > AutoCorrect Options > AutoFormat As You Type tab > deselect 'Fill formulas in tables to create calculated columns'

I had the same problem, and changing this setting worked for me.

Sean C
  • 21
  • 3
0

Ok, well not sure about the exact reason for the issue I was having, but what I found is this.

  • Restarting my computer, reopening excel again worked
  • The table I was working on was edited with a macro. I think this somehow caused the issue. After I remade the table I did not encounter the issue anymore. Until I ran the macro, have seen it occuring once more than. Anyway, for me this issue is solved.
gurben
  • 1
  • 3