Forum Discussion

warrevar's avatar
warrevar
Brass Contributor
Mar 04, 2019

Formulas and Merging Cells

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!

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 04, 2019

    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. 

Resources