Simple Spreadsheets

Ever feel like you’re swimming upstream in data? Need to make sense of spreadsheets, but not sure where to start? Have a gut feeling that you’re not getting the most out of spreadsheet programs like Microsoft Excel, Numbers, or Google Sheets?

During this session, we’ll focus on little-known spreadsheet secrets that save you time and energy. We’ll cover beginner, intermediate, and advanced number-crunching skills from vlookup to pivot tables. You’ll be armed and ready to organize your spreadsheets by adding filters and freezing panes; clean and recode messy data to get it ready for analysis; and explore your data more fully through pivot tables.

Target Audience

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.

Learning Objectives

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.

Hands-On Activities

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.

Materials Provided

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:
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 2: Navigate Spreadsheets:
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 3: Clean Data and Recode Variables:
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 4: Explore Preliminary Patterns:
Quickly explore your data and find initial patterns.
  • Data bars.
  • Color scales.
  • Top/bottom color-coding rules.
  • Spark lines and spark bars.

Step 5: Calculate Summary Statistics with Formulas and with Pivot Tables:
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.