SOLVED

Automatically applying formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2136942%22%20slang%3D%22en-US%22%3EAutomatically%20applying%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2136942%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20it%20is%20being%20shown%20on%20the%20picture%20attached%20to%20this%20post%2C%20my%20Excel%20is%20%22replying%22%20and%20%22applying%22%20automatically%20a%20formula%20that%20has%20been%20written%20on%20the%20G4%20cell%20all%20the%20way%20through%20the%20G6%20one%20and%20this%20behaviour%20is%20being%20alerted%20by%20the%20blue%20line%20around%20those%20cells.%20How%20do%20I%20deactivate%20this%20feature%2C%20pelase%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2136942%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2137085%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20applying%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2137085%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F967363%22%20target%3D%22_blank%22%3E%40lucasalmeida05%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20called%20spill.%20In%20English%20your%20formula%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOFFSET(dados!%24c%243%2C1%2C%2CCOUNTA(dados!c%3Ac)-1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit%20returns%20as%20many%20records%20starting%20from%20next%20to%20C3%20as%20counted%20in%20column%20C%20minus%201.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20what%20you'd%20like%20to%20return%2C%20if%20only%20one%20next%20record%20then%26nbsp%3B%3DOFFSET(dados!%24c%243%2C1%2C%2C)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2137224%22%20slang%3D%22en-US%22%3ERe%3A%20Automatically%20applying%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2137224%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F967363%22%20target%3D%22_blank%22%3E%40lucasalmeida05%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CHOW%20do%3D%22%22%20i%3D%22%22%20deactivate%3D%22%22%20this%3D%22%22%20feature%3D%22%22%3E%3C%2FHOW%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EBy%20reverting%20to%20obsolete%20versions%20of%20the%20Excel%20and%20applying%20traditional%20spreadsheet%20techniques.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EBetter%2C%20would%20be%20to%20learn%20and%20embrace%20the%20new%20functionality.%26nbsp%3B%20It%20is%20well%20worth%20the%20effort!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

As it is being shown on the picture attached to this post, my Excel is "replying" and "applying" automatically a formula that has been written on the G4 cell all the way through the G6 one and this behaviour is being alerted by the blue line around those cells. How do I deactivate this feature, pelase?

 

 

3 Replies
best response confirmed by lucasalmeida05 (New Contributor)
Solution

@lucasalmeida05 

That is called spill. In English your formula is

=OFFSET(dados!$c$3,1,,COUNTA(dados!c:c)-1)

it returns as many records starting from next to C3 as counted in column C minus 1. 

Not sure what you'd like to return, if only one next record then =OFFSET(dados!$c$3,1,,)

@lucasalmeida05 

<How do I deactivate this feature, please?>

By reverting to obsolete versions of the Excel and applying traditional spreadsheet techniques.

Better, would be to learn and embrace the new functionality.  It is well worth the effort!

@Sergei Baklan 

Thank you for answering me! I made the suggested changes on the formula and it worked out!