Inconsistent Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1698703%22%20slang%3D%22en-US%22%3EInconsistent%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698703%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20do%20certain%20data%20cells%20show%20as%20Inconsistent%20formula%20alert%20when%20the%20contents%20are%20simply%20numerical%20data%20e.g.%20R3%20and%20others%20don't%3F%3C%2FP%3E%3CP%3ESecondly%2C%20if%20I%20attempt%20to%20insert%20a%20blank%20row%20into%20the%20table%20it%20copies%20the%20Subtotal%20formula%20into%20each%20cell%20of%20the%20insert%20row.%26nbsp%3B%20The%20Clipboard%20is%20empty.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1698703%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1698813%22%20slang%3D%22en-US%22%3ERe%3A%20Inconsistent%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1698813%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F803094%22%20target%3D%22_blank%22%3E%40Andrewtickner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20there%20were%20formulas%20like%26nbsp%3B%3D%2BSUMPRODUCT(%24F%24139%3A%24F%24142%2C%23REF!)%20in%20these%20cells%2C%20Excel%20remembers%20initial%20formula.%20To%20avoid%2C%20select%20entire%20column%2C%20delete%20everything%20and%20start%20typing%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi

 

Why do certain data cells show as Inconsistent formula alert when the contents are simply numerical data e.g. R3 and others don't?

Secondly, if I attempt to insert a blank row into the table it copies the Subtotal formula into each cell of the insert row.  The Clipboard is empty.

 

Many thanks

 

1 Reply

@Andrewtickner 

That's since there were formulas like =+SUMPRODUCT($F$139:$F$142,#REF!) in these cells, Excel remembers initial formula. To avoid, select entire column, delete everything and start typing numbers.