The power of your Tiller Sheet really begins with the magic of Google Sheets. You have the opportunity to generate detailed reports and charts around your spending and income right out of the box. The Tiller Standard template has a few basic category reports, but our customers often create custom charts and reports in their finance worksheets.
Since you have the option to create multiple Tiller Sheets, you may find that you’d like to reuse your custom reports and charts across several sheets. Let’s say I created a pivot table and an accompanying chart to depict my monthly spending across each category. I want to copy this to a new Tiller Sheet I’ve just created, and I don’t want to have to recreate that pivot table and chart from scratch.
Luckily, Google Sheets makes it really simple to copy reports between spreadsheets. Let’s dive in. Be sure to check out the full instructional animation at the bottom of the post.
I’ve created a Monthly Spending pivot table that shows the total amounts per month for only the spending categories. Then I created a bar graph to visually depict how much I’ve spent each month for each category. I want to include the chart in the new sheet. The chart references the pivot table, so I’ll need to copy both of these tabs over to the new sheet and will copy the pivot table into the new sheet first.
Copying a tab to a different Tiller Sheet
To copy any tab between Google Sheets, right click the tab and choose “Copy to” from the menu. A dialog will open that allows me to find the destination sheet. Locate the Google Sheet where the tab should be copied and click “Select” near the bottom of the dialog. The best way to locate the destination sheet is to search for it by name. Otherwise you can copy the URL of the Tiller Sheet and paste it into the field near the bottom of the “Copy to” dialog.
I’ll update the name of the tab in the new sheet to reflect the purpose of the tab. In this case, it’s still a Monthly Spending pivot table so I’ll just remove the “copy of” part of the title. I can edit the name of the tab by double-clicking the tab.
Once I have the pivot table in place I can copy over the chart. I’ll use the same steps. Right click the Charts tab and click “Copy to” and then find the destination sheet and click “select.” The destination sheet should now appear under “Recent” from the “Copy to” dialog. For consistency, I’ll rename the Charts tab too.
Update the chart data range reference
The final step is to update the reference to the Monthly Spending pivot by selecting the data range for the chart to use. If I click on the chart a down arrow will appear in the upper right corner of the chart and I’ll click it to reveal a menu. Then I’ll click “Advanced edit” and select the “Recommendations” tab. From here I will click the grid icon that appears on the right side of the “Data Range” input area. When the “What data?” dialog appears I’ll navigate to the Monthly Spending Pivot tab and select the cells that should be included in the chart, click “ok,” and then click “update” on the Advanced Edit window for the chart. Now I have my chart and pivot in the new sheet without any rework.
Some fine tuning may be required. In this example, if the pivot table is filtering certain categories or other data, you’ll want to double check that your transactions are categorized on the Transactions tab and that the pivot table references all the cells on the Transactions tab.
The most important take-away is that any referenced data for reports or charts needs to be copied into the new sheet in the order in which they’re referenced. In this example, the chart references the pivot table, so we move the pivot table over first.