Fill Merged Cells Down

Learn Excel from MrExcel Podcast, Episode 2221: Fill Merged Cells Down.

Hey, welcome back to the MrExcel netcast. I'm Bill Jelen, I was in Calumet City, Elizabeth shows up and says she gets this file-- our file, like this file-- every day and it's horrible. Over here in Column A, it's not just that they put Midwest and left a bunch of blanks, it's a merged cell. Alright? They're sending the-- HQ is sending these files out to locations around the world with these freaking merged cells over here. Merged cells are evil.

Alright, here is how to get rid of this. First off, I'm going to right-click, Move or Copy, Create a copy, and move it to the new book. So we pretend it's like the workbook that Elizabeth is getting. File, Save As, and every time Elizabeth gets one of these workbooks, I want her to put it in the exact same spot with the exact same name. Alright? Exact same name-- perfect. So now we just have that single file, single sheet, I'll file close. And then we're going to create a brand-new workbook. I'm just going to insert a new worksheet here and this new worksheet's going to be called the Report. And on this blank, our Report will do Data, Get Data, From File, From a Workbook, point to the file that we're going to save it in-- so, with the exact same name every single time-- click Import, choose the first worksheet, and then click Edit. Alright. First off, this is freaking awesome-- power query will not have any of this merge cell nonsense, they instantly convert the merge cells into individual cells and all of these cells are null. Choose that column, it's already chosen here, and then Transform, Fill, Fill Down, like that, and then Home, Close & Load. Alright, so, there is my workbook.

Now, I see some have some extra columns out here. Let me right-click and we'll Edit.

And it looks like anything to the right of cost needs to go away. How much extra stuff out there? Right-click and Remove those columns-- perfect-- Close & Load. Alright, now, the work flow becomes, every time that Elizabeth gets a new report from headquarters, She's going to save it in that same spot with reliable name or whatever-- whatever-- we call it, and then open this workbook, and then out here click Refresh.

Or, if we wanted to work automatically, right-click here, go down to Properties, just outside of your screen all the way at the bottom, and then say, Every time that I open this file refresh the data. So every time I open this workbook it'll go out to the new workbook we got from headquarters, it'll replace those blank merged cells with the values that should be there, and life is great like that.

Power Query is covered in my book, but it's also covered in this book by Ken Puls and Miguel Escobar, M is for (DATA) MONKEY.

Alright, so, Elizabeth: How do I get rid of vertical merge cells? My headquarters keeps sending me these files every day. So, the plan: We're going to save the workbook in a reliable place with a reliable name; create a blank reporting workbook, Data, Get Data, From File, From Workbook, specify the sheet, BAM! The merge cells are gone. I select that column and Fill Down, Close & Load; then every time you get a new workbook, save that new workbook in the reliable place with the same reliable name; I open the Reporting workbook and Refresh, or we just set the Reporting workbook to Refresh automatically upon open.

To download the workbook from today's video is the URL in the YouTube description.

Want to thank Elizabeth for being in my seminar in Calumet City, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.