Slicers are normally used to change filter selections in a report, but a less well-known use is for report navigation, to allow users to jump to another location in the workbook.
What are slicers in Excel?
Slicers in Microsoft Excel serve as software filters designed to enhance data analysis when working with Excel tables or pivot tables containing substantial datasets. Beyond the basic function of data filtering, slicers facilitate a user-friendly comprehension of the extracted information presented on the screen.
It is noteworthy that Microsoft Excel Slicers seamlessly integrates with both Windows and Macintosh operating systems. In the subsequent discussion, we will delve into the practical aspects of constructing Excel slicers to optimize data manipulation and visualization.
How to Create Slicer in Excel
Here I have a report with several sheets. I would like to hide all the tabs at the bottom upon publishing and just have a page navigation at the top of each sheet.
To do this, I create a separate sheet containing the names of all the sheets that I want to be able to navigate between. Create an Excel slicer using this range that outputs the selected sheet to another cell.
I can now create an XL3Link that will link to whatever sheet is currently selected in the slicer. It uses an XL3Address that combines the outputted name with “!A1” as its link location argument.
=XL3Link(XL3Address(A8 & “!A1”), “Link”)
So now, changing the slicer and then clicking the link takes me to the selected sheet.
We would like the slicer to take us straight to the relevant sheet, without having to click the link after each selection. To achieve this, go back into editing the slicer and select ‘Activate XL3Link’, choosing the cell containing the link we just created.
Now, whenever the slicer is changed, the link is immediately activated and we are taken to the selected sheet.
I can copy this slicer into each sheet for easy navigation wherever I am in the workbook (right click on the slicer, select ‘Copy’ and then choose the location to copy to). Because each slicer outputs to the same cell, all the slicers stay in sync. I could of course also do this with a workbook slicer so it’s automatically present on each worksheet.
