Change source data in excel

Change the source data for a PivotTable

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 More…Less

After you create a PivotTable, you can change the range of its source data. For example, you can expand the source data to include more rows of data. However, if the source data has been changed substantially—such as having more or fewer columns, consider creating a new PivotTable.

You can change the data source of a PivotTable to a different Excel table or a cell range, or change to a different external data source.

Click the PivotTable report.
On the Analyze tab, in the Data group, click Change Data Source, and then click Change Data Source.

The Change PivotTable Data Source dialog box is displayed.
Do one of the following:

To use a different connection
1. Click select a Use an external data source, and then click Choose Connection.
  
  The Existing Connections dialog box is displayed.
2. In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).
3. Select a connection from the Select a Connection list box, and then click Open. What if your connection is not listed?
  
  Note: If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.
  
  For more information, see Manage connections to data in a workbook.
4. Click OK.
To change the data source of a PivotTable to a different Excel table or a cell range
- Click Select a table or range, and then enter the first cell in the Table/Range text box, and click OK.

If your connection is not listed in the Existing Connections dialog box, click Browse for More, and then look for the data source you want to connect to in the Select Data Source dialog box. Click New Source if appropriate, and follow the steps in the Data Connection Wizard, and then return to the Select Data Source dialog box.

If your PivotTable is based on a connection to a range or table in the Data Model, you can choose another Data Model table or connection on the Tables tab. However, if your PivotTable is based on the Workbook Data Model, you can’t change the data source.

Pick the connection you want, and click Open.
Click Only Create Connection.
Click Properties, and click the Definition tab.
If your .odc connection file has been moved, browse to its new location in the Connection file box.
If you need to change values in the Connection string box, contact your database administrator.

Click the PivotTable report.
On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source.

The Change PivotTable Data source dialog box is displayed.
Do one of the following:
- To use a different Excel table or cell range, click Select a table or range, and then enter the first cell in the Table/Range text box.
  
  Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .
To use a different connection, click select a Use an external data source, and then click Choose Connection.

The Existing Connections dialog box is displayed.
In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).
Select a connection from the Select a Connection list box, and then click Open.

Note: If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.

For more information, see Manage connections to data in a workbook.
Click OK.

Pick the connection you want, and click Open.
Click Only Create Connection.
Click Properties, and click the Definition tab.
If your .odc connection file has been moved, browse to its new location in the Connection file box.
If you need to change values in the Connection string box, contact your database administrator.

For more information about what data sources are supported, see Import and shape data in Excel for Mac (Power Query).

Click the PivotTable report.
On the Analyze tab, in the Data group, click Change Data Source, and then click Change Data Source.

The Change PivotTable Data Source dialog box is displayed.
Do one of the following:
- To change the data source of a PivotTable to a different Excel table or a cell range, click Select a table or range, and then enter the first cell in the Table/Range text box, and click OK
- To use a different connection, do the following:
  1. Click select a Use an external data source, and then click Choose Connection.
    
    The Existing Connections dialog box is displayed.
  2. In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).
  3. Select a connection from the Select a Connection list box, and then click Open. What if your connection is not listed?
    
    Note: If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.
    
    For more information, see Manage connections to data in a workbook.
  4. Click OK.

Pick the connection you want, and click Open.
Click Only Create Connection.
Click Properties, and click the Definition tab.
If your .odc connection file has been moved, browse to its new location in the Connection file box.
If you need to change values in the Connection string box, contact your database administrator.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Steps to Change the Data Source of a Pivot Table

To change the data source of an existing pivot table in Excel 2016, you will need to do the following steps:

Select any cell in the pivot table to reveal more pivot table options in the toolbar. In this example, we have selected cell A1 on Sheet2.

You now should see 2 new tabs appear in the toolbar called Analyze and Design.
Select the Analyze tab from the toolbar at the top of the screen. In the Data group, click on Change Data Source button and select «Change Data Source» from the popup menu.
When the Change PivotTable Data Source window appears, change the Table/Range value to the new data source that you want for your pivot table and then click on the OK button.

In this example, we want to change the range from Sheet1!$A$1:$F$16 to Sheet1!$A$1:$F$17 because we have added one more row to our data in Sheet1.
Now when you return to your pivot table, it should automatically refresh the pivot table and display the information from the new data source.

In this example, Order ID 10252 now shows in the pivot table results.

Источник

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

Источник

Содержание

Make changes to an existing data source in Power Pivot
Need more help?
Make changes to an existing data source in Power Pivot
Need more help?
Change the source data for a PivotTable

Make changes to an existing data source in Power Pivot

After you create a connection to an external data source in a Data Model, you can use the Power Pivot add-in to change:

The connection information—including the file, feed, or database used as a source, its properties, or other provider-specific connection options.

Table and column mappings.

Remove references to columns that are no longer used.

The tables, views, or columns you get from the external data source. See Filter the data you import into Power Pivot.

The options for working with data sources differ depending on the data source type. This procedure uses a simple Access database.

Follow these steps:

In the Power Pivot window, click Home > Connections > Existing Connections.

Select the current database connection and click Edit.

For this example, the Table Import Wizard opens to the page that configures an Access database. The provider and properties are different for different types of data sources.

In the Edit Connection dialog box, click Browse to locate another database of the same type but with a different name or location.

As soon as you change the database file, a message appears indicating that you need to save and refresh the tables to see the new data.

Click Save > Close.

Click Home > Get External Data > Refresh > Refresh All.

The tables are refreshed using the new data source, but with the original data selections.

Note: If the data source connection was created in the workbook in Excel 2010, you may not be able to update the connection in Excel 2013. For more information, see the section «Can’t update the data source connection» in Upgrade Power Pivot Data Models to Excel 2013.

When you change a data source, the columns in the tables in your model and those in the source may no longer have the same names, though they contain similar data. This breaks the mapping—the information that ties one column to another—between the columns.

Follow these steps:

In the Power Pivot window, click Design > Properties > Table Properties.

The name of the current table is shown in the Table Name box. The Source Name box contains the name of the table in the external data source. If columns are named differently in the source and in the model, you can go back and forth between the two sets of column names by selecting Source or Model.

To change the table that is used as a data source, for Source Name, select a different table than the current one.

Change column mappings if needed:

To add columns that are present in the source but not in the model, select the box beside the column name. The actual data will be loaded into the model the next time you refresh.

If some columns in the model are no longer available in the current data source, a message appears in the notification area that lists the invalid columns. You don’t need to do anything else.

Click Save to apply the changes to your workbook.

When you save the current set of table properties, any missing columns are automatically removed and new columns are added. A message appears indicating that you need to refresh the tables.

Click Refresh to load updated data into your model.

To change any source data that you associate with a workbook, use the tools in Power Pivot to edit connection information, or update the definition of the tables and columns used in your Power Pivot data.

Here are changes that you can make to existing data sources:

Edit the database name or server name

Change the name of the source text file, spreadsheet, or data feed

Change location of the data source

For relational data sources, change the default catalog or initial catalog

Change the authentication method or the credentials used to access the data

Edit advanced properties on the data source

Add or remove a filter on the data

Change the filter criteria

Add or remove tables

Change table names

Edit mappings between tables in the source and tables in the workbook

Select different columns from the source

Change column names

Delete columns from the workbook (does not affect data source)

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Источник

Make changes to an existing data source in Power Pivot

After you create a connection to an external data source in a Data Model, you can use the Power Pivot add-in to change:

The connection information—including the file, feed, or database used as a source, its properties, or other provider-specific connection options.

Table and column mappings.

Remove references to columns that are no longer used.

The tables, views, or columns you get from the external data source. See Filter the data you import into Power Pivot.

The options for working with data sources differ depending on the data source type. This procedure uses a simple Access database.

Follow these steps:

In the Power Pivot window, click Home > Connections > Existing Connections.

Select the current database connection and click Edit.

For this example, the Table Import Wizard opens to the page that configures an Access database. The provider and properties are different for different types of data sources.

In the Edit Connection dialog box, click Browse to locate another database of the same type but with a different name or location.

As soon as you change the database file, a message appears indicating that you need to save and refresh the tables to see the new data.

Click Save > Close.

Click Home > Get External Data > Refresh > Refresh All.

The tables are refreshed using the new data source, but with the original data selections.

Follow these steps:

In the Power Pivot window, click Design > Properties > Table Properties.

To change the table that is used as a data source, for Source Name, select a different table than the current one.

Change column mappings if needed:

To add columns that are present in the source but not in the model, select the box beside the column name. The actual data will be loaded into the model the next time you refresh.

If some columns in the model are no longer available in the current data source, a message appears in the notification area that lists the invalid columns. You don’t need to do anything else.

Click Save to apply the changes to your workbook.

When you save the current set of table properties, any missing columns are automatically removed and new columns are added. A message appears indicating that you need to refresh the tables.

Click Refresh to load updated data into your model.

Here are changes that you can make to existing data sources:

Edit the database name or server name

Change the name of the source text file, spreadsheet, or data feed

Change location of the data source

For relational data sources, change the default catalog or initial catalog

Change the authentication method or the credentials used to access the data

Edit advanced properties on the data source

Add or remove a filter on the data

Change the filter criteria

Add or remove tables

Change table names

Edit mappings between tables in the source and tables in the workbook

Select different columns from the source

Change column names

Delete columns from the workbook (does not affect data source)

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Источник

Change the source data for a PivotTable

You can change the data source of a PivotTable to a different Excel table or a cell range, or change to a different external data source.

Click the PivotTable report.

On the Analyze tab, in the Data group, click Change Data Source, and then click Change Data Source.

The Change PivotTable Data Source dialog box is displayed.

Do one of the following:

To use a different connection

Click select a Use an external data source, and then click Choose Connection.

The Existing Connections dialog box is displayed.

In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).

Select a connection from the Select a Connection list box, and then click Open. What if your connection is not listed?

Note: If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.

To change the data source of a PivotTable to a different Excel table or a cell range

Click Select a table or range, and then enter the first cell in the Table/Range text box, and click OK.

If your connection is not listed in the Existing Connections dialog box, click Browse for More, and then look for the data source you want to connect to in the Select Data Source dialog box. Click New Source if appropriate, and follow the steps in the Data Connection Wizard, and then return to the Select Data Source dialog box.

If your PivotTable is based on a connection to a range or table in the Data Model, you can choose another Data Model table or connection on the Tables tab. However, if your PivotTable is based on the Workbook Data Model, you can’t change the data source.

Pick the connection you want, and click Open.

Click Only Create Connection.

Click Properties, and click the Definition tab.

If your .odc connection file has been moved, browse to its new location in the Connection file box.

If you need to change values in the Connection string box, contact your database administrator.

Click the PivotTable report.

On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source.

The Change PivotTable Data source dialog box is displayed.

Do one of the following:

To use a different Excel table or cell range, click Select a table or range, and then enter the first cell in the Table/Range text box.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .

To use a different connection, click select a Use an external data source, and then click Choose Connection.

The Existing Connections dialog box is displayed.

In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).

Select a connection from the Select a Connection list box, and then click Open.

Pick the connection you want, and click Open.

Click Only Create Connection.

Click Properties, and click the Definition tab.

If your .odc connection file has been moved, browse to its new location in the Connection file box.

If you need to change values in the Connection string box, contact your database administrator.

Источник

Need more help?

See Also

Need more help?

Want more options?

Steps to Change the Data Source of a Pivot Table

Make changes to an existing data source in Power Pivot

Need more help?

Make changes to an existing data source in Power Pivot

Need more help?

Change the source data for a PivotTable

А вот еще интересные статьи: