Tutorial Summary: Using the New Excel Checkboxes

Objective

This is a step-by-step tutorial and idea guide for using the new native Excel checkboxes, highlighting their ease of use, setup, integration with formulas, and advanced features such as conditional formatting and automatic timestamps.

---

Key Points

Introduction to Excel Checkboxes

• Excel now has built-in checkboxes, making previous workarounds unnecessary.
• Checkboxes can be used for much more than just simple to-do lists, like learning trackers or dynamic dashboards.

Basic Setup

1. Inserting Checkboxes

   • Found under the Insert tab in Office 365.
   • Inserted directly into cells, toggled by clicking or using the space bar.
   • Checked boxes display TRUE, unchecked FALSE in the formula bar.
   • Checkbox color can be changed by adjusting the cell's font color.
   • Removing a checkbox: Delete cell contents or use "Clear All" to remove both the value and formatting.

2. Bulk Insertion

   • Select a range and insert checkboxes for all selected cells at once.

Tracking Progress

• Example: Learning list with checkboxes for each task.
• Counting Completed Tasks:
  • Use COUNTIF(range, TRUE) to count checked (TRUE) boxes.
• Counting Total Tasks:
  • Use COUNTA(range) to count total items (including text, numbers, and TRUE/FALSE).
• Display Format:
  • Combine completed/total, e.g., "4 out of 9", using & (Ampersand), and add a line break with CHAR(10).

Displaying Progress in Shapes

• Progress can be dynamically linked to a shape (e.g., a box) for visual dashboards:
  1. Insert a shape (Insert > Illustrations > Shapes).
  2. In the formula bar, type =cell_reference to show the progress inside the shape.
  3. Hide the source cells by moving or hiding columns.

Tracking Incomplete Tasks (Bucket List)

• Use the FILTER function to get items where the checkbox is unchecked (FALSE):

  =FILTER([Items], [Checkboxes]=FALSE, "")
  

• Dynamically updates as checkboxes are checked.

Conditional Formatting with Checkboxes

1. Strike-through on Completion
   • Select the range, then Home > Conditional Formatting > New Rule.
   • Use a formula referencing the checkbox cell (ensure only the column, not the row, is fixed).
   • Apply strikethrough formatting.
2. Changing Row Color on Completion
   • Add another rule using the same logic but change font or fill color (e.g., green).

Adding Timestamp on Completion

• Use the IFS function to set a timestamp when a box is checked:

  • If checkbox is FALSE: return nothing.
  • If the timestamp cell is empty: return NOW().
  • Otherwise: return cell's existing value.
  • Important: Enable "Iterative Calculation" in Excel options for this to work, as it creates a circular reference required for this functionality.

  Example formula:

  =IFS(
    [CheckboxCell]=FALSE, "",
    [TimestampCell]="", NOW(),
    TRUE, [TimestampCell]
  )
  

  • Be cautious; iterative calculations can have side effects.

---

Summary of Techniques Learned

• Inserting and customizing native Excel checkboxes.
• Linking checkbox values to formulas for dynamic tracking.
• Using COUNTIF/COUNTA for progress stats.
• Outputting progress to shapes for better dashboards.
• Using FILTER for dynamic not-completed lists.
• Applying conditional formatting (strike-through, color) triggered by checkboxes.
• Automatically creating timestamps when tasks are marked as completed, using iterative calculation.

---

Ideas for Use Cases

• Task and project management.
• Learning trackers.
• Interactive reporting dashboards.
• Any workflow requiring quick binary toggles and dependent logic.

---

Tip: Share your use cases and subscribe for more tutorials!