Get 20+ Excel courses at Simon Sez IT, including training for Excel 365, 2021, 2019, 2016, 2013, and more ️ https://www.simonsezit.com/course-category/excel/ Get a FREE 2.4 hour Excel 2019 Beginners course here ►
https://simonsezit.lpages.co/excel-2019-beginner-course-ssit/

In this video, learn how to utilize filters and slicers while creating pivot table in Microsoft Excel 2013.

Get the full course on Excel 2013 course here: http://www.simonsezit.com/courses/microsoft/learn-microsoft-excel-2013-advanced-training-tutorials/

Watch the Excel 2013 playlist: https://www.youtube.comhttp://www.youtube.com/playlist?list=PLzj7TwUeMQ3jVfCjoptJb_mimawnVezqh


Stay in touch:
SimonSezIT.com: https://www.SimonSezIT.com/
StreamSkill.com: https://StreamSkill.com/
YouTube Channel: https://www.youtube.com/user/simonsezittraining
If you enjoyed the video, please give a "thumbs up" and subscribe to the channel -)

Welcome back to our course on Excel 2013 Advanced. In the previous couple of sections we’ve been looking at pivot tables in Excel 2013 and in this section we’re going to continue looking at pivot tables. We’re going to concentrate in this section on filtering a pivot table and on the use of slices. Before we do that I’d like to do a couple of things that I mentioned earlier on and one of them is I’d like to change the range of data that’s actually covered by the pivot table we’ve been working on.

So this is very straightforward to do, particularly as we set this pivot table up on the basis of using our store sales table. The store sales table we setup with the first 20,000 rows of the data that I had. The data I have extends to 34,109 rows. Let’s go back to the pivot table. This is the bottom right hand corner and the total value of the cells there, $399,000 so we’ll call it $400,000. Let’s go back into the store data here, click somewhere within the table, and then the Table Tools Design tab, go to resize table, and there’s the old definition of a range going up to 20,000. Let’s make it 34,109, click on OK. Now let’s go back to the pivot table. Notice that we still have a value here of 399,783.01. So the update is not automatic.
Now this is pretty straightforward.

If you went to the Analyze tab, you’ve got a couple of options there in the Data Group. One of the options is to change the data source altogether. So if in fact you were going to pick up a new set of store data, perhaps the latest figures from another worksheet or even another workbook, you could do that here. But if all you want to do is to refresh the data source you’ve already got setup and just bear in mind if I click on change data source don’t forget the data source here is the table Store Sales which I have just changed the definition of. So my pivot table is already defined in terms of a table, a named table. If I go back, then into analyze and just click on refresh it does the refresh. And then let me just go along to that total and you’ll see the total now is $804,000. So I’ve almost doubled not only the number of transactions but the total value as well.

Now I just want to adjust what we see a little bit more. So on the Analyze tab in the pivot table tools, Show on the right gives me a field list. If I click on that, I can hide the pivot table fields panel. I also use that drop down there to bring it back again. I’m also going to use the Design tab Pivot Table Styles, click on the gallery here, and choose a different style from the gallery. I’m going to choose that style there. Okay. Now let’s start looking at filtering.

Now let’s start by filtering the row. So we’re talking about which rows are included. To include a row or not include it for that matter, we’ve actually got two fields. We have the branch and then we have the date. Let’s start with branches. At the top of the branch column here, basically the branches are shown in column A. We have a drop down and that leads us to a filter for branches. By default all branches are selected, but if I click on Select All here, to deselect all branches I could for instance say I only want to see Boston. Click on OK and my pivot table now is reduced to one that only shows Boston.

So you won’t be surprised to learn that those filters work in pretty much the same way that filters work generally in Excel. So let’s go back into that filter again. Let’s switch the whole lot back on again, click on OK. So I’ve now got all of my branches again and I could similarly go in there and say right I’m going to keep all of the branches but I’m going to choose to sort Z to A, so I’m going to put them in reverse alphabetical order now.

Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.