![]() ![]() In the create PivotTable dialog box, you need to configure as follows. Go back to the worksheet named “ August”, click on any cell in the table and go to click Design > Summarize with PivotTable.ġ1. Create pivot tables from different data setsġ0. Change the table name in this newly created worksheet to match the column header, which is “ Product”. In the popping up Create Table dialog box, check the My table has headers box and then click OK.ĩ. After removing duplicates, select the remained data cells and press the Ctrl + T keys. Then a dialog box pops up to tell you how many duplicates are removed from the list, click OK to close the dialog box.Ĩ. In the Remove Duplicates dialog box, click the OK button.ħ. Keep the data selected in the newly created worksheet, click Data > Remove Duplicates.Ħ. Press Ctrl + C keys to copy the data, then press the Ctrl + V keys to paste the data in the newly created worksheet.ĥ. Here I select the Product column in the worksheet “ August”. Go back to any worksheet containing data, such as “ August”, select the column data that you want to use to create the slicer. Create a new worksheet by clicking the New sheet button and rename the newly created worksheet as you need.Ĥ. August and September.Ĭlick on any cell in the table, go to the Design tab, and then modify the table name in the Table Name text box.Ĭreate a helper column for creating the slicer and convert to tableģ. Here I change the table names to match the names of the worksheets, i.e. After converting the ranges to tables, please rename the tables with descriptive names. If not, you need to convert them to Tables.ģ) Repeat the above step 1) and 2) to convert the data range in the worksheet “ September” to Table. Make sure both the data ranges are in Table format. Note: Both worksheets should have the same column data that used to create the slicer.ġ. The method in this section can do you a favor. Also, you need a single slicer to control both the pivot tables. If the pivot tables come from different data sets, you need to handle this task in a different way.Īs shown in the screenshot below, supposing you have two worksheets ( August and September) containing monthly sales for different months, and the data in these two tables needs to be analyzed by different pivot tables. The above pivot tables all come from the same data set. Now the slicer is connected to both the pivot tables. ![]() ![]() In the Report Connections dialog box, check the pivot tables that you need to connect to this slicer at the same time, and then, click the OK button. Right click the slicer and click Report Connections from the context menu.Ĩ. ![]() Since the slicer is now only connected to the PivotTable1, you need to link it to the PivotTable2 as well. Then a slicer is inserted into the current worksheet. In the opening Insert Slicers dialog box, tick the column you want to use to filter both the two pivot tables, and then click OK.ħ. Click on any cell in one pivot table, such as PivotTable1, and then go to click Analyze (under the PivotTable Tools tab) > Insert Slicer.Ħ. Now you need to insert a slicer (this slicer will be used to control both pivot tables). Here I created two pivot tables as shown in the screenshot below.ĥ. Go ahead to insert the second pivot table for the table range. In this case, I drag the Date field from the field list to the Rows area and drag the Sales field to the Values area. Please drag the needed fields to the target areas one by one. Now you need to add fields to your pivot table. In the opening Create PivotTable dialog box, specify where to place the pivot table in the Choose where you want the PivotTable report to be placed section, and then click the OK button.ģ. First, you need to create two pivot tables based on the table range.Ģ. This tutorial provides two methods for you to connect a single slicer to multiple pivot tables, not only from the same data set, but from different data sets.Ĭonnect a single slicer to multiple pivot tables from the same data setĬonnect a single slicer to multiple pivot tables from different data setsĪs shown in the screenshot below, there is a sales table in the range A1:H20, now, you want to create two pivot tables to analyze the data, and then use a single slicer to control these two pivot tables. In some cases, to make pivot tables work more efficiently, you may need to use a single slicer to control more than one pivot table in your workbook. How to connect a single slicer to multiple pivot tables in Excel?īy default, an Excel pivot table slicer is only connected to the pivot table that you are inserting the slicer from. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |