Learn Excel – Fill Merged Cells Down – Podcast 2221


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.

Leave a Reply

Your email address will not be published. Required fields are marked *