This session is designed for participants who:
- already understand general research terminology (e.g., mean vs. median);
- learned how to conduct statistical analyses in a software program like SPSS or SAS during their formal university training; and
- need to transition their research terminology and prior use of SPSS or SAS into Microsoft Excel.
Very few employers provide their staff with tools like SPSS or SAS. It’s often necessary to learn how to adapt to Excel’s formulas and functions. This workshop is designed to make that transition seamless.
After this session, participants will be able to:
- merge data from multiple sheets and files into a single master spreadsheet;
- organize spreadsheets by adding filters and freezing panes;
- clean and recode messy data to get it ready for analysis;
- run basic descriptive statistics and frequencies; and
- explore data more fully through pivot tables.
Rather than bore you with lecture after lecture, we pause frequently for hands-on activities so that attendees have opportunities to practice their new skills immediately.
Each section begins with a demonstration followed by a break for hands-on practice. For example, in the Navigate Spreadsheets section, I demonstrate how to create, move, re-name, color-code, password-protect, and hide sheets, and then participants practice this skill using one of the sample datasets provided. The workshop is 20 percent demonstration and 80 percent hands-on practice.Due to the highly interactive nature of this session, every participant must bring a laptop loaded with Microsoft Excel. No tablets or smart phones. PCs preferred; Macs okay.
Attendees will receive:
- Excel files with practice datasets;
- a detailed handout that lists the formulas and functions covered during the session; and
- a license for the Great Graphs ebook. An entire chapter is devoted to data analysis skills.
Sample Workshop Agenda
Here’s a sample workshop agenda so you can get a sense of which topics and activities we might cover in your session. Every workshop is customized. Your agenda might look a little different.Step 1: Import and Merge Datasets:
Step 2: Navigate Spreadsheets:
Datasets come in a variety of formats – from MS Access, from SPSS, or from CSV files. The first step in the data analysis process is to import, export, and merge multiple datasets together into a single master dataset.
- Combine two or more spreadsheets into a single spreadsheet (many to one): =vlookup() and =hlookup().
- Parse data from a single cell into multiple cells (one to many): Text to columns.
Step 3: Clean Data and Recode Variables:
When you’re staring at a spreadsheet for hours on end, a little organization can go a long way. To stay organized, I create new sheets, freeze panes, insert filters, and set up Excel Tables
- Organize sheets: create, copy, move, re-name, color-code, and password-protect.
- Freeze panes so that the top row stays in view even as your scroll.
- Sort and filter your variables.
Step 4: Explore Preliminary Patterns:
Before you can analyze your data, you’ll need to check for missing data and recode some variables.
- Highlight duplicate entries.
- Assess missing data with =counta(), =countblank(), and =sum().
- Deal with dates: =month(), =day(), =year().
- Deal with text: =left(), =mid(), =right().
- Deal with names: =lower(), =upper(), =proper().
- Transpose data (swap the rows and columns of a table).
- Recode variables with =if() and =ifs().
Step 5: Calculate Summary Statistics with Formulas and with Pivot Tables:
Quickly explore your data and find initial patterns.
- Data bars.
- Color scales.
- Top/bottom color-coding rules.
- Spark lines and spark bars.
your data by finding the mean, median, standard deviation, and other descriptive statistics. We’ll cover one of my favorite features of Excel—pivot tables. Pivot tables are the fastest, most accurate way to summarize your data – and they’re easier than you think. You can use pivot tables to summarize anything from demographic information to satisfaction survey responses.
- Generate summary statistics on ratio or interval data: =min(), =max(), =average(), =median(), =mode(), and =stdev().
- Generate summary statistics onnominal or ordinal data: Calculate frequencies with =countif(), countifs(), =sumif(), and =sumifs().
- Fast and easy number-crunching with pivot tables: Prerequisites for pivot tables, inserting pivot tables, dragging-and-dropping variables, refreshing data, exploring missing data, grouping, and using filters.