Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • Excel Range Slicers


    In the scenario where a Slicer needs to be used to allow the selection of an item from a range of cells, then have the corresponding selection passed to another report as a parameter, Excel range driven slicers can be useful.

    1. The screenshot below shows a selection list containing two continents and some of their children, each with a corresponding region code. When the user selects a region, its corresponding region code should be displayed.
    2. ExcelRangeSlicerRange.png
    3. This can be done through an XLCubed slicer. Create a slicer as follows:
    4. Select the XLCubed > Slicer > Excel ribbon item.
    5. ExcelRangeSlicers0.png
    6. Set the Slicer range to =$A$1:$C$7. This range of cells contains three columns: the region code, the caption, and the depth to be used for each item. The depth only applies to the treeview-style slicer; select it here.
    7. ExcelRangeSlicers1.png
    8. In the Properties section, check Update range with selection, select First Column and enter the cell location where you want the country code to be displayed. In our example it is B11.
    9. ExcelRangeSlicers2.png
    10. The Slicer is created using the selections that we have chosen:
    11. ExcelRangeSlicerSlicer.png
    12. When the user clicks on a slicer option, in this case France, its corresponding country code is displayed in cell B11.
    13. ExcelRangeSlicers3.png


      Alternative Layouts

      If you need a tree slicer your data may not be in the format specified above. In this case XLCubed can also accept a number of other formats.

      The following examples all produce the same result:

      Exceltreeexample.png

      Parent Column

      The data has three columns: Key, Text, ParentKey. Top level items have no parent, children refer the their direct parent. There can be multiple layers of nesting.

      Exceltreeparent.png

      One Column Per Level

      The data is in a table with a column for each level of the tree. Parents are repeated in the data when they have multiple children.

      Exceltreeonecolumn.png

      Key and Caption Column Per Level

      Data is in a similar shape to the "one column" layout, but there are two columns per level of the tree, allowing for items to have a key that is stored but not displayed.

      Exceltreetwocolumn.png