Pivot tables are one of the best ways to create reports quickly. The best part is that you can move a pivot table from one section to another, a new worksheet, or even a new workbook.
Two Ways for Moving a Pivot Tables
- New Worksheet: Select this option if you want to move your pivot to a new worksheet. The excel will automatically create a new worksheet and move the pivot.
- Existing Worksheet: This option will help you to move the pivot to the currently available worksheets in the workbook. Once you select this option all you need to do is select the cell where you need your pivot table.
Steps to Move a Pivot Table to a Existing Worksheet
- Click on any cell within the pivot table to activate Excel’s pivot table tools. This will add the “Analyze” (or “Options” in older versions) tab to the Excel ribbon.
- Navigate to the “PivotTable Analyze” tab on the Excel toolbar. This tab is dedicated to functions related to pivot tables and only appears when a cell inside a pivot table is selected.
- Within the “Analyze” tab, locate and click on the “Move PivotTable” command. This command is typically found in the “Actions” group.
- A dialog box will open after you click on the “Move PivotTable” command. This dialog box allows you to select the location where you want to move the pivot table.
- In the dialog box, identify and select the target sheet where you want to relocate your pivot table. If you move the pivot table to a new location within the same worksheet, you can specify the cell in the top-left corner of the new location for the pivot table.
- Once you’ve chosen the new location for your pivot table, confirm your choice by clicking “OK” in the dialog box. Excel will then move the pivot table to your choice.
Steps to Move a Pivot Table to a New Worksheet
If you need to move a Pivot Table in Excel, you can easily do so by following the steps outlined below.
- Look at the ribbon tabs and click on either 'Analyze', depending on the version of Excel you're using. These tabs house the controls for modifying Pivot Tables.
- Within the 'Actions' group of buttons, find and click on the 'Move PivotTable' button. This will open a dialog box.
- The Move PivotTable dialog box provides the options to move the PivotTable to a 'New worksheet' or an 'Existing worksheet'. When you choose 'Existing worksheet', you will also need to specify the location on the worksheet where you want to place the PivotTable.
- Now, click on the move pivot table and a dialogue box appears.
Once you’ve moved the PivotTable, be sure to save your changes. This will ensure that the new location of the pivot table is preserved.
Important Note: Moving a pivot table to a new worksheet or workbook doesn’t move the data source. And that’s why you need to ensure you won’t break that connection between the source data and the pivot table.
Use Keyboard Shortcut to Move a Pivot Table
You can easily do this by selecting the entire pivot table, cutting it (Ctrl+X), and then pasting it (Ctrl+V) in the desired location in your Excel worksheet.
- Select the Pivot Table: The keyboard shortcut Ctrl + A allows you to select a pivot table entirely. Before using this shortcut, select a cell in the pivot table and then use the shortcut.
- Cut the Pivot Table: Once the pivot table is selected entirely, you can use Ctrl + X to cut it from its current position.
- Paste the Pivot: The last thing to do is paste the pivot table into the new place. This could be in an existing sheet, a new worksheet, or a different workbook.
Move a Pivot Table to a Different Workbook
The best way to move a pivot table to a different workbook is by using the keyboard shortcuts we have discussed in the above method:
- Open Both Workbooks: Begin by opening the workbook that currently houses the pivot table (source workbook) and the workbook to which you wish to move the pivot table (destination workbook).
- Select the Entire Pivot Table in the Source Workbook: Navigate to the source workbook and ensure you select the entire pivot table, including all rows and columns.
- Copy the Pivot Table: Once you have selected the entire pivot table, copy it by pressing Ctrl+C.
- Switch to the Destination Workbook: Now move over to the destination workbook. It’s important to consider where you want to place the pivot table in this workbook.
- Paste the Pivot Table: Once you’ve chosen a suitable location in the destination workbook, paste the pivot table by pressing Ctrl+V.
When you are working with pivot tables in Excel and need to move them, there are several steps you should take to ensure:
- Select the Full Pivot Table Before Moving: Before dragging your pivot table to a new location, ensure you have selected the entire table. This includes all rows, columns, and any associated data. Not doing so could result in partial data loss, leading to inaccuracies in your analysis.
- Check All Related Formulas or Links: Pivot tables often have connected formulas or links that automatically update based on the data within the table. When you move a pivot table, these connections may need to be adjusted or updated. After moving your pivot table, double-check all related formulas or links to ensure they still function correctly.
More on Pivot Tables
- Delete a Pivot Table in Excel
- Pivot Table Keyboard Shortcuts
- Pivot Table from Multiple Worksheets
- Pivot Chart in Excel
⇠ Back to Pivot Table Tutorial