Give every board the same Excel columns

Every deal team sends the client a CP checklist in Excel, and every team builds the custom Excel export on its board by hand: which columns, in which order, which ones Excel should treat as amounts and dates. As an organisation admin, or in practice support, you define the layout once as an Excel export template. Teams apply it to their board in one click, and a board that has never set up its custom export starts from the firm's default.

The question that decides the columns: what does the reader need to sort, filter or add up?

Create the template

1. Open Admin panel from your user menu, and under Templates choose Excel export.

2. Under Custom Excel export templates, click New template.

   

3. In New Excel export template, type a Template name the teams will recognise, such as CP checklist for the client. Teams pick the template by this name.

4. The template starts with three columns: No (the row number), Item (the task title) and Amount (a text column on the board, cast as a number). Change them, delete the ones you do not need with the bin, and click Add column for more. Drag a column by its handle to move it.

   

Set up each column

5. Type the heading the reader sees in the column's name field, at the top of its card.

6. Under Source, choose where the values come from. Row number, Group name and Task title work on every board. Board column reads a column of the board: choose its Column type and type its Column title as the boards call it, such as Deadline or Responsible. A board's column is matched by its type and its title (capitals aside), so a column called Due date on the board does not match a template column titled Deadline.

7. Under Cast as, tell Excel what the values are: Number, Currency, Percent, Date, Yes/No or Text, or leave Auto. For numbers, amounts, percentages and dates, type an Excel format in Number format, or pick one from Common formats in the info icon beside it, such as an amount in kroner. A purchase price kept in a text column only adds up in Excel when it is cast as Number or Currency.

8. Set the Width and Alignment if the default does not suit the column. On the row number column, choose the Numbering: Board index (1.2.3) to match the numbers on the board, Sequential (1, 2, 3), Roman (i, ii, iii) or Letters (a, b, c).

   Sheet column	Source	Column type	Cast as
   No.	Row number
   Document	Task title
   Workstream	Group name
   Responsible	Board column	people	Auto
   Deadline	Board column	date	Date
   Purchase price	Board column	text	Currency

Set up the sheet and save

9. Under Sheet options, choose Include subtasks to put subtasks on their own rows, Add auto-filter to give every heading a filter button, and Freeze header row to keep the headings in view while the reader scrolls.

10. Check the Live preview. It fills the columns with sample data in the firm's Excel styling, so you see how each cast comes out. Click Save.

11. The first template the firm creates becomes the default. To make another one the default, click the star (Set as default) on it. The default is marked Default.

How the teams get it

• Boards without a custom export of their own. The first time someone opens the custom Excel export on the board (Board, Export, then Custom under Excel), the default template is applied for them.
• Boards with their own layout keep it. To switch, the team clicks Apply template in Custom Excel export and chooses the template under Organisation templates.
• Columns the board does not have. If a template column finds no match, Some columns could not be matched opens. The team picks the board's column for it, or Skip this column, and clicks Apply.

To change a template, click the pencil beside it. Deleting a template with the bin does not change any board: boards that use it keep their saved layout, but it is no longer offered. If it was the default, the next template in alphabetical order becomes the default.

Tips from practice

• Agree the column titles first. Matching works on the title, so the template only fills itself on boards that use the same names. Build the firm's board templates with the same titles (see build the firm's deal checklist template).
• One template per reader. A client's CP checklist and the team's internal tracker need different columns. Make one template for each and name them for the reader.
• Leave the internal columns out. A template is a good place to make sure internal notes and comments never reach the client's version.

The look of the workbook, its fonts, colours and logo, comes from the template above it on the same page: see make Excel exports look like the firm's. The export itself, from the team's side, is in hand the checklist over in Excel.