Product Updates - November 14, 2021 - Conditional Formatting

What’s new?

1. Conditional formatting

We’re excited to announce the launch of row and column-level conditional formatting in, allowing you to create rules that automatically apply styles based on data in your worksheets.

For example, in the Inventory Management workbook below we create a rule to highlight Product rows when the quantity of Stock Units falls below 10. We also create a rule to color code Unit Price cells using a gradient based on the the range of values in this column.


Conditional formatting is a great way to automatically highlight and call attention to specific information in your workbooks without having to manually apply styling.

Each worksheet can have any number of rules, which can be reordered based on priority, and temporarily disabled as needed. Conditional formatting styles override, but do not replace, manually applied styles.

For more details, see the new Conditional Formatting article in our Help Center.

2. New templates

We’ve added nine new templates to the Template Gallery, for use cases ranging from Construction to Education, Training, Facilities, Groups and Clubs.

For example, the new Construction Schedule for Basement Finish template from Vertex42 was created from a workbook that was recently used for an actual home remodeling project, including a curated list of materials from The Home Depot:


The new Facility Management template provides the foundation of a workflow automation tool for tracking assets, completing maintenance requests, and managing inventory. It also uses conditional formatting to gray out rows when a task is marked as Complete:


For school administrators and alumni relations managers, our Alumni Directory template is designed for tracking alumni information and interactions, and collecting updated alumni details using a Form view:


Other newly published templates include:

  • Employee Training (Human Resources): Training is vital for employee development, safety and productivity. This template provides a simple way to track both internal and external courses and certifications that are required and/or available to your employees.

  • Borrowed Items Tracker (Education): Teachers can use this spreadsheet to keep track of borrowed classroom items, the condition of each item, and the borrower’s treatment of the items. Views and filters make it easy to identify items that need replacement.

  • Course Scheduling (Education): Teachers and school administrators can use this spreadsheet to collaboratively plan and manage a schedule of classes for the upcoming semester. Keep track of classes, teachers, and rooms, as well as relationships between them.

  • Graduation Requirements (Education): College students, advisors, and counselors can use this spreadsheet to track and manage graduation requirements for students based on their major. Students can track requirements for a specific degree and other courses they would like to take.

  • Student Schedule (Education): Students can use this spreadsheet to track assignments and due dates along with related class information. Use views to see upcoming or overdue work, and automations to receive email reminders.

  • Team Roster and Game Schedule (Groups and Clubs): Youth sports usually involve parent volunteers and communication is vital for coordinating practices and games. This template helps manage a roster with useful contact information for sharing with parents and guardians.

Fixes and enhancements

We also fixed over 25 bugs including:

  1. 004025: Image files copied into the clipboard now paste as expected into Attachment cells when using Ctrl + V (Windows) and ⌘ + V (Mac).

  2. 004489: The Automations dialog no longer fails to load when one more more automations rely on a deleted column, as mentioned in the community.

  3. 001978: The SUBTOTAL function now ignores the results of other SUBTOTAL calculations, even when used in combination with functions like DESCENDANTCELLS, INDEX, etc., and in hierarchies, as mentioned in the community.

  4. 004520: Specifying an implicit column range such as A:A in Column Formulas now evaluates correctly.


This is great. The new Facility Management template (in the SSDC gallery) now grays out a row when a task is marked as Complete:


I like that you can also do this using a checkbox column:


And here is another example where a color scale is used for highlighting a rank column:


The second two examples are new templates that you can access from this page.