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

@Siddhartha10 

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

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
Highlighted

@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
Excel on iPad
Mattlewis in Excel on
2 Replies
Help with excel
Alexvez01098 in Excel on
10 Replies
Rotate data from single cell to vertical listings
WEI_Bob in Excel on
2 Replies
2 Seniority Lists
Aminam20 in Excel on
9 Replies
Conditional formatting with wildcard
Rajeev_Raghavan in Excel on
2 Replies