Tutorial :How to change pivot table data source in Excel? [closed]


I want to change it from one database to another.

There don't appear to be any options to do this on the pivot table context menu


Just figured it out-click anywhere in the table, then go to the tabs at the top of the page and select Options-from there you'll see a Change Data Source selection.


Looks like this depends heavily on your version of Excel. I am using the 2007 version and it offers no wizard option when you right click on the table. You need to click on the pivot table to make extra 'PivotTable Tools' appear to the right of the other tabs at the top of the screen. Click the 'options' tab that appears here then there is a big icon on the middle of the ribbon named 'change data source'.


right click on the pivot table in excel choose wizard click 'back' click 'get data...' in the query window File - Table Definition

then you can create a new or choose a different connection


In order to change the data source from the ribbon in excel 2007...

Click on your pivot table in the worksheet. Go to the ribbon where it says Pivot Table Tools, Options Tab. Select the Change Data Source button. A dialog box will appear.

To get the right range and avoid an error message... select the contents of the existing field and delete it, then switch to the new data source worksheet and highlight the data area (the dialog box will stay on top of all windows). Once you've selected the new data source correctly, it will fill in the blank field (which you deleted before) in the dialog box. Click OK. Switch back to your pivot table and it should have updated with the new data from the new source.


  • Right click on the pivot table, choose PivotTable Wizard.
  • Click the 'back' button twice.
  • Choose External Data Source,click next.
  • Click Get Data
  • In the first tab, Databases the first option is 'New Data Source'


This Add-on will do the trick for you.


It shows the connection string, and allows it to be changed. Don't forget to change the Query SQL as well, if needed (with the same tool).


Be a bit wary of any solution that doesn't involve re-creating the PivotTable from scratch. It is possible for your pivot fields' option names to get out of sync with the values they present to the database.

For example, in one workbook I'm dealing with involving demographic data, if you try to select the "20-24" age band option, Excel actually presents you with the figures for ages 25-29. It doesn't tell you it's doing this, of course.

See below for a programmatic (VBA) approach to the problem that solves this issue among others. I think it's fairly complete/robust, but I don't use PivotTables much so would appreciate feedback.

Sub SwapSources()    strOldSource = "2010 Data"  strNewSource = "2009 Data"    Dim tmpArrOut    For Each wsh In ThisWorkbook.Worksheets      For Each pvt In wsh.PivotTables          tmpArrIn = pvt.SourceData          ' row 1 of SourceData is the connection string.          ' rows 2+ are the SQL code broken down into 255-byte chunks.          ' we need to concatenate rows 2+, replace, and then split them up again            strSource1 = tmpArrIn(LBound(tmpArrIn))          strSource2 = ""          For ii = LBound(tmpArrIn) + 1 To UBound(tmpArrIn)              strSource2 = strSource2 & tmpArrIn(ii)          Next ii            strSource1 = Replace(strSource1, strOldSource, strNewSource)          strSource2 = Replace(strSource2, strOldSource, strNewSource)            ReDim tmpArrOut(1 To Int(Len(strSource2) / 255) + 2)          tmpArrOut(LBound(tmpArrOut)) = strSource1          For ii = LBound(tmpArrOut) + 1 To UBound(tmpArrOut)              tmpArrOut(ii) = Mid(strSource2, 255 * (ii - 2) + 1, 255)          Next ii            ' if the replacement SQL is invalid, the PivotTable object will throw an error          Err.Clear          On Error Resume Next              pvt.SourceData = tmpArrOut          On Error GoTo 0          If Err.Number <> 0 Then              MsgBox "Problems changing SQL for table " & wsh.Name & "!" & pvt.Name              pvt.SourceData = tmpArrIn ' revert          ElseIf pvt.RefreshTable <> True Then              MsgBox "Problems refreshing table " & wsh.Name & "!" & pvt.Name          Else              ' table is now refreshed              ' need to ensure that the "display name" for each pivot option matches              ' the actual value that will be fed to the database.  It is possible for              ' these to get out of sync.              For Each pvf In pvt.PivotFields                  'pvf.Name = pvf.SourceName                  If Not IsError(pvf.SourceName) Then ' a broken field may have no SourceName                      mismatches = 0                      For Each pvi In pvf.PivotItems                          If pvi.Name <> pvi.SourceName Then                              mismatches = mismatches + 1                              pvi.Name = "_mismatch" & CStr(mismatches)                          End If                      Next pvi                      If mismatches > 0 Then                          For Each pvi In pvf.PivotItems                              If pvi.Name <> pvi.SourceName Then                                  pvi.Name = pvi.SourceName                              End If                          Next                      End If                  End If              Next pvf          End If      Next pvt  Next wsh    End Sub  


right click on the pivot table in excel choose wizard click 'back' click 'get data...' in the query window File - Table Definition

then you can create a new or choose a different connection

worked perfectly.

the get data button is next to the tiny button with a red arrow next to the range text input box.


for MS excel 2000 office version, click on the pivot table you will find a tab above the ribon, called Pivottable tool - click on that You can change data source from Data tab


In case of Excel 2007 You can change datasource in Options menu /Change Data Source

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »