Importing and Merging Datasets
Datasets come in a variety of formats – from MS Access, from SPSS, or from CSV files, for example. These tutorials will help you import, export, and merge datasets so you can begin analyzing the data in Excel.
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 thevlookup 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 less-popular cousin.