Change Pivot Table Data Source To External Connection Greyed Out

Figure 1 – Data from Other Sources in PowerPivot. When we paste data below a Table, the Table automatically extends to include the new data. Book Description. I want to change it to point to a worksheet within the same workbook. Hi, Thank you for this. In the "Data" ribbon "Connections" tab the "Edit Links" option is greyed out. I need to update the location of the external data source each month. Extracting Connection Information from Pivot Tables It's pretty easy to add pivot tables to worksheets - even easier to add them when the source is a cube. The stumbling block turned out to be that when you have multiple pivot tables that use the same pivot cache, you cannot simply change the SourceData property of the existing cache as you can if there’s only one pivot table. For more information, see Chapter 10, Linking Calc Data. On the Ribbon, under the PivotTable Tools tab, click the Options tab. After entering the server/database specifics and your credentials if necessary, you are presented with the Power Query Editor dialog. I used your code and am trying to change it to create a pivot table with my data. Join Curt Frye for an in-depth discussion in this video, Connecting to an external data source, part of Excel 2016: Pivot Tables in Depth. I will explain why and I will also provide a workaround using PowerPivot. The normal way of formatting cells in Excel (highlight the area, then Format - Cells at the. However, that option is greyed out and I can't select the data source. Re: Edit "source data" on pivot chart Thanks, I understand that to be the situation (and the problem), I was just curious as to why it can't be "unlinked"? If you can change the source data (i. For example, it’s very straightforward to create a pivot table using a connection to an Access data source. Pivot Chart Source Data. After your pivot table has been created, you can still make changes and additions to your data. In the Create PivotTable dialog box, select the Use an external data source, then click Choose Connection. To obtain a distinct count in an Excel 2013 PivotTable, here is what we do. The biorefinery is using microorganisms and the fermentation route in order to produce the desired outputs. Repeat the same steps again to continue adding Excel tables to a data model. The benefit of using a data source connection is primarily for source data which changes. The following dialog box appears. I have a template that, as far as I know, contains no external data sources: I cannot find any cell that refers to an external source. You can perform calculations without having to input any formulas or copy any cells. VBA to change pivot source data. To use a different Microsoft Office Excel table or cell range, click Select a table or range, and then enter the first cell in the Table/Range text. Click number-3 menu; Click "back" twice; Choose Microsoft Excel List or Database radio button; click "next" then fill the range or the named range in the range-box; Click FINISH. Pivot table data source not valid [UPDATE: see my new post on this topic, including a super easy-to-follow flowchart] Occasionally, despite all our training and know-how, we run into a problem in Excel. If the data connection is configured correctly, you should be seeing. On the Analyze tab, in the Data group, click Change Data Source. Excel automatically selects the data for you. If this comes from an external file that your colleague cannot access or other database, you should check they can get to that data first. But Excel 2010 doesn't appear to have this feature. I have been able to work it around in the Desktop by selecting menu File. Select which fields you want to display on the report. Use workaround I found that saves tables into excel file and calls them as an external source. When you create a pivot table in your workbook, the source data records are saved in a special memory area – a pivot cache. Select New Data Source and Click OK. Book Description. The data in the original query was filtered by State, so that users could only see data that related to them. To manually update a table, select any cell in the table and choose. However, I’ve never seen a description of how to do it. To replace a pivot table, it is advisable to refresh” it. This macro is perfect for instances where you have an ever-changing data set that is feeding-in a bunch of Pivot Tables. And its only when I refresh the data. In order to pass a list of filter arguments to your pivot table instead of selecting them all manually you can do the following: 1) Pass your filter table and your data table to the Data Model (Power Pivot): Connect them on the filter columns. This works nicely when you need to strategically refresh only certain data sources. All you have to do is just create a pivot table with your source data and after that change the source with the named range using the same method which I have used in the first method of tables. Link pivot to access (or other database source) With Pivot Tables, you don’t have to have the data base that you want to work with in an Excel spreadsheet. My pivot table is currently pointing to an MS Access database. > I went into the properties and made sure I selected the connection that was. I would like to know if there is a way to. I used your code and am trying to change it to create a pivot table with my data. Data sources fall into two broad categories. Seems like it is best to keep you large excel file in a separate workbook and fire up your pivot table in a new Excel workbook thru the "use an external data source" and when needed to update the PT then just load new data in the initial / separate workbook and refresh the PT in the separate Excel file. Pivot tables are much more dynamic than standard Excel 2010 tables because they are so easy to manipulate and modify. Seems like it is best to keep you large excel file in a separate workbook and fire up your pivot table in a new Excel workbook thru the “use an external data source” and when needed to update the PT then just load new data in the initial / separate workbook and refresh the PT in the separate Excel file. The resulting table does not update automatically. You should see the Power Pivot Field List. Breaking Power Query via Power Pivot is a thing of the past Posted on October 21, 2015 by Ken Puls I'm pleased to let people know that breaking Power Query via Power Pivot is a thing of the past … at least for users of Excel 2013 or higher. Repeat this process by clicking the "Add…" button's drop-down menu again. I was working in Excel 2010 on a workbook with multiple sheets when I suddenly couldn't interact with the Insert or Data menus (they were entirely greyed out), I also couldn't interact with my PivotCharts at all. How do i enable it. Sort and filter grayed out - posted in General Excel Problems and Solutions: I am trying to sort a mailing list alphabetically and when I select the last name column the sort and filter are grayed out and I cannot get it to sort alphabetically. Excel Pivot tables are fantastic for creating fast and accurate, sorted, summary information in Excel. We’re going to add a pivot table to a new sheet. In the popup - you'll see another properties icon. When I try to alter the existing pivot table's data source and change it from a External Datasource Connection to a table range in the workbook I am unable to do so. Calculated fields are not available in an OLAP-based Pivot Table … XLCubed / 20th June 2016 1st May 2019 / Reporting & Analytics One of users biggest frustrations with cube connected PivotTables is that they can’t add calculated fields like they can with a regular PivotTable – the calculated field is greyed out on the ribbon. Microsoft's Excel spreadsheet application is designed to allow users to organize and interpret their data using advanced features such as pivot tables, formulas and macros. Once you add new data in your source sheet, just refresh your pivot table. Adding an Excel table to a workbook data model. You can always ask an expert in the Excel Tech Community , get support in the Answers community , or suggest a new feature or improvement on Excel User Voice. On Dialog Choose Data Source swap to. I will explain why and I will also provide a workaround using PowerPivot. Seems like it is best to keep you large excel file in a separate workbook and fire up your pivot table in a new Excel workbook thru the “use an external data source” and when needed to update the PT then just load new data in the initial / separate workbook and refresh the PT in the separate Excel file. The pivot table is very important when we want to analyze or slice data to get an insight. But when you get to the Pivot Table wizard, select "Use and External Data Source" and then select "Choose Connection" as shown below. I have two slicers connected to separate sets of pivot tables "slicing" the same field. We will also explore more complex uses such as creating a custom slicer style, connecting one slicer to multiple pivot tables, and more. Data connection information The connection string is passed to the data extension. We don't know how to use Excel built-in function [ANALYZE -> Change Data Source] to change to another SAS data source either. I have created a new field in the query and created a new pivot table worksheet. In my opinion, you need to create a new PivotTable. Any advice would be appreciated. External data is defined as data that exists outside of the Excel workbook, in some other place. This book is part of Que's exciting new Content Update Program, which provides automatic content updates for major technology improvements!. And an external data source could include a connection. And when you add new Employees or change Employees in the source data, a Data > Refresh All will update the 'Table_Employee' information, and you can enter the comments. In my solution, I first set up a data connection to the database, with a SQL select statement to fetch all the SalesPerson sales records:. Excel VBA Create Pivot Table: Step-by-Step Guide and 4 Examples to Create Pivot Tables with Macros By J. Power Pivot change edit connection greyed out. How to Change Pivot Table Source Data. Pivot Table Using SQL. Excel View Chart Data source Pivot Table From Multiple sources Excel 2007 Excel Pivot. " Even though they are greyed out, they are also still selectable. Microsoft's Excel spreadsheet application is designed to allow users to organize and interpret their data using advanced features such as pivot tables, formulas and macros. Select any cell in the pivot table. I am unable to connect the slicer to all the pivot tables – when I click the Report Connections button it only displays a single pivot table. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab). Select the data source (in this case, AdventureWorks Cube) pointing to a different environment (in this case, Production). Data Sources rely on a defined Gateway. Click the pivot table sheet, if it’s not already open. Excel makes it just as easy to change which fields from the original data source are displayed in the table as it does originally adding them when a table is first created. But when you get to the Pivot Table wizard, select “Use and External Data Source” and then select “Choose Connection” as shown below. Data Model refresh 3. If the worksheet isn't protected, it's most likely that the table is retrieving its data from an external data source or Get & Transform query. Add fields to a pivot table. When I go to the Change Data Source area, the option to choose a local range is greyed out. Don’t Delete a Table directly in the PowerPivot window. I tried to change the data source of that chart to a different pivot table and if I read your comment correctly, Excel 2010 should have allowed me to select the data source range. You can actually source the information from a variety of sources. To replace a pivot table, it is advisable to refresh" it. In the Change PivotTable Data Source. Linkback Tables in PowerPivot for Excel 2013 In PowerPivot for Excel 2010 it is possible to import an Excel table in a PowerPivot data model by using the Linked Table feature. My problem occurs when I wish to update my data and replace the 4 source tables. these in the know will see that this may show a "Y" within the cell. ) Connect to the ‘TheData’ & ‘Employee’ data sources using Power Query. Change graph features, including: add a chart. As Miguel Llopis of the Power Query team explains here and here, when you make certain changes to a table in the Power Pivot window the connection from your Power Query query to the Excel Data Model goes into 'read-only' mode. The source tables can be refreshed without incident. To manually update it, select the PivotTable and then go to Analyze > Refresh. More efficient and effective solutions use the Pivot Table, a tool for combining, comparing, and analyzing large amounts of data easily. I have a workbook full of pivot tables on different sheets that all use the same external data source ('\Data\Hospital Dashboard\2013\02-February\CL Volume Report February 2013 (cleaned) -RG. 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. I would like to know if there is a way to. " Even though they are greyed out, they are also still selectable. Data Model refresh 3. Choose the Acctivate Data Source (ODBC) from the list. For more information, see Chapter 10, Linking Calc Data. I have an existing pivot report using a data range in the report. Uncheck 'Save source data when opening the file': if your data is in an external file or database then unchecking this option will not save any data to the Pivot Cache. How to Change Pivot Table Source Data. 1 "Pivot" Table - Pivot Table. Two of these tables are pivots from one data table within the analysis and the third is another pivot from a seperate table (also in the analysis). When I go to the Change Data Source area, the option to choose a local range is greyed out. Excel® 2016 PIVOT TABLE DATA CRUNCHING. Once i change the data connection of the charts the slicer cant recognize the a slicer data source connection in Excel 2013 without deleting and creating. After several hours of work, I realize that I can't share pass this table along to a project lead, or share with a client, as the table is stored locally. As I added to the DataDump table over the weeks, it has gotten very large 22mb, so decided to move the DataDump table to a seperate file that I would create an external data connection to. odc , and I found it in the My Documents/My Data Sources folder associated with my user account. how to connect to a pivot table to a database. The wizard shows the Power Query created earlier. Load Status – This displays whether the data is loaded to a table, pivot table, pivot chart or is a connection only. Two of these tables are pivots from one data table within the analysis and the third is another pivot from a seperate table (also in the analysis). In my opinion, you need to create a new PivotTable. The scripts above reload a primary table that is linked to an external source (on both Desktop and Webplayer), but it will not reload the data for a Pivot table that is based on the primary table (internal). It's a community software. To do this, we need to start with grabbing the external data from SQL Server. Use Excel pivot table calculated item to create unique items in a pivot table field. With your PivotTable selected, go to the Options tab and select Change Data Source. In cases when the data type of the fields are not the same, you can use custom SQL to change the data type (cast) the field before performing the join. Now this tutorial will tell you how to update the pivot table when rows or columns of the data table change. There's a full explanation of why this is below. That other place could be almost anywhere, and Excel supports pulling external data from a wide variety of sources. Using Pivot Tables, you can view different summaries of the source data, display the details of areas of interest, and create. Click Create to create the report. PowerPoint: Where’s My Chart Data? Posted on March 17, 2011 by Veronica Mount Excel Charts are prime examples of the old saying “A picture is worth a thousand words,” and PowerPoint presentations provide a great vehicle for passing a chart’s message on to an audience. The source tables can be refreshed without incident. Skip navigation Connecting a Pivot Table to External Data Reporting Guru How to Best Import External Data into Excel & Import data from the. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab). Default = No. Auto-Merge with Pivot Tables 1. Using Excel Table as the source data range of the pivot table prevents this mistake. When part or all of the data you need to build your reports is stored in an external file or database. I an using Excel 2010. It seems like the "Change Data Source" feature of Pivot Tables is disabled in Excel 2013 when they are built on the Data Model (PowerPivot). This is the option we want. And this is really important. But when you get to the Pivot Table wizard, select “Use and External Data Source” and then select “Choose Connection” as shown below. Pivot Table Data Source - External to Internal Hi everyone, I've been searching to find a way of changing my pivot table data source from Access file to a local excel sheet (of same file). Using Pivot Tables, you can view different summaries of the source data, display the details of areas of interest, and create. Pivot Chart Source Data. The normal way of formatting cells in Excel (highlight the area, then Format - Cells at the. External data is defined as data that exists outside of the Excel workbook, in some other place. Step 2: Click on Insert->Pivot Table. control all pivot tables irrespective of which data connection they have. I know that this was possible with Excel 2003. So clicking any Refresh All button anywhere in Excel essentially completely reloads Power Pivot, refreshes all pivot tables, and updates all workbook data connections. Once you add new data in your source sheet, just refresh your pivot table. Scroll down to the Data Feeds section and you should find the Report option. And now I'm presented with this long list of connections. However, a pivot chart doesn't let you add data from outside the pivot table, or exclude data from the pivot table (though you could format a series to make it invisible). 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. If this is the case, you may need to change the sorting options within the query itself or copy and paste the data elsewhere so that it is no longer connected to another data source. I need to update the location of the external data source each month. If you use an Excel Table for the source data of your pivot table, you get a very nice benefit: your data range becomes "dynamic". I have two slicers connected to separate sets of pivot tables "slicing" the same field. We will also explore more complex uses such as creating a custom slicer style, connecting one slicer to multiple pivot tables, and more. Re: Edit "source data" on pivot chart Thanks, I understand that to be the situation (and the problem), I was just curious as to why it can't be "unlinked"? If you can change the source data (i. Pivoting data. Is there any way I could change the data source or do I have to recreate all the existing pivot table sheets. Type a name for the data source eg TM1 Prod. This way your. For all other types of data, the Edit Table Properties dialog shows the connection name and query used to retrieve the data. The pivot tables are not connected to the same data source (i. I want the user to be able to run a macro that will prompt them which week they want to use as the external data and then have. Microsoft's Excel spreadsheet application is designed to allow users to organize and interpret their data using advanced features such as pivot tables, formulas and macros. I have created a new field in the query and created a new pivot table worksheet. row 7) which stores the report data in the spreadsheet, and; The data connection (e. I know that this was possible with Excel 2003. Figure 10: Creating a data connection without creating a pivot table The next step is to locate the data connection file we just created so we can edit it. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?. You now should see 2 new tabs appear in the toolbar called Analyze and Design. Join Curt Frye for an in-depth discussion in this video, Updating and refreshing PivotTable data sources, part of Excel 2007: Pivot Tables for Data Analysis. PivotExDSSample. · Now press “Alt+F11” to open visual basic for Excel · From “Microsoft Excel Objects” double click on “ThisWorkbook”. Your data is now ready for use in a Pivot Table. Now this tutorial will tell you how to update the pivot table when rows or columns of the data table change. In addition, if the pivot tables are linked to an external source, you'll certainly want current values. Anybody experienced and found a solution so I can start getting distinct count in my pivot tables?. If the data source of Pivot Tables is in another workbook you can break this link too. If using this technique for a power Pivot Data model I recommend using these queries as a reference. Whenever possible opt for "one version of the truth. Alternatively, you can choose an external data source such as a database (this is an advanced topic and is outside the scope of this lesson). For those of you with some database knowledge this is similar to creating joins between tables, except all the tables live in Excel. A pivot table allows you to extract the significance from a large, detailed data set Our data setconsists of214 rows and 6 fields. I would like to share answer below. When you create a Pivot Table report Excel automatically stores a copy of the source data in a part of your computer’s memory called the data cache. Help! That's exactly what I felt like in this situation. Add a column for each way you need to filter or group data in your Pivot Table. In this case, click Action>Move PivotTable. That is to say, if you’re on the Dim_Products tab in Power Pivot, clicking Refresh reaches out to the external SQL Server and requests an update for only the Dim_Products table. In the Create PivotTable dialog box, select the Use an external data source, then click Choose Connection. If your data is from linked tables in the Excel Workbook, by default these are refreshed automatically when the data changes. How to update pivot table range in Excel? In Excel, when you remove or add rows or columns in your data range, the relative pivot table does not update at the same time. If you have a pivot table, you might want to automate the addition of a field. Pivot tables are one of Excel's most powerful features. The following dialog box appears. For this demonstration, let us copy the data from a SQL Server Table and paste it into the Excel file which we created in our previous tip on Importing Data from Multiple Data Sources into PowerPivot for Excel. After creating the pivot table, the method adds a row field and two data fields (a count and sum of the Order Total column). Click the Pivot Table which data source need to be changed from external connection to a range or table in the active worksheet. The only thing I can do is change to another external data connection. Tip: change your data set to a table before you insert a pivot table. 2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube. In that case, they can establish their own connection to the tabular database (assuming they have the necessary permissions) and import the data into a pivot table, just as we saw above. Manual Refresh Pivot Table Data Changes Excel 2010 Vba Read/Download Sample Excel VBA programming to change pivot table report filters. You can refresh the data. Skip navigation Connecting a Pivot Table to External Data Reporting Guru How to Best Import External Data into Excel & Import data from the. Select the data source (in this case, AdventureWorks Cube) pointing to a different environment (in this case, Production). Applix TM1 OLE DB MD Provider. Unlike formulas, Power Query does not pull data from one table to another. The Data Connection Wizard will pop up and request info about which server you wish to get the data from. You can also create a pivot table, which is a report that analyzes and summarizes your data. Excel Pivot tables are fantastic for creating fast and accurate, sorted, summary information in Excel. each one is associated with a different table within the workbook). More efficient and effective solutions use the Pivot Table, a tool for combining, comparing, and analyzing large amounts of data easily. Click Connect. If i choose "Chage Data Source" from pivottable ribbon, I can only choose another range, but external connection is greyed out. Order 10, Product, Category, Amount, Date and Country. But when you get to the Pivot Table wizard, select “Use and External Data Source” and then select “Choose Connection” as shown below. If the Pivot Table links to another workbook you have two options: Set another data source within your current workbook. Complete the wizard to create a data connection to your data source. My pivot table is currently pointing to an MS Access database. Scroll down to the Data Feeds section and you should find the Report option. Excel® 2016 PIVOT TABLE DATA CRUNCHING. Add fields to a pivot table. Many of the date functions in DAX are the same as Excel formulas, which makes this a snap. Select any cell in the source data table; On the Ribbon, click the Insert tab, and click Recommended Charts; Click on one of the charts samples that has a pivot table symbol at the top right. This works nicely when you need to strategically refresh only certain data sources. To manually update it, select the PivotTable and then go to Analyze > Refresh. I know that this was possible with Excel 2003. After your pivot table has been created, you can still make changes and additions to your data. It looks very similiar to a normal excel pivot table at a first glance but there are a few differences between excel cube and crosstab: - The greatest advantage of an excel cube over a simple crosstab is that the data is stored externally in a. I've created a workbook that has a pivot table in it that has the data sourced from an external connection. That is to say, if you’re on the Dim_Products tab in Power Pivot, clicking Refresh reaches out to the external SQL Server and requests an update for only the Dim_Products table. Excel automatically selects the data for you. You can also create a Pivot Table in Excel using an outside data source, such as Access. Now I want to move the old existing sheets to the new one. Excel automatically selects the data for you. Any advice would be appreciated. I an using Excel 2010. Select Excel from the Type drop-down list. Pivot table with external data source. Resolving Format Cells Change after Refreshing Data Sources in Power Query In this post I want to share my experience with refreshing data in Power Query and the way it affects loaded data to an Excel worksheet. Connection Dialog Box Use the Connection Dialog Box to build a connection string or to paste a connection string. You can click the icon on the right side of this field to change the Table/Range value if Excel guessed wrong. Can someone tell me if there is a way in VBA to change the data source fron existing Excel 2010 Pivot Table from an external. >zero"Y";;;. I need to update the location of the external data source each month. You can place it in a new worksheet or in an existing worksheet. The only thing I can do is change to another external data connection. pivot table data source copy and paste the pivot table as values, actually it will be no longer a pivot table just a formatted data set, be cautious if there is a GETPIVOTDATA formula referring to that pivot table it evaluates to #REF error; of course it breaks the "obvious" external links at the very end. You can use a Pivot Table as a database in the same way that you can use Simple Tables or Excel Tables. Enable download: Needs to be set to On in order to retrieve or download the data from the source. A pivot table is a table of statistics that summarizes the data of a more extensive table (such as from a database, spreadsheet, or business intelligence program). A dynamic range will automatically expand and shrink the table as you add or remove data, so won't have to worry that the pivot table is missing the latest data. 5 Advanced Excel Pivot Table Techniques. row 11) which defines the external source of the data. the data is a table that was saved as an excel file. If your Power Pivot data model imports millions of lines of data from an external data source, you may well want to avoid using the Refresh All feature. This is generally only useful if your table is connected to a very large data source and you need to make many changes to the layout. the external data queries) in a workbook and update the CommandText property. In my opinion, you need to create a new PivotTable. You can also create a Pivot Table in Excel using an outside data source, such as Access. To replace a pivot table, it is advisable to refresh” it. relational connections first, because Excel will not recognize them from the Pivot Table. A pivot table allows you to extract the significance from a large, detailed data set Our data setconsists of214 rows and 6 fields. You'll then use the connection to analyse data within the database. You will then see this dialogue box:. cub file and the connection parameters in a. If you have a pivot table, you might want to automate the addition of a field. Excel 2013: Getting Data From Parametized SQL Query (vs SQL Server) I would have thought that dragging in data from SQL Server into Excel (both Microsoft products) would be easy - and it is if you're looking to drag in entire tables, views, etc. If you want to change the data source for a single Excel Pivot Table, you can use a command on the Ribbon. If the Pivot Table links to another workbook you have two options: Set another data source within your current workbook. the external data queries) in a workbook and update the CommandText property. So clicking any Refresh All button anywhere in Excel essentially completely reloads Power Pivot, refreshes all pivot tables, and updates all workbook data connections. Click Browse for Files… (bottom left) 4. Clone your pivot table and convert the clone to a straight table. If this comes from an external file that your colleague cannot access or other database, you should check they can get to that data first. Select any cell in the pivot table. When you want to perform a join between two tables, the data type of the fields you join on must be the same. I would like to create an excel spreadsheet with a database connection and a pivot table from code behind, using either C# or VB. Create a pivot chart with shortcut keys, or create a pivot chart from the source data, before making a pivot table. In order to save the connection information right click in the pivot table and select "Table options" from the menu that comes up. Did you want to change fron External Data to Table? As far as I know you cannot change from External Data to Table, if you create PivotTable from External Data you cannot change the data source from External Data. For more information, see Chapter 10, Linking Calc Data. Changing an external data source for a pivot table A while ago I was asked to build a reporting tool that consisted of twenty or so pivot tables based on an external data source. Uncheck 'Save source data when opening the file': if your data is in an external file or database then unchecking this option will not save any data to the Pivot Cache. You can easily create a pivot table report using the PivotTable and PivotChart Wizard. > That done, I went into the first pivot table and selected the new connection > - Pivot Table Tools, Change Data Source, Use An External Datasourse, Choose > Connection button. In Excel, click Data > the connection you want in the Get External Data group. Connection does have the same connection string and query string, but it triggers odd behaviour. …If you want to change the data source you can select a new data source within…the workbook, but notice that the external connection options. Excel® 2016 PIVOT TABLE DATA CRUNCHING. There's a full explanation of why this is below. But when I upload this file to Power Pivot Gallery, buttons "Open new Excel workbook", "Create Power View Report" and "Manage Data Refresh" are not visible. how to connect to a pivot table to a database. If you want to change the data source you can select a new data source…within the Workbook. In other words, you don't need to get all columns within a single table. A new approach to Excel pivot tables Sep 20th 2007 In common with many of the features of the Office suite, working with Excel pivot tables has been changed both by the application of the new Office 2007 interface and by several specific enhancements. Seems like it is best to keep you large excel file in a separate workbook and fire up your pivot table in a new Excel workbook thru the “use an external data source” and when needed to update the PT then just load new data in the initial / separate workbook and refresh the PT in the separate Excel file. Order 10, Product, Category, Amount, Date and Country. then solution is create connection under Tab of Data then "Get External Data" Create Connection using "From Web" and paste the XML link into the Browser. I have a template that, as far as I know, contains no external data sources: I cannot find any cell that refers to an external source. Upon successful login, a list of Oracle Peoplesoft tables are listed, select a table that you want to link, you can also select multiple tables in this screen.