Microsoft Excel - Slicer Trick for Long Lists

**** For Office 365 users, the following formula is much simpler:
=LEFT(TEXTAFTER([@[Sales Representative]], " ", , ,1) )
Learn how to tame Slicers with too many buttons to fit on the screen using what's known as an Initialization Slicer.
File Download Link:
www.bcti.com/w...
The formula for extracting the first character from the last name:
=MID([@[ColumnName]], FIND("/", SUBSTITUTE([@[ColumnName]], " ", "/", LEN([@[ColumnName]])-LEN(SUBSTITUTE([@[ColumnName]], " ", "") ) ) ) + 1, 1)
Explanation of formula:
spreadsheetpla...

[@JM-mb6tf]

It is really hard to find good channels that are past the initial stages of Excel knowledge, so I am extremely happy to have found your channel. You explain absolutely perfectly, and I REALLY appreciate that you show 1-3 methods to solve the problem. Subscriber ? Oh yes I am. :)

[@bcti-bcti]

Thank you so much for your enthusiasm and participation. Thanks for the sub!👍👍👍👍

[@chaslanning4518]

You are very good indeed … explain just like a teacher … keep them coming!

[@deepjyotistories]

Thank You for this tutorial. I tried on some dummy data and this worked flawlessly.

[@rotethongvanit]

Great work!

[@bcti-bcti]

Thanks again! We appreciate you taking the time to watch.

[@Vogeln]

This was an amazing video! Thanks for posting it.

[@bcti-bcti]

I love this trick, too. Thank YOU for watching.

[@gerhardhanzmann-fc6gl]

Hi, thank you for your tuts. They are realy helpful. How do you get the empty row after each group in the Pivot Table?

[@bcti-bcti]

With a cell in the Pivot Table selected, go to the DESIGN ribbon for the Pivot Table; click "Blank Rows" - "Insert Blank Line after Each Item". Thank you for watching!!!

[@jethrobo3581]

Fantastic video!

[@JoseAntonioMorato]

Could you not make the workbook shown in the video available for download? I would be very grateful, as it would make it possible to practice your teachings. 🤗

[@bcti-bcti]

The file download link has been added to the video description. Thank you for watching and supporting the channel. 👍👍

[@JoseAntonioMorato]

@@bcti-bcti I really appreciate your kindness. 🤗

[@mga74]

Great tip ! Thank you - just out of curiosity - why not extract first character immediately in PQ in "first step" ?

[@syrophenikan]

Because I was trying to keep it simple for those not experienced with Power Query. But you're right, I would have done this in a single step query.

[@mga74]

@@syrophenikan No problem - as i am beginner with PQ myself i was still asking myself why don't do it asap there ;-) Thank you for replying ! Great video !

[@doczoff5655]

Nice tip!

[@sketchtwenty2]

How do you accommodate names with multiple spaces? There are ways of course but they involve complications. Rarely are the functions and features of Excel as simple to apply to real data as they are made out to be.

[@bcti-bcti]

You are correct. The "real world" is often more complicated, so this will likely be but one part of a larger (i.e., trickier) solution.

[@siyadoll]

hello i have a problem related to the slicer although I tick the box hide item with no data, i have 4 slicers on my dashboard when i clear the filter on 4th slicer it starts showing blank buttons or buttons that have no data on the 3 slicers,but I tick the box hide item with no data on all slicers then why when i clear filter the blank buttons appears on all slicers what is the reason behind this problem please tell me

[@bcti-bcti]

Without seeing the file, it's difficult to answer with any specificity. Each slicer is controlled separately, so I would check to ensure the options are set identically across all slicers. If that doesn't work, I'd need to see the file (or an example that showcases the same problem.)

[@siyadoll]

​@@bcti-bcti Thank you, sir, for your reply is there any way that I could send you the file trust me I have worried for a week for a solution but nothing succeeded in finding the solution

[@bcti-bcti]

You could send the file (or a download link for the file) to my support email ([e-mail]) and I'd be happy to take a look at it and see what I can come up with.@@siyadoll

[@siyadoll]

@@bcti-bcti o my God thank you so much

Why don't you simply use the TEXTAFTER function to locate the last name?

[@bcti-bcti]

This was meant to be a solution for anyone to use, not just Office 365/2019 users. But I agree, as an Office 365 user, I would use your solution. =LEFT(TEXTAFTER([@[Sales Representative]]," ",,,1)) Thanks for watching.

[@bcti-bcti]

I've updated my video description to include your solution. Thanks for helping me make this better. 👍

Yep, agreed - this only works for O365 :) Thanks! @@bcti-bcti

[@stevenperry7041]

?? Why not use the Left formula?

[@bcti-bcti]

The great thing about Excel is that the same problem can always be solved in multiple ways. There's nothing wrong with using the LEFT function; I just did it this way to demonstrate a method that might help solve other problems that the LEFT function might not be best suited for. Thanks for watching and contributing. 👍👍

[@一茜王]

It didnt work form mine.....

[@bcti-bcti]

@@一茜王 Sorry to hear.

[@lhdps]

People publishing videos with excel 365, with brand new functions and functionalities, and are still using the old approaches, it really doesn't make any sence. You don't need that complicated formula to get the first letter of any length surname. You just need = left(textafter(a1," ",1),1) where A1 is where you have your name. You're Welcome

[@bcti-bcti]

It's necessary when you are trying to deliver the functionality to those who may be using other versions of Office that do not offer these new functions. But I agree, I would use your approach in an O365 scenario.

[@LosoIAm]

Not everyone has or uses Excel 365. Besides, I would use VBA, for ease of maintenance!