How to Move a Chart to a New Sheet in MS Excel

by

When you create a chart in your Excel workbook, it’s created in the same sheet as your data by default. You might want to move the chart to a new sheet for reporting purposes. You can do this easily in MS Excel.

4 Methods of moving a chart to a new sheet in Microsoft Excel:

  • Move chart to another Excel sheet
  • Move chart to a new Chart sheet
  • Move chart by copy-pasting
  • Move chart using VBA code

Let us begin by looking at these methods in detail.

Method #1: Move Chart to Another Sheet

If you want to move the chart to a new sheet or another existing sheet, you can use this method.

Step #1: Open your Excel workbook

Open your existing workbook to move the chart from that sheet to another sheet.

Step #2: Navigate to Chart Design

Click on the chart and go to Chart Design, which will now show in the main menu bar.

Step #3: Select Move Chart

In the Chart Design ribbon, click on the Move Chart icon.

Step #4: Choose the target sheet

The Move Chart dialog box will open. 

Select the Object in: radio button. From the drop-down, choose the sheet where you want the chart to be placed. 

For our example, we will choose the sheet named Chart.

Step #5: Click OK

Once you’re done, click the OK button to confirm your choice.

Now, you will see that the chart from the Sales sheet has been moved to the Chart sheet.

Note: This will remove the chart from the original sheet and move it to the new sheet.

Method #2: Move Chart to a New Chart Sheet

Use this method if you want to move the chart to a brand new chart sheet.

What is a Chart Sheet?

A chart sheet means there would be nothing in the sheet but the chart.

A chart sheet is a Microsoft Excel worksheet, a worksheet that contains only a chart. A Chart Sheet does not have regular cells/tables like that in a regular worksheet.

This method describes how you can easily move an Embedded chart to a brand new Chart Sheet.

Lets Start

Step #1: Open your Excel workbook

Open your Excel workbook containing the chart that you want to move.

Step #2: Navigate to Chart Design

Click on the chart and go to Chart Design, which will now show in the main menu bar.

Step #3: Select Move Chart

In the Chart Design ribbon, click on the Move Chart icon.

Step #4: Select New sheet

The Move Chart dialog box will open. 

Select the New Sheet: radio button. 

You can rename the chart sheet if you want. For instance, let’s name this sheet Charts.

Step #5: Click OK 

Once you’re done, click the OK button to confirm your choice.

Now, you will see that the chart has been moved to a new sheet named Charts.

You can see that there are no cells because this is a chart sheet.

Method #3: Move Chart by Copy-Pasting

Another way to move the chart is to copy and paste it. This is one of the simplest and easiest methods to move a chart. This method would also keep the chart in the original sheet.

Step #1: Open your Excel workbook

Open your Excel workbook containing the chart that you want to move.

Step #2: Select the chart

Click on the chart you want to move to select it.

Step #3: Copy the chart

Copy the chart by right-clicking on it and selecting Copy. 

Or

Use the keyboard shortcut Ctrl + C if you’re a Windows user. You can use Cmd + C if you’re a Mac user.

Step #4: Paste the chart

Navigate to the sheet where you want the chart to be displayed. 

Right-click anywhere and select the Paste icon from the menu that opens.

Or

Use the keyboard shortcut Ctrl + V (Windows) or Cmd + V (Mac).

You will now see that the chart is placed on your new sheet.

Method #4: Move Chart Using VBA Code

If you have many charts, it can be time-consuming to move each chart one by one. Use this method to move all the charts from one sheet to another automatically.

Step #1: Insert the Developer tab

Follow the How to Add the Developer Tab steps, if you do not already have the Developer tab in your main menu bar.

Step #2: Select Visual Basic

Click on the Developer tab and select the Visual Basic icon in the ribbon.

Step #3: Insert a new module

Click on Insert.

From the drop-down that opens, select Module. This will create a new module.

Step #4: Copy and paste this VBA code 

Copy the following VBA code and paste it into the newly created module. 

Sub MoveAllCharts()
   Dim ChartObj As Object
   For Each ChartObj In Sheets("Source_Sheet").ChartObjects
      ChartObj.Chart.Location xlLocationAsObject, "Dest_Sheet"
   Next ChartObj
End Sub

Once you have pasted it, replace the words Source_Sheet with the name of the sheet containing the charts. 

Replace the words Dest_Sheet with the name of the sheet where you want to move your charts. Make sure not to add any spaces.

In our example, the Source_Sheet is Sales and the Dest_Sheet is Charts.

Step #5: Click the Run icon

After pasting and changing the code, click on the Run icon. It looks like a Play icon (Triangle).

Or

Press the F5 key on your keyboard to run the module.

You will now see that all the charts have been moved from the Sales sheet to the destination Chart sheet.

Conclusion

You can use any of the above methods to quickly move a chart from one sheet to another in MS Excel. If you want to move many charts at once, or if you often have to move charts for monthly reports, use the last method.

Leave a Comment