How to Use Symbols and Icons Instead of Text in Excel Slicers
ฝัง
- เผยแพร่เมื่อ 4 ต.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Master the art of dashboard customization in Excel with this engaging and informative tutorial. Ideal for HR professionals, data analysts, or anyone looking to enhance their Excel reports, this video guides you through creating visually appealing slicers using symbols.
⬇️ Download the workbook here: pages.xelplus....
Here's what you'll learn:
Dataset and Pivot Table Creation: Starting with an HR dataset, you'll see how to create a pivot table to analyze average salaries by cost center, sex, and full-time equivalent (FTE) status.
Transforming Tables and Slicers: Learn to convert a standard Excel table into an official one for dynamic pivot table updates. Plus, get insights into creating and formatting standard slicers.
Inserting Symbols in Data Source: Discover how to add unique character codes and symbols directly into your data source for a more intuitive slicer design.
Creating Custom Slicers with Symbols: Watch step-by-step as we replace traditional text slicers with symbol slicers, enhancing the dashboard's visual appeal and user-friendliness.
Fine-tuning Slicer Appearance: Learn how to adjust slicer settings for size, header display, and design, including creating a custom slicer with no borders.
Practical Examples: See how these techniques are applied in real-time to analyze different combinations, such as the average salary for male and female full-time or part-time workers.
★ My Online Excel Courses ► www.xelplus.co...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/pivot-slicer-symbols-file
Ого! Никогда не задумывался над изменением дизайна срезов. Спасибо огромное!
Glad you found something new :)
All your tutorial are the best in the world, can I call you a God of Excel 🤭?
You are making things understand so politely 😀
Your intelligence cannot be measured, doing a great job. I like it & I respect you for your knowledge in Excel.
Thank you Gopala for the kind words. Glad you enjoy the videos.
You are absolute genius.. I salute you..
Thank you. You're a very good teacher. I've learned a lot already 👌
I'm very happy to hear that.
Symbol template.. Cool idea.
you are making excel more interesting and exciting , thanks
Very happy to hear that. Thank you for the comment.
I submit to you
Always you have new brilliant techniques in MS-Excel.
Glad you like it Osama.
Que fantasía. estoy feliz de haberte encontrado. Saludos cordiales desde Medellín Colombia
Really nice!! 🙂
Your magical excel tutorial ...makes professional life easier, thanks
Very happy to hear that Barun! Thanks for dropping by :)
I really love you, I have learnt lot from your videos💯
Thanks a lot for your teaching!
You are welcome!
That's amazing! thanks 🙏👍
another good video. I am also working through your vba course, which I would recommend to others. Its a very good deal - you learn a lot (in my experience, its weeks worth of material, working at a moderate pace) and the approach is actually quite similar to a course you could pay a lot more for at a local college
Awesome, thank you! I'm glad you like the course.
All your videos are excellent .. thank u 💙
New informations in this video thanks a lot 💙
You are a genius
Thank you so much, It was really useful.
That's unique n very treat to anyone's eyes who observes it.
Love your work. Learned so much from your vids. Respect ✊.
Many thanks for the great videos you share all the times. You are doing an amazing job.
simple to master , thanks teacher !!
nice tricks!
Great tool and so simple.
Excellent videos, always!
Thank you Javier.
Thank you for your attention and support gablesyou🎉🎉
Thanks Leila !
very interesting
Thank you Leila. This is a great video & great learning.
You're very welcome! Thank you for your support.
As always very help full. Thanks
You're very welcome.
thank you so much madam, God bless you and yours Family ,
You're very welcome. Thank you very much for your support and your kind comment.
Too good for anyone...
WOW!
Well Explained.
Thanks for the video!
You're welcome Mohit.
Absolutely beneficial, thanks a lot Leila.
All the best,
You're very welcome Mohamed.
So easy and handy! Wow
That was a masterpiece!! And this function, Unichar is awesome! I am very excited with this video! Thank you so so much Leila! :)
You're very welcome Evgenia. No one called it a masterpiece before. I like that :)
That was awesome! Thank you so much! Please keep enlightening us!
You're welcome. Will do my best :)
thanks thanks
Hello Leila,am impressed your excel videos which is helped me a lot. Is that possible to prepare a pivot table with countries flags?? Based on the flag the report will display..can you do it for me??
Magnifique
Great Job Leila
Thank you Robert.
good job
Thank you. Glad you like it.
Awesome
Wow...! What a great idea. 👍👍👍
Gives a nice touch to reports. Thanks :)
So cool! :) Thank you for sharing.
You're welcome Celia :)
Thanks A lot Lelia
You're welcome Ahmed.
Really useful! thank you so much for this.
You're very welcome Fiona.
Very very nice!
Glad you like it.
Love this!
Thank you very much for the tutorial! Is there a way to disable right-click on slicers?
Could you please make example for slicer connected to different data tables
Nice one liela..thanks
You're very welcome.
Thanks, very useful. Please introduce some other features in pivot tables, LEILA!
Ok. For sure. Have added this to my list.
Leila Gharani Thanks for your attention!
Thank you Leila
Is it possible to connect one slicer to Pivot table and Data Table at the same time.
Awesome Laila.
Thanks Mostafa.
Can u share with us the refrence for symbols which founded in the condition formating (icon)
Great tutorials !! Is there a method to centre justify the icons or text in slicers please?
Miss Liela Absolutely Astounding
You are using multiple commands in a very fast flow
Hello Leila, We are trying to connect 1 slicer (for dates) to multiple pivot tables that are connected to different queries" any recommendations would help thank you.
Hello, can u help with sequence slicer with alpabet with number, like ( A1, A9, A10, B1, B2, B9, B10, B11)
awesome trick
Glad you like it :)
Thanks for sharing an informative tips. In the slicer, you removed the filter icon but selecting the male female icons. How to defilter it in such a way that the values to show for both?
Just press SHIFT while clicking icons
Hii Leila You are the One of the BEST . (Y)
THANK you Ankur!!
Kindly Mail me Some Problems on which i can make practice. Plzz
Looks beautiful.
Put the pivot table in a separate folder.
Then put the code below in the folder in program.
The pivot table is now automatically updated when the folder is activated.
Pivot table is now always up-to-date.
Management love it.
Private Sub Worksheet_Activate()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.PivotCache.Refresh
Next pt
End Sub
Agree - the code is very practical and easy to implement. I have these actually as a part of my dashboard and VBA courses :)
Many thanks for sharing.
مرسی خاله لیلا
It's a very nice tip! How can we set the symbol centered inside the button Ma'am?
Until now, there isn't an option that can control that....
Good job
Thanks Mohammad.
Hi, it's a great learning!!! cld u pls teach us d digital dashboards which includes KPI and forecasting and budgeting examples
Glad you like it Karthik - I have a course on this that's over 10 hours. You can see what's included in this video: th-cam.com/video/jD5SbrNKBcU/w-d-xo.html
Nice one madam.... thank...
Madam can u make videos on Excel VBA...
Pls ....
Thank you so much...
You're welcome Alpesh. Yes, yes. Videos in progress....
Ok.... madam..
N...
Thanks for replying....
How can we change the field position in a slicer. for example , i need in my slicer i need to change jan and feb in the last
Leila is my secret weapon
:)
I want to make condition in slicer like sal >500 and < 100 like condition to be display in slicer.
Leila, I am just wondering, by adding slicers (which we will later use it as part of dashboard), we are spending most of the time arranging and making it more presentable to whoever that is on the receiving end. Was this the key purpose of it all? I mean, as someone who crunches data, we look at large set of data and are able to tell some story. But doesn't seems to be the case for any non-data cruncher. So with that in mind, all we care for, is to make data presentable for dummy user, which means, rearrange, and to beautify, right??
Leila, is there a way to change a slicer based on cell input. Without vba.so if I enter john into A1, the slicer automatically changes to john
Hi Leila, what if I want to use text with symbols in the slicers?
I am not able to import the CSV file Once we set the format the table.pls help to fix the problem.
Hi Leila, i ve watched all your videos and impressed with you. Is there any way i can contact you i wana ask something
Hi how to freeze this slicer pls share with us
there are any short cut key to up down slicer in excel????
How to I use column headers as slicers?
I followed your instructions but, when I changed the Unicode number to symbol, slicer looks Unicode Word and I never changed it a symbol. How can I do it easly?
I could hear you laughing while typing Sex Symbol 😂 🤣😂... Anyways a great content and videos Leila... Keep it up
you couldn't control your laugh while writing "sex symbol" :D
👍
I have a query about the pivot table and the query is .. Suppose i have a data in excel ..and the 1st hadder is product and 2nd hadder is cost and 3rd hadder is quantity.. And then i make a pivot in diff sheet ..and i put the 1st hadder in rows and 2nd hadder in rows and 3rd as well..and then i want to multiply for 2nd and 3rd hadder ..and thr grandtotal will not be multiply ...in the pivot field.. The field 3rd value in pivot would be come for the total of ..field not be multiply with qty and cost
If you have any video in channel then plz send the link ..
Hi Leila;
I have one situation in excel. As you must be aware; in India; a person is required for have a PAN no. for his income tax purpose. This PAN is a 10 digit alpha-numeric and has a specific format. for e.g. ADGPV9999K. Wherein as you can see,
1. The first 5 are alphabets, 2. The fourth is always "P" which stands for "Person" ("C" for company, "T" for trust and so on), 3. From 6th to 9th are whole numbers and 4. the last is again an alphabet. I want to have a single integrated function (validation) which will take care of all these validations and shall throw a result "VALID PAN" when all these conditions are satisfied or "INVALID PAN" if any of the conditions are not met. the problem I am facing is that each of the 1st 5 are alphabets. In above example ADGPV. Supposing if a person wrongly enters a whole number or a special character in any of the 1-5 positions; using ISTEXT function; still validates the input; which is wrong. Will you please help solve this? I know this video is not the right platform for this query but hope you won't mind.
Hi Sachin - I think something like this might work:
=AND(NOT(ISNUMBER(AGGREGATE(14,6,VALUE(MID($A2,{1,2,3,4,5},1)),1))),NOT(ISNUMBER(RIGHT($A2,1))),ISNUMBER(VALUE(MID($A2,6,4))),LEN($A2)=10)
You could use an IF before this to check if the entry is valid or not.
Alternatively you can use it inside a data validation - because of the array reference to numbers though, you'll need to either use name manager or to use a function that gives a 1 to 5 array, like the indirect function...
For the data validation, you'll need to select "custom" and make sure you uncheck "ignore blank".
Hope this helps....
Thanks for your quick support Leila. Shall try and get back :)
Hi Leila, I have tried the above nested function. Its works fine except the last place from the right, which is an alphabet. Even when one enter a whole number the result is TRUE. Anyway I have come-up with this very long a "Railways coaches" kind of integrated function using IF, ISERROR, AND, MATCH which works fine. I was wondering if there is any specific function in excel which determines whether a particular data in the data string is an alphabet, whole number or a special character and returns true or false. Thanks for your kind support. Your videos have really been of great help all this time.
=IF((ISERROR((AND((MATCH(LEFT(A2,1),$XFC$1:$XFC$26,0)),(MATCH(MID(A2,2,1),$XFC$1:$XFC$26,0)),(MATCH(MID(A2,3,1),$XFC$1:$XFC$26,0)),(MATCH(MID(A2,4,1),$XFC$1:$XFC$26,0)),(MATCH(MID(A2,5,1),$XFC$1:$XFC$26,0)),(MATCH(RIGHT(A2,1),$XFC$1:$XFC$26,0)),(MATCH((VALUE(MID(A2,6,1))),$XFD$1:$XFD$27,0)),(MATCH((VALUE(MID(A2,7,1))),$XFD$1:$XFD$27,0)),(MATCH((VALUE(MID(A2,8,1))),$XFD$1:$XFD$27,0)),(MATCH((VALUE(MID(A2,9,1))),$XFD$1:$XFD$27,0))))))=FALSE,"VALID PAN","INVALID PAN")
Thanks Sachin for the feedback. For the last one, I forgot to wrap it up in the Value function. So Just before Right, we can put it in Value(). Glad you figured it out though :)
Hi, is there any way where we can select multiple fields in slicers
Yes - you just have to hold down the control key.
Thanks...
❤
@7:56 When Win+V didn't work yet...
Does it work for 2007 also? Cz I didn't find it in 2007 version.
Unfortunately not! Slicers exist from Excel 2010 onwards.
I have a question, a problem actually. I have a column named "days" that shows the sum of days an active employee has (e.g. 1250 days/ so I don't have an end date) and I need to change that into the number of months (e.g. 365 days = 12 months) in Excel 2016. I tried to look for a formula that can help me, but I'm not able to find one. I will sure appreciate if you can help me.
Since it's just going to be an estimate, how about using a formula to get the estimate =(days*12)/365 ?
Hi, I love your instructional videos! A question on sorting legend on a pivot chart. It won't allow me to sort or change order of the legend in the pivot chart, it's greyed out. Help!
one more question on slicer some times when i click on the value to open the data in a new sheet it returns all rows in stead of filtered no of rows , not sure why it happens but when i add them as a report filter in the pivot table it returns the right data . can you let me know why it happens
Not really. Are you using the same slicer for different pivot tables? If yes, make sure it has the connection to the other pivot tables as well.... I can't imagine why it would behave like this.
Leila no it is just one. The weird thing is if you add report filter and then the slicer it works fine
Hi ,
When i tried it find the Unichar symbol in a blank sheet as you had mentioned i got #name? error. do i need to do any formatting
I think it depends on your Excel version. I have office 365 - it might not be available in your version....
Leila Gharani hi I noticed that it is not supported in excel 2010
is there any other way to get those characters in Excel 2010
PUT VIDEO OF TALLY ERP 9 BASIC OR ADVANCE TALLY
What I can say to you !!!!!!!!
Hi i really like your way of teaching thank u so much can I get your email i wanna study online
Hi Ismail - glad you like the videos. You can find the list of my online courses on my website...
I'm really watching your videos the whole day even at work hhhh i liked the videos that talked about goal seek and solver oh my gosh u just let me know that i still know nothing about excel
:) The best thing about Excel is the learning never ends. I'm learning every day.
You are absolutely right and I really have so much fun while I'm working on it can you send me the link of your videos website in my email please besmilingforever120@gmail.com
Thank you for imparting the knowledge.