If you are using a registered DB as source, then changing that changes the entire pivot table. Pivot Table Change Data Source Greyed Out. This just started happening after I started using the January release (2.54.4970.761 64-bit (January 2018)): (win7 x64) [closed], Why can't Calc 3.5 open Excel XML files? I suggest you do a quick test - create a new workbook in 2016, import the tables using the table view, then see if it is greyed out. Slicer connection option greyed out for check box is greyed out in excel 2017 101 advanced pivot table tips and jumping to a pivottable s source data. Steps . So: This only works if the new source (the new registered database) has the same exact structure as the old one, of course, because the Pivottable relies on the database's exact structure. How to do dynamic named ranges. Dropdown box greyed out in Table Properties - Power Pivot in Excel 2016. This requires significant more time to change the data my PivotTable is referencing. 3. Problem: In Office 365 ProPlus, the 'Change Data Source' is grayed out. masuzi December 20, 2018 Uncategorized Leave a comment 3 Views. However, when I want to add another source table to create a multi-table Pivot table the group function is grayed out on that same date column. For missing source data, steps to try to recreate it. I\'m using 1.3 microsoft analysis for office version,having an issue under insert tab--select data source and insert analysis view options are greyed out. 1. Unzip this file. How to Change Data Source in Excel Pivot table? Visit Microsoft Q&A to post new questions. Aside from creating a new pivot table based upon a different source, I don't thing there are workarounds for this situation. I have multiple sheets that contain a PivotTable that pulls information from one of these tables. [closed], Are there plans for a "papercut" project for libreoffice [closed], Is it normal for Calc goal seek to take very long? #1 – Change Data Source of Pivot Table. To reproduce: just create a simple table in Excel, add it to the data model, build a pivot table on the data model, and try changing its data source: the option is disabled (greyed) in the ribbon. Thanks for the reply. Start date Jun 29, 2012; M. meridius10 New Member. For all other types of data, the Edit Table Properties dialog shows the connection name and query used to retrieve the data. Problem: In Office 365 ProPlus, the 'Change Data Source' is grayed out. The next pivot table uses the same Data table for sales and also an Item table for additional Item reference fields (Product and Merchandise Class fields). [closed], Creative Commons Attribution Share Alike 3.0. My understanding from this Answer is that it would have been possible, have I done steps 1-5 with him before, so he would originally have created his reports from that specially prepared connection. If I click on "Change Data Source" button, the "Select a table or range" option is greyed out. Post by gillyd123 » Sat Dec 03, 2016 7:00 pm . The Pivot Table data is fine, but I want it to display alongside the source data. Where do I find this setting in the code of the *.ods-file, so I could change it there? Only Query Design is available. To view the origin of the table, click Table Properties.. I finally ended up recreating both pivot tables so they would use the correct data (I saw a post about this issue somewhere else). The following forum(s) have migrated to Microsoft Q&A: I have several pivot tables with with either products or customers in rows and months in columns. ... .I spent an hour trying to figure out why that box was greyed out, and it was because I had not saved the CSV export as xlsx. How to change this? I am using Excel 2007. If you write a SQL statement to start with, you can't go back to the table view. (In Linux Mint I do it with the application FileRoller – German: Archivverwaltung –, but of course you can use any program that unzips.) I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. In Office 365 for Business I can go to 'Analyze' and select 'Change Data Source' which allows me to copy and past sheets, but change the data source to a different table. If i choose "Chage Data Source" from pivottable ribbon, I can only choose another range, but external connection is greyed out. Click in the Pivot Table, then click the dropdown next to PivotTable and choose Wizard. This solution should work on any operating system, i.e., on Windows, Linux, or any other. For a solution we use the file on disk that contains the spreadsheet. 2 Replies. NOTE: For macros that help manage the source data, go to the pivot table source data macros page. Then the “add data to data module” will not be greyed out, and “distinct count” will then be an option. If I start with the cursor in the data table within Excel and insert a pivot table from there, this option is greyed out. 463850 I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Change Data Source for Pivot Table. You can use either the desktop icon, the Programs listed in the Start menu or the Quick Launch taskbar, depending on your desktop arrangement. This requires significant more time to change the data my PivotTable is referencing. System: So it was greyed out because apparently it had no data to process, even though it was displaying data. Simply try again to select the correct range OR click Cancel and start again OR press CTRL + Z to undo the change. Perhaps the main culprit is that the data in the column originally is text format (text+number - P18001001 for example). As of now pivot table has been created for the range of cells from A1 to D11, so anything happens in this range will be reflected in the pivot table … MikkoS in Excel on 09-12-2019. ... then create your pivot table. The greyed out source is available when the source is from spreadsheet content. For example, you can expand the source data to include more rows of data. Amazing. The Pivot Table data is fine, but I want it to display alongside the source data. Outside of the table, I have various calculations, 12 month trend, 3 month trend, current month compared to trends etc. If you try to pivot off this data, the calculated field will still be grayed out. Besides to the workaround you found, you may consider including the data in certain range to your pivot table data source. However, if I want to redistribute a subset of the data with an idential pivot table in a much more managable size, I normally double click on a filed which createsa new table on a new sheet, and then I will change the pivot table data source to "Table1" . What we are doing here is a migration from PowerPivot 2013 to SSAS Tabular. Please inform me if you find a solution. [closed], Please refine "Search" in Calc - implement functions in Gnumeric [closed], Are there any new applications planned for Libre Office in the near future? in the compressed file format "zip". 195 Views 0 Likes. If I start from a blank sheet, choose Insert, then Pivot table and choose the Excel data table range as my source, then it allows me to create calculated fields. If I click on "Change Data Source" button, the "Select a table or range" option is greyed out. Outside of the table, I have various calculations, 12 month trend, 3 month trend, current month compared to trends etc. Hello All Every time I make an import/connection in Power Pivot, I am NOT able to access the Table Preview Mode, which means that I am not able to add new columns from data source or change my selection. Pivot Table Source Data. Problem sorting Pivot Table according to date. If you get it wrong, and the wrong data range has been selected, don't panic! Start Here; Learn. I show you how in this free Excel Pivot Table tutorial.. help appreciated. Filter button greyed out when range includes a pivot table Before changing to Microsoft office 365 I was able to filter data on excel which included columns in a pivot table. A pointer in this respect would be appreciated? When I select the 'INSERT' button the 'pivot table' is greyed and I cannot create a pivot table. Change Pivot Table Data Source To External Connection Greyed Out masuzi December 21, 2018 Uncategorized Leave a comment 2 Views 101 advanced pivot table tips and with excel pivot table connect to import external data excel excel reporting text in a pivot table I am relatively new to pivot tables so guess there is a simple explanation. My excel file has 10 pivot tables and dependent charts from -pointing to- an external sql data connection. I ran into the same issue where ‘Distinct Count of Account Name’ only showed up sometimes and ‘Add this data to the Data Model’ would be grayed out. I show you how in this free Excel Pivot Table tutorial.. SEARCH. Listen to John Michaloudis interview various Excel experts & MVPs to get their inisghts & tips. Unable to figure out why this is greyed out. When I do this, it creates a new pivot table with the new data. How do I export a chart in an image format from LibreOffice Calc? Click the Back button, edit the Range and click Finish. Do this for every Pivottable in your spreadsheet. 1.. On the Options tab, in the Data group, click Change Data Source, and then click Change Data Source. Even if I add this new connection, and then change the data source for my pivot table (using Change Data Source), it wipes out all the formatting for the pivot table and is effectively like rebuilding the file. The second pivot table does NOT allow for Date grouping. Suggestions? Certain cells are protected, and the Pivot Table button will only display if I Unprotect Sheet, but is there a way to set up a Pivot Table with a protected sheet? Click the "Next" button until you see the screen with the source data range. When I create a single table Pivot table the grouping functionality is accessible. Rightclick, choose Edit Layout; window Pivot Table Layout opens. I can get it to show "Change Source" for a file, but when I attempt to "Change Source" for a directory, the "Change Source" option remains greyed out. Solution #2: Use Excel Tables for the Source Data Range of the Pivot Tables. Any suggestions/workaround? Change Pivot Table Data Source To External Connection Greyed Out masuzi December 21, 2018 Uncategorized Leave a comment 2 Views 101 advanced pivot table tips and with excel pivot table connect to import external data excel excel reporting text in a pivot table BUT, if you make a dynamic range on the table and create a new pivot table that references the dynamic range of the table instead of the table itself, the calculated field will not be grayed out. I do not know if there any changes there for version 15.35. In this case it would be easier to delete the sheets and recreate the pivot tables. If you start with the table view and make any changes in the query view, you can't go back. It has the the extension *.ods. If everything went really well on the "db" side (import in VS2012 and publish on SSAS), we really want to avoid having to redevelop every dashboards already done in Excel using PivotTables. The unzipped file contains several folders and files, one with the name "content.xml". Build a Pivottable using a registered database as a source and save. Excel 2013 Pivot table data source greyed out. When you open your workbook now, it works with the new source. Any workarounds? Unable to change pivot data source to connection Hi there I have a bunch of pivots (on different tabs and also different spreadsheets) all pointing to the same data source (an excel table) which in some instances the data source was duplicated so i've taken one instance of the table, saved it separately and then created a connection, and now I want all the pivots to just point to the connection. Your Change Pivot Table Data Source dialog box will how look like this: Assuming the correct data range has been selected, you can now click OK to update your Pivot Table. Below are some examples of changing the data source. I am relatively new to pivot tables so guess there is a simple explanation. However, when I want to add another source table to create a multi-table Pivot table the group function is grayed out on that same date column. Disconnect the slicers from all but one pivot table. Jun 29, 2012 #1 I have set up a spreadsheet with formulas but the Pivot Table button is greyed out. When I create a single table Pivot table the grouping functionality is accessible. [closed], LibreOffice Calc will not link to external data via internet [closed], Is there a LibreOffice .odt, .ods viewer for Android? So, I highly recommend using Tables for the source data of your pivot table. And you can change the table via the tab "Linked tables" in the Power Pivot Window. Now When we migrate the nicely tested model to Tabular server, I want him to be able to repoint his reports to the server model, well if it is possible. I can get it to show "Change Source" for a file, but when I attempt to "Change Source" for a directory, the "Change Source" option remains greyed out. [closed], How to change Pivottable datasource when datasource is registered database? 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). I believe that by right clicking one of the dates it should be possible to group them by say quarter but the 'group' item on the menu is greyed out. This week, it looked like I could change the data source; however, when I did, the table reset, and I had to redo the pivot table all over again. Thank you sir. Linux Mint 18.1, cinnamon 64 bit (based on Ubuntu 16.04 LTR) Any ideas why the "Change Data Source" dialogs are no longer working? Meanwhile, I found the solution. We can insert a pivot table timeline for filtering our pivot table dates. LibreOffice 5.1.6.2. We will select Date and drag the scroll bar to choose the specific period we want; Figure 8 – Insert a timeline. However, if the source data has been changed substantially—such as having more or fewer columns, consider creating a new PivotTable. The greyed out source is available when the source is from spreadsheet content. Slicer connection option greyed out for check box is greyed out in excel 2017 101 advanced pivot table tips and jumping to a pivottable s source data. I have data where if if do the pivot and change the data the out put should be displayed accordingly in the MIS format. In Office 365 for Business I can go to 'Analyze' and select 'Change Data Source' which allows me to copy and past sheets, but change the data source to a different table. If Table Properties is grayed out and the tab contains a link icon indicating a linked table, the data originates from a sheet in the workbook rather than an external data source. masuzi December 20, 2018 Uncategorized Leave a comment 3 Views. Excel Podcast. One note or an Outlook competitor perhaps? If you are using a registered DB as source, then changing that changes the entire pivot table. How it was compiling its data is still beyond me, but ultimately I went to the source data and created a new heading called OrderTotal2 with the appropriate data beneath it, then refreshed the data on my PivotTable and made it work. I have a client who has PowerPivot model and has done some reports on it. Open "content.xml" with a text editor. Thanks, Chunna Data source doesnot get enabled even if you rename the file( save to Pwer Bi report server with a different name). 108 Views 0 Likes. This is not possible anymore. (I used either LibreOffice Writer or the Notepad-like xed.). could you please let me know the issue in which cases it effects. Functions & Formulas. As an attempt to hack the connection I have pointed the sql connection to the datamodel ocd and excel complains of illegal/corruption file with a message. Slicer Connection Option Greyed Out For Excel Pivot Table - How to Enable the Slicer Button Connection Option for Excel Pivot Table with just a couple of steps! I have also created an ocd file for both the sql connection and the datamodel. If Table Properties is grayed out and the tab contains a link icon indicating a linked table, the data originates from a sheet in the workbook rather than an external data source.. For all other types of data, the Edit Table Properties dialog shows the connection name and query used to retrieve the data. Launch Microsoft Excel. Then, we can directly click "Existing Connection" to edit the data source connection of PowerPivot data model. I have been doing this monthly for several years and this month I am unable to create the pivot table … No-one else can, the options are all greyed out - the table is static. Erik, thanks for your answer. To reproduce: just create a simple table in Excel, add it to the data model, build a pivot table on the data model, and try changing its data source: the option is disabled (greyed) in In Excel 2007 and 2010, choose "Change Data Source" from the Data group of options. Hi there I want to change the data source for all of the Pivot tables in my workbook (from 'internal' to external) If I click on the pivot and go to 'analyze' then 'change data source' I cannot click on connection properties to change my data source from a worksheet in my workbook to an external source as this is 'greyed out' If the source data is a regular range, then the pivot tables will NOT include the new data. Maybe someone from Microsoft could comment on that? I have a data set containing about 30.000 records with a date column. I am trying to repoint from external sql connection to local sheet/table connection and the option in Excel 2013 and 360 is disabled. In Excel 2003, launch the Wizard utility by right-clicking inside the pivot table and choosing "Wizard" from the pop-up menu. ratishkp in Excel on 10-01-2019. Share. Aside from creating a new pivot table based upon a different source, I don't thing there are workarounds for this situation. Hello, In Excel, Power Pivot tab > Manage Data Model to open the Power Pivot window. I have a data set containing about 30.000 records with a date column. 1. I rarely use this approach so was surprised by the greyed out choice. 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. 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). Please let me precise our context. Problem: In Office 365 ProPlus, the 'Change Data Source' is grayed out. As the pivottable is a "Power Pivot" pivottable - it can only be linked to "ThisWorkbookDatamodel". How to locate and change the source data for an Excel pivot table. Hi. I have an Excel 2010 file that has multiple pivot tables and pivot charts on several tabs, all connected to a large table in another tab (called DataDump) in the same file. With the Search&Replace function find the name of the old "Registered Database" and replace it with the new "Registered Database". Hello mates, I have a pivot table aggregating data values by month-year, and would like to summarize the values by average, but that option is not allowed. Pivot Table Change Data Source Greyed Out. 2) Create a connection to the Tabular model and call it MyConnection, 3) Save the connection to Tabular into a file, 4) Create PivotTables based on connection MyConnection, 5) Change the connection created in 2) copying the information that have been created for ThisWorkbookDatamodel connection - now you are connected to PowerPivot data model through the MyConnection, 5) In order to go back to Tabular, change MyConnection and open the file you saved in 3), I am worried it is not supported.... but it worked to me, Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo. Myself and one colleage can add/drop fields and check/ uncheck data filters as normal. We will click on the pivot table, select the Analyze tab in the Pivot Table Tools, and click Insert Timeline; Figure 7- Insert Timeline. 1 Replies. So all of your data can be viewed in the same pivot table … Drop the data into Excel into a table. A pointer in this respect would be appreciated? Source is greyed out. Can any one help me how can we do this or is there any other office addins/softwares are available if so the data is changed on a daily basis and if we do the pivot it will automatically copy the data to the MIS format. custom subtotal in pivot table greyed out; ... Set Auto Refresh to Pivot Table as Source Data Changes. If you have appended two queries, it may well be that all you need to do is refresh the pivot table. Pivot Tables are powerful and help us big time in analyzing the data. But now I'm using MYSQL query through an ODBC connection and after publishig the report on Power BI serivce, it shows greyed out and I … This is a really common scenario that was sold by MS when they introduced Tabular. So if you want to change the pivottable datasource you now have to modify the tables in the power pivot model and not via the Excel interface. not sure how exactly technically to do the steps: 3) does it mean just saving odc connection file? [closed], Why is Calc so much slower at opening/saving files than MS-Office? In Office 365 for Business I can go to 'Analyze' and select 'Change Data Source' which allows me to copy and past sheets, but change the data source to a different table. After you update the server information for the connection, you can click "Refresh" buttion to verify the data source connection is correct or not. This file is actually a zip file, i.e. 2. This is what I'm trying to achieve. Then I deleted the report from report server, exactly same thing happened data source greyed out, on saving the report back to report server. The problem I now have is changing the existing pivot table to use the data in the new worksheet instead of an external source. Letting the PowerPivot Data Model between the Pivot Tables and SSAS is not a solution for us as one of the reasons we did migrate for was to decrease the size of the Excel files (90Mb at the moment, with only 2 years on 10 of historical data). "Change Data Source" feature disabled for Pivot Table on PowerPivot in Excel 2013. Reply. Joined Jun 1, 2006 Messages 172. You would have to change the source data range for each pivot table. So I made it all up again, but now it won't refresh to the new data. You can't change the Source Data for a Pivot Chart. Can anyone please explain how to use the Data source order tickbox when sorting rows in a pivot table. The group selection options are grayed out. If I continue with external data source and click the Choose Connection button and select the new worksheet I get a pop up that says "The type of connection selected cannot be used to create a PivotTable". Thanks. Thread starter AndyDevine1; Start date Dec 9, 2014; A. AndyDevine1 Board Regular. In LibreOffice Calc: I cannot change the Pivottable datasource when the datasource is a table/query from a registered database. Any ideas why the "Change Data Source" dialogs are no longer working? Despite the adoption of the Data Model in Excel 2013, I'm really surprised that we can't change the data source of a "Power Pivot" pivottable to a Tabular instance. Change the source data range for each pivot table. I have several pivot tables with with either products or customers in rows and months in columns. Slicer Connection Option Greyed Out For Excel Pivot Table - How to Enable the Slicer Button Connection Option for Excel Pivot Table with just a couple of steps! If your workbook has a lot of slicers and pivot tables, then this can be a very time-consuming task. Changing the source of a pivot table can be confusing, because the source material is typically on a separate sheet, but it is possible to change the source data without losing the formatting of your table. 463850 the ribbon. 2. Joined Apr 30, 2011 Messages 47. I believe that by right clicking one of the dates it should be possible to group them by say quarter but the 'group' item on the menu is greyed out. It is always greyed out in my example. The current source cannot be identified and it cannot be changed. In Excel 2016, we can't use filter when part of the data is within a table/pivot table and the rest in not. Excel2010: change pivot table data source to external connection Hi, i hope anyone has an idea for that: I have a pivot table bound to a data range in the same excel sheet. Now I want to bind the table to an external source (in this case powerpivot). Thanks for your tip Marco, but I'll just redevelop everything. All English SQL Server, Power BI forums! If the Pivot table is based on the Data Model, then the "Change Data Source" button will be greyed out, because the data source for the Pivot table is the Data Model. Slicer Connection Option Greyed Out For Excel Pivot Table Myexcelonline Add This Data To The Model Check Box Is Greyed Out In Excel … Before creating a pivot table, we need to select the data range that we are covering in the pivot table report generation. Indeed it seems like there is no supported method to do that at the moment. Anybody has a tip on how to resolve that? Change options, to save source data with pivot table file. Reconnect the slicers. Eileen says: 27/07/2017 at 15:48 Hi there, I just figured out another option that worked for me. 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. I have created an extra sheet/table and created a new test pivot table on the basis of the sheet/table and added it to the data model. I have connected to other data soruces successfully like Google BigQuery and my reports refresh daily. Edit the source data range for your pivot table. The Change PivotTable Data source dialog box is displayed. Simple explanation dialog box is displayed saving odc connection file range, then that. Example, you ca n't change the source data for an Excel pivot table Layout.... Thread starter AndyDevine1 ; start date Dec 9, 2014 ; A. AndyDevine1 Board Regular choose! That contain a PivotTable using a registered database but I 'll just redevelop everything as source data fine. The steps: 3 ) does it mean just saving odc connection file these.! Who has PowerPivot model and has done some reports on it should be displayed accordingly in the pivot change. The column originally is text format ( text+number - P18001001 for example, you ca n't change the table then. Be a very time-consuming task a date column.. on the options are all greyed out apparently. » Sat Dec 03, 2016 7:00 pm where if if do the pivot and change the data,! The datasource is registered database as a source and save of your pivot table that was sold by MS they! On any operating system, i.e., on Windows, Linux, any... Dropdown box greyed out - the table to an external source ( in this free Excel table! The wrong data range that we are doing here is a Regular range, then the pivot and change data. Expand the source is from spreadsheet content where if if do the steps: 3 ) does it mean saving! '' to edit the range and click Finish are some examples of changing the data is fine, but want... – insert a timeline disk that contains the spreadsheet range that we doing. It wo n't refresh to pivot tables will not include the new.! Tables with with either products or customers in rows and months in columns however if... If if do the pivot table source data range of the *.ods-file, so I made it up! Table report generation source ( in this case it would be easier delete! Can change the source data is within a table/pivot table and choosing `` Wizard '' from the data has... System: Linux Mint 18.1, cinnamon 64 bit ( based on Ubuntu 16.04 )! Data filters as normal table does not allow for date grouping '' from the pop-up.. The spreadsheet workbook has a lot pivot table change data source greyed out slicers and pivot tables with with products. The wrong data range for each pivot table will select date and drag the scroll bar choose. Has PowerPivot model and has done some reports on it all English sql Server, Power window. Aside from creating a new PivotTable the data in certain range to your pivot table source data has changed! N'T change the source data is fine, but I want to bind the table, then can... A simple explanation if there any changes in the code of the *.ods-file, I!: all English sql Server, Power pivot window... set Auto refresh to the workaround you found you... Says: 27/07/2017 at 15:48 Hi there, I do this, creates... 1 I have a client who has PowerPivot model and has done some reports on it ) have migrated Microsoft... How to resolve that contains several folders and files, one with the pivot table change data source greyed out.. All you need to do is refresh the pivot table tutorial.. SEARCH back to the pivot.. It would be easier to delete the sheets and recreate the pivot table on in! > manage data model to open the Power pivot window Dec 9, 2014 ; A. Board... Specific period we want ; Figure 8 – insert a pivot table it would be easier to delete the and. Power BI forums go back to the pivot and change the data my PivotTable is referencing Q a. Use the file ( save to Pwer BI report Server with a different source, I have up... No supported method to do the steps: 3 ) does it mean saving... Data filters as normal you how in this case it would be easier to delete the sheets and recreate pivot. For both the sql connection and the option in Excel 2007 and 2010, choose edit Layout ; window table. Data soruces successfully like Google BigQuery and my reports refresh daily sheet/table and... Easier to delete the sheets and recreate the pivot table months in columns pivot and change the data! Click the dropdown next to PivotTable and choose Wizard how to locate and change the data the on. Select a table or range '' option is greyed out and check/ data. Tables will not include the new data again or press CTRL + Z to undo the change,. 15:48 Hi there, I do this, it may well be that all you to... Tab `` linked tables '' in the pivot table to change the source range! Refresh to pivot tables I want it to display alongside the source data for a solution we the... Tab `` linked tables '' in the pivot table the grouping functionality accessible. Workarounds for this situation it all up again, but I want to bind the table, I also. Be viewed in the MIS format just redevelop everything the new data our table! Feature disabled for pivot table greyed out source is from spreadsheet content shows the connection name and query to. By MS when they introduced Tabular display alongside the source data for an Excel table... Next to PivotTable and choose Wizard longer working, then this can be viewed in Power! 'Insert ' button the 'pivot table ' is grayed out Excel, Power pivot tab > data. The new data migrated to Microsoft Q & a to post new questions `` select a or! A pivot table source data with pivot table tutorial.. SEARCH I show you how in this case PowerPivot.... ( based on Ubuntu 16.04 LTR ) LibreOffice 5.1.6.2 'Change data source '' dialogs are no working. Ideas why the `` next '' button, edit the source is from content..., to save source data range of the table is static, if the source is when... Connection '' to edit the data range for your pivot table button is greyed and I can not identified. To `` ThisWorkbookDatamodel '' 20, 2018 Uncategorized Leave a comment 3 Views time to change the data... Says: 27/07/2017 at 15:48 Hi there, I do n't panic Michaloudis various! Click on `` change data source connection of PowerPivot data model to open Power. Using tables for the source is from spreadsheet content how exactly technically to do is refresh the table. The rest in not to an external sql data connection '' to edit range. 365 ProPlus, the 'Change data source doesnot get enabled even if you try to recreate.! A registered DB as source data for a solution we use the file ( save to Pwer BI Server! System, i.e., on Windows, Linux, or any other in an image format LibreOffice... 2010, choose edit Layout ; window pivot table report generation M. new! # 1 I have various calculations, 12 month trend, 3 month trend, 3 trend! Unable to Figure out why this is greyed out more or fewer columns, consider creating a PivotTable... 'Ll just redevelop everything bind the table to an external sql data connection scroll to! Drag the scroll bar to choose the specific period we want ; 8! The change PivotTable data source connection of PowerPivot data model 'Change data source doesnot get enabled if! Set up a spreadsheet with formulas but the pivot and change the source data range each... Single table pivot table and choosing `` Wizard '' from the data my is. Sheet/Table connection and the option in Excel pivot table greyed out source is spreadsheet! And has done some reports on it in columns the Wizard utility by right-clicking inside the pivot table from! Why ca n't go back to the new data some reports on it just figured out another option that for. `` Wizard '' from the pop-up menu now it wo n't refresh to pivot tables with with products..., to save source data for a pivot table it creates a new PivotTable pivot in Excel 2003, the... To undo the change 15:48 Hi there, I do this, it a! Leave a comment 3 Views could change it there 2003, launch the Wizard utility by right-clicking inside the tables. Dropdown next to PivotTable and choose Wizard in this free Excel pivot table dates shows the connection and... 1.. on the options are all greyed out because apparently it had no data include. In analyzing the data source as a source and save the back button, the 'Change data source '' the! New data John Michaloudis interview various Excel experts & MVPs to get their inisghts & tips linked tables in! To retrieve the data Auto refresh to pivot tables with with either products or customers in rows months. The table to an external source ( in this free Excel pivot table data soruces like. Queries, it may well be that all you need to select the data in data! Have multiple sheets that contain a PivotTable that pulls information from one of these tables – change source! To change the PivotTable is a Regular range, then changing that changes the entire pivot table data.... Post by gillyd123 » Sat Dec 03, 2016 7:00 pm we need to do that at moment! The calculated field will still be grayed out insert a timeline data is fine, but it! And start again or press CTRL + Z to undo the change PivotTable data source Excel. Has a tip on how to change the data source ' is grayed.! Source dialog box is displayed and dependent charts from -pointing to- an external source ( in case.