Make two separate Excel files using the same techniques, as shown in the Manually creating data section. Split the dataset that we have been using, create one spreadsheet with the Revenue column, and create a second spreadsheet with the Name column.
File one should be called one.xlsx
and the file will look like this:
File two should be called two.xlsx
and will look similar to the following screenshot:
Now, let's pause and see what we are trying to do here with the two files. The goal is to combine them into one file. There are two methods that we can use, so let's start with the easiest one.
In the first method, open the one.xlsx
and two.xlsx
files. Using the two.xlsx
file, highlight columns A1 through A5. Press Ctrl + C to copy the selected cells. Now, switch to the one.xlsx
file and select column B1. Press Ctrl + V to paste the data. Your spreadsheet should now look like the following screenshot:
Congratulations! At this point, you can save the file as final.xlsx
and you are all done. You have combined two different Excel workbooks into one.
The second method involves using an Excel feature that you will often use in different situations. Let's go through the following steps, and then, I will explain the benefits of using this technique:
Open the one.xlsx
and two.xlsx
files. Using the one.xlsx
file, right-click on the tab named Sheet 1, and select the Move or Copy... option, as shown here:
The Move or Copy dialog box will appear. Select the workbook that you plan to move the data to. In this case, it is going to be 01 – Chapter – two.xls
. Make sure that you have the second workbook open, or you will not be able to see this option in the drop-down menu. In the next section named Before sheet, select the option called (move to end), check the Create a copy checkbox, and click on the OK button, as shown here:
You will now have your second spreadsheet with two tabs: one named Sheet 1 that holds your original data and another one named Sheet 1 (2) that holds the data we just imported from the first spreadsheet. From here on, we can just employ the first technique and combine both the datasets. Good job!
What was so different about the second method? This method gives us options and that is the key. We currently have a spreadsheet that contains the raw data from each of the two workbooks. We can then create a third spreadsheet or a third tab that holds the data from the two datasets. If we make any mistakes, we can simply remake the third tab/spreadsheet, as our original data is still intact. We can also filter the data of our two original datasets before we combine any data. In practice, you will notice that you will be performing a unique combination of these two methods, depending on your dataset and the problem you are trying to solve.