00:00 So how to merge cells and google sheets, we're going to use this merge function up here in the uh menu, but I'm going to show you lots of reasons why you should uh merge cells, I'm going to show you lots of reasons why you merge cells horizontally vertically and maybe we might get into some Apps Script
00:17 which is going to be very interesting but let me first show you why we may do this so we have first name last name and we have two separate columns but we don't want to have the header say first name last name we want it to say something as simple as name and we want it to span both of these columns 
00:34 so we select A2 and B2 here and then we're going to go to the merge cells button and we're going to merge cells when anything we select we can merge into this so maybe we have first name and last name but maybe we want to put in here and look we have a error so we can't move anything inside of here so
00:56 what I'm going to do is undo the merge and then I'm going to move this one here and let's say this is like the middle name.
01:04 Right or the middle initial perhaps. Now we want to merge all three of these cells so I'm going to select A2, B2 and C2 and do exactly the same thing.
01:17 Merge and now we have one element and we can center that here we can say name and we can say okay great we have a header for all of these columns but there's some other things here we have merge all merge vertically merge horizontally and unmerge.
01:34 And so any time we select any number of cells or any range so we can have even like three columns and four rows if we go up to the little arrow next to the merge cells we're going to have these options.
01:52 If we just click merge cells it's going to merge all and that's cool we can create one element here we'll undo that but let's just see what happens when we merge horizontally and what it does is it creates rows this is really cool right let's do that again and select the exact same things and we're going
02:09 to click the arrow and say merge vertically and now we get columns that are merged so this is very very useful for a lot of different reasons and again I want to show you that we can also unmerge here through this menu item and so what happens though if we want to like sort of present a little bit of
02:28 data but in a nice way like maybe in a different way and instead of name over here on the header you see this all the time what if we had like groups of different people let's add a row down here.
02:40 And we wanted to say hey these are different types of people yeah we know their name we can clearly see that these are names but their categories maybe these are VPs and these are SVPs and these are CEOs.
02:52 Uh what I'm going to do is move a column over to the left. I'm going to select all of the rows to left of this selection and we're going to merge these and say I want to call these VPs.
03:06 Uh and this doesn't look very cool right it doesn't look very nice but it's all vertically aligned. Uh what I can do is center this and I can also rotate this text.
03:20 Actually I'm going to call this vice-presidance. There. And what I will also do is give it a slight color. Let's say hello.
03:28 I'm going to give all of this as well a slight color. So now we have this header that's at the head but on the side it's like a vertical header to the side.
03:37 We can do exactly the same here. Let's say we have a group of people that are CEOs. We can do exactly the same again.
03:45 We are going to merge those cells. We're going to align in the center and we're going to rotate that and we can go to CEOs.
03:54 Also we can add some color. This makes it really a nice way to sort of, segment little groups of people here.
04:03 So let's do that again. And we have a nice way to sort of segment them. We can also view with out grid lines and this makes it look a whole lot nicer.
04:15 Right? We have, We have these categories of people and they're listed with their header here. We can also make this header a little bit darker perhaps the same color but a different tone here.
04:28 Makes a really cool list. One thing you might want to, do and you cannot do is if we have text in both of here, both here, the first name and the last name here, Melinda Cooley and we're like hey we want to merge this text and we click on merge cells.
04:43 It's going to give us a heads up and an alert and say merge cells will only preserve the top, left most value.
04:50 Would you like to merge anyway? If you click ok, we only get the Melinda. So in order to actually merge these two words, let's say, uh we need to do something completely different.
05:04 So if you're instead of trying to merge two cells, you're trying to merge the text in them. We can use something like join and select a delimiter.
05:12 So just the thing between them is going to be a space. The value an array can be b3 to c3.
05:20 And we're going to get all of these people's full names in one cell. And that's how you merge text from two different cells.
05:28 But let's give you another option here instead of join where you have to think of what is the thing between everyone first and then every thing you put in here is going to have that thing between them that do the limiter.
05:40 Let's do concatenate. This is a totally different, uh formula. But concatenate is just going to push everything together. So if we had b3 colon c3, we are literally going to get the full name with no space.
05:56 So all we have to do is add another comma in between here and put in a text in quotes of a space.
06:03 And we can do that all the way down and we the full name with a space. If let's say we want to create some kind of e-mail or a g-mail account.
06:14 We have some domain. We can use this, uh, concatenate. We can instead of a space maybe put a hyphen and then add at the end at domain.com.
06:28 And this gives us a really cool way to create sort of a e-mail or some ID maybe or concatenate everything, to bind everything together.
06:40 Uh, with something other than a space we can choose whatever we want. That's pretty cool. Basically you're merging this text with a lot of different other text.
06:48 Back to merging cells. Again I want to show you this example of what you may not want to do if you are selecting all of these cells and you want to merge them all into one cell.
06:57 We click merge cells. Again it gives us this problem or this alert and says hey you're only going to preserve the top left most.
07:05 Click OK and see we only have Melinda but all of our cells have been merged. So all of the selection we have is absolutely merged.
07:12 We only have the one thing which is the Melinda here. The top left most value is going to be preserved.
07:18 That is something to be aware of as you are merging cells. And unmerging cells doesn't undo it. Uh command Z will undo it or, If you are not on a non-mack control Z, but again uhh let's merge those cells.
07:34 Okay. And now if we click on unmerge we have nothing but Melinda. So the text is destroyed if we are merging and unmerging using those cells.
07:44 But unless we do the command Z or undo function we can get that text back. So merging cells is very useful in creating a calendar or sort of a custom calendar I would say.
07:56 Where you can do a lot of really cool things. So this might be what your thing about when you see a calendar or here calendar.
08:02 You have your days of the week up here and this is just a monthly calendar. We can have the entire month here centered and we can say something like me.
08:10 Ah here and say hey here's a monthly calendar. Here's you know up to five separate weeks. And we have some information in here.
08:19 But let's say we wanted to add much more data inside of each of these days. And we also don't necessarily only want to have one column of data per day.
08:32 So what we can do is have Monday a couple times. Then Tuesday. Couple times. Witness. Day. And you can see what I'm doing here.
08:45 But we have this Monday, Monday, Tuesday, Tuesday, Wednesday, Wednesday. We're going to have this. Let's merge. These. We only need one.
08:54 And we're just going to copy paste this and rename it Tuesday. Copy paste it. Wednesday. Thursday. Stay. Friday. And then maybe Saturday and Sunday are only one.
09:10 Because maybe this is a work calendar. We don't need that much space there. But see now we have two columns for each of these.
09:19 We have these. My shelves are going to put them in. Uh together we're going to make this a little bit smaller.
09:32 What we can do here is resize columns to exactly 50. These two columns I will resize to 20, 30 each might be not enough.
09:44 It's okay. Maybe we resize this to 50. Resize or 100. And they're now exactly the same size. So, but we have two columns for each of these days.
10:01 Which is really cool. We can add maybe a third column, fourth column here. Add some little data here. Maybe we have the date here or some notes or even something as simple as.
10:11 Umm select the second part of each of these. If I can find it C E G I and K. And let's resize these to 200.
10:24 So, so sorry. Thank you. Great. And now we can add a checkbox. Maybe this is a weekly calendar. And we create a checkbox in each of these.
10:34 Is it checkbox? And so now you have a checklist Each and every day. And we use this merge cells to have a header here for each of these days.
10:45 So one cool thing about Google Sheets and merging is that if it's here in this menu quite likely it is available in Apps Script.
10:55 So we can automatically merge things. Let's say we want to merge things automatically at the end of the day or at the end of the week or at the end of a month or to prepare a template.
11:04 Let's go up to Extensions Apps Script and see what we can do. And I've already written a couple of things.
11:10 One is for a menu up here. Which is just function on open. Uh variable ui equals split sheet app that get ui ui dot create menu merge menu.
11:20 I am literally titling that menu here. Add item merge all. I am creating my own merge all function and I called it merge e.
11:28 And literally I typed out this myself function merge me. And then we have a function in curly brackets and all what's going to do is do spreadsheet app.
11:40 Get active range whatever the ranges that we have selected. And we're going to merge. Okay that's all we're doing right now but I'm going to show you some interesting stuff we can do with this.
11:52 Again we can use this function that we've written to automatically merge things. So let's take these 1, 2, 3. you select those go to merge menu merge all.
12:03 I'm going to run the script and there it is exactly the same as we had before but it does not give us that menu item that alert that says hey we're emerging things and only the top left one will be saved but it will do that it will only save the top left one.
12:20 So this is a little bit trickier to use but because it doesn't have alert but once you start wanting to automatically do these things you don't need that alert as well.
12:31 All right. So we can automatically merge that kind of thing. Uh, why do I mean automatically? Because now that we have this function that merges the act of range we can essentially merge anything we want and we can run it through a trigger.
12:47 So if you haven't yet My other videos I do talk about triggers and this is where the automatic things happen.
12:52 We can add a trigger. We can select the function which to run and merge me in this particular case is not going to work very well because it's taking an act of range.
13:02 So maybe you want to do that every hour but it's only going to take literally what you have selected. But what we can do however is a sign sort of we can look at the spreadsheet app, get active spreadsheet, get sheet by name.
13:15 We can find certain sections of the site of the pages we want to merge. Uh, by the name down here we can do all of this automatically.
13:24 So let's say you have a group assignment or you're trying to assign groups and you're like hey this one is going to be group one, this one is going to be group two and you want to create this header here.
13:34 We have group one but we want to merge it, we have group two but we want to merge it here again.
13:39 Uh, again we can do this programmatically. We can say hey take this row where on, insert a row, insert group number and merge the cells.
13:48 So let's try to do that. We want to merge group. Thank you. And so we're going to take a spreadsheet app dot get active range.
13:58 Uh we're not going to merge it yet because we want to get a row index I think we want to do.
14:05 Let's look at that and logaritht log. Umm let's create a variable row equals row and let's log that and see what it gives us.
14:17 So if we have we're assigning 15 to 19 and we want to run this merge. Thank you. Group. See if it gives us 15.
14:27 Yep. It gives us 15. So what we want to do is insert a row. So we'll take spreadsheet app. Get active.
14:40 spreadsheet. Get sheet by name. Actually we don't even need that. We need get active. Sheet. There we go. Dot. Insert.
15:00 Row. Before. Row. We have to make sure this is after the variable row so that it actually knows what row to go to.
15:06 Okay we wanna insert that. And now we wanna take what- whatever row that was, which was 15. And combine cell A and 2 or merge them.
15:19 Okay so let's do. Spread sheet ab. connective sheet. Get range. Uh we are going to do row. We're going to do column 1.
15:39 We're going to take the number of rows as 1. Call it number of columns as 2. We want to get the first 2.
15:45 And we want to merge. Okay. So now when we run merge group, it'll take wherever we are, insert a row, and then merge them.
15:55 Let's see if it works. We can go here merge group. Run. Let's see if we get any errors. We do not get any errors and we get a merged cell right above where we are.
16:10 Now let's add it to our menu. So we just add a item. Thank you. Add header to group. And we're going to call this merge.
16:22 Uhh. Merge group. And so now all we have to do is restart, save this. Close it and restart our sheet.
16:35 On open it's going to create that menu. Again. So let's check out ME, bell these five. We have merge menu.
16:47 Add header to group. Thank you. And right above our selection we have inserted a merge row. So this helps you automatically merge cells whenever you can capture that range.
16:58 We're doing this on the fly here. But you can do this also based on sheet name anywhere you can access it.
17:04 Sheet file in a sheet tab and know what range to merge. I also showed you this type of group uh header where instead of at the top we can put it on the side.
17:14 And let's try to do this automatically here. So instead of doing a group 1 and a group 2. Let's do horizontally.
17:22 Let's duplicate this or rather vertical. Vertical. And what this is going to do is let's insert uh com to left.
17:36 And we're going to select this as a group and we want all of what we want to do is take the entire index uh from the start to the finish of our selection.
17:44 Uh we want to merge those cells and call it group. Let's do the formatting ourselves right now. We're going to center it both ways and we are going to do this text like this group.
18:00 Okay so how do we get into that umm. Get that merge cell. They're based on a selection. In our abstract, let's write that function merge vertical group.
18:17 And so we're going to do something very similar where you're definitely going to get the index of where we are starting.
18:22 I think we can also get a number of rows equals take all of this as well. And instead of get row index we'll get number of rows.
18:38 So we know exactly how many rows to do. We are going to, we don't have to insert anything we just need to do.
18:46 Spread sheet app. Take this, range. Figure out the range and then merge it. So what is the range? It's going to start at row which is the top.
18:56 Top row, number, uh row a one column definitely. Uh number of columns is going to be actually the first one is number of rows is going to be number of rows and it's only going to be one column.
19:10 Okay, let's see if this works. We're going merge that vertical group based on, let's do merge vertical group. So this should merge all of these in a 10 to 14 if we have done this correctly.
19:25 Let's run it. . And let's look back and we have exactly done that. We have a group here. And what if we wanted to add that text there to group, let's say we want to do spreadsheet app.
19:44 Get active sheet dot get range is going to be row 1 1 1 because it's still now and well now it is one column and one row.
19:56 Uh but over there on that row we're going to set value. Thank you. To group. Let's add that, save it and let's see if we've done it here to 4 row thing.
20:10 We're going to run merge vertical group and we have the word group there automatically. it's say we want all of these people in one group.
20:20 We can click run merge vertical group. It says completed, done and done. Awesome. So let's add this to our uhh to our menu.
20:33 Add item group vertically. And then just take this name merge vertical group. There we go. And this pre-supposes that we have a uh in the first column here.
20:52 Right? We have to add that column. We're formatting it ourselves. And then just saying hey we're going to group these people together.
20:58 Maybe we want to also add a border in there. We can absolutely do that automatically. We can do that through Apps Script as well if we wish it.
21:05 I would like to give you a little bit of a warning when you're merging cells that basically you are uh somewhat destroying one of the biggest aspects of spreadsheets which is that rows and columns are relationships.
21:19 Uh in spreadsheets. I think that the row is one relationship everything in that row is related to each other and a column is also related to each other.
21:28 Uh and by merging cells across these uh entities you're sort of destroying sort of I say. Destroying this relationship you're saying that this name is now three columns.
21:41 This still has a relationship. These three columns are the entire name. Column A is the first name. Column B is the middle name.
21:48 Column C is the last name. It's totally fine. Bye. I just warn you that too much merging and too much of this kind of data changing will change the relationship of your rows and columns.
22:03 Again, one of the best aspects of spreadsheets is that we have this row relationship. Column relations, tab relations and again, sometimes you are destroying this.
22:13 I would just warn you from doing too much of this merging. As I say that warning about too much merging, you may find yourself doing this where you have sales.
22:25 But you have them over the course of year, a day or a week like you have week 1, week 2, week 3, week 4 and then you have.
22:40 Month 1 and you're like hey, I wanna actually every 4 weeks have different months. So we're gonna merge these cells and let's see what happens in me.
22:52 There we go. If we take this, just drag it by this um. Um. Bottom right corner. This month turns to month 2 which is great.
23:01 But this week is not doing very well. It goes to week 2. But we can also redo that there. Um.
23:08 Actually delete this and now do. Week 1, week 2, week 3. And let's see if we can click that button.
23:17 Click and drag there. We have 5, 6, 7, 8. Perfect. And we can keep dragging all the way here and keep doing month 2.
23:26 Actually we need to select these. And drag it over. And we have month 2. Alright, we have to fix it.
23:34 3, month 4. And again we can keep going. And this is like a nice way to sort of determine different time periods here all on the same uhh place.
23:49 We can, one thing I would recommend doing uh in addition to this is going to view show grid lines undoing that and adding borders.
23:59 Or at least some kind of, styling can add borders internally as well. And now you can see that month 2, month 3.
24:08 Uh, there we can also color this differently instead of doing borders if we want. Let's do that. I wanna show you this.
24:16 What that looks like. Let's do no borders. But this month we will do a little bit harder yellow. You can do a little bit lighter yellow here.
24:27 Do darker green here. And lighter green here. So now we have a relationship of this month to these weeks and they're color coordinated a bit.
24:38 But this merging allows us to do that with this background text. Again, we can show this with borders, or with this background color.
24:46 Really nice way to sort of, look at time periods and different time periods here. So another little warning if you are trying to aggregate data or grab data into code from merge cells.
24:58 We do have to access this merge cell month one at B5, the very first part of, of it. Let's look at some grabbing of some functions function, get that cell.
25:12 We're just going to call it that. Uh, we're going to go to spreadsheetapp.getactive sheet. Thank you. Uh, let's make sure we're getting the data one.
25:22 So we're actually going to do active spreadsheet.getsheet. My name.data.getrange. If we wanted to get that a month one, let's say a month one.
25:39 Literally a text month one here. B5. We're going to return ahh variable text equals this and what we're gonna do, Logger.
25:52 That log text. Okay, let's run. Get that cell. Make sure we save. Click run. Oh, we get the ring. So we definitely need to get value as well.
26:11 Make sure we add that. Get value. Save it. Run it. Let's see it again. And we get month one. But what if we want to change?
26:21 What if we didn't know that? We'd change it to C5. Save it. And run it and what will happen. Get info.
26:33 C5. C5 is month one. Not info. So we're getting the wrong information, right? So if we have C6, just make sure we're getting something, right?
26:52 Should say week two. You get week two. Great. This info is just literally information. So we're not getting anything with C5.
27:02 So this is a major warning if we are accessing data uhm in Apps Script but by from merge cells we need to make sure that we're getting that access from the very first top and left most uh cell reference.
27:19 So this is B5. This will be F5. So if we go to F5. Save it and click run. Thank you.
27:29 Let's see month 2. Perfect. But again, just if we do I5, let's say I5. What will we get? We should get nothing.
27:42 There we go. We got nothing. Thank you. I hope you enjoyed this journey through how to merge cells. We've merged cells horizontally, vertically, merge all unmerged.
27:52 We've done it with Apps Script. We've shown you how to do it with Apps Script to do group assignments vertically and horizontally.
27:59 We're also grabbing data and making sure that we're getting the correct data. I showed you a few issues with merge cells such as merging cells and unmerging cells doesn't retain the data when you merge cells with lots of data.
28:11 It only retains that upper most left thing. I hope this was a really detailed uh journey into merge cells and if you want to watch other videos I go deeper deeper into Google Sheets all the time here.
28:25 I'm really excited about it. I hope this was really awesome for you and really thorough. If you have any questions feel free to ask.