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
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies