In this article, you will learn how to consolidate cells in Google Sheets. In Google Sheets, you can merge cells in three ways, “Merge vertically”, “Merge horizontally”, and “Merge all”, as shown in the picture below. Merged cells sometimes are helpful to make your table or list look nice but may block your smooth work on the sheet. So, it is essential to understand the effect of merging cells.
How to get cells merged in Google Sheets
(i) With shortcut
There is no direct shortcut key. However, there is a shortcut to open the menu.
Select the cells you want to merge.
“Alt”+”O”, and then “M“ (on Windows) or “Control”+”Option”+”O”, and then “M” (on MacO).
Choose your action, “Merge all”, “Merge horizontally”, “Merge vertically”, or “Unmerge”. (Available options change according to the selected cells).
(i) Without shortcut
Select the cells you want to merge.
(i) Click the “Merge cells” icon (or go to the “Select merge type” icon next to the “Merge cells” icon and select how to merge) or (ii) navigate to the “Format” tab → “Merge cells” and choose your action, “Merge all”, “Merge horizontally”, “Merge vertically”, or “Unmerge”. (Available options change according to the selected cells).
What you need to consider when you merge cells
Only the value in the top-left cell is preserved when you merge cells.
When you copy and paste a merged cell, the merged cell is reproduced with the same content and formatting. If you need to copy only the value of the merged cell, you need to choose “Paste value only” (or “Paste as text”) when you paste.
Merged cells may be an obstacle in inserting rows and columns. If two rows or columns between which you want to insert a new row or column contain merged cells, you can’t insert a row or a column. You need to unlock the merged cell and then insert a new row or column.
Merged cells also prevent seamless copy and paste of a data set. Assume you want to copy and paste formulas in cells B2 and C2 vertically by a hundred rows, and there is a merged cell across the two columns B and C. (e.g., B5 and C5 are merged). As the merged cell can contain one value, only the formula in B2 is copied. Again, you need to unmerge the cells to copy and paste the formulas correctly.
In conclusion, you need to consider when you merge cells carefully. If you still want to consolidate cells, we recommend you consolidate cells when you don’t need to process a data set (e.g., inserting rows and columns or copying and pasting formulas and values).
How to unmerge cells
The process of unmerging cells is similar to that of merging cells.
Select a merged cell you want to unmerge.
(i) Click the “Merge cells” icon (or click the “Select merge type” icon next to the “Merge” icon and choose “Unmerge”, or (ii) navigate to the “Format” tab → “Merge cells” → “Unmerge”.
Why can I not merge cells in Google Sheets?
If you can’t merge cells, probably the cells are locked. You need to remove the protection of the cells. Check this article to learn how to unlock the cells.
Other formulas and tips related to text and cell formatting
If you don’t get the right answer to your question in this article or you have other questions related to text and cell formatting, you may find the answers in the following articles.
Are you learning this tip to build a financial model or financial KPI dashboard? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.