In this page
Important note on non-English Excel versions
Better Excel Exporter fully supports any Excel language version, but the function names, decimal and list separators depend on the language. Remember to read the related FAQ item, and adapt the code examples in this article correspondingly.
Formulas
You can use any Excel formula in your spreadsheet templates. Most Excel formulas also utilize Excel functions, thus we will use both in the examples in this section.
In the templates, formulas must be enclosed in square brackets:
| $[100 + SUM(A2)] |
Transforming formulas
A single template cell is frequently transformed into multiple cells during an export. This happens, for instance, when exporting multiple issues, each issue in its own row. The good news is that formulas will be automatically transformed to correspond this.
When you export 3 issues as in our previous example, the A2 template cell is transformed into A2, A3 and A4, thus the SUM formula automatically becoming:
| =100 + SUM(A2:A4) |
When you open the resulted Excel spreadsheet, the formula will be evaluated immediately, as expected.
Functions
As written above, you can use any Excel function in your spreadsheet templates. Functions are typically used with formulas, so please see the previous section for some introductory information.
Frequently used Excel functions
For the comprehensive list of Excel functions, please read the complete list of Excel functions article in the official Excel help.
In this section, some examples of the most frequently used functions are shown. The primary aim is to give you some ideas how to use them in Jira's context.
| Function name | What it does | Use it to |
|---|---|---|
| COUNTA() | Counts how many values are in the list of arguments. | Calculate the number of issues in a list. Typically used to define dynamic ranges. |
| SUM(...) | Adds its arguments. | Calculate the total of numeric Jira fields, like the total time spent, total story points, or the total of a "number" type custom field. |
| AVERAGE(...) | Returns the average of the arguments. | Calculate the average of numeric values, like average time in status, average number of issues created in a week, or the average of a "number" type custom field. |
| YEAR(...) | Converts a number to a year value. | Extract the year from Jira dates (ex: issue creation or resolution dates) for pivot charts. |
| MONTH(...) | Converts a number to a month value. | Extract the month from Jira dates (ex: issue creation or resolution dates) for pivot charts. |
| DAY(...) | Converts a number to a day value. | Extract the day from Jira dates (ex: issue creation or resolution dates) for pivot charts. |
| IF(...) | Specifies a logical test to perform. | Implement conditional cells like "support ticket is critical if the customer is Acme Corporation". |
| OR(...) | Logical OR function. | Implement conditional cells like "SLA is successful if status is Closed or priority is Lowest". |
| AND(...) | Logical AND functions. | Implement conditional cells like "SLA is successful if status is Closed and time spent is less than 8 hours". |
| NOT(...) | Logical NOT function. | Implement conditional cells like "SLA is successful if resolution is empty". |
Next step
Use tags for smarter issue iterations, to render hyperlinks, cell comments and more.
Questions?
Ask us any time.