How to Filter Pivot Tables for Month-to-Date MTD Comparisons with Slicers

welcome to Excel campus my name is John and in this video I'm gonna explain how to create month to date calculations in your pivot tables using a slicer so this is great for month over month or year over year comparisons in this pivot table here I basically have a sum of price or sum of revenue here and I'm trying to compare the January 2016 number to the January 2015 numbers and also the historical months as well but I only have the first 15 days worth of data for January 2016 so I need to filter down my 2015 numbers to also include just the first 15 days because currently these numbers include all the days of the month the entire month worth of data and it's not easy to make a comparison between just 15 days worth of data and then a full month historically so we can use a slicer for this I have a slicer setup here that just basically has the day of the month and if I just select 1 through 15 the first 15 days of the month that will filter my pivot table for the first 15 days in each month over here so now I can start to make some comparisons between the first 15 days in 2016 of January and then all of the months in 2015 as well so in this video I'm going to explain exactly how I set this up how we add this slicer in here and we will also add a field to our data set that calculates the day all right so I'm going to start fresh with my data set here and we're going to go ahead and recreate that pivot table and the slicer so just to give you a little background on this data set it's very simple it basically just contains a column here for the date this would be the transaction date so each row in the data set will be one transaction for an item that we sold and the price that we sold it for so as you can see here there's a lot of data if I hit ctrl down arrow that'll take me all the way to the bottom of the table here and you can see we only have data through January 15th 2016 so the first 15 days of the month for the new year so we basically just need to create a pivot table and then have a way to filter it for just the first 15 days for every month in the data set so I'm going to hit control up arrow again to go back up to the top and to do that we're going to add a new column here to our data set so I'm just going to select the cell here I'm going to type the word day we're going to call it day and then hit enter and that'll add a column to our table here now I am using Excel tables for this you don't have to use Excel tables but it does make it easier when you're working with pivot tables so in this day column we basically want to return at the day number of the month for the date and Excel has a function built into it called day that will help us do that so I'm going to type equals and then the word day and you can see there's a day function right here so I'm gonna hit the tab key to tab into that now the day function is very simple it just has one argument which is the serial number and that just means a date in Excel so the serial number is going to be a date so we're going to feed this day function a date in this case I'm going to select cell a2 here to select that date for that row and then I'm going to close the parentheses and hit enter and that will basically fill the entire column here with the day of the month for each date so you can see here in row four we have it's returning a two for the second day of the month because this is January 2nd 2015 if I scroll down here to February you can see that it's still doing the same thing for this row here it's returning a 4 for February 4th at the 4th day of the month so we basically now have a column that is going to allow us to filter the data for just as a certain period or a certain range of days within the month so the next thing I want to do is create the pivot table so I'm just going to select any cell inside my table here I'm going to go to the insert tab on the ribbon and choose pivot table and that will prompt me to create the pivot table it'll automatically select my entire table over here for me that's called TBL data and it's going to place it on a new worksheet and I'll hit OK so that's basically created a new pivot table here in a new worksheet and now we just need to build out the pivot table and add the slicer so the first thing I'm going to do is take the date field and drag it into the rows area of the the table now I'm using Excel 2016 and Excel 2016 automatically groups your dates for you so it's going to basically automatically group it into year's quarters and then months and that's what's happened here so if you could expand this you can see the quarters and then the months within those quarters now if you're using an earlier version of Excel that won't happen when you drag the date into the rows area so I'm going to ungroup this right click ungroup and this is what you'll see if you're using an earlier version of Excel and it's very easy to create a date grouping you just right-click on any cell that contains a date and clip click the group button here and that will bring up this window for grouping and in this case here we just want to group the months and the years so you can just select those two items if you want to do a quarter-over-quarter comparison you could select quarters as well we're going to keep it simple for this so I'm going to click OK and now basically I have the years and the months here in my rows area and I can see that over here as well we have years and then date basically represents the months so now I'm going to do is drag the rows I'm sorry the years up into the columns area so I basically get a call or a year over your comparison of 2015 and column B 2016 and column C and now I want to drag the price I'm going to take the price and put that in the values area of the pivot table and that's basically going to create a calculation a sum of price so now we're getting closer to the pivot table that we saw in the original example I don't need the grand total here so I'm just going to right click and select remove grand total we don't need that for now and now we need to add the slicer so basically I want to add the slicer for that day field that we created in the data set so I'm going to move my field list out of the way here I'm just going to select any cell inside the pivot table go to the analyze or the options tab in the ribbon and then choose insert slicer that'll bring up the insert slicer window here and it will just have a list of all the fields in our data set I'm going to select a day click the day checkbox and then hit OK and that'll basically now create this slicer and add it to the sheet that will allow us to slice on the days and you might also want to quickly reformat this so I had seven columns here if you go to that if you select the slicer go to the options tab the columns here instead of one I'm going to type seven and hit enter and then I just need to resize my slicer a little bit and basically now I have what looks like a calendar and I can just left click and hold on the one and then drag down here to the fifteen and that will slice my pivot table or filter my pivot table for the first 15 days of every month so now you can see here that my pivot table has been filtered for the first 15 days of every month and I can start making some comparisons from my 2016 to my 2015 data so we can see here that we actually have the same exact amount for 2015 and 2016 for the first 15 days of the month now you can also use a slicer for other time analysis as well maybe you just want to see the first 7 days of the month you could select the one dragged over here release the mouse on 7 and that will select all first 7 days of the month and now we can see basically our pivot table has been filtered for the data for the first 7 days of the month and if we just just to explain that how that's working a little better if we jump back to the data set here again we have our day column all that slicer is really doing is filtering our data down for those certain number of days so in this case here if we just wanted to see the data for the first day or the first two days of the month I can just select those items filter for those items and click OK and now basically my data sets filtered down for just the first two days of every single month in the years I scroll down we're only seeing ones and twos in the day column here and that's exactly what the pivot table is doing as well it's just basically in memory it's filtering that information if we just found the first two days it's just filtering that information for those first two days and then displaying that in the pivot table so that's basically how that works and it's a great way to do all kinds of time analysis and especially month to date calculations when you only have a certain time period or a certain number of days in your most current month of data if you enjoyed that video there are a few simple things you can do to help me out if you are watching this video on YouTube click the like button below the video and leave a comment with any questions or feedback and please don't forget to subscribe to my free email newsletter to get more tips and tricks that will help you learn Excel thanks again for watching and I'll see you soon

Channel: Excel Campus - Jon

Views: 174,157

Rating: 4.9507451 out of 5

Keywords: Excel, Pivot Tables, Slicers

Id: DGwkAi4hRY4

Length: 9min 28sec (568 seconds)

Published: Fri Jan 22 2016