Excel for Library Projects

1. Several people could not use the Xlookup() function because they are using older versions of Excel. For Session 2, I'll teach Index(Match) as an alternative to Xlookup(). Index(Match) can be more versatile than Xlookup().
   • In the Chat, Lacey Mamak explained that Vlookup() could be used instead of Xlookup(). See link to Microsoft overview.
2. I was asked to demonstrate CountIf(). We'll consider CountIf() in Session 2.
3. I was asked if we'll use tables. I will plan to use tables (and probably a data model) at Session 3 or 4. For Session 1, we learned how to use functions with cell references. When we look at tables, we'll also look at how to use structured references.
   • (Unfortunately, as I planned Sessions 3 and 4, I decided it would be too confusing if I switched from worksheets/ cell references to tables/ structured references. We looked briefly at proper tables at the end of Session 3.)

There were no questions at the end of Session 2.

After the Session, I realized my logic for counting items in the Pivot Chart section was wrong. Instead of counting titles, I should have counted barcodes. This is because there can be duplicate titles. Duplicate titles would be counted as 1 title. To count items, I should have used a unique value. The barcode would have been a better choice. See 45:40 in the video.

After Session 2, during office hours, one person followed up on conditional formatting. For Project 2, we saw how to highlight single cells if they met a condition. But what if we want to highlight the entire row if a single cell meets a condition? I didn't know how to do this, so we learned together by reference to this Youtube video.

During Session 3, I will ask if anyone wants to see anything in particular in the final session. I may not be able to address these ideas in Session 4, but I'd like to try to finish the series as helpfully as possible.

We'll focus on data matching for this session, so a better title might be, "Project 4: Data Matching for Collection Analysis." Instead of completing a report providing an overview of how journals and journal packages are used across the university, by subject, we will try a different report. During Session 3, there was interest in how to summarize Alma ER Holdings coverage data, so we'll work with holdings data. During Session 4, I won't have time to demonstrate how I summarize holdings data, but if there is interest, following this demonstration of how one might use such data, then I could offer a standalone session to show only how to prepare Alma ER Holdings data for summarization.