Formulas and Merging Cells

Brass 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?