This article will explain to you the tricks to connect a slicer with the Data Type slicers

What's the Data Type?

Data Type is a powerful tool, available only with Excel 365 and Excel Online, to connect the content of your cells with an external database.

For instance, let's write about some countries in a range of cells and click on the tool Data > Geography

Then, an icon, representing a flag, is automatically added to the cell. This is proof that the name is connected to the geography database of Microsoft.

And then, you just have to write the following formula to display all fields you can add to your country name. Yes, just a dot opens the list of fields 😎👍

=A15.

Finally, if you select the Area field, you display the area for each country

Use Slicers to select your Data Type

Now, if you want to build a dashboard where you can select a Data Type field with a slicer, you must proceed like this.

Step 1: Write the fields in a Table

Slicers can be used with Pivot Tables or Tables only. So here, we will write the Data Type's fields we want in a table. Of course, you must respect the writing of the field name. Otherwise, it won't work 😒

But also, and that's the first trick, we must create for each field and numerical ID (1, 2, 3, ....)

Also, it's recommended to rename your table (it's better when you read the formula).

Step 2: Add a Slicer

Here, nothing complex.

1. Select one of the cells of your Table
2. Go to the Table Design menu
3. Select the option Insert Slicer

4. Select the field you want as slicer

Then, a slicer with the field name is displayed in your worksheet

Step 3: SUBTOTAL formula

The second trick is to use the SUBTOTAL function. Why this function? It's because this function adapts to the applied filters 😉

=SUBTOTAL(9,tbl_Fields[Col ID])

Argument 9 means the formula will return the sum of the selected field. Like that, it will be easy to identify the field selected.

And when you select one of the fields of the slicer, the SUBTOTAL function returns exactly the ID of the field in the Table.

Step 4: Embedded SUBTOTAL into XLOOKUP function

Of course, the ID is useful only to return the field name selected. So in our case, we will write the following formula

=XLOOKUP(SUBTOTAL(9,tbl_Fields[Col ID]),tbl_Fields[Col ID],tbl_Fields[Fields])

Step 5: Use the FIELDVALUE function

The FIELDVALUE function can be used only with Data Types

This function needs 2 arguments:

1. The reference of the cell linked to the data type
2. The cell with the field name

=FIELDVALUE($D2,Sheet2!$D$18)

Improve the formulae

We can improve the 2 formulas to avoid formula

What is the default selected field?

If no field is selected, the function XLOOKUP returns #N/A.

To avoid this, we can fill the 4th argument of the XLOOKUP function to specify the default data type field. For instance, in the next formula, the default field is the field "Population".

=XLOOKUP(SUBTOTAL(9,tbl_Fields[Col ID]),tbl_Fields[Col ID],tbl_Fields[Fields],"Population")

Avoid SPILL error

Some Data Type fields return more than one result (Leaders, Subdivision, Official language, ...). If it's the case, the formula will return a SPILL error because there is not enough room to return all the results

The solution is to embedded the FIELDVALUE formula into the ARRAYTOTEXT function

=ARRAYTOTEXT(FIELDVALUE($D5,Sheet2!$D$1))