Why is merging spreadsheets needed? Because combining data from multiple spreadsheets into one spreadsheet helps us easily manage and analyze a large volume of data.
For example, if a sales team receives survey data from more than one source, updating and analyzing records from a single merged file is much more straightforward and easy.
But before you go merging your spreadsheets, here are 6 indispensable tips you should consider
When we say EXACT, we mean EXACT
Let’s look at an example where we want to merge (append) 2 spreadsheets
Here is the data for a spreadsheet called People_1
Here is the data for a spreadsheet called People_2
Both spreadsheets have only 1 identical column name: Name
Column name Age has extra whitespace in front of it in People_2, and column name Sport is lowercase with an extra comma in People_1
This results in the following merged spreadsheet
The column names that were not EXACTLY the same become separate in the merging process :(
This tip is similar to the previous one, but instead of merging vertically (appending), we will demonstrate how joining is affected when the values in the columns are not EXACTLY the same
Here we see data from two spreadsheets
Below is the result of joining the spreadsheets on the Name column
Since some of the values in the Name column are not EXACTLY the same, they do not appear in the final result. SAD :(
It is always recommended to convert to or use the CSV file format when merging (.csv).
It is much simpler to merge CSV files because they are plain-text files under the hood.
Being text files, a much much larger number of software programs support importing, reading, and merging them, compared to .xlsx Excel files.
Counterintuitively, it is easier to merge multiple Excel (.xlsx) files where the data to merge is in the first sheet of each file.
Compared to when the data is in multiple sheets in even just 1 Excel file!
This is due to the default setting in how Excel files are read in various merging software programs.
Using the.xlsx format instead of the.xls format also avoids exceeding the Excel limit.
If we leave empty lines at the top and empty columns to the left side of our data, then we need to pre-process (or clean) the data before merging to remove the blank rows and columns.
This wastes valuable time and slows down the process
If there are empty cells in the spreadsheets to merge that contain blanks (or spaces), then the merged file will result in empty cells in the merged data
Now that you know these indispensable tips, go forth and merge with confidence!