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.
- How to use Count, Countblank, and Sum
- The If formula: Example 1 and Example 2
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.
Coming Soon
- 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.
Ann, these videos are excellent! Can you do one (or a series) on pivot tables?
Sheila, Thank you! Pivot tables are coming… but not quite yet. There are lots of beginner and intermediate skills to cover first. I like to teach people the “long” way to analyze data so that they really appreciate pivot tables later on. Stay tuned, check back often, and you’ll see pivot tables by the end of the year. Thanks again.
An appreciation for Pivot Tables…
I can’t tell you how many people showed up in class saying they hated them. I used to take a giant data set (Columns A-K and some 2000+ rows), and give the class something crazy like:
I’d like you to provide me the total quantity of sales, broken down by quarter, for Robert, Jane, and Michael, in Denmark, Norway, and Sweden. Aaaaaand go…
*Blank stares*
How long would that take you?
“All day!”
I’ll teach you how to do it in less than 30 minutes, and I’ll demonstrate it in less than 1.
Pivot Tables – sold!
Great job on the blog Ann!
Ann, what a fabulous set of videos. I have watched them all, and several are new skills or new ways of working in Excel for me, so I’m finding them very helpful – even those that are “refresher” courses! Looking forward to new ones…
Pingback: Excel | Social Media and Nonprofits: Measurement | Scoop.it
Pingback: Excel | Social on the GO!!! | Scoop.it
Ann, I love how this section is growing and evolving. Your organization is excellent. So glad you’re including working with charts. I’m frequently frustrated in Excel trying to do what I want with charts. I’ve especially struggled with bar charts, changing bar colors, getting to the “vary bars by color” when there are a lot of bars, and generally manipulating the white space around charts. I’ve also struggled at times with data series labels, axis labels, and how to manipulate axis scales. Any tutorials on those topics are welcome!
Pingback: Susan Kistler on a New Excel Learning Resource Customized for Evaluators · AEA365
Pingback: Excel for Evaluation | Emery Evaluation | ResearChatCayuga
LOVE LOVE LOVE! Thanks for this great resource!
VERY HELPUL–NEW THINGS AND REVIEW!
Pingback: Chart Changin’ Cha-Cha « Evaluspheric Perceptions
Pingback: Dataviz Copycat Challenge! | Emery Evaluation
Pingback: Maggie Miller on Follow up to Small is Beautiful · AEA365
Wonderful, helpful, and clear! I really appreciate the short length of each – allows me to learn something specific and move on if I need to. Thank you for your generosity in sharing these!!!
Pingback: Blog-o-versary! | Emery Evaluation