Instead of manually refreshing the data, right-click on the query in the Queries and Connections pane. Any time you click Refresh, Excel will reload the data and fix the merged cells. Click on the left edge of this panel and make it wider so you can see the Refresh icon. When the data loads in Excel, you’ll see a narrow Queries and Connections panel appear on the right side of the screen. Further, you can leverage the Power Query steps and have them happen automatically every day going forward. There’s a good chance that you can do the Power Query steps in half the time of the nine steps in Excel. The data from the original file will appear in the new blank workbook, but the merged cells are gone and the data has been filled down. In Power Query, choose Home, Close, and Load.Excel fills all of the null cells with the value from above. On the Power Query Transform tab, choose Fill, and then Down. The now-empty cells are all marked with the value null. Notice that the merged cells are automatically unmerged when the data appears in the Power Query Editor.
![merging cells in excel 2016 cells in rows merging cells in excel 2016 cells in rows](https://www.techonthenet.com/excel/cells/images/merge_cells2011_001.gif)
In the bottom-right, choose Transform Data.
![merging cells in excel 2016 cells in rows merging cells in excel 2016 cells in rows](https://howtoimages.webucator.com/504.gif)
Start with a blank workbook that will hold the transformed data from this file.
![merging cells in excel 2016 cells in rows merging cells in excel 2016 cells in rows](https://i.pinimg.com/736x/97/92/f3/9792f32ebc6a680e96e356bf18a07601--microsoft-office-data.jpg)
Most people don’t realize that Microsoft quietly added such powerful tools to the Data tab in Excel 2016.įor it to work, you will need to save each day’s workbook in the same folder with the same name. You can open the Power Query editor, fix the problem in a few steps, and then teach Excel to apply those transformations automatically every time you get a new file. But if you have Excel 2016 or newer, there’s another option. Ctrl+C to copy, then right-click and Paste Values.Then press Ctrl+Enter to enter a similar formula in all cells. On the ribbon, go to Home, Find & Select, Go To Special, Blanks.Select from the end of column A back to A1.Click OK to close the Format Cells dialog.Click the Merge Cells box twice to unselect it.Click the dialog launcher in the lower-right corner of the Alignment group of the Home tab.Select all cells by choosing the rectangle above and to the left of A1.As the person receiving the file, you could either do all nine steps every day or find someone to write a macro to automate the steps:
![merging cells in excel 2016 cells in rows merging cells in excel 2016 cells in rows](https://cdn.ablebits.com/_img/d-19/blog-adv/merge-cells-2/slide-3.png)
This can be solved in nine complicated steps. Here’s the data set with merged labels in column A. It’s an ugly problem, but I’m sure similar issues are happening in other companies. And with a new file arriving every day, whatever steps I suggest would have to be repeated daily. Plus, there seems little chance that the attendee, working at an office in Illinois, is going to be able to convince the headquarters in Europe that it’s a really bad way to send out data. Horizontal merged cells make it difficult to sort, copy, and paste-this was the first time I had ever heard of anyone vertically merging cells. As I listened to this question, I was bewildered. One reason I love doing live Power Excel seminars for chapters of IMA ® (Institute of Management Accountants) is that I always get to see real-life problems happening in Excel.