Importing and Merging Datasets Gather Your Data Sources Together into a Single Master Spreadsheet

Sometimes we receive evaluation data in CSV format. CSV files contain “comma separated values,” which means your numbers and text are stored in one big text file with a comma between each value. However, in Excel, we need each value in its own column. In this video, I show you how to transform your CSV data into a tabular format for Excel within a minute using the “text to columns” icon.

The vlookup function is my all-time favorite function in Excel because it helps us merge data from various columns, sheets, and files into one spreadsheet. However, vlookup is a lesser-known feature of Excel, typically used only by advanced users. Vlookup is needed so often and saves so much time that I’ve dedicated multiple videos to explaining this function. I hope you enjoy learning about one of Excel’s best functions.

Why should you bother learning about the vlookup function? Vlookup saves you time as you’re merging datasets together. In this video, I demonstrate how it’s painstakingly slow to copy and paste data from different spreadsheets together when one spreadsheet contains 430 rows of data and another spreadsheet contains 137 rows of data.

I walk you through the four sections of the vlookup function. Here’s what the function looks like: =vlookup(lookup_value,table_array,col_index_num,[range_lookup])And here’s what each piece of the function really means:

• lookup_value: The cell that contains the person’s ID number. The ID numbers are the link or key that connect all the spreadsheets together. The ID numbers must be located in the first column of each table – in the first column of your new combined dataset and in the first column of every single table from which you’re pulling data.
• table_array: This is the table from some other spreadsheet or other file from which you’re pulling data.
• col_index_num: This is the column in that table where you’re pulling the data from. Just type in the number of the column. For example, if you want to pull in data from Column C of another table, you’d type “3.”
• range_lookup: Always type “false” and you’ll be in good shape.

Meet hlookup, vlookup’s cousin. Use vlookup — the vertical lookup — when ID numbers are listed vertically in the first column of your spreadsheet. Use hlookup — the horizontal lookup — when ID numbers are listed horizontally across the top row of your spreadsheet.

Swap rows and columns in a table by copying and pasting your entire table at once rather than by copying and pasting one cell at a time. Copy your table, go to the Home tab, click on the Paste icon, select Paste Special, and select Transpose.