Excel Pivot Chart with Slicers for Months to Show Values by Weekday Names

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/weekday-slicer-file

[@2808drw]

I love her way talking 👍 Her language so easy to understand and explantion is clear 👏👏👏

[@patriceairoldi]

Very good explanations, now I know better how to create a pivot chart. Thanks for this very good video Leila !

[@LeilaGharani]

Happy to hear that Patrice!

[@Zabikhan14]

Thank you so much.. ur video's not only motivate but also a way of solution.... thanks alot

[@sasarakayuhuu8035]

OMG you are brilliant... Thanks I really need it as I am creating a dashboard now. Wish for your happiness and success in life!

[@LeilaGharani]

Glad you can apply it to your dashboard :) You're very welcome Sasaraka.

[@rnjy20]

it wouldn't be a problem if i tell someone that you invented the Excel, as its totally believable :D you know the options so well

[@malcbatotony9518]

Thanks Leila. Very happy with this video.

[@LeilaGharani]

You're welcome 😊

[@Adam_K_W]

It's easier to use the TEXT function than it is to use the WEEKDAY function. Just type =TEXT(A2,"DDDD"), that will populate the fully spelled out day "TUESDAY", or type =TEXT(A2,"DDD") will give you the 3 letter abbreviation of the day, ie. "Tue"... That will save you from having to navigate the WEEKDAY function. This also works with months by typing "MMM" or "MMMM".

[@roderickmose4691]

@F yes, but the Pivot table doesn't seem to like the =A2 option.

[@sadavies2906]

Love the videos Leila very easy to follow and results are great. In this example is it easy to include all week days in the pivot table and subsequent chart if there isn’t and data for the day in the table. Obviously as you use the slider the chart changes based on the data but would like to have 7 days showing all the time with blanks if nothing was on a Monday for example

[@Rascalini19]

You are so Awesome! Very easy to follow along... Thank you so much!!!

[@tamimsajid]

a beautiful video from a beautiful soul. Thanks a lot.

[@LeilaGharani]

You're very welcome. Glad you like the video 😊

[@rachaelhuntley4905]

Just found you - you are amazing and the examples/lessons you give are the easiest to follow and you give the best explanations - thank you

[@uningeniero3343]

Oro, puro oro por estos rumbos.

[@amrelmashwady]

thank you so much , each lecture I learn thing new in" excel "from you

[@LeilaGharani]

That's great to hear Amr! You're very welcome.

[@charlesjames5965]

Leila can you please share your email for us to have those lectures even if it costs no matter

[@yanmike5212]

Thank you so much for imparting the knowledge

[@hassanmburia8825]

BE BLESSED

[@Mineirovsky]

I liked it very much. It's just what I'm looking for. Thanks!

[@tsauraiketiwa1086]

You are just good in this.

[@sulaimansait864]

Super mam you are explain so more update video for the excel sheet I small douit mam you can explain daily for the payroll excel sheet

[@rajamanikamr4779]

Thank you for you excel training, since I have developed Excel knowledge.from your videos

[@LeilaGharani]

You're very welcome! I'm glad you enjoy the tutorials.

[@stephengibson100]

These are wonderful. I'm learning so much, and building my own file of templates. Thanks so much.

[@LeilaGharani]

I'm glad you find these useful Stephen.

[@irviantantohadiprasetyo689]

It's very useful. Thanks, Leila

[@LeilaGharani]

My pleasure. I'm glad the video is useful.

[@ravibopara3559]

Firstly, many thanks to your tutorials on KZbin and on your website. Those helped me to boot my productivity levels in using Excel. Here is my requirement: 1. I want to create a excel like interface with rows and columns in SharePoint with borders as my team members are familiar with excel. 2. When adding a new item in list, most of the columns need to be updated from auto-populated list for each column. 3. This list of options for each column will change on weekly basis and that is captured in an excel workbook which I want to connect to this SharePoint list. If this can be achieved, please guide me. If this requirement can be achieved with other set of office apps in a better way, please suggest.

[@ckokse]

Yet another great video! Thank You!

[@muhammadmudassirhussainsid407]

Thanks its very useful to help me alot but I request u please increase your video quality its show me blur.

[@yogalekshumimarimuthu2173]

Thank you Leila, can you do a calendar dashboard using this slicer please ..

[@asepchannel1735]

great explanation. easy to learn

[@douglaszulu6281]

Great video on dashboards

[@LeilaGharani]

Thanks Douglas.

[@evgeniam8882]

Excel-lent video and this slicer format I was searching for a while finally it came to me! Thank you so much Leila!!

[@MAJIDMANSOOR1]

Waoo this video is most helpfull video ........ Thank you

[@LeilaGharani]

You're very welcome. Glad you find it helpful.

[@zagros367]

excellent

[@vokisek]

With custom formating you can format the date directly. You do not need it convert it with the weekday function. :)

[@ozlemcivan7455]

Hey there, thanks for the great vid, one question how can i use pivot charts with a table of data which expands every month. So the amount of data just gets larger and larger every day can pivot handle that ? Kind Regards

[@wrivers6412]

Finding your videos was the best thing ever!!! I've learned so much about excel and over the last two weeks that I've actually applied what I learned to some projects at work!!! Not only am I happy but my Leadership team at work is also happy!!!

[@LeilaGharani]

That's GREAT! I'm really happy to hear that! Thanks for sharing :) You've done the difficult part which is applying what you learnt to your own work.

[@eg_ak84]

New sub! Wow! Blown away with the content. Such quality, beauty, and knowledge all jam packed in each video! (Thank you, KZbin!)

[@shafikhan100]

I regular learn from your videos, request you to make an end to end video on pivot tables from basics to extremely advanced (A-Z)

[@iloveyoumadhuri]

Hi Leila! I had difficulty getting the Months slicer into my pivot table for my Mac. At the 6:30 part, where I had to remove my original slicer, I could not get months by clicking on the dates in the Pivot Chart Fields like you. Is it possible to get help from you?

[@LeilaGharani]

Sorry, I don't have Mac in the office. Maybe someone else from the community can help out.

[@brianbruno4749]

Leila....2.5M subscribers? Wow! And the content is fantastic. I consider myself an expert in Excel, but I've just never had a reason to use pivot tables. This is just what the doctor ordered. Thanks

[@Tanushree00099]

Hi Liela, For Excel 2010 ,Just found out that to get Slicer for the Months - we can add a column containing the month name.TEXT(A2,"MMMM"). Then we do not have to go through the process of grouping as the Pivot Table field does not reflect the Month option like it is for your excel. Further the Slicer setting does not have the option of "Hide blank data".

[@hongqirong9927]

Hi, alternatively, one can after selecting "Date" in the PivotTable fields at video 06:25 right click on the newly created date, select the line after the day, i.e Select 6-Mar or 13-Mar or 20-Mar which is found within the Sunday group. So I repeat, right click on any one date, Select Group, Select Starting date 9/2/2016 and ENding at 8/8/2016 (This is automatically selected - depending on the dates you have in your data, it is on auto), Select next "By Months" and "By Days". And we can proceed to add slicer as per in the video 07:30 . Thanks.

[@barttitulaerexcelbart9400]

well done! two remarks, 1 there is no Monday? 2 I would first fix the y axis, by giving it a Max value, and then remove it. then you always have the same heigts of the bars, for different months.

[@LeilaGharani]

Thanks Bart - 1) yes - there was no Monday in this data set. and 2) That's a good idea: improves the message delivery of the visualization. Thanks for that.

[@mattnyman9933]

@@LeilaGharani To be more accurate, September has a Monday but it is after Saturday. Is that a data management issue?

[@PhilipDring85]

is there a way to have the slicer as a float so that it stays in same position as I scroll doen the table

[@rickdemarco579]

Great video, but when I create the Pivot Table, my row is still showing a date (1/1/1900, 1/2/1900, etc.) and not the day shown within the table. Any suggestions?

[@amyjain97]

how do we use sumifs or countifs with slicer multiple selection, suppose I have a geo filter in pivot and when i select multiple geos in slicer and the formula I have applied should also get updated

[@rvibrahim]

Your videos are really awesome! Excellent demos/examples that you present offers us a great learning experience.

[@gogazanguridze4289]

Great!

[@frederikjrvanbruggen]

thanks for your examples, but how do you do that: date and automatically weekday, months, etc. Frits from Holland

[@darkshinigami9438]

How do you add the "Power BI" and "Power Pivot" tabs on Excel?

[@edwardisaac6811]

Hello leila, kindly advise the way i can get your help with pivot table i have a problem that require your kind assistance. Thank you,

[@thomas12ist]

Is there a way to get the slicer to multi select by default?

[@jessiesi1412]

Thanks for making so many great excel videos. These are so useful in the work. I have a question about slicer. If you have multiple slicers, is there a way to make them stay at the same place not collapse? One day I tried to add multiple slicers and adjust them the size I liked. However, when I tried to play with them, I found by select different categories, these slicers will move and not stay at the same position I organized first. Do you have an idea why? By the way, could we link the slicers to a different pivot table which we used the same data source? Could you please advice me? Thank you.

[@TsiriniainaRakotonirina]

Dear Leila, Thank you for your much appreciated training. I purchased all your 07 courses and finished the Basic Excel. I'm on the *Advanced Excel: Top Excel Tips & Formulas* now. I am following step by step this *17. Tip 10- PivotTables: Calculated Fields, Slicers & Helpful Tricks* and on the video *17:56*: ---> I try to Center the 2016 and 2015 across the Selection. And the Headings below as well. It definitely works. ===> *Unfortunately, when I update the filters, the formatting are gone every time.* I kept the *Analyze>>Options>>"Layout Format" Preserve Cell formatting on update" Ticked* but still nothing. May you help me on this please?

[@Mohamed-Naji]

Thank you, is there a way to print out the chart with slicer? Always the slicer does not appear.

[@madhun8092]

How do we use weekdays in power query?

[@josecarlosconejo5724]

Many thanks for your excellent videos and outstanding didactic abilities. There a before and after since I subscribed to your channel. I have one question on slicers. I am working on a dashboard for my boss with a slicer on months, but I need to limit the selection to one item, i.e., one month. I don’t want my boss to select more than one month at a time. Is it possible to limit selections in a slicer to one item at a time? Many thanks again for your videos...

[@LeilaGharani]

To restrict slicer selection to one, you'd have to use VBA. This way you can check if more than one selection was made. If yes, a message box could inform the user to select one item only.

[@josecarlosconejo5724]

Leila Gharani Many thanks!!! I’ll give it a try 😊👍 Happy New Year!!

[@davidavetisyan5762]

hi. is there a way to remove for example Friday from slicer without deleting all from the table?

[@yerbolabzhamiyev3268]

I can't stop watching Leila's videos! They are so interesting!

[@Ibrahimcampuskarianwala786]

hi help me plz (i have month name ,actual fee, Receive fee, Pending fee my problem is how can i make arrears received and arrear cell with actual fee receive fee and pending fee

[@samreenyasirshiraz]

Ms Access complete

[@ramlan8581]

To tell the truth , your explanation is 100% times better than my school teacher. Thanks a lot this is really gonna help me in my exams tomorrow ☺😊

[@LeilaGharani]

Good luck for tomorrow!

[@TheJimprez]

Hahaha. Just like Code Beauty. About 90% male viewers. The internet is what it is... Good luck. I'm more into Access.

[@englishbobshousemusic]

Hi Leila. I am trying to make a dashboard. It has multiple slicers controlling a pivot table. I want to stop the automatic sorting that happens to the slicers when selecting something. Do you have a solution for this. Great channel.

[@ismailismaili0071]

thank u so much this really was useful for me, kindly could you show me a perfect chart for target that compare by year and month like Jan 2016 compare by Jan 2017

[@LeilaGharani]

You're welcome Ismail. You could use different charts here, but for direct comparisons, I would either go with a clustered column chart or with a combo chart - a column chart and a scatter plot for the target. I have a full section on actual to budget comparisons in my Excel visualization course - you can watch the preview here: bit.ly/LeilaCharts

[@ismailismaili0071]

Thank u so much teacher I'll look into it

[@tian1139]

May I know how do I filter by 2 weeks instead of months? Example (1-14 Jan/15-31 JAN) as I am looking to track based on every 2 weeks? :)

[@gmo709]

Anyone know how to run these slicers horizontally with months or weeks running left to right vs horizontal? Oops there it is! Thanks! *around 9:40 into vid. Excellent. this is important because i lose that slicer when scrolling down or if i have a frozen pane or lots of rows,etc. Cool. Had to watch that a few times because it was fast but got it. this is the most imp thing for me! Ok, now new question: how do i make the buttons smaller? i have 52 weeks and i want this horizontal. Takes about 4 rows because each button is too wide. i want to shrink the width of the buttons. Hmm. Ok solved...columns button! YES.. new question...The scroll doesnt work left to right. It still runs vertically. Interesting. I would think it would run left to right like the vertical runs North south. But it still runs vertically. why? That isnt logical. run it left to right , same as the vertical scroll takes it down. Microsoft, plz consider this. thx.

[@jboag4631]

Hi, I’m really struggling to format my pivot chart data labels....I can do one bar individually but don’t have the time to do that....I’m trying to show the currency value with , and no decimal places. My pivot data is formatted that way but pulling through in a horrible, unreadable format...🙏

[@viptech3491]

I am big fan your video's I am learning and learnt a lot from your video's. one word to describe Excellent

[@LeilaGharani]

Very happy to hear that YoTech :)

[@paresh282]

After creating Pivot Table chart, I want to any perticular page within a same excel sheet which having 50 No.'s of pages, how can we make this? _pl reply

[@horvathgabor5485]

If I write the number 1 in a cell and then I go to the custom formatting and press DDDD - sunday appears. Okay. But why? Aaaand: interessingly, it doesn't depend on the operation system's settings.

[@1970mariano]

Leila, your videos are excellents and addictive. Thanks for sharing

[@rebm3028]

Hello, With the slicers of a pivot table, I can't keep the format (example wraps) As soon as I click on another segment, there is no more wrapping and the box is unchecked ...

[@ApoorvaDixit1488]

How can we distribute data week wise. I have a list of upcoming milestones for next 2 months which I want to show week wise in 8 weeks. Like W1 Sep, W2 Sep up to W4 Oct.

[@farithahamed3599]

I have an excel table and I created a slicer for the table which does the filtering Now I need to create a pivot chart where the slicer should work for both the excel table and pivot chart? Basically I need to have one Slicer for Excel Table and Pivot Chart. How it can be made possible??

[@akhilreddy5208]

Hello leila,i have a doubt on pivot please clarify.. For example,There is a data in sheet1 with dates,profits,loss etc.,A pivot table is inserted in the new sheet.Now my question is,there is a startdate & enddate in b4 & c4 respectively.If i change the dates the values in the pivot table should reflect only between those dates.How do we do that.Kindly help me with this

[@luc193]

I've a question abt it. When you haven't make any selection in the slicer, so none of the months and the moment you do select a month, the whole formatting of your chart is gone!! What can you do abt that?

[@ibrahimdaas]

Hi .. why you don't have a course in Udemy specifically for Pivot tables ?

[@LeilaGharani]

I have them included in my advanced Excel course :)

[@mykidwholovesminecraft7092]

I like your Excel video. Very informative. But if i have a pivot slicer how can i retrieve information using count

[@subhadiproy4025]

In my case I could not add the slicer as month.. it’s showing only year option and quarter options..but no month option. Please help

[@BayuHendra-qe7kg]

Excel is opened, Barbara, thanks for my solution

[@kHoPhAe]

Wow, I'm glad I subscribed, your channel is a gold mine, thank you!

[@LeilaGharani]

I'm glad to hear that :)

[@ChristyCorum]

I always look specifically to you, Leila. Great explanations, Easy to understand, Concise! Thank you, Thank you, Thank you!

[@vijaysahal4556]

I cnt add in pivot table month bcoz there are only date but while teaching class show there month but then I click date then show only date there are no month add..plz tell me how cn I add

[@miraskhabibulla1345]

Your tutorials are amazing! Concise and straight to the point

[@wout_s]

Thanks very much, especially for the formatting of the slicer! I have one problem though: when I click on the slicer, the cursor always jumps to the pivot chart. Is there any way to stop this from happening? I don't see it in your video!

[@mohitverma8190]

Hi Leila, Looking forward for your reply as i have a doubt after practicing. Slicer is able to control pivot and pivot chart, however data is not organised with the slicer. Is there a way that all three ( Data, Pivot Table, Pivot Chart) are controlled with single slicer. In your video as well it is same.

[@AndreaUK1973]

Hi Leila! Hope you’re well! Do you have a tutorial for building a Dynamic Employee’s Annual Leave spreadsheet ? I can’t find one in your channel!

[@siddhbhushanswami2056]

suppose if we have two pivot tables with dropdown data validation in both of them. Now, for Table A, if I select a value from drop down, then I want a corresponding value to get selected from drop down of Table B. But the catch is, source of both the drop downs is same (i.e. a single column). Is it possible?

[@nickybanya3206]

Hi Leila, your excel skills are excellent and very much effective in workforce management. This slicer is really nice. Can you please help with one click dashboard skills please.

[@paulajimenez65]

I have excel2007 and can't activate the slicer, is there any trick? Thanks!

[@hanyzaki6872]

Hi, Just wondering if you can help. How can I link a Table and a Pivot Table to use one slicer to work on both based on the account name (for example) which appears n both tables. Thank you

[@jankeysecka7591]

I guess you could my assignment in two minutes 😂😂😂 . I am so bad in excel as beginners

[@sanyuktarajgolikar9851]

hi leila, firstly thanks for the lucid lessons/ example.. secondly how to share excel workbook which contains slicers controlled charts? protected excel prevents slicer usage whereas unlocked sheet gives user scope of disarranging the layout etc Pls advise

[@briant6164]

Hi Leila, thanks for this. What if we have different numbers of rows for each day (such as a line for each sale) and (here is the hard part) some days have zero rows. I can't figure out a way for days without a row to count as zero when the pivot table calculates the average.

[@RAKESHYADAV-ht2qp]

please end with connecting with one more pivot table relationship that changes in char as well

[@oOoScarecrow31oOo]

Is it possible to select/unselect what buttons to display (or not) in the Slicers ? 🤔

[@Pecintakebenaran]

Very nice tutorial...very helpfull...how to analyze data with merge colom few subheader, please guide us on that

[@rankingacademy]

awesome and so clear

[@LeilaGharani]

Thank you.

[@mans0011]

Thanks again for another great video. How would you force the chart to show empty weekdays? Don’t want to see Sunday then Tuesday, for example.

[@javelpalmer]

Perfect! What's the easiest way to Import Data from the stock market Website to Excel? With this Pivot style.

[@luisguillermoperezmejia7907]

Excelente aporte. Quedó suscrito. Cordiales saludos desde Medellín Colombia.

[@VinodKumar-zn8ty]

Hello Mam, What is the pattern of Tuesday compared to Thursday you mentioned... can u make me clear on this.