Fill Merged Cells Down
July 02, 2018 - by Bill Jelen
Merged cells are the problem today, but these are vertical merged cells. Take a look at the figure below. If there are 7 rows for the Midwest region, the word Midwest appears only on the first row. Someone is then merging the remaining six blank cells with the Midwest cell to create a merged cell that is 7 cells tall.
This is a horrible way to send out data. If someone is proficient in Excel, they might want to sort the data, add subtotals, use pivot tables. Merged cells prevent this.
To make matters worse, you are getting a new file from headquarters every day structured like this. HQ is sending similar files out to 500 branch offices. Being just one tiny outpost, you don't have much chance to convince HQ that this is a horrible way to send out data.
Today's article is about how to quickly solve this problem with Power Query. The Power Query solution will be easier on days 2 through 9999 than the following steps in Excel:
Excel gurus might suggest these steps every day:
- Select all cells by choosing the rectangle above and to the left of A1.
- Click the dialog launcher in the lower right corner of the Alignment group of the Home tab.
- Click the Merge Cells box twice to unselect it.
- Click OK to close the Format Cells dialog
- Select from the end of column A back to A1.
- Home, Find & Select, Go To Special, Blanks, OK
- Type = UpArrow. Press Ctrl + Enter
- Select from the end of column A back to A1.
- Ctrl + C to copy. Right-click and Paste Values
But there is a much easier way. The new Power Query tools are built in to Windows versions of Office 365 and Excel 2016. If you have a Windows version of Excel 2010 or Excel 2013, you can download Power Query for free from Microsoft.
Here is the strategy with Power Query:
- Make a plan that you will save each day's workbook from HQ in the same folder with the same name.
- Open a new blank workbook that will hold the fixed data from HQ.
In the blank workbook, choose Data, Get Data, From File, From Workbook.
- Browse to the folder and file from step #1.
When the Power Query window opens, notice that the merged cells are gone. You have Midwest in row 1 and then six cells that contain "null". Select this column by clicking on the Region heading.
In Power Query, select the Transform tab. Choose Fill, Down. The word Midwest is copied from row 1 to rows 2 through 7. Similar tranformations happen throughout the data set.
Home, Close & Load. Power Query will close. In about 10-20 seconds, the cleaned data from HQ will appear in a new worksheet in the workbook.
- Save the workbook with a name such as CleanedDataFromHQ.xlsx.
- When the data is selected, you should see the Queries & Connections panel on the right side. If you don't see the panel, go to Data, Queries & Connections.
Right-click the query in the Queries & Connections panel. Choose Properties.
Choose "Refresh Data When Opening the File". Click OK.
Your workflow then becomes: (a) Receive the workbook from HQ in e-mail. (b) Save the attachment in the correct folder with the correct name. (c) Open the CleanedDataFromHQ.xlsx workbook. The new data will load into the workbook.
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.
Download Excel File
To download the excel file: fill-merged-cells-down.xlsx
Power Query is an amazing tool - it allows you to solve a variety of data-cleansing problems.
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
"For innovators there is always a simpler solution for the issue at hand."
Title Photo: Chad Walton on Unsplash