With the second pivot table (CountryYear) selected, weвЂ™ll insert a slicer. Renaming pivot tables in the Pivot Table ribbon.Д¬reate a second pivot table by pasting a copy of the first.ДЄfter pasting a copy of the first pivot table into the worksheet, we can go the pivot table field list and replace the rows dimension with RegionCountryName. In this case, I have dubbed the pivot tables CategoryYear and CountryYear. Although most Excel users accept the default names assigned by Excel for their pivot tables, I think itвЂ™s a good idea to give them more descriptive names. Once we have pasted this copy, we can remove the product category from the first column of the pivot table and replace it with RegionCountryName from the Geography dimension. We then paste a copy underneath the first. Once we have created this pivot table, we highlight it and hit C. First pivot table showing yearly sales by product category. In this example, this will be a pivot table that shows sales of product categories by year. In my opinion, it is best to choose a pivot table that will not change its number of rows for the top position on our worksheet. We would like to view the sales data for individual countries, but we might also like to group the countries by continent or by some other attribute. Sample data in this database covers the years 2007, 2008, and 2009. For our demonstration, we will query a Contoso Retail database running on the Analysis Services in tabular mode. Often, this will be a dimension not affected by the slicer. First we must identify a the pivot table dimension that will be shared by both pivot tables. We will imagine that we would like to examine sales by year for both product categories and countries. This can be achieved by simply copying and pasting a pivot table. The key to having two pivot tables controlled by the same slicer is to get two pivot tables to share the same pivot table cache. The pivot table cache is a data structure created in memory from a query, and it is this pivot table cache that is altered when a slicer filter is applied. Under normal circumstances if you create several Excel pivot tables each will get its own separate pivot table cache. This is easy to do when one understands the inner workings of pivot tables. There are many occasions when it would be valuable to have two (or or three or possibly more) Excel pivot tables respond to changes in a single slicer filter.

Excel remove slicer cache