Home

Excel . (dots) in formula not automatically changes to : when selecting multiple adjacent cells

%3CLINGO-SUB%20id%3D%22lingo-sub-809372%22%20slang%3D%22en-US%22%3EExcel%20.%20(dots)%20in%20formula%20not%20automatically%20changes%20to%20%3A%20when%20selecting%20multiple%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809372%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%20class%3D%22ac-container%22%3E%3CDIV%20class%3D%22ac-textBlock%22%3E%3CP%3EEarlier%3A%20Eg.%20Sum%20A1%3AA10%20in%20cell%20B10%3C%2FP%3E%3COL%3E%3CLI%3EGo%20to%20cell%20B10%20and%20type%20%3Dsum(%2C%3C%2FLI%3E%3CLI%3Eusing%20arrow%20key%20go%20to%20a1%20or%20a10%2C%3C%2FLI%3E%3CLI%3Etype%20.%3C%2FLI%3E%3CLI%3Eusing%20arrow%20key%20select%20the%20full%20range%20and%20press%20enter%3C%2FLI%3E%3C%2FOL%3E%3CP%3ENow%3A%3C%2FP%3E%3CP%3Etyping%20.%20as%20in%20step%203%20above%2C%20and%20the%20trying%20to%20select%20the%20range%20does%20not%20work.%26nbsp%3B%20The%20formula%20after%20hitting%20enter%20key%20shows%20%3DSUM(A10.a1)%20and%20returns%20a%20%23FIELD!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ESiddhartha%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-809372%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809422%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20.%20(dots)%20in%20formula%20not%20automatically%20changes%20to%20%3A%20when%20selecting%20multiple%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393890%22%20target%3D%22_blank%22%3E%40Siddhartha10%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20dot%20in%20a%20range%20with%20SUM%20doesn't%20work%20any%20more.%20It%20was%20discussed%20some%20ago%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809430%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20.%20(dots)%20in%20formula%20not%20automatically%20changes%20to%20%3A%20when%20selecting%20multiple%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809430%22%20slang%3D%22en-US%22%3EBut%20I%20used%20it%20till%20last%20week....%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809432%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20.%20(dots)%20in%20formula%20not%20automatically%20changes%20to%20%3A%20when%20selecting%20multiple%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809432%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393890%22%20target%3D%22_blank%22%3E%40Siddhartha10%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20really%20a%20nice%20trick!%3C%2FP%3E%3CP%3EWe've%20discussed%20this%20issue%20before%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FCell-Range-Delimiter%2Fm-p%2F302359%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3E%3CSTRONG%3EBottom%20line%3A%3C%2FSTRONG%3Ethis%20feature%20is%20broken%20or%20overwritten%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Ein%20recent%20Office%20365%20updates%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Edue%20to%20the%20new%20Excel%20data%20types%3C%2FSPAN%3E%3C%2FSPAN%3E(%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fexcel-data-types-stocks-and-geography-61a33056-9935-484f-8ac8-f1a89e210877%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EStocks%20and%20geography%3C%2FA%3E)%20that%20came%20with%20the%20new%20error%20type%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2F-field-error-66b3a5db-1904-41aa-9913-51ded9dd15f2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%23FIELD!%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F62893iAF43161B76B6C1AA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Expand%20Range%20Using%20Period.gif%22%20title%3D%22Expand%20Range%20Using%20Period.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809449%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20.%20(dots)%20in%20formula%20not%20automatically%20changes%20to%20%3A%20when%20selecting%20multiple%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809449%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20thinking%20this%20to%20be%20a%20trick.%20As%20already%20mentioned%20by%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F300277%22%20target%3D%22_blank%22%3E%40Jaydee2325%3C%2FA%3Ein%20the%20link%20mentioned%20by%20you%20its%20an%20old%20Lotus123%20command.%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20I%20thank%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F278060%22%20target%3D%22_blank%22%3E%40PRAY00%3C%2FA%3Efor%20raising%20this%20with%20the%20Excel%20Team%20as%20this%20was%20a%20very%20nice%20%22trick%22%20to%20be%20fast%20in%20excel.%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%2C%3CBR%20%2F%3E%3CBR%20%2F%3ESiddhartha%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-809450%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20.%20(dots)%20in%20formula%20not%20automatically%20changes%20to%20%3A%20when%20selecting%20multiple%20adjacent%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-809450%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F393890%22%20target%3D%22_blank%22%3E%40Siddhartha10%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20shall%20be%20ready%20to%20what%20some%20undocumented%20feature%20will%20be%20broken%20one%20day.%20This%20one%20is%20not%20the%20only%20which%20eventually%20stops%20to%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

Earlier: Eg. Sum A1:A10 in cell B10

  1. Go to cell B10 and type =sum(,
  2. using arrow key go to a1 or a10,
  3. type .
  4. using arrow key select the full range and press enter

Now:

typing . as in step 3 above, and the trying to select the range does not work.  The formula after hitting enter key shows =SUM(A10.a1) and returns a #FIELD! error.

 

Please help

 

Regards,

Siddhartha

5 Replies
Highlighted

@Siddhartha10 

Yes, dot in a range with SUM doesn't work any more. It was discussed some ago here.

Highlighted
Highlighted

@Siddhartha10

 

It was really a nice trick!

We've discussed this issue before in this link.

Bottom line: this feature is broken or overwritten in recent Office 365 updates due to the new Excel data types (Stocks and geography) that came with the new error type #FIELD!

Expand Range Using Period.gif

Highlighted
@Haytham Amairah

Thanks for thinking this to be a trick. As already mentioned by @Jaydee2325 in the link mentioned by you its an old Lotus123 command.

However, I thank @PRAY00 for raising this with the Excel Team as this was a very nice "trick" to be fast in excel.

Regards,

Siddhartha

@Siddhartha10 

We shall be ready to what some undocumented feature will be broken one day. This one is not the only which eventually stops to work.

Related Conversations
ABOUT PRIVATE CODE NUMBERS
radixanaliz in Excel on
1 Replies
Erro ao tentar usar o SOMASE
elton_janz in Excel on
0 Replies
Excel Map
Filippo2490 in Excel on
0 Replies
Problems to open a excel file with boxes in OSX
jsciko in Excel on
2 Replies
Excel "IF" - formula
Johnnie91 in Excel on
8 Replies