Google Sheets makes it really simple to copy reports between spreadsheets.
Here we have a monthly spending pivot table that shows the total amounts per month for only the spending categories. We also have a bar graph to visually depict spending by month for each category.
Let's say we want to include the chart in a new Tiller Sheet or another Google Sheet. The chart references the pivot table, so we'll need to copy both of these sheets over to the new Google Sheet and we'll copy the pivot table into the new sheet first.
To copy any sheet between Google Sheets, right click the tab and choose “Copy to” from the menu.
Locate the Google Sheet where the pivot table should be copied and click “Select” near the bottom of the dialog. Note: The best way to locate the destination sheet is to search for it by name. Otherwise you can copy the URL of the Google Sheet and paste it into the search bar of the “Copy to” dialog
Update the name of the tab in the new sheet to mirror the tab name in the original sheet so the references for the chart will still work. Note: double click the tab name to edit the name.
Right click the tab for the next report or chart you want to copy and click “Copy to” and then find the destination sheet and click “select.”
Update the data reference
Now that both the supporting pivot table and chart are in the new Google Sheet we'll need to update the data reference for the chart.
Click on the chart and 3 dots will appear in the upper right corner of the chart. Click this to reveal a menu.
Click “Edit chart”
Click the grid icon that appears on the right side of the “Data Range” input area.
When the “Select a data range” dialog appears navigate to the Monthly Spending Pivot sheet and select the cells that should be included in the chart, click “ok”
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 the pivot table is 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.