In the blank workbook, choose Data, Get Data, From File, From Workbook.īrowse to the folder and file from step #1. Open a new blank workbook that will hold the fixed data from HQ. Make a plan that you will save each day's workbook from HQ in the same folder with the same name. If you have a Windows version of Excel 2010 or Excel 2013, you can download Power Query for free from Microsoft. The new Power Query tools are built in to Windows versions of Office 365 and Excel 2016. Right-click and Paste Valuesīut there is a much easier way.

Home, Find & Select, Go To Special, Blanks, OK Select from the end of column A back to A1. Select all cells by choosing the rectangle above and to the left of A1.Ĭlick the dialog launcher in the lower right corner of the Alignment group of the Home tab.Ĭlick the Merge Cells box twice to unselect it.Ĭlick OK to close the Format Cells dialog

The Power Query solution will be easier on days 2 through 9999 than the following steps in Excel:Įxcel gurus might suggest these steps every day: Today's article is about how to quickly solve this problem with Power Query. Being just one tiny outpost, you don't have much chance to convince HQ that this is a horrible way to send out data. HQ is sending similar files out to 500 branch offices. To make matters worse, you are getting a new file from headquarters every day structured like this. If someone is proficient in Excel, they might want to sort the data, add subtotals, use pivot tables. Someone is then merging the remaining six blank cells with the Midwest cell to create a merged cell that is 7 cells tall. If there are 7 rows for the Midwest region, the word Midwest appears only on the first row. Merged cells are the problem today, but these are vertical merged cells.

How to merge and center in excel 2013