How to Use an Excel Slicer Selection in a Formula or Cell
Question details
The user needs to retrieve the selected value from an Excel Data Model slicer to use it dynamically inside a formula or a specific cell.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Attempting to read a slicer selection dynamically for downstream calculations, specifically when connected to an Excel Data Model where standard referencing functions fail.
- Observed behavior
- Standard functions like INDIRECT do not work for Data Model slicers. The goal state is to extract the selected item, such as a date or time, using specialized cube functions.
Ensure that your PivotTable and Slicer are connected to a Data Model. Note the exact internal name of your slicer from the Slicer Settings, as you will need it to write the extraction formulas correctly.
Extract Slicer Selection using CUBESET and CUBERANKEDMEMBER
Use a combination of Cube functions to read the active selection from a Data Model slicer and display it in a target cell.
When dealing with a Data Model PivotTable, regular formulas like INDIRECT cannot read the slicer state. Instead, you must use the CUBESET function to gather the selected items into a set, and then use CUBERANKEDMEMBER to display the specific selection.
Select an empty cell (for example, E10). Type the formula =CUBESET("ThisWorkbookDataModel", Slicer_YourSlicerName, "Caption"). Replace 'Slicer_YourSlicerName' with the exact internal name of your slicer (e.g., Slicer_PI_ID1_UpdateTime1).
In the cell where you want the actual slicer value to appear, type the formula =CUBERANKEDMEMBER("ThisWorkbookDataModel", E10, 1). This retrieves the first selected item from the set you created in the previous step.
If the formula returns an #N/A error, right-click the slicer, click 'Slicer Settings', and verify that the 'Name to use in formulas' exactly matches the name used in your CUBESET formula.
Easily Manage Data Models and Slicers in WPS Office
WPS Spreadsheet provides robust support for PivotTables, Slicers, and advanced formula calculations, allowing you to manipulate and extract your data seamlessly.
- 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the PivotTable and Data Model.
- 2. Insert a Slicer: Select your PivotTable, navigate to the Analyze tab, and click 'Insert Slicer' to filter your data visually.
- 3. Apply Formulas: Use standard or advanced functions within the WPS formula bar to link your slicer selections to your dashboard cells effortlessly.
Frequently Asked Questions
Why does my CUBESET formula return an #N/A error?
This usually happens if the slicer name in the formula is incorrect or if the PivotTable is not added to the Data Model. Right-click the slicer, open 'Slicer Settings', and ensure you are using the exact 'Name to use in formulas' (which often begins with 'Slicer_').
Can I use the INDIRECT function to read a slicer selection?
No, if the slicer is connected to a Data Model, the INDIRECT function cannot interact with it directly. You must use Cube functions or a VBA macro to retrieve the value.
How do I find the internal name of my slicer for the formula?
Right-click on the slicer and select 'Slicer Settings' from the context menu. Look for the text box labeled 'Name to use in formulas'. This is the exact text string you must reference in your CUBESET formula.
What happens if I select multiple items in the slicer?
The CUBESET function will capture all the selected items as a set. You can then use the CUBERANKEDMEMBER function with different rank arguments (e.g., 1 for the first item, 2 for the second) to extract them individually into separate spreadsheet cells.
