Copying pivot tables

We have a pivot table for revenue, but we really need similar information to display net income on our dashboard. Fortunately, we don't have to go through that whole exercise again. Since our net income pivot table is based on the same data as our revenue pivot table, we can copy and paste. To create a net income based pivot table, follow these steps:

1. Click anywhere inside the pivot table on the Revenue tab.
2. Select the Analyze tab under the PivotTable Tools grouping.
3. Go to Select | Entire PivotTable.
4. Right-click on the pivot table and select Copy.
5. Create a new sheet using the plus (+) key at the bottom.
6. In cell A1, click on the Paste button on the Home ribbon.

Building a net income pivot table

This creates a copy of the sheet on a different tab. Now, we're ready to modify our copied pivot table. Follow these steps:

1. Select the copied pivot table and drag Account Category off the Filters area and back into the PivotTable Fields list.
2. Drag Posting Type into the Filters area:
   
3. Open the drop-down box for the Posting Type filter next to (All) and select Profit and Loss.
4. The periods should show the first four periods. If not, select cell B5 and click the filter button next to Column Labels. Uncheck Select All and select only periods 1 through 4.
5. Rename the tab Net Income.
6. Right-click in the pivot table and select PivotTable Options. Name the pivot table Net Income.
7. Save the file:
   

We now have source data for two of the five major elements of our dashboard. We can refresh this data any time from within Excel simply by going to Data | Refresh All.

Creating a cash pivot table

We need a couple more pivot tables to round out our dashboard, so let's build them really fast. First, we need cash, and since it's built from our account summary data as well, it makes this easy. For cash, we just want to see total cash. To build the cash pivot table, make another copy of the revenue pivot table with these steps:

1. Click anywhere inside the pivot table on the Revenue tab.
2. Select the Analyze tab under the PivotTable Tools grouping.
3. Go to Select | Entire PivotTable.
4. Right-click in the pivot table and select Copy.
5. Create a new sheet using the plus (+) key at the bottom.
6. In cell A1, click on the Paste button on the Home ribbon.
7. Rename the worksheet tab to Cash.
8. Save the file.

Now, let's modify it to just get the cash total using these steps:

1. In the PivotTable Field list area on the right-hand side:
   • Remove Year and Period ID from Columns
   • Remove Accounts from Rows
   • Move Account Category Number to Rows
   • Add Year to Filters
   
2. Select the filter symbol next to Year and select (All).
3. Select the filter symbol next to Row Labels and uncheck everything except Cash.
4. Right-click in the pivot table and select PivotTable Options. Name the pivot table Cash.
5. Save the file:
   

Nice job. We'll use this information as part of a speedometer graph later on.

Tip

Selecting all years worked in the sample company because of the number of open years in the GP 2016 sample data. In the real world, you would typically only need the open year since it has a beginning balance and subsequent transactions.