Forum Discussion
Keeping slicer report connections as I add new data to the tab driving the pivot tables
I have an Excel file with multiple tabs which I use as a template. The file has one tab (Data Tab) where I place raw data which is used to drive pivot tables. The columns are always the same name and in the same order, but rows can vary between 100 to 16,000. I then have three other tabs (Pivot Tab 1, Pivot Tab 2 and Pivot Tab 3) with each tab having 4 pivot tables along with 5 different slicers for those pivot tables. The 5 slicers are the same across all three pivot table tabs. My issue is I have to unlink the Report Connections between all the pivot tables and all the slicers before putting new data in the Data Tab before I refresh the pivot tables on the Pivot Tabs. I would like to keep the Report Connections as-is and just paste my new data set into the Data Tab, refresh the pivot tables but NOT have to reconnect each slicer to each pivot table every time. Again, the only difference is the # of rows from my data file, everything else is the same. Can I do this? Thank you.
- My guess is that you're pasting the entire table, including the headings. This may confuse Excel. Better to just paste the data, excluding the headers. And while you're at it, if you haven't done so yet, format that range as a table and base your pivots on the table.
JKPieterse Thanks for the answer. I haven't done the table thing, but was thinking that might be the way to go. You're right, I am just copying/pasting the data rows from my data sheet into the tab which drivers the pivot tables and slicers. I will give this a shot. Thanks!
- JKPieterse Thanks again! I changed the Data Tab to a table and that has fixed this issue and has made the file much quicker to complete and adding additional data analysis.
