Home

Formulas and Merging Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-360020%22%20slang%3D%22en-US%22%3EFormulas%20and%20Merging%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360020%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excel%20Community!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20requires%20me%20to%20occassionally%20merge%20cells%20WITHOUT%20it%20impacting%20a%20formula%20that%20references%20these%20cells.%26nbsp%3B%20Currently%2C%20if%20I%20merge%20two%20or%20more%20cells%20in%20a%20column%2C%20the%20formula%20no%20longer%20works.%26nbsp%3B%20Any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-360020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360611%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20and%20Merging%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360611%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20answer%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F34788096%2Fis-it-possible-to-test-return-if-a-cell-is-merged-without-vba%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F34788096%2Fis-it-possible-to-test-return-if-a-cell-is-merged-without-vba%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360556%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20and%20Merging%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360556%22%20slang%3D%22en-US%22%3E%3CP%3EAre%20there%20any%20formulas%20you%20can%20use%20(some%20sort%20of%20'IF'%20statement%2C%20I'd%20guess)%20that%20can%20account%20for%20if%20a%20cell%20is%20merged%20or%20not%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-360069%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20and%20Merging%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-360069%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECell%20merging%20moves%20all%20content%20of%20merged%20cells%20into%20upper-left%20cell%20of%20merged%20region.%20If%2C%20for%20example%2C%20you%20merge%20A5%3AA7%20with%20some%20value%20in%20A6%2C%20after%20the%20merging%20the%20reference%20on%20A6%20gives%20wrong%20result%2C%20you%20shall%20use%20A5%20instead.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20there%20are%20a%20lot%20of%20side%20effect%20with%20cell%20merging%20and%20it's%20highly%20recommended%20not%20to%20use%20it.%20Horizontal%20merging%20alternative%20is%20Center%20across%20selection.%20Vertical%20-%20formatting%20could%20help.%20If%20you%20still%20prefer%20to%20merge%20you%20shall%20be%20ready%20what%20something%20won't%20work%20or%20will%20work%20in%20a%20wrong%20way%20and%20redesign%20your%20data%20taking%20into%20account%20merging%20specifics.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
warrevar
Occasional Contributor

Hi Excel Community!

 

I have a spreadsheet that requires me to occassionally merge cells WITHOUT it impacting a formula that references these cells.  Currently, if I merge two or more cells in a column, the formula no longer works.  Any ideas?

 

Thanks!

3 Replies

Hi,

 

Cell merging moves all content of merged cells into upper-left cell of merged region. If, for example, you merge A5:A7 with some value in A6, after the merging the reference on A6 gives wrong result, you shall use A5 instead.

 

In general there are a lot of side effect with cell merging and it's highly recommended not to use it. Horizontal merging alternative is Center across selection. Vertical - formatting could help. If you still prefer to merge you shall be ready what something won't work or will work in a wrong way and redesign your data taking into account merging specifics. 

Are there any formulas you can use (some sort of 'IF' statement, I'd guess) that can account for if a cell is merged or not?

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