PDA Event: Excel for Library Projects Series

Series Description:  

This series of four one-hour sessions will provide library workers the opportunity to learn and apply essential Microsoft Excel skills. Each session will focus on a practical library project or set of tasks. The series will not provide a comprehensive overview of Excel functionality. Instead, we’ll focus on selected practical skills as applied to real projects. The projects will build in difficulty from session to session.

Generally speaking, we’ll progress from novice to intermediate skills, but because we’ll be working on projects, we’ll develop a mix of skills with each project. For example, in the first session, while reviewing a sample set of purchase orders to project budgets, we’ll cover some novice skills like sorting and filtering, but we’ll also use Excel functions, which can take a little bit of practice to learn. In subsequent sessions, we’ll continue using the skills we learned in earlier sessions, and we’ll add new skills. The sessions will be focused on specific projects, but the skills will be applicable to many other kinds of projects and tasks, so even if one doesn’t perform exactly this kind of library work, the skills should be useful in other contexts.

Each session will be recorded. Handouts and sample datasets will be provided so that participants can complete the projects on their own, between sessions. During each session, the example project will be demonstrated live. There will not be time for questions during the demonstrations, but after each session, Nat will stay in the same Zoom room for another hour to answer questions. Weekly office-hours will also be provided so that attendees can ask questions and get help one-on-one.

Session 1: Budget Projections

Friday, September 13, 12:00-1:00 p.m. Session, 1:00-2:00 p.m. Q&A

Let’s review and summarize a sample set of purchase orders for continuing resources to project serials budgets. For this project, we’ll format, sort, and filter our data. We’ll learn several ‘tricks’ to work more quickly in Excel. We’ll use Excel functions, sometimes also called formulas, to project inflation and to compare lists of purchase orders. We’ll use a pivot table to summarize our data.

Session 2: The Weeding Report

Friday, October 11 12:00-1:00 p.m. Session, 1:00-2:00 p.m. Q&A  

Let’s prepare a weeding report from sample physical collections data. For this project, we’ll continue learning how to use functions effectively. We’ll calculate how many years ago an item was acquired or circulated. We’ll look at a simple method to combine these calculations as a single variable we can use efficiently to identify candidates for weeding. We’ll use conditional formatting and other techniques to improve the legibility of our report.

Session 3: Data Cleaning for Collection Analysis

Friday, November 8 12:00-1:00 p.m. Session, 1:00-2:00 p.m. Q&A  

Let’s learn methods to clean up data so that it’s easier to combine with other data. For this project, we’ll focus on Excel functions for cleaning and re-structuring data. We will then combine data from more than one data source. We’ll continue building on our skills with functions so that they perform more of our work for us, more quickly. We may look at other tools that work well with Excel.

Session 4: Collection Analysis Report Production

Friday, December 13 12:00-1:00 p.m. Session, 1:00-2:00 p.m. Q&A 

During this session, we may need to take time to review the previous projects or complete any work we started in previous sessions. If we have time, we’ll continue working with the data from Project 3. Our goal will be to create a powerful report to get an overview of how journals and journal packages are used across the university, by subject. Or we might work on a different collection analysis report, depending on how the series has gone and how participants are handling what they’ve learned. We may look at other tools that work well with Excel, especially Power BI.

Presenter: 

Nat Gustafson-Sundell is a Collections Librarian and Professor at Minnesota State University Mankato. Previously, he was an Electronic Resources Librarian at Northwestern University. In his first career, he served as Treasurer (CFO) of a software company and Business Manager of a market research firm. At Mankato, Nat is a College of Business liaison and he leads journal collection development, licensing, acquisitions, and collection analysis. He collaborates with a Collection Management Technology (CMT) team on collection analysis. They have reported their leading-edge work in presentations and publications. Learn more about the CMT team's work. 

Register to attend.

Sponsored by Minitex