An Old Tool with Enduring Value: Using Excel to Prepare Data for Analysis

Author(s)

Gregory A. Smith, Liberty UniversityFollow

Publication Date

2-22-2018

Document Type

Conference Presentation

Disciplines

Library and Information Science | Statistics and Probability

Comments

A presentation made at the Wrangling Library Data virtual conference organized by Amigos Library Services, 22 February 2018. Slide deck and spreadsheets demonstrated during the conference session are available here.

Abstract

Microsoft Excel was first released on the Windows platform 30 years ago and has since become widely used. Although new tools for manipulating, analyzing, and visualizing data are constantly emerging, Excel remains a potent tool—and not just because of newer features. Simple functions such as TRIM, MID, SUBSTITUTE, FIND, ROUNDDOWN, and VLOOKUP can be used to manipulate data sets in powerful ways.

This workshop applies selected functions to realistic library data sets. Demonstrations include:

• deriving time-series categories from date and time stamps
• pre-coding survey comments based on keywords
• dealing with messy data points such as call numbers and publisher names
• removing extraneous punctuation and symbols from textual data
• identifying keywords that are misspelled or on a stopword list

Spreadsheets available for download provide simple utilities for performing these tasks. For those interested in creating or modifying formulas, presentation slides illustrate the basics of using selected functions to transform data in Excel.

Recommended Citation

Smith, Gregory A., "An Old Tool with Enduring Value: Using Excel to Prepare Data for Analysis" (2018). Faculty Publications and Presentations. 168.
https://digitalcommons.liberty.edu/lib_fac_pubs/168