How to change pivot table data source in Excel

Question

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

User · Answer

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.

User · Answer

This Add-on will do the trick for you    http   www contextures com xlPivotPlayPlus01 html  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  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  lt  gt  0 Then             MsgBox  Problems changing SQL for table    amp  wsh Name  amp       amp  pvt Name             pvt SourceData   tmpArrIn   revert         ElseIf pvt RefreshTable  lt  gt  True Then             MsgBox  Problems refreshing table    amp  wsh Name  amp       amp  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  lt  gt  pvi SourceName Then                             mismatches   mismatches   1                             pvi Name     mismatch   amp  CStr mismatches                          End If                     Next pvi                     If mismatches  gt  0 Then                         For Each pvi In pvf PivotItems                             If pvi Name  lt  gt  pvi SourceName Then                                 pvi Name   pvi SourceName                             End If                         Next                     End If                 End If             Next pvf         End If     Next pvt Next wsh  End Sub

User · Answer

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

[excel] How to change pivot table data source in Excel?

Examples related to excel

Examples related to business-intelligence

Examples related to xls

Examples related to pivot-table