All Collections
Reporting
Custom report templates
How to edit a custom report template in Excel
How to edit a custom report template in Excel

Find out more about what you can do to customise a Click Travel report template in Excel

Hayley Marsden avatar
Written by Hayley Marsden
Updated over a week ago

Introduction

If you have Excel skills, you can customise your own custom report template in Excel. For example, you can add your own pivot tables and change the column ordering. 

Take care to keep the RawDataRange in the RawData tab intact. Also, no macros are allowed. But please feel free to add and remove any of the sheets, except for the RawData sheet.

When you are finished, save the report template as a workbook (.xlsx format) and upload to Click Travel.

TIP

If you've not edited a Click Travel report template
in Excel before, please start off by familiarising
yourself with the template's
RawDataRange and pivot tables.

How to edit a report template in Excel

1 You cannot edit one of the templates that we provide, so begin by creating your own custom report template

2 Download the custom report template onto your computer:


3 Open the custom report template in Excel. You can:

  • Rearrange the order of the columns on the RawData sheet. But make sure you don't change the column headers in row 1.

  • Add new columns to the RawData sheet. Each new column uses the data in other columns to perform a calculation. Row 1: leave this blank. Row 2: insert a user-friendly heading. Row 3: Insert the lookup formula.

  • Delete any columns on the RawData sheet that you don't want.

  • Amend the pivot table field list from any worksheet apart from the RawData sheet.

  • Add new worksheets.

  • Use the RawDataRange to set up pivot tables, charts, formulae etc

EXAMPLE

Your team uses a custom field with ID set to
'department'. Add a pivot table on a new worksheet
named Department spend. Configure the pivot table
to calculate department spend by using the
cust_data.department
column
and the service_cost column.

IMPORTANT

  • When open in Excel, the report template is a workbook. (The report template is NOT the same as an Excel template.)

  • The workbook must have a named range called RawDataRange.

  • The row above the RawDataRange must contain the Click Travel column headers for the data you want in that column. (Look up the Travel Analysis and Travel Billing column headers. You can also use custom data column headers.)

  • The workbook CANNOT contain macros. (Macros don’t work on Click Travel.)

4 Save your changes in Excel. 

IMPORTANT

Make sure you save the report template as a
workbook
in .xlsx format (Excel 2007
onwards).

5 Upload the report template to Click Travel. 

Did this answer your question?