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

[@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

[@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.

[@michaeldiamond2726]

Great Video Leila - like your explanation on some of the in's and outs of how to manipulate a slicer. Keep them coming!

[@LeilaGharani]

Glad to hear that Michael. You're very welcome.

[@hassanmburia8825]

I have created my team dashboard through the slice be blessed

[@waleethmohamedismail4543]

Hi Leila, Greetings! Actually, I learned new things.

[@LeilaGharani]

That's great :)

[@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.

[@sandeepchoudhary5860]

Stellar Leila..!

[@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.

[@DEADYBOYS]

Second Methud Text Fungtion

[@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

[@evgeniam8882]

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

[@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)

[@eg_ak84]

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

[@rvibrahim]

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

[@darkshinigami9438]

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

[@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.

[@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.

[@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?

[@frederikjrvanbruggen]

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

[@Mineirovsky]

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

[@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.

[@yerbolabzhamiyev3268]

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

[@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 :)

[@2808drw]

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

[@TheJimprez]

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

[@1970mariano]

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

[@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? :)

[@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...🙏

[@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.

[@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 ...

[@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.

[@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.

[@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

[@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??

[@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!

[@englishbobscardetailing6001]

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.

[@miraskhabibulla1345]

Your tutorials are amazing! Concise and straight to the point

[@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

[@BayuHendra-qe7kg]

Excel is opened, Barbara, thanks for my solution

[@mykidwholovesminecraft7092]

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

[@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?

[@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

[@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

[@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.

[@muhammadmudassirhussainsid407]

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

[@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

[@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

[@kHoPhAe]

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

[@LeilaGharani]

I'm glad to hear that :)

[@yogalekshumimarimuthu2173]

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

[@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

[@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

[@paulajimenez65]

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

[@jankeysecka7591]

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

[@rankingacademy]

awesome and so clear

[@LeilaGharani]

Thank you.

[@oOoScarecrow31oOo]

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

[@RAKESHYADAV-ht2qp]

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

[@ChristyCorum]

I always look specifically to you, Leila. Great explanations, Easy to understand, Concise! Thank you, Thank you, 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?

[@luisguillermoperezmejia7907]

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

[@bernadettedelacruz154]

Is it possible to apply the slicer too on the data table? Column a to c?

[@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!

[@thomas12ist]

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

[@manimaran4306]

how to bring month in order in filters instead of shows it in rows

[@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

[@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!

[@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?

[@veebee3969]

Very helpful. Great video.

[@Pecintakebenaran]

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

[@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

[@norulzanamohamedsamuri1364]

thanks. great help. nice tutorial

[@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.

[@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.

[@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.

[@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

[@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.

[@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,

[@victorodoyo8977]

How can you sort out data chronologically

[@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

[@javelpalmer]

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

[@Rascalini19]

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

[@dontbeafraid5313]

Hi Leila, can you please help me with my project and willing to pay. Hoping for your positive response. Thanks Your fan Dee

[@LeilaGharani]

Hi Dee, sorry but due to ongoing commitments I cannot take on any consulting projects. You could check out Upwork as an alternative.

[@VinodKumar-zn8ty]

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

[@nayasachin]

For Weekday, you can also use =text(B2,"mmmm").. Sorted!!

[@asadmahboob1123]

Hi Leilal, Thank you for the video. It's very informative. Is there a way to show the data label on a chart only when one single button on a slicer is selected/clicked? If filter is cleared on the slicer, I don't want the data level to be displayed as it will get clumsy.

[@matthiasschumann3177]

Hi Leila, I really enjoy your nice videos and have learned a lot. My current problem: I always have to present data and text in different Excel dashboard sheets. What I have is a big data base with different data. By slicer I select the needed Data and different pivots are presenting the correct results. In the dashboard I take over the pivot data with an lookup formula and they are there. Problem is, that the fixed cell wide is defined, the hight should be automatically extended and reduced by the lenghts of the text. I have no idea how that could be reached automatically by always choosing different data. If you have an idea here, or I did not found your right video... thanks for a hint.

[@Zabikhan14]

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

[@yanmike5212]

Thank you so much for imparting the knowledge

[@vokisek]

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

[@tristangeoffroy9286]

Thank you for the explanation on how customising slicer, .. very stylish as always with your tutorials, ...and a very sweet voice. thank you

[@gjeess1]

Another excellent video, that provides clear instructions for the end-user. Thank you.

[@Tube-id7su]

Actually, i dont know, why the people hitting dislike button.

[@madhun8092]

How do we use weekdays in power query?

[@luttenoop2534]

I am using Excel 2010. Please advise how to group by month?

[@hongqirong9927]

Hi, 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.

[@jitendragupta78]

I learnt so many things like how to have multiple columns in slicers, how to make charts much more presentable..

[@MdAzad-rw3pv]

Dear madam,thanks for video.some problem,I want to know when I will write name of month chart will be show day wise data.

[@dagie07]

Thanks!! This may be a unique request, but is there a way to maintain the row elements of a pivot, even if there is no data in the data table for that particular field? So, maybe in this example, if you have a filter by date (or date range), and wanted to show values by day of week, but the filtered range doesn't have Monday, but we want to maintain all 7 days in the pivot table even after I've filtered on a range that didn't include any Mondays. Does that make sense?

[@dagie07]

So, in your example, if you select Aug or Sep, how would you keep all 7 days of the week?

[@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

[@AndreasBodin]

Great tutorial Leila! I have a question. Is it possible having a pivot table with date and in the pivot chart under the date also have the weekday for that date? So for 3/9 2016, I would like it in the pivot chart to show 3/9 and beneath the date, it should say Saturday. I do not want to create an extra column with the weekday function. I've tried to find a solution but get stuck. :) Maybe it doesn't exist?

[@charliesapks1435]

I HAVE 9 DIFFERENT PIVOT TABLES CHAR ON ONE SHEET BUT THE SLICER ONLY CAN CONTROL 2 WHY ?