Excel resizing

When trying to resize tags in Excel, Templater will choose the best possible range which includes all tags.
Templater has specialized handling for various Excel features, such as:

  • tables
  • named ranges
  • merge cells
  • charts, graphs and pie charts
  • anchors/links
  • formulas
  • pivots
  • images
  • conditional formatting
  • PowerQuery/Get & Transform
  • newlines, shapes, ...

If you have tags on multiple sheets this means that Templater will choose all sheets between first and last one. This is useful for reports which generate mostly the same content but have different first and/or last page.

Page resizing can be invoked in several more ways. If all tags are on single sheet and you want to resize that entire sheet, you can place a single tag in header, or use sheet metadata on one of tags.
For example, if your object has ID property, you can resize entire sheet with [[ID]:sheet] tag.
If tag is found in the sheet name (in which case {{TAG}} format must be used) this will indicate that full sheet resize must be performed.

But sometimes you have nested collection – for example, simple invoice object has items collection, but if we want to process multiple invoices at once, then we probably want table for each items per invoice and some bigger range for invoice data.
Named range enables us to specify how much room we want per single invoice. Ranges can be nested, so very complicated reports can be created this way.
If Templater can't find table or named range, it will calculated maximum range which encapsulates all tags and use this range as context.

Merge cells, named ranges and tables influence the originally selected tag range.
Since Templater will avoid breaking them during resize, they expand the area which is resized.

If same tag is repeated multiple times in a single sheet, both will be included in the resize context.

Builtin special metadata - fixed - requires the use of tables to behave correctly (as expected).

Comments will be duplicated on resize (or removed on resize 0).

Tags can be repeated in different sheets, in which case resizing will behave as if each sheet contains a separate collection which needs to be resized.

Special case of resizing is resizing to 0 = removing context.
Some object types can be removed while others cannot. If resize(tags, 0) is called on a table, table will still remain in the document, while the first row will look empty.
If resize(tags, 0) is called on named range, it will be removed completely (even the tables within the named range). For resize to work as expected all tags within named range must be specified, otherwise a different logic will be invoked.

During resize, row/cell styles will be maintained. This means that row height should be preserved, along with cell formattings.
Both horizontal and vertical resizing will move objects around (merge cells, formulas, named ranges, ...) but it will also rewrite formulas to reference expected cell. Since rewriting formulas has a performance penalty it's always preferable to write formulas in such a way that they don't need to be changed when they are moved around. This can be done by formula using table columns or named ranges as input source instead of referencing a specific cell.
Cell reference will be respected, so that formulas which reference cell in a fixed way will be changed differently than formulas which reference cell in a sliding way.

Templater is also aware of the PowerQuery/Get & Transform tools and will cope with tags used within them.
This allows for complex scenarios such as embedding of CSV documents with tags bound via PowerQuery to a sheet.

Template: Tags across multiple rows
Tags across multiple rows - before

Result screenshot:
Tags across multiple rows - after

Back to Features