In Excel, you can format a report by combining multiple cells into one. The tool is called “Merge”. It is in Home tab, Alignment section, “Merge & Center”.
If you select the dropdown instead of pressing the button directly, you will see options as listed below:
- Merge & Center
- Merge Across
- Merge Cells
- Unmerge Cells
In Merge Cell operation, it is important to know what cells to select and what will happen for each of these options. If you select different areas to combine, the result will be different.
In the screenshot above, the blue dashed lines contain the data, and all four cells are selected at once.
As shown, Merge & Center operation only keeps the cell at the top left, and make the remaining text centred in the merged cells.
Merge Across on the other hand merge cells horizontally only, and keeps the first cell of each row.
Merge Cells work like Merge & Center, except the remaining text is not centred but aligns left if it is a text and aligns right if it is a numerical value.
All three combining operation comes with data loss which may be something to keep in mind.
Excel gives you a warning that only the cell in the upper left of the selected region gets to keep its content. In the case of “Merge Across”, you will get a warning for every row in the selection. So say you selected 10 rows for “Merge Across” operation, you will get 10 warnings.
Look Up or Sum Across Merged Cells
If you perform Excel functions on merged cells you will get incorrect result. This is because in merged cells, only the upper left cell of each merged region contains value.
In the screenshot above, an attempt to sum price by category using SUMIF() function results in wrong values for the very reason mentioned previously.
To look up merged cells or sum across them correctly, you will need to do the following:
Fill blanks in data category as the first step.
Next, select the region that you just filled blanks with (in this case cells E2 to E13) and copy it to another area. Merge cells one by one in this new area.
Select the merged cells, press Ctrl+C to copy or select Format Painter in Home tab.
Apply formatting to cells E2 to E13 in this case using paste special (Keyboard shortcut Alt+E+S+T in that order) or with the format painter.
Cells in Category column are now merged, and you can perform Excel functions on the region as usual.
Follow the link to learn more about looking up merged cells