Excel Challenge 48: Build a Library Book Tracker

If you love tackling real-world Excel problems, this library book tracker challenge is perfect for you! It’s a great way to test your data analysis, formula-writing, and problem-solving skills.

Here’s everything you need to dive in:

1. Watch the challenge video for a complete overview of the scenario.
2. Review the instructions below to understand the tasks and guidelines.
3. Download the Excel worksheet and start crafting your solution.

Finished the challenge? Share your process with other Excel enthusiasts! Join our Slack channel to exchange ideas, ask questions, and connect with a like-minded community of learners.

The challenge 🥷

Imagine you’re the librarian responsible for organizing book loans for 100 students. Here’s the setup:

• Each student has a unique ID and can borrow up to five books at a time.
• There are 120 unique book titles, some of which have multiple copies.
• Students can borrow books for up to 21 days, with a $1 charge for each overdue day.

Your job? Use Excel to fill in all the blanks and automate the library’s tracking system!

Your tasks

1. Determine book availability

On the Data worksheet, you’ll populate the Status column to indicate whether a book is "available" or "not available."

• Use the number of copies of each title.
• Check the Transactions worksheet to see if any copies have been borrowed.

2. Track student borrowing and fines

On the Check worksheet, create a system where you can:

• Select a student ID and return their name.
• Indicate if the student has books currently on loan.
• Show if the student has any fines due for late returns.

In cell E2, create a formula to determine if the student can borrow additional books based on their current borrowing limit.

3. Book query system

Still on the Check worksheet, design a query system for:

• Returning the title of a book entered in cell G2.
• Indicating whether the book is available for borrowing.
• Displaying the earliest available date if all copies of the book are currently on loan.

4. Refer to constraints

All tasks and constraints are outlined in the Challenge worksheet, so be sure to refer back to it as needed.

The hints 🤩

There’s no one-size-fits-all solution to this challenge — so get creative! That said, don’t be surprised if you find yourself reaching for COUNTIFS or the IF function. 

You might also find it helpful to brush up on how to nest multiple formulas.

The solution ✅

Did you crack the code and tackle all the tasks? Now it’s time to see how your solution stacks up! Watch our walkthrough to discover how we approached this challenge.

Have a unique method? We’d love to see it! Excel is all about creativity and flexibility, and there’s always more than one way to solve a problem. Share your solution or even a new challenge idea with our community on Slack and connect with fellow enthusiasts.

Enjoyed this challenge? Keep the momentum going by exploring the rest of our Excel challenges or diving into the Resource Hub, which is packed with tutorials, tips, and tricks to boost your skills. For targeted learning, check out our Excel courses to master specific tools and techniques.

Keep crunching those numbers — we’ll see you in the next challenge! 👋