multiple slicers

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,053
Office Version
  1. 365
Platform
  1. Windows
I have a table of data with locations, ages, and skills in the left hand column. can a slicer be set up for each data type? ie one for the locations, one for the ages, and one for the skills
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Of course, but with an exclusionary condition of each slicer.

MultipleSlicers.PNG
 
Upvote 0
Thanks Pedro. my data is laid out a little differently. its all in column A. Like the example below.

Book1
ABCDEF
1DetailsMikeDenisMarySallyPeter
2ParisYy
3LondonYY
4JoburgY
5Sydneyy
615-21Yy
722-30Y
831-50Yy
9DoctorYy
10Lawyery
11SingerY
12SoldierYy
13SpyY
Sheet1

Of course, but with an exclusionary condition of each slicer.

The result i want is the same as you posted. three separate slicers. One for location, one for Ages, and then one for skills. any ideas.
 
Upvote 0
my data is laid out a little differently
Your table is not normalized and needs many transformations.

The first thing is to override the dynamization of columns with Power Query to get this normalized table:

AnularDinamizaciónPQ.PNG


Insert a PivotTable (left), with the above table as data source, and an auxiliary table (right):

PT&AuxiliarTable.PNG


One more pivot table must be created with data source in the auxiliary table and finally the slicers are created:

PT&Slicers.PNG


The result i want is the same as you posted. three separate slicers. One for location, one for Ages, and then one for skills. any ideas.
If that's the result you want to get, I can try to explain the steps to get all those tables in another message.

Do you know anything about Power Query?
Do you have Power Query installed in your version of Excel?
 
Upvote 0
As you have not yet answered the questions I asked, I am sending you the M formulas in Power Query of my first solution to your problem (follow my signature below) so that you can test them.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Details", type text}, {"Mike", type text}, {"Denis", type text}, {"Mary", type text}, {"Sally", type text}, {"Peter", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Name-Type", each Text.Combine({[Name], [Type]}, "-"), type text)
in
    #"Inserted Merged Column"
 
Upvote 0
As you have not yet answered the questions I asked, I am sending you the M formulas in Power Query of my first solution to your problem (follow my signature below) so that you can test them.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Details", type text}, {"Mike", type text}, {"Denis", type text}, {"Mary", type text}, {"Sally", type text}, {"Peter", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Details", "Type"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Value"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Name-Type", each Text.Combine({[Name], [Type]}, "-"), type text)
in
    #"Inserted Merged Column"
sorry Pedro. Thanks for the above. I got pulled onto something else for the time being but will have a look in the evenings over the next few days. Not sure about Power Query. I can add individual parts of PQ to my quick access toolbar but not the whole menu.
 
Last edited:
Upvote 0
Not sure about Power Query. I can add individual parts of PQ to my quick access toolbar but not the whole menu.
What version of Excel do you have installed?

In many versions of Excel, to show the Power Query Editor, in the ribbon menu select:
Data > Get Data > Launch Power Query Editor...

PQ_Editor.png


Can you see the Power Query editor?
 
Upvote 0
This is my proposed solution to transform your table, with a bad layout, into a pivot table to which slicers can be inserted.

This solution only uses several steps in Power Query with which the table transformation can be followed step by step.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="TableDetails"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Type", "Details"}, "Name", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Value"}),
    #"Group Clust Index" = Table.Group( #"Removed Columns",List.RemoveItems(Table.ColumnNames(#"Removed Columns"),{"Details"})
                                 ,{"ColOfTables",each Table.AddIndexColumn(_,"idx")}),
    #"Expanded ColOfTables" = Table.ExpandTableColumn(#"Group Clust Index", "ColOfTables", {"Details", "idx"}, {"Details", "idx"}),
    #"Sorted Rows" = Table.Sort(#"Expanded ColOfTables",{{"idx", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Type]), "Type", "Details"),
    #"Removed Index" = Table.RemoveColumns(#"Pivoted Column",{"idx"}),
    #"Filled Down" = Table.FillDown(#"Removed Index",{"Locations", "Ages", "Skills"})
in
    #"Filled Down"

Follow my signature below to read an explanation about this solution.
 
Upvote 0

Forum statistics

Threads
1,223,335
Messages
6,171,519
Members
452,409
Latest member
brychu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top