Home

Cell Range Delimiter

%3CLINGO-SUB%20id%3D%22lingo-sub-302359%22%20slang%3D%22en-US%22%3ECell%20Range%20Delimiter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-302359%22%20slang%3D%22en-US%22%3E%3CP%3EI%20used%20to%20be%20able%20to%20use%20a%20period%20to%20specify%20a%20cell%20range%20in%20a%20formula%2C%20e.g.%20--%20%3Dsum(a1.a5).%3C%2FP%3E%3CP%3EThis%20formula%20now%20gives%20a%20%23FIELD!%20error.%20It%20seems%20that%20Excel%20will%20only%20accept%20a%20colon%20(%3A)%20now%20to%20separate%20the%20limits%20of%20a%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20an%20intentional%20change%20or%20am%20I%20missing%20something%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EBob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-302359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388996%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Range%20Delimiter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F254800%22%20target%3D%22_blank%22%3E%40BobM_48%3C%2FA%3E%26nbsp%3BThis%20was%20broken%20with%20the%20release%20of%20the%20%22Stocks%20and%20Geographies%22%20feature%20which%20reworked%20how%20that%20dot%20operator%20is%20used.%26nbsp%3B%20I%20think%20right%20now%20that%20feature%20is%20only%20part%20of%20Excel%202016%20for%20Office%20365.%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20actually%20been%20in%20contact%20with%20Microsoft%20about%20this%20and%20they've%20added%20it%20to%20their%20backlog%20to%20address%2C%20so%20this%20will%20hopefully%20be%20resolved%20at%20some%20point%20in%20the%20future.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-367304%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Range%20Delimiter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-367304%22%20slang%3D%22en-US%22%3EAs%20a%20very%20old%20Lotus%20123%20user%2C%20I%20suspect%20that%20this%20was%20part%20of%20the%20Lotus%20compatibility.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-302468%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20Range%20Delimiter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-302468%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Bob%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20never%20knew%20about%20this%20trick%20before%2C%20but%20it's%20a%20really%20cool%20trick!%3C%2FP%3E%3CP%3EI%20am%20using%20Excel%202019%2C%20and%20I%20can%20use%20it.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EYou%20can%20select%20the%20starting%20cell%2C%20then%20type%20the%20period%20(.)%20to%20allow%20you%20to%20expand%20the%20range%20using%20just%20one%20arrow!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EAnd%20a%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Efter%20entering%20the%20formula%2C%20the%20period%20(.)%20becomes%20colon%20(%3A).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%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%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20this%20feature%20has%20been%20broken%20or%20overwritten%20in%20Excel%20365%20because%20of%20the%20new%20data%20types%20in%20Excel%20(%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%22%3EStocks%20and%20geography%3C%2FA%3E)%20which%20comes%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%22%3E%23FIELD!%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI%20think%20we%20need%20some%20clarification%20from%20the%20Excel%20team.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F131881%22%20target%3D%22_blank%22%3E%40John%20Campbell%3C%2FA%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22UserName%20lia-user-name%20lia-user-rank-Microsoft%20lia-component-message-view-widget-author-username%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9664%22%20target%3D%22_blank%22%3E%40Joe%20McDaid%3C%2FA%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
BobM_48
Occasional Visitor

I used to be able to use a period to specify a cell range in a formula, e.g. -- =sum(a1.a5).

This formula now gives a #FIELD! error. It seems that Excel will only accept a colon (:) now to separate the limits of a range.

 

Is this an intentional change or am I missing something?

 

Thanks,

Bob

3 Replies

Hi Bob,

I never knew about this trick before, but it's a really cool trick!

I am using Excel 2019, and I can use it.

You can select the starting cell, then type the period (.) to allow you to expand the range using just one arrow!

And after entering the formula, the period (.) becomes colon (:).

 

Expand Range Using Period.gif

 

It seems that this feature has been broken or overwritten in Excel 365 because of the new data types in Excel (Stocks and geography) which comes with the new error type #FIELD!.

 

I think we need some clarification from the Excel team.
 
As a very old Lotus 123 user, I suspect that this was part of the Lotus compatibility.

@BobM_48 This was broken with the release of the "Stocks and Geographies" feature which reworked how that dot operator is used.  I think right now that feature is only part of Excel 2016 for Office 365.

I've actually been in contact with Microsoft about this and they've added it to their backlog to address, so this will hopefully be resolved at some point in the future.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies