How to Move Chart to New Sheet in Excel? 2 Easy Ways!

By default, when you insert a chart in Excel, it’s created in the same worksheet where you have the source dataset.

But in many cases, you may want to move the chart from the sheet where it’s created to another worksheet (or a chart sheet – which has nothing but the chart).

This can be useful when you’re creating dashboards, where the data can be scattered throughout the workbook, but you can get all the important charts in the sheet that has the dashboard.

When it comes to moving a chart to another sheet, you can:

  • Move it as a chart object from one sheet to another
  • Move the chart to a chart sheet, where you have only the chart

In this tutorial, I will show a step-by-step process to move the chart to a new sheet in Excel.

So let’s get started!

Chart Object vs Chart Sheet

Before I get into the steps of moving a chart to a new sheet in excel, let me quickly explain the difference between a chart object and a chart sheet.

When you create a chart in a worksheet that already has the data, what you get is a chart object (which is a chart that sits above your worksheet and you can move it around like an object).

On the contrary, a chart sheet is a completely different sheet that is dedicated to a single chart. So when you move an existing excel chart into a new chart sheet, you would only have the chart in that sheet (and there won’t be any cells or tables like a regular worksheet).

In this tutorial, I will show you how to move an existing chart into a new worksheet (as an object) or to a new chart sheet.

Moving Chart to a New Worksheet (or Another Existing Sheet)

Suppose you have the data set as shown below and you have created a chart using it.

Data with Chart that needs to be moved

Now, I want to move this chart to a different worksheet in the same workbook (this worksheet – to which I want to move the chart – should already exist in the workbook).

Below are the steps to do this:

  1. Click on the chart object that you want to move
  2. Click on the Chart Design tab (this is a contextual tab that only appears when you select any chart)
Click on Chart Design
  1. In the Location group, click on the ‘Move Chart’ icon
Click on Move Chart
  1. In the Move Chart dialog box, make sure ‘Object in’ option is selected.
Select Object in option
  1. From the drop-down, select the sheet where you want to move the selected chart. In this example, I am moving the chart to a sheet named ‘Summary’
Select the sheet to which you want to move the chart
  1. Click OK

The above steps would move the selected chart from the existing worksheet to the worksheet you selected in Step 5.

In case you want to move this chart to a completely new blank worksheet, you will first have to add that new worksheet and then repeat the process (so that the name of this new worksheet is shown in Step 5)

Note that the above steps would remove the chart from the source worksheet and move it to the destination worksheet.

In case you want to keep the chart in the source worksheet and get a copy of it in the new worksheet, you need to first create a copy of the chart (using a simple Control C and Control V), and then move one of these charts to the destination worksheet.

Move Chart by Copy Pasting

Another really quick way to move a chart to a new worksheet is by simply copying the chart and pasting it in the new worksheet.

Doing this would create a copy of the chart in the new worksheet. So if you want to completely move the chart and not have it where you have the data, you can simply keep the copy and delete the original chart (or use Cut-Paste instead of Copy-Paste).

Below are the steps to move a chart using simple copy-paste:

  1. Create a new sheet where you want to move the chart (if you don’t have that already)
  2. Activate the sheet where you have the chart that you want to move
  3. Right-click on the chart and select Copy (or select the chart and use Control + C)
Right click and copy the chart
  1. Go to the Sheet where you want to get a copy of the chart
  2. Right-click and in the Paste Special option, click on the ‘Use Destination Theme’ icon (or use the keyboard shortcut Control + V to paste)
Right click and then click on Paste while keeping destination formatting

The above steps would create a copy of the chart in the current worksheet.

Moving a Chart to a New Chart Sheet

If you want to move a specific chart into its own chart sheet (which is meant to contain nothing else but a chart), you can use the below steps:

  1. Click on the chart object that you want to move to a new chart sheet
  2. Click on the Chart Design tab
Click on Chart Design
  1. In the Location group, click on the ‘Move Chart’ icon
Click on Move Chart
  1. In the Move Chart dialog box, select ‘New Sheet’
Click on New Sheet option
  1. Give a name to the sheet where this chart would be moved (or keep the default Chart1 name)
Give a name to the Chart Sheet
  1. Click OK

The above steps would remove the chart from the current worksheet, create a new chart sheet, and move the chart to this new chart sheet.

New Sales Chart Sheet is inserted

All the customizations that you can do with a chart object can also be done with a chart that’s in the chart sheet (except resizing and moving around the chart).

Note that you can also move other charts into this newly inserted Chart Sheet, but the one that you inserted first would remain in the background and all the other charts would be treated as an object that floats over that chart.

Moving All the Charts in the Workbook to a New Sheet

While the manual way of moving a chart to another worksheet or chart sheet is quite easy, it could become quite tedious in case you have a lot of charts that you want to move.

Imagine 20 sheets with one chart in each sheet that you want to move to the summary or dashboard sheet.

In such a scenario, you can use a simple VBA code that would go through all the charts in your workbook and move these two into the specified worksheet.

Below is the VBA code that would move all the charts from all the worksheets to one destination worksheet (in this example, it’s the sheet with the ‘Dashboard’)

'Code Developed by Sumit Bansal from https://trumpexcel.com
Sub MoveCharts()
Dim chartObject As Object
Dim SheetwithCharts As Worksheet
For Each SheetwithCharts In Application.ActiveWorkbook.Worksheets
If SheetwithCharts.Name <> "Dashboard" Then
For Each chartObject In SheetwithCharts.ChartObjects
chartObject.Chart.Location xlLocationAsObject, "Dashboard"
Next chartObject
End If
Next SheetwithCharts
End Sub

In the above code, I have hard-coded the name of the destination sheet where the charts would be moved (the destination sheet name used is ‘Dashboard’).

In case you want to move these charts to any other worksheet, just replace the word Dashboard with the name of that worksheet.

Below are the steps to run this VBA macro code in Excel:

  1. Click the Developer tab in the ribbon (if you can’t see the Developer tab, read this guide to enable it)
Click the Developer tab
  1. Click on Visual Basic icon. This will open the VB Editor
Click on Visual Basic
  1. In the Project Explorer, select any object of the workbook that has the charts (if you don’t see Project Explorer, click on ‘View’ option in the menu and then click on Project Explorer)
View Project Explorer
  1. Click the ‘Insert’ option in the menu and then click on ‘Module’. This will insert a new module for the workbook
Insert a Module
  1. Copy and paste the above code in the Module code window (you can open the module code window anytime by double-clicking on the module name in the Project Explorer)
Copy paste the code to the module code window
  1. Select any line in the code
  2. Press the F5 key (or click the green play button in the toolbar)
Run the Macro to move all charts to a specified sheet

The above steps would move all the charts from all the sheets in the workbook into the sheet named ‘Dashboard’.

How do the Code works – A Simple Explanation

The above code uses a For Next loop to go through all the worksheets in the workbook. It then uses an if condition to check whether the name of the worksheet is ‘Dashboard’ or not.

If the name of the worksheet is ‘Dashboard’, nothing happens, and in case it’s not, then the code goes through each chart in that worksheet (this is again done using a For Next loop).

Each chart in the sheet is then moved to the dashboard worksheet (and also removed from the sheet where it originally existed).

Pro Tip: I strongly recommend you create a copy of the original file before you run this code (as the changes done by the VBA code can not be undone)

So these are some simple ways that you can use to move a chart to any specific sheet in the workbook (or to a new chart sheet).

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment