How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Monday

Merge and Unmerge Cells and Preserve Values in Google Sheets

Posted by   on

In this tutorial, I will walk you through how to merge and unmerge cells in Google Sheets.


I know it’s a simple task, and many of you may have already familiar with it. I have already explained the same as part of another Google Sheets formula tip.


So, to make this tutorial exciting to read, I have included some formula tips to help you preserve all the values in the merge area that you may lose otherwise.


In another tutorial, I’ll explain how to fill merged cells in Google Sheets as many found it a time taking activity. In that, I will try to make it somewhat a simple procedure.


Introduction to Merge Cells in Google Sheets


I don’t prefer to work in a Spreadsheet that contains lots of merged cells. It may very severely hamper the data manipulating capabilities of Google Sheets or the Spreadsheet program you are using.


When it comes to Google Sheets, there are several functions that won’t work well with a dataset that contains vertically or horizontally merged cells.


The DGET, SORT, QUERY, SUMIF, and FILTER are some examples of this.


Also, meu commands like Sort, Filter, and Randomize range (within DATA menu) won’t work or work improperly.


Here is one example.


Formula Issues with Merged Cells in Google Sheets
screenshot#1

I have four names in column B and their sales quantities in column C.


When I want to sum the sales of “Brendan,” I’ve used the below SUMIF formula.


=sumif(B3:B12,E3,C3:C12)

It returned 7 instead of 10. It is because the SUMIF criteria in cell E3 found a match in cell B8. The cells B9 and B10 do not contain the name “Brendan.” So the corresponding values didn’t include in the sum.


You can solve such issues by filling merged cells physically or virtually. We will learn that in another tutorial. Here we can learn how to merge and unmerge cells in Google Sheets.


Merge Cells in Google Sheets and Different Merge Types


To merge cells in Google Sheets, please follow the below steps.



  1. Select the cells to merge.

  2. Then go to the menu Format > Merge cells (Alt+O+M).

  3. Select one of the available merge options.

    1. Vertically (Alt+O+M+V) – When you have cells selected in a column.

    2. Horizontally (Alt+O+M+H) – When you have cells selected in a row.

    3. Merge all (Alt+O+M+A) – When you have cells selected in multiple rows and columns.




Here one more important point to note. In the last case (point#3.3), that means you have cells selected in multiple rows and columns. In such a case, you can choose one of the vertical or horizontal sortings also.


Five Merge Types Explained


You can merge cells in Google Sheets in five types or directions. Here are them.


Merge Cells in Google Sheets - Five Types
screenshot#2

In addition to the above format menu commands and shortcuts, you can choose the toolbar option to merge cells in Google Sheets.


It’s just adjoining to the “Borders” toolbar icon, which may be faded out by default. To use this toolbar icon, first select more than one cell.


Merge Cells in Google Sheets and Preserving All the Values


Assume you have more than one value in the selected cells. When you merge cells in Google Sheets, it will only preserve the top-leftmost value. Google Sheets will warn you about the same.


It applies to all the above five types.


What is the solution to this issue?


Using formulas, we can preserve values. For that, we will use the TEXTJOIN, ampersand, or both to combine the values.


The solution depends on the orientation of the selected cells.


Here are the formulas to preserve all the values in the selected cells when you merge cells in Google Sheets in all the above five ways.


Preserve Values When You Merge - Formulas
screenshot#3

1. Single Column


The range is B2:B4.


In cell E2, enter the below TEXTJOIN formula. The delimiter in this formula is a new line character expressed by char(10).


=textjoin(char(10),true,B2:B4)

Select E2:E4 > Format > Merge cells > Merge vertically.


2. Single Row


The range is B7:C7.


In cell E7, enter the following formula, which follows the above logic.


=textjoin(char(10),true,B7:C7)

Select E7:F7 > Format > Merge cells > Merge horizontally.


3 to 5. Multiple Rows and Columns in the Merge Range


3. Merge All:


The range is B10:C12.


In cell E10, enter the below formula, which contains the functions ArrayFormula and TEXTJOIN.


The former function is used because of the presence of the ampersand.


=ArrayFormula(textjoin(char(10),true,B10:B12&": "&C10:C12))

Select E10:F12 > Format > Merge cells > Merge all.


4. Vertically:


The range is B15:C17.


In cell E15, enter the below nested TEXTJOIN formula, which also uses the new line delimiter/string separator.


=textjoin(char(10),true,B15:B17),textjoin(char(10),true,C15:C17)

Select E15:F17 > Format > Merge cells > Merge vertically.


5. Horizontally:


The range is B20:C22.


In cell E20, enter the below formula.


=ArrayFormula(B20:B22&": "&C20:C22)

Select E20:F22 > Format > Merge cells > Merge horizontally.


Finally, select the range E2:F22. Then right-click to open the shortcut menu. Select “Copy.” Again right-click. This time select “Paste Special” and apply paste values.


Now you can remove columns B and C.


How to Unmerge Cells in Google Sheets


As I have mentioned at the beginning of this Google Sheets tutorial, I don’t prefer a Sheet with merged cells. But if somebody shares such a sheet, I will instantly unmerge the cells.


I feel comfortable working in Sheets that don’t have such types of formatting.


How to unmerge cells in Google Sheets?


Select the merged cells and go to Format > Merge cells > Unmerge (Alt+O+M+U). You can also use the corresponding toolbar icon.


In the above example (screenshot#3), you can select E2:E4 and unmerge as above.


How to unmerge multiple sets of merged cells in Google Sheets?


If you have multiple types of merging in a Sheet, it will be a tedious job to unmerge all the cells in Google Sheets.


For example, in the above example (screenshot#3), you can select the range E7:F22 to unmerge all the merged cells in one go. But you can’t include E2:E4 with it as the number of columns is different.


That’s all.


Thanks for the stay. Enjoy!


Resources:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!