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
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??
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
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.
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?
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
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??
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....
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 :)
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 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 ..
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.
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.
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!
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
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
Grab the file I used in the video from here 👉 pages.xelplus.com/pivot-slicer-symbols-file
Ого! Никогда не задумывался над изменением дизайна срезов. Спасибо огромное!
Glad you found something new :)
Thank you. You're a very good teacher. I've learned a lot already 👌
I'm very happy to hear that.
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.
I submit to you
you are making excel more interesting and exciting , thanks
Very happy to hear that. Thank you for the comment.
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.
Always you have new brilliant techniques in MS-Excel.
Glad you like it Osama.
You are absolute genius.. I salute you..
Many thanks for the great videos you share all the times. You are doing an amazing job.
All your tutorial are the best in the world, can I call you a God of Excel 🤭?
That's amazing! thanks 🙏👍
I really love you, I have learnt lot from your videos💯
Great tool and so simple.
Excellent videos, always!
Thank you Javier.
Love your work. Learned so much from your vids. Respect ✊.
Your magical excel tutorial ...makes professional life easier, thanks
Very happy to hear that Barun! Thanks for dropping by :)
All your videos are excellent .. thank u 💙
New informations in this video thanks a lot 💙
Thank you for imparting the knowledge.
simple to master , thanks teacher !!
Thank you so much, It was really useful.
That's unique n very treat to anyone's eyes who observes it.
Really nice!! 🙂
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 :)
Symbol template.. Cool idea.
Thank you Leila. This is a great video & great learning.
You're very welcome! Thank you for your support.
You are a genius
Absolutely beneficial, thanks a lot Leila.
All the best,
You're very welcome Mohamed.
Thanks Leila !
That was awesome! Thank you so much! Please keep enlightening us!
You're welcome. Will do my best :)
Que fantasía. estoy feliz de haberte encontrado. Saludos cordiales desde Medellín Colombia
As always very help full. Thanks
You're very welcome.
WOW!
Well Explained.
Thanks for the video!
You're welcome Mohit.
Thank you for your attention and support gablesyou🎉🎉
nice tricks!
So cool! :) Thank you for sharing.
You're welcome Celia :)
Very very nice!
Glad you like it.
Great Job Leila
Thank you Robert.
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.
Love this!
very interesting
Wow...! What a great idea. 👍👍👍
Gives a nice touch to reports. Thanks :)
Really useful! thank you so much for this.
You're very welcome Fiona.
So useful!
Glad to hear that!
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??
Thank you very much for the tutorial! Is there a way to disable right-click on slicers?
Thanks A lot Lelia
You're welcome Ahmed.
Magnifique
Nice one liela..thanks
You're very welcome.
thanks thanks
Too good for anyone...
Awesome Laila.
Thanks Mostafa.
Awesome
Thank you Leila
Is it possible to connect one slicer to Pivot table and Data Table at the same time.
Great tutorials !! Is there a method to centre justify the icons or text in slicers please?
good job
Thank you. Glad you like it.
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.
awesome trick
Glad you like it :)
Could you please make example for slicer connected to different data tables
you couldn't control your laugh while writing "sex symbol" :D
Good job
Thanks Mohammad.
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!
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.
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....
Can u share with us the refrence for symbols which founded in the condition formating (icon)
Leila is my secret weapon
:)
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
Hello, can u help with sequence slicer with alpabet with number, like ( A1, A9, A10, B1, B2, B9, B10, B11)
Miss Liela Absolutely Astounding
You are using multiple commands in a very fast flow
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
👍
مرسی خاله لیلا
I could hear you laughing while typing Sex Symbol 😂 🤣😂... Anyways a great content and videos Leila... Keep it up
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....
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
Hi Leila, what if I want to use text with symbols in the slicers?
Hi Leila, i ve watched all your videos and impressed with you. Is there any way i can contact you i wana ask something
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, 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 how to freeze this slicer pls share with us
I want to make condition in slicer like sal >500 and < 100 like condition to be display in slicer.
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 :)
How to I use column headers as slicers?
there are any short cut key to up down slicer in excel????
❤
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 am not able to import the CSV file Once we set the format the table.pls help to fix the problem.
Hi, is there any way where we can select multiple fields in slicers
Yes - you just have to hold down the control key.
Thanks...
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 ..
@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 ?
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
PUT VIDEO OF TALLY ERP 9 BASIC OR ADVANCE TALLY
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
What I can say to you !!!!!!!!
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!
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
Thanks a lot for your teaching!
You are welcome!