Anyone and everyone can learn how to analyze their own evaluation data in Excel. I created these Excel for Evaluation video tutorials to share my favorite tips, tricks, and techniques with beginner-level and intermediate-level Excel users like you. Each video is 1 to 4 minutes long, so you can watch a few videos while eating lunch. You’ll be a data nerd in no time!
Data analysis is a process that consists of several steps. You need to teach yourself how to import your dataset into Excel, organize your spreadsheet, clean and recode data, look for patterns, calculate key statistics, and then show off the most important information in graphs and charts. I invite you to follow my tutorials to boost your skills at each stage of the data analysis process.
The stages of the data analysis process include:
Importing and Merging Datasets: The first step in data analysis is to import your dataset into Excel. In these videos, I teach you how to import, export, and merge datasets.
- 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
Housekeeping: Learn how to organize your datasheet by creating, editing, and protecting sheets; freezing panes; and using filters.
Cleaning and Recoding: Before you can analyze your data, you’ll need to check for missing data and recode some variables.
Exploring the Data: Learn how to use conditional formatting to quickly explore your data and find initial patterns.
- Min and Max
- Mean, Median, and Standard Deviation
- How to calculate frequencies using countif: Example 1 and Example 2
Pivot Tables: You can use pivot tables to summarize anything from demographic information to satisfaction survey responses.
- Inserting a pivot table
- Understanding basic pivot table components and terminology
- Using pivot tables to create crosstabs
- Using pivot tables to check for miscreant data (guest post by Oz du Soleil)
- How to insert a chart
- How to change the chart type
- How to move a chart to its own sheet
- How to create data labels in charts
- How to remove tick marks and grid lines
- How to create stacked bar charts
- How to reverse the order of your categories in your charts
- How to fix funny-looking line charts with ‘Switch Row/Column’
- How to adjust the units in your chart’s axes
Extras: These extra tricks are the icing on the cake. Don’t underestimate the power of these time-saving formulas.
- How to use the & operator
- How to use the month, day, and year functions
- How to use the today and now functions
- How to generate random numbers using randbetween
- How to use the lower, upper, and proper functions
- How to use the left, mid, and right functions
- How to transpose data
- How to find out which formulas are available in Excel
Putting It All Together: These videos combine techniques shown in earlier videos so you can learn how to apply your new skills to a variety of datasets.
- Proper and &
- Satisfaction Survey Analysis and Data Tables
- Satisfaction Survey Data Tables: Troubleshooting
- Automating Dashboards with Word and Excel
- Automatic data tables in Excel using =countifs() and drop-down menus
- Automatic snapshots in Excel using nested IF arrays and drop-down menus
- Easy appendices for open-ended survey data
Want to request a tutorial on a specific skill? Comment below or email me.