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 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 and drag to apply the formatting to multiple cells.
- That’s it! The format is applied to all selected cells.
- 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.
- Select the range of cells that have the format that needs to be copied.
- Click the format paste button once.
- Select the range to be pasted. (For this example, we will try to apply it to the next two rows.)
- The result is a bit less than we wanted. It worked, but only for the first row.
- 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.
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.
Source is data row:
Destination is formula row (note the equals signs):
This doesn’t work:
Source is one data row and one formula row:
Destination is the column to the right:
Result (no change):
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:
This does work:
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, firstname.lastname@example.org
Jon Harvey, email@example.com