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 ultimately start analyzing the data in Excel.
Tutorials about importing, exporting, and merging datasets include:
- How to parse data using the ‘text to columns’ feature
- When to use vlookup
- Why to use vlookup
- How to use vlookup
- Vlookup time saver #1
- Vlookup time saver #2
Or, watch the entire importing and merging playlist in YouTube.
How to parse data using the ‘text to columns’ feature
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. To learn more about CSV files, check out the Wikipedia definition here.
When to use vlookup
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 to use vlookup
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.
How to use vlookup
In this tutorial, 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.