Excel dashboard using slicers when cell contains multiple values

Question

I'm having trouble setting up the dashboard in Excel. I have information about sales, clients, and meetings. The type of transaction is listed in one of the columns (for example AAA, BBB, etc). There is often only one type of transaction for each row, but occasionally there are two or even three.

So for example it could look like this:

Invoice	Type	Price
Invoice_1	AAA	100$
Invoice_2	BBB	200$
Invoice_3	AAA, BBB	500$

I wanna create a dashboard with slicers and Pivotcharts, but when I do slicer based on the Column "Type" it gives me three values to choose from: AAA; BBB; AAA, BBB.

Is there a way around this problem? So Excel could identify this data as simply AAA or BBB (to check if the cell contains a given type of transaction separated by a comma?

Answer 1

All values in the column are displayed by an Excel slicer. The slicer displays the three separate values that you have. Excel doesn't somehow understand that "AAA, BBB" implies "both AAA and BBB" in your context. It simply takes the text at face value.

You must tidy up your data so that it has just two values if you want the slicer to display only two values. This can entail splitting up data rows with many types into rows with only one type value each.

I hope this helps you.