University of Chicago | Center for Research Informatics | REDCap Excel Supplement
Introduction
This guide describes how to perform some basic data manipulation tasks in Microsoft Excel. Excel is
spreadsheet software that is used to store information in columns and rows, which can then be organized
and/or processed. Excel is a powerful program with an intuitive user interface, and can be a great option for
cleaning, manipulating, and organizing data.
In addition to its data manipulation tasks, Excel provides a number of standard statistical and graphing
procedures. However, these should be approached with caution, as statisticians have found numerous errors
in Excel’s statistical routines and distributions. Moreover, in recent years, professional statistical packages
such as SPSS (a.k.a. PASW) and Stata have developed easy-to-use, point-and-click interfaces, complete with
drop-down menus and dialogue boxes, making them easier to use for those not familiar with the command-line
interface. For these reasons, we do not recommend using Excel for statistical analysis, beyond very basic
descriptive statistics and getting a feel for your data. If you choose to enter and clean your data initially in
Excel, we recommend transferring it to another program, such as Stata or SPSS, before conducting analyses.
This guide has been produced to help you understand some of the basic data manipulation tasks in Excel.
However, general technical support for Excel is NOT provided by the CRI. It is hoped that this guide will
help you understand the program enough to allow you to diagnose and troubleshoot whatever difficulty you are
having. Excel’s Help section, a targeted web search, as well as fellow colleagues are all excellent resources to
aide you in this task. Do not underestimate the information available on the web to help solve your problem.
Table of Contents
Formulas...............................................................................................................................................................3
Sorting..................................................................................................................................................................4
Filtering.................................................................................................................................................................6
Conditional Statements……………………….........................................................................................................9
Text to Columns…………....................................................................................................................................12
Paste Special......................................................................................................................................................15
Transposing Data......……..................................................................................................................................18
Lookup Functions......……...................................................................................................................................19
Duplicate Records..............................................................................................................................................21
Find and Replace................................................................................................................................................22
Combine Data.....................................................................................................................................................23
Conditional Formatting........................................................................................................................................25
Text Functions…….............................................................................................................................................27