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

in this video I'm going to show you two things now one is excels which they function and two is how you can use pivot charts and pivot slicers to add interactivity to your reports so that you can quickly analyze large data sets by which they so for example you want to see if there is a specific pattern on Tuesdays as compared to Thursdays now optimally your charts should also be super easy to update so let's take a look at an example here I have a set of dates and closing price of a specific stock these dates go through a few months now what I want to do is find the weekday that corresponds to this date and ultimately I want to create a report I want to create a chart that shows the weekdays and the average closing price for that weekday so I want to be flexible but I can either look through the entire data set or I can select specific months and take a look at the average price by weekday the first thing I need to do is to bring rich day here and that uses a very simple function called the weekday function so what you need is the serial number so that's basically your days then you need a return type and that basically specifies how you how your week starts starts on a Sunday or Monday now the default of Excel is that it starts on a Sunday but let's say logically you say okay well the week starts on Monday I'm going to pick two as the argument so see what we get we get two back so what does that mean I've put the 2016 calendar here so we can cross check this is the month of February 9th of February is a Tuesday we got to back because we said our rich days starts for Monday so that's a 1 and that's a 2 if I change this to 1 saying that the weekday starts from a Sunday I get a 3 what I don't want here is a is numbers because I want to make a chart and on the x-axis I want to show the weekday name to change this number to the weekend name I can use excels custom formatting so just press control 1 to go to the custom formatting view and here I can type in the abbreviation for day which is d if I type it once I have the number two times I get the 0 2 now I get the text and if I press it 4 times I have the full which they name so Excel figures that out because behind this there is a date so let's see if this name is correct was that in Monday the 9th now the 9th is actually a Tuesday so this is something you need to watch out for but if you're going to use custom formatting to show the week's a name you need to go with excels default which is a 1 or you just leave it out otherwise you get the wrong name so two things right now let's push this down and randomly check some other one let's check this one so the 10th of March it should be a Thursday and that's a Thursday ok so let's move this out of the way let's now create a pivot chart based on this weekdays so have a favorite chart we need a pivot table to feed our pivot table we're going to use this data set but best is to transform this data set into an official excel table by pressing ctrl T now the reason you would want to change this to an excel table is that your source data for your pivot table is going to include any new data that you add to the bottom of this table once you refresh that pivot table if you don't do this you have to manually go and expand that source data so I turned that into a table the first thing I'm going to do is to go to design and clear this default layout it gives me to go back to my own let's give this table a name I'm going to call it prices now I have the option to summarize with pivot table that's my table I want to put it here and put it somewhere close we'll put it on a one now what do I choose here well I want to have weekday on the bottom of the x-axis and closing price on the Y just going to take my space and Excel generally figures it out correctly most of the cases where it should go so that's in the row that's in the column but I don't want some of closing so I'm going to go to value field settings and change that to average a while I'm here I don't want to see all these mixed up decimal places there so I'm going to go to a number format number and just keep this at two decimal places okay so now based on this I'm going to create a pivot chart so just go here click on pivot chart I'm fine with the column the first thing I'm going to do is go remove the elements that I don't need all of these right mouse click add data labels to this and let's give it a name and let's take these away these buttons by going to analyze and clicking on this to toggle them off okay so I have a chart it's not interactive I need to have a slicer so I can go in and insert a slicer I want my slicer by month but let's see what options I have I only have days here so let's click that see what we get but I get every single day that's in here and that's not really going to make sense I want to group these by month so what I need to do is to bring the month option in my pivot table so let me just remove that slicer let's go back to the pivot table to get that I can click on date and you can see what it did it automatically added months and date here so I added a grouping for me here now I don't want to have this stuff in my pivot chart so I can remove this stage just kick them out and kick that one out but I still have the month option available here which means I can use it in my slicer now if you're using an older version of Excel and it doesn't do this for you you need to go to your pivot table and click on those dates and group them by month so now all I need to do is to bring in a slicer for months now it doesn't matter if you bring in your slicer from the pivot table option or from the chart tool options because that controls both views it controls this and ultimately this is controlled by this then insert slicer and now that I have the month option I'm going to click that and I get the month ok so I click on a specific month I get the average prices by which day for that month I can hold down control and click on different months I can click this or I can use this and pick or do select what I don't want okay now these grayed out ones are months that I don't have data in so if I don't want to see them at all I can control that under slicer tools option under slicer settings and I can say hide items with no data so it's going to hide these ones so the other thing is I do want to see the header if I want to see I want to be able to use this filtering but I don't want that captions I'm just going to delete that as well okay so it's starting to look good let's just make some cosmetic changes to this so that they fit better in the report but one thing I'm going to do is reduce the gap width of my chart to a hundred to get them closer then let's go with a darker color and well let's say that in my report I have let's bring these down here you can see better I have a light background color I can take away the shape fill from my chart so that it sits better on my report and that border is definitely not needed here so let's take that away and here for the slicer you have different options we can decide if you want them in this format so more horizontally just have to reduce this or if you want them set up like this so that really depends on how you're planning to set up your report now I want this slicer to also fit better in this report so I'm going to go on format this just click on it you see the slicers Styles first up is take one that is closest to the style that you want so I'll go with this one the changes I would want to make to this is one is take away the border and another is to give it the same background color as this gray here to do that you have to duplicate one of these given ones I'm going to use this one and give it a name let's call it price slicer now for the whole slicer I'm going to format that under border I don't want any border and fulfill I want that gray here now when you do that it doesn't take it because it's still on the old one so you'll see yours in the custom you have to go and select it and then it looks much better our last test let's go and add new data set to this we ended in August let's add some data for September just going to find and replace the month here just for the testing let's see if September pops up here so I do have to go and refresh this the data for September is now here so we click on it and we can see it here so that's how you can use excels weekday function to get the name of the week out and that's how you can also use pivot charts and pivot slicers to do interactive analysis on your data sets if you liked this video don't forget to give it a thumbs up and if you like these type of videos don't forget to subscribe to my channel so that you can get notifications when new videos like this one come out

Channel: Leila Gharani

Views: 212,544

Rating: 4.9776273 out of 5

Keywords: Excel PivotTable, pivot charts, pivot charts with slicers, Excel pivot slicer dashboard, Excel pivot slicers, customize pivot slicer, excel weekday text, average stock price report, Excel pivot date slicer, Advanced Excel tricks, Excel custom slicer, Excel weekday name formula, Excel tips and tricks, Excel weekday function, Excel 2010, Excel 2016, Excel for analysts, Excel 2013, Excel pivot for months from date, Leila Gharani, Advanced Excel Tutorials, XelplusVis

Id: G6ImRBn3cQk

Length: 11min 47sec (707 seconds)

Published: Thu Jul 13 2017