Using helper columns in Smartsheet

Helper columns are additional columns you can add to a sheet that facilitate specific operations, such as managing, analyzing, or visualizing data. These are often powered by column formulas but can also include manual values.

They are frequently hidden during regular use or locked to prevent modifications. Helper columns don’t require updates during everyday sheet operations.

Benefits

• Simplify filters and reports by identifying rows that meet multiple or complex criteria through a checkbox, eliminating the need to define these criteria repeatedly.
• Improve readability by simplifying formulas and making data easier to understand.
• Create reusable and modular workflows.
• Enhance collaboration by providing clear insights for teams with visual cues.
• Create unique reference values for cross-sheet data lookups. 

Use helper columns efficiently as they count towards your limits on total columns, rows, cells, and references.

Common uses for helper columns 

Tracking status or progress

Use helper columns that have formulas or dropdown lists to monitor a task’s status or completion percentage. For example, by using the IF function, you can create a column that determines if a task is On Track or Delayed based on its due date and progress.

Automation triggers

Helper columns can drive automation. For example, a checkbox column might trigger an automated alert or action when checked.

This doesn’t apply for cross-sheet formulas or cell-linked values.

Date calculations

Helper columns help calculate deadlines, overdue status, or time between events. For example, you can have a column to display the number of days remaining until the due date.

Flags and alerts

You can use symbols or dropdowns to flag specific rows based on conditions. For example, you can have the OR function in a symbol column that flags tasks at risk.

Data categorization

You can add columns to group or filter data, such as assigning priority levels or tagging rows for a specific report. For example, categorize rows by department using a dropdown or formula.

Summary and roll-up data

Use helper columns to prepare data for roll-up in reports or dashboards. For example, use the  COUNT function with a Checkbox column to mark specific rows to include or exclude in filters or reports based on the hierarchy level or comparing values.

Text and data formatting

You can extract or manipulate text from other columns for custom display purposes, such as using the JOIN function to combine a task name and assignee or joining all parent tasks for a row to show its ancestry.

Conditional formatting drivers

Helper columns can contain calculated values used to apply conditional formatting rules. For example, a column with numeric values based on priority and due date proximity should be shown as green, yellow, or red fill colors.

Another use is to leverage ISEVEN or ISODD in a helper column, looking at a sequence number, to format every other row with a light fill to enable easy data tracking across a wide sheet. 

Tips for working with helper columns

• Use column formulas in helper columns to ensure they’re populated when new rows are created.
• Lock your helper columns to avoid unwanted edits from collaborators.
• Label helper columns with a descriptive title. Include Helper in the title to identify them to other users and in reports and filters.
• Hide helper columns to avoid noise in your sheet and accidental edits by other collaborators.

Hidden helper columns still show in card view, exported versions, when you save as new, and dropdown options for reports and sheet filters. As a workaround, consider relocating them to the end of a sheet, locking the columns, and labeling them.