multiple slicers

[ajm]

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

 

[pedrowave]

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

 

[ajm]

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

Book1
	A	B	C	D	E	F
1	Details	Mike	Denis	Mary	Sally	Peter
2	Paris	Y				y
3	London		Y	Y
4	Joburg			Y
5	Sydney				y
6	15-21		Y			y
7	22-30	Y
8	31-50			Y	y
9	Doctor	Y				y
10	Lawyer				y
11	Singer	Y
12	Soldier			Y	y
13	Spy		Y
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.

 

[pedrowave]

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:

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

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

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?

 

[pedrowave]

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.

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"

 

[ajm]

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.

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.

 

[pedrowave]

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

Can you see the Power Query editor?

 

[pedrowave]

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.

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.