What Should You Do About Merged Cells and How Can Power Query Help?
Welcome to our comprehensive guide on using Power Query to manage merged cells in Excel. This tutorial targets beginners eager to streamline their data processing and enhance spreadsheet management.
Why not follow along using this Excel file.
Understanding Merged Cells
Merged cells are a common issue in Excel that can disrupt data analysis. In this section, we explain the problems merged cells create and why it’s crucial to handle them properly.
Complete Guide to Handling Merged Cells
Step 1: Identify Merged Cells
Have a look at the following spreadsheet.
From the above sheet you can see that people merge cells primarily for aesthetic reasons. Note that each area for North - West have been merged. Also the cells under each area have been merged. This, however, plays havoc with your data. It's these merged cells that we need to resolve.
Step 2: Resolve Merged Cells Using Power Query
If you haven't already done so download the associated merged-cells-start.xlsx exercise file so you can follow along.
First click on a product in the data then click on Data - From Table/Range
The Create Table box will appear and will automatically select the data.
(If you had gaps in your data then you may have to manually select the data).
Click on OK.
The data has been loaded into Power Query.
Note the null values in column 1. When you merge cells values are lost and the data cannot be analysed effectively. Also worth noting is the heading Column1. All columns in data analysis must have a column heading and seeing as there wasn't one in Excel Power Query automatically provided one.
(If you want to know why data has to been organised in a certain way then you may be interested to learn about Edgar F. Codd who worked for IBM. No matter what data you need to handle, his paper on the normal form is basis for all structured data today.)
For this example we're going to remove the Null Values. In the video we were able to filter out the total rows, however for this step-by-step tutorial we will remove all the nulls from the data field.
Click the filter drop-down list to the right of Data and uncheck null and click OK.
Next double click on the column heading column1 and rename it team.
With the column still selected click Transform - Fill - Fill Down.
The fill down process copies each of the values for the relative teams down until it finds a change. If your data is structured appropriately then what was a null value, will be assigned the correct value.
Changing the Data Types
Did you know that when you enter data into Excel that data types are automatically assigned to the columns. This data types are shown at the top of the columns left of the column name for example if you look to the left of the Date column you can see the Date/Time data type. Seeing as we only want to record the date in that column let's change the data type to Date.
Click on the data type button to the left of Date and choose Date from the list:
Now update the data types for Price per Unit and Total Sales to currency.
Click on the Price per Unit column header hold the shift key then click on the Total Sales column
Right click on any of the selected column headers then go to Change Type - Currency. The data type of the columns will change to currency.
Now double click on the name of Table1, on the left, to change the name of the table. Enter SalesData as the new name.
Now it's time to load the data back into Excel.
Click the Home tab then Close and Load.
Note that the data has been loaded back into Excel.
You data appears in Excel and is now ready to be converted into a PivotTable for analysis as brough out in the accompanying video.
Power Query - A Powerful Tool
Power Query is such a powerful tool, and in the right hands can be used for procedures that will save you oodles of time. Ensure that you follow this series to find out more about how much time this data cleansing tool will save you.
Book a training course
OK now that you've had a taste of what we can do at Computer Tutoring then you might want to take a look at our intermediate or advanced Excel 365 training course. It's just the think you need to bring your team up to speed on using the latest technology available to you.