6 Indispensable Tips for Merging Your Spreadsheets

by Author Profile Picture Harshit Jain on 2021-10-22

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

1. Column names must be EXACTLY the same when Appending or Joining

When we say EXACT, we mean EXACT

  1. There should be no whitespace
  2. The format should be the same, i.e., lowercase, uppercase or title case
  3. They should be spelled the same
  4. There should be no additional punctuations

Let’s look at an example where we want to merge (append) 2 spreadsheets

Here is the data for a spreadsheet called People_1

Append Table 1

Here is the data for a spreadsheet called People_2

Append Table 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

Final Append Table

The column names that were not EXACTLY the same become separate in the merging process :(

2. When Joining, the values in the Column(s) must be EXACTLY the same

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

Join Data with Columns

Below is the result of joining the spreadsheets on the Name column

Final Join Data with Columns

Since some of the values in the Name column are not EXACTLY the same, they do not appear in the final result. SAD :(

3. The file should be in CSV format while merging it

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.

4. If your files are .XLSX, keep your data in one sheet

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.

5. Don’t leave empty rows at the top or empty columns to the left of your data

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.

Blank rows or columns

This wastes valuable time and slows down the process

6. Don’t add empty lines at the end of your data

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

Blank Join 1
Blank Join 2

Final Blank Join

Now that you know these indispensable tips, go forth and merge with confidence!