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. As I listened to this question, I was bewildered. 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. 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. And with a new file arriving every day, whatever steps I suggest would have to be repeated daily. It’s an ugly problem, but I’m sure similar issues are happening in other companies.
Here’s the data set with merged labels in column A.
This can be solved in nine complicated steps. 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:
- 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.
- On the ribbon, go to Home, Find & Select, Go To Special, Blanks. Click OK.
- Type = and then press the up arrow. Then press Ctrl+Enter to enter a similar formula in all cells.
- Select from the end of column A back to A1.
- Ctrl+C to copy, then right-click and Paste Values.
That’s a lot to do every day. But if you have Excel 2016 or newer, there’s another option. 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. Most people don’t realize that Microsoft quietly added such powerful tools to the Data tab in Excel 2016.
For it to work, you will need to save each day’s workbook in the same folder with the same name. Start with a blank workbook that will hold the transformed data from this file. Then follow these steps:
- On the Excel 2016 Data tab, choose Get Data, From File, and From Workbook. Browse to the file with the ugly data set.
- In the Navigator panel, click on the Sheet name in the left panel. In the bottom-right, choose Transform Data. Notice that the merged cells are automatically unmerged when the data appears in the Power Query Editor. The now-empty cells are all marked with the value null.
- Select the Region column. On the Power Query Transform tab, choose Fill, and then Down. Excel fills all of the null cells with the value from above.
- In Power Query, choose Home, Close, and Load.
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. There’s a good chance that you can do the Power Query steps in half the time of the nine steps in Excel. Further, you can leverage the Power Query steps and have them happen automatically every day going forward.
When the data loads in Excel, you’ll see a narrow Queries and Connections panel appear on the right side of the screen. Click on the left edge of this panel and make it wider so you can see the Refresh icon. Any time you click Refresh, Excel will reload the data and fix the merged cells.
Instead of manually refreshing the data, right-click on the query in the Queries and Connections pane. From the bottom of the list, choose Properties. In the Query Properties dialog, choose the box labeled “Refresh Data When Opening the File.”
Each day, after you save the workbook from headquarters, you can open the Power Query workbook and it will automatically unmerge the cells, fill down, and show you the data.
Download this month’s workbook to try it out for yourself: SF-2018-08-SourceData.
I am always amazed by Power Query’s ability to take lengthy tasks in Excel and simplify them dramatically.
SF SAYS
Power Query is in all Windows versions of Excel 2016 or newer. A free add-in from Microsoft is available for Excel 2010 and 2013.
August 2018
