MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fill Merged Cells Down

July 02, 2018 - by Bill Jelen

Fill Merged Cells Down

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.

Merged cells are evil.
Merged cells are evil.

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:

  1. Select all cells by choosing the rectangle above and to the left of A1.
  2. Click the dialog launcher in the lower right corner of the Alignment group of the Home tab.
  3. Click the Merge Cells box twice to unselect it.
  4. Click OK to close the Format Cells dialog
  5. Select from the end of column A back to A1.
  6. Home, Find & Select, Go To Special, Blanks, OK
  7. Type = UpArrow. Press Ctrl + Enter
  8. Select from the end of column A back to A1.
  9. 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:

  1. Make a plan that you will save each day's workbook from HQ in the same folder with the same name.
  2. Open a new blank workbook that will hold the fixed data from HQ.
  3. In the blank workbook, choose Data, Get Data, From File, From Workbook.

    Start the Power Query process
    Start the Power Query process
  4. Browse to the folder and file from step #1.
  5. 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.

    Power Query automatically removes the merged cells
    Power Query automatically removes the merged cells
  6. 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.

    Fill the null cells with the value from above
    Fill the null cells with the value from above
  7. 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.

    Return the data to Excel
    Return the data to Excel
  8. Save the workbook with a name such as CleanedDataFromHQ.xlsx.
  9. 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.
  10. Right-click the query in the Queries & Connections panel. Choose Properties.

    Open the Properties for this query
    Open the Properties for this query
  11. Choose "Refresh Data When Opening the File". Click OK.

    Set the query to run every time you open the workbook.
    Set the query to run every time you open the workbook.

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.

Watch Video

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

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.