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
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
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
IF statements and conditional formatting
clare1981 in Excel on
0 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
4 Replies