Challenge: Library Book List Management

You are creating a table to manage books in a library. You need to filter and sort books by various parameters, use conditional formatting to highlight relevant data, remove duplicates, and organize the data for ease of use.

Assignment:

1. Create a table titled "Library Book List" with the following columns:

   • Book Title;
   • Author;
   • Genre;
   • Year of Publication;
   • Availability (e.g., "Available," "Checked Out");

   Enter 10–15 books published from 1980 onward. Add a few duplicates to demonstrate the use of the remove duplicates function.

2. Use the "Find and Replace" function:

   • Find all books of a specific genre (e.g., "Science Fiction") and replace the genre name with a more general term, such as "Sci-Fi.";

   (Press Ctrl + H (Windows) or Cmd + H (Mac), enter "Science Fiction" in the "Find" field, "Sci-Fi" in the "Replace" field, and click Replace all to make the changes.)

3. Sort the list by year of publication:

   • Sort the table by the "Year of Publication" column from the oldest to the newest;

   (Click on the column letter of "Year of Publication," go to Data > Sort sheet by column D, A → Z to sort the books from oldest to newest.)

4. Apply data filtering:

   • Apply a filter to show only books that are available for checkout, i.e., those marked as "Available" in the "Availability" column;
   • Try using Custom Filter Views to create different filters for different users (e.g., one filter for all available books, another for books published after 2000);

   (Select the entire table, click on Data > Create a filter, then click the dropdown in the "Availability" column and select "Available." To create custom filter views, go to Data > Filter views > Create new filter view.)

5. Apply conditional formatting:

   • In the "Year of Publication" column, highlight books published before 1990 using color formatting. For example, use red for older books so that they stand out;

   (Select the "Year of Publication" column, go to Format > Conditional formatting, select "Less than", enter "1990" and choose a red color for the formatting.)

6. Remove duplicates:

   • If there are duplicate entries for books (e.g., multiple copies of the same book), use the Remove Duplicates tool to delete duplicate rows and leave only unique records;

   (Select the entire range of data, go to Data > Data cleanup > Remove duplicates, check the columns to check for duplicates, and click Remove duplicates.)

7. Add slicers:

   • Use slicers to filter by genres or years of publication, allowing users to quickly select books by these categories;

   (Select your data, go to Data > Slicer, choose the column to filter by (e.g., Genre or Year of Publication), and click OK to create the slicer. Use the slicer options to filter the data.)