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)
Charts and Graphs: When used well, charts can showcase your most important findings.
First, learn how to insert basic charts and adjust features like tick marks and grid lines.
- 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
Once you understand the basic chart components, learn how to leverage Excel elbow grease to make advanced charts.
- How to make a bubble chart (link to my dataviz challenge and solutions)
- How to make back-to-back charts (link to Evergreen Data blog)
- How to make sparklines (link to Visualizing Data blog)
- How to make a dot plot (link to Visualizing Data blog)
- How to make a histogram (link to Visualizing Data blog)
- How to make a bullet graph (link to Visualizing Data blog)
- How to make a box and whisker plot (link to Visualizing Data blog)
- How to make a Pareto chart (link to Visualizing Data blog)
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.
- Importing and exporting data between Excel, SPSS, and Access
- Pasting between Excel and Word (Paste Special, transposing data)
- Charts: Sparklines, how to adjust the colors and fonts in your charts
- Descriptive statistics: Large, mode, quartiles, sumif and sumifs, sum vs. count, count vs. counta
- Pivot tables: Refreshing data, double-clicking
- Inferential statistics: Normalizing data with z scores, t-tests
Want to request a tutorial on a specific skill? Comment below or email me.