Merge Cells and Unmerge Cells

Quick reference

• 00:04 Okay, in this section I want to start to look at formatting spreadsheets.
• 00:08 And in this video I want to look at merging and unmerging cells.
• 00:11 So let's say we've got our Pizza Excel spreadsheet, and we want to merge
• 00:16 all of these cells, A2 through B5, and we want to make them into one big cell.
• 00:22 We want to delete everything in here and
• 00:24 just make this one big cell that sort of straddles both of these columns.
• 00:28 How would we do that?
• 00:29 Well, that's what we're going to look at in this video.
• 00:31 So let's head back over to our code, and I've created a file called format.py,
• 00:36 and we've got our import things here at the top.
• 00:40 So let's create a workbook function, and this is going to be a work book instance.
• 00:42 Now let's load that pizza spreadsheet.
• 00:44 So let's go load_workbook.
• 00:46 And that's pizza.xlsx, now let's create
• 00:52 our active sheet ws = wb.activate.
• 00:57 So we've done all these things many times.
• 00:59 Now like I said let's merge all of those cells that we talked about.
• 01:03 So to do that we just call ws.merge_cells, and this is a function,
• 01:07 let's say Merge Cells here.
• 01:09 And here we just pass in the range that we want to merge,
• 01:16 so we want to merge A2 through B5.
• 01:21 Now, if we don't change this, it will take whatever's in the top left-hand
• 01:26 corner as the value of the merged cell.
• 01:29 So if we look here at our pizza file, that would be John,
• 01:32 the top left hand corner of whatever you're merging.
• 01:35 So we're merging all of these, so
• 01:37 this value will be John, unless we decided to change it.
• 01:41 So let's Save our file.
• 01:43 So that's wb.save, and let's change this name to pizza3.xslx.
• 01:50 Remember, we already have a pizza2 and
• 01:52 we don't want to save it as the same name because we don't over write the data e
• 01:55 already have which is why we want to create different file.
• 01:58 So, okay lets go ahead and save this and run our format.py file from our terminal.
• 02:02 So lets go python Format.py, now we can look at pizza3.xslx.
• 02:09 And you could see boom we've got a big merge cell and John is in there.
• 02:14 So, notice all the data that was in all of these cells before is gone,
• 02:18 has been overwritten by John, which is the only thing in this giant cell.
• 02:22 And that's how we do that.
• 02:23 So we can also unmerge doing the same basic thing.
• 02:28 So let's close this file, head back over to our code, and we can call unmerge.
• 02:34 So let's unmerge.
• 02:36 And it's the same thing, it's ws only now it's .unmerge_cells.
• 02:42 And again, we can pass in whatever we want.
• 02:43 So, let's unmerge, our A2 and B5.
• 02:49 Let's save this and run it.
• 02:53 And when we do, we can open up our pizza3.xlsx file.
• 02:57 And we see these are now separate cells, they're not all merged together.
• 03:02 The data is all gone, but John still remains.
• 03:04 So that's how to merge and unmerge, now like I mentioned,
• 03:07 we could also change the value.
• 03:09 So after we merged, let's comment this out.
• 03:13 Let's just, as we have before we could just ws, and
• 03:17 then we can pass in A2 because we know that's what it's going to be.
• 03:22 And we can set this equal to something whatever we want.
• 03:25 Now if we save this and run it, and then check our pizza3.xlsx file,
• 03:30 we see this has changed to something, right?
• 03:34 Very cool, so that's one way to merge.
• 03:37 You can do it using our other notation that we've looked at in the past,
• 03:40 where we designate rows and columns.
• 03:42 To do that it's the same thing, ws.merge_cells, but
• 03:47 now we can issue a start_row of equal, let's say 2.
• 03:52 We can do a start_column equals 1,
• 03:58 we can do an end_row of 5,
• 04:02 and then end_column of 2.
• 04:07 So this is the same thing as A2 to B5, we're just doing it in row and
• 04:11 column notation.
• 04:12 So A2 is row 2, column 1, B5 is row 5, column 2, all right?
• 04:19 So it's the same thing it's just a different way to do it in case you don't
• 04:22 know the specific titles of your columns and rows,
• 04:25 if you're doing a loop or something, and it's the same thing.
• 04:28 So we can save this and run it real quick.
• 04:30 And then when we open up our pizza3 file,
• 04:33 we see it we get again the same merge thing with something in there.
• 04:38 So that's how to merge in unmerge cells.
• 04:40 In the next video we'll start to look at more formatting type things, and
• 04:44 we'll look at font properties.

Lesson notes are only available for subscribers.