# Working in sheets

# View preferences

# Resizing columns

To widen or narrow a table column, click the right-hand-side border of its column header cell, drag it horizontally to the desired new position, and drop it there.

# Freezing columns

By default, the Issue type and Issue key columns are frozen on the left of the table. You can also freeze other columns. Hover with the cursor over the header of the relevant column, a More icon appears. Click it and in the menu that opens choose Freeze columns up to here.

To reset column freezing, click the More icon again and choose Reset column freezing, or click the icon in the status bar below the table and choose Reset column freezing.

# Hiding columns v3.4.0

To hide a column in the table of a Sheet, hover with the cursor over the header of the relevant column, a More icon appears. Click it and choose Hide column.

This is helpful, for example, when you'd like to save different column layouts in different Views. That way, you can switch between different column layouts within the same Sheet by switching Views.

To unhide columns, click the icon in the status bar below the table and choose Unhide for it or Unhide all columns.

# Redacting columns v3.4.0

To redact a column in the table of a Sheet, hover with the cursor over the header of the relevant column, a More icon appears. Click it and choose Redact column.

This is helpful, for example, when you'd like to temporarily mask specific columns that contain sensitive information, so you can take a screenshot of your Sheet or show it in a meeting.

To unredact columns, click the icon in the status bar below the table and choose Unredact for it or Unredact all columns.

# Sorting columns

To sort a Sheet by a column's values, hover with the cursor over the header of the relevant column, a Sort column icon appears. Click it once to sort in ascending order, a second time for descending order, and a third time to clear the sorting.

You can also reset column sorting by clicking the icon in the status bar below the table and choosing Clear column sorting.

Pro tip

If you are looking to sort the Issues in your Sheet by multiple Fields, use an ORDER BY clause in the JQL query (Jira Query Language (opens new window)) of your sheet scope. You can find the syntax here in Atlassian’s documentation (opens new window).

# Filtering columns

You can filter the contents of a Sheet by defining field values in one or multiple columns. Hover with the cursor over the header of the relevant column, a Filter column icon appears. Click it and in the menu that opens type in a text, pick one or multiple field values, or select a number, date or time range to filter by.

Filter column

At the top of many columns' filtering menus, you can switch between different filter styles, such as Option filter, Text filter, Number filter, Date filter, Date and time filter, Time filter.

The search field in Text filters optionally accepts logical operators and regular expressions. See Using logical operators and Using regular expressions for more details.

If Hierarchy or Structure are enabled, you can choose whether ancestor and descendant issues of column filtering and table search matches should stay visible. Click the Filter levels icon above the row handles to the left of the table. In the Column filtering and table search section of the dialog that opens, in the Results menu, you can choose between Show matches only (default), Show matches and ancestor issues, Show matches and descendant issues or Show matches, ancestors, descendants. v3.3.0

To clear filtering, click the Filter column icon again and choose Clear, or click the icon in the status bar below the table and choose Clear column filtering.

Pro tip

If you are unsure if a Sheet is currently filtered, have a look at the status bar at the bottom right corner of the page. Icons are indicating whether you have applied any view preferences. If so, then you can remove them all at once by clicking for example on the Column filtering icon in the status bar and choosing Clear all column filters.

# Filtering levels v1.2.7

To generally hide issues without children or parents in a Hierarchy or Structure, click the Filter levels icon above the row handles to the left of the table.

In the Level filtering section of the dialog that opens, in the Issues without children menu, you can choose Hide top level issues or Hide parent level issues. v1.2.7

In the Issues without parents menu, you can choose Hide child level issues or Hide bottom level issues. v2.5.1

Parent levels are all Levels above the lowest Level. Child levels are all Levels below the top-most Level. Top level means only the top-most Level. Bottom level means only the lowest Level. E.g. in a Structure with five Levels, counting from the lowest up, Parent levels would be 2, 3, 4, 5, Child levels would be 1, 2, 3, 4, Top level is only 5, and Bottom level is only 1.

To only show issues that satisfy criteria of the enabled Hierarchy or Structure, activate the Hide issues outside levels checkbox. This hides Issues that would otherwise get listed in "Other level X issues" groupings at the bottom of the table or Levels, as they are not part of the tree. v2.5.1

To reset this preference to its default state, click the icon above the row handles again and choose Show all or Reset, or click the icon in the status bar below the table and choose Reset level filtering.

You can filter the contents of a Sheet using the search field above the table at the right of the page.

The table search optionally accepts logical operators and regular expressions. See Using logical operators and Using regular expressions for more details.

If Hierarchy or Structure are enabled, you can choose whether ancestor and descendant issues of column filtering and table search matches should stay visible. Click the Filter levels icon above the row handles to the left of the table. In the Column filtering and table search section of the dialog that opens, in the Results menu, you can choose between Show matches only (default), Show matches and ancestor issues, Show matches and descendant issues or Show matches, ancestors, descendants. v3.3.0

To clear the table search, empty the search field or click the icon in the status bar below the table and choose Clear table search.

# Level-specific search and filtering v1.2.7

JXL for Jira Data Center and Server v1.2.7 − v.3.2.0

To exclude Levels of a Hierarchy or Structure from your Column filtering, click the icon above the row handles to the left of the table. In the Column filtering preferences section of the dialog that opens, find the Levels menu.

You can choose Only filter top level or Only filter parent levels. This is helpful, for example, when you need to exclude sub-task issues from column filtering. v1.2.7

You can also choose Only filter child levels or Only filter bottom level. This is helpful, for example, when you need to exclude higher-level issues from column filtering. v1.5.6

To exclude Levels of a Hierarchy or Structure from your Column filtering or Table search, click the Choose levels icon in a column filtering menu or in the table search field.

You can choose between Apply to all levels (default), Apply to top level, Apply to parent levels, Apply to child levels, and Apply to bottom level. This is helpful, for example, when you need to exclude sub-task issues or higher-level issues from column filtering or search.

Parent levels are all Levels above the lowest Level. Child levels are all Levels below the top-most Level. Top level means only the top-most Level. Bottom level means only the lowest Level. E.g. in a Structure with five Levels, counting from the lowest up, Parent levels would be 2, 3, 4, 5, Child levels would be 1, 2, 3, 4, Top level is only 5, and Bottom level is only 1.

To reset this preference to its default state, click the Choose levels icon again and choose Apply to all levels.

# Using logical operators v1.2.3

To enter a logical statement in a text search, make sure the icon in the search field is activated. You can use the following syntax and keywords in text column filtering, the table search field, and text conditions of conditional formatting.

Logical operators

Disjunction OR

Matches will include at least one of the expressions.

term1 OR term2

This is also the default connective used if you don't type any operator. I.e. it is equivalent to:

term1 term2

Conjunction AND

Matches will include all of the expressions.

term1 AND term2

The precedence of the AND operator is higher than that of the OR operator.

Negation NOT

Matches will not include the expression.

term1 AND NOT term2

Empty values EMPTY v1.5.3

Matches empty cells.

EMPTY

Also usable in a disjunction.

term1 OR EMPTY

If you'd like all Issues returned that have any value set, use EMPTY in a negation.

NOT EMPTY

Grouping ( )

Using round parentheses you can group expressions to control operators' precedence in the order of evaluation.

(term1 OR term2) AND term3

Phrasing and escaping " "

By surrounding expressions with quotation marks you can search for exact phrases.

term1 "term2 term3 term4"

If the phrase you're searching for includes double quotation marks, surround it with single quotation marks.

'term1 term2 "term3 term4"'

If it includes single quotation marks, surround it with double quotation marks.

"term1 term2 'term3 term4'"

If it includes reserved words such as EMPTY or NULL, also surround it with quotation marks.

"phrase with terms empty or null"

# Using regular expressions v3.1.0

To enter a regular expression (opens new window) in a text search, make sure the icon in the search field is activated. You can use regular expressions in text column filtering, the table search field, and text conditions of conditional formatting.

# Working with issues

# Editing cells

You can view the Field values and other data of your Issues in your Sheets' table cells. To change them, simply double-click on any cell and edit away, like you are used to from products like Microsoft Excel or Google Sheets. Depending on the cell’s data, you can either edit the content directly in the table cell, or in a dropdown menu or dialog that appears.

Edit cell

Screen configuration

While you can add any column to any Sheet in JXL, many table cells will only be available and editable for individual Issue types if you have configured Jira accordingly.

You need to have the appropriate Field added to the Issue type’s Screen in your Jira settings, specifically to the Edit operation Screen, or in absence of that to the Default Screen (Manage issue screens (opens new window)). The Field must also not be hidden in Field configurations. In Team-managed projects in Jira Cloud, the Field has to be added to the Issue type in Project settings (Customize an issue type's fields (opens new window)).

Generally, in order to be permitted to edit Fields of an Issue while it’s on a particular Status, its Workflow properties need to allow for it (Use workflow properties (opens new window)).

If a cell (i.e. the Field or data it represents) is not available, the cell appears with diagonal stripes and is uneditable. This can have various reasons, e.g.:

  • The data is not applicable for the Issue type (e.g. Sub-tasks cell of a sub-task issue).
  • The Field is generally not available in the Project type (i.e. Components cell of an Issue from a Team-managed project in Jira Cloud).
  • You don’t have sufficient Permissions to view the Field.
  • The relevant feature is not enabled in your Jira site.

If a cell (i.e. the Field or data it represents) is not editable, its content appears light grey when selected, and is uneditable. This can have various reasons, e.g.:

  • The Field is generally not editable (e.g. the Created date or Resolution of an Issue).
  • The appropriate Field is not on the Issue type’s Screen.
  • The appropriate Field is not added to the Issue type (Team-managed projects in Jira Cloud).
  • You don’t have sufficient Permissions (e.g. for editing, assigning, closing, transitioning Issues, or managing Sprints).
  • Editing the Field type is not yet supported by JXL. In this case, feel free to request this feature with us and we’ll look into it. (Supported fields)

You can undo and redo changes with the usual Keyboard commands.

# Copying and pasting values

Start by selecting the cell or multiple cells of which you want to copy the values. You can then copy and paste the selected cells' values with the usual Keyboard commands.

Copy and paste cells

Keep in mind

The values of different Jira Field types are often incompatible. Examples: You cannot copy the value of an Assignee cell and paste it into an Issue type cell. You can, however, in most circumstances copy the value of a Reporter cell and paste it into an Assignee cell, as both have the same or similar enough Field type.

JXL will let you know with a friendly message in case any of your pastes were unsuccessful due to the destination Field/s not accepting the value/s. This can have various reasons, e.g.:

  • The pasted content is incompatible with the destination Field type.
  • The pasted content is compatible but mismatches all the destination Field’s available options.
  • The pasted content is an unallowed value (e.g. non-existent Transitions between specific Statuses of the Workflow)
  • You don’t have sufficient Permissions to update the destination Field/s.

You can undo and redo changes with the usual Keyboard commands.

# Bulk editing issues

As described above, with the right sheet scope, and by selecting, copying and pasting hundreds or thousands of cells you can get almost any bulk change done.

If you need to take any action or alter any data that aren't represented by Fields you can always just initiate the Jira native bulk editing tool. Select the rows (or just cells of rows) of the Issues you'd like to change, right-click in the selection to open the cell context menu, and choose Bulk edit. The Jira bulk editing tool opens pre-populated with the Issues from your Sheet.

Context menu

# Creating issues

To create an Issue in your Sheet, hover with the cursor over the row handles on the left of the table, between the two rows where you want to create the new issue row (i.e. over a row border), and click when the Create icon appears. In the Create issue row that appears next, enter a Summary, edit Issue type and Project if needed, and click the Create button or press ⌘ Cmd ↲ Enter (macOS) or ⌃ Ctrl ↲ Enter (Windows, Linux, etc.).

Create issue

Keep in mind

A new Issue will appear at the position in the table where you created it. However, that position might be temporary. When you re-apply any view preferences (i.e. column sorting, filtering and table searching) that you might have set, the Issue might get listed at a different position in the table. When you refresh the Sheet, the Issue might disappear entirely, if it doesn't satisfy the criteria of the sheet scope.

Screen configuration

When creating an Issue within a Grouping or Hierarchy Level, it will be created with the relevant field values set. E.g. when creating an Issue within an Assignee Grouping, it will get assigned to the User of the Grouping it is being created in. An attempt to create an Issue will fail if you have not configured Jira accordingly.

You need to have the appropriate Fields (in the before-mentioned example, the Assignee Field) added to the Issue type’s Screen in your Jira settings, specifically to the Create operation Screen, or in absence of that to the Default Screen (Manage issue screens (opens new window)). The Field must also not be hidden in Field configurations. In Team-managed projects in Jira Cloud, the Field has to be added to the Issue type in Project settings (Customize an issue type's fields (opens new window)).

# Cloning issues

Keep in mind

This feature is available in JXL for Jira Cloud only.

For the purpose of cloning issues (opens new window), JXL is integrated with the Deep Clone for Jira (opens new window) app. If you have the app installed, you can select the rows (or just cells of rows) of the Issues you'd like to clone, right-click in the selection to open the cell context menu, and choose Clone or Bulk clone. A Deep Clone for Jira dialog opens pre-populated with the Issues from your Sheet.

# Duplicating issues

Stay tuned

This feature is not released yet.

# Deleting issues

Stay tuned

This feature is not released yet.

Updated: 4 Oct 2024