Do you know you can connect a slicer to change your Data Type entries? Here is the trick.
Country dashboard with the population
To illustrate the technique, we have created a basic Dashboard with some countries' name and the Population property. If you don't know how to build such Dashboard, follow the explanations in this tutorial.
Use Slicers to select your Data Type
Slicers are exclusive to Pivot Tables and Tables. Therefore, it's necessary to enter the Data Type fields you wish to include in a table. It's crucial to match the field names exactly as they are, or the slicers will not function properly.
To construct a dashboard that allows you to select a Data Type field using a slicer, you should follow these steps.
Step 1: Write the fields in a Table
Create a Table with only 2 columns
- Column ID
- Your fields name
Step 2: Next add a Slicer from your Table
Here, nothing complex.
- Select one of the cells of your Table
- Go to the Table Design menu
- Select the option Insert Slicer
- Select the field you want as slicer (here we will select the column Fields)
Then, a slicer with the field name is displayed in your worksheet
Step 3: SUBTOTAL function
Then, we will build a formula with the SUBTOTAL function. Why this function? It's because this function adapts to the applied filters 😉
=SUBTOTAL(9,tbl_Fields[Col ID])
The argument '9' indicates that the formula will calculate the sum of the filtered rows. When you select a field from the slicer, the SUBTOTAL function accurately returns the ID of that field in the table.
Step 4: Convert the 'ID' column to 'Field's Name'.
However, it's not the Column ID we're interested in; rather, it's the value of the field's name. To retrieve this, we can use either the VLOOKUP or XLOOKUP function.
=XLOOKUP(SUBTOTAL(9,tbl_Fields[Col ID]),tbl_Fields[Col ID],tbl_Fields[Fields])
Step 5: Connect the result of the formula with Data Type
In Excel, there is a function created only for the Data Type. It's the FIELDVALUE function. This function needs 2 arguments:
- The reference of the cell linked to the data type (the cell with an icon)
- The cell with the field name (the result of the previous formula)
In this case, we need to substitute the initial formula, =D2.Population, with the new formula provided.
Improve the Result to avoid Errors.
There are two scenarios in which we can return errors.
- In the scenario where no field is selected, a default value must be established.
- And the scenario where Data Type returns more than one value
Create a default field
If no field is selected, the XLOOKUP function returns #N/A. However, XLOOKUP has a fourth argument that can handle this situation. For instance, we could set the default value to the Population field. We will update the function as follow:
=XLOOKUP(SUBTOTAL(9,tbl_Fields[Col ID]),tbl_Fields[Col ID],tbl_Fields[Fields],"Population")
Avoid SPILL error
Certain Data Type fields yield multiple results, such as Leaders, Subdivision, Official Language, etc. When this occurs, the formula will produce a SPILL error due to insufficient space to display all the results in a row.
The solution involves embedding the FIELDVALUE function within the ARRAYTOTEXT function.
=ARRAYTOTEXT(FIELDVALUE($D5,Sheet2!$D$1))
