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
Siddhartha10
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

@Siddhartha10 

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

@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

@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