Connect slicer with Data Type

Connect slicer with Data Type
Last Updated on 19/10/2022
Reading time: 3 minutes

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

Countries and Geography Data Type

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.

Icon data type added to your cells content

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.

Display the data type fields by formula

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

Countrys area returns by Data Type

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, ....)

Data type fields in a Table

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
Menu Table Slicer
  1. Select the field you want as slicer
Select the field you want as slicer

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

Slicer with the field names

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.

SUBTOTAL function for all the data

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

Result returns by the SUBTOTAL function when a field is selected

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])

XLOOKUP returns the field selected in the slicer

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)

The FIELDVALUE function builds the result

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.

If no field is selected the result is NA

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")

Default Field if no slicer is selected

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

SPILL Error because the data type has more than one answer

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

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

ARRAYTOTEXT returns more than one result per cell

Leave a Reply

Your email address will not be published. Required fields are marked *

Connect slicer with Data Type

Reading time: 3 minutes
Last Updated on 19/10/2022

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

Countries and Geography Data Type

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.

Icon data type added to your cells content

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.

Display the data type fields by formula

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

Countrys area returns by Data Type

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, ....)

Data type fields in a Table

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
Menu Table Slicer
  1. Select the field you want as slicer
Select the field you want as slicer

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

Slicer with the field names

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.

SUBTOTAL function for all the data

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

Result returns by the SUBTOTAL function when a field is selected

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])

XLOOKUP returns the field selected in the slicer

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)

The FIELDVALUE function builds the result

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.

If no field is selected the result is NA

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")

Default Field if no slicer is selected

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

SPILL Error because the data type has more than one answer

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

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

ARRAYTOTEXT returns more than one result per cell

Leave a Reply

Your email address will not be published. Required fields are marked *