Login Try Free

Row/Column Reports · Track 2 · Tutorial 6 of 6

Keep row/column reports maintainable

A report built once should stay right as accounts are added and rows are renamed. A few habits do most of the work: let sections and tags find the accounts, rename rows in the editor, and never delete a row a formula still uses.

You will needThe Management P&L from the Build row/column reports track, with its Revenue, Direct costs, Gross profit and Gross margin rows.
By the endA Management P&L whose rows pick up new accounts by themselves, whose formulas follow a renamed row, and whose layout comes from a shared column set.

1Let sections and tags find the accounts

  1. Open the Management P&L in the editor and look for Account rows that are meant to cover a group of accounts.
  2. Replace each with a Section row, or with an account row that reads a tag, as in Read accounts by accounting-standard tag.

A section or tag row reads every account in it, so an account added to the chart next month appears in the report with no change. An Account row reads that one account only; keep it for a line that really is one account, such as Selling and marketing.

2Rename rows in the editor

  1. Click the Direct costs row, change its Row label to Cost of sales, and click Update Row.
  2. Click the Gross profit row and read its Working Formula.
The report worksheet with row 6 renamed from Direct costs to Cost of sales; the Gross profit formula's working formula now reads 'REVENUE' − 'Cost of sales'; and the message shown when a row a formula uses has been deleted: Unknown row reference: Cost of sales. Choose a row from the formula picker.
The renamed row (1), the formula following it (2), and the message for a row that no longer exists (3).

The formula now reads 'REVENUE' − 'Cost of sales': renaming a row in the editor carries the new name into every formula that uses it. A section shown with its accounts is used by its section name, such as REVENUE, and its Row label can’t be changed.

3Check formulas before you delete a row

  1. Before deleting a row, click each formula row and look for the row’s name in the Working Formula.
  2. If a formula uses it and you only want it out of sight, click it and turn on Hide under Display instead.

Deleting a row a formula uses asks you to confirm first, naming the formula rows that read it. If you delete it anyway, Save is refused with Invalid Formula until no formula uses it. When you edit such a formula you see Unknown row reference, with the row’s name. A hidden row is left out of the report, and formulas can still use it.

4Share layouts through column sets

  1. Give reports that should look alike the same column set, such as Month vs budget, as their Period.

Change the set once under Reports → Column Sets, and every report that uses it changes with it. A layout copied into each report by hand has to be changed in each one.

5Check the result

  1. Click Save, then View.

Check

The row reads Cost of sales, and Gross profit is still Revenue less Cost of sales in every column. No formula row shows 0 where it showed a figure before.

6If something goes wrong

What you seeWhat to do
Invalid Formula when you saveA formula still uses a deleted row. Click the formula, remove the missing part from the Working Formula, and choose the row again from the formula picker.
Unknown row reference when you update a formulaThe named row no longer exists. Choose a row from the formula picker.
A new account is missing from the reportIt is not in any section or tag the report reads. Add it to the right section or tag, or change an Account row to a Section row.
A ratio row shows 0 for one monthThe row it divides by is 0 that month. The report shows 0 rather than an error.

Want more detail? See the documentation

Customizing Reports Custom Columns
All tutorials