Excel for evaluation

Resource link

This website, created by Ann Emery, provides a series of short videos on using Microsoft Excel to analyze data.

The tutorials cover tips, tricks and techniques useful for beginner-level and intermediate-level users, ranging from importing data to creating charts.

Excerpt

"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." (Emery, 2012)

Contents

Importing and Merging Datasets

  • How to parse data using text to columns feature
  • Why/Why/How to use vlookup
  • Vlookup time savers

Housekeeping

  • How to create, edit, and protect sheets
  • How to freeze panes
  • How to sort data with filters

Cleaning and Recoding

  • How to use Count, Countblank, and Sum
  • The If formula: Example 1 and 2

Exploring the Data

  • How to automatically color-code your cells
  • Highlighting blank cells
  • Top/bottom rules
  • Data bars

Descriptive statistics

  • Min and Max
  • Mean, Median, and Standard Deviation
  • How to calculate frequencies using countif: Example 1 and 2

Pivot Tables

  • Inserting a pivot table
  • Understanding basic pivot table components and teminology
  • Using pivot tables to create crosstabs
  • Using pivot tables to check for miscreant data

Charts and graphs

  • 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

Advanced charts

  • How to make a bubble chart
  • How to make back-to-back charts
  • How to make sparklines
  • How to make a dot plot
  • How to make a histogram
  • How to make a bullet graph
  • How to make a box and whisker plot
  • How to make a Pareto chart

Extras

  • 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

  • Proper () and & in excel
  • Satisfaction survey analysis and data tables
  • Automating dashboards with Word and Excel

Sources

Emery, A. (2012) Excel for Evaluation. Retrieved from http://emeryevaluation.com/excel-for-evaluation/