Excel pivot table, used to analyze summary data is very powerful, combined with the slicer, you can filter and view different dimensions at any time, strong combination
1. Pivot table slicer
For example, the table on the left is the sales business flow data, which records the time, month, region, product, quantity, and amount
The boss allows you to quickly summarize the total number and total amount of various goods sold
At this time, we only need to select any cell data, and then under the Insert tab, select the pivot table:
For the sake of demonstration, here we put the pivot table in the position of cell H2
Then we just need to put the item field in the row label and the quantity and amount fields inside the value
Get the results you want:
At this time, the boss made a request, and I wanted to filter the months
If we just put the month field in the filter field and then check the filter, it would be a bit low
We can do something more advanced and use slicers to filter:
Let's select the pivot table, and under Analytics, click Insert slicer, and then select Month
At this time, a filter button will be screened, and the data will automatically change where the point is reached, making it more convenient to filter and present the results
2. Linkage of slicers
At this time, the boss put forward another demand, and he also wanted to know the number and amount of sales in each region
Then we can do the same thing, we can insert a pivot table
It's just that at this time, the row label is put into the area, and the quantity and amount are also placed in the value, and the following result is obtained:
However, at this time, the slicer we created before, after clicking, it will only control the first pivot table, and the second pivot table cannot be updated in linkage, so our slicer, no matter how you choose, the following pivot table content will not display the data of the corresponding month:
If we want a slicer, filter the contents of both pivot tables
We just need to select the slicer, then select the report connection above, and then select the 2nd pivot table
With this connection, we can use a slicer to control the data of two pivot tables at the same time:
Have you learned this little trick? Try it out!