Click a cell to filter multiple worksheets or workbooks automatically
Kutools for Excel
Boosts Excel With 300+
Powerful Features
Supposing, you have multiple workbooks, one is the main workbook which contains the product profile, and other workbooks contain the detail information of the product. Now, you want to create a real-time filtering between these workbooks and view the workbooks side by side. It means when clicking one cell in the key column of the primary workbook, all corresponding data in the other workbooks will be filtered immediately as below demo shown.
If you want to achieve this filtering effect, Kutools for Excel supports a powerful feature – Link Sheets, with this feature, you can correlate the data in multiple workbooks and view the sheets side by side in real-time filtering.
Click a cell to filter multiple worksheets or workbooks automatically
Please do with the following steps to achieve this operation:
1. Open the main workbook that you want to correlate with another one.
2. Then, click Kutools > Link Sheets, see screenshot:
3. And a prompt box popped out, please click OK button.
4. In the opened Manage Scenario dialog box, click Create button to create a new scenario, see screenshot:
5. In the Create A Scenario window, the active sheet is displayed in the Primary Worksheet section, specify the key column header from the Primary Key Column drop down that you want to link to other sheets based on.
6. And then, click Add button to add other workbooks that you want to link with the main sheet into the Secondary Worksheet list box. Then, select the sheets and key columns from the secondary workbooks that you want to filter.
- If you want to delete specific slave workbook in the list box, please select it, and then click Delete button.
- To remove all slave workbooks in the list box, please click Clear button.
- Clicking Example button at the bottom left corner of this window will open two sample workbooks which help to demonstrate the effect of the feature.
7. After finishing the settings, click Ok button. And a prompt box will pop out to remind you if you want to save the scenario, see screenshot:
If you click Yes to save, in the following dialog box, type a scenario name, and if you click No, this setting will not be saved.
8. After saving or not saving the scenario, another prompt box is popped out to ask if you want to open the scenario, click Yes button to open current scenario, see screenshot:
9. All the sheets will be arranged based on the number of the sheets. For instance, if there are two sheets, they will be displayed vertically side by side; if there are three sheets, they will be displayed left and right; if there are more than three sheets, they will be displayed in cascade layout by default.
Now, when you click any cell in the main workbook, all corresponding data in the secondary worksheets will be filtered out at once based on the clicked cell, see below demo:
- Link Sheets Tool: This menu will be displayed on the top right of your window when enabling the Link Sheets feature.
Enable/Disable: Click this button to enable or disable the Link Sheets feature.
Open scenario: Click this button to open the Manage Scenario dialog box.
Arrange: Click this button to arrange the layout of the sheets. You can arrange the sheets in horizontal, vertical, left and right, cascade layout as you need.
Toggle Workbook: Click this button to toggle between the secondary workbooks.
Close: Click this button to close this feature. - Manage Scenario dialog box:
Open button: Select one scenario in the list box and click this button to open and activate it.
Create button: Click this button to create a new scenario you need.
Rename button: Select one scenario in the list box and click this button to rename it.
Delete button: Select one scenario in the list box and click this button to delete it.
Clear button: Remove all scenarios in the list box at once.
Enable/Disable: Click this button to enable or disable the Link Sheets feature.
Open scenario: Click this button to open the Manage Scenario dialog box.
Arrange: Click this button to arrange the layout of the sheets. You can arrange the sheets in horizontal, vertical, left and right, cascade layout as you need.
Toggle Workbook: Click this button to toggle between the secondary workbooks.
Close: Click this button to close this feature.
Productivity Tools Recommended
Office Tab: Use handy tabs in Microsoft Office, just like Chrome, Firefox, and the new Edge browser. Easily switch between documents with tabs — no more cluttered windows. Know more...
Kutools for Outlook: Kutools for Outlook offers 100+ powerful features for Microsoft Outlook 2010–2024 (and later versions), as well as Microsoft 365, helping you simplify email management and boost productivity. Know more...
Kutools for Excel
Kutools for Excel offers 300+ advanced features to streamline your work in Excel 2010 – 2024 and Microsoft 365. The feature above is just one of many time-saving tools included.
