Financial Reporting Studio contains a button to copy and paste formatting. This is a powerful tool, but it has some limitations that make it appear less useful than it really is. This post will explore what this tool can do (and show you how to work around its limitations).

Basic Format Paste

We will start using a very basic grid with a little formatting in Column A.

Select the cell that you want to copy.

(click to enlarge)

(click to enlarge)

  1. Click the format paste button once. (Click a second time turns format paste off. Note that the button does not appear depressed or give any visual indication that it is active, but it is.)
(click to enlarge)

(click to enlarge)

  1. Click and drag to apply the formatting to multiple cells.
(click to enlarge)

(click to enlarge)

  1. That’s it! The format is applied to all selected cells.
  2. Once the format paste is used once, it is inactive. The paste format button can be clicked again to apply the format to other cells, if desired.

Format Paste with Multiple Cells

It is possible to select multiple cells and have format pasting. We’re starting by adding formatting to Row¬†1, but note that the shading in the right-most cell mean that these cells are not all identical.

(click to enlarge)

(click to enlarge)

  1. Select the range of cells that have the format that needs to be copied.
  1. Click the format paste button once.
  2. Select the range to be pasted. (For this example, we will try to apply it to the next two rows.)
(click to enlarge)

(click to enlarge)

  1. The result is a bit less than we wanted. It worked, but only for the first row.
(click to enlarge)

(click to enlarge)

  1. The format paste tool can only paste to ranges of the same size. If we select two rows now, we can paste the format to the next two rows.

(click to enlarge)

(click to enlarge)

(click to enlarge)

(click to enlarge)

Limitations of Format Paste

Limit 1: Source range must be of a single type of row/column

There are three types of rows and columns: data, formula and text. Paste format does not work if the source range includes multiple types of rows or columns. At long as the source is of the same type, you can paste to a different type.

This works:

Source is data row:

(click to enlarge)

(click to enlarge)

Destination is formula row (note the equals signs):

Result:

(click to enlarge)

(click to enlarge)

This doesn’t work:

Source is one data row and one formula row:

(click to enlarge)

(click to enlarge)

Destination is the column to the right:

(click to enlarge)

(click to enlarge)

Result (no change):

(click to enlarge)

(click to enlarge)

The row and column headers (where members are selected for the dimensions) can be considered a fourth type of row/column. These also cannot be in the source range with another type of row or column, or else the paste format will not work.

Limit 2: The cells where the row and column headers intersect cannot be used as a source (although it can be used as a destination).

This doesn’t work:

(click to enlarge)

(click to enlarge)

This does work:

(click to enlarge)

(click to enlarge)

Limit 3: Conditional formatting cannot be pasted with the format paste tool.

Questions? Need help? Feel free to reach out to us at:

Gerriet Suiter, gsuiter@ecapitaladvisors.com

Jon Harvey, jharvey@ecapitaladvisors.com