Slicers in Excel with Sub Category - Help Please

jevi

Active Member
Joined
Apr 13, 2010
Messages
261
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I am looking on the internet for slicers with sub categories, check even the pivot tables slicers but it sound I can't understand.

I need a first slicer with 5 products and than I need below each product another slicer with the sub names. Smth like below:

So I need the 1 slice with the 5 products and then I need other 5 slices with each sub product, because then I need to show a table of calculation. Is it possible? :confused:
SLICE 1SLICE 2SLICE 3SLICE 4SLICE 5
PRODUCT 1PRODUCT 2PRODUCT 3PRODUCT 4PRODUCT 5
THE PRODUCTS HAVE SUB CATEGORIES OF PRODUCTS
PRODUCT 1.1PRODUCT 2.1PRODUCT 3.1PRODUCT 4.1PRODUCT 5.1
PRODUCT 1.2PRODUCT 2.2PRODUCT 3.2PRODUCT 4.2PRODUCT 5.2
PRODUCT 1.3PRODUCT 3.3PRODUCT 4.3PRODUCT 5.3
PRODUCT 1.4PRODUCT 3.4PRODUCT 4.4PRODUCT 5.4
PRODUCT 1.5PRODUCT 4.5
PRODUCT 4.6

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
THANK YOU SO MUCH
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,065
Office Version
  1. 2016
Platform
  1. Windows
Maybe you can use: Dependent Validation Lists
 

jevi

Active Member
Joined
Apr 13, 2010
Messages
261
Office Version
  1. 2016
Platform
  1. Windows
but it won't give the data as I want to click on product 1, and then click on one of the sub products of product 1 so I can have the calculations.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,065
Office Version
  1. 2016
Platform
  1. Windows
Can you give an example of your calculation table?
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710

ADVERTISEMENT

As for the slicers, I would think you only need to set the Option for the Sub-Categories slicer to "Hide values with no data"
 

jevi

Active Member
Joined
Apr 13, 2010
Messages
261
Office Version
  1. 2016
Platform
  1. Windows
Hi Mart,

Thinking carefully and working last night I think one slice with all the subcagories is fine, because the name of them is so long so I can't do as I said before even I can find the way:). But as I tried I think there was no way.

Still I am sending the calculation formula.
Code:
[TABLE="width: 435"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Product Name[/TD]
[TD]SubProdcut Name[/TD]
[TD]Total[/TD]
[TD]Months[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.1[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.3[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.4[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.5[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]Product 2.1[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]Product 2.2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.1[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.2[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.3[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.4[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
 

jevi

Active Member
Joined
Apr 13, 2010
Messages
261
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank u Spiller but all subcategories have data, still I found the way as I said the name was too long so is not worth having the subcategories for each product on different slicer. Still I see the the slicer are a little but rigid even though I love them.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
The point would be that selecting "Product 3" in the product slicer would reduce the number of options in the "Subproduct slicer" making that selection much easier.
I have seen the desire to use slicers for a field with too many options, but by using intersecting fields, makes the end choice manageable.
 

jevi

Active Member
Joined
Apr 13, 2010
Messages
261
Office Version
  1. 2016
Platform
  1. Windows
But do you know Spiller why the slicer main like when I choose Product 3, it moves place product three and it goes at the first slice and does't stay at the third slice as I would like. I don't like to change them place the main products. I did also a custom list and it sound is not working. What do you think? I think i did everything from the slicer settings.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,722
Messages
5,626,497
Members
416,187
Latest member
L_D18

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
Top